PostgreSQL database size is less after backup/load on Heroku

Question

Recently I created a new Heroku app for production and populated it's database with a backup that I took from the staging database.

The problem is that the database size, as shown on Heroku's Postgres webpage for the two databases is different!

The first database, where I took the backup from was 360 MBs and the new database that was populated was only 290 MBs.

No errors showed up during the backup/load process. and taking a backup from the two databases results in the same backup file size (around 40 MBs).

The project is working fine, and the two apps look exactly the same, but I'm concerned that I might have lost some data that would cause troubles in the future.

More info: I'm using the same production database plan on both Apps.

Also, the first database is not attached to the first instance (because it was added from the Postgres management page, not from the App's resources page) and the new database is attached to the new App.

Thanks in advance

Ihor Romanchenko · Accepted Answer · 2012-11-22 08:32:07Z

8

It is ok for postgresql DB to consume more space when in use.

The reason of this is its MVCC system. Every time you UPDATE any record in a database it creates another "version" of this record instead of rewriting the previous. This "outdated" records will be deleted by VACUUM process, when there will be no need in them.

So, when you restored your db from backup, it didn't have any "dead" records and its size was less.

Details here http://www.postgresql.org/docs/current/static/mvcc.html and http://www.postgresql.org/docs/current/static/sql-vacuum.html.

P.S. You do not need to worry about it. Postgresql will handle VACUUM automatically.

answered Nov 22, 2012 at 8:32

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:43:09Z

0

See if this helps: PostgreSQL database size increasing

Also try to measure the size of each table individually and for those tables where you see differences, compare counts of records: postgresql total database size not matching sum of individual table sizes

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered Nov 22, 2012 at 4:26

Dmitry Frenkel

1,79612 silver badges17 bronze badges

Collectives™ on Stack Overflow

PostgreSQL database size is less after backup/load on Heroku

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related