Performance of pgx.Conn.Query() with Rows v.s. pgconn.PgConn.CopyTo() binary result decoding

[ns-gzhang]

Hi @jackc I have a question regarding performance in the subject. The background is that I'm developing something similar to Debezium using your pglogrepl library to get CDC from PG into a data warehouse directly. I have the streaming part working (thanks to your pglogrepl example). I need to add initial snapshot dumping as efficiently as possible, as the data volume is potentially large, especially if we use a PG snapshot for recovery when necessary.
I could use pgx.Conn.Query() to get rows data the standard way. If pgconn.PgConn.CopyTo() performance is significantly better, however, I would want to use pgconn.PgConn.CopyTo() with binary decoding. But I need to learn how to decode the binary data from CopyData received from the CopyTo() stream, if you could point me to the right direction. We don't have any special types so setup for decoding is relatively easy. Your guidance is much appreciated!

[jackc]

I don't know if performance will be significantly better with copy or not. I would suggest benchmarking Query without parsing the results -- that is, call Close without reading the rows first. Then benchmark CopyTo and see if it is significantly faster.

If it is enough better that you want to proceed, you can use the pgtype.Map from the connection to decode the individual values. But I don't think pgx has any code you could use to directly decode the copy stream. The pgx CopyFrom logic might be somewhat helpful though. I think it would be roughly the inverse.

[ns-gzhang]

Thanks Jack. I was just reading the pgx CopyFrom code, seems to me CopyTo would be exactly the reserve as you said.
I will do a benchmarking. To make sure I understand what you suggested, by "without parsing the results", you mean to iterate through with rows.Next() without scan, and then rows.Close()?
To dump a snapshot of a table, I would just use SELECT * FROM table for one connection, and add a range predicate with WHERE ... if I need to go in parallel with multiple connections to speed up. The query to be used will be the same for either approaches.

[jackc]

You don't need to call rows.Next before rows.Close. But other than that, yes.

[ns-gzhang]

Thanks Jack. I didn't realize that rows.Close() would drain the incoming stream.
I wrote the following code to compare the elapsed times. Hope the way copyto() writing to io.Discard is meaningful.

func query() {
	st := time.Now()
	rows, err := conn.Query(context.Background(), "SELECT * FROM files")
	if err != nil {
		fmt.Fprintf(os.Stderr, "query error: %v", err)
		return
	}
	rows.Close()

	fmt.Println("query time elapsed:", time.Since(st))
}

func copyto() {
	st := time.Now()
	_, err := conn.PgConn().CopyTo(context.Background(), io.Discard, "COPY files TO STDOUT binary")
	if err != nil {
		fmt.Fprintf(os.Stderr, "copy error: %v", err)
		return
	}

	fmt.Println("copyto time elapsed:", time.Since(st))
}

On a small table files with 1M+ rows, PG on a container on my MacPro, local execution (outside of the PG container) results:

query time elapsed: 7.87152343s
copyto time elapsed: 7.843730505s
query time elapsed: 8.08019797s
copyto time elapsed: 7.68677219s
copyto time elapsed: 8.740366395s   <= swap order
query time elapsed: 7.928080484s

Does this mean not worth the effort to use CopyTo()?

[jackc]

Does this mean not worth the effort to use CopyTo()?

In looks that way, at least in this case.