REINDEX takes significantly more time on production system than on local snapshot
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?
we have never deleted or updated rows in the table
Then there is typically no reason to run
VACUUM FULLat all. Maybe
FULL) is useful. But, typically, that's covered by https://www.postgresql.org/docs/current/routine-vacuuming.html .
REINDEXmay be useful. Consider the scenarios listed in the manual https://www.postgresql.org/docs/current/sql-reindex.html#id-188.8.131.52.5 and https://www.postgresql.org/docs/current/routine-reindex.html . If you create the index after filling the table,
REINDEXis certainly wasted.
Not exactly sure where the observed difference comes from. (A hosted service is always a bit of a blackbox.)
VACUUM FULLrebuilds table and indexes from scratch. An additional
REINDEXadds nothing useful over just
VACUUM FULL. See: