Running 10M PostgreSQL Indexes in Production

Re:Running 10M PostgreSQL Indexes in Production by joncrocks on Tuesday June 13, 2017 @06:55AM (#14543930)

Did you consider creating new tables (with appropriate, more generic indexes) vs. creating indexes for everything?

You've got to consider that indexes can be viewed as cut-down tables that you can't directly address.

I would have thought that you are going to start leaning heavily on the query optimiser/index updaters, which may well not be well designed for this kind of approach. With the caveat that it obviously seems to working now :-)

[ Parent | on HN ]

Re:Running 10M PostgreSQL Indexes in Production by malisper on Tuesday June 13, 2017 @02:50PM (#14547482)

One of the nice advantages of partial indexes is that Postgres will automatically fallback to the next best possible plan. If we created separate tables for each event, either we would have to handle the logic for falling back ourselves, or the data would be momentarily non queryable.

I have come up with a schema/set of indexes that we think would allow us to have a fixed set of indexes. The downside is we would have to do a lot of the work Postgres is currently doing ourselves.

[ Parent | on HN ]