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