cross-posted from: https://lemmy.cafe/post/1403198

Overview

This is a quick write up of what I had spent a few weeks trying to work out.

The adventure happened at the beginning of October, so don’t blindly copy paste queries without making absolutely sure you’re deleting the right stuff. Use select generously.

When connected to the DB - run \timing. It prints the time taken to execute every query - a really nice thing to get a grasp when things take longer.

I’ve had duplicates in instance, person, site, community, post and received_activity.

The quick gist of this is the following:

  • Clean up
  • Reindex
  • Full vacuum

I am now certain vacuuming is not, strictly speaking, necessary, but it makes me feel better to have all the steps I had taken written down.

\d - list tables (look at it as describe database);

\d tablename - describe table.

\o filename\ - save all output to a file on a filesystem. /tmp/query.sql` was my choice.

instance

You need to turn indexscan and bitmapscan off to actually get the duplicates

SET enable_indexscan = off;
SET enable_bitmapscan = off;

The following selects the dupes

SELECT
	id,
	domain,
	published,
	updated
FROM instance
WHERE
	domain IN (
		SELECT
		        domain
		FROM
		        instance
		GROUP BY domain
		HAVING COUNT(*) > 1
	)
ORDER BY domain;

Deleting without using the index is incredibly slow - turn it back on:

SET enable_indexscan = on;
SET enable_bitmapscan = on;

DELETE FROM instance WHERE id = ;

Yes, you can build a fancier query to delete all the older/newer IDs at once. No, I do not recommend it. Delete one, confirm, repeat.

At first I was deleting the newer IDs; then, after noticing the same instances were still getting new IDs I swapped to targetting the old ones. After noticing the same god damn instances still getting new duplicate IDs, I had to dig deeper and, by some sheer luck discovered that I need to reindex the database to bring it back to sanity.

Reindexing the database takes a very long time - don’t do that. Instead target the table - that should not take more than a few minutes. This, of course, all depends on the size of the table, but instance is naturally going to be small.

REINDEX TABLE instance;

If reindexing succeeds - you have cleaned up the table. If not - it will yell at you with the first name that it fails on. Rinse and repeat until it’s happy.

Side note - it is probably enough to only reindex the index that’s failing, but at this point I wanted to ensure at least the whole table is in a good state.

Looking back - if I could redo it - I would delete the new IDs only, keeping the old ones. I have no evidence, but I think getting rid of the old IDs introduced more duplicates in other related tables down the line. At the time, of course, it was hard to tell WTF was going on and making a wrong decision was better than making no decision.

person

The idea is the same for all the tables with duplicates; however, I had to modify the queries a bit due to small differences.

What I did at first, and you shouldn’t do:

SET enable_indexscan = off;
SET enable_bitmapscan = off;

DELETE FROM person
WHERE
	id IN (
		SELECT id
		FROM (
			SELECT id, ROW_NUMBER() OVER (PARTITION BY actor_id ORDER BY id)
			AS row_num
			FROM person) t
		WHERE t.row_num > 1 limit 1);

The issue with the above is that it, again, runs a delete without using the index. It is horrible, it is sad, it takes forever. Don’t do this. Instead, split it into a select without the index and a delete with the index:

SET enable_indexscan = off;
SET enable_bitmapscan = off;

SELECT
	id, actor_id, name
FROM person a
USING person b
WHERE
	a.id > b.id
AND
	a.actor_id = b.actor_id;

SET enable_indexscan = on;
SET enable_bitmapscan = on;

DELETE FROM person WHERE id = ;

person had dupes into the thousands - I just didn’t have enough time at that moment and started deleting them in batches:

DELETE FROM person WHERE id IN (1, 2, 3, ... 99);

Again - yes, it can probably all be done in one go. I hadn’t, and so I’m not writing it down that way. This is where I used \o to then manipulate the output to be in batches using coreutils. You can do that, you can make the database do it for you. I’m a better shell user than an SQL user.

Reindex the table and we’re good to go!

REINDEX table person;

site, community and post

Rinse and repeat, really. \d tablename, figure out which column is the one to use when looking for duplicates and delete-reindex-move on.

received_activity

This one deserves a special mention, as it had 64 million rows in the database when I was looking at it. Scanning such a table takes forever and, upon closer inspection, I realised there’s nothing useful in it. It is, essentially, a log file. I don’t like useless shit in my database, so instead of trying to find the duplicates, I decided to simply wipe most of it in hopes the dupes would go with it. I did it in 1 million increments, which took ~30 seconds each run on the single threaded 2GB RAM VM the database is running on. The reason for this was to keep the site running as lemmy backend starts timing out otherwise and that’s not great.

Before deleting anything, though, have a look at how much storage your tables are taking up:

SELECT
	nspname                                               AS "schema",
	pg_class.relname                                      AS "table",
	pg_size_pretty(pg_total_relation_size(pg_class.oid))  AS "total_size",
	pg_size_pretty(pg_relation_size(pg_class.oid))        AS "data_size",
	pg_size_pretty(pg_indexes_size(pg_class.oid))         AS "index_size",
	pg_stat_user_tables.n_live_tup                        AS "rows",
	pg_size_pretty(
		pg_total_relation_size(pg_class.oid) /
		(pg_stat_user_tables.n_live_tup + 1)
	)                                                     AS "total_row_size",
	pg_size_pretty(
		pg_relation_size(pg_class.oid) /
		(pg_stat_user_tables.n_live_tup + 1)
	)                                                     AS "row_size"
FROM
	pg_stat_user_tables
JOIN
	pg_class
ON
	pg_stat_user_tables.relid = pg_class.oid
JOIN
	pg_catalog.pg_namespace AS ns
ON
	pg_class.relnamespace = ns.oid
ORDER BY
	pg_total_relation_size(pg_class.oid) DESC;

Get the number of rows:

SELECT COUNT(*) FORM received_activity;

Delete the rows at your own pace. You can start with a small number to get the idea of how long it takes (remember \timing? ;) ).

DELETE FROM received_activity where id < 1000000;

Attention! Do let the autovacuum finish after every delete query.

I ended up leaving ~3 million rows, which at the time represented ~ 3 days of federation. I chose 3 days as that is the timeout before an instance is marked as dead if no activity comes from it.

Now it’s time to reindex the table:

REINDEX TABLE received_activity;

Remember the reported size of the table? If you check your system, nothing will have changed - that is because postgres does not release freed up storage to the kernel. It makes sense under normal circumstances, but this situation is anything but.

Clean all the things!

VACUUM FULL received_activity;

Now you have reclaimed all that wasted storage to be put to better use.

In my case, the database (not the table) shrunk by ~52%!

I am now running a cronjob that deletes rows from received_activity that are older than 3 days:

DELETE FROM
	received_activity
WHERE
	published < NOW() - INTERVAL '3 days';

In case you’re wondering if it’s safe deleting such logs from the database - Lemmy developers seem to agree here and here.