CODEWITHSUNDEEP
postgresqlpsql
ericj
ericj

Reputation: 2291

How to handle large result sets with psql?

I have a query which gives about 14M rows (I was not aware of this). When I use psql to run the query, my Fedora machine froze. Also after the query was done, I could not use Fedora anymore and had to restart my machine. When I redirected standard output to a file, Fedora also froze.

So how should I handle large resultsets with psql?

Upvotes: 7

Views: 4532

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45770

psql accumulates complete results in client memory by default. This behavior is usual for all libpq based Postgres applications or drivers. The solutions are cursors - then you are fetching only N rows from server. Cursors can be used by psql too. You can change it by setting FETCH_COUNT variable, then it will use cursors with batch retrieval size FETCH_COUNT.

postgres=# \set FETCH_COUNT 1000
postgres=# select * from generate_series(1,100000); -- big query

Upvotes: 7

Related Questions