3 ms·
Hot damn, looks like CLUSTER is exactly what I need to handle a pending performance problem! I'd searched for this before, but not knowing the keywords just en
by Izkata 6y ago
Hot damn, looks like CLUSTER is exactly what I need to handle a pending performance problem! I'd searched for this before, but not knowing the keywords just ended up with people saying "add an index", which was not useful:
We have a table of denormalized data that's used as one of the inputs to an ETL system, and it has to be sorted by one of the columns in the table. This is the table's only purpose. Unfortunately, postgres refuses to use the index because random-access across the entire table is slower than loading and sorting it all, when retrieving all data in the table. This means the ETL system has to wait for about 15 minutes before any data is returned.
Some preliminary tests on a copy of the database indicate that, using CLUSTER to order the data, it will do an index scan instead of a the table scan+sort, and start returning data immediately. As long as the correlation remains high enough with regular updates, I'm thinking maybe this can be a scheduled once-a-week operation...
- durkie 6y agoHighly recommended to check out the pg_repack extension for this situation. CLUSTER requires an access exclusive lock, preventing anything from reading or writing the table during the cluster operation. pg_repack works around this by creating new tables with your data, clustering those, recording/replaying all of the transactions that happened on the original tables during that time, and then renaming everything (something along those lines). Very noticeable speed improvement and reduction in read loads when I did it last.
- Izkata 6y agoIn my tests earlier, CLUSTER only took about 5 minutes (8 if you include creating the index (which would already exist) and an ANALYZE afterwards), and the only things inserting into this table are background celery processes. It would also be offset from when ETL runs by half a day, so I don't think there'll be any performance problems. My goal for now is minimal effort to set up, so it takes minimal maintenance later on, but I'll keep pg_repack in mind in case something weird does happen.