Thread: psql memory usage when creating delimited files

I had in interesting issue the other day while trying to generate
delimited files from a query in psql, using:

\f'|'
\t
\a
\o out.file
select * from really_big_table sort by createddate;

This quantity of data involved here is fairly large (maybe 2-4GB).
Watching the memory usage, the postmaster consumed a fair chunk of RAM
(obviously) while running the query, but I was surprised to see psql
start taking increasingly large quantities or RAM, to the point that in
the end the machines memory was exhausted, postmaster died (and
restarted OK) causing psql to quit.

I was surprised in that I assumed that psql would just be taking rows
from postmaster and writing them to disk, hence requiring very little
RAM, but it appeared that it tried to load the data into memory.

Is there some option I'm missing in my export script that would prevent
this happening?  I managed to work around the issue by issuing a number
of smaller queries, but that's not something I want to do on a regular
basis.

Thanks,

David.

On Sep 18 01:52, David Brain wrote:
> I had in interesting issue the other day while trying to generate
> delimited files from a query in psql, using:
>
> \f'|'
> \t
> \a
> \o out.file
> select * from really_big_table sort by createddate;
>
> This quantity of data involved here is fairly large (maybe 2-4GB).
> Watching the memory usage, the postmaster consumed a fair chunk of RAM
> (obviously) while running the query, but I was surprised to see psql
> start taking increasingly large quantities or RAM, to the point that in
> the end the machines memory was exhausted, postmaster died (and
> restarted OK) causing psql to quit.
>
> I was surprised in that I assumed that psql would just be taking rows
> from postmaster and writing them to disk, hence requiring very little
> RAM, but it appeared that it tried to load the data into memory.

See "BUG #1756: PQexec eats huge amounts of memory"[1] and "SELECT very
slow"[2] threads in the ml archives.

[1] http://archives.postgresql.org/pgsql-bugs/2005-07/msg00074.php
[2] http://archives.postgresql.org/pgsql-sql/2005-06/msg00118.php

> Is there some option I'm missing in my export script that would prevent
> this happening?  I managed to work around the issue by issuing a number
> of smaller queries, but that's not something I want to do on a regular
> basis.

Use COPY instead. (You can also prefer CURSORs in your client
applications. See related discussions.)


Regards.

On Sep 18 02:57, David Brain wrote:
> >Use COPY instead. (You can also prefer CURSORs in your client
> >applications. See related discussions.)
>
> Yes, but COPY isn't an option if I need to do:
>
>     SELECT * from really_big_table where foo='zebra' order by createddate
>
> Which is what I really need to do (forgot the where clause in the example).

For an alternative to COPY, IMHO you can create and use a CURSOR with
some help from shell (e.g. a while loop) to form a "FETCH FORWARD ..."
query family.

> Any other workarounds?

AFAIK, you'll be able to use subselects and views in COPY with 8.2
release.


Regards.