How large can you set track_activity_query_size without incurring performance issues?

Question

We have some pretty large queries in our production postgresql (13.8) database that exceed the default 1024 for track_activity_query_size (some queries are over 100k). I've bumped this value up to 8192, but it still does not capture quite a few of our queries.

I'm wondering:

1. How high can I reasonably set this (I've read 1MB is the max) without incurring performance issues?
2. What metrics can I monitor to track if I've set it too high?

I'd like to set it to 256k, or possibly higher, but that seems like it could be excessive.

Answer 1

It is difficult to give an authoritative answer to performance question; only a benchmark can do that. My answer is based on my understanding of the code.

The only impact that increasing track_activity_query_size has is that more memory is allocated in the process array at server start. The size of the process array is max_connections + autovacuum_max_workers + 1 + max_worker_processes + max_wal_senders + max_prepared_xacts, so you have to multiply track_activity_query_size by that number to see how much memory will be allocated.

The only performance impact is that you have less memory available for caching data and processing queries.