Harder, Better, Faster, Stronger: New Features in Postgres 8.4 | Hacker News Reader

Harder, Better, Faster, Stronger: New Features in Postgres 8.4

linux-mag.com

21 pointslinuxmag10 comments

Too bad that the article completely skips these important new features:

  - Windowing Functions
  - Common Table Expressions & Recursive Joins
  - Default & Variadic parameters for functions
  - Column Permissions
  - Per-database locale settings

But do I like the Daft Punk title. :)

This was a good, technical article that focused on what's new, as advertised. Does anybody at HN have experiences with VACUUM in Postgres? (referenced in the article)

My databases in Postgresql have not exceeded approximately 10GB, so for larger databases you may want additional information.

With AutoVacuum enabled, and adding "VACUUM FULL" to the end of my nightly backup routine, I have found Postgresql to be really fast. MySQL has had some performance updates since I switched, but at the time Postgresql was performing better for me. I believe MySQL was at the first release of version 5. If there is a performance difference between Microsoft SQL and Postgresql, I'm not noticing it. I have a lot of experience with Microsoft SQL, and I can tell you that prefer Postgresql.

You don't need VACUUM FULL. If you have problems with too much disk space used by your tables, increase the autovacuum frequency.

Thanks nimbix and dr_strangelove, I'm going to take that out of my backup routine, but being that I don't like to 'remember to do things' - add it to a once every three months routine.

My experience with several 10-20GB databases shows that if you have autovacuum enabled you don't need to run vacuum full very often. We only do it once every couple of months and we're not seeing any significant table bloat, even though we're dealing with lots of updates.

To give you a background why vacuum is necessary: When you update a row in postgres, it is marked as deleted and a row with the updated contents is inserted. Likewise, when you delete a row, it is only marked as deleted but the space on disk will not automatically be reused. The vacuum process appends all these rows to the list of reusable rows.

If the autovacuum process is run too infrequently, the space used by the tables grows larger and there are many gaps between used rows. In addition to filling up more disk space than necessary this reduces sequencial scan performance. In this case you should run a vacuum full which packs the table and relases the unused space to the operating system (think of file system defragmentation). Unfortunately this is quite slow and uses table locks so you should not do it on a production system.

[EDIT:] From the awesome postgres documentation:

http://www.postgresql.org/docs/current/static/routine-vacuum...

One thing I've never liked about postgres is that for any major version upgrade (8.3 -> 8.4) you need to do a full dump and restore of all your data. How do people handle this?

We do it over the weekend. Or at night. Or both. It's not fun, but we can handle it once every year and a half.

I considered using Slony replication the last time we were upgrading to copy data between the databases, but I figured it doesn't make much difference and did a dump/restore instead.

oh! So what happens when you run apt-get upgrade or something similar ? 1. Will the upgrade fails because there are running databases? 2. Or can you dump using the new DB version and restore again ?

Upgrading mysql was a breeze.

Reply on news.ycombinator.com