# Suppression list lookup is indexed for O(1) performance

- **Pattern:** `ab-000874` (`data-quality-list-hygiene.suppression-bounce.suppression-lookup-performance`)
- **Severity:** low
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000874
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000874)

## Why it matters

Every email dispatch that uses a local suppression table performs a lookup against that table before sending. Without an index on the `email` column, this lookup is a sequential full-table scan. At 100,000 suppressed addresses, a sequential scan takes tens of milliseconds. At 1,000,000 entries — common for mature lists — it degrades to hundreds of milliseconds per lookup, making pre-send verification the bottleneck in campaign throughput. Bulk campaigns that send to 50,000 contacts perform 50,000 suppression lookups; at 200ms each, that is 2.8 hours of scan time before a single email is dispatched.

## Severity rationale

Low because an unindexed suppression table degrades performance incrementally as the list grows, rather than causing immediate correctness failures — but the degradation eventually makes pre-send verification impractical.

## Remediation

Add a unique constraint (which PostgreSQL implements as a unique index) on the `email` column of the `suppressions` table:

```sql
ALTER TABLE suppressions ADD CONSTRAINT suppressions_email_unique UNIQUE (email);

-- Verify the index is used:
EXPLAIN ANALYZE SELECT 1 FROM suppressions WHERE email = 'test@example.com';
-- Expected: Index Scan using suppressions_email_unique
```

If a unique constraint is not appropriate (e.g., you allow multiple suppression reason entries per email), use a non-unique index instead:

```sql
CREATE INDEX idx_suppressions_email ON suppressions (email);
```

If Redis is used as the suppression store, use a SET data structure (`SISMEMBER`) for O(1) membership checks — do not store suppressed emails in a Redis LIST and iterate.

## Detection

- **ID:** `suppression-lookup-performance`
- **Severity:** `low`
- **What to look for:** Count all indexes on the suppression table and list all indexed columns. Check whether the `suppressions` table has a unique index on the `email` column. Without an index, suppression lookup is a full table scan — which degrades linearly as the suppression list grows. On a list of 1M suppressed addresses, an unindexed lookup can take seconds per check, making pre-send verification prohibitively slow.
- **Pass criteria:** The `suppressions` table has at least 1 unique index (or unique constraint, which implies an index) on the `email` column. Lookups by email are O(log N) or better.
- **Fail criteria:** No index on the `email` column of the suppression table. Lookups perform a sequential scan.
- **Skip (N/A) when:** Suppression is managed entirely by the external ESP with no local suppression table.
- **Detail on fail:** Example: `"suppressions table has no index on email column — EXPLAIN ANALYZE shows Seq Scan with 450ms for 500k rows"`
- **Remediation:** Add a unique index or constraint:

  ```sql
  -- Unique constraint (also creates an implicit unique index):
  ALTER TABLE suppressions ADD CONSTRAINT suppressions_email_unique UNIQUE (email);

  -- Or explicit index if constraint is not appropriate:
  CREATE UNIQUE INDEX idx_suppressions_email ON suppressions (email);

  -- Verify the index is used:
  EXPLAIN ANALYZE SELECT * FROM suppressions WHERE email = 'test@example.com';
  -- Should show: Index Scan using suppressions_email_unique
  ```

  If using Redis or another cache as the suppression store, ensure the data structure used provides O(1) lookup (SET or HASH, not LIST).

## External references

- iso-25010 performance-efficiency

Taxons: cost-efficiency

HTML version: https://auditbuffet.com/patterns/ab-000874