storing C binary array in bytea via libpq
Hi,
I've a table in which a field is BYTEA, as I need to store around 200 raw
bytes in this field.
I need to perform many many INSERT starting from a common C array and, in
order to get good performance, I want to do many of them in a single BEGIN
COMMIT block.
What is the best choice from libpq?
PQexec needs to have the bytes encoded into a string. This expansion lowers
the performance (I tried with \x format, not with the other option yet).
With PQexecParams, can I send a raw array of bytes as a bytea parameter?
And also, as PQexecParams can't accept multi commands, if I enclose many
PQexecParams calls between a PQexec("BEGIN;") and PQexec("COMMIT") would
it work as I wish?
Other options?
Regards
Pupillo
Tom DalPozzo <t.dalpozzo@gmail.com> writes:
I've a table in which a field is BYTEA, as I need to store around 200 raw
bytes in this field.
I need to perform many many INSERT starting from a common C array and, in
order to get good performance, I want to do many of them in a single BEGIN
COMMIT block.
What is the best choice from libpq?
PQexec needs to have the bytes encoded into a string. This expansion lowers
the performance (I tried with \x format, not with the other option yet).
With PQexecParams, can I send a raw array of bytes as a bytea parameter?
Sure. Specify binary format for that parameter.
And also, as PQexecParams can't accept multi commands, if I enclose many
PQexecParams calls between a PQexec("BEGIN;") and PQexec("COMMIT") would
it work as I wish?
Well, it'll be faster than committing them separately, but have you
considered bundling this up into a single INSERT with multiple VALUES
rows? It'd be a bit tedious to manage by hand, but if the command is
being constructed by a program anyway, it shouldn't be much harder
than separate INSERTs. Much of the time in this is going to go into
parsing and network round-trip overhead, so one statement is going
to handily beat N statements whether they're in a transaction block
or not.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[ please keep the list cc'd ]
Tom DalPozzo <t.dalpozzo@gmail.com> writes:
To be honest, I didn't know or I forgot about multiple VALUES in one
command! Thanks for reminding!
As for the PQexecParams, should I specify something in const Oid
*paramTypes parameter? Or just something like $1::bytea?
You can do it either way. Hard-wiring the type OID will be a bit faster
than making the server parse a ton of repetitive cast constructs, but on
the other hand it means you have a magic number in your program. It's
unlikely the OID assigned to bytea would ever change, but the technique
doesn't scale well to user-defined types. Take your choice.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Import Notes
Reply to msg id not found: CAK77FCS0RCVBJ4ZaRe79mp0YVvz7rRU4RvEtEV0q90KWW0W8dA@mail.gmail.com
Hi,
I tried both ways: they're ok.
Also, multiple VALUES in one INSERT is actually better as performance.
Thanks again
Pupillo
2016-12-06 19:49 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:
Show quoted text
[ please keep the list cc'd ]
Tom DalPozzo <t.dalpozzo@gmail.com> writes:
To be honest, I didn't know or I forgot about multiple VALUES in one
command! Thanks for reminding!
As for the PQexecParams, should I specify something in const Oid
*paramTypes parameter? Or just something like $1::bytea?You can do it either way. Hard-wiring the type OID will be a bit faster
than making the server parse a ton of repetitive cast constructs, but on
the other hand it means you have a magic number in your program. It's
unlikely the OID assigned to bytea would ever change, but the technique
doesn't scale well to user-defined types. Take your choice.regards, tom lane
On Wed, Dec 7, 2016 at 4:10 AM, Tom DalPozzo <t.dalpozzo@gmail.com> wrote:
Hi,
I tried both ways: they're ok.
Also, multiple VALUES in one INSERT is actually better as performance.
If you are chasing performance with the binary protocol you might want
to take a look at libpqtypes: http://libpqtypes.esilo.com/
It facilitates fast communication to/from the database. For
inserting, you stack an array of composites locally and send it to the
database with a single query and the receiving side can unwrap it and
do the insert in a function. Advancements in json handling have
largely displaced this kind of usage in many situations but if you
have extreme performance requirements it's still worth a look.
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general