Обсуждение: Binary COPY IN size reduction
Hey,
Currently every row in BINARY defines the number of columns (2 bytes) and every column defines its size (4 bytes per column) - see https://www.postgresql.org/docs/12/sql-copy.html#id-1.9.3.55.9.4.6.
Our application sends millions of rows to the database every hour using the COPY IN protocol.
We've switched recently from TEXT based COPY to the BINARY one.
We've noticed a slight performance increase, mostly because we don't need to escape the content anymore.
Unfortunately the binary protocol's output ended up being slightly bigger than the text one (e.g. for one payload it's 373MB now, was 356MB before)
We would like to share our thoughts on how we may be able to improve that, if you're open to suggestions.
We've switched recently from TEXT based COPY to the BINARY one.
We've noticed a slight performance increase, mostly because we don't need to escape the content anymore.
Unfortunately the binary protocol's output ended up being slightly bigger than the text one (e.g. for one payload it's 373MB now, was 356MB before)
We would like to share our thoughts on how we may be able to improve that, if you're open to suggestions.
It's possible our request is related to what the doc already refers to as:
It is anticipated that a future extension might add a header field that allows per-column format codes to be specified.
----
Currently every row in BINARY defines the number of columns (2 bytes) and every column defines its size (4 bytes per column) - see https://www.postgresql.org/docs/12/sql-copy.html#id-1.9.3.55.9.4.6.
NULL values are currently sent as a two byte -1 value.
Given that BINARY can't do any type conversion anyway, we should be able to deduce the expected size of most columns - while keeping the size prefixes for the dynamic ones (e.g. BYTEA or TEXT).
The extension part of the header (https://www.postgresql.org/docs/12/sql-copy.html#id-1.9.3.55.9.4.5:~:text=Header%20extension%20area%20length) would allow us to keep this backwards compatible by switching between the two versions.
If we don't want to use this part of the header for the BINARY format, maybe we could add a FIXED modifier to the COPY IN sql definition?
Or alternatively if we don't want to deduce their counts and sizes for some reason, could we get away with just sending it once and having every row follow the single header?
----
By skipping the column count and sizes for every row, in our example this change would reduce the payload to 332MB (most of our payload is binary, lightweight structures consisting of numbers only could see a >2x decrease in size).
For dynamic content, where we have to provide the size in advance we could send that in variable length encoding instead (e.g. the sign bit could signal whether the next byte is still part of the size). Variable length sizes would allow us to define a special NULL character as well.
In our case this change would reduce our payload further to 317MB.
In summary, these proposed changes would allow us to reduce the payload size by roughly 15% - but would expect even greater gains in general.
Thanks,
Lőrinc Pap
Thanks for the quick response, Tom!
What about implementing only the first part of my proposal, i.e. BINARY COPY without the redundant column count & size info?
That would already be a big win - I agree the rest of the proposed changes would only complicate the usage, but I'd argue that leaving out duplicated info would even simplify it!
I'll give a better example this time - writing 1.8 million rows with column types bigint, integer, smallint results in the following COPY IN payloads:
20.8MB - Text protocol
51.3MB - Binary protocol
25.6MB - Binary, without column size info (proposal)
Thanks for your time,
Lőrinc
On Fri, Apr 24, 2020 at 4:19 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Lőrinc Pap <lorinc@gradle.com> writes:
> We've switched recently from TEXT based COPY to the BINARY one.
> We've noticed a slight performance increase, mostly because we don't need
> to escape the content anymore.
> Unfortunately the binary protocol's output ended up being slightly bigger
> than the text one (e.g. for one payload it's *373MB* now, was *356MB* before)
> ...
> By skipping the column count and sizes for every row, in our example this
> change would reduce the payload to *332MB* (most of our payload is binary,
> lightweight structures consisting of numbers only could see a >*2x*
> decrease in size).
TBH, that amount of gain does not seem to be worth the enormous
compatibility costs of introducing a new COPY data format. What you
propose also makes the format a great deal less robust (readers are
less able to detect errors), which has other costs. I'd vote no.
regards, tom lane