PostgreSQL partial unique indexes: let archived names coexist safely

A partial unique index can enforce one active name per workspace while allowing archived records to retain that name. Put the active-record predicate in the index and make create, archive and restore follow the same rule. The difficult case is restoring an old record after a replacement has claimed its name; define that conflict as a product outcome before writing the migration.

State the invariant precisely

For a synthetic integrations table, the invariant is one active normalized_name per workspace_id. Archived rows are outside the active set. Decide how normalization handles case, whitespace and Unicode, and whether names can be null. Store the chosen normalized value consistently or use a reviewed expression. A uniqueness rule on the display string will not automatically match the interface's idea of equivalent names.

PostgreSQL documents partial indexes as indexes over rows satisfying a predicate and partial unique indexes as a way to enforce uniqueness on that subset. A normal partial index speeds eligible access but does not establish uniqueness. The inspected implementation context contains predicate-specific indexes; the partial unique design below is a separate synthetic proposal.

PostgreSQL: partial indexes and subset uniqueness

A disposable schema contract

The following example uses a nullable archived_at timestamp to define the active set. It is illustrative SQL and has not been run against PostgreSQL here. Before adding the index to an existing table, inspect duplicate active values under the exact normalization rule and resolve them through a reviewed data policy. Do not discover those conflicts halfway through a production deployment.

CREATE UNIQUE INDEX fixture_active_integration_name
ON fixture_integrations (workspace_id, normalized_name)
WHERE archived_at IS NULL;
INSERT INTO fixture_integrations
  (workspace_id, normalized_name, archived_at)
VALUES ($1, $2, NULL)
ON CONFLICT (workspace_id, normalized_name)
  WHERE archived_at IS NULL
DO NOTHING
RETURNING id;

The conflict target includes the partial-index predicate. Verify inference against the actual schema, especially if other unique indexes cover related columns. DO NOTHING is a choice, not an entire API contract: an empty RETURNING result must become a documented conflict or an explicit lookup. Silently returning success would leave the user believing an integration was created when it was not.

Restore deserves a first-class conflict state

Create an active integration named analytics, archive it, then create another active analytics. Restoring the old row must fail or ask for a new name while the replacement remains active. Do not quietly archive the replacement unless that is an explicit authorized product operation. The constraint protects the invariant during concurrent creates; application preflight checks alone cannot close that race.

Scope every action by the verified workspace and record identity. Two workspaces may legitimately have the same active normalized name under this design. An archive action should affect one intended row, not every row with that name. If the API supports optimistic versions, include the current version so a stale restore screen cannot overwrite a recent rename or archive decision.

Review query and migration implications separately

A uniqueness index is justified by the invariant even when no particular query plan uses it. Query acceleration still depends on predicate matching, operator shape and selectivity. Measure the list or lookup query separately rather than claiming an index makes every request faster. Every indexed write has maintenance and storage cost, and changes to the active-state predicate can require a new migration plan.

For the acceptance artifact, record create/create collision, archive/recreate, restore collision, rename collision and cross-workspace reuse. Add concurrent insertion in two transactions and inspect both returned results and final rows. Capture database version and the installed index definition. A migration marked IF NOT EXISTS only checks whether an object name exists; it does not prove an existing index has the intended definition. Review the catalogue definition before treating a rerun as schema conformance.

Multi-tenant SaaS data isolation with FastAPI and PostgreSQL

Observability for small teams: start with the failed customer task

Bring one API or data workflow and a sanitized failure example for a bounded implementation assessment.

AptixLabs saas development