Re: Tuning resource parameters for a logging database.

On Thu, May 21, 2009 at 1:36 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>

Below, I meant with a logging / reporting database...

> With a logging database you're optimizing two often opposing actions.
> Lots of small inserts in a stream that HAVE to get processed and put
> in efficiently.  This is often accomplished with minimum
> shared_buffers and work_mem, because there's no need for the overhead
> of large shared_buffers and insert queries for logging dbs don't need
> much work_mem.
>
> With a reporting database you run queries that chew up tons of memory
> both shared_buffers and work_mem for efficient operation.
>

>> work_mem = 128MB
>
> Bigger than needed for logging, good for reporting.  You can probably
> just leave it.

Note that you can set work_mem per user, so have the reporting users
log in with a different user and you can crank this up a bit, say 512M
to 1G if you're only ever running 1 or 2 reports. Careful about
running the machine out of memory, work_mem is a foot gun if you set
it too high and run a lot of queries at once.