Re: Postgres problems with large databases.

| > I'm encounting some strange problems trying to use postgres on a table
| > with a fairly substantial amount of records (~1 million).
| >
| > The problem comes when you try and select a record from the database.
| > Postmaster in debug mode complains whole heartedly about resource errors,
| > and after that, everything goes downhill (everything from postmaster crashes
| > to index corruption).  This occurs both with 6.5.3 and 7.0.2.
| >
| > Any pointers/ideas?
|
| First off I'm sure the developers would like to know more than:
|   "complains whole heartedly about resource errors"
| can you name the errors specifically?

Sorry, I'm reporting this second hand.

The resource errors are massive, and usually "Out of Swap" messages from
the query executed, which is just a simple..

SELECT url from table where com='n' limit 1;

| Second, when was the last time to vacuumed the table?  what about
| vacuum analyze?

The database is vacuumed every six hours.

| Third, do you have nice indexes on the tables to help the search?

Of course.

Below illustrates the issue of corruption..

BT=# select * from murl where com='y' limit 1;
<<THIS WOULD LOCATE THE RECORD>>
            url            | com | ban | qcon | words |          time
---------------------------+-----+-----+------+-------+------------------------
 http://www.altavista.com/ | y   |     |      |       | 2000-08-10 21:19:05-04
(1 row)

BT=# update murl set com='n' where url='http://www.altavista.com/';
<<THIS WOULD UPDATE 0>>

BT=# update murl set com='n' where url like 'http://www.altavista.com/';
<<THIS WOULD ALSO UPDATE 0>>

BT=# select * from murl where url='http://www.altavista.com/';
<<THIS WOULD RETURN>>
 url | com | ban | qcon | words | time
-----+-----+-----+------+-------+------
(0 rows)


Cheers,
Dan
--
Man is a rational animal who always loses his temper when he is called
upon to act in accordance with the dictates of reason.
                -- Oscar Wilde