How to run A/B tests on your Postgres data

Postgres can support a rigorous A/B testing workflow, but the database serving customer requests should not become an unbounded analytics cluster by accident.

For a small product, the cleanest source of experiment data may already be PostgreSQL. Accounts, subscriptions, orders, and application events live close together. SQL is transparent, and a warehouse migration may not yet be justified.

The statistical requirements are the same as in a dedicated warehouse: randomized assignment, symmetric exposure, stable identity, outcomes measured after exposure, aggregation at the randomization unit, and quality checks before lift. The additional challenge is operational. Experiment queries can scan long histories and join large facts while the same database is processing writes and latency-sensitive reads.

This guide builds a reliable Postgres analysis shape, then adds guardrails for permissions, indexes, replicas, query timeouts, and the point at which to graduate to an analytical warehouse.

Design the data contract before the schema

Each exposure needs an experiment key, variation, assigned unit, and timestamp. Add environment, source, feature revision, and pre-treatment dimensions when they help diagnose routing.

CREATE TABLE analytics.experiment_exposures (
  experiment_id text NOT NULL,
  variation_id text NOT NULL,
  user_id uuid NOT NULL,
  exposed_at timestamptz NOT NULL,
  source text NOT NULL,
  PRIMARY KEY (experiment_id, user_id, variation_id, exposed_at)
);

CREATE INDEX experiment_exposures_lookup
  ON analytics.experiment_exposures
  (experiment_id, exposed_at, user_id);

The primary key above prevents exact duplicates, but it does not decide how to handle repeat exposure. Analysis normally uses the first valid exposure per unit and flags units seen in multiple variations.

GrowthBook's feature flag experiment workflow links randomized variation assignment to exposure tracking. Whatever assignment system you use, log exposure at the first equal opportunity to experience either arm—not only after a treatment-specific action succeeds.

Match IDs to the randomization unit

If assignment is by account, store account_id and aggregate account outcomes once. If it is by user, do not join through a many-to-many account history that duplicates users. The experimental-unit guide explains why uncertainty must be calculated at the independently randomized level.

Use UTC timestamps and explicit attribution

Store timestamptz, define the experiment phase in one time zone, and measure outcomes only after exposure. A 7-day conversion metric also needs a maturity rule so recently exposed units are not compared with users who had the full window.

Build one row per unit before estimating effects

The safest SQL shape deduplicates exposure, aggregates outcomes to the unit, then summarizes by variation.

WITH first_exposure AS (
  SELECT DISTINCT ON (user_id)
    user_id,
    variation_id,
    exposed_at
  FROM analytics.experiment_exposures
  WHERE experiment_id = $1
    AND exposed_at >= $2::timestamptz
    AND exposed_at < $3::timestamptz
  ORDER BY user_id, exposed_at
),
unit_metric AS (
  SELECT
    e.variation_id,
    e.user_id,
    (COUNT(o.order_id) FILTER (
      WHERE o.completed_at >= e.exposed_at
        AND o.completed_at < e.exposed_at + interval '7 days'
    ) > 0)::int AS converted
  FROM first_exposure e
  LEFT JOIN commerce.orders o ON o.user_id = e.user_id
  GROUP BY e.variation_id, e.user_id
)
SELECT
  variation_id,
  COUNT(*) AS units,
  SUM(converted) AS conversions,
  AVG(converted::numeric) AS conversion_rate
FROM unit_metric
GROUP BY variation_id;

This example uses bound parameters, retains non-converters through a left join, and calculates one binary outcome per user. It is a starting point, not a complete statistical engine. Ratio metrics, repeated measures, winsorization, variance reduction, sequential monitoring, and multiple comparisons need consistent implementations.

GrowthBook's metric system and fact tables let teams define reusable warehouse metrics rather than hand-maintaining one result query per experiment.

Standardize the metrics that matter

Define primary outcomes and guardrails before a query result becomes a product decision.

Read the KPI Playbook

Protect Postgres from analytical load

Prefer a read replica

A replica isolates most experiment reads from the primary's CPU and I/O, but it is not free isolation. Long-running queries can create resource pressure, and replay lag means recent exposures or outcomes may be missing. Record replica lag with each refresh and define when results are mature enough to read.

If analysis must touch the primary, begin with a narrow pilot. Set a workload-specific statement_timeout, use lock_timeout, and ensure queries do not request row locks. Canceling an analytical query is better than degrading checkout.

Inspect plans with EXPLAIN

Use EXPLAIN (ANALYZE, BUFFERS) in a safe environment or carefully on production-shaped data. PostgreSQL's EXPLAIN documentation shows how plans reveal sequential scans, join strategies, estimates, and actual timing.

Index the predicates and joins that materially reduce work: experiment ID and exposure time, unit IDs, and outcome timestamps. Avoid speculative indexes on every dimension; each index adds storage and write overhead.

Pre-aggregate repeated work

If every refresh scans raw events, create daily unit-level facts or a materialized view. PostgreSQL's materialized-view documentation notes the tradeoff: persisted results can be much faster, but may not be current.

Make freshness visible. Refresh concurrently where the schema and unique index allow it, or build an incremental fact pipeline. Never present a cached experiment result without the source cutoff time.

Watch transactions and replica lag

Monitor query duration, rows read, temporary files, CPU, I/O, connection saturation, lock waits, and replica replay delay. An analysis connection pool needs a hard maximum. Experiment dashboards should not open dozens of unconstrained database sessions.

Community discussions about Postgres versus a dedicated warehouse rightly focus on workload scale and operational tradeoffs. The decision is not that Postgres “cannot do analytics”; it is whether the analytical workload fits the reliability envelope of this database.

Create least-privilege access

Do not connect an experimentation platform as an application owner or superuser. Create curated views and a read-only role.

CREATE ROLE experiment_reader LOGIN PASSWORD 'use-a-secret-manager';
GRANT CONNECT ON DATABASE appdb TO experiment_reader;
GRANT USAGE ON SCHEMA analytics TO experiment_reader;
GRANT SELECT ON analytics.experiment_exposures,
                analytics.purchase_facts
TO experiment_reader;

ALTER ROLE experiment_reader SET statement_timeout = '60s';
ALTER ROLE experiment_reader SET lock_timeout = '2s';
ALTER ROLE experiment_reader SET default_transaction_read_only = on;

Use a secret manager rather than placing a literal password in migration code. The PostgreSQL privilege model supports granular SELECT grants on tables, views, materialized views, or columns.

Views can expose approved columns and standardized filters. Review owner and invoker behavior: PostgreSQL's CREATE VIEW documentation explains how security_invoker changes underlying permission and row-security checks.

If row-level security is required, test it as the actual analysis role. PostgreSQL row security policies default to denying rows when RLS is enabled but no applicable policy exists; owners and roles with bypass privileges behave differently from ordinary readers.

Connect GrowthBook to Postgres

GrowthBook's warehouse-native platform is designed to analyze data in connected sources. For a Postgres implementation:

1. Point the connection at an approved replica or analytical instance.
2. Use the dedicated reader and require encrypted transport.
3. Define the experiment-assignment query over the curated exposure model.
4. Define fact tables, dimensions, and metrics with explicit windows.
5. preview generated SQL and run it under the reader role.
6. Reconcile counts with application logs and a direct SQL query.
7. Run an A/A test before relying on the pipeline for a risky launch.

GrowthBook's experiment analysis can surface warehouse-backed results and diagnostics, but database monitoring remains your responsibility. Tag or identify the connection so queries can be attributed in pg_stat_activity and logs.

Validate the experiment before reading lift

Begin with allocation and exposure health:

• compare expected and observed variation counts;
• identify units in multiple variations;
• check null IDs and timestamp boundaries;
• compare join rates by arm;
• verify outcomes never precede exposure;
• compare pre-treatment metrics and invariant dimensions;
• confirm the analysis cutoff and replica freshness.

GrowthBook's sample ratio mismatch check can flag unexpected allocation. Stop interpreting treatment effects until its cause is understood.

Next, inspect the estimate and interval against a practical decision threshold. Statistical significance alone does not say whether an effect is valuable. Include guardrails for latency, errors, refunds, or support costs, and account for multiple metrics with a declared decision rule.

Know when Postgres is no longer the right analysis engine

Move or replicate experiment data into an analytical warehouse when:

• raw event tables grow faster than operational retention allows;
• concurrent experiment refreshes create load spikes;
• joins spill to disk or regularly exceed timeouts;
• replica lag makes results misleading;
• long retention or cohort queries compete with application work;
• teams need independent scaling, governance, and cost attribution;
• metrics increasingly combine CRM, billing, support, and product events.

Keep the semantic contracts when you move: exposure meaning, unit ID, metric windows, and validation queries should not change silently. Run both pipelines on the same completed experiments and explain every material difference before cutover.

Postgres is a sensible beginning when data volume and operating discipline fit. A dedicated analytical system is the sensible next step when the experiment program becomes a threat to transactional reliability. The goal is not loyalty to a database; it is an inspectable causal comparison that never puts the product at risk.

Operate the pipeline without surprising the application

Schedule experiment refreshes through a queue with concurrency limits instead of letting every dashboard request launch analytical SQL. Cache completed summaries for the UI, and provide an explicit refresh action for authorized users. A product page should never hold an application connection open while a large cohort query runs.

Set statement_timeout, lock_timeout, and an application name on the analysis role. Route it to a replica when freshness and recovery objectives allow. Track query duration, rows, temporary-file use, buffer reads, replica lag, cancellations, and connection-pool saturation. A query that finishes in staging can behave differently when production tables, autovacuum, and concurrent traffic are involved.

Treat replica lag as part of metric maturity. If exposure rows arrive before outcome rows, a treatment can appear worse simply because its traffic is newer or written through a different path. Record the source replay position or analysis cutoff and compare lag across the full time window.

Use materialized facts selectively. A daily user-metric table can remove repeated event joins, but its refresh needs ownership, idempotency, late-data handling, and a visible timestamp. Rebuild a bounded period when source corrections arrive rather than appending contradictory versions.

Rehearse recovery. Cancel a long-running POC query, pause the replica, rotate the credential, and introduce a compatible then incompatible schema change. The system should fail visibly, preserve the last verified result as stale, and recover without widening privileges or blocking transactional work.

Harden the metric queries like application code

Keep experiment SQL in version control with named parameters, code review, fixtures, and regression tests. Include cases for no outcome, multiple exposures in one arm, exposure to two arms, events exactly on window boundaries, refunds, null identifiers, and late-arriving rows.

Inspect EXPLAIN (ANALYZE, BUFFERS) on production-shaped data through the intended role and replica. Check row-estimate errors, nested-loop growth, repeated scans, sorting, and temporary files. A safe plan should remain bounded as the exposure window and number of concurrent metrics expand.

Avoid long-lived analytical transactions. They can retain old row versions and interfere with vacuum even on a replica topology. Fetch compact aggregate results, close transactions promptly, and expose result freshness separately from request latency.

Preserve a decision artifact

For each experiment, store the hypothesis, eligible population, randomization unit, variation definitions, first-exposure rule, metric versions, analysis cutoff, query commit, statistical settings, guardrails, and final action. Link the code rollout and cleanup work.

If late data or a query correction produces a restatement, keep both the original and revised result with an explanation. This allows future teams to improve the data model without erasing the evidence that informed an earlier product decision.

The artifact also makes database migration safer: a new warehouse pipeline can reproduce known inputs and summaries before it becomes authoritative.

Review a sample of completed artifacts each quarter. Re-run the bounded reference query, confirm the retained metric version still resolves, and check that the cleanup link represents the permanent code path. Also inspect slow-query and replica-lag trends from the experimentation role.

This maintenance catches quiet drift: a view owner changes, an identity mapping is rewritten, or a query that was safe at launch grows into a reliability risk. The experiment program should evolve before the primary database becomes its alerting system.

Analyze tests on data you own

Connect Postgres metrics to a transparent experimentation workflow and validate the pipeline with a low-risk test.

Start Building Free