Storing Billions of Webhook Audit Logs in PostgreSQL: Partitioning, Indexing, Compression, and Retention Every SaaS platform that sends or receives webhooks eventually needs a...
api audit log storage
audit log database design
automated table partitioning
database architecture webhooks
database bloat optimization
database optimization for webhooks
database partitioning strategies
declarative partitioning postgres
enterprise webhook infrastructure
high throughput postgresql
high-volume webhook data
incoming webhook logging
managing massive database tables
millions of rows postgresql
optimize audit logs postgres
outgoing webhook tracking
pg_partman postgresql
pg_partman tutorial
postgres data retention policies
postgres indexing strategies
postgres logging solutions
postgresql large tables
postgresql partition by range
postgresql partitioning
postgresql performance tuning
postgresql scaling limits
postgresql table bloat
PostgreSQL table partitioning webhooks
postgresql time-series tables
postgres query optimization
reliable webhook storage
saving webhook payloads
scale postgres tables
scale webhook logs
scaling database for webhooks
scaling postgresql database
store billions of webhooks
store webhook payloads
time based table partitioning
time series data postgres
webhook audit logs
webhook audit log storage
webhook backend design
webhook compliance storage
webhook data retention
webhook delivery tracking
webhook event logging
webhook infrastructure
webhook logging architecture
webhook observability
webhook scalability
webhook system architecture
Storing Billions Of Webhook Audit Logs In Postgre SQL Partitioning Indexing Compression And Retent
Storing Billions of Webhook Audit Logs in PostgreSQL: Partitioning, Indexing, Compression, and Retention
Every SaaS platform that sends or receives webhooks eventually needs a durable record of every delivery: to debug failures, to power customer-facing delivery logs and replay tools, and to satisfy audit requirements. At a few hundred deliveries per second, that record becomes one of the largest tables you own.
This guide walks through a production design for keeping billions of webhook audit records in PostgreSQL using time-based declarative partitioning, pg_partman, pg_cron, targeted indexing, payload compression, and partition-level retention.
Version notes. Everything below was checked against the official PostgreSQL, pg_partman, and pg_cron documentation as of September 29, 2026. PostgreSQL 18 is the current stable major release (18.6 was the latest minor release in August 2026). PostgreSQL 19 is in beta (Beta 4 shipped September 24, 2026, with general availability planned for October 2026), so anything marked "PG 19" may still change before release. pg_partman 5.5.0 (July 2026) is the latest release at the time of writing.
- Why a single table stops scaling A typical webhook audit record carries metadata plus the full HTTP request and response:
Column group Examples
Identity id, webhook_id, tenant_id
Routing endpoint_url, event_type
Outcome status_code, execution_time_ms, attempt_number
Bodies request_headers, request_payload, response_headers, response_payload (usually jsonb)
Time created_at
The arithmetic is unforgiving. At 500 deliveries per second you write 43.2 million rows a day, about 1.3 billion a month and roughly 15.8 billion a year.
There is no magic row count at which PostgreSQL "hits a wall"; where you feel it depends on row width, hardware, and how many indexes you maintain. But the failure modes on one huge table are predictable:
Index maintenance outgrows memory. An index led by tenant_id, such as (tenant_id, created_at), sends each insert to a different part of the B-tree depending on the tenant. Once the index is larger than the memory available for caching, inserts turn into random I/O. Random UUIDv4 primary keys are even worse for the same reason: every insert lands at a random leaf page.
Cache churn. A firehose of new rows continuously pulls fresh pages through shared_buffers and the OS cache, pushing out the pages your application queries actually need.
Large payloads go out of line (TOAST). When a row exceeds roughly 2 kB, PostgreSQL compresses and/or moves wide values into a companion TOAST table, leaving an 18-byte pointer in the main heap tuple. That keeps the main table compact, but reading a payload needs extra lookups, and the TOAST table has its own vacuum and bloat behavior.
Deleting old rows is the most expensive way to expire data. A retention job such as DELETE ... WHERE created_at < now() - interval '30 days' produces dead tuples, a large burst of WAL (which also has to be shipped to every standby), and sustained autovacuum work. Plain VACUUM makes the space reusable inside the table, but it only returns space to the operating system when the empty pages happen to be at the end of the file. Compacting the rest traditionally meant VACUUM FULL, which takes an ACCESS EXCLUSIVE lock. PostgreSQL 19 adds REPACK ... CONCURRENTLY to reduce that pain, but not deleting in the first place is still better.
Time-based partitioning attacks all four problems at once: each partition's indexes stay small enough to cache, old data is removed by dropping whole tables, and the hot working set is confined to the newest partitions.
What's new in PostgreSQL 18 and 19 that matters for this workload
Feature Version Why it matters here
uuidv7() built-in 18 Time-ordered UUIDs keep primary-key index inserts near the right edge of the B-tree instead of scattering them, and you no longer need an extension or client library.
B-tree skip scan 18 A multicolumn index can now be used in more cases when leading columns are not constrained. It pays off mainly when the skipped column has few distinct values, so treat it as a safety net rather than a replacement for designing indexes around your queries.
Asynchronous I/O subsystem 18 Can speed up sequential scans, bitmap heap scans, and vacuum, which helps wide time-range scans over many partitions.
Default TOAST compression changes from pglz to lz4 19 (beta) The exact setting recommended in section 7 becomes the default.
max_locks_per_transaction default rises from 64 to 128 19 (beta) Directly relevant if queries touch many partitions (see section 9). The release notes point out that, because of a change in lock allocation, settings effectively need to be doubled to match previous capacity.
COPY TO on partitioned tables and JSON output 19 (beta) Simplifies exports. Before 19 you had to use COPY (SELECT ...) for partitioned parents.
REPACK CONCURRENTLY 19 (beta) Reclaims space and reorganizes a table without blocking reads and writes.
Parallel autovacuum for a table's indexes 19 (beta) Helps when individual partitions carry many indexes.
Also note that PostgreSQL 14 stops receiving fixes on November 12, 2026. Both DETACH PARTITION ... CONCURRENTLY and pg_partman 5.x require PostgreSQL 14 or newer, so plan your upgrade now if you are still on 14.Time-series range partitioning
Declarative partitioning arrived in PostgreSQL 10 and has been refined in every release since. A partitioned table is a logical parent with no storage of its own; rows are routed to physical child tables (partitions) according to the partition key. At plan time and, for parameterized queries, at execution time, PostgreSQL applies partition pruning (enable_partition_pruning, on by default) to skip partitions that cannot match the WHERE clause.
The parent table
Code example
Copy code
CREATE TABLE webhook_audit_logs (
id uuid NOT NULL DEFAULT uuidv7(), -- PG 18+. On older versions use gen_random_uuid()
webhook_id uuid NOT NULL,
tenant_id uuid NOT NULL,
endpoint_url text NOT NULL,
event_type varchar(100) NOT NULL,
request_headers jsonb,
request_payload jsonb,
response_headers jsonb,
response_payload jsonb,
status_code integer NOT NULL,
execution_time_ms integer NOT NULL,
attempt_number integer NOT NULL DEFAULT 1,
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (id, created_at) -- must include the partition key
) PARTITION BY RANGE (created_at);
A note on the identifier: a commonly copied snippet uses DEFAULT gen_random_bytes(). That is not a valid UUID default (gen_random_bytes(n) comes from the pgcrypto extension, requires a length argument, and returns bytea). Use uuidv7() on PostgreSQL 18+, or gen_random_uuid() (built in since PostgreSQL 13) if you are on an older release and can accept random keys.
The primary key rule (and what it means for deduplication)
Any PRIMARY KEY or UNIQUE constraint on a partitioned table must include all of the partition key columns, and the partition key cannot contain expressions or function calls for this purpose. If you declare PRIMARY KEY (id) on the table above, PostgreSQL rejects it with a feature_not_supported error, because a unique index is enforced per partition and there are no global indexes.
The practical consequence is easy to miss: PostgreSQL cannot guarantee that id is unique across partitions. With uuidv7() or gen_random_uuid() collisions are astronomically unlikely, but if you need hard idempotency (for example, "never record the same delivery ID twice"), enforce it outside the big table: in the ingestion service, in a small unpartitioned deduplication table, or in a short-TTL cache.
Creating partitions by hand (and why not to)
Code example
Copy code
CREATE TABLE webhook_audit_logs_p20260928 PARTITION OF webhook_audit_logs
FOR VALUES FROM ('2026-09-28 00:00:00+00') TO ('2026-09-29 00:00:00+00');
CREATE TABLE webhook_audit_logs_p20260929 PARTITION OF webhook_audit_logs
FOR VALUES FROM ('2026-09-29 00:00:00+00') TO ('2026-09-30 00:00:00+00');
If an insert arrives with a created_at that falls outside every partition's range and there is no default partition, the insert fails with an error along the lines of no partition of relation "webhook_audit_logs" found for row. For an ingestion path, that is an outage. Automate partition creation (next section).
Always use timestamptz, and write partition bounds with an explicit UTC offset so the boundaries do not depend on the session time zone.
Choosing partition granularity
There is no official formula. Pick the interval from three inputs: how many rows and gigabytes one partition would hold, how granular your retention needs to be, and how many partitions you will keep at once.
Volume (rule of thumb) Typical interval Why
Above ~10M events/day Daily Keeps each partition's hot indexes small and gives day-level retention granularity
~1M to 10M events/day Daily or weekly Weekly if daily partitions would be tiny
Below ~1M events/day Weekly or monthly Avoids a swarm of nearly empty tables
These thresholds are heuristics, not limits.
How many partitions is too many? The PostgreSQL documentation warns that too many partitions increase planning time and memory use, and that memory can grow significantly when many sessions each touch many partitions, because each backend caches metadata for every partition it touches. It also says the planner handles partition hierarchies of up to a few thousand partitions fairly well as long as pruning removes most of them. So the popular "keep it under 1,000" advice is a reasonable conservative target, not a hard rule. Measure planning time and per-backend memory with your real queries.
The arithmetic still matters: hourly partitions kept for three years would be 26,280 tables, which is well outside comfortable territory. Daily partitions kept for a year are 365, which is fine. Daily partitions kept for six years are about 2,190, at which point you should consider consolidating old data into monthly partitions or moving it off the instance.
- Automating the partition lifecycle with pg_partman and pg_cron pg_partman pre-creates upcoming partitions and enforces retention. Version 5.x is a major rewrite, and a lot of older tutorials no longer work:
Older tutorials (pg_partman 4.x) pg_partman 5.x
p_type := 'native' Trigger-based partitioning was removed; everything is declarative. p_type now means range (the default) or list.
p_interval := 'daily' (or weekly, hourly, ...) Intervals must be valid PostgreSQL interval values, such as '1 day'. The old keywords are rejected with an error.
Minimum PostgreSQL 9.4+ Minimum PostgreSQL 14
Partition suffixes like _p2026_09_29 Suffixes are simplified to YYYYMMDD, for example _p20260929
Step 1: install into a dedicated schema
Code example
Copy code
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;
Step 2: hand the parent table to pg_partman
Code example
Copy code
SELECT partman.create_parent(
p_parent_table := 'public.webhook_audit_logs',
p_control := 'created_at',
p_interval := '1 day',
p_premake := 7
);
p_premake := 7 keeps seven future partitions ready, which protects ingestion against a stalled maintenance job.
Default partition trade-off. By default, create_parent also creates a default partition that catches rows outside every defined range (it is controlled by p_default_table). That is a useful safety net, but PostgreSQL refuses DETACH PARTITION ... CONCURRENTLY on a table that has a default partition. Choose deliberately:
Keep the default partition as insurance, and alert if it ever contains rows. It is named _default, for example webhook_audit_logs_default.
Or pass p_default_table := false if you want to detach partitions concurrently yourself and prefer out-of-range inserts to fail loudly.
Step 3: set a retention policy
Code example
Copy code
UPDATE partman.part_config
SET retention = '30 days',
retention_keep_table = true -- detach only; export, verify, then DROP yourself
WHERE parent_table = 'public.webhook_audit_logs';
How the retention options interact:
Setting Result
retention_keep_table = true (the default) Partitions past the retention window are detached but kept as standalone tables
retention_keep_table = false Partitions are dropped
retention_schema = 'archive' Partitions are detached and moved to that schema; this takes precedence over retention_keep_table
Note that a configuration that sets retention_keep_table = false and a retention_schema (which is common in copied examples) does not drop anything: the tables are moved to the archive schema. The pg_partman project deliberately defaults to the non-destructive behavior, and it is a good idea to leave it that way until your export pipeline is proven.
Also note that 30 days is only an example. See section 8 for what compliance frameworks typically expect.
Step 4: schedule maintenance
Either run the pg_partman background worker (pg_partman_bgw, configured in shared_preload_libraries with an interval) or schedule run_maintenance_proc() with pg_cron:
Code example
Copy code
postgresql.conf (requires a restart)
shared_preload_libraries = 'pg_cron'
cron.database_name = 'appdb' # defaults to 'postgres'
Code example
Copy code
CREATE EXTENSION pg_cron;
SELECT cron.schedule(
'partman-maintenance',
'0 * * * *', -- every hour, on the hour
$$CALL partman.run_maintenance_proc()$$
);
Things worth knowing about pg_cron:
It only loads if it is in shared_preload_libraries, and it can only be installed in one database per cluster. To run jobs against other databases, use cron.schedule_in_database().
Schedules run in GMT unless you set cron.timezone.
On managed services you set these through the parameter group instead of postgresql.conf. Amazon RDS and Aurora document this exact pg_partman plus pg_cron pairing.
run_maintenance_proc() is preferred over the plain run_maintenance() function because it commits after each partition set, which reduces lock contention.
Verify the setup:
Code example
Copy code
SELECT * FROM partman.show_partitions('public.webhook_audit_logs');
SELECT count(*) FROM webhook_audit_logs_default; -- should always be 0
SELECT * FROM cron.job_run_details ORDER BY start_time DESC LIMIT 10;
- Indexing partitioned audit tables Each partition has its own local indexes. Because pruning routes most queries to one or a few small partitions, those indexes stay small and cacheable. The design task is to match indexes to your actual access patterns:
Access pattern Index
Delivery debugging: "all attempts for this webhook_id, ordered by time" B-tree on (webhook_id, created_at)
Customer portal: "this tenant's deliveries in the last 24 hours" B-tree on (tenant_id, created_at DESC)
Failure dashboards: "this tenant's failed deliveries" Partial index WHERE status_code >= 400
Sub-day time-window scans, such as "the last 15 minutes" BRIN on created_at
Lookup by a field inside the payload Expression index or GIN, only if you really need it
B-tree indexes
Code example
Copy code
CREATE INDEX idx_wal_tenant_time ON webhook_audit_logs (tenant_id, created_at DESC);
CREATE INDEX idx_wal_webhook_time ON webhook_audit_logs (webhook_id, created_at DESC);
Creating an index on the partitioned parent creates a matching index on every partition, and pg_partman new partitions inherit it automatically. What you cannot do is CREATE INDEX CONCURRENTLY on the parent. For an existing large partition set, build the index without blocking writes like this:
Code example
Copy code
-- 1. Create an (initially invalid) index on the parent only
CREATE INDEX idx_wal_tenant_time ON ONLY webhook_audit_logs (tenant_id, created_at DESC);
-- 2. Build it concurrently on each partition, then attach
CREATE INDEX CONCURRENTLY idx_wal_tenant_time_p20260929
ON webhook_audit_logs_p20260929 (tenant_id, created_at DESC);
ALTER INDEX idx_wal_tenant_time ATTACH PARTITION idx_wal_tenant_time_p20260929;
-- repeat for every partition; the parent index becomes valid when all are attached
Two further notes. First, always include the time range in queries so pruning kicks in and the planner only considers one or a few partition indexes. Second, PostgreSQL 18's skip scan can help a (tenant_id, created_at) index serve some queries that omit tenant_id, but only when the skipped column has few distinct values. With thousands of tenants it will not rescue a query that ignores tenant_id.
BRIN for time windows inside a partition
Rows are appended to the current partition in roughly created_at order, so the physical position of a row correlates strongly with its timestamp. That is the ideal case for a Block Range Index (BRIN): instead of one entry per row, it stores a min/max summary per range of heap pages.
Code example
Copy code
CREATE INDEX idx_wal_created_brin
ON webhook_audit_logs USING brin (created_at)
WITH (pages_per_range = 32, autosummarize = on);
Practical guidance:
The default pages_per_range is 128 (about 1 MB of heap per entry). Lower values, such as 32, give finer-grained pruning at the cost of a slightly larger, still tiny, index. Measure with EXPLAIN (ANALYZE, BUFFERS) and watch "Rows Removed by Index Recheck".
autosummarize is off by default. Without it, freshly filled block ranges stay unsummarized until vacuum or a manual brin_summarize_new_values() call, and unsummarized ranges are always scanned.
BRIN only works when correlation is high. Check it: SELECT attname, correlation FROM pg_stats WHERE tablename = 'webhook_audit_logs_p20260929' AND attname = 'created_at';. Values near 1 are ideal; below roughly 0.7 BRIN stops being worthwhile.
With daily partitions, pruning has already narrowed a query to one day. BRIN earns its keep for sub-day windows and for very large partitions. If every query spans whole days, you may not need it.
BRIN cannot serve point lookups by tenant_id or webhook_id; that is the B-tree's job.
To see the real size difference on your own data, compare pg_size_pretty(pg_relation_size('index_name')) for a B-tree and a BRIN on the same column. Published comparisons vary widely with row width and range size, so measure rather than rely on someone else's numbers.
Partial indexes for failures
If the overwhelming majority of deliveries succeed, indexing them for error dashboards is wasted write I/O.
Code example
Copy code
CREATE INDEX idx_wal_failures
ON webhook_audit_logs (tenant_id, created_at DESC)
WHERE status_code >= 400;
The planner will only use a partial index when the query's WHERE clause implies the index predicate, so queries must include status_code >= 400 (or something that provably implies it).
Indexing payload fields
event_type is already a column, so index that directly rather than extracting it from JSON. For a custom field inside a payload, prefer a narrow expression index on the one key you look up:
Code example
Copy code
CREATE INDEX idx_wal_resource
ON webhook_audit_logs ((request_payload ->> 'resource_id'))
WHERE request_payload IS NOT NULL;
If you truly need to search arbitrary keys, a GIN index with jsonb_path_ops works, but it is expensive to maintain at this write rate. Consider indexing only a curated subset of fields or shipping the payloads to a search or analytics system instead.
Every index has a cost
Each additional index multiplies write work and storage, and it exists on every partition. Periodically check pg_stat_user_indexes.idx_scan and drop indexes nobody uses.
- Write path: keep ingestion fast Batch inserts. Multi-row INSERTs or COPY from a queue consumer beat one-row-per-transaction writes by a wide margin. PostgreSQL 19 further speeds up COPY FROM for text and CSV input using SIMD instructions. Use a connection pooler so hundreds of app instances do not each hold a backend. Keep the index set lean on the newest partition (see above). The hot partition takes all the writes. Insert through the parent, not into child tables, so routing stays in one place. Autovacuum and statistics. Append-only partitions are still vacuumed for visibility maps and freezing, and PostgreSQL's autovacuum does not analyze the partitioned parent itself. Run ANALYZE webhook_audit_logs; periodically, or schedule it, so parent-level statistics exist.
- Payload storage: TOAST, compression, and fidelity Disk usage is usually dominated by request and response bodies. A 100 KB payload multiplied across 100 million rows is about 10 TB before compression, so this is where storage decisions matter most.
How TOAST behaves
PostgreSQL uses 8 kB pages and does not let a row span pages. When a row value gets wider than about 2 kB (TOAST_TUPLE_THRESHOLD), the TOAST machinery compresses and/or moves fields out of line until the row shrinks to about TOAST_TUPLE_TARGET (also about 2 kB), or no more gains are possible. Out-of-line values are split into chunks of roughly 2 kB stored in the table's TOAST table, and the main tuple keeps an 18-byte pointer.
Code example
Copy code
Main heap page (8 kB) TOAST table
+--------------------------------+ +-----------------------------+
| id, webhook_id, status_code... | | chunk_id 98223, seq 0: ... |
| request_payload -> [pointer]---+-----------> | chunk_id 98223, seq 1: ... |
+--------------------------------+ +-----------------------------+
Use LZ4 compression (PostgreSQL 14+)
PostgreSQL 14 added LZ4 as an alternative to the legacy pglz for TOAST compression. LZ4 compresses and decompresses considerably faster, at a modestly worse compression ratio. That is usually the right trade for ingestion-heavy logging. It requires a server built with LZ4 support (standard packages and managed services generally include it).
Code example
Copy code
-- Cluster, database, or role level (applies to newly written values)
ALTER DATABASE appdb SET default_toast_compression = 'lz4';
or per column:
Code example
Copy code
ALTER TABLE webhook_audit_logs
ALTER COLUMN request_payload SET COMPRESSION lz4,
ALTER COLUMN response_payload SET COMPRESSION lz4;
Things to know:
Changing the setting affects newly written values only. Existing values keep whatever algorithm compressed them, and a table can contain a mix.
Verify what is actually in use with SELECT pg_column_compression(request_payload) FROM webhook_audit_logs LIMIT 10;. A NULL result means that value was not compressed.
PostgreSQL 19 (beta) changes the default TOAST compression from pglz to lz4, so new installs will get this behavior automatically.
Storage strategies, corrected
PostgreSQL has four per-column TOAST strategies:
Strategy Compress? Move out of line? Notes
PLAIN No No Fixed-width types
EXTENDED (default for jsonb, text, bytea) Yes Yes Compress first, then move out of line if the row is still too big
EXTERNAL No Yes Faster substring operations on wide text/bytea; useful for data that is already compressed
MAIN Yes Only as a last resort Tries hard to keep values inline; the opposite of what you want for large bodies
The default EXTENDED is already the right choice for large JSON bodies, so running SET STORAGE EXTENDED (as some guides suggest) changes nothing. The way to keep wide payloads from polluting hot pages is to not read them. A list view that selects only id, status_code, execution_time_ms, and event_type never has to fetch the TOASTed bodies, because only the small pointer sits in the heap tuple. Avoid SELECT * in list endpoints, and fetch payloads only for the detail view.
jsonb does not preserve what was received
jsonb normalizes documents: it discards insignificant whitespace, does not preserve key order, and keeps only the last value of duplicate keys. For an audit log that is a real limitation. Webhook signatures are typically computed over the exact raw request body, so if you ever need to re-verify a signature, replay a delivery byte for byte, or prove what was received, store the raw body (as bytea or text) instead of, or alongside, a parsed jsonb copy.
Redact before you store
Audit logs are a data-retention liability. Strip or hash Authorization headers, API keys, signing secrets, and cookies before persisting request headers, and decide up front which payload fields can contain personal data, since those logs fall under your privacy obligations (see section 8).
The hybrid architecture: metadata in PostgreSQL, bodies in object storage
If payloads are large and long-lived, storing them in PostgreSQL inflates storage, backups, WAL archiving, and replica disk. A hybrid design keeps the queryable metadata in the partitioned table and writes the bodies to S3 or GCS:
Code example
Copy code
CREATE TABLE webhook_audit_logs (
id uuid NOT NULL DEFAULT uuidv7(),
webhook_id uuid NOT NULL,
tenant_id uuid NOT NULL,
event_type varchar(100) NOT NULL,
status_code integer NOT NULL,
execution_time_ms integer NOT NULL,
attempt_number integer NOT NULL DEFAULT 1,
payload_key text, -- e.g. s3://bucket/2026/09/29/.json.zst
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
Consideration Payloads in PostgreSQL Hybrid (metadata in PostgreSQL, bodies in S3)
Ingestion bottleneck Disk I/O and TOAST compression CPU Object-store request rate and async worker throughput
Query capability Full SQL over the JSON SQL over metadata only; body search needs another engine (for example Amazon Athena)
Storage cost (us-east-1 list prices, verify current rates) EBS gp3 is about $0.08 per GB-month before replicas, Multi-AZ, and backups S3 Standard is $0.023 per GB-month for the first 50 TB; Standard-IA is $0.0125; Glacier Deep Archive is $0.00099
Backups and recovery Payloads inflate base backups and WAL Database stays small; the bucket has its own lifecycle and versioning
Consistency One transactional write Two systems: write the body first, then the row; sweep for orphans; keep the bucket lifecycle policy aligned with partition retention
Remember that database storage is billed several times over once you count standbys, backups, and snapshots, so the per-GB gap between block storage and S3 is wider in practice than the list prices suggest.
- Retention, tiering, and zero-downtime purging The biggest operational win of partitioning is replacing row-level DELETE with partition-level DETACH and DROP. Both are metadata-level operations. They generate no dead tuples and only a small amount of WAL compared to a bulk delete, so they scale with the number of partitions rather than the number of rows.
How long should you keep logs?
The 30-day example earlier is fine for a debugging window, but check your obligations before you settle on a number. Retention requirements vary:
Framework What it says about retention
PCI DSS v4.0 Retain audit log history for at least 12 months, with the most recent 3 months immediately available for analysis
HIPAA Requires certain compliance documentation to be retained for 6 years; whether your logs fall under that is an interpretation question for your compliance team
SOC 2 No fixed retention period; auditors evaluate whether you define a policy and follow it, and 12 months is a common expectation for security-relevant logs
GDPR No fixed period; the storage limitation principle says personal data, including identifiers in logs, must be kept no longer than necessary for the stated purpose
This is general information, not legal advice. The common pattern is tiered: a hot window in PostgreSQL (for example 30 to 90 days), a longer cold tier in object storage (Parquet in S3 or GCS), and tagged or minimized data for anything containing personal information.
Detaching without blocking writers
ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY was introduced in PostgreSQL 14 (not 12). Read the fine print:
Code example
Copy code
SET lock_timeout = '5s'; -- avoid queueing behind long transactions indefinitely
ALTER TABLE webhook_audit_logs
DETACH PARTITION webhook_audit_logs_p20260829 CONCURRENTLY;
It runs in two transactions, so it cannot be executed inside a transaction block.
In the first transaction it takes a SHARE UPDATE EXCLUSIVE lock on both the parent and the partition, marks the partition as detached, commits, and then waits for all transactions that were using the partitioned table to finish. In the second, it takes an ACCESS EXCLUSIVE lock on the partition (not the parent) to complete the detach. Long-running transactions can therefore delay the operation, but they do not block new writes to the other partitions.
It is not allowed if the table has a default partition (see section 4).
If the operation is cancelled or the server crashes after the first stage, finish it with ALTER TABLE ... DETACH PARTITION ... FINALIZE.
A CHECK constraint duplicating the partition bounds is added to the detached table so that re-attaching it later does not require a full scan.
If you let pg_partman do the detaching (via retention_keep_table = true), you do not need to run this yourself, but you should understand what the extension is doing and check its documentation for how it detaches on your version.
Archiving a detached partition to Parquet
Core PostgreSQL's COPY cannot write s3:// URLs or Parquet files on its own. Snippets that do COPY ... TO 's3://...' WITH (FORMAT 'parquet') rely on an extension, most commonly pg_parquet, which extends COPY to read and write Parquet on the local filesystem, S3, Azure Blob Storage, Google Cloud Storage, and HTTP(S), and supports json/jsonb columns. It must be added to shared_preload_libraries, so check whether your managed provider offers it.
Code example
Copy code
-- requires: shared_preload_libraries = 'pg_parquet' and CREATE EXTENSION pg_parquet;
COPY webhook_audit_logs_p20260829
TO 's3://webhook-cold-storage/webhook_audit_logs/2026/08/29.parquet'
WITH (format 'parquet');
If you cannot use the extension, alternatives include COPY ... TO STDOUT piped into a small exporter (Python with PyArrow, for example), or exporting from a replica with an external tool. PostgreSQL 19 also lets COPY TO emit JSON directly.
The safe purge sequence
Detach (via pg_partman retention or DETACH ... CONCURRENTLY).
Export the detached table to object storage.
Verify: compare row counts (and ideally checksums) between the detached table and the exported file, and confirm the object exists.
DROP TABLE the detached table.
Never automate step 4 before step 3 has been proven in your environment. A dropped partition is gone, which is exactly why pg_partman defaults to keeping detached tables.
- Pitfalls, tuning, and operational checklist Always filter on the partition key Code example Copy code -- BAD: no time filter, so every partition is a candidate SELECT * FROM webhook_audit_logs WHERE webhook_id = 'a89c31f4-...';
-- GOOD: pruning limits the scan to a single daily partition
SELECT * FROM webhook_audit_logs
WHERE webhook_id = 'a89c31f4-...'
AND created_at >= '2026-09-29 00:00:00+00'
AND created_at < '2026-09-30 00:00:00+00';
Without a time filter PostgreSQL cannot prune, so it plans and executes against every partition, which multiplies planning cost, lock acquisition, and index probes by the partition count. In the application, make the time range mandatory for every audit-log query (a sensible default such as "last 24 hours" works well in a customer portal).
Lock table pressure
A query that touches N partitions also locks each partition's indexes. Queries spanning hundreds of partitions can exhaust the shared lock table, which is sized from max_locks_per_transaction. The default is 64 on PostgreSQL 18 and earlier, and rises to 128 in PostgreSQL 19. Raise it if you routinely query wide time ranges or run maintenance across many partitions; the change requires a restart.
Configuration that matters
Code example
Copy code
enable_partition_pruning = on # default; keep it on
Off by default because they can increase planning time and memory.
Turn on only if you join partitioned tables on the partition key
or aggregate across many partitions, and measure the effect.
enable_partitionwise_join = off
enable_partitionwise_aggregate = off
max_locks_per_transaction = 128 # see above (restart required)
shared_buffers = '8GB' # common starting point: ~25% of RAM on a dedicated server
maintenance_work_mem = '1GB' # speeds index builds on new or attached partitions
default_toast_compression = 'lz4' # default from PG 19
Regarding work_mem: it is allocated per sort or hash operation, per node, and per parallel worker, so raising it globally can multiply quickly across concurrent queries. Tune it per role or per session for reporting workloads rather than setting a large global value.
Monitoring
Default partition row count. Any rows there mean the partition creation job fell behind or a client is writing bad timestamps.
Future partition runway. Alert if fewer than, say, 3 future partitions exist.
cron.job_run_details for failed maintenance runs.
Partition count and per-backend memory as the set grows.
Index usage via pg_stat_user_indexes.
Converting an existing table
To move an existing unpartitioned table to partitioning, you generally either create a new partitioned table and backfill it in batches (pg_partman includes partition_data_proc for moving data), or attach the existing table as a partition of a new parent. When attaching, add a CHECK constraint matching the intended bounds first (and validate it) so PostgreSQL can skip the full-table scan it would otherwise perform to verify the range.
When PostgreSQL stops being the right tool
If you need free-text or arbitrary-field search across payloads, analytical queries over billions of rows, or retention measured in many years with ad hoc access, move the cold tier to a columnar or search system (Parquet plus a query engine, ClickHouse, OpenSearch, or similar) and keep PostgreSQL for the recent, operationally hot window. Time-series extensions such as TimescaleDB are another option worth evaluating if you would rather have partition management and compression built in.
- Reference architecture Code example Copy code Incoming and outgoing webhooks (hundreds to thousands per second) | v Ingestion service (batching, redaction, dedupe) | | v v PostgreSQL: webhook_audit_logs Object storage (payload bodies) (PARTITION BY RANGE created_at) [hybrid design only] | +-----------------+------------------+-----------------------+ | | | | v v v v Partition t-1 Partition t0 Partitions t+1..t+7 Default partition (local indexes, (hot: B-tree + (pre-created by (safety net; BRIN, partial) BRIN + partial) pg_partman) alert if non-empty) | | older than retention window (pg_partman + pg_cron, hourly) v DETACH -> export to Parquet (pg_parquet) -> verify counts -> DROP TABLE Production checklist PostgreSQL 14 or newer (18 recommended); plan the 14 end-of-life migration (November 12, 2026). Primary key includes created_at; deduplication enforced outside the partitioned table if you need it. pg_partman 5.x configured with a valid interval ('1 day'), adequate premake, and an explicit decision on the default partition. Maintenance scheduled through pg_cron or pg_partman_bgw, with alerts on failures and on the default partition. Every application query filters on created_at. Indexes chosen from real access patterns; BRIN only where correlation is high; partial index predicates repeated in queries. LZ4 TOAST compression enabled; list endpoints never SELECT * the payload columns. Raw bodies stored if you need byte-exact replay or signature verification; sensitive headers redacted. Retention period mapped to your compliance obligations, with a cold tier if you need more than the hot window. Export verified before any DROP TABLE; retention_keep_table stays true until then. ANALYZE on the parent scheduled; max_locks_per_transaction sized for your widest queries. Summary Partitioning turns webhook audit logging from a growing liability into a predictable pipeline. Range partitioning on created_at keeps hot indexes small, pg_partman and pg_cron remove the manual partition babysitting, BRIN and partial indexes keep write costs down, LZ4 shrinks payloads and speeds ingestion, and detach-then-drop replaces expensive bulk deletes with metadata operations. PostgreSQL 18's uuidv7() fixes the random-key problem at the source, and PostgreSQL 19 will make LZ4 the default and raise the lock-table default, both of which fit this workload. Measure against your own traffic, and keep the export-and-verify step in front of every drop.
Sources
PostgreSQL 18 release notes: https://www.postgresql.org/docs/release/18.0/
PostgreSQL 19 (beta) release notes: https://www.postgresql.org/docs/19/release-19.html
PostgreSQL 14 announcement (DETACH PARTITION ... CONCURRENTLY): https://www.postgresql.org/about/news/postgresql-14-beta-1-released-2213
Commit introducing DETACH PARTITION ... CONCURRENTLY (behavior, locks, default-partition restriction): https://www.postgresql.org/message-id/E1lPX7q-0001lJ-NO%40gemulon.postgresql.org
PostgreSQL versions and support dates: https://www.postgresql.org/support/versioning/
PostgreSQL table partitioning documentation: https://www.postgresql.org/docs/current/ddl-partitioning.html
PostgreSQL TOAST documentation: https://www.postgresql.org/docs/19/storage-toast.html
PostgreSQL BRIN documentation: https://www.postgresql.org/docs/12/brin-intro.html
pg_partman repository and changelog: https://github.com/pgpartman/pg_partman and https://github.com/pgpartman/pg_partman/blob/development/CHANGELOG.md
pg_partman releases on PGXN: https://pgxn.org/dist/pg_partman/
pg_partman retention behavior: https://www.keithf4.com/partman-retention/
pg_cron: https://github.com/citusdata/pg_cron
Amazon RDS guide to pg_partman with pg_cron: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_Partitions.md
pg_parquet documentation: https://access.crunchydata.com/documentation/pg_parquet/latest/
Crunchy Data on LZ4 becoming the default in PostgreSQL 19: https://www.crunchydata.com/blog/postgres-19-compression-from-pglz-to-lz4
AWS S3 and EBS list prices are published on the AWS pricing pages; verify current rates for your region before budgeting.
Top comments (1)
The jsonb-doesn't-preserve-what-was-received point is the one people miss, if you need byte-exact replay or signature re-verification, storing only the parsed jsonb silently throws that away. The detach-then-drop replacing DELETE for retention is such a clean use of partitioning.