Re: Improving PostgreSQL insert performance

Hi all,

Thanks a lot for the many responses!

About preparing statements: this is done properly in Java, and pgsql does
it by itself. So that cannot be done better ;)

I tried the copy command, and that indeed works quite brilliantly:
Inserted 24000000 rows in 22004 milliseconds, 1090710.7798582076 rows per
second

That's faster than Oracle. But with a very bad interface I have to say for
normal database work.. I will try to make this work in the tooling, but it
needs some very special code to format all possible values properly, and to
manage the end of the copy, so it is not usable in general which is a pity,
I think.

So, I am still very interested in getting normal inserts faster, because
that will gain speed for all work.. If Oracle can do it, and Postgres is
able to insert fast with copy- where lies the bottleneck with the insert
command? There seems to be quite a performance hit with the JDBC driver
itself (as the stored procedure is a lot faster), so I can look into that.
But even after that there is quite a gap..

Regards,

Frits

On Fri, Jun 9, 2017 at 4:33 PM Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
wrote:

> On Fri, Jun 9, 2017 at 7:56 AM, Frits Jalvingh <jal(at)etc(dot)to> wrote:
> > Hi Kenneth, Andreas,
> >
> > Thanks for your tips!
> >
> > I increased shared_buffers to 8GB but it has no measurable effect at
> all. I
> > think that is logical: shared buffers are important for querying but not
> for
> > inserting; for that the speed to write to disk seems most important- no
> big
> > reason to cache the data if the commit requires a full write anyway.
> > I also changed the code to do only one commit; this also has no effect I
> can
> > see.
> >
> > It is true that Oracle had more memory assigned to it (1.5G), but unlike
> > Postgres (which is completely on a fast SSD) Oracle runs on slower disk
> > (ZFS)..
> >
> > I will try copy, but I first need to investigate how to use it- its
> > interface seems odd to say the least ;) I'll report back on that once
> done.
>
> I you want an example of copy, just pg_dump a table:
>
> pg_dump -d smarlowe -t test
>
> (SNIP)
> COPY test (a, b) FROM stdin;
> 1 abc
> 2 xyz
> \.
> (SNIP)
>