General overview of what I'm building:
- Storing time-series data so I expect millions of rows added per month.
- Table has 4 columns, 2 of which are indexed, 1 is a numeric foreign key and the 4th contains a non-searchable JSON blob.
- 4th column is indexed in Elasticsearch for full-text search needs.
- For every 10000 rows inserted, ~1000 queries are immediately run to retrieve the last 60 days worth of data for each distinct entity. On occasion 10 users have access to a frontend to manually run queries for data of any age; UX is important so queries must return within seconds.
- I only need to keep a rolling year's worth of data.
The only upsides I'm seeing to partitioning is the ease of dropping entire partitions at once when purging, like with ES indexes. It is also suggested queries limited to recent data perform better when partitioned by temporal grouping.
But if I maintain proper indexes and only query on those, at what point do I need to consider partitioning?