4 ms·
Where do things stand with the status of vacuum in Redshift which derived from Postgres? What version of postgres vacuum implementation are they derived from?
by gregw2 9y ago
Where do things stand with the status of vacuum in Redshift which derived from Postgres?
What version of postgres vacuum implementation are they derived from? Have they implemented or incorporated any of these improvements since their fork? (Obviously Redshift doesn't need the btree index ones since it uses distribution keys and zone maps.)
I ask because my experience is that Redshift vacuum implementation is awful on large heavily used data warehouses, and I wonder if there is any hope for improvement.
- bigtones 9y agoAmazon Redshift is not actually based in Postgres, it just supports a Postgres interface. It is built on top of technology from the massive parallel processing (MPP) data-warehouse company ParAccel which was acquired by Actian, and licenced by Amazon.
- anarazel 9y agoWhich in turn initially was based on postgres. But there indeed isn't much in common anymore...
- Ralfp 9y ago> Amazon Redshift is based on PostgreSQL 8.0.2 Same wikipedia article you are quoting ;) https://en.wikipedia.org/wiki/Amazon_Redshift https://en.wikipedia.org/wiki/Amazon_Redshift
- scapecast 9y agoI can confirm that Redshift is based on Postgres. And I know that straight from the source: Dave Steinhoff, one of the founders of ParAccel, is on our team. https://www.intermix.io/about-us/ https://www.intermix.io/about-us/
- blaisio 9y agoVacuum in Redshift is doing something entirely different from vacuum in Postgres. You really need to look at the documentation.
- scapecast 9y agoYeah, true. Even though Redshift is based on Postgres, it does not leverage the automatic vacuuming facility of postgres. So the vacuuming of tables to recover disk space from deleted rows needs to be done manually. There are 3 types of vacuuming in Redshift (DELETE ONLY, SORT, REINDEX). The vacuum SORT operation is done on tables that have a sort key. To the extent that a vacuum SORT is an expensive (high IO) operation, we recommend when possible, to avoid the need to vacuum by loading the rows in sort order. If you do that, you will not need to vacuum the table, and this is the optimal solution for very long tables. When dealing with a very long table (>10b rows) that has a sort key, it’s useful to partition the table into multiple tables where the table names are modified to indicate the partition. This is useful when pruning the size of the table, you don’t have to run VACUUM DELETE ONLY. The key is to vacuum on a regular basis, and keep your stats off below 10%. If you wait too long, you might end up in a place where you have to resize your cluster or do a deep copy. One more thing: Run your vacuum jobs in the queue with the most memory.