Memory usage on select for large number of rows
By default, the results are entirely buffered in memory for two reasons:

1) Unless using the -A option, output rows are aligned so the output cannot start until psql knows the maximum length of each column, which implies visiting every row (which also takes a significant time in addition to lots of memory).

2) Unless specifying a FETCH_COUNT, psql uses the synchronous PQexec function directly on the query, which buffers the entire resultset. But when setting a FETCH_COUNT, it will use a cursor-based method with successive fetch calls and freeing or reusing the client-side buffer every FETCH_COUNT rows.

So a big resultset should be fetched by a command like:

psql -A -t --variable="FETCH_COUNT=10000" \
     -c "select columns from bigtable" \
     &gt; output-file


With FETCH_COUNT reduced if the rows are very large and it still eats too much memory.

The -t stands for --tuples-only, which suppresses the output of headers and footers.