pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Steve Crawford <scrawford@pinpointresearch.com>
To: Lee Wu <Lwu@mxlogic.com>
To: pgsql-admin@postgresql.org
Subject: Re: select * and save into a text file failed
Date: Fri, 10 Jun 2005 10:36:09 -0700
Message-ID: <200506101036.09527.scrawford@pinpointresearch.com> (raw)
In-Reply-To: <E1F0E4D89D8FA44789DFC065C8E755281A360E@mxlhq-exch01.corp.mxlogic.com>
References: <E1F0E4D89D8FA44789DFC065C8E755281A360E@mxlhq-exch01.corp.mxlogic.com>

On Friday 10 June 2005 10:03 am, Lee Wu wrote:
> That is exactly what I did:
>
> \o a_lot_room_to_hold_my_result
> select * from a_table
>
> either
> 1. out of memory for query result
> 2. killed
> 3. crash PG
>
> "If you have a very large table you can exhaust memory on the
> client side unless you are writing the data directly to a file."
> How besides "\o" and pg_dump?
>
> We have 4G RAM, and shared_buffers= 32768, it is a dedicate test
> box, while the table is about 2G.

Something to try (I don't know if it will work because I don't know 
the exact internals of the PG libraries but it's something I've 
observed): set the output formatting to unaligned (\pset format 
unaligned).

I had a situation once where selecting a few thousand rows exhausted 
my RAM. The problem was that one item in a text column was ~8k long 
which meant that every other row had 8k of padding. And this was the 
case for more than one column so the pager was having to swallow gobs 
of data most of which was blank space. I ran the output to a file and 
ran some tests. Switching to unaligned output dropped the size by 
orders of magnitude. Depending on the nature of your data this may 
help or do nothing at all.

Of course you are going to hit RAM or disk limitations on any given 
machine. Cursors are there for your use and your project may require 
them.

Cheers,
Steve



view thread (11+ messages)

Message-ID: <200506101036.09527.scrawford@pinpointresearch.com>
Permalink:  ../200506101036.09527.scrawford@pinpointresearch.com/
Also on:    postgresql.org/message-id/200506101036.09527.scrawford@pinpointresearch.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: scrawford@pinpointresearch.com, Lwu@mxlogic.com
  Subject: Re: select * and save into a text file failed
  In-Reply-To: <200506101036.09527.scrawford@pinpointresearch.com>
  References: <E1F0E4D89D8FA44789DFC065C8E755281A360E@mxlhq-exch01.corp.mxlogic.com> <200506101036.09527.scrawford@pinpointresearch.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox