PostgreSQL partitioning for event tables in audit logging

Why event and audit tables become a problem

Event tables and audit tables look similar, but they exist for different reasons.

An event table records things that happen: page views, emails sent, webhook calls, job runs. An audit table records who changed what and when: a status change, a permission update, a payout approval, often with “before” and “after” details.

Both grow quickly because they’re append-only. You rarely delete individual rows, and new rows arrive every minute. Even a small product can produce millions of log rows within weeks once you include background jobs and integrations.

The pain shows up in day-to-day work. Admin panels need quick filters like “errors from yesterday” or “actions by this user.” As the table grows, those basic screens start to lag.

You’ll usually notice a few symptoms first:

• Filters take seconds (or time out) even with a narrow date range.
• Indexes grow so large that inserts slow down and storage costs climb.
• VACUUM and autovacuum take longer, and you start noticing maintenance.
• Retention gets risky: deleting old rows is slow and creates bloat.

Partitioning is one way to deal with this. In plain terms, it splits one large table into many smaller tables (partitions) that share one logical name. PostgreSQL routes each new row into the right partition based on a rule, usually time.

That’s why teams look at PostgreSQL partitioning for event tables: it can keep recent data in smaller chunks, so PostgreSQL can skip entire partitions when a query only needs a time window.

Partitioning isn’t a magic speed switch. It can help a lot for queries like “last 7 days” and it makes retention simpler (dropping old partitions is fast). But it can also create new problems:

• Queries that don’t use the partition key may have to check many partitions.
• More partitions means more objects to manage and more ways to misconfigure things.
• Some unique constraints and indexes become harder to enforce across all data.

If your admin panel relies heavily on date filters and predictable retention rules, partitioning can be a real win. If most queries are “find all actions by user X across all history,” it may create headaches unless you design the UI and indexes carefully.

Typical access patterns for logs and audits

Event and audit tables grow in one direction: up. They get a steady stream of inserts and almost no updates. Most rows are written once, then read later during support work, incident reviews, or compliance checks.

That “append-only” shape matters. Write performance is a constant concern because inserts happen all day, while read performance matters in bursts (when support or ops needs answers quickly).

Most reads are filters, not random lookups. In an admin panel, someone usually starts broad (last 24 hours) and then narrows down to a user, an entity, or an action.

Common filters include:

• A time range
• An actor (user ID, service account, IP address)
• A target (entity type + entity ID, like Order #1234)
• An action type (created, updated, deleted, login failed)
• A status or severity (success/error)

Time range is the natural “first cut” because it’s almost always present. That’s the key insight behind PostgreSQL partitioning for event tables: many queries want a slice of time, and everything else is a second filter inside that slice.

Retention is the other constant. Logs rarely live forever. Teams often keep high-detail events for 30 or 90 days, then delete or archive. Audit logs may have longer requirements (365 days or more), but even then you usually want a predictable way to remove old data without blocking the database.

Audit logging also comes with extra expectations. You generally want history to be immutable, every record to be traceable (who/what/when plus request or session context), and access to be controlled (not everyone should see security-related events).

These patterns show up directly in UI design. The filters people expect by default - date pickers, user selectors, entity search, action dropdowns - are the same filters your table and indexes need to support if you want the admin experience to stay fast as volume grows.

How to tell if partitioning is worth it

Partitioning isn’t a default best practice for audit logs. It pays off when one table becomes so large that everyday queries and routine maintenance start working against each other.

A simple size hint: once an event table reaches tens of millions of rows, it’s worth measuring. When the table and its indexes grow into tens of gigabytes, even “simple” date-range searches can become slow or unpredictable because more data pages are read from disk and indexes become expensive to maintain.

The clearest query signal is when you regularly ask for a small time slice (last day, last week), but PostgreSQL still touches a large part of the table. You’ll see it as slow “recent activity” screens, or audits filtered by date plus user, action type, or entity ID. If query plans show large scans or buffer reads are consistently high, you’re paying for data you didn’t mean to read.

Maintenance signals matter just as much:

• VACUUM and autovacuum take much longer than they used to.
• Autovacuum falls behind and dead tuples (bloat) build up.
• Indexes grow faster than expected, especially multi-column indexes.
• Lock contention becomes more noticeable when maintenance overlaps with traffic.

Operational costs are the slow drip that pushes teams into partitioning. Backups and restores get slower as one table grows, storage creeps up, and retention jobs become expensive because large DELETEs create bloat and extra vacuum work.

If your main goals are a clean retention policy and faster “recent period” queries, partitioning is usually worth a serious look. If the table is moderate and queries are already fast with good indexing, partitioning can add complexity without a clear payoff.

Partitioning options that fit event and audit tables

For most audit and event data, the simplest choice is range partitioning by time. Logs arrive in time order, queries often focus on “last 24 hours” or “last 30 days,” and retention is usually time-based. With time partitions, dropping old data can be as simple as removing an old partition instead of running a large DELETE that bloats the table.

Time range partitioning also keeps indexes smaller and more focused. Each partition has its own indexes, so a query for last week doesn’t need to walk through one giant index covering years of history.

Other partitioning styles exist, but they fit fewer log and audit cases:

• List (tenant or customer) can work when you have a small number of very large tenants and queries usually stay within one tenant. It becomes painful when you have hundreds or thousands of tenants.
• Hash (even write spread) can help when you don’t have time-window queries and you want writes distributed evenly. For audit logs it’s less common because it makes retention and time-based browsing harder.
• Subpartitioning (time plus tenant) can be powerful, but complexity grows quickly. It’s mainly for very high volume systems with strict tenant isolation needs.

If you choose time ranges, pick a partition size that matches how you browse and retain data. Daily partitions make sense for very high volume tables or strict retention. Monthly partitions are easier to manage at moderate volume.

A practical example: if an admin team checks failed login attempts every morning and filters by the last 7 days, daily or weekly partitions mean the query only touches the most recent partitions. PostgreSQL can ignore the rest.

Whatever approach you choose, plan for the boring parts: creating future partitions, handling late-arriving events, and defining what happens at each boundary (end of day, end of month). Partitioning pays off when those routines stay simple.

How to pick the right partition key

A good partition key matches how you read the table, not how the data looks in a diagram.

For event and audit logs, start with your admin panel: what filter do people use first, almost every time? For most teams it’s a time range (last 24 hours, last 7 days, custom dates). If that’s true for you, time-based partitioning usually gives the biggest and most predictable win because PostgreSQL can skip entire partitions outside the selected range.

Treat the key as a long-term promise. You’re optimizing for the queries you’ll keep running for years.

Start with the “first filter” people use

Most admin screens follow a pattern: time range plus optional user, action, status, or resource. Partition by the thing that narrows results early and consistently.

A quick reality check:

• If the default view is “recent events,” partition by timestamp.
• If the default view is “events for one tenant/account,” tenant_id can make sense, but only if tenants are large enough to justify it.
• If the first step is always “pick a user,” user_id might sound tempting, but it typically creates too many partitions to manage.

Avoid high-cardinality keys

Partitioning works best when each partition is a meaningful chunk of data. Keys like user_id, session_id, request_id, or device_id can lead to thousands or millions of partitions. That increases metadata overhead, complicates maintenance, and often slows planning.

Time-based partitions keep partition count predictable. You choose daily, weekly, or monthly based on volume. Too few partitions (one per year) won’t help much. Too many (one per hour) adds overhead quickly.

Choose the right timestamp: created_at vs occurred_at

Be explicit about what time means:

• occurred_at: when the event happened in the product.
• created_at: when the database recorded it.

For audits, “occurred” is often what admins care about. But delayed delivery (offline mobile clients, retries, queues) means occurred_at can arrive late. If late arrivals are common, partitioning by created_at and indexing occurred_at for filtering can be more operationally stable. The other option is to define a clear backfill policy and accept that old partitions will occasionally receive late events.

Also decide how you store time. Use a consistent type (often timestamptz) and treat UTC as the source of truth. Format for the viewer’s timezone in the UI. That keeps partition boundaries stable and avoids daylight saving surprises.

Step by step: plan and roll out partitioning

Partitioning is easiest when you treat it like a small migration project, not a quick tweak. The goal is simple writes, predictable reads, and retention that becomes a routine operation.

A practical rollout plan

1. Pick a partition size that matches your volume. Monthly partitions are usually fine at a few hundred thousand rows per month. If you insert tens of millions per month, weekly or daily partitions often keep indexes smaller and vacuum work more contained.

2. Design keys and constraints for partitioned tables. In PostgreSQL, a unique constraint must include the partition key (or be enforced another way). A common pattern is (created_at, id), where id is generated and created_at is the partition key. This avoids surprises later when you discover a constraint you expected isn’t allowed.

3. Create future partitions before you need them. Don’t wait for inserts to fail because there’s no matching partition. Decide how far ahead to create them (for example, 2-3 months) and make it a routine job.

4. Keep per-partition indexes small and intentional. Partitioning doesn’t make indexes free. Most event tables need the partition key plus one or two indexes that match real admin filters, such as actor_id, entity_id, or event_type. Skip “just in case” indexes. You can add them later to new partitions and backfill older ones if needed.

5. Plan retention around dropping partitions, not deleting rows. If you keep 180 days of logs, dropping an old partition is fast and avoids long-running deletes and bloat. Write down the retention rule, who runs it, and how you verify it worked.

Small example

If your audit table gets 5 million rows per week, weekly partitions on created_at are a reasonable start. Create partitions 8 weeks ahead and keep two indexes per partition: one for common searches by actor_id and one for entity_id. When the retention window ends, drop the oldest weekly partition instead of deleting millions of rows.

If you’re building internal tools in AppMaster, it helps to decide the partition key and constraints early so the data model and generated code follow the same assumptions from day one.

What partitioning changes for admin panel filters

Once you partition a log table, admin panel filters stop being “just UI.” They become the main factor that decides whether a query touches a few partitions or scans months of data.

The biggest practical shift: time can’t be optional anymore. If users can run an unbounded search (no date range, just “show me everything for user X”), PostgreSQL may need to check every partition. Even if each check is fast, opening many partitions adds overhead and the page feels slow.

A rule that holds up well: require a time range for log and audit searches and default it to something sensible (like last 24 hours). If someone truly needs “all time,” make it a deliberate choice and warn that results may be slower.

Make filters match partition pruning

Partition pruning only helps when the WHERE clause includes the partition key in a form PostgreSQL can use. Filters like created_at BETWEEN X AND Y prune cleanly. Patterns that often break pruning include casting timestamps to dates, wrapping the column in functions, or filtering primarily on a different time field than the partition key.

Inside each partition, indexes should match how people actually filter. In practice, the combinations that often matter are time plus one other condition: tenant/workspace, user, action/type, entity ID, or status.

Sorting and pagination: keep it shallow

Partitioning won’t fix slow pagination by itself. If the admin panel sorts by newest first and users jump to page 5000, deep OFFSET pagination still forces PostgreSQL to walk past lots of rows.

Cursor-style pagination tends to behave better for logs: “load events before this timestamp/id.” It keeps the database using indexes instead of skipping huge offsets.

Presets help here too. A few options are usually enough: last 24 hours, last 7 days, today, yesterday, custom range. Presets reduce accidental “scan everything” searches and make the admin experience more predictable.

Common mistakes and traps

Most partitioning projects fail for simple reasons: the partitioning works, but the queries and the admin UI don’t match it. If you want partitioning to pay off, design it around real filters and real retention.

1) Partitioning on the wrong time column

Partition pruning only happens when the WHERE clause matches the partition key. A common mistake is partitioning by created_at while the admin panel filters by event_time (or the other way around). If your support team always asks “what happened between 10:00 and 10:15,” but the table is partitioned by ingestion time, you can still touch more data than expected.

2) Creating too many tiny partitions

Hourly (or smaller) partitions look tidy, but they add overhead: more objects to manage, more planning work for the query planner, and more chances for missing indexes or mismatched permissions.

Unless you have extremely high write volume and strict retention, daily or monthly partitions are usually easier to operate.

3) Assuming “global uniqueness” still works

Partitioned tables have constraints: some unique indexes must include the partition key, otherwise PostgreSQL can’t enforce them across all partitions.

This often surprises teams that expect event_id to be unique forever. If you need a unique identifier, use a UUID and make it unique together with the time key, or enforce uniqueness in the application layer.

4) Letting the admin UI run wide-open searches

Admin panels often ship with a friendly search box that runs without filters. On a partitioned log table, that can mean scanning every partition.

Free-text search across message payloads is especially risky. Add guardrails: require a time range, cap the default range, and make “all time” a deliberate choice.

5) No retention plan (and no plan for partitions)

Partitioning doesn’t automatically solve retention. Without a policy, you end up with a pile of old partitions, messy storage, and slower maintenance.

A simple operating rule set usually prevents this: define how long raw events stay, automate creating future partitions and dropping old ones, apply indexes consistently, monitor partition count and boundary dates, and test the slowest admin filters against realistic data volumes.

Quick checklist before you commit

Partitioning can be a big win for audit logs, but it adds routine work. Before you change the schema, sanity-check how people actually use the table.

If your main pain is that admin pages time out when someone opens “Last 24 hours” or “This week,” you’re close to a good fit. If most queries are “user ID across all history,” partitioning may help less unless you also change how the UI guides searches.

A short checklist that keeps teams honest:

• Time range is the default filter. Most admin queries include a clear window (from/to). If open-ended searches are common, partition pruning helps less.
• Retention is enforced by dropping partitions, not deleting rows. You’re comfortable dropping old partitions and have a clear rule for how long data must be kept.
• Partition count stays reasonable. Estimate partitions per year (daily, weekly, monthly). Too many tiny partitions increase overhead. Too few large partitions reduce the benefit.
• Indexes match the filters people actually use. Besides the partition key, you still need the right per-partition indexes for common filters and sort order.
• Partitions are created automatically and monitored. A routine job creates future partitions, and you know when it fails.

A practical test: look at the three filters your support or ops team uses most. If two of them are usually satisfied by “time range + one more condition,” PostgreSQL partitioning for event tables is often worth serious consideration.

A realistic example and practical next steps

A support team keeps two screens open all day: “Login events” (successful and failed sign-ins) and “Security audits” (password resets, role changes, API key updates). When a customer reports suspicious activity, the team filters by user, checks the last few hours, and exports a short report.

Before partitioning, everything sits in one large events table. It grows quickly, and even simple searches start to drag because the database works through a lot of old rows. Retention is painful too: a nightly job deletes old rows, but large deletes take a long time, create bloat, and compete with normal traffic.

After partitioning by month (using the event timestamp), the workflow improves. The admin panel requires a time filter, so most queries touch only one or two partitions. Pages load faster because PostgreSQL can ignore partitions outside the selected range. Retention becomes routine: instead of deleting millions of rows, you drop old partitions.

One thing still stays hard: free-text search across “all time.” If someone searches an IP address or a vague phrase with no date limit, partitioning can’t make that cheap. The fix is usually in product behavior: default searches to a time window and make “last 24 hours / 7 days / 30 days” the obvious path.

Practical next steps that tend to work well:

• Map your admin panel filters first. Write down which fields people use and which ones must be required.
• Pick partitions that match how you browse. Monthly partitions are often a good start; move to weekly only when volume forces it.
• Make time range a first-class filter. If the UI allows “no date,” expect slow pages.
• Align indexes with the real filters. When time is always present, a time-first index strategy is often the right baseline.
• Set retention rules that match partition boundaries (for example, keep 13 months and drop anything older).

If you’re building an internal admin panel with AppMaster (appmaster.io), it’s worth modeling these assumptions early: treat time-bounded filters as part of the data model, not just a UI choice. That small decision protects query performance as the log volume grows.