Polymarket Data in ClickHouse

• Bulk: the REST/s3 export writes Parquet or CSV, then INSERT ... SELECT FROM file or the s3 table function loads it.
• Streaming: a small writer appends rows as they arrive; the archive pattern is append-only with monotonic sequences.
• Partition by toStartOfDay(ts) and sort by (market, ts) for point-in-time and market-range queries.
• Use ReplacingMergeTree only if you must dedupe on (market, ts); plain MergeTree is usually right.

• Point-in-time: SELECT-bid-ask-AS-OF at a given ts for one market — the core replay primitive.
• Settlement: group final-minute frames per market and compute one-sided proportions.
• Liquidity over time: median of (ask - bid), top-of-book depth by hour and market type.
• Cross-market: join on UTC-second between Polymarket rows and exchange series.

A year of crypto and sports 250ms books runs to billions of rows; ClickHouse crunches them in seconds with the coarse schema above. Aggregate hourly and keep raw rows on parquet for reprocessing.

Books, prices, and metrics come back over the same endpoints at the same 250ms resolution, so a dashboard needs one schema, not three.

Define a MergeTree with ORDER BY (market, ts) and PARTITION BY toStartOfDay(ts); bulk-load from Parquet via the s3/URL table functions; a worker appends new frames as they are captured.

The AS-OF query that returns the last row at or before a timestamp is indexed by the sort key, making replays of any past second instant.

Verify the ingest counter: 1 day x 24h x 14,400 250ms frames per market equals expected rows; off-by-factor-500 means the resolution param was dropped and frames landed at ms.

At billion-row scale the queries that crawl in Postgres run in milliseconds here, which is the honest line between the two.

ClickHouse is the scale-up answer: a billion 250ms frames, a shared server, and point-in-time replays that stay instant because the (market, ts) sort key is the query path.

The honest trade is operational weight — you run a server, so you only migrate when DuckDB/Postgres genuinely thrashes, which for most teams is never.

Once the materialized hourly views exist, dashboards read views and researchers read raw; the same machine serves both without contention.

First real task is the point-in-time replay: pick an arbitrary past second, ask for the last frame at or before it for one market, and confirm it agrees with the CSV export at the same instant.

Then build the hourly materialized view (spread, depth, volume per market-hour) and check it against a DuckDB query over the same files; when the two agree, the pipeline is honest.

ClickHouse earns its keep when the record is genuinely large: point-in-time replays at a chosen past second, billion-row settlement scans, and shared, governed access all deserve a server that owns a sort key built for the job.

The migration test is cheap and decisive: run the same settlement query from Postgres and ClickHouse over a multi-month window. When ClickHouse wins by orders of magnitude and the materialized hourly views keep dashboards off raw rows, the operational cost of the server is justified.

Keep the raw frames in Parquet alongside the live table so reprocessing never depends on the serving system; that split — raw for truth, ClickHouse for speed — is the durable architecture behind every big deployment.

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