From: | Luca Ferrari <fluca1978(at)gmail(dot)com> |
---|---|
To: | Peter Geoghegan <pg(at)bowt(dot)ie> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: after restore the size of the database is increased |
Date: | 2019-07-16 06:27:03 |
Message-ID: | CAKoxK+6kjyRoZtxsUEU=mnobbc5Xmyc72jbkEcWOWj=VfkAuYw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Jul 15, 2019 at 7:21 PM Peter Geoghegan <pg(at)bowt(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Luca Ferrari | 2019-07-16 07:06:44 | Re: How to run a task continuously in the background |
Previous Message | Luca Ferrari | 2019-07-16 06:19:02 | Re: after restore the size of the database is increased |