From: Kenneth Marshall <ktm@rice.edu>
To: Frits Jalvingh <jal@etc.to>
Cc: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Andreas Kretschmer <andreas@a-kretschmer.de>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Improving PostgreSQL insert performance
Date: Fri, 9 Jun 2017 09:53:47 -0500
Message-ID: <20170609145347.GE26506@aart.rice.edu> (raw)
In-Reply-To: <CAKhTGFXiU_t_EiB37q-H897pFqQY9a=hDAuAZV7T3dLMTsvSiw@mail.gmail.com>
References: <CAKhTGFX-ChBSjqENrAv8uqUR_H5PTvf14jG0cOAq6c-ami_7sQ@mail.gmail.com>
<9e2a0a34-dd83-43af-d41a-791b34ff76e1@a-kretschmer.de>
<20170609132857.GC26506@aart.rice.edu>
<CAKhTGFVTvFxsfngDNgQV-ciWYFn6aXROwGj6s4Gqkqa0bTusJw@mail.gmail.com>
<CAOR=d=1CYLvtesjcCmC95rWZSzJ02pYZyfJ8SA9jfx-CCekx3A@mail.gmail.com>
<CAKhTGFXiU_t_EiB37q-H897pFqQY9a=hDAuAZV7T3dLMTsvSiw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On Fri, Jun 09, 2017 at 02:39:37PM +0000, Frits Jalvingh 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
Hi Frits,
Have you looked at UNLOGGED tables and also having more that 1 insert
stream running at a time. Sometimes multiple parallel inserts can be
faster.
Regards,
Ken
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ktm@rice.edu, jal@etc.to, scott.marlowe@gmail.com, andreas@a-kretschmer.de
Subject: Re: Improving PostgreSQL insert performance
In-Reply-To: <20170609145347.GE26506@aart.rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox