Skip site navigation (1) Skip section navigation (2)

Large selects

From: Bendik Rognlien Johansen <bensmailinglists(at)gmail(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Large selects
Date: 2006-08-24 08:26:45
Message-ID: 1DE86526-D22F-483E-BA4A-DAF1B85EDFCC@gmail.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Hello!

We are having a lot of trouble trying to use JDBC with PostgreSQL.

This is what we want to achieve:

- Select all rows in a table (people)
- Select addresses belonging to each person. By subselect or join  
(addresses)
- Select phone numbers belonging to each person. By subselect or join  
(contacts)
- Loop through the results, building a Lucene index.


Our tables:
- people: ~ 8 million records (could be larger in the future, maybe  
50 million)
- addresses ~8 million records
- contacts: ~10 million records


Hardware:
2x dual core 3GHz intel Xeon
1-2 Gb RAM



Problem:

It seems that the PostgreSQL driver fetches the whole result, storing  
it in memory, causing Java to run out of memory.  
java.lang.OutOfMemoryError
We have tried increasing memory for the program (-Xmn100M -Xms512M - 
Xmx512M), but we only have one gigabyte of memory to work with.

Does anyone have any suggestions on how to deal with this, other that  
throwing more hardware at the problem? Cursors?


Any help is much appreciated!
Thanks




Responses

pgsql-jdbc by date

Next:From: Roland WalterDate: 2006-08-24 08:30:38
Subject: Re: Large selects
Previous:From: surabhi.ahujaDate: 2006-08-24 05:57:30
Subject: Re: [JDBC] org.postgresql.util.PSQLException: An I/O error occured while sending to the backend

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group