Is it more efficient to create check constraints on PostgreSQL table before or after bulk load?

Question

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?

Answer 1

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.