pg.ddx.io  pgaudit/pgaudit GitHub issues and pull requests  
help / color / mirror / Atom feed
From: dwsteele (@dwsteele) <noreply+dwsteele@github.com>
To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>
Subject: Re: [pgaudit/pgaudit] issue #300: Severe performance degradation when enabling pgaudit (ddl,role only) under high insert load
Date: Fri, 26 Jun 2026 10:11:11 +0000
Message-ID: <gh-pgaudit-pgaudit-300-c4808585503@github.com> (raw)
In-Reply-To: <gh-pgaudit-pgaudit-300@github.com>
References: <gh-pgaudit-pgaudit-300@github.com>

Thanks for the detailed report and the throughput graph — this is a useful case. There are really two separate things going on here, so let me take your questions in turn.

> Is this level of performance degradation expected when enabling pgaudit, even when only ddl,role logging is configured?

This is expected. The class filter (`ddl,role`) is applied *last*, inside `log_audit_event()`. It does **not** prevent pgaudit from doing per-statement work on your INSERT/COPY traffic.

In general auditing is not intended to be run on high ingest (likely automated) roles.

> Are there any pgaudit configuration parameters or PostgreSQL settings that can mitigate this issue?

Two practical options:

- **Scope auditing off the ingest role.** Keep `ddl,role` globally, but disable it for the role doing the COPYs:
```sql
ALTER ROLE ingest_user SET pgaudit.log = 'none';
```
With the bitmap at zero and no `pgaudit.role`, `ExecutorCheckPerms` skips `log_select_dml()` entirely for that role, removing the dominant cost while you keep full DDL/role auditing for human and admin sessions.

- **If you only need DDL/role visibility, consider core `log_statement = 'ddl'` instead.** It's evaluated in the utility path and installs no executor hooks, so it has effectively no cost on the INSERT/COPY path. The trade-off is cruder output and no object-level granularity, but for a pure `ddl,role` requirement at this volume it sidesteps the problem cleanly.

> Does the pgaudit stack is empty error indicate a known bug or unsupported interaction (e.g., with COPY, TimescaleDB, or high concurrency workloads)?

This has been a bug in certain code paths, but in your case I suspect TimescaleDB may be misusing the hook chain and causing the error.  Something in TimescaleDB's COPY/chunk-routing path is likely calling permission checks outside the expected ordering — most likely a per-chunk/per-batch memory context being reset mid-COPY, which fires pgaudit's reset callback and pops the stack item before the check runs.

Because it's an `ERROR`, it aborts the COPY/transaction, which probably triggers client-side retries and compounds the throughput loss you're seeing.

To help us pin it down, could you provide:
- the TimescaleDB and pgaudit versions and the exact PostgreSQL version,
- a minimal reproduction (table/hypertable definition + a COPY that triggers it), and
- a backtrace from a debug build (`gdb` on the backend when the error fires), if at all possible.

We'd also suggest opening a parallel issue on the TimescaleDB side, since reproducing requires their COPY path. An audit extension should never be able to abort a user transaction this way, so we'll look at making that path non-fatal regardless.

> Are there recommended best practices for using pgaudit in very high-ingest-rate environments?

- Audit by role, not globally: enable classes only on the roles/sessions that need them and turn auditing off for high-volume ingest roles (option 1 above).
- Validate any audit configuration against representative load before rolling it out, since the cost scales with statement/chunk rate rather than with how much is actually logged.

view thread (12+ messages)  latest in thread

Message-ID: <gh-pgaudit-pgaudit-300-c4808585503@github.com>
Permalink:  ../gh-pgaudit-pgaudit-300-c4808585503@github.com/

 · 


This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox