News for hackers. Stuff that matters.
Re:Harder, Better, Faster, Stronger: New Features in Postgre... by nimbix on Friday April 24, 2009 @12:33PM (#577724)
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. :)
Re:Harder, Better, Faster, Stronger: New Features in Postgre... by thesethings on Friday April 24, 2009 @11:33AM (#577628)
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)
Re:Harder, Better, Faster, Stronger: New Features in Postgre... by dr_strangelove on Friday April 24, 2009 @12:53PM (#577761)
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...

Re:Harder, Better, Faster, Stronger: New Features in Postgre... by sachmanb on Friday April 24, 2009 @12:09PM (#577681)
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.

Re:Harder, Better, Faster, Stronger: New Features in Postgre... by nimbix on Friday April 24, 2009 @12:27PM (#577709)
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.
Re:Harder, Better, Faster, Stronger: New Features in Postgre... by dr_strangelove on Friday April 24, 2009 @12:24PM (#577702)
You don't need VACUUM FULL. If you have problems with too much disk space used by your tables, increase the autovacuum frequency.
Re:Harder, Better, Faster, Stronger: New Features in Postgre... by sachmanb on Friday April 24, 2009 @01:28PM (#577817)
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.
Re:Harder, Better, Faster, Stronger: New Features in Postgre... by chrisbolt on Friday April 24, 2009 @12:42PM (#577743)
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?
Re:Harder, Better, Faster, Stronger: New Features in Postgre... by nimbix on Friday April 24, 2009 @12:52PM (#577757)
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.

Re:Harder, Better, Faster, Stronger: New Features in Postgre... by boundlessdreamz on Saturday April 25, 2009 @01:54AM (#578671)
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.