MIME-Version: 1.0
References: <CANWftzK2MZ7Js_56V+ZcLxZyH1utBZx4uEg03P7Cee86K2roCQ@mail.gmail.com>
In-Reply-To: <CANWftzK2MZ7Js_56V+ZcLxZyH1utBZx4uEg03P7Cee86K2roCQ@mail.gmail.com>
From: =?UTF-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123@gmail.com>
Date: Fri, 15 Nov 2024 16:28:06 +0100
Message-ID: <CAKkG4_=pL3s0cpr4d4hWcobRcdBF0=73RMcasiSz8vM9dcubxw@mail.gmail.com>
Subject: Re: Validating check constraints without a table scan?
To: Philip Couling <couling@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000640e090626f537e1"
Archived-At: <https://www.postgresql.org/message-id/CAKkG4_%3DpL3s0cpr4d4hWcobRcdBF0%3D73RMcasiSz8vM9dcubxw%40mail.gmail.com>
Precedence: bulk

--000000000000640e090626f537e1
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, Nov 15, 2024 at 9:38=E2=80=AFAM Philip Couling <couling@gmail.com> =
wrote:

> Is there a solid reason why adding a check constraint does not use
> existing indexes for validation.
>
> We are currently looking at partitioning a multi TB table leaving all
> existing data in place and simply attaching it as a partition to a new
> table. To prevent locking, we are trying to add an INVALID check constrai=
nt
> first and then validate it.
>
> I can trivially prove the invalid constraint is valid with a simple SELEC=
T
> which will use an existing index and return instantaneously. But AFAIK
> Theres no way to mark a constraint as valid without scanning all the rows=
.
>

Most likely your query is not exactly the same as the check constraint.
Think about NULL and similar.


> This operation is really problematic on a production database with heavy
> IO load.
>
> Is there a solid ready why validating check constraints cannot use
> existing indexes? If I can prove the constraint is valid so trivially wit=
h
> a SELECT, then why can Postgres not do the same (or similar)?
>

Here is what has worked for me many times:

1. create the check constraint as NOT VALID. From now on no new or updated
row is allowed to violate it.
2. check if the constraint holds with a query on a binary replica. Make
sure the query starts only when the constraint is visible on the replica.
3. on the master: UPDATE pg_constraint SET conisvalidated=3Dtrue WHERE
conname=3D'your_constraint_name' AND conrelid=3D'schema.table'::regclass

How you perform step 2 is irrelevant. Checking it on a replica would simply
avoid the load on the master. You just need to make sure there is no
conflicting data in the table.

WARNING, you need to be damn sure of your data if you do that. But if you
are, it works.

Here is the procedure how I solved the same problem for some of our
multi-TB tables (PG14):

The table has a column called transaction_time. We wanted to partition by
that column. For some historical reason the column did not have a NOT NULL
constraint. However, there was no way our processes could insert NULL in
that column and there was no row with NULL in that field. So, first was to
add the NOT NULL constraint:

BEGIN;

ALTER TABLE my.table
ADD CONSTRAINT transaction_time_not_null CHECK(transaction_time IS NOT
NULL) NOT VALID;

UPDATE pg_constraint
   SET convalidated=3Dtrue
 WHERE conname =3D 'transaction_time_not_null'
   AND conrelid =3D 'my.table'::REGCLASS
RETURNING conname, conrelid::REGCLASS, convalidated;

COMMIT;

Now for cosmetic purposes we first turn the check constraint above into a
normal NOT NULL constraint:

BEGIN;

SET LOCAL client_min_messages =3D 'debug4';
-- expecting this message
-- DEBUG:  existing constraints on column "table.transaction_time" are
sufficient to prove that it does not contain nulls
ALTER TABLE my.table
      ALTER COLUMN transaction_time SET NOT NULL;
RESET client_min_messages;

ALTER TABLE my.table
      DROP CONSTRAINT transaction_time_not_null;

COMMIT;

If you set client_min_messages to something like debug4, then the database
tells you if it wants to scan the table or if existing constraints are
sufficient to prove the condition.

transaction_time in our case is never in the future. Also database
transactions are short-lived. Nothing exceeds 30 seconds. So, I can trust
the effect of any action performed more than 30 seconds ago in the database
is visible.

So, I set the time after which new rows go to the new partition at least 10
minutes from now at the next hour boundary. 30 seconds would be good
enough. I chose 10 minutes just for extra safety.

SELECT date_trunc('hour', now()+'1h 10min'::interval)::timestamp AS
switch_time\gset

Next comes the actual change:

BEGIN;

-- rename the existing table
ALTER TABLE my.table RENAME TO table_old;

-- drop triggers. We will recreate them later.
DROP TRIGGER ... ON my.table_old;
DROP TRIGGER ...;

-- create partitioned table
CREATE TABLE my.table (
    LIKE my.table_old
    INCLUDING DEFAULTS
    INCLUDING CONSTRAINTS
)
PARTITION BY RANGE (transaction_time);

-- recreate triggers
CREATE TRIGGER ...
BEFORE DELETE ON my.table
   FOR EACH ROW EXECUTE FUNCTION my,trigger_fun();
CREATE TRIGGER ...;

-- create the partition for future rows
CREATE TABLE my.table_current PARTITION OF my.table
  FOR VALUES FROM (:'switch_time') TO ('infinity');

-- and some indexes and FK
ALTER TABLE my.table_current ADD PRIMARY KEY (id);
CREATE INDEX ON my.table_current (...);
ALTER TABLE my.table_current
  ADD CONSTRAINT fk_name
      FOREIGN KEY (...)
      REFERENCES ...(...) ON UPDATE ... ON DELETE ...;

-- now attach the old table as partition
ALTER TABLE my.table_old
ADD CONSTRAINT partition_boundaries
CHECK('-infinity'<=3Dtransaction_time AND transaction_time<:'switch_time')
  NOT VALID;

-- for procedural reasons we know the constraint is valid. Let's make PG
believe it too.
UPDATE pg_constraint
   SET convalidated=3Dtrue
 WHERE conname=3D'partition_boundaries'
   AND conrelid=3D'my.table_old'::REGCLASS
RETURNING conname, conrelid::REGCLASS, convalidated;

-- now attach it. We use again debug4 to check if the table scan is avoided=
.
SET LOCAL client_min_messages =3D 'debug4';
-- expecting
-- DEBUG:  partition constraint for table "table_old" is implied by
existing constraints
ALTER TABLE my.table ATTACH PARTITION my.table_old
  FOR VALUES FROM ('-infinity') TO (:'switch_time');
RESET client_min_messages;

-- drop the now unnecessary constraint
ALTER TABLE my.table_old
 DROP CONSTRAINT partition_boundaries;

COMMIT;

Once the new partition gets too full, we will use a similar procedure to
adjust the partition boundary of the new partition and then create the next
partition.

--000000000000640e090626f537e1
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Nov 15, 2024 at 9:38=E2=80=AFAM P=
hilip Couling &lt;<a href=3D"mailto:couling@gmail.com">couling@gmail.com</a=
>&gt; wrote:</div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quo=
te" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204=
);padding-left:1ex">Is there a solid reason why adding a check constraint d=
oes not use existing indexes for validation.<div dir=3D"auto"><br></div><di=
v dir=3D"auto">We are currently looking at partitioning a multi TB table le=
aving all existing data in place and simply attaching it as a partition to =
a new table. To prevent locking, we are trying to add an INVALID check cons=
traint first and then validate it.</div><div dir=3D"auto"><br></div><div di=
r=3D"auto">I can trivially prove the invalid constraint is valid with a sim=
ple SELECT which will use an existing index and return instantaneously. But=
 AFAIK Theres no way to mark a constraint as valid without scanning all the=
 rows.</div></blockquote><div><br></div><div>Most likely your query is not =
exactly the same as the check constraint. Think about NULL and similar.</di=
v><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0p=
x 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div d=
ir=3D"auto"></div><div dir=3D"auto">This operation is really problematic on=
 a production database with heavy IO load.</div><div dir=3D"auto"><br></div=
><div dir=3D"auto">Is there a solid ready why validating check constraints =
cannot use existing indexes? If I can prove the constraint is valid so triv=
ially with a SELECT, then why can Postgres not do the same (or similar)?</d=
iv></blockquote><div><br></div><div>Here is what has worked for me many tim=
es:</div><div><br></div><div>1. create the check constraint as NOT VALID. F=
rom now on no new or updated row is allowed to violate it.</div><div>2. che=
ck if the constraint holds with a query on a binary replica. Make sure the =
query starts only when the constraint is visible on the replica.</div><div>=
3. on the master: UPDATE pg_constraint SET=C2=A0conisvalidated=3Dtrue=C2=A0=
WHERE conname=3D&#39;your_constraint_name&#39; AND conrelid=3D&#39;schema.t=
able&#39;::regclass</div><div><br></div><div>How you perform step 2 is irre=
levant. Checking it on a replica would simply avoid the load on the master.=
 You just need to make sure there is no conflicting data in the table.</div=
><div><br></div><div>WARNING, you need to be damn sure of your data if you =
do that. But if you are, it=C2=A0works.</div><div><br></div><div>Here is th=
e procedure how I solved the same problem for some of our multi-TB tables (=
PG14):</div><div><br></div><div>The table has a column called transaction_t=
ime. We wanted to partition by that column. For some historical reason the =
column did not have a NOT NULL constraint. However, there was no way our=C2=
=A0processes could insert NULL in that column and there was no row with NUL=
L in that field. So, first was to add the NOT NULL constraint:</div><div><b=
r></div><div>BEGIN;<br><br>ALTER TABLE my.table<br>ADD CONSTRAINT transacti=
on_time_not_null CHECK(transaction_time IS NOT NULL) NOT VALID;<br><br>UPDA=
TE pg_constraint<br>=C2=A0 =C2=A0SET convalidated=3Dtrue<br>=C2=A0WHERE con=
name =3D &#39;transaction_time_not_null&#39;<br>=C2=A0 =C2=A0AND conrelid =
=3D &#39;my.table&#39;::REGCLASS<br>RETURNING conname, conrelid::REGCLASS, =
convalidated;<br><br>COMMIT;</div><div><br></div><div>Now for cosmetic purp=
oses we first turn the check constraint above into a normal NOT NULL constr=
aint:</div><div><br></div><div>BEGIN;<br><br>SET LOCAL client_min_messages =
=3D &#39;debug4&#39;;<br>-- expecting this message<br>-- DEBUG: =C2=A0exist=
ing constraints on column &quot;table.transaction_time&quot; are sufficient=
 to prove that it does not contain nulls<br>ALTER TABLE my.table<br>=C2=A0 =
=C2=A0 =C2=A0 ALTER COLUMN transaction_time SET NOT NULL;<br>RESET client_m=
in_messages;<br><br>ALTER TABLE my.table<br>=C2=A0 =C2=A0 =C2=A0 DROP CONST=
RAINT transaction_time_not_null;<br></div><div><br></div><div>COMMIT;</div>=
<div><br></div><div>If you set client_min_messages to something like debug4=
, then the database tells you if it wants to scan the table or if existing =
constraints are sufficient to prove the condition.</div><div><br></div><div=
><div>transaction_time in our case is never in the future. Also database tr=
ansactions are short-lived. Nothing exceeds 30 seconds. So, I can trust the=
 effect of any action performed more than 30 seconds ago in the database is=
 visible.</div><div><br></div>So, I set the time after which new rows go to=
 the new partition at least 10 minutes from now at the next hour boundary. =
30 seconds would be good enough. I chose 10 minutes just for extra safety.<=
/div><div><br></div><div>SELECT date_trunc(&#39;hour&#39;, now()+&#39;1h 10=
min&#39;::interval)::timestamp AS switch_time\gset<br></div><div><br></div>=
<div>Next comes the actual change:</div><div><br></div><div>BEGIN;</div><di=
v><br></div><div>-- rename the existing table</div><div>ALTER TABLE my.tabl=
e RENAME TO table_old;<br><br></div><div>-- drop triggers. We will recreate=
 them later.</div><div>DROP TRIGGER ... ON my.table_old;<br>DROP TRIGGER ..=
.;<br><br></div><div>-- create partitioned table</div><div>CREATE TABLE my.=
table (<br>=C2=A0 =C2=A0 LIKE my.table_old<br>=C2=A0 =C2=A0 INCLUDING DEFAU=
LTS<br>=C2=A0 =C2=A0 INCLUDING CONSTRAINTS<br>)<br>PARTITION BY RANGE (tran=
saction_time);<br></div><div><br></div><div>-- recreate triggers</div><div>=
CREATE TRIGGER ...<br>BEFORE DELETE ON my.table<br>=C2=A0 =C2=A0FOR EACH RO=
W EXECUTE FUNCTION my,trigger_fun();<br></div><div>CREATE TRIGGER ...;</div=
><div><br></div><div>-- create the partition for future rows</div><div>CREA=
TE TABLE my.table_current PARTITION OF my.table<br>=C2=A0 FOR VALUES FROM (=
:&#39;switch_time&#39;) TO (&#39;infinity&#39;);<br></div><div><br></div><d=
iv>-- and some indexes and FK</div><div>ALTER TABLE my.table_current ADD PR=
IMARY KEY (id);<br>CREATE INDEX ON my.table_current (...);<br></div><div>AL=
TER TABLE my.table_current<br>=C2=A0 ADD CONSTRAINT fk_name<br>=C2=A0 =C2=
=A0 =C2=A0 FOREIGN KEY (...)<br>=C2=A0 =C2=A0 =C2=A0 REFERENCES ...(...) ON=
 UPDATE ... ON DELETE ...;<br></div><div><br></div><div>-- now attach the o=
ld table as partition</div><div>ALTER TABLE my.table_old<br>ADD CONSTRAINT =
partition_boundaries<br>CHECK(&#39;-infinity&#39;&lt;=3Dtransaction_time AN=
D transaction_time&lt;:&#39;switch_time&#39;)<br>=C2=A0 NOT VALID;<br><br>-=
- for procedural reasons we know the constraint is valid. Let&#39;s make PG=
 believe it too.</div><div>UPDATE pg_constraint<br>=C2=A0 =C2=A0SET convali=
dated=3Dtrue<br>=C2=A0WHERE conname=3D&#39;partition_boundaries&#39;<br>=C2=
=A0 =C2=A0AND conrelid=3D&#39;my.table_old&#39;::REGCLASS</div><div>RETURNI=
NG conname, conrelid::REGCLASS, convalidated;<br><br>-- now attach it. We u=
se again debug4 to check if the table scan is avoided.<br>SET LOCAL client_=
min_messages =3D &#39;debug4&#39;;<br>-- expecting<br>-- DEBUG: =C2=A0parti=
tion constraint for table &quot;table_old&quot; is implied by existing cons=
traints<br>ALTER TABLE my.table ATTACH PARTITION my.table_old<br>=C2=A0 FOR=
 VALUES FROM (&#39;-infinity&#39;) TO (:&#39;switch_time&#39;);<br>RESET cl=
ient_min_messages;<br><br>-- drop the now unnecessary constraint<br>ALTER T=
ABLE my.table_old<br>=C2=A0DROP CONSTRAINT partition_boundaries;<br></div><=
div><br></div><div>COMMIT;</div><div><br></div><div>Once the new partition =
gets too full, we will use a similar procedure to adjust the partition boun=
dary of the new partition and then create the next partition.</div></div></=
div>

--000000000000640e090626f537e1--