Binary COPY IN size reduction
Hey, 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. 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 <https://en.wikipedia.org/wiki/Variable-length_code> 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* -- Lőrinc Pap Senior Software Engineer <https://gradle.com/>
Re: Binary COPY IN size reduction
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) I.e. this would make the binary protocol almost as small as the text one (which isn't an unreasonable expectation, I think), while making it easier to use at the same time. Thanks for your time, Lőrinc On Fri, Apr 24, 2020 at 4:19 PM Tom Lane wrote: > =?UTF-8?Q?L=C5=91rinc_Pap?= 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 > -- Lőrinc Pap Senior Software Engineer <https://gradle.com/>
