DELETE removes values stored in user-created tables.

Syntax

DELETE FROM <table_name> [AS <alias>]
[USING <from_item> [, ...]]
[WHERE <condition>]
;
Syntax element Description
<table_name> The table whose values you want to remove.
AS <alias> Optional. The alias for the table. If specified, only permit references to <table_name> as <alias>.
USING from_item Optional. Table expressions whose columns you want to reference in the WHERE clause. This supports the same syntax as the FROM clause in SELECT statements, e.g. supporting aliases.
WHERE <condition> Optional. Only remove rows which evaluate to true for condition.

Details

Known limitations

  • DELETE cannot be used inside transactions.
  • DELETE can reference read-write tables but not sources or read-only tables.
  • Concurrency. DELETE does not take a table-wide write lock. DELETE, UPDATE, and INSERT ... SELECT run under optimistic concurrency control: a bounded number run concurrently, so a long-running one can delay other such statements (including on unrelated tables), and a statement that conflicts with a concurrent write to the same table is retried automatically. Plain INSERT ... VALUES is not blocked.
  • Large deletes. A single DELETE that matches a very large number of rows can exceed max_result_size or statement_timeout. See Large DELETE statements.

Large DELETE statements

Materialize processes a DELETE by reading every row the statement matches back through the cluster so it can retract it. The work therefore scales with the number of rows matched, not just the size of the table or the complexity of the WHERE clause.

A DELETE has two limits:

  • Size. The rows a DELETE matches can total at most max_result_size (default 1 GiB). A larger DELETE fails with total result exceeds max size and removes no rows.
  • Time. statement_timeout (default 60 seconds) covers the statement until its commit, including time spent waiting behind other DELETE, UPDATE, and INSERT ... SELECT statements. If the timeout fires during the commit, the DELETE can still succeed.

To tell whether a DELETE is at risk before running it, time the matching SELECT: a DELETE does roughly the same work as selecting the rows it matches. If SELECT * FROM <table> WHERE <condition> does not return comfortably within statement_timeout, a DELETE with the same WHERE clause will not complete either. A DELETE that fails with a statement timeout removes no rows (the statement is atomic), so it is safe to retry as smaller batches.

To delete more than fits in one statement, split it into smaller statements that each match fewer rows, for example by key range:

-- Instead of one large delete:
DELETE FROM events WHERE ts < '2026-01-01';

-- Delete in smaller batches by key range:
DELETE FROM events WHERE ts < '2026-01-01' AND id BETWEEN 0 AND 99999;
DELETE FROM events WHERE ts < '2026-01-01' AND id BETWEEN 100000 AND 199999;
-- ...continue until all matching rows are removed.

Raising statement_timeout or increasing the cluster size can help a moderately large DELETE complete, but a very large read-back can still fail, so splitting the statement is the reliable approach. The same guidance applies to large UPDATE statements, which use the same mechanism.

Examples

CREATE TABLE delete_me (a int, b text);
INSERT INTO delete_me
    VALUES
    (1, 'hello'),
    (2, 'goodbye'),
    (3, 'ok');
DELETE FROM delete_me WHERE b = 'hello';
SELECT * FROM delete_me ORDER BY a;
 a |    b
---+---------
 2 | goodbye
 3 | ok
CREATE TABLE delete_using (b text);
INSERT INTO delete_using VALUES ('goodbye'), ('ciao');
DELETE FROM delete_me
    USING delete_using
    WHERE delete_me.b = delete_using.b;
SELECT * FROM delete_me;
 a | b
---+----
 3 | ok
DELETE FROM delete_me;
SELECT * FROM delete_me;
 a | b
---+---

Privileges

The privileges required to execute this statement are:

  • USAGE privileges on the schemas that all relations and types in the query are contained in.
  • DELETE privileges on table_name.
  • SELECT privileges on all relations in the query.
    • NOTE: if any item is a view, then the view owner must also have the necessary privileges to execute the view definition. Even if the view owner is a superuser, they still must explicitly be granted the necessary privileges.
  • USAGE privileges on all types used in the query.
  • USAGE privileges on the active cluster.
Back to top ↑