Any drawbacks of using GIN PostgreSQL index for an integer foreign key?

Question

I have a large table (600 millions rows) with a foreign key other_id of type integer. A single value of the foreign key is repeated about 100 times on average. I need to have an index on that FK column because the data is frequently selected by other_id.

My tests show that the gin index type is about 10 times smaller and about 3 times as performant as the default btree index (the performance was tested using SELECT queries).

The question is are there any real-world drawbacks of using the gin index instead of the btree index? It looks like this index type isn't used much for a very common case like mine, i.e. an integer foreign key. But my tests show vast performance gains. Why then gin isn't recommended for such scenarios?

I had to execute CREATE EXTENSION btree_gin to be able to use the gin index for the integer column.

I know about the UPDATE being possibly slow due to FASTUPDATE being enabled by default: Occasional/intermittent, slow (10+-second) UPDATE queries on PostgreSQL table with GIN index

I care only about the equality = operator being able to use the index (also, IN (...) with a possibly large number of values but I assume this is also equality).

Answer 1

This seems like a rather esoteric thing, which is probably why it doesn't get recommended all that much. I do recommend it when the issue comes up, which just isn't all that often. The size of one index is unlikely to be all that meaningful in the context of an entire database, so making it smaller usually isn't worth worrying about a great deal.

I don't think there are any downsides which are specific to this being a foreign key. The automatically-generated queries that are used to maintain the many-valued side of the constraint can use the GIN index just as well as the Btree index.

I'd turn "fastupdate" to off, unless you do relevant benchmarking which shows you want it on. Since the index is over a scalar using "btree_gin", you don't get the explosion in index page writes for each inserted row, like you do with a normal GIN index, so the need for fastupdate is less.

Since this index type is much less used than regular btree indexes, there are more likely to be undiscovered bugs lurking in it (especially if fastpdate = on). I wouldn't (and don't) let that put me off from using it if it really provided me with something of value, but it keeps me from just blindly using "btree_gin" everywhere I conceivably could.

One thing I've noticed is that the replay of updates/inserts to GIN indexes seem pretty slow (compared to what I naively expected), which could be relevant for PITR, recovery, or streaming replication. If any of those things are important to you, make sure you include them in your test.

Answer 2

gin index type is about 10 times smaller and about 3 times as performant as the default btree index (the performance was tested using SELECT queries).

and:

it's 30GB for btree and 3GB for gin

Yes, that speaks heavily in favor of the GIN index.

OTOH, this:

repeated about 100 times on average.

and this:

I think I won't ever UPDATE the other_id column, but I can INSERT many rows. Also, I might issue UPDATE statements to the rows which will SET the same other_id values.

Seems to indicate that much of your table is sorted physically in regard to other_id, or at least with clusters of the same other_id. If so, and/or you don't update too many rows out of order and can insert the "many rows" you mentioned sorted by other_id and/or can afford to CLUSTER your table once in a while (or do the same with pg_repack to allow concurrent write access), then consider a block range index (BRIN) instead, which should be much smaller, yet:

CREATE INDEX tbl_other_id_brin_idx ON tbl USING brin (other_id)

The manual:

pages_per_range

Defines the number of table blocks that make up one block range for each entry of a BRIN index (see Section 67.1 for more details). The default is 128.

It might pay to experiment with a smaller pages_per_range setting in your case, like:

CREATE INDEX tbl_other_id_brin_idx ON tbl USING brin (other_id)
WITH (pages_per_range = 8);

That setting as well as index size and performance largely depend on physical clustering, row size, write patterns and typical queries. While typically slower than a B-tree index it might work well for your case.

Related:

• Speed up creation of Postgres partial index

Answer 3

The extension you need is btree_gin and not pg_trgm.

I would say that the biggest drawback is that GIN indexes are slower to update than B-tree indexes (which is why the fastupdate option was invented).

If that is fine with you because you have few data modifications, and query speed is more important, use the GIN index (but then I would recommend disabling fastupdate because it can slow down queries).

You might want to benchmark data modification speed to get an idea how it will perform.