KDBS
  1. Docs
  2. SQL reference

PURGE

v3.0.0

Remove deleted rows in a primary-key range and free their keys for reuse.

In this article

Removes deleted rows within a range of primary keys and frees their keys, so that an INSERT can supply one of those keys again. It is the only way a committed key becomes free.

Syntax

PURGE FROM <table> WHERE <primary-key comparison> [AND <primary-key comparison>] ...

Each comparison compares the primary key with a non-negative integer, using =, <, <=, >, >= or BETWEEN a AND b. The comparisons are combined into one key range.

Examples

kdbs> INSERT INTO passengers VALUES (9, 'Oh Haneul', 0, '2026-09-01')
INSERTED oid=4005 id=9 page=133 slot=8
kdbs> DELETE FROM passengers WHERE id = 9
DELETED 1
kdbs> PURGE FROM passengers WHERE id = 9
PURGED 1
kdbs> INSERT INTO passengers VALUES (9, 'Bae Sooin', 1, '2026-09-02')
INSERTED oid=4005 id=9 page=133 slot=9

The last INSERT is placed because key 9 is free again.

PURGE FROM passengers WHERE id BETWEEN 100 AND 199;

How it works

  • Only keys whose rows were deleted by a committed DELETE are freed. Live rows inside a wider range are skipped; naming a single live key is an error.
  • A key that was never used, was rolled back, or is already purged is skipped without error, so PURGE can always be run again.
  • A row is freed only when no open transaction can still read it. PURGE waits up to 1 second for such readers, then is refused with a retryable TXN_CONFLICT that names the key, having freed nothing. It holds no locks while it waits.
  • The whole range is checked before anything is written, then keys are freed one at a time in key order. If a crash interrupts it, running the same statement again finishes the range.
  • The engine never issues a freed key by itself. Only an INSERT that supplies the key takes it.
  • Reply: PURGED <n>, the number of keys freed.

A purged key is a new identity. If a system outside the database recorded the old key, it must treat a row inserted later under that key as a different record. The log records when each purge happened.

Limitations

  • Autocommit only. Inside BEGIN ... COMMIT it is refused with NOT_IMPLEMENTED, and the transaction is not aborted.
  • Only conditions on the primary key. Any other condition is refused rather than ignored, because ignoring it would free more than was asked.
  • B+ tree tables only, and not system tables.
  • Requires the admin role.

See also