[Libpqxx-general] Exec query with binary parameters

[Michael Akinde]

Hi Jeroen et al.,

Thanks for the response. I'm on leave right now and until after easter, so unfortunately it'll be a few days before I can follow up on this properly.

----- "Jeroen Vermeulen" <jtv at xs4all.nl> wrote:
> Michael, is the performance improvement you're seeing compared to
> plain INSERT statements?  There are much faster ways to insert data:

We have a function interface to the database, since we need to isolate the users from the table structure (we have very poor experiences with allowing any kind of developer/user interaction with the schema). But these are INSERT statements internally.
 
> 1. There's tablewriter if you're writing many rows in one go.  It
> saves a lot of network traffic, statement parsing etc.

Haven't looked at this, but it don't think it will help us in the general case (since a function really only works for single tuple inserts).

> 2. Prepared statements.  Those do support binary parameters.

I didn't look at this myself, but we used prepared statements before we moved from large objects to inserting using bytea binary objects. As I understood it, the prepared statement interface struggled with recognizing the type-casting because of the PL/PGSQL functions. It's one thing I would like to look over again. 
 
> 3. Large objects.  This API provides a file-like interface to blobs. 
> I don't think it's still the preferred way of handling binary data
> though, now that the old limits on row sizes are gone.

We used large objects before (which worked great), but unfortunately Postgres seems to suffer from disk fragmentation once the size of the blobs exceed ca. 1 Mb. At least that is the theory of the Postgres developers, and given that this is a fairly common issue in databases in general, it doesn't seem unlikely.

I should add that the further tests I managed to do prior to going on leave, provide some additional food for thought. As mentioned, binary transfer had a major advantage when running with the database on a standard PC, but with more processors (8x), 64-bit architecture, and faster harddisks, the performance advantage seems to be much smaller. I'll be looking further into this once I'm back at the office.

Regards,

Michael Akinde
----
Database Architect, Met.no
http://wdb.met.no