Polymarket Data in Postgres

• books(ts timestamptz, market ref, bid numeric, ask numeric, bid_depth int, ask_depth int, vol int) — one wide row per 250ms frame.
• Index with BRIN on ts: it exploits the natural sort of append-only loads and stays small for terrabyte-scale tables.
• Normalize metadata separately: markets(slug, asset, type, resolution_time, outcome) joined on demand.
• For time-series workloads, TimescaleDB hypertable on ts gives you time_bucket for free — the same schema, chunked.

• Spread: SELECT time_bucket(1m, ts), median(ask - bid) FROM books WHERE market = ? GROUP BY 1.
• Last-frame replay: SELECT DISTINCT ON (market) * FROM books WHERE ts <= $1 ORDER BY market, ts DESC;
• One-sided ratio at settlement: count(*) FILTER (WHERE ask_depth = 0)/count(*) GROUP BY market over final windows.
• Cross-market: JOIN on date_trunc(second, ts) against exchange rows for same-second studies.

Bulk load via COPY from the exported CSV/Parquet, or from the s3 export directly; transactional INSERT ... ON CONFLICT for appends.

Set a retention: raw 250ms rows are worth keeping; hourly aggregates handle the rest — the archive sells raw access, your Postgres is your cache.

Free Starter reads 3 days of history at 250ms; Pro extends windows to 30–120 days for crypto and 30 for sports.

The wide books table with a BRIN index on ts is the core; add markets metadata (slug, type, resolution time) as a small normalized side table for joins in every dashboard.

Load with COPY from the exported CSV/Parquet in one pass; appends use ON CONFLICT (ts, market) DO NOTHING so replays are safe.

Insert counts per pull should equal (end - start) in seconds x 4 for 250ms; any drift means window math is off and downstream settlement stats inherit the error.

TimescaleDB converts the same schema to a hypertable with time_bucket for free when the series becomes the primary workload.

Postgres is the boring, correct choice: a wide table, a BRIN index on ts, and a metadata join are enough for months of 250ms rows queried in milliseconds, and the whole stack is maintenance everyone on the team already knows.

The design honesty is in the healthy split — the SQL server holds the working copy; the archive remains the canonical full record beyond what a transactional database wants to carry.

The last-frame replay via DISTINCT ON is the single most-used query in practice; it covers mid-over-time for any historical instant without an index change.

First load uses the CSV export for one market, one day at 250ms (34,560 rows), COPY into the wide table, then query DISTINCT ON for the last frame of the day to confirm the window boundary.

Add the CHECK constraint on price bounds before the second load, so any parse slip fails at ingest, and confirm BRIN is actually being used with EXPLAIN on a ts range query.

Postgres is the middle step that most teams should actually use: a wide table with a BRIN index, a tiny dimension table, and the DISTINCT ON replay cover months of 250ms rows in milliseconds with zero new infrastructure.

The design choices that cost nothing at load and everything later are the constraint (0 to 1 prices), the UTC timestamp discipline, and the one-sided view that downstream dashboards read. Each is a one-time decision with a permanent payoff.

Move to TimescaleDB only when time_bucket becomes a daily habit; until then the plain schema is simpler to explain, back up, and hire for.

• API data guide
• Data download
• Order book data
• MCP server