Binary COPY IN size reduction - Mailing list pgsql-hackers
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
pgsql-hackers by date: