We have a table with time-series-like data (~200million rows, 30GB data size + index) in a PG 11 database, and for the first time since we started writing into it, we have to delete from it (~30%).
To keep things performant after the delete we want to

  • rebuild the indexes
  • run a full vacuum on it

To get a better understanding of how much time this will take I have created a dump of the given table, loaded it into a local database, deleted the data, rebuilt the index, and ran a full vacuum:

REINDEX TABLE my_schema.my_table;
VACUUM (FULL, VERBOSE, ANALYZE) my_schema.my_table;

To my surprise, both finished quite fast (2 x 30min).

I know to get the exact time required to run these commands on the server I need to backup the filesystem to get the exact same local snapshot as the server and run it on the same hardware. However from my limited understanding because we have never deleted or updated rows in the table the restored local snapshot should be physically very similar to the table on the server. So the operations on the server should take a similar amount of time to finish.

This is not what I observe currently. I have deleted the data and re-indexing took 6x more times than on the local system. Similarly, the vacuum took around 6x times more as well. This is a significant difference that I would have never expected.

My local hardware is similar to the server (16GB vs 20Gb), but with more CPU cores (2vCPU vs i7 8vCPU). I have SSD on my local machine and I don't know what storage the server uses but I would be surprised if it was non-SSD (We are using Azure Single Server for PostgreSQL).

The CPU utilization was below 10% on the server all the time so I think the operation was not CPU bound. (I am just guessing here.)

The table contains 4 indexes: 3 of those a single-column BTREE index with mostly even distribution (date-time, synced source file ID, consumer ID) and a compound index for these 3 together.

Why the difference is so big? Was my local testing flawed?

  • What about hardware differences? CPU, RAM & esp. HDD/SSD configuration?
    – Vérace
    Jan 30, 2022 at 22:15
  • Please show the exact commands used. The question is hardly useful without. And other information as instructed here: dba.stackexchange.com/tags/postgresql-performance/info Jan 30, 2022 at 22:37
  • @Vérace-getVACCINATEDNOW Hardware was similar but I don't know the storage method on the server. I have updated the question. Jan 31, 2022 at 0:19
  • @ErwinBrandstetter Thanks for the request I have updated the question. Jan 31, 2022 at 0:19
  • 1
    Did you make sure that you really need to rebuild table and indexes? Normally, that is not necessary in PostgreSQL. Jan 31, 2022 at 4:56

1 Answer 1


we have never deleted or updated rows in the table

Then there is typically no reason to run VACUUM FULL at all. Maybe VACUUM (without FULL) is useful. But, typically, that's covered by autovacuum.

REINDEX may be useful. Consider the scenarios listed in the manual here and here. If you create the index after filling the table, REINDEX is certainly wasted.

Not exactly sure where the observed difference comes from. (A hosted service is always a bit of a blackbox.)

Either way, VACUUM FULL rebuilds table and indexes from scratch. An additional REINDEX adds nothing useful over just VACUUM FULL. See:

  • The reason I run index rebuild is that I have read it somewhere that an index rebuild prior to vacuuming can significantly make vacuum faster. I have not tested with vacuum only so I don't know if this is true in this case or not. Jan 31, 2022 at 8:27
  • 1
    @NoNameProvided: Sure, REINDEX can make a following VACUUM (FULL) faster. But VACUUM FULL alone is still faster than doing both. And I doubt you need either. Jan 31, 2022 at 16:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.