Trigger vs. check constraint

Question

I want to add a field-level validation on a table. There is a field named "account_number" and this field should always pass a "luhn" check. I've found a function called "luhn_verify" that seems to work properly (google for it if you are interested). It returns a boolean. My question is:

Are there any major performance advantages in PostgreSQL for using a trigger for this validation vs. a check constraint.

Additional information:

• PostgreSQL 9.1
• Table does not currently have an insert trigger, but does have an update.

Disclaimers:

I feel like this has probably already been answered, but I can't seem to find a distinct answer. If so, please mark as duplicate and reference original question/answer.

Might be a better questions for the dba board.

Antipas

I don't expect any meaningful difference. It is trivial to test, and it's always a good idea to test in your exact environment. – Candler

Answer 1

The rule of thumb is to use CHECK constraint when possible.

A CHECK constraint is faster, simpler, more portable, needs less code and is less error prone. Triggers can easily be circumvented by other triggers, for instance.

A TRIGGER is more complicated. Use it when you have to, for more complex requirements.

If a CHECK constraint is too restrictive for your case or causes trouble reloading a dump, you could use the NOT VALID modifier as middle ground (Postgres 9.2+). And, optionally, VALIDATE it later. See:

• Disable all constraints and table checks while restoring a dump

Postdate

@Erwin what you think is payload to have, or not, a Check Constraint, like not empty, in PostgreSQL 9.x? – Oder

@ErwinBrandstetter always right! #19138311 – Oder

@Erwin in my experience postgres treats check constraints and triggers differently when one is restoring the database, namely triggers can be ignored but check constraints cannot be, so depending upon the constraint one could face a more complicated restore when using check constraints. – Mischa

@rozkosz: Certainly worth mentioning. Since Postgres 9.1 you can create a CHECK constraint with NOT VALID, which would allow some leeway. With a validated check constraint in place, existing data in a dump should never violate a CHECK constraint to begin with. – Postdate

Is a check constraint actually faster? more performant in Postgres? I have read that modern RDBMS make triggers as fast as check constraints. Do you have something to support that? – Zolly

@Erwin It seems that postgres does not always maintain the correct table ordering in a dump so that even if the data satisfies the constraints the CHECK constraint can end up being invalid. I keep forgetting this and have to re-write my CHECK constraints as TRIGGERS. – Mischa