We use PostgreSQL 9.0 for our application and there is a table called events with a lot of metadata columns about event itself. The most important is resource_id that tells for which resource is related.

This table has being growing a lot and currently stores only 6 months of data. Its table_size is ~200 GB with 456 millions rows.

To optimize our most common searches we have two indexes:

  • timestamp
  • timestamp with resource_id

These indexes are used frequently and its size is ~160 GB.

This scenario that I've described is happening in a single Master instance without Slaves servers nor sharding.

Now we intend to extend this period from 6 months to 5 years of data. Estimating this amount in data would be something about ~4 billions rows with a table_size about ~ 2 TB of data.

So our indexes will have something about 1.6 TB (which I'm really worried to run in a single instance).

We had some ideas to improve this such as:

  • Remove indexes from table and store all that data in database focused in Data warehousing and indexing all this data in an ElasticSearch cluster
  • Remove index from table, indexing data in an ElasticSearch but with only ID in its body. So I would only use ElasticSearch to search and database to retrieve this data
  • Give a try in InfluxDB (time-series database), filtering and selecting data from all this 5 years directly from it
  • Upgrade PostgreSQL to 9.6, clusterize (Master + 2 Slaves) and/or partitioning

We're looking for scalable solutions that proper solves this scenario best-way possible. Thoughts?

  • I don't see any reason to shard this data. What you definitely can do is partitioning. Events are usually so that the most recent ones are the most interesting, and older ones are queried increasingly less often. Upgrading to 9.6 is definitely a good idea - BRIN indexes, for example, might be useful. If there is a subset of metadata that you need way more often than the others, it might also make sense to split your table vertically, moving the less often viewed data into a separate table. – dezso Apr 26 '17 at 16:23
  • @dezso didn't know about BRIN indexes, maybe it could be a solution. About sharding I confused when writing, already fixed, really meant partitioning – Weverton Timoteo Apr 26 '17 at 16:46
  • Why have an index on both timestamp and (timestamp, resource_id) ? Dropping the single-column index would cut the size dedicated to indexes nearly in half. – jjanes Apr 26 '17 at 20:42
  • Split up your giant event table into lots of small ones. Monthly or weekly tables - and the use constraint partitioning by time-stamp postgresql.org/docs/current/static/ddl-partitioning.html – maxTrialfire Apr 28 '17 at 19:59
  • @jjanes we use both indexes to query events by timestamp and combined (timestamp + resource_id). @maxTrialfire we already considered splitting by timestamp, thanks for contributing :D – Weverton Timoteo May 30 '17 at 21:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.