Re: Improving PostgreSQL insert performance

Am 09.06.2017 um 15:04 schrieb Frits Jalvingh:
> Hi all,
>
> I am trying to improve the runtime of a big data warehouse
> application. One significant bottleneck found was insert performance,
> so I am investigating ways of getting Postgresql to insert data faster.

* use COPY instead of Insert, it is much faster
* bundle all Insert into one transaction
* use a separate disk/spindel for the transaction log

>
> I already changed the following config parameters:
> work_mem 512MB
> synchronous_commit off
> shared_buffers 512mb
> commit_delay 100000
> autovacuum_naptime 10min
>
> Postgres version is 9.6.3 on Ubuntu 17.04 64 bit, on a i7-4790K with
> 16GB memory and an Intel 750 SSD. JDBC driver is postgresql-42.1.1.
>

increase shared_buffers, with 16gb ram i would suggest 8gb

Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com