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.
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.
Add a unique constraint (which PostgreSQL implements as a unique index) on the email column of the suppressions table:
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:
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.
ID: data-quality-list-hygiene.suppression-bounce.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:
-- 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).