Default result formats should be settable per session

[jackc]

The binary format can be significantly faster for some data types. Unfortunately, blindly executing a query with all results returned in the binary format is not safe because there is no guarantee the driver understands the binary format for every type the query returns. In addition, setting all results to binary is undesirable as some types are significantly faster in text than binary format (https://github.com/jackc/pg_text_binary_bench).

This means that either the text format must be used or an extra network round trip must be expended for a Describe before the query is sent.

pgx tries to work around this to some degree by having automatic statement preparation and caching. It works well, but must be disabled when using a connection pooler like PgBouncer.

If the default result formats could be specified, then the binary format could be used more often, with less network overhead, with less driver complexity.

[davecramer]

this is an excellent idea. This does not seem particularly difficult in my mind to do from the server POV. Have you looked at what it might take ?

[jackc]

Have you looked at what it might take ?

No, my familiarity with the PostgreSQL source code is mostly limited to examining backed/utils/adt to decipher the binary formats and occasionally libpq to see what the C client's behavior is.

[davecramer]

I'll try to find some time this week to look at this.

[davecramer]

how would you like this implemented
set FORMAT_BINARY='int8', 'int4', 'int2', ?
alternatively we could just use oid's?

[jackc]

Oh, that's interesting. I hadn't even thought about using set. I had been thinking that it would need to be a new message in the wire protocol. But set would definitely be cleaner. 👍

I'm not sure about oid vs name. oids are simpler, but names are easier to use.

oids require a lookup for non-builtin types like hstore.

I presume names would be interpreted like regtype to handle schemas. This is a little easier to use but it means that the actual type affected would be dependent on search_path. And then there are edge cases like what happens if FORMAT_BINARY is set and then search_path changes.

So as as user of this feature it doesn't really matter to me, but my guess is oid would be easier to implement.

Another question is does FORMAT_BINARY override the requested format of parameterized / prepared queries or is there a new format code that means "text or default".

[davecramer]

So as as user of this feature it doesn't really matter to me, but my guess is oid would be easier to implement.

Honestly I thought the use case just be simple types like ints and other numbers, possibly text. Hadn't even considered UDT's

Another question is does FORMAT_BINARY override the requested format of parameterized / prepared queries or is there a new format code that means "text or default".

I had thought this was just for simple query. Currently the code path for prepared queries is a bit more difficult to change
Is this useful to you for simple queries ?

[jackc]

Honestly I thought the use case just be simple types like ints and other numbers, possibly text. Hadn't even considered UDT's

That would be the predominant use case to be sure. But pgx's type mapping system is entirely pluggable. To pgx the only difference between standard builtin types and UDTs is builtin types are preregistered by default.

Is this useful to you for simple queries ?

Yes, but only in narrow circumstances. pgx supports 5 different query execution modes. The first 4 use the extended protocol.

1. Automatically prepare and cache statements. This is the default.
2. Cache statement descriptions (i.e. argument and result types) and assume they do not change. Behavior is undefined if the database schema is modified or the search_path is changed.
3. Get the statement description on every execution. Queries require two round trips to execute. It is safe even when the the database schema is modified concurrently.
4. Send query without getting description first. This uses the extended protocol, but uses the text result format.
5. Simple query protocol.

pgx's default mode uses prepared statements. The other four modes are mostly used when a connection pooler precludes usage of prepared statements. They each provide a different trade off of network round trips, binary format support, parameterized queries vs client side parameter escaping and interpolation, and resilience to a concurrent change to the database schema.

This change would improve performance of mode 5, but the big win for me would be in simplifying the extended protocol modes. It might make sense to entirely remove modes 2 and 3 since mode 4 would gain binary format support.

[davecramer]

OK, it's somewhat easier to do 5. I'll hack together a patch shortly and see if we get any traction.

[davecramer]

@jackc if you have some time can you test this https://github.com/davecramer/postgres/tree/format_binary
it allows for set format_binary='20,21,23'
Where it is a comma separated string of oids
it does not check for errors
I know it works for simple selects.

I'll test it myself but I thought I would throw this up there for you to test

[jackc]

I haven't done any C development in a really long time, and rarely even compile C projects from source. And I can't say I'd ever compiled PostgreSQL from source before either so I had some trepidation trying this. But wow, PostgreSQL is a clean build. It really was just ./configure --prefix=..., make, and make install. Maybe now I'll try beta releases sometime.

Anyway, your patch worked perfectly for me.

Running this program:

package main

import (
	"context"
	"fmt"
	"log"
	"os"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgproto3"
)

func main() {
	ctx := context.Background()

	config, err := pgx.ParseConfig(os.Getenv("DATABASE_URL"))
	if err != nil {
		log.Fatal(err)
	}
	config.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol

	conn, err := pgx.ConnectConfig(ctx, config)
	if err != nil {
		log.Fatal(err)
	}
	defer conn.Close(ctx)

	_, err = conn.Exec(ctx, `set format_binary='20,21,23'`)
	if err != nil {
		log.Fatal(err)
	}

	conn.PgConn().Frontend().Trace(os.Stdout, pgproto3.TracerOptions{SuppressTimestamps: true})

	var n2 int16
	var n4 int32
	var n8 int64
	var s string
	err = conn.QueryRow(ctx, `select 1::int2, 2::int4, 3::int8, 'some text'`).Scan(&n2, &n4, &n8, &s)
	if err != nil {
		log.Fatal(err)
	}

	fmt.Println(n2, n4, n8, s)
}

Got this output:

jack@glados ~/dev/pgx_default_format_binary » DATABASE_URL='database=postgres port=15432' go run main.go
F	Query	51	 "select 1::int2, 2::int4, 3::int8, 'some text'"
B	RowDescription	103	 4 "int2" 0 0 21 2 -1 1 "int4" 0 0 23 4 -1 1 "int8" 0 0 20 8 -1 1 "?column?" 0 0 25 -1 -1 0
B	DataRow	46	 4 2 '\x0\x1' 4 '\x0\x0\x0\x2' 8 '\x0\x0\x0\x0\x0\x0\x0\x3' 9 'some text'
B	CommandComplete	14	 "SELECT 1"
B	ReadyForQuery	6	 I
1 2 3 some text
F	Terminate	5

It's definitely sent back in binary from the simple protocol and pgx can understand it.

[davecramer]

Ya, the compile (as long as you have the dependencies) is pretty easy.

So it would be useful to get some performance numbers before I go to hackers with the patch.
I realized there is also a great argument for this with pgbouncer as we have to turn off named statements and binary mode since there's no way to get the statement back from pgbouncer.
I think JDBC will just understand this as well.

Thanks!

[davecramer]

So JDBC worked without any changes... now to get some performance measurements.

[jackc]

I did some test benchmarks here: https://github.com/jackc/postgresql_simple_protocol_binary_format_bench

It executes a simple select that returns an int4 column, a text column, and a timestamptz column.

jack@glados ~/dev/postgresql_simple_protocol_binary_format_bench » DATABASE_URL='database=postgres port=15432' go test -bench=. -benchtime=5s
goos: darwin
goarch: amd64
pkg: github.com/jackc/postgresql_simple_protocol_binary_format_bench
cpu: Intel(R) Core(TM) i9-9880H CPU @ 2.30GHz
BenchmarkTextFormat1Row-16         	  124485	     47964 ns/op
BenchmarkBinaryFormat1Row-16       	  128959	     46008 ns/op
BenchmarkTextFormat100Rows-16      	   41881	    144596 ns/op
BenchmarkBinaryFormat100Rows-16    	   61512	     97302 ns/op
PASS
ok  	github.com/jackc/postgresql_simple_protocol_binary_format_bench	27.545s

At 100 rows the text format takes 48% longer than the binary format.

[davecramer]

Is this on localhost ?

[jackc]

Unix socket.

[davecramer]

so really measuring conversion time. I would guess this would be more dramatic over a longer distance

[jackc]

so really measuring conversion time.

Right, that's the win on the client side.

I would guess this would be more dramatic over a longer distance

I would think it would be less meaningful over a longer distance because the network time would start to dominate.

[davecramer]

I would think it would be less meaningful over a longer distance because the network time would start to dominate.

depends on the attribute. large text fields would be a win.