Postgres CLUSTER on JSONB GIN index?

Question

I'm trying to evaluate using Postgres with one JSONB column vs a more traditional relational model (each fields being one column). I recently found that CLUSTERing on an index in the relational model (in this case an index on one column) helped read performance a lot, understandably.

From my understanding, running CLUSTER ON will actually reorder how the table is stored physically, based on the index specified. What I was hoping to know is, if I run CLUSTER ON , how exactly will the table be reordered? Is there any way to know before I actually execute the command, or is this more of a try-it-and-see scenarios?

If it's helpful, we are using the jsonb_path_ops operator.

Thanks!

Answer 1

I doubt you can cluster on a GIN index because there is no natural ordering in the index. Clustering physically reorders on the order of the index and gin indexes don't have an ordering on them per se.

So I would be extremely surprised if this worked. I think you would need a btree index instead.

Now, you could do a functional index on an attribute of the jsonb and cluster on that, I think.