Re: Improving PostgreSQL insert performance

["Sunkara, Amrutha" <amrutha@xxxxxxxxxxx>]

Frits, 

When you use the copy command, are you doing anything special to get the run time that you are indicating?

On Fri, Jun 9, 2017 at 10:39 AM, Frits Jalvingh <jal@xxxxxx> wrote:

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.marlowe@xxxxxxxxx> wrote:

On Fri, Jun 9, 2017 at 7:56 AM, Frits Jalvingh <jal@xxxxxx> 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)