Why should you care?

PostgresVerse AI team wants churn prediction features computed in SQL nightly and vectors for similarity in same database.

See it live — copy this example

Run in pgAdmin or psql.

CREATE TABLE events (
  event_id bigserial PRIMARY KEY,
  user_id bigint,
  event_type text,
  created_at timestamptz DEFAULT now()
);
CREATE TABLE user_churn_features (
  user_id bigint PRIMARY KEY,
  orders_30d int,
  embedding vector(4)
);
SELECT user_id, orders_30d FROM user_churn_features
WHERE orders_30d < 2 ORDER BY embedding <-> '[0.2,0.1,0.9,0.3]' LIMIT 20;

What happened?

• events capture behavior.
• user_churn_features holds engineered metrics and small embedding.
• Vector ORDER finds users similar to at-risk profile for campaign.

Practice next

1. Create tables and seed events.
2. Aggregate INSERT INTO user_churn_features SELECT ... GROUP BY user_id.
3. Run vector similarity query.
4. REFRESH MATERIALIZED VIEW for daily feature snapshot.
5. JOIN churn features to customers for email export.

Remember

OLTP events + feature table + vectors coexist. SQL builds batch features; pgvector serves similarity. Separate roles for ML pipeline vs API.

PostgresVerse churn model

Data scientist trains on SQL-exported features; marketing hits similar vectors same day.

Outcome: Single PostgresVerse source reduces feature skew.