From 30s to 100ms: PostgreSQL Materialized Views for Customer Segmentation

A deep dive into PostgreSQL Materialized Views for high-performance customer segmentation

---

The Problem

Our multi-tenant CRM platform allows marketing teams to create customer segments with complex filters like:

• Customers with active subscriptions at Location A
• Who made 3+ transactions in the last 30 days
• With wallet credit > $50

Users could combine 65+ filter attributes across 7 tables with AND/OR conditions. The UI needed to show a live count as users built segments.

The pain point? Response times of 30+ seconds made the feature unusable.

graph LR
    subgraph "Current Performance"
        A[User Adds Filter] --> B[API Call]
        B --> C[Complex SQL Query]
        C --> D[30s Wait...]
        D --> E[Count Displayed]
    end

    style D fill:#ff6b6b,stroke:#333,stroke-width:2px

---

Root Cause Analysis

The Query Pattern

Each filter generated IN (SELECT...) subqueries:

SELECT COUNT(DISTINCT customer.id)
FROM customer
WHERE
  customer.id IN (
    SELECT customer_id FROM subscription_account
    WHERE status = 'active'
  )
  AND customer.id IN (
    SELECT customer_id FROM transaction_log
    WHERE created_at > NOW() - INTERVAL '30 days'
    GROUP BY customer_id HAVING COUNT(*) >= 3
  )
  AND customer.id IN (
    SELECT customer_id FROM wallet_account
    WHERE balance > 50
  )

The Performance Killer

Issue	Impact
Each subquery = full table scan	O(n) per filter
No covering indexes	Index-only scans impossible
COUNT DISTINCT on millions	Memory pressure
7 potential JOINs	Cartesian explosion risk

With millions of rows in transaction tables and 100+ tenants, this was a recipe for disaster.

---

The Solution: Materialized Views

Instead of computing aggregates at query time, we pre-compute them and store in materialized views.

Architecture Overview

flowchart TB
    subgraph Source["Source Tables"]
        SA[subscription_account]
        TL[transaction_log]
        WA[wallet_account]
        PA[package_account]
    end

    subgraph MVs["Materialized Views"]
        MV1["customer_attributes_mv<br/>Subscription, Wallet, Package aggregates"]
        MV2["customer_transactions_mv<br/>Transaction aggregates"]
    end

    subgraph API["API Layer"]
        SAPI[Segment Filter API]
    end

    SA --> MV1
    WA --> MV1
    PA --> MV1
    TL --> MV2

    MV1 --> SAPI
    MV2 --> SAPI

    style MV1 fill:#0d3026,color:#64ffda,stroke:#64ffda
    style MV2 fill:#0d3026,color:#64ffda,stroke:#64ffda

Two-View Strategy

We split into two MVs based on refresh frequency needs:

View	Data	Refresh Interval	Reason
customer_attributes_mv	Subscriptions, Wallets, Packages	45 seconds	High-value, frequently filtered
customer_transactions_mv	Transactions, Visits, Purchases	5 minutes	Large volume, less time-sensitive

---

MV Design: Pre-computed Aggregates

customer_attributes_mv

CREATE MATERIALIZED VIEW customer_attributes_mv AS
WITH
sub_stats AS (
    SELECT
        customer_id,
        COUNT(*) as subscription_count,
        BOOL_OR(status = 1) as is_active_subscriber,
        ARRAY_AGG(DISTINCT location_code) as location_codes,
        ARRAY_AGG(DISTINCT package_id) as package_ids,
        MAX(next_bill_date) as next_bill_date
    FROM subscription_account
    GROUP BY customer_id
),
wal_stats AS (
    SELECT
        customer_id,
        COUNT(*) as wallet_count,
        SUM(balance) as total_balance
    FROM wallet_account
    GROUP BY customer_id
)
SELECT
    c.id,
    c.customer_uuid,
    c.first_name,
    c.last_name,
    c.email,
    c.phone,
    -- Pre-computed flags (no subqueries needed at query time!)
    COALESCE(sub.is_active_subscriber, false) as is_active_subscriber,
    COALESCE(sub.subscription_count, 0) as subscription_count,
    sub.location_codes,
    sub.package_ids,
    sub.next_bill_date,
    -- Wallet aggregates
    COALESCE(wal.wallet_count, 0) as wallet_count,
    COALESCE(wal.total_balance, 0) as wallet_balance
FROM customer c
LEFT JOIN sub_stats sub ON c.customer_uuid = sub.customer_id
LEFT JOIN wal_stats wal ON c.customer_uuid = wal.customer_id;

GIN Indexes for Array Queries

The magic for multi-select filters:

-- Fast "customer has package X" lookups
CREATE INDEX idx_attr_packages_gin
  ON customer_attributes_mv USING GIN (package_ids);

-- Fast "customer visited location Y" lookups
CREATE INDEX idx_attr_locations_gin
  ON customer_attributes_mv USING GIN (location_codes);

-- Unique index required for CONCURRENTLY refresh
CREATE UNIQUE INDEX idx_attr_mv_id
  ON customer_attributes_mv (id);

---

Query Transformation

Before: Subquery Hell (30+ seconds)

SELECT COUNT(DISTINCT id) FROM customer
WHERE id IN (
  SELECT customer_id FROM subscription_account WHERE status = 1
)
AND id IN (
  SELECT customer_id FROM wallet_account WHERE balance > 50
);

After: Direct MV Query (<100ms)

SELECT COUNT(*) FROM customer_attributes_mv
WHERE is_active_subscriber = true
  AND wallet_balance > 50;

Array Contains with GIN Index

-- "Customer has package 123 OR 456"
SELECT COUNT(*) FROM customer_attributes_mv
WHERE package_ids && ARRAY[123, 456];

-- "Customer visited location 'LOC_A' AND 'LOC_B'"
SELECT COUNT(*) FROM customer_attributes_mv
WHERE location_codes @> ARRAY['LOC_A', 'LOC_B'];

Operators:

• && = overlaps (ANY)
• @> = contains (ALL)

---

Refresh Strategy

The Challenge

• Multi-tenant: 100+ separate databases
• Data freshness: <2 minute staleness acceptable
• Zero downtime: Queries must work during refresh

Solution: Status Table + Concurrent Refresh

-- Track when data changed vs when MV was refreshed
CREATE TABLE mv_refresh_status (
    mv_name VARCHAR(100) PRIMARY KEY,
    last_refresh_at TIMESTAMP,
    last_data_change_at TIMESTAMP,
    is_refreshing BOOLEAN DEFAULT false
);

The Critical Keyword: CONCURRENTLY

-- ❌ BLOCKS all queries during refresh (bad!)
REFRESH MATERIALIZED VIEW customer_attributes_mv;

-- ✅ Allows concurrent reads (good!)
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_attributes_mv;

Requirement: Unique index on MV for CONCURRENTLY to work.

How CONCURRENTLY Works Internally

sequenceDiagram
    participant Writer as Data Writer
    participant Source as Source Tables
    participant Refresh as REFRESH Process
    participant MV as Materialized View
    participant Reader as Query Reader

    Note over Refresh: Takes snapshot at T0
    Refresh->>Source: Read snapshot T0

    Writer->>Source: INSERT new row T1
    Note over Source: Row committed

    Reader->>MV: SELECT COUNT
    Note over MV: Returns old data (T0)

    Refresh->>MV: Atomic swap T2
    Note over MV: New data includes T0 NOT T1

    Note over Reader: Next query sees T0 data
    Note over Writer: T1 appears in next refresh

Key insight: Writes during refresh appear in the next refresh cycle, not the current one. This is why we accept 1-2 minute staleness.

---

TypeScript Implementation

interface RefreshConfig {
  mvName: string;
  intervalMs: number;
  debounceMs: number;
}

class MVRefreshService {
  private readonly configs: RefreshConfig[] = [
    { mvName: 'customer_attributes_mv', intervalMs: 45000, debounceMs: 10000 },
    { mvName: 'customer_transactions_mv', intervalMs: 300000, debounceMs: 60000 },
  ];

  @Cron('*/30 * * * * *') // Every 30 seconds
  async checkAndRefresh() {
    for (const tenant of await this.getTenants()) {
      const connection = await this.getConnection(tenant.id);

      for (const config of this.configs) {
        await this.refreshIfNeeded(connection, config);
      }
    }
  }

  private async refreshIfNeeded(
    connection: DataSource,
    config: RefreshConfig
  ): Promise<void> {
    const status = await this.getRefreshStatus(connection, config.mvName);

    // Skip if already refreshing
    if (status?.is_refreshing) return;

    // Skip if no data changes since last refresh
    if (status?.last_data_change_at <= status?.last_refresh_at) return;

    // Debounce: wait for writes to settle
    const timeSinceChange = Date.now() - status.last_data_change_at.getTime();
    if (timeSinceChange < config.debounceMs) return;

    // Perform refresh
    await this.executeRefresh(connection, config.mvName);
  }

  private async executeRefresh(
    connection: DataSource,
    mvName: string
  ): Promise<void> {
    const queryRunner = connection.createQueryRunner();

    try {
      // Mark as refreshing
      await queryRunner.query(
        `UPDATE mv_refresh_status SET is_refreshing = true WHERE mv_name = $1`,
        [mvName]
      );

      // Increase work_mem for complex aggregations
      await queryRunner.query(`SET work_mem = '128MB'`);

      // The magic: CONCURRENTLY allows reads during refresh
      await queryRunner.query(
        `REFRESH MATERIALIZED VIEW CONCURRENTLY ${mvName}`
      );

      // Update status
      await queryRunner.query(
        `UPDATE mv_refresh_status
         SET last_refresh_at = NOW(), is_refreshing = false
         WHERE mv_name = $1`,
        [mvName]
      );

      this.logger.log(`Refreshed ${mvName} successfully`);
    } catch (error) {
      await queryRunner.query(
        `UPDATE mv_refresh_status SET is_refreshing = false WHERE mv_name = $1`,
        [mvName]
      );
      throw error;
    } finally {
      await queryRunner.release();
    }
  }
}

---

Performance Results

Before vs After

Scenario	Before	After	Improvement
Is Active Subscriber	30s	50ms	600x faster
Subscriber + Wallet > $50	35s	80ms	437x faster
3+ Transactions in 30 days	45s	120ms	375x faster
Complex 5-filter segment	60s	200ms	300x faster

Production Metrics

┌────────────────────────────────────────────────────────┐
│         Segment Filter API - Production Stats          │
├────────────────────────────────────────────────────────┤
│                                                        │
│  Response Time (p50):    45ms   ████░░░░░░░░          │
│  Response Time (p95):    120ms  █████████░░░          │
│  Response Time (p99):    250ms  ████████████          │
│                                                        │
│  MV Refresh Time:        2-5s   ██░░░░░░░░░░          │
│  Data Staleness:         <90s   ████░░░░░░░░          │
│                                                        │
│  Daily Queries:          50,000+                       │
│  Error Rate:             <0.01%                        │
└────────────────────────────────────────────────────────┘

---

Handling Edge Cases

1. High-Volume Data Sync Window

Our system receives bulk operational data during a fixed daily window. We extend the debounce to avoid refresh storms:

private getDebounceMs(config: RefreshConfig): number {
  const currentHour = new Date().getHours();
  const isDuringBulkWindow = currentHour >= 13 && currentHour < 14;

  // 5 min debounce during bulk sync, normal otherwise
  return isDuringBulkWindow ? 300000 : config.debounceMs;
}

2. MV Doesn’t Exist Yet (New Tenant)

Graceful fallback to legacy query:

async getSegmentCount(tenantId: string, filters: Filter[]): Promise<number> {
  const connection = await this.getConnection(tenantId);
  const mvExists = await this.checkMVExists(connection, 'customer_attributes_mv');

  if (mvExists && this.canUseMV(filters)) {
    return this.queryMV(connection, filters);  // Fast path
  } else {
    return this.legacyQuery(connection, filters);  // Slow fallback
  }
}

3. Unsupported Filters

Some filters can’t be pre-computed (e.g., free-text search). We detect and route:

private canUseMV(filters: Filter[]): boolean {
  const MV_SUPPORTED = ['is_active_subscriber', 'wallet_balance', 'package_ids', 'location_codes'];
  return filters.every(f => MV_SUPPORTED.includes(f.attribute));
}

---

Lessons Learned

✅ What Worked

1. Separate MVs by refresh needs — Attributes (45s) vs Transactions (5min)
2. GIN indexes for arrays — Perfect for “any of these values” queries
3. CONCURRENTLY refresh — Zero query downtime
4. Status table pattern — Only refresh when data actually changed
5. Debouncing — Prevents refresh storms during bulk writes

⚠️ Gotchas

1. Index count matters — 30+ GIN indexes slow down refresh significantly
2. ARRAY_AGG ordering — Add ORDER BY for deterministic results
3. Multi-tenant complexity — Each DB needs separate MV management
4. Unique index required — CONCURRENTLY fails without it

---

Key Takeaways

1. Pre-compute what you filter on — Don’t make PostgreSQL work hard at query time
2. Array columns + GIN indexes — Perfect for multi-select filters
3. CONCURRENTLY is non-negotiable — Never block production reads
4. Staleness is acceptable — Most UIs can tolerate 1-2 minute delays
5. Monitor refresh times — They grow as data grows

For our use case, trading 1-2 minute data staleness for 300-600x performance improvement was an easy decision. Users went from abandoning the feature to actively using it for complex targeting campaigns.

---

This is part of a series on building high-performance systems. Check out my other posts on Azure Event Hub Architecture, LRU Connection Pooling, and CQRS in Practice.