Suppression list lookup is indexed for O(1) performance

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.

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: 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).