after restore the size of the database is increased

On 7/15/19 6:21 AM, Luca Ferrari wrote:

Hi all,
this should be trivial, but if I dump and restore the very same
database the restored one is bigger than the original one.
I did vacuumed the database foo, then dumped and restored into bar,
and the latter, even when vacuumed, remains bigger then the original
one.
No other activity was running on the cluster.

What am I missing here?

% vacuumdb --full foo
vacuumdb: vacuuming database "foo"

% pg_dump -Fd -f backup_foo.d -U postgres foo

% createdb bar
% pg_restore -Fd backup_foo.d -U postgres -d bar

% 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 | 2686571167
pg_database_size | 2690212355

% vacuumdb --full bar
vacuumdb: vacuuming database "bar"

% 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 | 2686571167
pg_database_size | 2688193183

What does \l+ show?

% psql -c 'select version();' -U postgres template1

version
-----------------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 11.3 on amd64-portbld-freebsd12.0, compiled by FreeBSD
clang version 6.0.1 (tags/RELEASE_601/final 335540) (based on LLVM
6.0.1), 64-bit
(1 row)

--
Adrian Klaver
adrian.klaver@aklaver.com

On Mon, Jul 15, 2019 at 6:22 AM Luca Ferrari <fluca1978@gmail.com> wrote:

What am I missing here?

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.

--
Peter Geoghegan

On Mon, Jul 15, 2019 at 7:07 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:

What does \l+ show?

The same as pg_size_pretty:

foo=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges | Size | Tablespace | Description
-----------+----------+-----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
bar | luca | SQL_ASCII | C | C |
| 2566 MB | pg_default |
foo | luca | SQL_ASCII | C | C |
| 2562 MB | pg_default |

foo=# SELECT pg_size_pretty( pg_database_size( 'foo' ) ) AS foo,
pg_size_pretty( pg_database_size( 'bar' ) ) AS bar;
-[ RECORD 1 ]
foo | 2562 MB
bar | 2566 MB

Luca

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

Hi all,
this should be trivial, but if I dump and restore the very same
database the restored one is bigger than the original one.
I did vacuumed the database foo, then dumped and restored into bar,
and the latter, even when vacuumed, remains bigger then the original
one.
No other activity was running on the cluster.

I can also think of toast data rearranged differently after dump-restore,
accidentally in such a way that it's packed into pages more efficiently.

Not that the probability of such a behavior is very high though.

Best regards,
  Alexey