Database Indexing for CRM Performance: When Slow Queries Start Costing You

Why indexing matters for automation stacks

Most automation stacks run on SaaS CRMs where performance is someone else's problem. But when you move into custom databases (Supabase, Postgres), performance becomes yours.

A 50k-row contact table with no indexes can take 30+ seconds per query. The same table with appropriate indexes returns in milliseconds.

At scale, this isn't a minor optimization. It's the difference between a responsive app and a broken one.

---

What an index is (plain language)

Imagine a phone book sorted by last name. Finding "Smith" is fast — flip to S, scan to Smith.

Now imagine a phone book sorted by ZIP code. Finding "Smith" means scanning every page. Slow.

An index is a secondary sorted structure that lets the database find data by a specific column without scanning the whole table.

Without an index on email, finding a contact by email = scan every row. With an index, it's a near-instant lookup.

---

When to add indexes

Rule of thumb: index columns you frequently:

1. Filter on (in WHERE clauses)
2. Join on (JOIN ... ON column)
3. Sort by (ORDER BY)
4. Use as lookup keys (email, phone, user_id)

Don't index:

• Every column (slows inserts/updates)
• Columns you never filter on
• Tables with few rows (full scan is fine under ~1,000 rows)

---

Common indexes for a CRM schema

For a contacts table with columns (id, email, phone, name, source, created_at, updated_at, deleted_at):

Always add

-- Primary key (auto-indexed in most DBs)
-- id is already indexed

-- Unique constraint on email (common CRM requirement)
CREATE UNIQUE INDEX idx_contacts_email 
ON contacts (LOWER(email));

-- Phone lookup
CREATE INDEX idx_contacts_phone 
ON contacts (phone);

Usually add

-- Filter/sort by creation date
CREATE INDEX idx_contacts_created_at 
ON contacts (created_at DESC);

-- Filter by source
CREATE INDEX idx_contacts_source 
ON contacts (source);

-- Soft delete filter
CREATE INDEX idx_contacts_deleted_at 
ON contacts (deleted_at) 
WHERE deleted_at IS NULL;

Consider

-- Composite index if you frequently filter by multiple columns
CREATE INDEX idx_contacts_source_created 
ON contacts (source, created_at DESC);

---

The performance difference

Real numbers for a 100,000 row contacts table on Supabase:

Query: SELECT * FROM contacts WHERE email = 'jane@example.com';

• Without index: 800-1500ms (full table scan)
• With index: 2-5ms (index lookup)

Query: SELECT * FROM contacts WHERE source = 'facebook-ads' ORDER BY created_at DESC LIMIT 100;

• Without index: 1200-2000ms (scan + sort all rows)
• With single-column indexes: 50-150ms
• With composite index: 5-20ms

At 1M rows, the difference is even more dramatic (30+ seconds vs. 10ms).

---

Types of indexes

B-Tree (default)

Standard index type. Works for equality and range queries (=, <, >, BETWEEN, ORDER BY).

CREATE INDEX idx_name ON table (column);

Use for: most cases.

Unique index

Enforces uniqueness + provides fast lookup.

CREATE UNIQUE INDEX idx_email ON contacts (LOWER(email));

Use for: fields that must be unique (email, phone after normalization).

Partial index

Index only a subset of rows, based on a WHERE clause.

CREATE INDEX idx_active_contacts 
ON contacts (email) 
WHERE deleted_at IS NULL;

Use for: when most queries filter on a specific condition (e.g., "only active contacts"). Smaller index, faster.

Composite index

Index on multiple columns.

CREATE INDEX idx_source_date 
ON contacts (source, created_at DESC);

Use for: queries that filter on multiple columns together.

Order matters: (source, created_at) is great for WHERE source = X AND ORDER BY created_at, but not for queries that only filter by created_at.

GIN / GiST indexes

For arrays, JSON, full-text search.

CREATE INDEX idx_tags_gin 
ON contacts USING GIN (tags);

Use for: JSON queries, tag arrays, full-text search.

Expression index

Index on a computed expression, not a raw column.

CREATE INDEX idx_lower_email 
ON contacts (LOWER(email));

Use for: case-insensitive lookups, normalized values.

---

Identifying slow queries

In Supabase

Supabase dashboard → Database → Query Performance. Shows slowest queries.

In Postgres directly

Enable pg_stat_statements extension:

CREATE EXTENSION pg_stat_statements;

Then query it:

SELECT query, calls, mean_exec_time, total_exec_time 
FROM pg_stat_statements 
ORDER BY mean_exec_time DESC 
LIMIT 20;

Shows the top 20 slow queries by average execution time.

Explaining a query

Use EXPLAIN ANALYZE to see how Postgres is running a query:

EXPLAIN ANALYZE 
SELECT * FROM contacts 
WHERE email = 'jane@example.com';

Output shows:

• Scan type (Seq Scan = bad, Index Scan = good)
• Estimated cost
• Actual rows processed
• Execution time

If you see "Seq Scan" on a large table, add an index.

---

When to remove an index

Indexes aren't free. They:

• Take disk space (10-30% extra per indexed column)
• Slow down INSERT/UPDATE/DELETE (every write updates every index)

Review indexes quarterly:

-- Find unused indexes in Postgres
SELECT 
  schemaname, 
  tablename, 
  indexname, 
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0 means the index hasn't been used since stats last reset. If it's been there for months and still zero scans, drop it:

DROP INDEX idx_unused_thing;

---

Common indexing mistakes

Mistake 1: Too many indexes

Every new column gets its own index. 8 indexes per table slows inserts to a crawl, bloats storage, and provides minimal query speed-up on indexes that aren't actually used.

Rule: add indexes reactively (when a query is slow) not proactively (just in case).

Mistake 2: Not indexing foreign keys

Joins without indexes on the join columns are slow.

-- Always index foreign key columns
CREATE INDEX idx_deals_contact_id ON deals (contact_id);

Mistake 3: Ignoring the cost of UPDATE/INSERT

A table with 5 indexes takes 5x longer to insert into than an unindexed table. If your workload is write-heavy (logging events, high-frequency updates), minimize indexes.

Mistake 4: Indexing LOWER(email) but querying email

-- Index:
CREATE INDEX idx_email ON contacts (LOWER(email));

-- Query (doesn't use the index):
SELECT * FROM contacts WHERE email = 'Jane@example.com';

-- Query (uses the index):
SELECT * FROM contacts WHERE LOWER(email) = 'jane@example.com';

The query must match the indexed expression exactly.

Mistake 5: Not indexing date ranges

Reports filtering by date without an index on the date column = full table scans.

-- Always index timestamp columns you filter on
CREATE INDEX idx_created_at ON contacts (created_at DESC);

---

Supabase-specific tips

Row-Level Security (RLS) and indexes

If RLS policies filter by user_id, index user_id. RLS is applied after the table scan unless you have indexes that match.

The 15-second query timeout

Supabase has a 15-second timeout on queries. A query that takes 20 seconds will fail. Add indexes before you hit this wall.

Connection pooling

Supabase uses connection pooling (port 6543 pooled, 5432 direct). If you're running lots of small queries, pooled connections work better.

---

Monitoring index usage

Quarterly, check:

1. Slow query log. Are there queries taking >100ms? Add indexes.
2. Unused index list. Drop indexes with zero scans.
3. Index bloat. Postgres indexes can become fragmented. Run REINDEX quarterly or when space usage seems off.
4. Database size trend. If database is growing faster than data, check for index bloat.

---

Real example: fixing a slow CRM page

Symptom: Contacts page in custom dashboard takes 8 seconds to load. 200k row table.

Diagnosis:

• Query: SELECT * FROM contacts WHERE deleted_at IS NULL ORDER BY created_at DESC LIMIT 50;
• EXPLAIN ANALYZE: Seq Scan on contacts, 8200ms

Fix:

CREATE INDEX idx_active_recent 
ON contacts (created_at DESC) 
WHERE deleted_at IS NULL;

After:

• Query time: 15ms
• Page load: under 1 second

Total engineering time: 30 minutes.

---

Sources

PostgreSQL indexing concepts from postgresql.org/docs/current/indexes.html. Specific syntax for Postgres verified against current documentation. Index performance benchmarks are typical ranges observed in production deployments on Supabase and AWS RDS.

Slow queries in your automation stack dashboard? Let's talk — query optimization is usually a 1-2 hour engagement for dramatic improvements.