This PostgreSQL query compactifies the LNbits apipayments table. It collects all transactions older than a certain date (30 DAYS here) and aggregates them in one single transaction (for each, incoming and outgoing payments, separately).
The balances of all users remain unaffected. However, the transaction table can shrink significantly, which should speed up everything that LNbits does (especially the balance view).
Thank you @einzie for helping with this.
-- ******************************
-- lnbits compactification script
-- ******************************
BEGIN;
-- create a archival backup table if it doesn't exist yet
CREATE TABLE IF NOT EXISTS apipayments_archive (LIKE apipayments INCLUDING ALL);
-- summarize all old transactions
INSERT INTO apipayments (checking_id, amount, fee, wallet, pending, memo, time)
SELECT MAX(checking_id) || '-temp-checking' AS checking_id,
SUM(amount) as amount,
SUM(ABS(fee)) as fee,
wallet,
False as pending,
'Archive transaction' AS memo,
NOW() - INTERVAL '30 DAYS' AS time
FROM apipayments
WHERE time < date(NOW() - INTERVAL '30 DAYS')
AND pending = False
AND amount > 0
GROUP BY wallet;
INSERT INTO apipayments (checking_id, amount, fee, wallet, pending, memo, time)
SELECT MAX(checking_id) || '-temp-checking' AS checking_id,
SUM(amount) as amount,
SUM(ABS(fee)) as fee,
wallet,
False as pending,
'Archive transaction' AS memo,
NOW() - INTERVAL '30 DAYS' AS time
FROM apipayments
WHERE time < date(NOW() - INTERVAL '30 DAYS')
AND pending = False
AND amount < 0
GROUP BY wallet;
-- backup archived transactions into archive table
INSERT INTO apipayments_archive SELECT * FROM apipayments WHERE time < date(NOW() - INTERVAL '30 DAYS') AND pending = False;
-- delete archived transactions from original table
DELETE FROM apipayments WHERE time < date(NOW() - INTERVAL '30 DAYS') AND pending = False;
UPDATE apipayments SET checking_id = REPLACE(checking_id,'-temp-checking','');
COMMIT;