iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
For a webhook audit table in the billions of rows, the design that holds up best is usually a range-partitioned table keyed on the time each event was received, a small set of indexes matched to the lookups your team actually runs, payload bodies stored in a column you tune separately from searchable metadata, and expiry handled by detaching and dropping whole partitions instead of deleting rows one at a time.
Treat that as a starting hypothesis, not a recipe. PostgreSQL’s documentation explains how each mechanism behaves, but it does not publish a benchmark for webhook workloads. The number of rows alone does not decide the partition interval or the index set.
What actually decides the design
Five inputs decide the design. Measure each one before you commit to DDL.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problems- Query mix. Which filters appear together, such as tenant and time, endpoint and time, status and time, or a single delivery identifier, and how far back those queries reach.
- Ingestion rate. Average and peak events per second. Multiplied by the partition interval, this gives rows per partition.
- Payload width. Typical and largest body sizes, and how often readers open full bodies rather than metadata alone.
- Retention rules. Whether expiry is by calendar window, and whether any records must be kept longer or removed selectively.
- Operational limits. Tolerable lock durations, maintenance windows, replica lag, disk headroom, and who is allowed to run DDL.
As illustrative arithmetic only, a sustained 2,000 events per second produces about 172.8 million rows per day (2,000 × 86,400). A monthly partition at that rate would hold roughly 5.2 billion rows, while a daily partition would hold about 173 million. Those figures show why the interval is a real decision. They are not a threshold, because the right answer depends on the query and retention factors above.
#1 Best Overall
Confirm your PostgreSQL version first
The examples use PostgreSQL 18 syntax. At the time of writing, the official documentation listed the 18 line as current, with 18.6 as the point release it showed, and PostgreSQL 19 in development. If you run 19 or later, check its release notes before applying these steps, because defaults and operational behavior can change between major versions.
- Concurrent partition detach and the
pg_column_compression()function require PostgreSQL 14 or later. - The
lz4TOAST compression method exists only if the server was built with LZ4 support. Runpg_config --configureand look for--with-lz4before you set it. - Run
SHOW server_version;to confirm the exact version you are on.
Partitioning: key, interval, and lifecycle
Why range partitioning on received time
Declarative range partitioning routes each row into an ordinary child table based on the bounds of the partition key. The parent table holds no rows of its own. When a query filters on the partition key, PostgreSQL can prune child tables it does not need to read. A received-time key fits audit logs for two reasons: lookups usually bound a time window, and retention usually expires data by time.
The official PostgreSQL documentation, published by the PostgreSQL Global Development Group, describes the operational payoff 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 sentence describes partition-based data management. It does not mean every partition operation is instant or lock-free, as the detach steps later in this article show.
Choosing the interval
The documentation does not name a best interval. It warns in both directions: too few partitions can leave indexes large and reduce data locality, while too many increase planning overhead. The table below sets out the trade-offs. The right row depends on your own numbers.
| Interval | Child tables per year | Expiry granularity | Trade-off to test |
|---|---|---|---|
| Daily | About 365 | One day | Fine-grained expiry and smaller indexes per partition, but more partitions for the planner to consider and more DDL to automate |
| Weekly | About 52 | One week | A middle ground when volume is high but a daily partition count feels excessive |
| Monthly | 12 | One calendar month | Few partitions and simple automation, but each partition and its indexes can grow very large at high ingestion rates |
Keys must include the partition column
On a partitioned table, every primary key and unique constraint must include the partition key. Design the key around that rule from the start:
CREATE TABLE webhook_audit_log (n id bigint GENERATED BY DEFAULT AS IDENTITY,n received_at timestamptz NOT NULL,n tenant_id bigint NOT NULL,n endpoint_id bigint NOT NULL,n delivery_id uuid NOT NULL,n event_type text NOT NULL,n status smallint NOT NULL,n payload bytea NOT NULL, -- raw request bodyn PRIMARY KEY (received_at, id)n) PARTITION BY RANGE (received_at);A lookup by delivery ID alone cannot use that primary key, and without a time bound it will touch every partition. Either include a received-time range in those queries, or create a non-unique index on delivery_id. If delivery IDs must be unique, that constraint has to include received_at, so enforce it that way or in the application.
Create partitions ahead and plan for late events
An insert whose timestamp falls outside every existing partition fails, so the next window must exist before its data arrives:
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Rank #2
CREATE TABLE webhook_audit_log_2026_11 PARTITION OF webhook_audit_logn FOR VALUES FROM ('2026-11-01 00:00:00+00') TO ('2026-12-01 00:00:00+00');A default partition catches rows that match no range, but it complicates later maintenance. Attaching a new partition whose range already has rows sitting in the default partition requires PostgreSQL to check those rows, which can be slow on a large default partition. Concurrent detach is also refused while a default partition exists. Treat a default partition as a short-term safety net, not as the normal home for data. Events that arrive late for a window you have already detached will fail or land in the default partition, so set retention boundaries with that delay in mind.
Indexes: build them from the queries you run
Start with access paths you can name, then test each candidate against the plans it changes. Every index adds write work and storage on each partition it covers, so an index no query needs is pure cost.
Tenant and time B-tree
B-tree is the default index type and handles equality and ordered range conditions. For the common pattern of listing one tenant’s recent events, a composite index is the usual first candidate:
CREATE INDEX webhook_audit_log_tenant_time ON webhook_audit_log (tenant_id, received_at DESC);On a partitioned table this creates a matching index on each existing child, and partitions created later receive it automatically.
BRIN on received_at, only when physical order follows time
A BRIN index stores summaries of value ranges across adjacent blocks. It is compact, but it works only when the indexed values correlate with physical row order, which is typical for an append-only table ingested in time order. BRIN is lossy, so PostgreSQL rechecks candidate rows, and that recheck work is something a B-tree does not need.
Measure correlation before choosing BRIN. After an ANALYZE, the statistics view reports it per column:
SELECT tablename, attname, correlationnFROM pg_statsnWHERE tablename LIKE 'webhook_audit_log%' AND attname = 'received_at';Values near 1 or -1 indicate strong correlation; values near zero mean BRIN will be weak on that column. Late-arriving events and retries that write old timestamps into recent partitions lower the correlation, so check it after a realistic ingest pattern rather than on a freshly loaded test table.
CREATE INDEX webhook_audit_log_received_brin ON webhook_audit_log USING brin (received_at);A BRIN index is only as current as its summaries. Newly filled block ranges may stay unsummarized until vacuum processes them. You can summarize them explicitly with brin_summarize_new_values('webhook_audit_log_received_brin').
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Status and endpoint filters
A low-cardinality column such as status rarely justifies an index on its own. Its value is usually as the second key of a composite, for example (status, received_at DESC) or (endpoint_id, received_at DESC), and only when a dashboard or support workflow runs that query repeatedly.
Defer payload indexes
A GIN index over a JSON payload makes every field searchable and makes every write pay for that. Before adding one, confirm that payload-field search is a real requirement. If it is, extract the few fields you search into typed columns at ingest and index those with B-tree. That costs far less than indexing whole bodies.
Build indexes without blocking writes
A plain CREATE INDEX on a partitioned table builds each partition’s index while holding a SHARE lock, which blocks writes for the duration. On a live table, build the child indexes concurrently, create the parent index without descending into partitions, and attach the children to it:
-
Create the index on each child with
CONCURRENTLY:CREATE INDEX CONCURRENTLY webhook_audit_log_2026_10_tenant_timen ON webhook_audit_log_2026_10 (tenant_id, received_at DESC); -
Create the parent index with
ON ONLY, which builds no storage of its own:Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy.CREATE INDEX webhook_audit_log_tenant_timen ON ONLY webhook_audit_log (tenant_id, received_at DESC); -
Attach each child index to the parent index. The parent index becomes valid once every partition has an attached index:
ALTER INDEX webhook_audit_log_tenant_timen ATTACH PARTITION webhook_audit_log_2026_10_tenant_time;
Verify with EXPLAIN (ANALYZE, BUFFERS)
Run the real query with realistic parameters and check three things: which child tables the Append node scanned, whether the index was used with the expected conditions, and how many buffers and rechecks the scan performed.
EXPLAIN (ANALYZE, BUFFERS)nSELECT id, event_type, status, received_atnFROM webhook_audit_lognWHERE tenant_id = 42n AND received_at >= now() - interval '7 days'nORDER BY received_at DESCnLIMIT 50;With a received-time predicate, only the recent child tables should be scanned. Partitions removed at execution time appear as “Subplans Removed” rather than being absent from the plan. If the plan reads every child table, the predicate is not reaching the partition key in a form PostgreSQL can prune on.
Payload storage and compression
How PostgreSQL stores large values
A row cannot span multiple pages, so PostgreSQL uses TOAST for large variable-length values. TOAST can compress a value, move it to an associated TOAST table, or do both, transparently; queries still return the full value. For audit logs, the practical consequence is that a list query reading only metadata columns does not need to fetch the large values. Keep filter and display fields in ordinary columns and select only the columns each query needs.
Recommended Free Tools
Choose the compression method per column
PostgreSQL 18 documents two TOAST compression methods: pglz, which is always available, and lz4, which requires a build with LZ4 support. The COMPRESSION column option sets the method for a column. If you do not set it, the default_toast_compression setting is consulted when a value is inserted. The change applies to newly stored values; existing values keep their method until they are rewritten.
ALTER TABLE webhook_audit_log ALTER COLUMN payload SET COMPRESSION lz4;nALTER SYSTEM SET default_toast_compression = 'lz4'; -- cluster-wide, then reloadMeasure the result on each partition. The query below groups rows by stored method and compares raw and stored sizes; pg_column_compression() returns NULL for values stored uncompressed, which the COALESCE labels.
SELECT COALESCE(pg_column_compression(payload), 'uncompressed') AS method,n count(*) AS rows,n avg(octet_length(payload)) AS avg_raw_bytes,n avg(pg_column_size(payload)) AS avg_stored_bytesnFROM webhook_audit_log_2026_10nGROUP BY 1;The compression ratio depends entirely on payload content. Repetitive JSON often compresses well; data that is already compressed or random does not. Weigh the ratio against CPU cost on writes and reads, and against how often readers fetch full bodies.
Storage strategy: EXTENDED or EXTERNAL
The default storage strategy, EXTENDED, allows both compression and out-of-line storage. EXTERNAL keeps values out of line without compressing them. The documented benefit is faster substring operations on wide text and bytea values, paid for with more storage. If readers mostly fetch whole bodies, EXTENDED is the usual starting point. Change it only after measuring a substring-heavy workload.
Expiry and space reclamation
Expiry and vacuum are separate questions. Dropping a partition removes a whole time window without deleting its rows one by one. A row-level DELETE leaves dead tuples behind, and plain VACUUM makes that space reusable inside the table, but it generally does not return it to the operating system. The table compares the options.
| Method | Lock behavior | Space returned to the operating system | Best for |
|---|---|---|---|
Plain DETACH PARTITION |
ACCESS EXCLUSIVE lock on the parent, which blocks reads and writes on the table | Not applicable; the child leaves the parent, and its storage is untouched until dropped | Maintenance windows |
DETACH PARTITION ... CONCURRENTLY |
SHARE UPDATE EXCLUSIVE lock on the parent, which permits normal reads and writes; command restrictions apply | Not applicable until the detached table is dropped | Live systems |
DROP TABLE on a detached partition |
Affects only that table | Yes, when the table’s files are removed | Final step after archiving |
Batched DELETE followed by VACUUM |
Row-level locks, with no table-wide lock; generates dead tuples | Generally no; space is reused within the table | Selective exceptions that do not align with partition boundaries |
VACUUM FULL |
ACCESS EXCLUSIVE lock; rewrites the table | Yes | Rare reclamation after large deletes, during downtime |
Whole-window expiry, step by step
On a live system, detach the expired partition concurrently, archive it, then drop it:
-
Check the partition list with
d+ webhook_audit_login psql. A default partition is marked in that list, and if one exists, concurrent detach is unavailable. -
Detach the window. Run this as its own statement, outside a transaction block:
Free tools Windows power users keep installed
One-click scans. No signup required.
Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy.ALTER TABLE webhook_audit_log DETACH PARTITION webhook_audit_log_2026_07 CONCURRENTLY; -
If the detach is interrupted, the partition stays in a detach-pending state. Finish it with:
ALTER TABLE webhook_audit_log DETACH PARTITION webhook_audit_log_2026_07 FINALIZE; -
Archive the table before dropping it. A custom-format dump is one option, and restoring it to a scratch database to check row counts is a sensible step before the drop:
pg_dump --format=custom --table=webhook_audit_log_2026_07 --file=webhook_audit_log_2026_07.dump audit_db -
Drop the detached table, which removes its files:
DROP TABLE webhook_audit_log_2026_07;
Row deletes for exceptions
Use row-level deletes only for records that do not align with a partition boundary, such as a single tenant’s deletion request. Run them in bounded batches so each transaction holds its locks briefly, and keep autovacuum able to keep pace with the deletion rate. Otherwise dead tuples accumulate and queries read more pages than they need.
Failure modes to check
- Insert fails with “no partition of relation … found for row.” The next window was not created. Create it, and make the creation job run well before the boundary.
- Concurrent detach is refused. The statement is inside a transaction block, or a default partition exists. Run it as its own statement, and resolve the rows in the default partition first.
- A detach is left pending after an interruption. Run the
FINALIZEform shown above. - A plan touches every child table. The query lacks a predicate on
received_at, or it wraps the column in an expression such asdate(received_at), which prevents pruning and index use. Rewrite the predicate as a plain range on the column. - A BRIN query is slow with many rechecks. Physical order no longer follows time. Compare against the B-tree plan, and consider replacing BRIN or re-summarizing the unsummarized ranges.
- An index build blocked writes. A non-concurrent
CREATE INDEXran on the parent. Use the staged procedure for the next index. - Disk usage stays flat after large deletes. Plain vacuum reuses space inside the table without returning it to the operating system. Partition drops return space;
VACUUM FULLdoes too, but it takes an ACCESS EXCLUSIVE lock and rewrites the table, so reserve it for planned downtime.
Frequently Asked Questions
Can I convert an existing audit table into a partitioned table in place?
Not with a simple ALTER. PostgreSQL does not convert a regular table into a partitioned table in place. Create a new partitioned table with the same columns and key, copy history into the matching partitions in time-bounded batches, route new writes to the new table during a short cutover, verify row counts for each window, and only then rename the tables. Keep the old table until verification is complete.
Why store the raw webhook body as bytea instead of jsonb?
A jsonb value is a normalized document. Key order, whitespace, and duplicate keys are not preserved, and for duplicate keys only the last value is kept. If you verify webhook signatures, such as an HMAC computed over the raw body, during replay or audit, you need the exact bytes the sender transmitted. Store those bytes in bytea, and extract the few fields you search into typed columns at ingest.
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.
