SQL RECHECK

What is SQL RECHECK?

RECHECK forces the database to re-validate previously deferred or NOT VALID constraints and to re-scan lossy index entries so that only rows that truly satisfy the predicate remain.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL RECHECK: PostgreSQL 12+ (native) Not available in MySQL, SQL Server, Oracle, SQLite

SQL RECHECK Full Explanation

RECHECK is a PostgreSQL-specific maintenance command that tells the server to verify that existing data still meets the rules defined by a constraint or index that was earlier created with the NOT VALID clause or that relies on a lossy access method (GiST, GIN, BRIN, SP-GiST). During normal query execution PostgreSQL already performs row-level rechecks for lossy indexes, but the RECHECK command lets you do it proactively and in bulk. For constraints, it upgrades them from NOT VALID to VALID without recreating the object from scratch. RECHECK scans the target rows, applies the original predicate, and marks the object as validated when no violations are found. If any row fails, the command aborts and reports the first offending row. Because data is scanned and checked, RECHECK can be I/O intensive and should usually be run during off-peak hours. RECHECK does not rebuild the index structure or alter query plans; it only guarantees logical correctness.

SQL RECHECK Syntax

RECHECK TABLE table_name [ CONSTRAINT constraint_name [, ...] ];
RECHECK INDEX index_name;

SQL RECHECK Parameters

Example Queries Using SQL RECHECK

--Validate a NOT VALID check constraint added during a hotfix
ALTER TABLE orders ADD CONSTRAINT chk_total_gt_zero CHECK (total_amount > 0) NOT VALID;
--later, during maintenance
RECHECK TABLE orders CONSTRAINT chk_total_gt_zero;

--Force a lossy GiST index to be fully rechecked after bulk loading
RECHECK INDEX geo_idx_users_location;

Expected Output Using SQL RECHECK

If no violations exist, PostgreSQL returns RECHECK completed and marks the constraint or index as VALID
If conflicting rows are found, the statement fails with ERROR: constraint "name" for relation "table" is violated, and no changes are committed

Use Cases with SQL RECHECK

Finalizing NOT VALID constraints after a zero-downtime deployment
Confirming data integrity before promoting a staging database to production
Cleaning up false positives in spatial or full-text searches backed by lossy indexes
Periodic compliance audits where constraints must be guaranteed as VALID

Common Mistakes with SQL RECHECK

Assuming RECHECK rebuilds or optimizes the index – it only validates data.
Forgetting that RECHECK TABLE without a constraint list rechecks every NOT VALID constraint, which can be slow.
Running RECHECK on tables with heavy write traffic, causing lock contention.
Expecting the command to exist in MySQL or SQL Server – it is PostgreSQL-only.

First Introduced In

PostgreSQL 12

Frequently Asked Questions

What happens if RECHECK finds a violation?

The command aborts, no changes are committed, and PostgreSQL reports the first offending row so you can fix or remove it.

Does RECHECK improve query performance?

No. It only guarantees correctness. To improve performance, consider REINDEX, CLUSTER, or ANALYZE.

Can I cancel a long-running RECHECK?

Yes. Like any SQL statement, it can be cancelled with `pg_cancel_backend` or by terminating the session, though partial work is rolled back.

Is RECHECK transactional?

Yes. If it completes successfully, the validation is committed. If it fails or is cancelled, all effects are rolled back.