Re: hundreds of millions row dBs

> Out of curiosity, what value of sort_mem were you using?
>
> (In PG 8.0, the sort memory setting used by CREATE INDEX will be
> maintenance_work_mem not work_mem, which should help in getting larger
> values to be used. But in existing releases you usually need to think
> about a manual tweak.)

Normally it is set to 65535. However, during the load I bump it up to
655350. The system has 2GB ECC memory.

> Here is an instance where a really big ram disk might be handy.
> You could create a database on a big ram disk and load it, then build
> the indexes.

I'm afraid we don't have quite that much RAM... With just under 400 million
rows right now, it is 74 GB. That will probably grow to around 300 GB or so
before it stabilizes.

> Actually, if you have a RAM disk, just change the $PGDATA/base/nnn/pgsql_tmp
> subdirectory into a symlink to some temp directory on the RAM disk.
> Should get you pretty much all the win with no need to move stuff around
> afterwards.
>
> You have to be sure the RAM disk is bigger than your biggest index though.

Hmm. That's a thought. I expect our largest index will still be bigger
than available RAM though. How can I check index sizes?

We already have pg_xlog on a dedicated mirrored disk. Would it help
significantly to give pgsql_tmp its own mirrored disk? PGDATA is on an 8
disk hardware RAID 5.

Wes