I did a pg_dump on a JIRA database that I was hosting in a PostgreSQL 8.3 server. Size of the database after vacuum full was 217132652 (approximately 207 MB).

Then I restored that JIRA database on a PostgreSQL 9.4 server with the following command:

$ psql -X -v ON_ERROR_STOP=1 -d jira2 -U jira -h localhost < jiradb2017_03_12.sql

I assume the restore would exit on any error since I used ON_ERROR_STOP=1, but the SQL script finished correctly (despite some warnings not related to data restore).

I ended up with a database with a size of 158019348 (approximately 151 MB).

So, what's the story here? Can I just assume database was restored successfully and PostgreSQL optimized its storage (somewhere between 8.3 and 9.4 versions) engine and is using space more efficiently?

  • 3
    Pablo, have you tried restoring to 8.3 and checking the size? The would confirm or eliminate any effect of the version cahnge Mar 12, 2017 at 20:20

1 Answer 1


When you restore a database you have all the information on it packed, with no empty space between rows (or in indices), unless some specific settings are in place (basically: FILLFACTOR for tables and FILLFACTOR for indices).

On the other hand, when your database has been in use for some time, and you've had your share of inserts, updates and deletes, free unused space will appear. This is because of the way PostgreSQL and Multiversion Concurrency Control, a.k.a. MVCC work. MVCC allows for less lockings, which basically means you save time. But you pay a price in terms of space:

  1. Every UPDATE is equivalent to an INSERT together with a DELETE, with the overhead (at least in terms of space used) associated to both.
  2. When you have several transactions running, and every one is INSERTing, UPDATEing or DELETEing, you have simultaneously several copies of every row involved.
  3. The space allocated to these row versions won't be freed immediately after commit, and for a while, will be unused space within the files where your table data (and indexes) is being stored.

Autovacuum takes care of this space being made reusable by default, or you could have some specific procedure for routine vacuuming.

This fact already can explain the size change.

Optimizations between versions probably also took place; and can explain further improvements. Optimizations could also have been made for speed and not for size, and the actual size could actually grow from one version to the next. I really don't know the specifics to be able to tell; although the comment from @Erwin states that both changes making your tables shrink and changes making your tables bloat (grow) have taken place since version 8.3.

To distinguish between the two effects, if you're curious, you could just, as @Jack Douglas suggests, restore your database on 8.3. It will most probably shrink in size. If it shrinks to less than 151 MB (a size smaller that what you get with 9.4 version), then the removal of unused space made your DB shrink, and the version change actually made your DB grow.

For a better understanding of MVCC, look at Bruce Momjian's presentation.

  • 1
    This is very much to the point. And yes, changes both shrinking and bloating basic table size have taken place since Postgres 8.3. Mar 16, 2017 at 3:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.