pg.ddx.io  pgaudit/pgaudit GitHub issues and pull requests  
help / color / mirror / Atom feed
[pgaudit/pgaudit] issue #300: Severe performance degradation when enabling pgaudit (ddl,role only) under high insert load
12+ messages / 4 participants
[nested] [flat]

* [pgaudit/pgaudit] issue #300: Severe performance degradation when enabling pgaudit (ddl,role only) under high insert load
@ 2026-01-14 09:32 "harunkucuk5 (@harunkucuk5)" <noreply+harunkucuk5@github.com>
  0 siblings, 0 replies; 12+ messages in thread

From: harunkucuk5 (@harunkucuk5) @ 2026-01-14 09:32 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>

We are running a very high–throughput PostgreSQL workload (≈ 1M inserts/sec) using TimescaleDB.
Under normal conditions (pgaudit disabled), the system sustains this throughput without issues.

However, when pgaudit is enabled—even with logging limited strictly to ddl,role (no read/write auditing)—we observe a severe performance degradation, with throughput dropping to around 250K inserts/sec.

In addition to the performance drop, the following errors repeatedly appear in the logs:

```bash
Jan 12 13:44:31 DB-SQL2 patroni[1837773]: 2026-01-12 12:44:31.084 GMT [1837773] postgres@mydb ERROR:  pgaudit stack is empty
Jan 12 13:44:31 DB-SQL2 patroni[1837777]: 2026-01-12 12:44:31.084 GMT [1837777] postgres@mydb ERROR:  pgaudit stack is empty
Jan 12 13:44:31 DB-SQL2 patroni[1837769]: 2026-01-12 12:44:31.098 GMT [1837769] postgres@mydb ERROR:  pgaudit stack is empty
Jan 12 13:44:31 DB-SQL2 patroni[1837771]: 2026-01-12 12:44:31.111 GMT [1837771] postgres@mydb ERROR:  pgaudit stack is empty
Jan 12 13:44:31 DB-SQL2 patroni[1834099]: 2026-01-12 12:44:31.112 GMT [1834099] LOG:  parameter "pgaudit.log" changed to "none"
```

### Workload details 

Inserts are performed using the COPY command.

Data is ingested batch by batch (no single-row inserts).

No application-level DDL changes are happening during ingestion.

The following graph shows database throughput when auditing is enabled and then disabled again:

<img width="1838" height="795" alt="Image" src="https://github.com/user-attachments/assets/dcb2ce57-e119-482b-b8d9-8afb3b8f404a"; />

### Questions

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

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

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

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

Any guidance or insight would be greatly appreciated.

^ permalink  raw  [nested|flat] 12+ messages in thread

* Re: [pgaudit/pgaudit] issue #300: Severe performance degradation when enabling pgaudit (ddl,role only) under high insert load
@ 2026-06-26 10:11 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  10 siblings, 0 replies; 12+ messages in thread

From: dwsteele (@dwsteele) @ 2026-06-26 10:11 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@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.

^ permalink  raw  [nested|flat] 12+ messages in thread

* Re: [pgaudit/pgaudit] issue #300: Severe performance degradation when enabling pgaudit (ddl,role only) under high insert load
@ 2026-06-26 10:11 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  10 siblings, 0 replies; 12+ messages in thread

From: dwsteele (@dwsteele) @ 2026-06-26 10:11 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>

@dwsteele assigned @dwsteele.

^ permalink  raw  [nested|flat] 12+ messages in thread

* Re: [pgaudit/pgaudit] issue #300: Severe performance degradation when enabling pgaudit (ddl,role only) under high insert load
@ 2026-06-26 10:11 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  10 siblings, 0 replies; 12+ messages in thread

From: dwsteele (@dwsteele) @ 2026-06-26 10:11 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>

@dwsteele applied label "question".

^ permalink  raw  [nested|flat] 12+ messages in thread

* Re: [pgaudit/pgaudit] issue #300: Severe performance degradation when enabling pgaudit (ddl,role only) under high insert load
@ 2026-06-26 10:39 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  10 siblings, 0 replies; 12+ messages in thread

From: dwsteele (@dwsteele) @ 2026-06-26 10:39 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>

@harunkucuk5 PR #325 should address the performance issues you are seeing. Please give it a try and see how it works.

It's not likely to be back patched but there is still time to get it into PG19.

^ permalink  raw  [nested|flat] 12+ messages in thread

* Re: [pgaudit/pgaudit] issue #300: Severe performance degradation when enabling pgaudit (ddl,role only) under high insert load
@ 2026-07-21 03:07 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  10 siblings, 0 replies; 12+ messages in thread

From: dwsteele (@dwsteele) @ 2026-07-21 03:07 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>

@dwsteele closed this.

^ permalink  raw  [nested|flat] 12+ messages in thread

* Re: [pgaudit/pgaudit] issue #300: Severe performance degradation when enabling pgaudit (ddl,role only) under high insert load
@ 2026-08-06 13:20 ` "akuzm (@akuzm)" <noreply+akuzm@github.com>
  10 siblings, 0 replies; 12+ messages in thread

From: akuzm (@akuzm) @ 2026-08-06 13:20 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>

> 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.

At the moment it doesn't seem possible to do anything that requires `ExecCheckPermissions` from ProcessUtility hook, unless pgaudit goes first in the hook call sequence. Is this the misuse that you are referring to? Having pgaudit always go first sounds more like a pgaudit-specific requirement to me, it might make sense to document it. I'm not sure if Postgres has an explicit contract on the hook order there, or the extension loading order.  In TimescaleDB, we're running our own implementation of COPY from that hook. It seems that the single ProcessUtility hook w/o explicit ordering guarantees, as provided by Postgres at the moment, is not adequate for making this work reliably.

^ permalink  raw  [nested|flat] 12+ messages in thread

* Re: [pgaudit/pgaudit] issue #300: Severe performance degradation when enabling pgaudit (ddl,role only) under high insert load
@ 2026-08-07 02:24 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  10 siblings, 0 replies; 12+ messages in thread

From: dwsteele (@dwsteele) @ 2026-08-07 02:24 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>

> unless pgaudit goes first in the hook call sequence

It doesn't expect to run first, it just expects the hook chain to be honored. If another extension can't do that it should disable pgAudit or pgAudit should be loaded first.

That said, the improvements in the linked PR may help as long as pgAudit is not configured to audit anything that requires the ExecCheckPermissions hook. We were not planning to back patch this but if you can demonstrate that it prevents conflicts with other extensions (e.g. TimescaleDB) then we'll likely reconsider.

^ permalink  raw  [nested|flat] 12+ messages in thread

* Re: [pgaudit/pgaudit] issue #300: Severe performance degradation when enabling pgaudit (ddl,role only) under high insert load
@ 2026-08-10 14:56 ` "akuzm (@akuzm)" <noreply+akuzm@github.com>
  10 siblings, 0 replies; 12+ messages in thread

From: akuzm (@akuzm) @ 2026-08-10 14:56 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>

> > unless pgaudit goes first in the hook call sequence
> 
> It doesn't expect to run first, it just expects the hook chain to be honored.

But what does this mean exactly? The errors in question happen when another extension calls ExecCheckPermission from its ProcessUtility hook, without calling the pgaudit ProcessUtility hook first. I don't understand what protocol could avoid this, save for calling pgaudit first. "Honoring the call chain" sounds too generic, e.g. calling the pgaudit ProcessUtility hook last doesn't prevent the error. TimescaleDB doesn't call it at all, because it's unclear how to chain an alternative implementation of COPY to some other hooks, but that's not the immediate cause of this problem.


> That said, the improvements in the linked PR may help as long as pgAudit is not configured to audit anything that requires the ExecCheckPermissions hook. We were not planning to back patch this but if you can demonstrate that it prevents conflicts with other extensions (e.g. TimescaleDB) then we'll likely reconsider.

In my testing, the PR https://github.com/pgaudit/pgaudit/pull/325 avoids the error with `pgaudit.log = 'ddl, role'`, so I think backporting it could improve the situation. Not sure if it's worth the effort by itself, because the case with `pgaudit.log = write` would still need another workaround.

On TimescaleDB side, I'm considering to make the hook order depend on the order in `shared_preload_libraries`. At least this will give an easy workaround, because at the moment our hook always ends up first. Unfortunately this change is not very clear-cut since it's a multi-library extension with dynamic loading.

^ permalink  raw  [nested|flat] 12+ messages in thread

* Re: [pgaudit/pgaudit] issue #300: Severe performance degradation when enabling pgaudit (ddl,role only) under high insert load
@ 2026-08-13 04:19 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  10 siblings, 0 replies; 12+ messages in thread

From: dwsteele (@dwsteele) @ 2026-08-13 04:19 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>

> But what does this mean exactly? The errors in question happen when another extension calls ExecCheckPermission from its ProcessUtility hook, without calling the pgaudit ProcessUtility hook first.

What I mean is, that won't work when auditing is enabled. If pgAudit sees ExecutorCheckPerms without ExecutorStart or ProcessUtility then it has no context on what command is being run and cannot complete the audit. That's the entire job of the module so it has no choice but to error. In previous versions this was a segfault, which was definitely bad, but now it should be a hard, debug-able error.

> Not sure if it's worth the effort by itself, because the case with pgaudit.log = write would still need another workaround.

You'll need to disable pgAudit in this case. Not sure what else can be done but I'm open to suggestions. FWIW, this was the exact method used in another custom COPY implementation that I'm aware of.

> On TimescaleDB side, I'm considering to make the hook order depend on the order in shared_preload_libraries

I think this would work, if you can manage it. Ideally pgAudit should always be first, or at least it should not be after any extensions that do not propagate the hook chain, i.e. call the the next hook function when they are done.

^ permalink  raw  [nested|flat] 12+ messages in thread

* Re: [pgaudit/pgaudit] issue #300: Severe performance degradation when enabling pgaudit (ddl,role only) under high insert load
@ 2026-09-03 23:52 ` "ducquangkstn (@ducquangkstn)" <noreply+ducquangkstn@github.com>
  10 siblings, 0 replies; 12+ messages in thread

From: ducquangkstn (@ducquangkstn) @ 2026-09-03 23:52 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>

 I am syncing our Postgres from Aws to Azure by pgcopydb. Between 2 runs, the performance drops about 1.5 times, and enabling pgaudit is one of the significant changes.

FYI, I try to disable pgaudit at the source by `ALTER ROLE ingest_user SET pgaudit.log = 'none';` as suggested and disable pgaudit at target DB, but nothing changed. Disabling pgaudit completely is not a choice in my case due to compliance.

General speaking, I don't want to bash anyone, but this is a seriours performance issue (do you agree with that?), and the PR #325 does not include any performance report. So how are you sure that will fix this issue at all?




^ permalink  raw  [nested|flat] 12+ messages in thread

* Re: [pgaudit/pgaudit] issue #300: Severe performance degradation when enabling pgaudit (ddl,role only) under high insert load
@ 2026-09-04 07:07 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
  10 siblings, 0 replies; 12+ messages in thread

From: dwsteele (@dwsteele) @ 2026-09-04 07:07 UTC (permalink / raw)
  To: pgaudit/pgaudit <noreply+pgaudit-pgaudit@github.com>

> but this is a seriours performance issue (do you agree with that?)

It really depends on how you are using the tool, but yes, it does appear to be a performance issue for these bulk copy cases.

> So how are you sure that will fix this issue at all?

I'm hoping users with bulk data scenarios will test and let us know if the fix works. That is, the OP of this issue, and you.

^ permalink  raw  [nested|flat] 12+ messages in thread


end of thread, other threads:[~2026-09-04 07:07 UTC | newest]

Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-14 09:32 [pgaudit/pgaudit] issue #300: Severe performance degradation when enabling pgaudit (ddl,role only) under high insert load "harunkucuk5 (@harunkucuk5)" <noreply+harunkucuk5@github.com>
2026-06-26 10:11 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-06-26 10:11 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-06-26 10:11 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-06-26 10:39 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-07-21 03:07 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-08-06 13:20 ` "akuzm (@akuzm)" <noreply+akuzm@github.com>
2026-08-07 02:24 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-08-10 14:56 ` "akuzm (@akuzm)" <noreply+akuzm@github.com>
2026-08-13 04:19 ` "dwsteele (@dwsteele)" <noreply+dwsteele@github.com>
2026-09-03 23:52 ` "ducquangkstn (@ducquangkstn)" <noreply+ducquangkstn@github.com>
2026-09-04 07:07 ` "dwsteele (@dwsteele)" <noreply+dwsteele@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