2

I create a table with 1 million records, then I delete those records. (Common with some sort of processing list.)

CREATE TABLE example (id int PRIMARY KEY);
INSERT INTO example SELECT generate_series(1, 1000 * 1000);
DELETE FROM example;
VACUUM example;

The index size remains at the same size as before deletion.

SELECT pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_class c
WHERE oid = 'example_pkey'::regclass;
 size  
-------
 21 MB
(1 row)

Likewise, bloat-detection queries (e.g. ioguix/pgsql-bloat-estimation) identify this index as extremely bloated.

I don't care about the disk usage itself. Will the performance of the index be affected by its large size? Should I be running REINDEX?

1
1

There may be a substantial impact on the first few queries after the rows were deleted, because autovacuum hasn't finished processing the table yet and queries need to dig through a lot of index entries that belong to deleted rows.

However, these index scans will mark the index entries as "dead", and subsequent index scans will ignore them.

After that, and after VACUUM has finished, index scans will be alnost as fast as before.

1
  • I believe this is correct. Completely empty index pages don't hurt performance. Unfortunately, a lot of the scripts just look at the total physical index size, which never decreases without REINDEX/VACUUM FULL. pgstatindex can give more accurate information, but it's slow as it has to read the index. Aug 6 at 14: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.