How to Build a Complete Polymarket Database

You have an API key and you've seen the Parquet dumps. Now what? This guide walks you through building a complete Polymarket database from scratch and keeping it in sync with one daily job. By the end, you'll have every trade, position, split, merge, and redemption since 2022 in your own database, refreshed every morning.

What you'll build

The end result is a PostgreSQL database with one table per dataset:

Every fill, every payout, every position, going back to 2022 — living in your database, where you can index it, join it with your own data, and query it however you want.

How the dumps are organized

Everything comes from two endpoints: GET /dumps lists every file you can download, and GET /dumps/{path} downloads one. There are two kinds of files:

• Daily files — one Parquet file per table per UTC day, e.g. order_filled_events_v2/20261008.parquet. A day's file is published once that day is over, in the nightly build (05:00 UTC). After that it normally never changes.
• Snapshots — one file at a fixed path, overwritten every day: positions/positions_current.parquet, markets/markets.parquet, parlay_legs/parlay_legs.parquet, and the UMA resolution files under uma/.

The listing gives you each file's path, size in bytes, and mtime (Unix seconds of its last write):

curl -s -H "x-api-key: YOUR_KEY" https://api.predmktdata.com/dumps | python3 -m json.tool

{
    "files": [
        {"path": "markets/markets.parquet", "size": 229687311, "mtime": 1791523812},
        {"path": "order_filled_events_v2/20261008.parquet", "size": 151204977, "mtime": 1791522106},
        ...
    ],
    "base_url": "/dumps/"
}

That's all you need to stay in sync. A file you don't have yet, or whose size or mtime differs from the copy you downloaded, is a file to fetch. The same rule covers new days, the daily snapshots, and the occasional past file we regenerate after a correction — so none of them needs special handling.

Step 1: Create the schema

Create a PostgreSQL database and the tables. Columns follow the dump files exactly, in the same order, so loading is a plain SELECT *. Amounts are BIGINT in raw on-chain units — divide by 1e6 for USDC. Timestamps are UTC.

Each event table gets one extra column, source_file, that records which dump file a row came from. It's what makes re-runs safe — more on that below the schema.

CREATE DATABASE polymarket;

-- Fills, v1 exchanges (Nov 2022 → Apr 28, 2026; frozen)
CREATE TABLE order_filled_events (
    timestamp           TIMESTAMP,   -- UTC
    transaction_hash    TEXT,
    block_number        BIGINT,
    maker               TEXT,
    taker               TEXT,
    maker_asset_id      TEXT,
    taker_asset_id      TEXT,
    maker_amount_filled BIGINT,
    taker_amount_filled BIGINT,
    fee                 BIGINT,
    side                TEXT,        -- 'buy' / 'sell'
    source_file         TEXT
);

-- Fills, v2 + v3 exchanges (Apr 2026 → today, all new trading)
CREATE TABLE order_filled_events_v2 (
    timestamp           TIMESTAMP,
    transaction_hash    TEXT,
    block_number        BIGINT,
    exchange            TEXT,
    order_hash          TEXT,
    maker               TEXT,
    taker               TEXT,
    side                INTEGER,     -- 0 = buy, 1 = sell
    token_id            TEXT,
    maker_amount_filled BIGINT,
    taker_amount_filled BIGINT,
    fee                 BIGINT,
    builder             TEXT,
    metadata            TEXT,
    source_file         TEXT
);

CREATE TABLE payout_redemptions (
    timestamp           TIMESTAMP,
    transaction_hash    TEXT,
    block_number        BIGINT,
    redeemer            TEXT,
    condition_id        TEXT,
    payout              BIGINT,
    source_file         TEXT
);

CREATE TABLE position_splits (
    timestamp           TIMESTAMP,
    transaction_hash    TEXT,
    block_number        BIGINT,
    stakeholder         TEXT,
    condition_id        TEXT,
    amount              BIGINT,
    source_file         TEXT
);

CREATE TABLE position_merges (LIKE position_splits);

CREATE TABLE position_conversions (
    timestamp           TIMESTAMP,
    transaction_hash    TEXT,
    block_number        BIGINT,
    stakeholder         TEXT,
    condition_id        TEXT,
    index_set           BIGINT,
    amount              BIGINT,
    source_file         TEXT
);

-- Parlay cash-outs
CREATE TABLE combinatorial_redemptions (
    timestamp           TIMESTAMP,
    transaction_hash    TEXT,
    block_number        BIGINT,
    recipient           TEXT,
    position_id         TEXT,
    amount              BIGINT,
    payout              BIGINT,
    source_file         TEXT
);

-- Last traded price per token per UTC day
CREATE TABLE daily_token_prices (
    day                 TEXT,        -- 'YYYYMMDD'
    token_id            TEXT,
    price               DOUBLE PRECISION,  -- USDC per share, 0-1
    source_file         TEXT
);

-- Snapshots
CREATE TABLE positions (
    user_address        TEXT,
    token_id            TEXT,
    amount              BIGINT,
    avg_price           BIGINT,      -- x 1e6
    realized_pnl        BIGINT,
    total_bought        BIGINT,
    last_block          BIGINT,
    PRIMARY KEY (user_address, token_id)
);

CREATE TABLE markets (
    condition_id        TEXT PRIMARY KEY,
    question            TEXT,
    outcome_yes         TEXT,
    outcome_no          TEXT,
    yes_token_id        TEXT,
    no_token_id         TEXT,
    market_slug         TEXT,
    end_date_iso        TEXT,
    neg_risk            BOOLEAN
);

-- Lets the loader find one file's rows quickly
CREATE INDEX ON order_filled_events (source_file);
CREATE INDEX ON order_filled_events_v2 (source_file);
CREATE INDEX ON payout_redemptions (source_file);
CREATE INDEX ON position_splits (source_file);
CREATE INDEX ON position_merges (source_file);
CREATE INDEX ON position_conversions (source_file);
CREATE INDEX ON combinatorial_redemptions (source_file);
CREATE INDEX ON daily_token_prices (source_file);

Step 2: Download — one sync script

The same script does the initial backfill and every daily run. It compares the listing with a small state file of what you already have, and downloads only what's new or changed, 8 files at a time. Downloads go to a temporary file first, so an interrupted run never leaves a half-written Parquet file behind.

# sync_dumps.py
import concurrent.futures, json, os, requests

API = "https://api.predmktdata.com"
KEY = os.environ["PREDMKTDATA_API_KEY"]
OUT = "dumps"
STATE = os.path.join(OUT, "state.json")        # path -> [size, mtime] you have
CHANGED = os.path.join(OUT, "changed.txt")    # files the loader still has to load

# Only the tables you load (see the note below)
WANT = (
    "order_filled_events/", "order_filled_events_v2/", "payout_redemptions/",
    "position_splits/", "position_merges/", "position_conversions/",
    "combinatorial_redemptions/", "daily_token_prices/",
    "positions/", "markets/",
)

os.makedirs(OUT, exist_ok=True)
listing = requests.get(f"{API}/dumps", headers={"x-api-key": KEY}).json()["files"]
state = json.load(open(STATE)) if os.path.exists(STATE) else {}

todo = [f for f in listing
        if f["path"].startswith(WANT)
        and state.get(f["path"]) != [f["size"], f.get("mtime")]]
print(f"{len(todo)} files to fetch, {sum(f['size'] for f in todo) / 1e9:.1f} GB")

def download(f):
    # 1) the API answers with a 302 to a presigned URL (valid 60 min)
    r = requests.get(f"{API}/dumps/{f['path']}",
                     headers={"x-api-key": KEY}, allow_redirects=False)
    r.raise_for_status()
    # 2) stream the file to disk, then move it into place
    path = os.path.join(OUT, f["path"])
    os.makedirs(os.path.dirname(path), exist_ok=True)
    with requests.get(r.headers["location"], stream=True) as s:
        s.raise_for_status()
        with open(path + ".part", "wb") as fh:
            for chunk in s.iter_content(1 << 20):
                fh.write(chunk)
    os.replace(path + ".part", path)

changed = []
with concurrent.futures.ThreadPoolExecutor(max_workers=8) as pool:
    jobs = {pool.submit(download, f): f for f in todo}
    for job in concurrent.futures.as_completed(jobs):
        f = jobs[job]
        try:
            job.result()
        except Exception as e:
            print("FAILED", f["path"], e)   # retried on the next run
            continue
        state[f["path"]] = [f["size"], f.get("mtime")]
        changed.append(f["path"])

json.dump(state, open(STATE, "w"))
with open(CHANGED, "a") as fh:                  # append: survives a failed load
    fh.writelines(p + "\n" for p in changed)
print(f"downloaded {len(changed)} files")

The first run downloads the full history — the script prints the total size before it starts, and the fills tables are by far the largest part. Every later run fetches only the new day's files plus whatever snapshots changed. The positions snapshot is several GB on its own, so drop "positions/" from WANT if you don't need it refreshed every day.

If you'd rather check a single snapshot without the listing, conditional requests work too: storage returns an ETag with every file, and sending it back as If-None-Match on the presigned URL gets a bodyless 304 when nothing changed. It still costs one GET /dumps/{path} call to get the URL, so the listing's mtime stays the cheapest check.

url = requests.get(f"{API}/dumps/markets/markets.parquet",
                   headers={"x-api-key": KEY}, allow_redirects=False).headers["location"]
r = requests.get(url, headers={"If-None-Match": saved_etag})
if r.status_code == 304:
    print("markets unchanged")
else:
    open("dumps/markets/markets.parquet", "wb").write(r.content)
    saved_etag = r.headers["ETag"]

Step 3: Load — one loader

DuckDB is the easiest way to move Parquet into PostgreSQL: it reads Parquet natively and writes straight into Postgres through its postgres extension. The loader reads the list of changed files from the sync step and reloads each event file in its own transaction — delete whatever that file loaded before, insert the file again. The markets snapshot is replaced wholesale. Positions get their own step below.

# load_dumps.py
import duckdb, os

OUT = "dumps"
CHANGED = os.path.join(OUT, "changed.txt")
EVENT_TABLES = {
    "order_filled_events", "order_filled_events_v2", "payout_redemptions",
    "position_splits", "position_merges", "position_conversions",
    "combinatorial_redemptions", "daily_token_prices",
}

db = duckdb.connect()
db.execute("INSTALL postgres; LOAD postgres;")
db.execute("ATTACH 'dbname=polymarket user=postgres' AS pg (TYPE POSTGRES)")

paths = sorted(set(open(CHANGED).read().split())) if os.path.exists(CHANGED) else []

for path in paths:
    table = path.split("/")[0]
    local = os.path.join(OUT, path)
    if table in EVENT_TABLES:
        # one file = one unit: wipe what this file loaded before, then load it again
        db.execute("BEGIN")
        db.execute(f"DELETE FROM pg.{table} WHERE source_file = '{path}'")
        db.execute(f"INSERT INTO pg.{table} SELECT *, '{path}' FROM read_parquet('{local}')")
        db.execute("COMMIT")
    elif table == "markets":
        db.execute("BEGIN")
        db.execute("DELETE FROM pg.markets")
        db.execute(f"INSERT INTO pg.markets SELECT * FROM read_parquet('{local}')")
        db.execute("COMMIT")
    else:
        continue   # positions: see Step 4
    print("loaded", path)

# everything loaded: clear the to-do list
if paths:
    os.remove(CHANGED)

On the first run this loads the entire history, which takes a while — the fills tables alone are well over a billion rows. After that, each run touches a handful of files and finishes quickly. If anything fails midway, just run it again: changed.txt is only cleared after every file has loaded, and reloading a file that already went in is harmless.

Step 4: Positions — query in place, or mirror

The positions snapshot is a single file, positions/positions_current.parquet, that always holds the current state of every (wallet, token) position: amount held, average price, realized P&L, total bought, and the last block in which it changed. It's ~318M rows, so pick the option that matches what you need.

Option A: don't load it

DuckDB queries the Parquet file directly and fast. If you only analyze positions, this is often all you need:

SELECT user_address, SUM(realized_pnl) / 1e6 AS realized_pnl_usdc
FROM 'dumps/positions/positions_current.parquet'
GROUP BY user_address
ORDER BY realized_pnl_usdc DESC
LIMIT 20;

Option B: full replace (first load, then weekly)

Rebuild the table on the side and swap it in, so readers never see a half-loaded table. This gives you an exact mirror of the snapshot, including positions that no longer exist.

# positions_full.sh
python3 - <<'EOF'
import duckdb
db = duckdb.connect()
db.execute("INSTALL postgres; LOAD postgres;")
db.execute("ATTACH 'dbname=polymarket user=postgres' AS pg (TYPE POSTGRES)")
db.execute("DROP TABLE IF EXISTS pg.positions_new")
db.execute("CREATE TABLE pg.positions_new AS "
           "SELECT * FROM read_parquet('dumps/positions/positions_current.parquet')")
EOF

psql polymarket <<'SQL'
ALTER TABLE positions_new ADD PRIMARY KEY (user_address, token_id);
CREATE INDEX ON positions_new (last_block);
-- re-create any other positions indexes you use here, before the swap
BEGIN;
DROP TABLE positions;
ALTER TABLE positions_new RENAME TO positions;
COMMIT;
SQL

Option C: daily delta (cheap)

Between full replaces, only bring in the positions that changed since your last load — the rows whose last_block is newer than the newest one you already have — and upsert them:

# positions_delta.sh
python3 - <<'EOF'
import duckdb
db = duckdb.connect()
db.execute("INSTALL postgres; LOAD postgres;")
db.execute("ATTACH 'dbname=polymarket user=postgres' AS pg (TYPE POSTGRES)")
cursor = db.execute("SELECT coalesce(max(last_block), 0) FROM pg.positions").fetchone()[0]
db.execute("DROP TABLE IF EXISTS pg.positions_delta")
db.execute(f"CREATE TABLE pg.positions_delta AS "
           f"SELECT * FROM read_parquet('dumps/positions/positions_current.parquet') "
           f"WHERE last_block > {cursor}")
EOF

psql polymarket <<'SQL'
INSERT INTO positions SELECT * FROM positions_delta
ON CONFLICT (user_address, token_id) DO UPDATE SET
    amount       = EXCLUDED.amount,
    avg_price    = EXCLUDED.avg_price,
    realized_pnl = EXCLUDED.realized_pnl,
    total_bought = EXCLUDED.total_bought,
    last_block   = EXCLUDED.last_block;
DROP TABLE positions_delta;
SQL

Step 5: Add indexes

Once the backfill is done, add indexes for the queries you'll run most. They're optional, but they make a huge difference at this size — and building them after the bulk load is much faster than maintaining them during it. (Keep the source_file indexes from Step 1 from the start; the loader needs them.)

CREATE INDEX idx_fills2_block ON order_filled_events_v2 (block_number);
CREATE INDEX idx_fills2_maker ON order_filled_events_v2 (maker);
CREATE INDEX idx_fills2_taker ON order_filled_events_v2 (taker);
CREATE INDEX idx_fills2_token ON order_filled_events_v2 (token_id);
CREATE INDEX idx_fills2_ts    ON order_filled_events_v2 (timestamp);

CREATE INDEX idx_fills_maker  ON order_filled_events (maker);
CREATE INDEX idx_fills_ts     ON order_filled_events (timestamp);

CREATE INDEX idx_pos_block   ON positions (last_block);   -- used by the daily delta
CREATE INDEX idx_pos_token   ON positions (token_id);

CREATE INDEX idx_redeem_cond ON payout_redemptions (condition_id);
CREATE INDEX idx_prices_tok  ON daily_token_prices (token_id, day);

-- Markets: for joining token_id to market info
CREATE INDEX idx_markets_yes ON markets (yes_token_id);
CREATE INDEX idx_markets_no  ON markets (no_token_id);

Step 6: Run it every day

New files land in the nightly build at 05:00 UTC, covering the UTC day that just ended. Schedule your job a bit later:

# crontab — 07:00 UTC every day
# Monday to Saturday: positions delta
0 7 * * 1-6  cd /opt/polymarket && python3 sync_dumps.py && python3 load_dumps.py && ./positions_delta.sh
# Sunday: exact positions mirror
0 7 * * 0    cd /opt/polymarket && python3 sync_dumps.py && python3 load_dumps.py && ./positions_full.sh

Because the sync only fetches what changed and every load is idempotent, running late, running twice, or missing a few days is harmless — the next run catches up on everything you don't have yet. You're never more than about a day behind: yesterday's activity (UTC) is in your database every morning.

Verify your data

Because every row carries its source_file, you can check your database against the files exactly, not approximately. This lists any file whose row count in Postgres doesn't match the Parquet file — an empty result means everything matches:

import duckdb
db = duckdb.connect()
db.execute("INSTALL postgres; LOAD postgres;")
db.execute("ATTACH 'dbname=polymarket user=postgres' AS pg (TYPE POSTGRES)")

t = "order_filled_events_v2"
print(db.execute(f"""
    WITH f AS (SELECT filename, count(*) AS n
               FROM read_parquet('dumps/{t}/*.parquet', filename = true) GROUP BY 1),
         d AS (SELECT 'dumps/' || source_file AS filename, count(*) AS n
               FROM pg.{t} GROUP BY 1)
    SELECT coalesce(f.filename, d.filename) AS file, f.n AS in_file, d.n AS in_db
    FROM f FULL OUTER JOIN d ON f.filename = d.filename
    WHERE f.n IS DISTINCT FROM d.n
""").fetchall())

Example queries on your new database

Now you have the full dataset. Here are some things you can do that aren't possible with Polymarket's own API. One rule before you sum volume: each match is recorded as one row per maker order plus a taker-side row that restates the same volume, so naive sums double-count. Per wallet, group all rows by maker; for market turnover, drop rows whose taker is one of the exchange contracts.

-- Top 10 wallets by trading volume since the v2 cutover (USDC)
-- All rows grouped by maker: each wallet's part in each match counts once
SELECT maker,
       SUM(CASE WHEN side = 0 THEN maker_amount_filled
                ELSE taker_amount_filled END) / 1e6 AS volume_usdc
FROM order_filled_events_v2
GROUP BY maker
ORDER BY volume_usdc DESC
LIMIT 10;

-- Daily turnover over the last 30 days, each match counted once
SELECT timestamp::date AS day,
       SUM(CASE WHEN side = 0 THEN maker_amount_filled
                ELSE taker_amount_filled END) / 1e6 AS volume_usdc,
       COUNT(*) AS fills
FROM order_filled_events_v2
WHERE timestamp > (now() AT TIME ZONE 'UTC') - interval '30 days'
  AND lower(taker) NOT IN (
      '0x4bfb41d5b3570defd03c39a9a4d8de6bd8b8982e', '0xc5d563a36ae78145c45a50134d48a1215220f80a',
      '0xe111180000d2663c0091e4f400237545b87b996b', '0xe2222d279d744050d28e00520010520000310f59',
      '0xe3333700ca9d93003f00f0f71f8515005f6c00aa')
GROUP BY day
ORDER BY day;

-- Wallets with the highest realized P&L
SELECT user_address, SUM(realized_pnl) / 1e6 AS realized_pnl_usdc
FROM positions
GROUP BY user_address
ORDER BY realized_pnl_usdc DESC
LIMIT 20;

-- What a wallet holds, with market names
SELECT m.question,
       CASE WHEN p.token_id = m.yes_token_id THEN m.outcome_yes ELSE m.outcome_no END AS outcome,
       p.amount / 1e6 AS shares,
       p.avg_price / 1e6 AS avg_price
FROM positions p
JOIN markets m ON p.token_id IN (m.yes_token_id, m.no_token_id)
WHERE p.user_address = '0x...' AND p.amount > 0
ORDER BY shares DESC;

For a full-history query on fills, read both tables: order_filled_events (v1, through Apr 28, 2026) and order_filled_events_v2 (everything since). They don't overlap. In v1, side is the text 'buy'/'sell' and the traded token is taker_asset_id on buys and maker_asset_id on sells.

Production tips

• Keep the Parquet files. They're your source of truth: any file can be reloaded at any time, and DuckDB can query them directly without touching Postgres.
• Build indexes after the backfill. Except the source_file ones — the loader needs those from the first file on.
• Partition the fills tables. In PostgreSQL, partitioning by month or quarter makes date-range queries and maintenance much faster at a billion-plus rows.
• Watch freshness. Alert if the newest order_filled_events_v2 file you have is more than two days old, or if a run printed FAILED.
• Don't re-download what you have. The size + mtime check is what keeps the daily run to a handful of requests and well inside the download allowance.