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