Thread: storing C binary array in bytea via libpq

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

[ 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