Frederik Ramm <frederik(at)remote(dot)org> wrote:
> I am (ab)using a PostgreSQL database (with PostGIS extension) in
> a large data processing job - each day, I load several GB of data,
> run a lot of analyses on it, and then throw everything away again.
> Loading, running, and dumping the results takes about 18 hours
> every day.
>
> The job involves a lot of index building and sorting, and is run
> on a 64-bit machine with 96 GB of RAM.
>
> Naturally I would like the system to use as much RAM as possible
> before resorting to disk-based operations, but no amount of
> maintenance_work_mem setting seems to make it do my bidding.
If you can tolerate some risk that for a given day you might fail to
generate the analysis, or you might need to push the schedule back
to get it, you could increase performance by compromising
recoverability. You seem to be willing to consider such risk based
on your mention of a RAM disk.
- If a single session can be maintained for loading and using the
data, you might be able to use temporary tables and a large
temp_buffers size. Of course, when the connection closes, the
tables are gone.
- You could turn off fsync and full_page_writes, but on a crash
your database might be corrupted beyond usability.
- You could turn off synchronous_commit.
- Make sure you have archiving turned off.
- If you are not already doing so, load the data into each table
within the same database transaction which does CREATE TABLE or
TRUNCATE TABLE.
Other than the possibility that the temp table might keep things in
RAM, these suggestions don't directly address your question, but I
thought they might be helpful.
-Kevin