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...

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.