For a webhook audit table holding billions of rows, the design that holds up best in most cases is a range partition on the time each event was received, a small set of B-tree indexes matched to the queries you actually run, large payloads stored in a column whose compression you have measured, and expiry handled by detaching and dropping whole partitions instead of deleting rows one at a time. Row count alone does not settle any of these choices. The query mix, ingestion rate, payload width, retention rules, and operational limits decide the partition interval and the index set. PostgreSQL’s documentation explains how each mechanism behaves but does not publish a benchmark for webhook workloads, so the numbers in this guide are planning arithmetic, not measurements.
Confirm your PostgreSQL version first
The SQL in this guide assumes PostgreSQL 14 or later. Run SHOW server_version; before copying anything, because several features depend on the major version.
As an Amazon Associate I earn from qualifying purchases.
- When this guide was prepared, the PostgreSQL documentation listed 18.x as the current release line and 19 as the development line. A newer major release may have shipped since then, so read the documentation for the version you actually run.
ALTER TABLE ... DETACH PARTITION ... CONCURRENTLYrequires PostgreSQL 14 or later and cannot run inside a transaction block.- LZ4 TOAST compression requires PostgreSQL 14 or later and a server built with LZ4 support. To check the build, run
pg_config --configureand look for--with-lz4.
Start with the queries and the retention rule
Write down four things before choosing any structure. Each one changes the answer.
Recommended Free Tools
- Read paths. Which filters appear in dashboards, support tooling, and replay jobs: tenant plus time range, delivery identifier, endpoint, status, event type, or a field inside the payload.
- Write rate. Average and peak inserts per second. Peaks often matter more than the daily total, because they drive index maintenance and autovacuum behavior.
- Row and payload width. Two billion narrow metadata rows and two billion rows carrying multi-kilobyte bodies have very different heap sizes, index sizes, and cache behavior.
- Retention and lock tolerance. The exact window, such as 90 days for everything or 90 days for metadata with 14 days for bodies, whether any rows must be kept longer for dispute or legal reasons, and how much lock contention the expiry job can cause.
A starting schema
The table below is a reasonable starting point, not a finished design. It partitions on received_at, keeps filter columns as ordinary columns, and stores the body as jsonb.
#1 Best Overall
CREATE TABLE webhook_audit (
id bigint GENERATED ALWAYS AS IDENTITY,
received_at timestamptz NOT NULL,
delivery_id uuid NOT NULL,
tenant_id bigint NOT NULL,
endpoint_id bigint NOT NULL,
event_type text NOT NULL,
status smallint NOT NULL,
payload jsonb,
PRIMARY KEY (received_at, id)
) PARTITION BY RANGE (received_at);
CREATE TABLE webhook_audit_2026_10 PARTITION OF webhook_audit
FOR VALUES FROM ('2026-10-01 00:00:00+00') TO ('2026-11-01 00:00:00+00');The primary key has to include received_at, because a unique constraint on a partitioned table must contain every partition key column. That rule also governs how you enforce uniqueness on delivery identifiers, which the FAQ covers.
Partition key and interval
Why receipt time is the default key
Declarative partitioning routes each row into a child table according to range, list, or hash bounds. The parent is a logical table with no storage of its own. When a query’s WHERE clause constrains the partition key, the planner can skip child tables that cannot match. That is partition pruning. Audit queries are usually bounded in time (“failures for this tenant in the last six hours”), so a timestamp key lets most reads touch only a few children.
The same key serves expiry, because retention is almost always defined in time and whole time windows can be removed as units. The PostgreSQL partitioning documentation puts it this way: “One of the most important advantages of partitioning is precisely that it allows this otherwise painful task to be executed nearly instantaneously by manipulating the partition structure, rather than physically moving large amounts of data around.” That describes partition-based data management. It does not mean every partition operation is instant or lock-free, as the expiry section explains.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Use the time your system received the webhook, not the timestamp the provider claims. Provider timestamps can be missing, skewed, or reordered during replays. Keep the provider’s timestamp as an ordinary display column if you need it.
Choosing the interval with arithmetic
The documentation warns in both directions. Too few partitions can leave each index large and locality poor, while too many increase planning overhead. It does not name a best interval, so work from the numbers. The table assumes 2 billion rows retained over a 90-day window, an even average of about 22.2 million rows per day, and 300 bytes of heap per metadata row. It ignores peaks, indexes, and TOAST. Replace these assumptions with measurements from your own system.
Rank #2
| Interval | Average rows per partition | Child tables for a 90-day window | Approximate heap per partition | Extra retention beyond the 90-day rule |
|---|---|---|---|---|
| Daily | about 22 million | about 90 | about 6.7 GB | up to about 1 day |
| Weekly | about 156 million | about 13 to 14 | about 47 GB | up to about 6 days |
| Monthly | about 667 million | about 3 to 4 | about 200 GB | up to about 30 days |
Monthly partitions are cheap to manage, but they overshoot retention by up to a month and put a 200 GB heap, plus its indexes, behind every expiry decision. Daily partitions expire precisely but give the planner and autovacuum about ninety children to track. Weekly is often the middle ground. If your peak days run well above the average, size the interval against the peak partition, because that is the one that determines index build time and vacuum duration. Choose the interval where your hot queries touch a handful of children and the largest partition’s indexes can be built and vacuumed inside your maintenance windows.
Late events and the default partition
An insert whose timestamp falls outside every defined range fails unless a default partition exists. A default partition catches those rows, but it creates a cost later. Creating a partition for a range the default already holds requires checking the default for rows that belong in the new range, and that check slows as the default grows. Create the next few partitions ahead of time, and track the default’s size. If your default partition is named webhook_audit_default, run SELECT pg_size_pretty(pg_relation_size('webhook_audit_default')); on a schedule. A growing default is a sign that partition creation is falling behind.
Indexes built from access paths
Every index is a copy of part of every row, maintained on every insert. Start from the read paths you wrote down, and add an index only when a representative query needs it.
B-tree for equality and time-range filters
B-tree is the default and handles equality and ordered range conditions. For this table, the usual candidates are:
(tenant_id, received_at)for “this tenant’s events in a time window.”(delivery_id)for support lookups by delivery. This index does not need to be unique.(status, received_at)for failure dashboards that scan recent non-2xx deliveries.
Verify each candidate with EXPLAIN (ANALYZE, BUFFERS) on realistic data volume. Confirm two things in the plan: the index is used, and only the child tables the predicate could match appear in the plan. Where pruning happens at execution time, look for “Subplans Removed.” Measure insert throughput after adding each index, not just query latency.
BRIN for append-ordered timestamps
A BRIN index stores a summary for each run of adjacent heap pages (128 pages per range by default, adjustable with pages_per_range). It is very small, but it only helps when the indexed values follow physical row order. Append-only audit tables often satisfy that for received_at, although backfills and replays can break it. Check correlation after ANALYZE has run on the partition:
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errorsSELECT attname, correlation
FROM pg_stats
WHERE tablename = 'webhook_audit_2026_10' AND attname = 'received_at';Values near 1 or -1 indicate strong correlation. Values near 0 mean BRIN will rarely skip blocks. If the correlation looks good, build a candidate on a partition that is receiving writes with CREATE INDEX CONCURRENTLY and compare it with B-tree on the same query:
CREATE INDEX CONCURRENTLY webhook_audit_2026_10_received_brin
ON webhook_audit_2026_10 USING brin (received_at) WITH (pages_per_range = 128);BRIN results are lossy, so PostgreSQL rechecks the heap tuples it returns. Ranges that have not been summarized yet are scanned rather than skipped until summarization happens. Autovacuum summarizes them eventually, and SELECT brin_summarize_new_values('webhook_audit_2026_10_received_brin'); summarizes pending ranges on demand. Use BRIN for broad time-window scans, and keep B-tree on tenant and delivery columns where you need precise lookups.
Leave payload fields unindexed until a search is required
A GIN index over an entire jsonb body is the reflex move for payload search, and on a write-heavy audit table it is expensive. Add one only after you have confirmed that people really search by payload content. When the need is narrow, an expression index on a single key costs far less. An expression index helps only queries that use the identical expression:
CREATE INDEX CONCURRENTLY webhook_audit_2026_10_payload_id
ON webhook_audit_2026_10 ((payload->>'id'));Building indexes on a partitioned table without a long lock
A plain CREATE INDEX on the parent builds every child in one statement and blocks writes while it runs. Use the staged method instead:
- Create the parent index without building it on data:
CREATE INDEX ON ONLY webhook_audit (tenant_id, received_at);. The parent index is a placeholder. It stays invalid until every partition has an attached index. - For each child, build the index concurrently:
CREATE INDEX CONCURRENTLY webhook_audit_2026_10_tenant_received ON webhook_audit_2026_10 (tenant_id, received_at);. Concurrent builds cannot run inside a transaction block and take longer than ordinary builds. If one fails, it leaves an invalid index behind; drop it and retry. - Attach each child index to the parent:
ALTER INDEX webhook_audit_tenant_received ATTACH PARTITION webhook_audit_2026_10_tenant_received;. - Confirm the parent index is valid once every child is attached:
SELECT indexrelid::regclass, indisvalid FROM pg_index WHERE indrelid = 'webhook_audit'::regclass;.
Payload storage and compression
What TOAST does with large bodies
PostgreSQL cannot store a row that spans pages, so large variable-length values are handled by TOAST. Depending on the column’s settings, TOAST compresses a value, moves it to a separate TOAST table, or does both. Queries see the same data either way, and small values stay inline and are unaffected.
Compression is chosen per column. The COMPRESSION option sets the method for a column; otherwise the default_toast_compression setting applies at insert time. PostgreSQL 18 documents pglz, and lz4 when the server was built with LZ4 support. A change of method affects only values inserted afterward. Existing rows keep their stored form until they are rewritten.
SHOW default_toast_compression;
ALTER TABLE webhook_audit ALTER COLUMN payload SET COMPRESSION lz4;Test the change on a staging copy first. Then check the setting on the child tables in psql, and measure the real effect with pg_column_size(payload) on a sample of production-like bodies, which returns the stored size after compression. Do not assume every payload compresses, or that one method wins on CPU, disk, and read latency together.
Storage strategy: EXTENDED or EXTERNAL
Each column also has a storage strategy. The default, EXTENDED, allows compression and out-of-line storage. EXTERNAL keeps values out of line without compressing them. Set it with ALTER TABLE ... ALTER COLUMN ... SET STORAGE.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →| Strategy | Compression | Out-of-line storage | Trade-off | When to test it |
|---|---|---|---|---|
| EXTENDED (default) | Yes | Yes | Smaller disk use, with decompression CPU on read | Most webhook bodies |
| EXTERNAL | No | Yes | Larger disk use | Frequent substring reads on wide text or bytea values; rarely relevant for jsonb |
Separate metadata from bodies when reads differ
Most list views need only the metadata columns, and only detail views need the body. Filtered fields belong in ordinary columns, so routine list queries never have to fetch a large body. If bodies are read rarely, you can also split them into a sibling table keyed by (received_at, id) and partitioned on the same interval, so pruning and expiry line up. The costs are a join on detail views, two tables to keep consistent during ingestion, and two expiry jobs. The benefit is that bodies can expire earlier than metadata, which many retention policies allow.
Expiry and space reclamation
Detach, archive, and drop whole partitions
When an entire interval falls outside retention, remove it as one unit. On PostgreSQL 14 and later, the concurrent detach is the safer default:
- Detach the expired child without holding the strongest lock on the parent:
ALTER TABLE webhook_audit DETACH PARTITION webhook_audit_2026_07 CONCURRENTLY;. This takes SHARE UPDATE EXCLUSIVE on the parent rather than the ACCESS EXCLUSIVE lock a plain detach requires. Check the command restrictions in the documentation for your version. - Archive or export the detached table while it stands alone, and verify the archive before continuing.
- Drop it:
DROP TABLE webhook_audit_2026_07;.
The plain DETACH PARTITION takes an ACCESS EXCLUSIVE lock on the parent. While it waits, it queues behind existing readers and blocks new ones, and while it runs, ingestion stalls. Use the simple form only where a short outage is acceptable.
Delete in batches only for exceptions
Row deletes are the fallback for rules that do not align with partition boundaries, such as erasing one tenant’s history on request. Run them in small batches so each transaction stays short. Include received_at in the outer predicate as well as the subquery, so the planner can prune partitions:
Free tools Windows power users keep installed
One-click scans. No signup required.
DELETE FROM webhook_audit
WHERE received_at < '2026-07-01'
AND id IN (
SELECT id FROM webhook_audit
WHERE tenant_id = 4211 AND received_at < '2026-07-01'
ORDER BY id
LIMIT 5000
);Repeat the statement until it deletes zero rows, and schedule it outside peak ingestion. Each batch leaves dead tuples behind, so the table needs vacuum to keep pace.
What VACUUM reclaims and what it returns
PostgreSQL does not physically remove deleted or updated row versions at the moment of the change. Plain VACUUM marks that space reusable within the table and runs alongside normal reads and writes, but it usually does not return the space to the operating system. VACUUM FULL rewrites the table to compact it and holds an ACCESS EXCLUSIVE lock for the duration. Among these options, dropping a partition is the only one that returns disk space without rewriting a table.
| Operation | Lock behavior | Space returned to the operating system | Typical use |
|---|---|---|---|
| Plain VACUUM | Allows normal reads and writes | Usually no; space is reused inside the table | Routine maintenance after batched row deletes |
| VACUUM FULL | ACCESS EXCLUSIVE on the table | Yes, by rewriting the table | Rare recovery from severe bloat when downtime is acceptable |
| DROP TABLE on an expired partition | ACCESS EXCLUSIVE on the dropped table only | Yes, when the table is dropped | Standard expiry for whole intervals |
If batched deletes leave a busy child with more dead tuples than autovacuum keeps up with, tighten its autovacuum thresholds on that child rather than running VACUUM FULL.
Before you go live
- Load a copy at production row width and run each hot query with
EXPLAIN (ANALYZE, BUFFERS). - Time a full cycle on that copy: create the next partition, detach concurrently, archive, and drop.
- Confirm that the default partition stays small and that future partitions exist several intervals ahead.
- After a realistic load, check BRIN correlation and summarization coverage.
- Record index sizes and insert latency with and without each index, and keep only the indexes that pay for their write cost.
”
Frequently Asked Questions
How do I make sure a webhook delivery is recorded only once?
A unique constraint on a partitioned table must include the partition key, so UNIQUE (delivery_id) on webhook_audit is not available. You have two options. A unique constraint on (delivery_id, received_at) prevents duplicates only when the timestamp matches, which is rarely enough for retries that arrive later. A small deduplication table keyed by delivery_id, with its own retention, written in the same transaction as the audit insert, gives a true global guarantee. It adds one write and one index for every delivery, so measure that overhead before committing to it.
Can I change the partition interval after launch?
Yes, for range partitions. Existing children are not rewritten. To change the interval going forward, create new children whose bounds start where the last existing child ends and do not overlap. Mixed widths work, and pruning still follows the bounds, but your expiry job and dashboards must handle partitions of different sizes.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
