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(dot)ship(dot)traveller(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > Samuel Williams <space(dot)ship(dot)traveller(at)gmail(dot)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