Tuning Postgres for Single connection use? Or is postgres wrong tool?

Question

Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions?

I'm a social scientist looking to use Postgres not as a database to be shared by multiple users, but rather as my own tool for manipulating a massive data set (I have 5 billion transaction records (600gb in csv) and want to pull out unique user pairs, estimate aggregates for individual users, etc.).

All the advice I can find online on tuning (this, this, this etc.) is written for people anticipating lots of concurrent connections. Anyone have basic rules of thumb for someone working on a database alone for data manipulation?

UPDATE: - This also means almost no writing, except to creation of new tables based on selections from the main table. (Apparently that's important -- thanks Erwin!)

Updated 2: - I'm on a Windows 8 VM with 16gb ram, SCSI VMware HD, and 3 cores if that's important.

Answer 1

If possible i would do following : Define 2 tablespaces, one for data , one for result tables. work_mem and maintenance_work_mem to 1Gb and 2Gb , shared buffer to high only if you run two or more queries same time to same set of data, if you don't do that then shared memory will be more or less wasted.

Biggest problems you will have is that Postgresql uses only one core per session , so easiest way to get more done is just run several queries on different sessions. Second problem will be disk i/o, several fast disks + different tablespaces help. Third is RAM settings. I would configure work_mem so that sessions*work_mem is somewhere at 10GB and keep maintenance_work_mem on 1-2GB. maintenance_work_mem can be set from query like this set maintenance_work_mem='6GB'; so you can set it higher when you do maintenance. You can also set work_mem like set work_mem='6GB' for session.

Table definitions also affect space used see: https://stackoverflow.com/questions/2966524/calculating-and-saving-space-in-postgresql . Probably not needed, but with 600GB of data scientific data it may help alot.

Answer 2

Got some feedback on this question from this Postgres listserv, so thought I'd share it here:

Jeff:

I'd go with a small shared_buffers, like 128MB, and let the OS cache as much as possible. This minimizes the amount of double buffering.

And set work_mem to about 6GB, then bump it up if that doesn't seem to cause problems.

In the scenario you describe, it is probably no big deal if you guess too high. Monitor the process, if it it starts to go nuts just kill it and start again with a lower work_mem. If it is a single user system, you can afford to be adventurous.

If you need to build indexes, you should bump up maintenance_work_mem, but I just would do that in the local session not system wide.

and from Gavin:

For tables that don't change, consider a packing density of 100%.

Take care in how you design your tables, and the column types.

Consider carefully the queries you are likely to use, so you can design appropriate indexes.