Re: libpq pipelineing

Hi,

Here are some initial numbers.

DB::Client
Warming up --------------------------------------
         db-postgres   281.000  i/100ms
          db-mariadb   399.000  i/100ms
              mysql2   533.000  i/100ms
                  pg   591.000  i/100ms
Calculating -------------------------------------
         db-postgres      2.725k (± 1.8%) i/s -     13.769k in   5.053750s
          db-mariadb      3.990k (± 2.4%) i/s -     19.950k in   5.002453s
              mysql2      5.153k (± 4.7%) i/s -     26.117k in   5.079570s
                  pg      5.772k (± 4.4%) i/s -     28.959k in   5.027423s

Comparison:
                  pg:     5771.7 i/s
              mysql2:     5152.8 i/s - 1.12x  (± 0.00) slower
          db-mariadb:     3990.3 i/s - 1.45x  (± 0.00) slower
         db-postgres:     2725.5 i/s - 2.12x  (± 0.00) slower

The db-* gems are event driven. However, that is of less interest right now.

This benchmark creates a table, inserts 1000 (or some fixed number) of
rows, and then selects them all back out.

What I noticed is that `PQgetvalue` and `PQgetisnull` is not
particularly efficient, at least via FFI. Requires rows * columns * 2
FFI calls.

libmariadb provides `mysql_fetch_row` which returns a `char **` per
row. Requires only rows FFI calls.

Does a similar method exist for libpq? e.g. `PGgetrow(index) ->
char**` (array of strings, one for each column, may be nil to indicate
null).

Kind regards,
Samuel

On Tue, 30 Jun 2020 at 12:50, Samuel Williams
<space.ship.traveller@gmail.com> wrote:
>
> Tom, I'm implementing a small abstraction layer for event-driven
> result streaming on PostgreSQL and MariaDB for Ruby, and I'll endeavor
> to report back with some numbers once I have enough of it working to
> benchmark something meaningful.
>
> Thanks for your patience and help.
>
> Kind regards,
> Samuel
>
> On Tue, 30 Jun 2020 at 02:06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > Samuel Williams <space.ship.traveller@gmail.com> writes:
> > > Those methods don't seem to have an equivalent in libpq - you can use
> > > PQgetResult but it buffers all the rows. Using single row mode results
> > > in many results for each query (seems like a big overhead).
> >
> > Have you got any actual evidence for that?  Sure, the overhead is
> > more than zero, but does it mean anything in comparison to the other
> > costs of data transmission?
> >
> > > Maybe the statement about efficiency is incorrect, but it would be
> > > nice if you could incrementally stream a single result set more
> > > easily.
> >
> > More easily than what?  If we did not construct a PGresult then we would
> > need some other abstraction for access to the returned row, dealing with
> > error cases, etc etc.  That would mean a lot of very duplicative API code
> > in libpq, and a painful bunch of adjustments in client code.
> >
> >                         regards, tom lane