4 ms·

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 ar

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)

sachmanb 18y ago
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.
- dr_strangelove 18y ago
  You don't need VACUUM FULL. If you have problems with too much disk space used by your tables, increase the autovacuum frequency.
  - sachmanb 18y ago
    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.
- nimbix 18y ago
  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.
dr_strangelove 18y ago
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-vacuuming.html http://www.postgresql.org/docs/current/static/routine-vacuum...

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)

sachmanb 18y ago

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.

dr_strangelove 18y ago
You don't need VACUUM FULL. If you have problems with too much disk space used by your tables, increase the autovacuum frequency.
- sachmanb 18y ago
  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.
nimbix 18y ago
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.

dr_strangelove 18y ago

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-vacuuming.html http://www.postgresql.org/docs/current/static/routine-vacuum...