Postgresql – Postgres: Vacuum taking too long

amazon-rdspostgresql

I have a database currently sitting on ~6B live tuples, ~4B dead tuples.

I've ran the VACUUM command for 3 days now, and then suddenly my internet died. Meaning i lost all progress. How can i speed up the VACUUM speed? It's currently running on AWS RDS.

Is there a way to run command in background, e.g. disconnect/turn off PC?

Btw running on 9.3

Best Answer

You may want to turn on AUTO VACUUM? AWS Docs suggests that as a best practice

Set the autovacuum and once it is done, you can turn it off if you wish.

If you want to really run it in a terminal and then disconnect, refer to this answer: How to execute a query from psql without waiting for the result?

Related Solutions

PostgreSQL Performance – Simple Select Query Taking Too Long

You have almost 40000 rows where word='Facebook*'. I suppose that these rows are somewhat randomly scattered on the whole table. This means that Postgres has to read tens of thousands of random blocks from a disk, which might be slow.

You can try to cluster rows that have the same word together. You can use the command cluster misspelled using test_scanid;. This would take some time and block the table until done, so plan accordingly.

PostgreSQL – Troubleshooting Slow Drop Table Operations

You can list the running backends with

SELECT * FROM pg_stat_activity;

identify the process trying to rollback something which is working on this table.

Find its pid.

You can terminate a backend with the query SELECT pg_terminate_backend(64738), where 64738 is the pid column in your previous SELECT.

After that, you can likely DROP that table.

If even that won't work, then restart postgresql, but most likely it will.

Best Answer

Related Solutions

PostgreSQL Performance – Simple Select Query Taking Too Long

PostgreSQL – Troubleshooting Slow Drop Table Operations

Related Question