Re: cursor interface to libpq

From: "Kirby Bohling (TRSi)" <kbohling(at)oasis(dot)novia(dot)net>
To: Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>
Cc: pgsql-interfaces(at)postgresql(dot)org
Subject: Re: cursor interface to libpq
Date: 2000-09-20 16:00:47
Message-ID: Pine.GSO.4.21.0009201026100.10741-100000@oasis.novia.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-interfaces


Thomas,

Right now I have a database that has right around 4 Million
rows in the primary table. I am porting it away from MySQL basically
because I want transactions. When the table is written as insert
statements it is around 3.2Gb, when that is gzipped it comes out to 1.3Gb.

That table size will probably continue to grow at a rate of 200,000
rows a week. I don't know the exact size of each row, but it is under the
8K limit. Right now I am working on a FreeBSD box with 1.5Gb of swap and
256MB of Ram. I believe that I could get that upgraded to 1Gb of ram, and
add as much swap space as I wanted. But to be honest I really don't want
to store every row of every select statement in memory. I believe that
the database is around 1.5Gb on disk almost all of it in the one table.

The whole thought of having to keep the entire table in memory at once to
do a backup is scary. I don't expect anyone at work to appreciate
having to match the disk space of the database in order to do backups. I
will either have to switch RDBM's, or unwrap the pg_dump to use the
lower level primatives of parseInput, getAnotherTuple, or better yet
implement it with the cursors at the query level and then I could get by
until a better solution came along. I can deal with most everything with
the exception of the backups.

Any advice, or expectations of memory requirements would be
appreciated. Sorry, if this is the wrong forum for this dicussion, but my
original question started here.

Thanks,
Kirby

On Wed, 20 Sep 2000, Thomas Lockhart wrote:

> > I am trying to run a select statement, and I keep running out of
> > memory. I have noticed that libpq has the nice feature that I don't have
> > to request each row one at a time. After a little investigate, I found
> > that libpq appears to get the entire result set at once. Well, at least
> > PQgetvalue() doesn't appear to do anything besides return a pointer to a
> > string. There is no lazy evaluation. It doesn't just fetch the row I
> > need, and flush old ones as memory permits.
>
> Use a cursor at the query level to keep the result set in the backend.
> But you will still (possibly) run out of memory, since the *backend*
> must keep the result set in memory and/or on disk. Make sure you have
> enough swap space, disk space, and main memory to handle the size of
> database you have.
>
> > Is there any interface that I can link to a C/C++ program that
> > will allow me row at a time access to the data? Not being able to run
> > select statements is going to be a fairly sizeable problem for me.
>
> Look at cursors, but that doesn't eliminate the need for enough memory.
>
> Expect to buy more memory, and make sure you have enough swap space.
> Send more details on what you have currently and what your database
> looks like and we can give you better feedback on your resource
> requirements.
>
> - Thomas
>

In response to

Responses

Browse pgsql-interfaces by date

  From Date Subject
Next Message Thomas Lockhart 2000-09-20 16:22:52 Re: cursor interface to libpq
Previous Message Herbert Ambos 2000-09-20 15:58:11 Re: pgadmin