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
~200 GB with
456 millions rows.
To optimize our most common searches we have two indexes:
These indexes are used frequently and its size is
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
~ 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 warehousingand indexing all this data in an
- Remove index from table, indexing data in an
ElasticSearchbut with only ID in its body. So I would only use
ElasticSearchto search and database to retrieve this data
- Give a try in
InfluxDB(time-series database), filtering and selecting data from all this
5 yearsdirectly 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?