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?
Thanks!
Best Answer
Partitioning has a few other benefits as SQL Server almost sees it as a brand new table. Thus it will use more threads and run concurrent scans on the GAM/SGAM/etc. pages to find your data. It'll also lock only that partition if it needs to escalate which is a nice to have, assuming you are partition aligned. So if you're running a query that hits multiple partitions and you have partition elimination working, it will be able to do the metadata lookups and scans on all those in parallel.
It gives you the benefit of having a particular index for the top (or first) partition when it is queried so heavily but drop the index or add other ones for historic data. This way you can maximize your index usage that way as well.
You can even compress data per partition if you'd like, although you're using newer versions of SQL Server you might get some good compression per partition or just table.
There could be more but those are the main ones I can think of.