0

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!

Improve this question
5
  • 1
    Unless your queries do partition elimination, partitioning wont provide you with speed. Why not archive data to another database and reference it using 3 parts in your all if required.
    – Kin Shah
    Aug 16, 2016 at 20:54
  • 1
    Partitioning makes sense if you have to place DB files to different physical drives.
    – Alex Kudryashev
    Aug 16, 2016 at 20:54
  • I would suggest you look at columnstore & InMemory - sql server 2016 for speed & performance.
    – Kin Shah
    Aug 16, 2016 at 21:15
  • This is tagged SQL, which is a language, but not tagged with any particular database engine. That may change things a bit. If the ~1,000 queries all have a particular entity_id, it may be more efficient to scan a partition on that entity_id or to use a local index on the date column in a table that is partitioned on entity_id than 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.
    – Justin Cave
    Aug 16, 2016 at 23:13
  • I did not mention a specific engine because I have some flexibility in choosing one right now. I figured every engine would have its own quirks so I was hoping for more agnostic advice rather than anything platform-specific.
    – Ivan
    Aug 17, 2016 at 12:11

1 Answer 1

Reset to default
2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.