Posgresql Optimization: GIN indexes and Cluster Operation

GIN Indexes

In the regular index structure, a single key is normally associated with either one or multiple values, so finding the desired index entries are relatively simple. But the structure Generalized Inverted Index (GIN) holds is different.

For GIN index, a value can have multiple key associated with it. Like GiST, user-defined indexing strategies can be implemented on GIN. It is also a way of implementing full-text search.

Cluster operation

In Postgres database, reading records randomly from disk is normally considered 4 times expensive than sequence disk read. If records in the database table are randomly distributed, retrieving more than one record will take longer time because of random data access.
Clustering a database table using an index will reorganize the order of data records according to the index order, which in turn will reduce the number of random disk access and speed up the process.

The approach Postgres has taken for clustering is different from the SQL Server and DB2, there is no additional penalty during transactions of having a clustered or clustering index, it does not try to maintain the physical order of records and all new data goes to the end of the table.
CLUSTER is not a one-time operation; one has to run CLUSTER every now and then to reorganize the order of records. After the first time of clustering, Postgres will remember which index it was clustered by.
During the process of clustering, an exclusive lock is acquired on the table, which will block other transaction from accessing. It is also a time consuming process depending on the data size it holds and the workload of the server.

So it is recommended to run during evenings and weekend when others will not be disturbed.
Also remember to run ANALYZE after clustering to update the database statistics.

In the next article, we'll consider another two important optimization techniques: Bitmap index scan and Bitmap heap scan