I'm working on a system that uses PostgreSQL 11 as it's main data store, and I'm looking at how to most efficiently query a large table of events (up to 120 million rows, so not 'big data' big, but not exactly tiny), mainly based on time ranges. In particular, events will be searched across these preset time periods:

  • Last 1 hour
  • Last 4 hours
  • Last 24 hours
  • Last 7 days
  • Last 30 days

I was immediately drawn to the declarative partitioning capabilities of Postgres 11, but I've never used it before.

All of the examples I've seen use static values for partitioning, such as FOR VALUES FROM ('2018-02-01') TO ('2018-03-01'), which isn't going to work without a background task to manage partitions - so I have a couple of questions:

  1. Can rows overlap multiple partitions? For example, an event that occurred 3 hours ago fits the criteria for the first 2 time periods above
  2. Can rows be partitioned by relative time periods, such as 'last 1 hour'?

You could partition by 4 hour intervals (aligned to a specific boundary pattern) and then subpartition that into hours. But, I don't see what that would get you. If you have them partitioned by hour and run a query that covers 4 hours, it will just query the appropriate 4 individual hourly partitions. Adding an intervening layer of 4-hour partitioning doesn't get you anything.

For 2, no. It would have to move rows between partitions atomically with the tick of the smallest clock granularity, and that just isn't feasible. But if you query for an unaligned one hour interval, it will just have to include two adjacent one hour partitions, so it should still get a large part of whatever benefit there is to be had.

But it isn't clear what benefit there is to be had. It will depend on the nature of your queries. The biggest benefit you might get would be that once data reaches 31 days, you could drop the partition, rather than needing a massive DELETE.

  • For your last point, we would have a retention policy, where we delete old data, so dropping a partition would be really useful. On the benefit, I would have hoped that running a query that only had to hit a segment of the available data would be much faster. – Cocowalla Nov 30 '18 at 8:12
  • Indexes are generally a more efficient way to hit a segment of the data than partitions are. There are exceptions, of course, but you really have to dig into the nitty gritty to decide if what you have is an exception. Partitioning is neat, but I think it is overused. It should be a last resort, rather than a first resort. – jjanes Nov 30 '18 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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