2

I am trying to cluster my table faster. The table is 27 GB worth of data, and has one index using a point geometry which is 19 GB in size. I am seeing about 5.5 hours to cluster the table.

I am assuming clustering is a IO heavy process, so anything to improve read/write times (new disk) would be my first thought, but are there any other things that could improve the time it takes to cluster a table?

I ran this test on a machine with an SSD and 12 GB of RAM, using postgresql 9.4

3
  • 1
    Did you try to increase maintenance_work_mem and/or work_mem (not entirely sure which one is used by the cluster command)
    – user1822
    Nov 30, 2015 at 16:11
  • I haven't tried increasing those, but I do notice the the memory usage is sitting at about 50%. I'll run some tests and update accordingly. Thanks.
    – user81658
    Nov 30, 2015 at 16:24
  • 1
    @a_horse_with_no_name I ran some tests tweaking maintenance_work_mem. Set at 256MB, I was able to squeeze about 13% improvement. Increasing any further, had diminishing returns for my particular setup. I am open to other options, but this did answer my original question. If you want to post as an answer.
    – user81658
    Dec 1, 2015 at 17:22

1 Answer 1

4

I found an interesting solution which you might be interested in. Basically, it involves creating a temporary clone of the table, where the data is copied into (this does the clustering). This will keep your original table available. After the insert is completed; you use the copy as new source; and discard the original (unclustered) table.

The table is partitioned into three child tables: static1, static2, and latest. The parent table has a rule that redirects all inserts into the "latest" partition. This serves as the data collection point, where new rows sit until they are migrated into one of the other two partitions. The "static1" and "static2" partitions serve as front and back buffers for the cluster operation. Only one partition will actually have any rows at any given time (the front buffer); the other will be empty (the back buffer).

You can also have a look at pg_repack; which claims to provide an alternative to VACUUM FULL without the exclusive lock. This plugin basically does the same as the blog above does manually:

To perform full table repacks, pg_repack creates a work table in the "repack" schema and sorts the rows in this table. Then, it updates the system catalogs directly to swap the work table and the original one.

Alternatively, playing with maintenance_work_mem might be a simple fix; increasing that might be enough.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.