How We Tracked 10,000,000 Spins Per Day with Go and PostgreSQL Partitioning

Every spin is a legal record

I worked at an online casino gaming company. Regulated market, multiple jurisdictions, thousands of concurrent players, hundreds of game titles. Every time a player clicked "Spin" on a slot machine, we had to record it. Not just the result. The bet amount, the win amount, the game ID, the player ID, the jurisdiction, the random number generator seed, and a timestamp accurate to the millisecond.

This was not optional. Gaming regulators require a complete, auditable trail of every wager. If a regulator asks "show me every spin player X made in September 2019," you need to produce it within hours. If you lose data, you lose your license.

At peak hours we were recording around 10,000,000 spin events per day. On promotional weekends with free spins campaigns that number doubled. The spins table grew by roughly 300,000,000 rows per month.

For the first year, this worked. Then it didn't.

The table that stopped responding

The spins table looked like this:

CREATE TABLE spins (
    id            bigserial PRIMARY KEY,
    player_id     bigint NOT NULL,
    game_id       int NOT NULL,
    jurisdiction  text NOT NULL,
    bet_amount    numeric(12,2) NOT NULL,
    win_amount    numeric(12,2) NOT NULL,
    rng_seed      text NOT NULL,
    created_at    timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_spins_player_created ON spins (player_id, created_at DESC);
CREATE INDEX idx_spins_game_created ON spins (game_id, created_at);
CREATE INDEX idx_spins_jurisdiction_created ON spins (jurisdiction, created_at);

At 500,000,000 rows, three things broke at the same time.

Queries on recent data got slow. The daily revenue report ran a simple aggregation:

SELECT game_id, sum(bet_amount), sum(win_amount)
FROM spins
WHERE created_at >= now() - interval '24 hours'
GROUP BY game_id;

On 500M rows, the planner chose a sequential scan of the idx_spins_game_created index. The index itself was 18GB. Traversing it to find 24 hours of data in a 2-year table took 14 seconds. The finance team ran this query every morning. Sometimes they ran it three times because they thought it was stuck.

Deleting old data became dangerous. Gaming regulations in most jurisdictions require keeping records for 5 years. But some internal analytics tables derived from spins only needed 90 days. Running:

DELETE FROM spins_analytics WHERE created_at < now() - interval '90 days';

on a 200M-row derived table took 40 minutes, generated gigabytes of WAL, and caused replication lag that triggered alerts. Autovacuum then spent another hour cleaning up the dead tuples.

Index maintenance crushed writes. Every insert updated three indexes. At 10M inserts per day, that is 30M index entry writes per day. The indexes were bloated, and periodic REINDEX operations required downtime or a CONCURRENTLY build that ran for hours.

Range partitioning: one table, many partitions

PostgreSQL declarative partitioning splits a logical table into physical partitions. Each partition is a separate table on disk, but queries against the parent table work transparently. PostgreSQL routes reads and writes to the correct partition automatically.

For time-series data like spins, RANGE partitioning on the timestamp column is the natural fit:

CREATE TABLE spins (
    id            bigserial,
    player_id     bigint NOT NULL,
    game_id       int NOT NULL,
    jurisdiction  text NOT NULL,
    bet_amount    numeric(12,2) NOT NULL,
    win_amount    numeric(12,2) NOT NULL,
    rng_seed      text NOT NULL,
    created_at    timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

Then create one partition per month:

CREATE TABLE spins_2025_09 PARTITION OF spins
    FOR VALUES FROM ('2025-09-01') TO ('2025-10-01');

CREATE TABLE spins_2025_10 PARTITION OF spins
    FOR VALUES FROM ('2025-10-01') TO ('2025-11-01');

CREATE TABLE spins_2025_11 PARTITION OF spins
    FOR VALUES FROM ('2025-11-01') TO ('2025-12-01');

When you insert a row with created_at = '2025-10-15 14:32:01', PostgreSQL routes it to spins_2025_10 automatically. No application code changes. The insert statement is the same INSERT INTO spins (...) it always was.

The three problems, solved

Fast queries on recent data

The daily revenue query now only scans the current month's partition:

EXPLAIN ANALYZE
SELECT game_id, sum(bet_amount), sum(win_amount)
FROM spins
WHERE created_at >= now() - interval '24 hours'
GROUP BY game_id;

Append  (cost=0.00..48231.45 rows=112847 width=44)
       (actual time=0.031..82.417 rows=10241847 loops=1)
  ->  Seq Scan on spins_2026_02  (cost=0.00..48231.45 rows=112847 width=44)
        (actual time=0.029..61.340 rows=10241847 loops=1)
        Filter: (created_at >= (now() - '24:00:00'::interval))
Planning Time: 0.412 ms
Execution Time: 83.109 ms

PostgreSQL pruned every partition except the current month. Instead of scanning a 18GB index across 500M rows, it scanned a single partition with ~10M rows. From 14 seconds to 83 milliseconds.

This is called partition pruning. The planner looks at the WHERE clause, compares it against the partition boundaries, and eliminates partitions that cannot contain matching rows. It happens at plan time, before any data is read.

Instant data removal

Dropping old data is no longer a DELETE. It is a DROP TABLE:

DROP TABLE spins_2024_06;

This is instantaneous regardless of how many rows the partition contained. No dead tuples, no vacuum, no WAL bloat, no replication lag. The files are unlinked from disk and the space is reclaimed immediately.

For our compliance requirement, we kept 5 years of monthly partitions (60 partitions). For the analytics-derived tables, we kept 3 months and dropped anything older. The 40-minute DELETE became a sub-second DROP.

Smaller indexes, faster writes

Each partition has its own indexes. Instead of one 18GB B-tree across 500M rows, you have 24 smaller B-trees (one per month), each covering ~20M rows:

CREATE INDEX idx_spins_2025_10_player
    ON spins_2025_10 (player_id, created_at DESC);

CREATE INDEX idx_spins_2025_10_game
    ON spins_2025_10 (game_id, created_at);

CREATE INDEX idx_spins_2025_10_jurisdiction
    ON spins_2025_10 (jurisdiction, created_at);

Smaller indexes mean faster lookups, faster inserts (less tree traversal per write), and faster maintenance. Reindexing a single month's partition takes seconds, not hours.

PostgreSQL can also create indexes on the parent table and propagate them automatically to new partitions:

CREATE INDEX ON spins (player_id, created_at DESC);
CREATE INDEX ON spins (game_id, created_at);
CREATE INDEX ON spins (jurisdiction, created_at);

Every new partition inherits these index definitions. No manual step needed.

BRIN indexes for time-ordered partitions

Inside each monthly partition, rows arrive in roughly chronological order. When data is physically ordered by time, BRIN (Block Range Index) is a good fit.

A BRIN index stores the min/max value for each block of pages instead of indexing every row. It is tiny compared to a B-tree:

CREATE INDEX idx_spins_2025_10_created_brin
    ON spins_2025_10 USING brin (created_at);

A BRIN index on a 20M-row partition is roughly 48KB. The equivalent B-tree would be around 400MB. For time-range scans within a single partition ("show me all spins between 3 PM and 4 PM on October 15"), BRIN is fast enough and uses almost no disk.

We kept B-tree indexes on player_id and game_id (for point lookups and joins) but switched to BRIN for created_at within each partition. The total index footprint dropped from 54GB to under 8GB.

The Go insert pipeline

Recording 10M spins per day means sustaining roughly 115 inserts per second on average, with peaks of 500+/sec during evening hours across European and Latin American markets.

Single-row inserts with database/sql would work at this rate, but we were also shipping data in batches from game providers. Some providers sent results in bulk, a batch of 50,000 spins every 30 seconds. For those, the COPY protocol was essential.

type SpinRecord struct {
    PlayerID     int64
    GameID       int32
    Jurisdiction string
    BetAmount    float64
    WinAmount    float64
    RNGSeed      string
    CreatedAt    time.Time
}

func insertSpinBatch(ctx context.Context, conn *pgx.Conn, spins []SpinRecord) (int64, error) {
    rows := make([][]interface{}, len(spins))
    for i, s := range spins {
        rows[i] = []interface{}{
            s.PlayerID,
            s.GameID,
            s.Jurisdiction,
            s.BetAmount,
            s.WinAmount,
            s.RNGSeed,
            s.CreatedAt,
        }
    }

    count, err := conn.CopyFrom(
        ctx,
        pgx.Identifier{"spins"},
        []string{
            "player_id", "game_id", "jurisdiction",
            "bet_amount", "win_amount", "rng_seed", "created_at",
        },
        pgx.CopyFromRows(rows),
    )
    if err != nil {
        return 0, fmt.Errorf("copy spins: %w", err)
    }
    return count, nil
}

CopyFrom uses PostgreSQL's binary COPY protocol. No SQL parsing per row, no per-statement overhead. A batch of 50,000 rows completes in under 2 seconds. PostgreSQL handles the partition routing automatically -- the COPY targets the parent table spins, and each row lands in the correct monthly partition based on its created_at value.

We ran 4 worker goroutines, each with its own connection from a pgxpool.Pool, consuming batches from a channel:

func runIngestWorkers(ctx context.Context, pool *pgxpool.Pool, batches <-chan []SpinRecord, workers int) error {
    g, ctx := errgroup.WithContext(ctx)

    for i := 0; i < workers; i++ {
        g.Go(func() error {
            for {
                select {
                case <-ctx.Done():
                    return ctx.Err()
                case batch, ok := <-batches:
                    if !ok {
                        return nil
                    }
                    conn, err := pool.Acquire(ctx)
                    if err != nil {
                        return fmt.Errorf("acquire conn: %w", err)
                    }
                    _, err = insertSpinBatch(ctx, conn.Conn(), batch)
                    conn.Release()
                    if err != nil {
                        return fmt.Errorf("insert batch: %w", err)
                    }
                }
            }
        })
    }

    return g.Wait()
}

The producer read from game provider feeds (HTTP webhooks and message queues), buffered spins into slices of 10,000, and pushed them into the channel. The workers drained the channel and COPYed into Postgres. Backpressure was handled by the channel's buffer size. If all workers were busy, the producer blocked until a slot opened.

Automated partition creation

If a row arrives and no matching partition exists, PostgreSQL rejects the insert with an error. You need partitions to exist before data arrives. We automated this with a Go goroutine that ran daily:

func ensurePartitions(ctx context.Context, pool *pgxpool.Pool, monthsAhead int) error {
    now := time.Now().UTC()

    for i := 0; i <= monthsAhead; i++ {
        target := now.AddDate(0, i, 0)
        partName := fmt.Sprintf("spins_%d_%02d", target.Year(), target.Month())
        rangeStart := time.Date(target.Year(), target.Month(), 1, 0, 0, 0, 0, time.UTC)
        rangeEnd := rangeStart.AddDate(0, 1, 0)

        query := fmt.Sprintf(`
            DO $$
            BEGIN
                IF NOT EXISTS (
                    SELECT 1 FROM pg_class WHERE relname = '%s'
                ) THEN
                    EXECUTE format(
                        'CREATE TABLE %s PARTITION OF spins FOR VALUES FROM (%%L) TO (%%L)',
                        '%s', '%s'
                    );
                    RAISE NOTICE 'Created partition %s';
                END IF;
            END $$;
        `, partName, partName, rangeStart.Format(time.RFC3339), rangeEnd.Format(time.RFC3339), partName)

        if _, err := pool.Exec(ctx, query); err != nil {
            return fmt.Errorf("ensure partition %s: %w", partName, err)
        }
    }
    return nil
}

We called this with monthsAhead = 3 so there were always 3 future partitions ready. If the job failed one day, there was a 3-month buffer before any inserts would be rejected. The function is idempotent. Running it twice does nothing the second time.

Partition dropping for old analytics data followed the same pattern:

func dropOldPartitions(ctx context.Context, pool *pgxpool.Pool, table string, keepMonths int) error {
    cutoff := time.Now().UTC().AddDate(0, -keepMonths, 0)
    cutoffName := fmt.Sprintf("%s_%d_%02d", table, cutoff.Year(), cutoff.Month())

    rows, err := pool.Query(ctx, `
        SELECT inhrelid::regclass::text AS partition_name
        FROM pg_inherits
        JOIN pg_class ON pg_class.oid = inhrelid
        WHERE inhparent = $1::regclass
        ORDER BY partition_name
    `, table)
    if err != nil {
        return fmt.Errorf("list partitions: %w", err)
    }
    defer rows.Close()

    for rows.Next() {
        var name string
        if err := rows.Scan(&name); err != nil {
            return fmt.Errorf("scan partition: %w", err)
        }
        if name < cutoffName {
            if _, err := pool.Exec(ctx, fmt.Sprintf("DROP TABLE IF EXISTS %s", name)); err != nil {
                return fmt.Errorf("drop %s: %w", name, err)
            }
            log.Printf("dropped partition %s", name)
        }
    }
    return rows.Err()
}

One function creates future partitions. Another drops expired ones. Both are idempotent and safe to run on any schedule.

The numbers

After migrating to partitioned tables, here is what changed:

Metric	Before (single table)	After (monthly partitions)
Daily revenue query	14 seconds	83ms
Data deletion (90-day cleanup)	40 minutes + vacuum	Sub-second DROP
Total index size	54 GB	8 GB
Insert throughput (COPY, 4 workers)	~45,000 rows/sec	~48,000 rows/sec
Replication lag during deletes	2-5 minutes	None
Reindex time (single month)	Hours (full table)	15 seconds (one partition)

Insert throughput was roughly the same. Partitioning does not slow down writes meaningfully. The gains were entirely on the read side, the maintenance side, and the operational sanity side.

The migration

Moving 500M rows from a non-partitioned table to a partitioned one is its own project. We did it without downtime using this sequence:

1. Create the new partitioned table (spins_new) with all partitions.
2. Start writing to both spins (old) and spins_new (new) via dual-write in the application. Both inserts in the same transaction.
3. Backfill historical data in batches: INSERT INTO spins_new SELECT * FROM spins WHERE created_at BETWEEN $1 AND $2. We processed one month at a time, 30 batches total.
4. Verify row counts per month between old and new.
5. Swap the names: ALTER TABLE spins RENAME TO spins_legacy; ALTER TABLE spins_new RENAME TO spins.
6. Remove the dual-write code path.
7. Drop spins_legacy after a 2-week safety window.

The backfill took about 4 hours running sequentially (one month at a time to avoid spiking I/O). The rename was instantaneous. Total downtime: zero.

Lessons from running this at scale

Partition by month, not by day. Daily partitions sound appealing for fine-grained control, but at 10M rows/day you end up with 365 partitions per year. The planner has to evaluate all of them during planning. Monthly partitions (12 per year, 60 for 5 years) keep the partition list manageable.

Always create partitions ahead of time. A missing partition at midnight on the first of the month means rejected inserts. Three months of buffer is cheap insurance.

Use BRIN for time, B-tree for everything else. BRIN on created_at within each partition is the best tradeoff between index size and query speed for range scans. B-tree on player_id and game_id is still necessary for point lookups.

DROP is better than DELETE. If your retention policy aligns with partition boundaries (and it should), you never need to DELETE rows. DROP TABLE is O(1) regardless of row count.

Monitor partition sizes. If one month suddenly has 3x the rows of others, something changed in traffic patterns. We tracked pg_total_relation_size() per partition in our monitoring dashboard as an early warning signal.

PostgreSQL as an event store

The gaming industry generates extraordinary volumes of transactional data, and every row matters legally. We evaluated dedicated event stores, time-series databases, and distributed log systems. In the end, PostgreSQL with range partitioning handled 10M events per day, maintained 5 years of history, and answered regulatory queries in milliseconds.

The tooling was straightforward: Go for the ingest pipeline, pgx for the COPY protocol, goroutines for parallelism, and PostgreSQL's built-in partitioning for lifecycle management. No Kafka, no ClickHouse, no Cassandra. One relational database with a feature that has been stable since PostgreSQL 10.

For most applications that generate millions of rows per day, analytics events, IoT telemetry, transaction logs, audit trails, this same pattern applies. Partition by time, index within partitions, drop instead of delete, and automate the lifecycle.

If you want to test partitioning on a real PostgreSQL instance, start with a free database on Noctaploy. Create a partitioned table, load a few million rows with COPY, and run your queries. Extensions like pg_stat_statements are available from the dashboard to profile query performance across partitions.