On Mon, Jul 15, 2019 at 7:21 PM Peter Geoghegan <pg@bowt.ie> wrote:
> Sometimes B-Tree indexes can be *larger* after a REINDEX (or after
> they're recreated with a CREATE INDEX). It's not that common, but it
> does happen. There isn't actually a very large size difference here,
> so it seems worth comparing index size in detail.
A very good guess, and effectively reindexing the databases the size
of the _restored_ one has shrinked being less than the original one
(as I would expect in first place):
% psql -U postgres -c '\x' -c "SELECT pg_database_size( 'foo' ),
pg_database_size( 'bar' );" template1
Expanded display is on.
-[ RECORD 1 ]----+-----------
pg_database_size | 2685776543
pg_database_size | 2690269699
% vacuumdb --full foo
vacuumdb: vacuuming database "foo"
% psql -U postgres -c "REINDEX DATABASE foo;" foo
REINDEX
% vacuumdb --full bar
vacuumdb: vacuuming database "bar"
% psql -U postgres -c "REINDEX DATABASE bar;" bar
REINDEX
% psql -U postgres -c '\x' -c "SELECT pg_database_size( 'foo' ),
pg_database_size( 'bar' );" template1
Expanded display is on.
-[ RECORD 1 ]----+-----------
pg_database_size | 2685817503
pg_database_size | 2685624835
However I still don't get why the size should not be the same after
such vacuum+reindex. If my brain serves me well, in this case we have
less than 0.2MB of difference, that compared to the database size
(~2.5GB) is more than acceptable. Nevertheless, I would have thought
that a restored database has been always smaller than the original
one.
Luca
