Обсуждение: How to Cluster an Index live?
Hi, How can I have my table clustered as records are being insert/updated (per transaction). I tried doing so with creating an index using pgAdmin III, and it generated the following SQL: CREATE INDEX someindex ON mytable (m2); ALTER TABLE mytable CLUSTER ON someindex; Now, above DID NOT cluster my table and I had to run PostgreSQL's own cluster command to have it clustered. According to my db-savvy colleagues (who use SQL Server), a live cluster is a must have on a high traffic large db and they are questioning this feature of PostgreSQL. I'd appreciate comments here, Thanks, Mike
According to PostgreSQL.org TODO: CLUSTER * -Make CLUSTER preserve recently-dead tuples per MVCC requirements * Automatically maintain clustering on a table This might require some background daemon to maintain clustering during periods of low usage. It might also require tables to be only partially filled for easier reorganization. Another idea would be to create a merged heap/index data file so an index lookup would automatically access the heap data too. A third idea would be to store heap rows in hashed groups, perhaps using a user-supplied hash function. http://archives.postgresql.org/pgsql-performance/2004-08/msg00349.php * %Add default clustering to system tables To do this, determine the ideal cluster index for each system table and set the cluster setting during initdb. * %Add VERBOSE option to report tables as they are processed, like VACUUM VERBOSE * -Add more logical syntax CLUSTER table USING index; support current syntax for backward compatibility The question is when these todo items are going to get addressed.
Look for it in 8.3: http://archives.postgresql.org/pgsql-patches/2006-08/msg00124.php
"Mike" <akiany@gmail.com> writes: > Look for it in 8.3: > http://archives.postgresql.org/pgsql-patches/2006-08/msg00124.php What he's looking for isn't actually included in that todo list. I'm sorry but nobody has been looking at an online cluster command. Clustered tables of the type you're imagining aren't really supported in Postgres at all. What Postgres does is reorder the table in place but the index is still stored separately. The patch you refer to here would help keep the table in order as updates and inserts happen which doesn't currently happen. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com