Hi I have a table t, with just two columns, an `id SERIAL` and a `record JSONB`. I have about 3 million records where I save some JSON data in each record. Each `record` column jsonb has about 40 json key-values, of which about 10 are nested fields. Now, if I run a query, like: ``` select distinct record -> 'destinationServiceName' from t; ``` it takes about 1 minute or so to return the list of unique values for this column, to search across the 3 million records. The number of records in my setup may go up to a few tens of millions. I want these queries to be faster. I also intend to do a few more aggregation queries, like, "Give the maximum responseTime for each distinct destinationServiceName" etc. in future. I have used Elasticsearch and kibana in the past and I am planning to migrate to postgres jsonb field (not as individual columns), as vertical scaling is preferred for my case and the schema could not be fixed. I assumed that if I add more indexes, these types of queries would become faster. So I tried to create an index using the command: ``` CREATE INDEX idx_records ON t USING GIN (record); ``` But this index creation fails now as my postbird client timesout. So my questions are: 1) Is it a good idea to use postgres as a JSON database, if I have tens of millions of records ? If you run such a setup, can you share any words of advice on Dos and Don'ts ? 2) Is there a better way to do the index creation, reliably, for a table with a few million records already ? 3) There are various types of indexes. I am using GIN based on some short-term googling. Are there any good blog posts, links, tutorials, courses etc. that you have found useful in understanding the postgres jsonb performance tuning ? Thanks. -- Sankar P http://psankar.blogspot.com
