Home Postgres performance tuning
Reply: 1

Postgres performance tuning

Vadi Visuvalingam
Vadi Visuvalingam Published in 2018-01-13 06:00:35Z

Does applying cluster on a Postgres table of size billion rows improve performance?

I am attempting to find out whether applying the cluster operation will improve the query times for a table that is very large.

Vao Tsun
Vao Tsun Reply to 2018-01-13 14:42:38Z


In cases where you are accessing single rows randomly within a table, the actual order of the data in the table is unimportant. However, if you tend to access some data more than others, and there is an index that groups them together, you will benefit from using CLUSTER. If you are requesting a range of indexed values from a table, or a single indexed value that has multiple rows that match, CLUSTER will help because once the index identifies the table page for the first row that matches, all other rows that match are probably already on the same table page, and so you save disk accesses and speed up the query.

as you can see cluster will improve or not peformance, depending on how you query the table, not on its size. Billion or million - it will benefit if you use the index on which you cluster the table and you tend to use some part of data more often, then other. And you don't very intensively change the data:

When a table is clustered, it is physically reordered based on the index information. Clustering is a one-time operation: when the table is subsequently updated, the changes are not clustered. That is, no attempt is made to store new or updated rows according to their index order. (If one wishes, one can periodically recluster by issuing the command again.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.302435 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO