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?
entity_id, it may be more efficient to scan a partition on that
entity_idor to use a local index on the date column in a table that is partitioned on
entity_idthan it would be to use a single large index on an unpartitioned table but this will depend on things like whether partitioning also physically orders rows for the same
entity_id. There may be other database-specific options to be aware of.