Postgres very slow queries after deleting 500 million rows

Question

I have a Postgres 11 database that grew faster than expected, ultimately to around 500 million rows, and after optimizing our code we were able to get this down to around 10 million rows at any given time (around 1,000-5,000 inserts per second on average).

The problem is that after removing 490 million rows, auto vacuum seems to constantly be running, and queries still take almost as long as they did before removing 490 million rows.

As downtime is not really an option for us, is there any suggestions on how we could help optimize things at all?

1. Should I remove any indexes on the table and recreate them? Will this help at all?
2. Since auto vacuum seems to constantly run, and we can't do a vacuum full (can't have downtime if possible), any recommendations around this?

I posted another question about adding a primary key to use AWS DMS to upgrade to a newer version of Postgres where I believe VACUUM can run in parallel (hopefully that will help): Postgres add PRIMARY KEY to partitioned very active table without lock

Kinda running out of options hoping someone may have some suggestions :(

Here's output from just a simple query for 1000 rows that takes ~18-19 seconds

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT * FROM "public"."table" LIMIT 1000 OFFSET 0;

  Limit  (cost=0.00..46.92 rows=1000 width=128) (actual time=18452.685..18468.174 rows=1000 loops=1)
  Output: difficulty, user, worker, useragent, ipaddress, source, created
  Buffers: shared hit=2585 read=322681 dirtied=321175 written=102
  I/O Timings: read=13292.325 write=0.399
  ->  Seq Scan on public.shares_nexa  (cost=0.00..14031593.64 rows=299050464 width=128) (actual time=18452.683..18468.080 rows=1000 loops=1)
        Output: difficulty, user, worker, useragent, ipaddress, source, created
        Buffers: shared hit=2585 read=322681 dirtied=321175 written=102
        I/O Timings: read=13292.325 write=0.399
Planning Time: 0.056 ms
Execution Time: 18468.237 ms

n_dead_tup for this specific table shows 556993041

Answer 1

For reference the way we were able to solve this was a combination of things. Thanks to @jjanes answer I dug further into autovacuum_work_mem which found was set to -1 which tells db to use maintenance_work_mem.

What I did was:

autovacuum_work_mem value up to 1GB

autovacuum_vacuum_cost_limit up to 2000

IOPS in AWS up to 19000

After doing this, and then scaling up the IOPS to max, the vacuum process was able to complete without constantly restarting itself. After doing that was able to scale IOPS back down to a normal level.

Another option that worked as well in test environment was inside of a transaction creating a new table, dropping the original, and then renaming to the original table name:

BEGIN;
CREATE TABLE mytable_tmp (LIKE mytable) PARTITION BY LIST (locationid);
ALTER TABLE mytable_tmp ADD COLUMN id BIGSERIAL NOT NULL;
ALTER TABLE mytable_tmp ADD PRIMARY KEY(id, locationid);
CREATE TABLE mytable_tmp_office PARTITION OF mytable_tmp FOR VALUES IN ('office');

INSERT INTO mytable_tmp SELECT * FROM mytable;

DROP TABLE mytable;

ALTER TABLE mytable_tmp RENAME TO mytable;
ALTER TABLE mytable_tmp_office RENAME TO mytable_office;
COMMIT;

Doing this we then were able to re-add the indexes after it was completed. IF you want to copy indexes and everything else use this instead when creating the tmp table:

CREATE TABLE mytable_tmp (LIKE mytable INCLUDING ALL) PARTITION BY LIST (locationid);