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'?