Thread: after restore the size of the database is increased
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 % 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)
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