Feature Store on a Budget: Serve ML Features from Postgres

For: A mid-level ML engineer at a 30–80-person B2B SaaS company who has two models in production but no dedicated MLOps team — features are being recomputed on every inference request directly from raw tables, latency is climbing, and their tech lead just asked why training and serving disagree on feature values

You don't need Feast or Tecton to stop training-serving skew. If features are being recomputed from raw tables on every prediction, the fix is a single append-only feature table in your existing Postgres, keyed on (entity_id, event_timestamp), that both your training pipeline and your inference API read from. That's it — that's the feature store. The rest of this post is the schema, the queries, and the discipline that makes it work.

This tutorial assumes you have two models running, no MLOps engineer, and a Postgres instance somewhere between 20GB and 500GB. If that's you, keep reading. If you're serving 50k predictions per second across 200 features, you've outgrown this pattern — go look at Feast.

Why your training and serving values disagree

When features are computed on-demand from raw tables at inference time, three things go wrong. First, the SQL you wrote in the training notebook is subtly different from the SQL in the API handler — one uses NOW(), the other uses the row's created_at. Second, late-arriving data quietly changes historical feature values, so a model trained yesterday sees different features than one trained today for the same event. Third, aggregations over sliding windows drift because nobody agrees on the boundary.

None of this is a tooling problem. It's a write-discipline problem. The moment you materialize features once, timestamped, and never mutate them, the skew disappears.

The core idea: features as an append-only log

Most teams try to fix skew by building a user_features table with one row per user, updated in place. This is the wrong shape. Updates destroy the historical values a training set needs, and they invite race conditions between the training job and the serving path.

Instead: one row per (entity, snapshot_time). New feature values append. Old values stay. Training does a point-in-time join. Serving reads the latest row per entity. Same table, same code path, no skew.

Prerequisites

• Postgres 12+ (14+ preferred for better partitioning)
• A raw events table you can aggregate over — orders, sessions, clicks, whatever
• Python 3.9+ with psycopg2 or asyncpg
• Roughly 10x the disk headroom of your current feature footprint (append-only means growth)
• A way to run a scheduled job — cron, Airflow, a GitHub Action, doesn't matter

Step 1: Create the feature table

We'll model this for a B2B SaaS scenario — predicting churn for workspace accounts. Features: 7-day active user count, 30-day event volume, days since last login.

CREATE TABLE workspace_features (
  workspace_id   BIGINT      NOT NULL,
  event_ts       TIMESTAMPTZ NOT NULL,
  active_users_7d       INT,
  events_30d            INT,
  days_since_last_login INT,
  computed_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (workspace_id, event_ts)
) PARTITION BY RANGE (event_ts);

CREATE TABLE workspace_features_2024 PARTITION OF workspace_features
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE workspace_features_2025 PARTITION OF workspace_features
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

CREATE INDEX ON workspace_features (workspace_id, event_ts DESC);

Two things worth noting. The primary key is composite — no id column, because the semantic key is entity plus time. The DESC index makes the "get latest features for this workspace" query an index-only lookup.

Expected output:

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX

Step 2: Write the feature computation as one deterministic query

This is the query both training and the scheduled materialization job will use. Never write it twice.

-- feature_query.sql
-- Params: :as_of (timestamptz)
SELECT
  w.id AS workspace_id,
  :as_of::timestamptz AS event_ts,
  COUNT(DISTINCT CASE WHEN e.created_at >= :as_of::timestamptz - INTERVAL '7 days'
                      THEN e.user_id END) AS active_users_7d,
  COUNT(CASE WHEN e.created_at >= :as_of::timestamptz - INTERVAL '30 days'
             THEN 1 END) AS events_30d,
  EXTRACT(DAY FROM :as_of::timestamptz - MAX(e.created_at))::INT AS days_since_last_login
FROM workspaces w
LEFT JOIN events e
  ON e.workspace_id = w.id
  AND e.created_at < :as_of::timestamptz
WHERE w.deleted_at IS NULL
GROUP BY w.id;

The critical detail: e.created_at < :as_of. Never <=, never NOW(). Point-in-time correctness lives or dies on this predicate.

Step 3: Schedule the materialization

Run this hourly. Or every 15 minutes. Whatever your freshness budget allows.

# materialize.py
import psycopg2
from datetime import datetime, timezone

conn = psycopg2.connect(DSN)
as_of = datetime.now(timezone.utc).replace(minute=0, second=0, microsecond=0)

with open('feature_query.sql') as f:
    feature_sql = f.read()

with conn.cursor() as cur:
    cur.execute(f"""
      INSERT INTO workspace_features
        (workspace_id, event_ts, active_users_7d, events_30d, days_since_last_login)
      {feature_sql}
      ON CONFLICT (workspace_id, event_ts) DO NOTHING
    """, {'as_of': as_of})
    conn.commit()
    print(f"Materialized {cur.rowcount} rows at {as_of}")

Expected output:

Materialized 4213 rows at 2025-03-14 09:00:00+00:00

ON CONFLICT DO NOTHING means reruns are safe. The snapshot for 09:00 UTC will never differ between runs, because the query is deterministic on as_of.

Step 4: Serve features at inference time

Your API handler does one query. No aggregations, no joins to raw tables.

# serve.py
GET_LATEST_FEATURES = """
  SELECT active_users_7d, events_30d, days_since_last_login
  FROM workspace_features
  WHERE workspace_id = %s
  ORDER BY event_ts DESC
  LIMIT 1;
"""

def get_features(workspace_id: int):
    with pool.connection() as conn, conn.cursor() as cur:
        cur.execute(GET_LATEST_FEATURES, (workspace_id,))
        row = cur.fetchone()
    if row is None:
        raise FeatureMissError(workspace_id)
    return {'active_users_7d': row[0], 'events_30d': row[1],
            'days_since_last_login': row[2]}

With the (workspace_id, event_ts DESC) index this is a single-page index lookup. On a modest instance you should see sub-millisecond latency at p50 and low single-digit ms at p99. If you're not, run EXPLAIN ANALYZE — you probably forgot the DESC.

Step 5: Build training sets with point-in-time joins

This is where teams without a feature store usually cheat and use current feature values against historical labels. Don't. Your training set gets built like this:

-- Given a labels table (workspace_id, label_ts, churned)
SELECT
  l.workspace_id,
  l.label_ts,
  l.churned,
  f.active_users_7d,
  f.events_30d,
  f.days_since_last_login
FROM churn_labels l
JOIN LATERAL (
  SELECT active_users_7d, events_30d, days_since_last_login
  FROM workspace_features
  WHERE workspace_id = l.workspace_id
    AND event_ts <= l.label_ts
  ORDER BY event_ts DESC
  LIMIT 1
) f ON TRUE;

The LATERAL subquery grabs the feature snapshot that was current at label time. This is what your model would have seen in production, which is what training on it should reproduce. Training-serving skew: eliminated by construction.

Step 6: Add a monitoring view

You want to know when the materialization stalls before your model does.

CREATE VIEW feature_freshness AS
SELECT
  MAX(event_ts) AS latest_snapshot,
  NOW() - MAX(event_ts) AS staleness,
  COUNT(DISTINCT workspace_id) AS entities_in_latest
FROM workspace_features
WHERE event_ts > NOW() - INTERVAL '2 days';

Point your existing alerting at staleness > INTERVAL '2 hours' and you have SLA monitoring for free.

Step 7: Partition maintenance and retention

Append-only means your table grows forever unless you do something. Two options: drop old partitions once labels are frozen, or move them to cheap storage.

-- After 18 months, drop:
DROP TABLE workspace_features_2023;

-- Or detach and archive:
ALTER TABLE workspace_features DETACH PARTITION workspace_features_2023;

Set a calendar reminder. This is the one piece of ongoing work the pattern demands.

Common errors and fixes

"Serving latency is fine at p50 but terrible at p99"

You're hitting a cold partition. Increase shared_buffers, or add a covering index: CREATE INDEX ... (workspace_id, event_ts DESC) INCLUDE (active_users_7d, events_30d, days_since_last_login). Now the query is index-only and never touches the heap.

"Training set has NULL features for some rows"

Labels exist before the first materialization ran for that entity. Either backfill the feature table for the historical range (same query, loop over as_of values), or filter labels to label_ts >= (SELECT MIN(event_ts) FROM workspace_features).

"ON CONFLICT DO NOTHING is silently dropping updates I want"

You've violated the append-only rule. If you genuinely need to correct a snapshot (bad code shipped), don't UPDATE — insert a new row with a slightly later event_ts and a corrected_from column. Preserve history.

"Feature computation query takes 40 minutes"

You're scanning the full events table on every run. Two fixes: (1) incremental materialization — only aggregate events since the last snapshot and add to the previous — but this is harder to get right. (2) partition events by created_at too, so the 30-day window scan hits only recent partitions. Do (2) first.

"Two features that used to correlate at 0.8 now correlate at 0.3"

Check computed_at vs event_ts in your feature table. A materialization job probably ran late, so the "9am snapshot" was actually computed at 11am against different raw data than usual. The point-in-time join in Step 5 should insulate training from this, but if it doesn't, you're joining on computed_at somewhere by accident.

What this pattern is bad at

Honest tradeoffs. This approach fails when:

• Sub-millisecond p99 at high QPS. Postgres index lookups are fast but not Redis-fast. Above ~5k feature-fetches per second you'll want an in-memory tier.
• Streaming features. If your model needs the last 30 seconds of user behavior, a batch materialization every 15 minutes won't cut it. You need Kafka + a stream processor.
• Hundreds of features across dozens of entities. The pattern scales to maybe 30-50 features across a handful of entity types. Beyond that, the schema management gets painful and Feast starts earning its complexity.
• Feature discovery across teams. There's no catalog, no lineage UI, no governance. If ten data scientists need to find and reuse features, you want a real feature platform.

For a 30-80-person SaaS with two models, none of these matter yet. Ship this, buy yourself 18 months, revisit when the constraints actually bite.

How CodeNicely can help

Most of the ML infrastructure work we do isn't building the fancy platform — it's fixing the boring plumbing that's silently degrading model accuracy. When we worked on GimBooks, a YC-backed accounting SaaS, a lot of the value was in exactly this kind of data-discipline work: making sure the same numbers showed up in the same place regardless of which service asked. If your ML team is two people, your Postgres is already load-bearing, and you'd rather not adopt a new platform this quarter, that's the shape of engagement we're good at. Take a look at our AI Studio if you want a broader sense of the work.

Frequently Asked Questions

When should I graduate from Postgres to a real feature store like Feast?

Three signals: you're serving more than roughly 5,000 feature-fetches per second, you have more than 50 features across multiple entity types, or you have multiple ML teams needing to discover and reuse each other's features. Below those thresholds, Postgres wins on operational simplicity. Above them, the metadata and streaming capabilities of a dedicated feature store start paying for themselves.

Can I use this pattern with a read replica to isolate serving load?

Yes, and you should once serving traffic is non-trivial. Point your inference API at a read replica and the materialization job at the primary. Replication lag of a few seconds is usually fine for ML features — you're already tolerating batch materialization intervals of minutes.

How do I handle features that need to combine data from multiple services in a microservices setup?

You need a data pipeline that lands the raw data in one place first — usually your warehouse or an analytics Postgres — before you compute features on top. Trying to do point-in-time joins across live microservice databases is where teams get into trouble. Solve the data-consolidation problem first, then the feature store pattern applies cleanly.

Does this pattern work for real-time features that need to reflect the last few seconds of activity?

No. A scheduled materialization job has a floor freshness of whatever your schedule is — 15 minutes, an hour. If your model genuinely needs sub-minute freshness, you need a streaming layer. Be honest about whether you actually need that; many teams assume they do and their model would perform identically on hourly features.

How much will it cost to migrate our current ad-hoc feature computation to this pattern?

That depends on how many features you have, how tangled the current computation code is, and whether your raw events schema needs cleanup first. Contact CodeNicely for a personalized assessment — we'll look at your current setup and give you a concrete plan.

The whole pattern fits on one page: append-only table keyed on entity plus timestamp, one deterministic query used by both materialization and training, point-in-time joins with LATERAL, latest-row lookup for serving. If your team can hold that discipline, you have a feature store. The word "Feast" doesn't need to appear in your stack diagram.