PostgreSQL – Can queries that require all partitions be blocked or rejected?
We are planning to partition a dataset of ~500million rows using list/range partitioning in PostgreSQL 13.4 on RDS. Our read access patterns should only require a very small number of partitions to be accessed for each query. We would like to prevent read queries that require accessing every partition, so that this strategy can serve well as the volume of data grows. Is it possible to enforce any checks or guards at the database layer to reject/block/fail on queries that require a query plan that includes every partition?
Essentially, I would expect this to manifiest as ensuring there is sufficient filtering in the
WHEREclause for the query planner to know it does not need to route to every partition. We can examine the query plans for a sample of the queries coming from the business logic, but it would be nice to be able to enforce it automatically. Ideally we can enforce in production if the overhead is not prohibitive, but just doing it in lower environments would be a positive step. Are there any options or suggestions on how that might be possible? Many thanks in advance.
There is no way to do that.
The best I can think of is to set the parameter
statement_timeoutin your database session so that statements that run too long fail with an error.