Postgres 11 declarative partitioning by relative time 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:


Can rows overlap multiple partitions? For example, an event that occurred 3 hours ago fits the criteria for the first 2 time periods above
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.