Just wanted you to be aware of the work we're doing at TimescaleDB (http://www.timescale.com/), a time-series database extension for PostgreSQL.
Some of how we might help you:
- automatic partitioning by space (primary key - like country_id, for instance) and time. This creates "chunks" of your data, right-sized by volume and time constraints (which makes inserts fast at much greater scale than Vanilla PostgreSQL - not sure if this is a problem for you)
- this will also help if your queries are selective on time and country_id (per this example)
- the partitioning by time allows you to DROP old chunks without the need for vacuums
On Thu, Apr 20, 2017 at 8:30 AM, Vick Khera <vivek@khera.org> wrote:
I'm curious why you have so many partial indexes. Are you trying to make custom indexes per query? It seems to me you might want to consider making the indexes general, and remove the redundant ones (that have the same prefix list of indexed fields).
Secondly your table is 102Gb. Clearly there's a lot of data here. How many rows does that take? I would further suggest that you partition this table such that there are no more than about 10 million rows per partition (I've done this by using a id % 100 computation). Maybe in your case it makes sense to partition it based on the "what" field, because it appears you are trying to do that with your partial indexes already.
Now, just need to get performance another 2 orders of magnitude better. Can we make the index a bit more coarse grained, perhaps convert long/lat to integers or something, use a hilbert index, use a postgis index... ideas?