0

Via pg_proctab(), I can see that the connections from our application can use up to 800MB of RAM per connection in postgres. Usually up to 400MB. (Update to clarify after a comment: These connections are from connections pools, most of the time they sit IDLE, with queries in between. The 400MB are measured when the connection is IDLE.)

I would like to investigate what these 400MB consist of. Why 400MB, and not just 40MB for example? I already looked at the jdbc connection objects on the application side and found nothing out of the ordinary.

My (wild) guess would be that the high RAM usage comes from the fact that we have many partitions on our tables.

Does postgres provide a way to look into the RAM of the processes of connections? We are on AWS RDS, so best case would be a way where I do not need server access.

Improve this question
8
  • pg_proctab returns OS, not database statistics. A better question would be to check what do your queries do and how long do connections remain active? Are you sure that 400MB is per connection instead of eg buffered data? If the queries don't take advantage of indexes the database will have to load entire tables in memory and scan them for matches. Commented Mar 11, 2024 at 9:56
  • Databases always cache data anyway, so they don't have to reload from disk the next time the data is needed. They're meant to use all available RAM. Commented Mar 11, 2024 at 9:58
  • The 400MB is column rss of 'pg_proctab()' which to me seems to be a correct representation of the RAM the connection needs, as the process-view that AWS offers shows the same number of RAM usage for the process. The connections are from a connection pool and are IDLE most of the time - so it's not data of running queries. I also don't believe this is 'buffered data' as the buffer cache is a different set of memory as far as I understand the matter. Commented Mar 11, 2024 at 10:00
  • 1
    Have you checked Resources consumed by idle PostgreSQL connections in the AWS Database blog ? Commented Mar 11, 2024 at 10:03
  • 2
    My point is the question is wrong. pg_proctab doesn't show connection statistics. It shows coarse-grained OS-level statistics. The article Analyzing the Limits of Connection Scalability in Postgres describes various techniques and extensions you can use to see what's actually going on and links to Measuring the Memory Overhead of a Postgres Connection Commented Mar 11, 2024 at 10:31

1 Answer 1

Reset to default
2

First, you have to keep in mind that part of the memory that is reported as being used might be shared memory from shared buffers, which is shared by all database processes.

Other than that, an idle connection will use little memory, with the possible exceptions of

  • memory used by cached execution plan from prepared statements and statements in PL/pgSQL functions

  • memory used for temporary tables (temp_buffers)

  • memory used by WITH HOLD cursors

  • memory used by the metadata cache (interesting if you have lots of tables)

Improve this answer
2
  • Thank you. I am pretty sure that metadata cache is the issue in my scenario (0.5m partitions with 1.4m indexes and toasts and toast indexes on less than 100 tables, of which many are accessed in the pooled sessions that live for 30 minutes). Commented Mar 12, 2024 at 10:45
  • Regarding 'part of the memory that is reported as being used might be shared memory', I find column rss of pg_proctab to not behave this way. For example: DB has 128GB of RAM, which I can explain for example with: 53GB are free (file system cache) + 32GB is shared_buffers + 6GB for autovacuum (autovacuum_max_workers is 3 and autovacuum_work_mem is 2GB) + 38GB from pg_proctab (select pg_size_pretty(sum(rss)*1000) from pg_proctab()). So it all adds up. Commented Mar 12, 2024 at 10:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.