How to optimize PostgreSQL database size

PostgreSQL is a powerful, open source relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness.

One of the PostgreSQL's most sophisticated features is so called Multi-Version Concurrency Control (MVCC), a standard technique for avoiding conflicts between reads and writes of the same object in database. MVCC guarantees that each transaction sees a consistent view of the database by reading non-current data for objects modified by concurrent transactions. Thanks to MVCC, PostgreSQL has great scalability, a robust hot backup tool and many other nice features comparable to the most advanced commercial databases.

Unfortunately, there is one downside to MVCC, the databases tend to grow over time and sometimes it can be a problem. In recent versions of PostgreSQL there is a separate server process called the autovacuum daemon (pg_autovacuum), whose purpose is to keep the database size reasonable. It does that by trying to recover reusable chunks of the database files. Still, there are many scenarios that will force the database to grow, even if the amount of the useful data in it doesn't really change. That happens typically if you have lots of UPDATE and/or DELETE statements in the applications that are using the database.

Mechanisms for recovering used space are well documented, and I decided to do a little test to see how much space can actually be recovered. Unfortunately, all of the procedures you'll find in this article demand that database is taken offline during their run. But, that's the price you'll have to pay to get your disk space back.

Test setup

For this interesting test I have used a real database that was taken from a great commercial antispam/antivirus product. Original size of the database was 5876 MB. The database was running for few weeks before I decided to take its database for "academic" purposes. The tests were run on a quite old (dual PIII), but still capable machine (mostly thanks to a 6 SCSI 10krpm disk RAID6 array). The PostgreSQL database under test was a slightly older one, version 7.4.8, because that is what the product is shipping currently. The pg_autovacuum daemon was running all the time while the database was in production (keeping metadata information about lots of quarantined spam and/or virus email messages, you probably wanted to know :)).

Vacuuming

Standard procedure for getting back the space is the VACUUM FULL command. While plain VACUUM (without FULL) simply reclaims space and makes it available for re-use, VACUUM FULL does more extensive processing, including compacting the database files to the minimum number of disk blocks. The documentation clearly says that this form is much slower and requires an exclusive lock on each table while it is being processed.

So, running it in the form of "vacuumdb -afvz" command, which covered the whole database and also generated some useful statistics, revealed that it indeed is a slow process. It took full 3 hours for the command to finish. The resulting database was occupying 4278 MB database (a 27% reduction in database size). During the run, vacuumdb was heavy on both CPU and disk.

Reindexing

Next recommended procedure is reindexing the whole database, because indexes can be even tougher when it comes to reusing the old blocks. I was scared that reindexing would took more time than vacuuming, but it finished in only 9 minutes. What was even more surprising is that reindexing the database (using reindexdb command) saved another 39% of the initial database size!

It looks like reindexdb is much faster and more efficient about getting the disk space back, at least on this test database. The final database size was 1990 MB, only 34% of what we started with. I hope I don't need to remind that the database still holds the same data, it's just physical disk usage that has dropped three times after slightly more than 3 hours of vacuuming and reindexing. Reindexing was mostly CPU bound, so it would be even faster on a modern machine with faster processors.

Take 2: dump and restore

Then I decided to do a completely different test. I reverted the test database to the initial state, full 5876 MB, and then proceeded to dump it, with the idea of clean restore after that, of course. That is a standard procedure when you're upgrading a PostgreSQL database from one major version to another (for example: 7.4 to 8.0). Dumping the database (with a standard pg_dump command) took only 3 and half minutes, which came as a pleasant surprise. After that I dropped the database (with dropdb) and recreated it (createdb) with the same encoding and owner and used the standard psql utility to import the data back which took another 16 minutes.

If you compare this times to the former ones, you'll see that this procedure took only around 20 minutes which is almost 10 times faster! But, best part is yet to come. The final database size was only 1685 MB which is less than what we achieved with the vacuumdb/reindexdb procedure. Once again, the useful data in the database is identical to the data we initially had.

Conclusion

If you have read all the details above closely, you now know that standard PostgreSQL recipe of running VACUUM FULL in regular intervals (typically overnight or on weekends) might not be the best choice. If reloading database (dump/restore procedure) does the job 10 faster and with a better final result then maybe that is what you should do, no matter how ugly it looks. But, of course, before you make final decision, you should run tests on your own database to see what's best for you.

Also it can be concluded that the PostgreSQL database could use some improvements in this specific area of disk space management. I know that disk space is quite cheap nowadays, but I'm also pretty sure that oversized database is not only taking more space, but is also somewhat slower because it has to keep track about more data on disk (think disk caches, RAM...).

In the end, it would be interesting to hear what have other people observed on various database and hardware setups out there.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Non-invasive Database management options?

HI, I know this blog is quite dated, but i wonder whether there are alternatives to the Vacuumdb, reindexdb, and dump/restore options that will be usable without taking the database offline?

Any possiblity of adding the -f Flag to the AutoVacuum function?

Looking to retain the live function of a log database without taking it offline for the time necessary to reduce or compact the database

Jay

Reindexing

I seem to be able to do reindexing without taking the database offline with no side effects. For me this is the method of choice so far.

dump/restore bloat

We had a database that was about 120GB in total on the filesystem. After a dump/restore it brought it down to 88G.
We also noticed that the load average on the box came down drastically.
All in all it seemed to be good for the database. I'm going to write some scripts that will vacuum and reindex on the database, its a little scary to dump and restore a DB ;)

We had the database running for about 6 months on 8.2

Options of the commands you used

Hi,

Thank you for this very interresting post!
I think I have the same "commercial antispam/antivirus" product that use Postgres ;) and I'm also interrested to decrease the "on disk" size...
I would be interrested to have all the commands you used to dump and restore the database.. (I mean all the options you passed to pg_dump, createdb ...) so if you have the logs of the commands you use, I will take less risks than removing the database by myself with my own untested options... :)
(pretty scary to delete my database ;) )
Thank you

Short tutorial

Sure, here it goes:

  1. log in as pmx - we'll do everything as pmx, superuser privileges are not needed.
  2. pmx-database stop - we need to stop database before the next step, to capture consistent data.
  3. cp -av ~pmx/../postgres /somewhere - this is the most important step of all, so you need to do it carefully. If anything goes wrong with the following steps, you'll always be able to recover your database from the backup we made by doing this recursive copy.
  4. edit pg_hba.conf - you need to remove all lines that allow access through the IPv4 sockets. That's because we'll need to have database up & running to made a dump of it, but we must not allow other services to use it (change the data in it) and thus make the dump/reload step inconsistent.
  5. pmx-database start - start the database.
  6. pmx-database dump dumpfile - dump the contents of the database to dumpfile.
  7. dropdb pmx_quarantine - drop the old database.
  8. createdb pmx_quarantine - recreate the database. It will be empty after this step.
  9. pmx-database import dumpfile - finally, with this command we're importing the old data, but after this command finishes, the database will be compacted and will take much less space on disk. This will also have positive effect on the performance of the whole system.
  10. pmx-database stop - stop the database to reconfigure it once again.
  11. restore original pg_hba.conf - allow all users of the database to connect.
  12. pmx-database start - start it.
  13. (optional) pmx-httpd restart - if you encounter problems accessing your quarantine, restart the EUWI service and everything will work once again.

To provide you with this list, I have run all this steps on our production database and it compacted the database from 7.7GB to 3.7GB. If anybody thinks that's not much, let me remind you that the final database fits in the RAM installed in our server, while the 7.7GB database did not. That means an order of magnitude faster response (RAM is much faster than disk).

All the steps together took about half an hour, so that you know for how long your quarantine will be unavailable to your users. Of course, this time depends wildly on the size of your database, CPU power of your server and above all performance of the disk(s) (array) you have installed. So it could be anywhere from a few minutes (for a small install) to a few hours (if anybody runs this software for a really large userbase on a powerful server).

If anybody goes through all this pain, please leave a comment here. I'm really curious to know how many people are prepared to do tricky tasks like this. Thanks.

Sry, beginner question

Hi,

I'm not sure how to use your tutorial on a windows machine. I'm also not very experienced in database maintenance via shell or linux..

A few questions:

1) What means pmx? Is it the database main user? Still used pgadmin to connect as su to the database. I think my database administrator name is probably postgres and the pw to access the passwort, which I need to type in when I use pgadmin?
2) 3)Could you please make an example for windows dumbos?
3) What am I need to type in when I'm not logged in as pmx? For example in line 5),6),7),8) ... 7) dropdb pmx_quarantine ... When I'm not logged in as pmx, am I need to replace pmx with postgres for my case?

Hope to get some answers. Need to purge some rows and columns and the full vacuum took very very long last time....

Thx in advance!!

pmx is a commercial product,

pmx is a commercial product, so you should use instructions from the article. Using windows shouldn't be too different, PostgreSQL is one and only. :)

If you want to try the reloading, you need to properly do only 3 commands (be careful!):

- pg_dump > db.dump (this dumps db data in ascii format)
- dropdb (this deletes data!!!)
- psql -f db.dump (this reloads saved data back to database)

Those commands are supposed to be run from UNIX shell, I don't know how different it is on Windows.

I'd advice you to test on an unimportant database, first. By using that procedure you put your data at great risk, you might lose it all if you do a wrong step!

Postgres compression

Excellent tutorial. Step by step all the way. I followed your advice (not exactly to the letter) and it produced more than satisfactory results.

Also, thanks for the post since I mostly have to support mysql db's and am pretty shaky at this one.

Good stuff :)

, Mike

Avoid having to close entire database

Just stumpled upon this great article.
Been having the same problem with our postgresql installation.
On our system we have some databases which cannot be taken offline, so to avoid any data updates during the dump I just rename the database before doing the dump.

alter database seo rename to seo_offline;
dump
restore
alter database seo_offline rename to seo;

Just an idea which may help others.

regards,
Claus Larsen

Re: Short tutorial

Hello,

Thank you very much for these informations...
In the same time I asked you for commands, I also asked to the software editor to know if they have also recommended informations about this operation... And they answered.
The commands are nearly the same...

Note that they gave me 2 additionals commands to run before the export (for our product):
psql -qc 'truncate quarantine_rule_hit'
psql -qc 'truncate message_body'

It seems that some "body message" informations are also in the database (but I don't know why because they are also into files?)

Now some informations after the database reducing operations:
- At the beginning the database use 33GB (Yes I have a lot of spams)
- The "truncate" was done in a few seconds
- The size after the simple truncate is now 16GB (probably a lot of "body" unused informations)
- The dump of the database was done in 9 minutes.
- The dump size is 767MB
- The dropdb and createdb were done in a few seconds.
- The restore was done in 53minutes
- The new postgres size is now 9.7GB

So all the process was done in about one hour :)

I can forward to you the email send by the Editor if you are interrested... (just send me an email.)

Thank you very much for your help!! :)

More commands from the Shadowy MTA company

Here's some more advice given to me by the aforementioned shadowy anti-spam MTA company:

If you run the following commands as the pmx user, you will free up a large amount of space in your database (>=PureMessage 5.3, if less, omit autovac_state delete):

export PGDATABASE=pmx_quarantine
PATH=/opt/pmx/postgres/bin:$PATH

psql -qc "truncate quarantine_rule_hit;"
psql -qc "delete from autovac_state where name ~ 'public.quarantine_rule_hit';"

psql -qc "truncate prd_relay;"
psql -qc "delete from autovac_state where name ~ 'public.prd_relay';"

psql -qc "truncate prd_sender;"
psql -qc "delete from autovac_state where name ~ 'public.prd_sender';"

psql -qc "reindex database pmx_quarantine;"

The quarantine_rule_hit table is only used in advanced searches in the field "Spam Hit".

The table prd_relay is the relay information for all messages. This table can become large.

Re: Short tutorial

Great web site. Just what I needed. I am also using the same anti-spam/anti-virus product (is there a reason why we avoid the name here?).

I have long been suffering from the database using up way more disk space than what it should. This page inspired me to try the export/drop/create/import routine. But, I am lucky to have a spare database host (there for disaster recovery purposes), so before trying this on my working database host, I first tried importing a dump created in my working database host into the disaster recovery host. During this process I found a couple of things that need changing in the procedures (at least for my environment).

First the Solaris "cp" command does not support the "-a" or "-v" options, so if you're using Solaris you may have to copy the ~pmx/../postgres tree using cpio. If you have the gnu version of cp that would work. If you have rsync (as I do), then that will work. I did:

mkdir /d3/postgres-backup
rsync -va --copy-dirlinks --progress ~pmx/../postgres/ /d3/postgres-backup

(I needed that --copy-dirlinks option in there because I have symlinked the postgres/var directory to a different file system. To copy back I would have to use the --keep-dirlinks option).

Now, I did this just yesterday afternoon, leaving an "import" running when I went home. I came in this morning and found the import is still going! The file I'm importing from is less than 4Gb, so I really think it shouldn't take this long. Then I looked in my postgres log and see that there are scheduled jobs running which make use of the database. Yes, I commented out all the "host" lines in my pg_hba.conf file, but I didn't comment out the "local" line, so the localhost is still doing its scheduled jobs, which I'm guessing, are locking tables and holding up the import.

Note that I'm relatively new to databases and this postgres is the first database I've ever had to maintain.

For those new to maintaining postgres, just be sure you also comment out the "local" line in the pge_hba.conf file and/or disable the scheduler or the individual scheduler jobs that access the database.

I'll try to get back to this another day. I have to get to other work now.

Thanks for this site.

Marnix

dada

dada

Names, names...

> is there a reason why we avoid the name here?

Sure. They haven't paid neither for advertisement, nor for consultancy work. ;)

Re: Short tutorial (followup to my previous comments)

Thought I'd better followup to my previous comments. You need to leave the "local" line uncommented in the pg_hba.conf file. Sorry. I'm a bit new at this database stuff. What I do think you need to do though is to turn off the scheduler that is running jobs which access the database. Either turn off the scheduler or turn off the individual jobs. I'm not 100% sure of this, but I believe it's what made the import not finish even after 13 hours. I finally aborted it and am now retrying with the scheduler turned off.

Marnix

Very interesting info

Thank you for the very interesting additional data about the whole process. Yes, I would very much like to see what "professionals" say on the subject, you can use the email address that is at the bottom of the page, thank you!

I see that PostgreSQL 8.2 is in beta, and it's ChangeLog promises improvements in the vacuum efficiency. Let's hope it'll improve performance of installations and workloads like ours a little bit. Of course, we need to wait for our beloved product to finally upgrade PostgreSQL from the ancient 7.4.8 to something newer. I don't understand why that hasn't happened yet. Maybe they are waiting for 8.2? ;)

The final test

OK, before I delete the test database, I have yet another test results to share with you. Looking at some comments posted on the other forums that carried link to this article, it came to my mind that I could have also tested vacuumdb/reindexdb combo in reverse. That is, first reindexdb then vacuumdb. So without further ado, these are the results:

  • reindexdb took slightly less than 10 minutes and the database size went down to 3596 MB after it's run
  • vacuumdb took 1 hour, 25 minutes and shrinked the database to a final 2526 MB

Interesting, when commands are run in this order you get worse result, but on the other hand, the process is about two times faster.

That's the moment when I scratched my head and said, but hey, at least reindexdb is fast, let's run it once again!

  • reindexdb (for the second time) took 8 and half minutes and got the database down to the same 1990 MB that we got when we run vacuumdb and then reindexdb

Now, say that's not interesting data. :)

Let's summarize all those numbers:

vacuumdb/reindexdb:           190min - 1990MB
reindexdb/vacuumdb/reindexdb: 105min - 1990MB
dump/dropdb/createdb/restore:  20min - 1685MB

I believe you now have more than enough data to make your own conclusions.

A good steady state size

What you're shooting for isn't the smallest possible foot print. While it may seem counterintuitive, the database will run fastest when it has a bit of free space internally, which allows it to update without constantly reallocating disk space.

Now, if you aren't properly vacuuming the database from it's initial startup, and it's grown far too bloated, then you should run a vacuum full.

However, if the database is kept properly vacuumed, then a full vacuum should not be necessary.

Reindexing, by the way, is not transaction safe. It is theoretically possible for it to fail in such a way that the index you are recreating will be lost. Not likely, not common, but reindex is NOT a normal every day maintenance command, but more of a "something's wrong, better fix it" command. However, it's ability to shrink indexes better than vacuum has led to it becoming a common tool for database maintenance.

What I'm trying to say is that what you REALLY wanna do is have the Free Space Map and auto_vacuum settings such that your database settles at about 20% or so free space and never needs a full vacuum or reindex.

Certain usage patterns make that nearly impossible, but it's kinda the goal.

Agreed, but...

I agree with you on everything you said. But, I must also add that probably the biggest truth is hidden in your last sentence. :)

Yes, it seems that sometimes (but quite often) it's so damn hard to keep the database from filling your disk completely. Let's do a little math: (5876 - 1685) / 5876 * 100 = 71%. That's how much free space I had when I started to investigate the situation because there were some performance problems with that database. And that is with the pg_autovacuum daemon running at all times and I don't recall that the database was restarted even once in the two weeks period from it's initialization.

But, still, Mischa has some very strong points! Here's why, in the 7 days following my tests (I had to restart the database to take the snapshot for the tests this article is about) that same database in production has grown to a massive 8290 MB!

So last night I finally took the oportunity to do the real thing, on the production database. Because picture speaks a thousand words, look at the first one. Do you believe that it's the same database, same data, left and right?

PostgreSQL size hourlyPostgreSQL size hourly

Now for the second one. As I already said, all this tests were conducted because we had some performance problems with that server. After it become obvious that the database is the culprit (other tasks are running on the server) I got an idea that maybe full vacuuming of the database would give the server some air to breathe. The following graph clearly shows that the results are possibly better than I expected:

PostgreSQL CPU hourlyPostgreSQL CPU hourly

I hope it's pretty obvious how CPU wait time (defined as the time that CPU could have been doing useful work, but instead waited for disks to provide data) dramatically dropped at around 2am when the dump/restore procedure was finished. Not only that, on the left side is Sunday, the day that brings less load to the servers worldwide. :) All the spikes that you can see in the middle of the graph are mostly preparation tasks (backups and more backups, everything by the book).

And there's yet another thing to note. From 7pm to approx. 9:30pm I conducted a live (the database was in production) "vacuum verbose analyze". It obviously took almost 2,5 hours to finish and was really heavy on the disks. You can see what happened when I ran that very same command at 2:45pm next day. A brief spike. Seems like it didn't touch disk at all, just used some CPU.

Let me finish this comment with my own conclusion that PostgreSQL database still needs some work in this area. In the ideal world, database admin shouldn't need to worry about this quite low level tasks, let alone that he has to dump/reload the database every so and then to recover disk space and performance. The administrator should have only a few high level knobs, for example one could be called "keep_free_space_pct" and I would put 20% in it. As you can see I agree with your valuation. :)

What tools did you use for performance / graph?

Hi, just curious about what tools did you use to get the statistics for pg size and cpu load and to plot them.

Standard RRD graphing. Check

Standard RRD graphing. Check this page: http://oss.oetiker.ch/rrdtool/

Hurrah! Postgres 8.1 has

Hurrah! Postgres 8.1 has made "autovacuum" into a server process, and we'll all be able to stop "managing" it as user-tuned add-on.

But what about users with 7.4, or 8.0? What do they do when the standalone "pg_autovacuum" just doesn't seem to be doing its job?

The standalone "pg_autovacuum" has two features that _sometimes_ conspire to make it, well, less than useful:

* if it can't connect to the server, it exits
* all its trigger threshold info is (only) in memory

It's easy to make restart it, but each time it restarts, it has lost track of the rows changed per table since it last vacuumed.

So, if you have a database server that goes down regularily, that's a real problem (There are other reasons why "pg_autovacuum" might be blocked and bail, but they all have the same effect).

How often is "regularily"? Given default parameters, tables are vacuumed when the total number of rows updated and deleted is twice the number of rows in the table. For a large table, that might be a very long time ... days, or even weeks. An outage every weekend, and the table NEVER will be vacuumed.

The simple but hard-on-the-system solution is, vacuum the database just before you (re)start "pg_autovacuum". Ouch.

A better solution is to use the output of the ANALYZE command, to drive vacuuming. ANALYZE reports the number of "live" and "dead" rows per table; VACUUM removes "dead" rows. As it happens, these are the numbers that the 8.1 server vacuum process uses.

ANALYZE is much faster than VACUUM, and should be done more frequently, anyway. "pg_autovacuum" runs ANALYZE itself, 2-3 times as often as VACUUM.

Here's a task you can run once an hour, or more, to ensure every table is no more than half-dead: This job is more overhead than "pg_autovacuum", but it's a good start to the more refined solution: an autovac script that saves its state in the database.

psql -qc "ANALYZE VERBOSE" 2>&1 \
| awk '$2 == "analyzing" { gsub(/"/,""); table = $3 }
$14 == "dead" && $9 < $13 { print ";VACUUM",table }' \
| psql

http://cvs.distributed.net/

http://cvs.distributed.net/viewvc.cgi/stats-sql/tools/ has some scripts that will help keep pg_autovacuum running.

Also, the default vacuum thresholds are far, far to conservative; they will let a database grow to 3x it's original size before vacuuming! The parameters in that script set more reasonable values.

Even the autovac settings in 8.1 are pretty conservative; I typically cut them in half.

Right on topic

After I have seen all the abovementioned problems in production, I tend to agree with you. pg_autovacuum is not the slickiest software around. It helps, but is not bullet proof and won't work in all environments.

That simple script you're suggesting is a very interesting approach and I believe it would work very well. Unfortunately, the format of "analyze verbose" output is different on 7.4.8 (no "dead rows" count) so it is applicable only to the newer versions of PostgreSQL. But thanks for your contribution anyway, it seems that you had your share of debugging, too, and learned lots of useful stuff from it.