Early in the morning every day a pgAgent job refreshes the contents of table A from table B on my PostgreSQL 8.4 database. Table A contains around 140k records across 91 columns and has two indexes - one as part of the PRIMARY KEY and the other a GIST index on a POINT PostGIS geometry column.

To make the process go a little faster the job drops the index on the geometry column, before deleting the records in the table A and inserting the records from table B, then the index is recreated. This all being done the autovacuum daemon gets to work when it feels like it (after ten minutes or so from comparing the job stats and table stats for the job completion time and autovacuum run time).

Upon checking on the table this morning after all this had happened the table stats told me the table size was 272MB, the TOAST table size was 8192bytes, and the index size was 23MB. This seemed quite large so I issued a REINDEX command on the table and the index size came down to 9832kB.

My question(s) is this:

Why does the REINDEX apparently reduce the size of the indexes so much when the indexes (or at least the geometry column index) have been built anew from scratch? Should I make sure that the table has been vacuumed/analyzed before the indexes are built? Is not dropping the index on the primary key a factor in this? What am I missing?

  • 1
    Does anything prevent you from upgrading to 9.3? Otherwise, I don't remember 8.4 too much, but can it be that the sizes differ only because the table was not analyzed recently? I'd check (if possible) if after a plain ANALYZE the reported size decreases, too.
    – dezso
    Jul 24 '14 at 12:48
  • @dezso We can't update to a more recent version in the near future unfortunately. I will try re-analyzing at the next opportunity after one of the daily refreshes - does ANALYZE collect statistics on the indexes? Jul 24 '14 at 12:55
  • @deszo Issuing a VACUUM ANALYZE checking the results and then REINDEXing gives the same drastic reduction in the index size. Jul 31 '14 at 11:40
  • Or, while on the topic of upgrading, why not straight to the current version 9.4? Postgres 8.4 has reached EOL in 2014. Vacuuming and indexing has been reworked and improved many times since. Feb 22 '15 at 15:36
  • @ErwinBrandstetter - we are crawling towards an update here... Soon my colleagues will be updating their software which will allow them to upgrade to Cadcorp SIS 8.0, which will in turn allow us to upgrade to Postgres (to 9.3). I look forward to reaping the vacuuming and indexing rewards! Feb 24 '15 at 15:09

If the CREATE INDEX statement sees that another session holds an active snapshot that might still be interested in the deleted records, then it includes those deleted records into the new index.

Similarly, if a REINDEX sees that another session holds an active snapshot that might still be interested in the deleted records, then it includes those deleted records into the new index.

If a VACUUM sees that another session holds an active snapshot that might still be interested in the deleted records, then it keeps those records in the table. And then the REINDEX or CREATE INDEX also need to carry them into the new index, as long a the snapshot still exists.

Once there or no longer any snapshots that could possible see the deleted rows, then the VACUUM may remove them from the table. But a CREATE INDEX or REINDEX could also just not carry them over into the new index, whether VACUUM had gotten around to removing them from the able or not.

So in your scenario, the role of the VACUUM between the initial CREATE INDEX and REINDEX is probably just to take up time, during which time your long-running transaction hopefully goes away on its own and drops the interfering snapshot.

  • That must be it. I will have to keep a watch for such transactions. Aug 8 '16 at 15:32
  • Is reindexing required for postgres 9.3? Jul 11 '17 at 9:42
  • "If X sees that another session holds an active snapshot..." Can that information be queried somewhere ?
    – yucer
    Aug 10 '21 at 13:55

Having tried different orders of doing things it does seem that performing a VACUUM before a REINDEX instruction is the only way to get the reduction in size, perhaps because the un-vacuumed space adds to the index (indexing of deleted records?). Forcing a table rewrite by using

ALTER TABLE blah ALTER COLUMN whiffle SET DATA TYPE whiffle_type;

does the same kind of thing, as it clears out the disused space.

Having to VACUUM in the middle of the process does break the flow a little as one must issue a VACUUM command outside of a transaction.

  • Are you deleting or truncating? And have you set a fillfactor of 100 on those indexes? Dec 1 '15 at 23:27
  • Hi @DavidAldridge. I am deleting rather than truncating. The fillfactor is the default. Dec 4 '15 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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