PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to calculate query result memory size in Postgres?

I’m creating a statistic reporting tool and facing an issue related to memory due to a high query result (application server not responding due to RAM is full).
I can’t restrict the query result by limit or where clause due to the dynamic scenario.
So I am planning to calculate query result memory before the query execution; if memory exceeds the RAM size, then I can inform the user, "Query result is very huge, Can’t process."

Is there any option to get the memory size of query result in Postgres?

example:
sql query : select date,ledger,credit,debit,company,.... from blah;

expecting query like

select sum(pg_size(result)) from (select date,ledger,credit,debit,company,.... from blah)result;
Tags:

2

Answers


  1. 0

    No, there is not possibility to get size of result. It is stored on client side, and from server perspective it is not interesting. If you have problems with size of result, you can

    • allow to use more memory on client side
    • use cursors – then the communication is separated to smaller batches controlled by commands OPEN and FETCH. Some clients (environments) supports cursors transparently.

    https://www.cybertec-postgresql.com/en/declare-cursor-in-postgresql-or-how-to-reduce-memory-consumption/

    Login or Signup to reply.
  2. 0

    There is no way to determine the result set size ahead of time, because the database server doesn’t know the size before it has finished processing the query, and then it is already too late, because result rows are sent to the client right when they have been calculated.

    The best estimate for the result set size can be had with EXPLAIN:

    EXPLAIN /* your query */

 Top node of query plan  (cost=123.45..94012.34 rows=185082 width=26)
   [other query plan nodes]

    By multiplying the estimated result rows and the estimated average result row width, you can get an estimate for the result set size.

    However, the better approach is to receive the result set in manageable chunks. This can be done with a cursor, like Pavel’s answer suggests, or you can use the chunk mode new in PostgreSQL v17.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search