Retrieve large result sets using psql

Problem: sometimes you need to retrieve very large result sets - for example to pipe for export to a file. As psql normally fetches the entire result set in a single operation, you may experience "out-of-memory" errors.

Solution: set psql's FETCH_COUNT parameter, which will cause psql to retrieve data in blocks of FETCH_COUNT rows and thereby limit memory usage.

The following example - with FETCH_COUNT set to an impracticably low value - demonstrates FETCH_COUNT in action - as rows are being fetched in blocks of 5, the default aligned format is being applied to the values in each block of rows, not the entire data set, resulting in misalignment of the output. (As the main purpose of FETCH_COUNT is to deal with large data sets you wouldn't want to display anyway, this is not usually an issue).

testdb=# \set FETCH_COUNT 5
testdb=# SELECT id, order_code, distributor_id FROM orders LIMIT 15;
   id    |      order_code       | distributor_id 
---------+-----------------------+----------------
 1001984 | yuti59i76             |           1052
 1002596 | 32556436              |             96
 1002071 | 49667-009-347496671_1 |            103
 1002070 | 49667-009-347496671_2 |            103
 1002068 | 49667-009-347496672_2 |            103
 1002069 | 49667-009-347496672_1       |            103
 1002344 | 1338165747                  |             67
 1002355 | 43229_X1214EE               |          15620
 1002494 | 20121016-350371870_0_3017_0 |             55
 1001979 | wrewqrwqr                   |           1052
 1001980 | 3255325    |           1052
 1001981 | 2346457658 |           1052
 1002356 | sb1-jpz    |          15620
 1001982 | wqrtewtew  |           1052
 1001985 | 47568658   |           1052
(15 rows)