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?