polm23
Reputation: 15623
Are constraint checks on partitions in postgres always a good idea?
Suppose I have a table partitioned by month in postgres where the following are true:
- Records are never updated.
- New records are only added with the current time, give or take a few minutes.
- A trigger handles inserts so records only go into this month's partition or last month's.
Do constraint checks on the partitions / child tables help me here? Since the trigger handles the end-of-month period, it seems they would be redundant and thus unhelpful, but I feel I might be missing something.
Upvotes: 0
Views: 172
Answers (1)
Frank Heikens
Reputation: 127297
The constraints are needed for the query planner, it uses the constraint to know where to find specific data. See the documentation.
Upvotes: 3
Related Questions
- Postgresql - CHECK constraint not preventing ACCESS EXCLUSIVE lock and table scan when attaching new partition
- Partitioned table - is adding an index on the partition column unnecessary?
- Primary key and constraints on partitioned tables
- Is it safe to use postgres table partitioning with overlapping constraints?
- Overhead of using many partitions in Postgres
- PostgreSQL: when we should set constraint_exclusion off?
- Will Postgres enforce check constraints on columns that I didn't update?
- Table inheritance: Enforcing unique constraints across partitions?
- PostgreSQL Partitioning. Constraint Exclusion not kicking in on SELECT
- Partitioning in PostgreSQL when partitioned table is referenced