CODEWITHSUNDEEP
postgresqlgoout-of-memorypgx
Nemo
Nemo

Reputation: 2572

Do prepared statements in PostgreSQL respect shared_buffers memory limits?

Recently I observed OOMs in a PostgreSQL 16 server used by an application which made heavy use of prepared statements. Memory would gradually grow until hitting an OOM.

How to limit the usage of memory? I expected shared_buffers and similar limits to avoid such an occurrence.

We're using the pgx driver (v5.7.2) for Go.

Upvotes: 1

Views: 38

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247605

Shared buffers is a shared memory cache for persistent database data: tables, indexes, sequences etc., which is shared by all database processes that access these data.

In contrast, prepared statements are stored in the private memory of the backend (server) process of the database session; neither prepared statements nor query plans are shared between database sessions in PostgreSQL.

There is no limit for the memory that can be occupied by prepared statements. If you don't want prepared statements to occupy too much memory, don't create too many of them and DEALLOCATE them once you don't need them any more. The solution from your answer works because prepared statements make use of the extended query protocol.

There is a PostgreSQL parameter that limits the use of backend private memory: work_mem. But that parameter only limits the memory used during query execution (sorts, hash tables, bitmaps, ...) and has no influence on other memory allocations. For example, if you read a large text string, that will be stored in memory in its entirety, no matter if it exceeds work_mem or not. Prepared statements are a similar case.

Upvotes: 0

Nemo
Nemo

Reputation: 2572

According to BUG #14726: Memory consumption of PreparedStatement, filed against PostgreSQL 9.5.7, there is no way to limit the memory usage of PreparedStatement. A sufficiently large query, multiplied by a few times, may exhaust all your memory.

According to the lone answer to that report, applications have to consider memory limits in their usage of prepared statements, but it's not possible to determine what the memory footprint of prepared statements is.

I'd be against that, as it pretty much would destroy the point of having prepared statements at all --- if the server forgets them at random, or even just has to re-prepare them unexpectedly, it's not holding up its end of the contract. It's the application's job to use that resource in a prudent manner, just like any other SQL resource.

With pgx, it was enough to use QueryExecModeSimpleProtocol to avoid the problem:


config, cfgErr := pgx.ParseConfig(cfg.ConnString)
config.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol
sqldb := stdlib.OpenDB(*config)

Upvotes: 1

Related Questions