Re: Performance considerations for very heavy INSERT traffic

On 9/12/05, Brandon Black <blblack(at)gmail(dot)com> wrote:
>
> I'm in the process of developing an application which uses PostgreSQL for
> data storage. Our database traffic is very atypical, and as a result it has
> been rather challenging to figure out how to best tune PostgreSQL on what
> development hardware we have, as well as to figure out exactly what we
> should be evaluating and eventually buying for production hardware.

A few suggestions...

1) Switch to COPY if you can, it's anywhere from 10-100x faster than
INSERT, but it does not necessarily fit your idea of updating multiple
tables. In that case, try and enlarge the transaction's scope and do
multiple INSERTs in the same transaction. Perhaps batching once per
second, or 5 seconds, and returning the aggregate result ot the
clients.

2) Tune ext3. The default configuration wrecks high-write situations.
Look into data=writeback for mounting, turning off atime (I hope
you've done this already) updates, and also modifying the scheduler to
the elevator model. This is poorly documented in Linux (like just
about everything), but it's crtical.

3) Use 8.1 and strongly look at Bizgres. The data partitioning is critical.

4) Make sure you are not touching more data than you need, and don't
have any extraneous indexes. Use the planner to make sure every index
is used, as it substantially increases the write load.

I've worked on a few similar applications, and this is a hard thing in
any database, even Oracle.

Chris

--
| Christopher Petrilli
| petrilli(at)gmail(dot)com