Not Just UNIQUE: Generalized Index Constraints

Jeff Davis

From PostgreSQL Conference West Speakers

UNIQUE indexes have long held a unique position among constraints: they are the only way to express a constraint that two tuples in a table conflict without resorting to triggers and locks (which severely impact performance). But what if you want to impose the constraint that one person can't be in two places at the same time? In other words, you have a schedule, and you want to be sure that two periods of time for the same person do not overlap. This is nearly impossible to do efficiently with the current version of PostgreSQL -- and most other database systems. I will be presenting Generalized Index Constraints, which is being submitted for inclusion in the next PostgreSQL release, along with the PERIOD data type (available now from PgFoundry). I will show how these can, together, offer a fast, scalable, and highly concurrent solution to a very common business requirement. A business requirement is still a requirement even if your current database system can't do it!

operator_exclusion_constraints.pdf187.35 KB