I am seeing performance issues with frequently updated / inserts table, and one of my hypothesis is the gin index I have on int array.
The gin index was added since we have query of "X exists in array?" in high profile endpoint.
I created a one-time replica of the production DB and removed the index and replaced it with GIST and gist__int_ops to check it's performance (since it should work better on frequently updated /inserts table).
The gist index creation never finishes, I let it run for hours and no results on the other side, gin index creation takes few minutes.
I tried cleaning the data - I had arrays with 50 to 120 items, and I removed them and now all of my data is an array with one element and still, index creation is slow.
DB: PostgreSQL 11.5 on RDS (db.m4.4xlarge) Table size: 9373 MB
Any ideas what I can do here?