1

I am creating a table in PostgreSQL and bulk load with data first. PostgreSQL's documentation recommends to insert large volumes of data first and then create indexes and foreign key constraints. What's about check constraints? Is it better to create them before inserting initial data or after? Or does it matter?

For example, a table will have an index on time, NOT NULL constraint on it, check constraint on time interval and possibly on device. If no bulk load, the definition will be:

CREATE TABLE conditions(
    time timestamptz NOT NULL,
    device int,
    value float,
    CONSTRAINT cond_time CHECK ("time" >= '2020-01-16 01:00:00+01'::timestamp with time zone 
                            AND "time" < '2020-01-23 01:00:00+01'::timestamp with time zone),
    CONSTRAINT cond_device CHECK (my_hash_function(device) >= 1073741823
                              AND my_hash_function(device) < 1075441823)
);
CREATE INDEX conditions_time_index ON conditions(time);

The index will be create after the bulk. The data to bulk load is cleaned and satisfies the constraints. The constraints are needed in future during normal operations.

Will it be more efficient if the check constraints and NULL NOT are created before bulk load or after? Does it matter if an index will exist on the column targeted in the check constraint?

1 Answer 1

0

You can benchmark it, but it shouldn't make a difference. The check expression will be evaluated for each row, and it only uses the data from that row. It should make no difference if that takes place while you load the data or afterwards.

6
  • Thank you for the answer. So it sounds that there is no optimization for evaluating checks, e.g., using indexes. Then having checks from the start might be more efficient, since otherwise it will be necessary to re-read data.
    – k_rus
    May 4, 2021 at 7:03
  • A check constraint is only allowed to see the data from the row it checks. How could an index help with that? May 4, 2021 at 7:11
  • If a check is only on one column, the index can show if all values satisfy the condition. It is unlikely to be implemented for check constraint, but it might be already implement for NOT NULL constraint.
    – k_rus
    May 4, 2021 at 7:20
  • There is such an optimization for NOT NULL, if I remember right, but not for check constraints. May 4, 2021 at 7:27
  • 1
    I mixed it up. I was thinking of this, but that's something different. The parsed check constraint is stored in pg_constraint.conbin. You'll have to test if you want to know numbers. The time it takes depends on the constraint. May 4, 2021 at 7:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.