Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP

Loading…

Support binary formats for result fields and query parameters. #27

Open
lpsmith opened this Issue · 0 comments

1 participant

@lpsmith
Owner

For some of postgresql's types, transferring them in a binary format would result in dramatically better performance. Particularly large improvements would likely be seen for timestamps, floating-point numbers, integers, and arrays of many different kinds of values.

This seems a particularly large issue, as evidenced by the fact that most postgresql clients only support binary formats for a limited number of datatypes. Many applications get around this by supporting a limited number of binary formats that are the most relevant for the performance of that application, and then casting the remaining fields to text. However, this does not interact well with the approach currently used by postgresql-simple, which uses those types for (dynamic) type checking purposes.

Also, it's unfortunate that retrieving results from PostgreSQL in a binary format is currently an all-or-nothing proposition. It seems to me that we need to find an incremental approach to this issue; otherwise I think any attempt to resolve this is likely to fail. One possibility that has crossed my mind is to have some way of having instances of FromField support a textual format and an optional binary format, and advertise whether or not binary formats are accepted advertised in the type. Then hopefully some compile-time metaprogramming could determine whether or not the query's results could be requested in binary instead of text.

This is less than ideal, because a FromField instance that does not support binary formats would then disable binary transfers for (often more profitable) columns. But it is incremental, in a way.

Query parameters are a bit easier, not least because one can transfer a few parameters in a binary format while the remainder can be transferred in a textual format. The one downside is that this requires the use of protocol-level parameters, and these types of queries have the rather arbitrary limitation that multiple statements cannot be transferred to PostgreSQL in one request, meaning that it can take more round trips between postgres and the client to accomplish a task. And it is a minor change to the interface that will break a few people.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.