Re: High RAM usage on postgres

Merlin Moncure wrote:

> problem is psql buffering whole result set in memory before outputting
> result. note this is core problem with libpq client library until
> very recently. there are several easy workarounds:
>
> *) use cursor
> *) don't select entire table, page it out using index (I can suggest
> some methods )
> *) if you are outputting to file, consider using COPY

Also there's psql's FETCH_COUNT that is specifically meant to avoid the
buffering problem.

From the 9.1 manpage:

FETCH_COUNT
If this variable is set to an integer value > 0, the results
of
SELECT queries are fetched and displayed in groups of that
many
rows, rather than the default behavior of collecting the
entire
result set before display. Therefore only a limited amount of
memory is used, regardless of the size of the result set.
Settings of 100 to 1000 are commonly used when enabling this
feature. Keep in mind that when using this feature, a query
might fail after having already displayed some rows.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org