Re: [psycopg] Turbo ODBC - Mailing list psycopg

On 1/15/17 3:25 PM, Adrian Klaver wrote:
> Have you looked at asyncpg:

I'm pulling Matt Rocklin in, who I've been working with on some plpython
improvements. Here's his offlist replies to me:

> On 1/15/17 5:15 PM, Matthew Rocklin wrote:
>> You might also contact Wes McKinney at check out
>> Arrow: http://pyarrow.readthedocs.io/en/latest/
>>
>> This project was designed for inter-system data interchange.  It would
>> help with conversion to Spark and other popular tools as well.
>>
>> On Sun, Jan 15, 2017 at 6:01 PM, Matthew Rocklin <mrocklin@continuum.io
>> <mailto:mrocklin@continuum.io>> wrote:
>>
>>     I haven't taken a look at asyncpg.
>>
>>     Quick note, you might want to be careful about mixing HDFS and
>>     HDF5.  Postgres's competition for data storage is HDF5 not HDFS
>>     (Which stands for the Hadoop File System)
>>
>>     I still think that the best thing to do here would be to dump out
>>     python arrays for each of the columns in a result set.  I suspect
>>     that you could beat /any/ system that doesn't do this handily.  This
>>     would avoid any performance pain of building up Python objects, and
>>     can continue to use just the standard library.
>>
>>     You can stick to Pure Python and still send arrays encoded with
>>     bytes.  We can convert these to NumPy or Pandas trivially with zero
>>     overhead.
>>
>>     In [1]: from array import array   # this is in the standard library
>>     In [2]: b = b'\x00' * 16  # 16 zeroes as a bytestring
>>     In [3]: a = array('L', b) # eight byte unsigned integer
>>     In [4]: a
>>     Out[4]: array('L', [0, 0])
>>
>>     We would only be bound by
>>
>>      1. The cost in postgres to tranpose the data from row-major to
>>         column major
>>      2. The cost to move bytes across a socket
>>
>>     This should run at gigabytes per second.  Everything else is likely
>>     to be competing around to 50-100MB/s range at top speed is my guess.

My guess is that the big gains for external communication would come from:

1) reducing latency effects
2) reducing data marshaling

1 could be accomplished in various ways, and some changes to the FE-BE
protocol might be helpful. If we can demonstrate a clear win we might be
able to get protocol changes. I do suspect this needs to be tune-able
though. My 4x improvement to plpy.execute is changing from the
equivalent to a large batch to single row operation. Moving data to
python over a local filesystem socket would be hurt be a large batch
size, while something on a high latency connection would presumably
benefit from moderately large batches since that reduces overall TCP
overhead.

I think 2 would necessitate FE-BE changes, although some of that could
be tested without the changes. Right now, everything is going to get
marshaled into a simple text format (ie: ints become a string of
digits), but for test purposes there's ways you could run that through
the binary output functions (in the case of int4, you'd get 4 bytes in
network order, which should be faster to handle (and could be passed
directly to something like ndarray).

The two problems you run into with the array type are handling NULLs and
building columns from rows. You could allow the option of specifying
that NULLs won't be allowed in specific fields, or you could use
something like a Pandas Series that provides other ways of handling the
equivalent of NULL.

For the row to column conversion, there's simply no way to get around
the pattern of appending items one at a time to an array-like container
(list, ndarray, Series, etc), and dealing with the memory allocation
problem. I've looked at python's list code and it's certainly no worse
at that then what Postgres would do. The big reason I would do that work
in python though is you then have the ability to use an number of types
for that; they just have to support the equivalent of append().
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)