Re: [PERFORM] Improving PostgreSQL insert performance

[Joe Conway]

On 06/10/2017 07:32 PM, Alvaro Herrera wrote:
> Frits Jalvingh wrote:
> 
>> 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..
> 
> Did you try inserting multiple tuples in one command?  Something like
> INSERT INTO .. VALUES ('col1', 'col2'), ('col1', 'col2'), ('col1', 'col2')
> It's supposed to be faster than single-row inserts, though I don't
> know by how much.

When I did the testing of the patch originally I saw significant
improvements, e.g. 8x in early versions. The thread is here:
https://www.postgresql.org/message-id/flat/44C4451A.4010906%40joeconway.com#44c4451a.4010...@joeconway.com

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature

Re: [PERFORM] Improving PostgreSQL insert performance

[Alvaro Herrera]

Frits Jalvingh wrote:

> 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..

Did you try inserting multiple tuples in one command?  Something like
INSERT INTO .. VALUES ('col1', 'col2'), ('col1', 'col2'), ('col1', 'col2')
It's supposed to be faster than single-row inserts, though I don't
know by how much.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Improving PostgreSQL insert performance

[Frits Jalvingh]

I think binary is worse.. according to the postgres documentation:

The binary format option causes all data to be stored/read as binary format
rather than as text. It is somewhat faster than the text and CSV formats,
but a binary-format file is less portable across machine architectures and
PostgreSQL versions. Also, the binary format is very data type specific;
for example it will not work to output binary data from a smallint column
and read it into an integer column, even though that would work fine in
text format.

By itself it is similar in badness as both require completely different
semantics than insert..
On Sat, 10 Jun 2017 at 22:12, Nicolas Paris  wrote:

> > I tried the copy command, and that indeed works quite brilliantly:
> > Inserted 2400 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.
>
> Have you thought about the COPY with binary format ? Thats looks more
> robust than the text format you used in your benchmarks.
>

Re: [PERFORM] Improving PostgreSQL insert performance

[Nicolas Paris]

> I tried the copy command, and that indeed works quite brilliantly:
> Inserted 2400 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.

Have you thought about the COPY with binary format ? Thats looks more
robust than the text format you used in your benchmarks.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Improving PostgreSQL insert performance

[Frits Jalvingh]

On Sat, Jun 10, 2017 at 12:08 AM Vladimir Sitnikov <
sitnikov.vladi...@gmail.com> wrote:

> Would you mind sharing the source code of your benchmark?
>

The source code for the several tests, plus the numbers collected so far,
can be found at:

https://etc.to/confluence/display/~admjal/PostgreSQL+performance+tests

Regards,

Frits

Re: [PERFORM] Rollback table data.

[phb07]

Hi Dinesh,

Le 07/06/2017 à 14:48, Andreas Kretschmer a écrit :



Am 07.06.2017 um 13:33 schrieb Dinesh Chandra 12108:


Dear Expert,

Is there any way to rollback table data in PostgreSQL?


if you are looking for somewhat similar to flashback in oracle the 
answer is no.


Well, if this is what you are looking for, the E-Maj extension may help 
you. In few words, it allows 1) to log updates on tables sets (using 
triggers), 2) to set marks on these tables sets when they are in a 
stable state and 3) to travel back and forth to these marks.

Some pointers:
- pgxn to download a stable version : https://pgxn.org/dist/e-maj/ (the 
downloadable zip file also contains a presentation that may help to 
quickly get a good view of the extension - doc/emaj.2.0.1_doc_en.pdf)

- on-line documentation : http://emaj.readthedocs.io/
- github projects : https://github.com/beaud76/emaj and 
https://github.com/beaud76/emaj_ppa_plugin


Best regards. Philippe.

Regards, Andreas





--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance