Logging API Usage Events to Postgres

Every commercial API eventually needs one table it can defend in front of a customer who says "we never made 412,000 calls." That table is the usage event log, and where you put it decides whether your billing is auditable or merely plausible. Part of the Tracking API Usage and Analytics guide, this page resolves a single question: how do you make Postgres the durable record of every billable request without paying for that durability in p99 latency? The answer is a buffered writer using COPY, a schema built for per-key rollups rather than for row lookups, monthly partitions with an automated retention job, and a reconciliation query that catches drift before Stripe finalizes an invoice.

Resist reaching for a purpose-built analytics store on day one. Plain Postgres handles tens of millions of usage rows a month on a $30 instance, it already sits in your stack, and it joins usage against customers and subscriptions in one query. The engineering that matters is not the database choice; it is keeping the write off the hot path.

The write path is the entire decision

There are four realistic ways to get an event from a finished request into durable storage, and they differ by two orders of magnitude in what they cost the caller. A synchronous INSERT inside the request cycle adds a full round trip, a pooled connection, and a WAL flush to every response. On a modest managed Postgres that is roughly 4 ms at p99, and it competes for the same connections your product queries need — the fastest route to connection pool exhaustion that exists.

Buffering in process and flushing batches with COPY costs the request a put_nowait call on an asyncio.Queue: about 40 microseconds, no I/O, no connection. The trade is a loss window — events sitting in memory when the process dies are gone. A Redis Stream buffer closes that window at the cost of a network hop per request and a second moving part; a stdout log plus a shipper closes it too, but now you own a log pipeline and your billing data lives somewhere you cannot join against.

Write pathAdded p99Loss window
Inline INSERT~4.1 msnone
Buffer + COPY~0.04 msone flush interval
Redis Stream~0.4 msRedis fsync policy
stdout + shipper~0.1 msshipper backlog

Look at where those milliseconds land in a real request budget. A monetized endpoint doing external work spends 35 to 40 ms in the handler, so 4 ms of billing I/O raises p99 by ten percent and adds a failure mode: if Postgres is slow, your API is slow, for a write nobody is waiting on.

Request latency budget with inline insert versus buffered writer Two stacked bars showing a 41.3 millisecond request when the usage row is inserted inline, versus 37.2 milliseconds when the event is buffered in memory and flushed with COPY. p99 for one billable request Inline INSERT 41.3 ms Buffer + COPY 37.2 ms auth + middleware 1.2 ms handler work 36 ms usage write Buffering removes 4.06 ms and one pooled connection from every request.

Pick the buffered writer. The loss window is bounded by your flush interval, and you can shrink it to a second or two without meaningfully changing throughput. Losing two seconds of usage events during an unplanned pod kill costs you a few cents; adding 4 ms to every request costs you a slower API forever and a support ticket the first time your database has a bad afternoon.

Buffered writes with COPY, not executemany

copy_records_to_table on asyncpg pushes rows through the Postgres binary COPY protocol. It is roughly five to ten times faster than a parameterized multi-row INSERT for batches of a few hundred, and it takes one connection for one transaction regardless of batch size. If you have not picked a driver yet, the asyncpg vs psycopg3 comparison covers the trade — for this workload asyncpg wins on raw COPY throughput.

The drain loop below collects up to USAGE_BATCH_MAX rows or waits USAGE_FLUSH_SECONDS, whichever comes first, then writes once. Two details matter commercially. First, a failed flush spools to disk instead of vanishing, so a database restart costs you a delay, not revenue. Second, the shutdown handler drains the queue before the process exits, which turns every planned deploy into a zero-loss event.

Python
# usage/writer.py
import asyncio
import json
import os
from pathlib import Path

import asyncpg

BATCH_MAX = int(os.getenv("USAGE_BATCH_MAX", "500"))
FLUSH_SECONDS = float(os.getenv("USAGE_FLUSH_SECONDS", "2.0"))
QUEUE_MAXSIZE = int(os.getenv("USAGE_QUEUE_MAXSIZE", "20000"))
SPOOL_DIR = Path(os.getenv("USAGE_SPOOL_DIR", "/var/tmp/usage-spool"))
DSN = os.getenv("USAGE_DATABASE_URL", "postgresql://localhost/api")

COLUMNS = [
    "occurred_at", "customer_id", "api_key_id", "route",
    "status_code", "duration_ms", "billable_units",
]

queue: asyncio.Queue[tuple] = asyncio.Queue(maxsize=QUEUE_MAXSIZE)


async def _collect_batch() -> list[tuple]:
    """Block for the first row, then fill the batch until it is full or the timer expires."""
    loop = asyncio.get_running_loop()
    batch = [await queue.get()]
    deadline = loop.time() + FLUSH_SECONDS
    while len(batch) < BATCH_MAX:
        remaining = deadline - loop.time()
        if remaining <= 0:
            break
        try:
            batch.append(await asyncio.wait_for(queue.get(), remaining))
        except TimeoutError:  # builtin alias since 3.11
            break
    return batch


def _spool(batch: list[tuple]) -> None:
    SPOOL_DIR.mkdir(parents=True, exist_ok=True)
    path = SPOOL_DIR / f"{os.getpid()}-{id(batch)}.jsonl"
    with path.open("w", encoding="utf-8") as fh:
        for row in batch:
            fh.write(json.dumps(row, default=str) + "\n")


async def drain_forever(pool: asyncpg.Pool) -> None:
    while True:
        batch = await _collect_batch()
        try:
            async with pool.acquire() as conn:
                await conn.copy_records_to_table(
                    "usage_event", records=batch, columns=COLUMNS
                )
        except (asyncpg.PostgresError, OSError):
            _spool(batch)          # replay later; never lose a billable row
            await asyncio.sleep(1.0)
        finally:
            for _ in batch:
                queue.task_done()

Wire the loop to the application lifespan so it finishes its work before the process leaves. Give it a dedicated pool of two connections so a flush can never starve product traffic.

Python
# usage/lifespan.py
import asyncio
import os
from contextlib import asynccontextmanager

import asyncpg
from fastapi import FastAPI

from usage.writer import DSN, drain_forever, queue

DRAIN_TIMEOUT = float(os.getenv("USAGE_DRAIN_TIMEOUT", "10.0"))


@asynccontextmanager
async def lifespan(app: FastAPI):
    pool = await asyncpg.create_pool(DSN, min_size=1, max_size=2)
    task = asyncio.create_task(drain_forever(pool))
    try:
        yield
    finally:
        try:
            await asyncio.wait_for(queue.join(), DRAIN_TIMEOUT)
        except TimeoutError:
            pass
        task.cancel()
        await pool.close()
Queue depth over time with two-second COPY flushes Queue depth climbs as requests enqueue events and drops to zero at each two-second flush, with a final drain triggered by SIGTERM at five seconds. Queue depth between flushes (250 req/s) 500 0 COPY 500 rows COPY 500 rows SIGTERM drain 0 s 2 s 4 s 5 s Worst-case loss equals one flush interval; the shutdown hook removes it on planned deploys.

A schema shaped for rollups, not lookups

Nobody ever reads a single usage event. Every query you will actually run is an aggregate over a customer and a time range, so design for sequential scans of recent data and for one covering index. Skip the surrogate primary key entirely — a bigint identity column costs eight bytes and a sequence hit per row and buys you nothing, because you will never fetch a usage event by id.

Two indexes carry the whole workload. A BRIN index on occurred_at is the star: on naturally time-ordered data it summarizes ranges of pages instead of storing a pointer per row, so it occupies a few hundred kilobytes where a btree would take gigabytes, and it barely slows the write. A btree on (customer_id, occurred_at) with billable_units in an INCLUDE clause serves the per-key rollup as an index-only scan. That is it. Every extra index you add is a tax on every COPY.

Python
# usage/schema.py
import asyncio
import os

import asyncpg

DDL = """
CREATE TABLE IF NOT EXISTS usage_event (
    occurred_at     timestamptz NOT NULL,
    customer_id     text        NOT NULL,
    api_key_id      text        NOT NULL,
    route           text        NOT NULL,
    status_code     smallint    NOT NULL,
    duration_ms     integer     NOT NULL,
    billable_units  integer     NOT NULL DEFAULT 1
) PARTITION BY RANGE (occurred_at);

CREATE INDEX IF NOT EXISTS ix_usage_brin_time
    ON usage_event USING brin (occurred_at) WITH (pages_per_range = 32);

CREATE INDEX IF NOT EXISTS ix_usage_customer_time
    ON usage_event (customer_id, occurred_at) INCLUDE (billable_units);

CREATE TABLE IF NOT EXISTS usage_rollup_daily (
    day             date NOT NULL,
    customer_id     text NOT NULL,
    route           text NOT NULL,
    calls           bigint NOT NULL,
    billable_units  bigint NOT NULL,
    PRIMARY KEY (day, customer_id, route)
);
"""


async def main() -> None:
    conn = await asyncpg.connect(os.getenv("USAGE_DATABASE_URL", "postgresql://localhost/api"))
    try:
        await conn.execute(DDL)
    finally:
        await conn.close()


if __name__ == "__main__":
    asyncio.run(main())

Resist adding a unique constraint for deduplication. On a partitioned table any unique index must include the partition key, so you would be enforcing uniqueness on (occurred_at, request_id) — which does not stop a replay with a different timestamp, and it forces a btree lookup on every inserted row. Deduplicate at rollup time with DISTINCT ON instead, where it costs you nothing per request. The same reasoning drives idempotent webhook receivers: push uniqueness to the cheapest place that still gives a correct answer.

Which index serves which usage query A per-customer billing rollup uses the covering btree index on customer and time, while a whole-fleet latency scan uses the BRIN index on occurred_at, both reading the partitioned usage_event table. Billing rollup query one customer, one month Latency report query all keys, last 24 h btree (customer, time) INCLUDE billable_units BRIN (occurred_at) pages_per_range 32 usage_event 7 columns, no PK index-only scan, no heap fetch summary index: ~300 KB per 10 M rows

Partitions and retention that stay boring

Partition by month with RANGE (occurred_at) and create partitions ahead of need. The payoff arrives on retention day: dropping a month of raw events becomes a metadata operation that finishes in milliseconds, while the equivalent DELETE rewrites gigabytes, bloats the write-ahead log, and leaves dead tuples for autovacuum to chew through during your busiest week.

Keep raw events for 90 days and daily rollups for 25 months. Ninety days covers a full billing cycle plus a dispute window; two years of rollups costs less than a coffee. Run the maintenance job daily through whatever scheduler you already have — a container cron entry or the Celery beat setup you use for other periodic work.

Python
# usage/partitions.py
import asyncio
import os
from datetime import date, timedelta

import asyncpg

MONTHS_AHEAD = int(os.getenv("USAGE_PARTITION_MONTHS_AHEAD", "2"))
RAW_RETENTION_DAYS = int(os.getenv("USAGE_RAW_RETENTION_DAYS", "90"))


def _month_start(d: date, offset: int = 0) -> date:
    month_index = d.year * 12 + (d.month - 1) + offset
    return date(month_index // 12, month_index % 12 + 1, 1)


async def ensure_partitions(conn: asyncpg.Connection, today: date) -> list[str]:
    created = []
    for offset in range(0, MONTHS_AHEAD + 1):
        start = _month_start(today, offset)
        end = _month_start(today, offset + 1)
        name = f"usage_event_{start:%Y_%m}"
        await conn.execute(
            f"CREATE TABLE IF NOT EXISTS {name} PARTITION OF usage_event "
            f"FOR VALUES FROM ('{start.isoformat()}') TO ('{end.isoformat()}')"
        )
        created.append(name)
    return created


async def drop_expired(conn: asyncpg.Connection, today: date) -> list[str]:
    cutoff = _month_start(today - timedelta(days=RAW_RETENTION_DAYS))
    rows = await conn.fetch(
        "SELECT c.relname FROM pg_class c "
        "JOIN pg_inherits i ON i.inhrelid = c.oid "
        "JOIN pg_class p ON p.oid = i.inhparent "
        "WHERE p.relname = 'usage_event' ORDER BY c.relname"
    )
    dropped = []
    for row in rows:
        name = row["relname"]
        stamp = name.removeprefix("usage_event_")
        year, month = (int(part) for part in stamp.split("_"))
        if date(year, month, 1) < cutoff:
            await conn.execute(f"ALTER TABLE usage_event DETACH PARTITION {name}")
            await conn.execute(f"DROP TABLE {name}")
            dropped.append(name)
    return dropped


async def main() -> None:
    conn = await asyncpg.connect(os.getenv("USAGE_DATABASE_URL", "postgresql://localhost/api"))
    try:
        today = date.today()
        print({"created": await ensure_partitions(conn, today),
               "dropped": await drop_expired(conn, today)})
    finally:
        await conn.close()


if __name__ == "__main__":
    asyncio.run(main())

Detach before dropping: DETACH PARTITION takes a brief lock and removes the child from the parent's plan, then DROP TABLE frees the files without blocking the parent. Roll the day's events into usage_rollup_daily before retention runs, and your customer usage dashboard keeps working on history that no longer exists in raw form.

Monthly partition lifecycle and retention Seven monthly partitions in a row: the three oldest are detached and dropped past the ninety day cutoff, the current month is live, two future months are pre-created, and a daily rollup table spans all of them. usage_event partitions 2026_02 dropped 2026_03 dropped 2026_05 retained 2026_06 retained 2026_07 writing now 2026_08 pre-created 2026_09 pre-created 90-day cutoff: DETACH then DROP usage_rollup_daily — one row per day, customer, route — kept 25 months

Reconciling the log against Stripe

Your Postgres log and Stripe's meter are independent counters, and independent counters drift. Events get spooled and never replayed, a deploy kills a pod mid-flush, a retry double-reports. Run reconciliation the day before each invoice finalizes: aggregate your rollup for the period, pull Stripe's aggregate for the same window, alert on any gap over half a percent. That threshold is deliberate — chasing single-event differences wastes a week, while a systematic gap shows up immediately at that resolution.

Python
# usage/reconcile.py
import asyncio
import os

import asyncpg
import stripe

stripe.api_key = os.getenv("STRIPE_SECRET_KEY")
DRIFT_ALERT_RATIO = float(os.getenv("USAGE_DRIFT_ALERT_RATIO", "0.005"))

LOCAL_TOTAL_SQL = """
SELECT COALESCE(SUM(billable_units), 0)::bigint AS units
FROM usage_rollup_daily
WHERE customer_id = $1 AND day >= $2 AND day < $3
"""


async def local_units(conn: asyncpg.Connection, customer_id: str, start, end) -> int:
    return await conn.fetchval(LOCAL_TOTAL_SQL, customer_id, start, end)


def stripe_units(meter_id: str, stripe_customer_id: str, start_ts: int, end_ts: int) -> int:
    summaries = stripe.billing.Meter.list_event_summaries(
        meter_id, customer=stripe_customer_id, start_time=start_ts, end_time=end_ts
    )
    return int(sum(float(s["aggregated_value"]) for s in summaries.auto_paging_iter()))


async def check(customer_id: str, stripe_customer_id: str, start, end,
                start_ts: int, end_ts: int) -> dict:
    conn = await asyncpg.connect(os.getenv("USAGE_DATABASE_URL", "postgresql://localhost/api"))
    try:
        mine = await local_units(conn, customer_id, start, end)
    finally:
        await conn.close()
    theirs = stripe_units(os.getenv("STRIPE_METER_ID", ""), stripe_customer_id, start_ts, end_ts)
    drift = theirs - mine
    ratio = abs(drift) / mine if mine else 0.0
    match ratio:
        case r if r == 0:
            status = "exact"
        case r if r < DRIFT_ALERT_RATIO:
            status = "within_tolerance"
        case _:
            status = "investigate"
    return {"local": mine, "stripe": theirs, "drift": drift, "status": status}

Postgres is the authority here, not Stripe. When they disagree, you replay missing meter events from your rollup rather than adjusting your log to match the invoice — the details of that reporting path live in the Stripe metered billing configuration page. Log every reconciliation result as a structured record so a support conversation six months later has evidence; the structured logging setup makes those searchable without extra work.

Reconciliation decision path before an invoice finalizes The rollup total is compared against the Stripe meter aggregate; an exact or near match closes the period, while drift above half a percent triggers replaying missing meter events from Postgres. usage_rollup_daily SUM(billable_units) Stripe meter event summaries compare ratio |drift| / local < 0.5% >= 0.5% close the period let the invoice finalize replay meter events Postgres is the authority Run one day before the billing period closes, while corrections are still free.

When to choose this, and when to avoid it

Choose the buffered Postgres log when you run under roughly 2,000 requests per second per instance, usage drives billing, and you want usage joinable against customers without a second data store. That covers virtually every API business earning under a million dollars a year. At 500 requests per second you write about 1.3 billion rows a year, but 90-day retention keeps the live table near 320 million rows and 35 GB — comfortable on a managed instance costing $50 to $90 a month, a fraction of a cent per thousand requests once you work through the cost per API request math.

Avoid it in three cases. If a single lost event is legally unacceptable — payments, regulated metering — put a durable buffer in front, either a Redis Stream or a queue, and accept the extra hop. If your analytics questions are open-ended slice-and-dice across billions of rows, a column store earns its keep and Postgres does not. And if you are running serverless functions with no long-lived process, the in-memory buffer has nowhere to live; write to a managed queue instead, because a function that exits after each request cannot flush anything.

Migrating from inline inserts

Switching from a synchronous insert to the buffered writer takes an afternoon and no downtime. First, deploy the partitioned usage_event table alongside the existing one and run ensure_partitions so the current and next two months exist. Second, ship the writer and lifespan hook with the middleware still inserting inline — a drain loop with nothing to drain proves startup and shutdown work. Third, flip the middleware to queue.put_nowait behind an environment flag on one instance and compare row counts across both tables for an hour. Fourth, backfill with one INSERT INTO usage_event SELECT ... FROM old_usage per month so each statement lands in a single partition. Finally, drop the old table and watch pool wait time fall.

Builder verdict

Buffer in process, write with COPY, partition by month, and let Postgres be the authority your invoices are built on. This is the highest return-per-hour infrastructure in a monetized API: two files of code and a nightly maintenance job buy you billing you can defend, per-customer margin you can query, and a hot path measurably faster than the version that inserted a row per request. Every alternative costs more — a dedicated analytics store adds a bill and a sync problem before you have customers to justify it, and a log-shipping pipeline strands your revenue data where you cannot join it. Ship the buffered writer this week, add reconciliation before your first invoice cycle closes, and revisit the decision only when one Postgres instance genuinely stops keeping up.

FAQ

How much does this cost to run at 50 million requests a month? Roughly $50 to $90 a month of managed Postgres. Fifty million events at about 110 bytes plus index overhead is around 8 GB per month, and 90-day retention caps the live table near 25 GB. Compute barely moves: batched COPY at 500 rows per transaction is a rounding error next to serving the requests.

What happens to billing if a pod dies mid-flush? You lose at most one flush interval of events, so two seconds at the default setting — a few hundred rows worth cents. Planned deploys lose nothing, because the shutdown hook drains the queue before the process exits. If even that is unacceptable, put a Redis Stream between the middleware and the writer and accept roughly 0.4 ms per request.

Do I need TimescaleDB or ClickHouse instead? Not until plain Postgres stops keeping up, which for most API businesses means well past a thousand requests per second sustained. Declarative partitioning plus a BRIN index covers the access pattern, and one database keeps usage joinable against customers. Migrating later is a data-copy problem, not a rewrite.

How risky is migrating from inline inserts to buffered writes? Low, if you stage it. Deploy the new table and drain loop first with the middleware unchanged, then flip writes behind an environment flag on one instance and compare counts for an hour before rolling out. The old path stays functional the whole time, so a rollback is one variable change, not a redeploy.

Should the usage log live in the same database as my application data? Start there, and split only when write volume starts affecting product query latency. A separate database means a second connection pool, a second backup policy, and joins you can no longer run. When you do split, the writer needs only a new DSN.