Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Use binary encoding for bind arguments #294

Open
jgaskins opened this issue Nov 28, 2024 · 0 comments · May be fixed by #295
Open

Use binary encoding for bind arguments #294

jgaskins opened this issue Nov 28, 2024 · 0 comments · May be fixed by #295

Comments

@jgaskins
Copy link
Contributor

Following up on #290, I would like to encode all bind params as binary. Partly because it might help avoid issues like #267 entirely to use a single encoding format, but also I ran a quick benchmark and, unless I've made a mistake in that benchmark, it's faster by a surprising margin and allocates significantly less on the heap (zero bytes allocated for String and UUID parameters).

benchmark code
require "benchmark"
require "uuid"
require "../src/pq/param"
require "../src/pg/geo"

buffer = IO::Memory.new(1 << 16)

{
  1234,
  1234.5678,
  Time.utc,
  UUID.v7,
  "abc",           # short string
  "." * 20 * 1024, # 20KB
  PG::Geo::Point.new(-76.4218, 39.288436),
}.each do |value|
  puts value.class
  Benchmark.ips do |x|
    x.report "text" { buffer.rewind.write text_encode(value).slice }
    x.report "binary" { buffer.rewind.write binary_encode(value).slice }
  end
  puts
end

def text_encode(value : Int32 | Float64 | UUID | String | Time | PG::Geo::Point)
  PQ::Param.encode(value)
end

def binary_encode(value : Int32 | Int64 | Float64)
  slice = Bytes.new(sizeof(typeof(value)))
  IO::ByteFormat::NetworkEndian.encode value, slice
  PQ::Param.binary slice
end

def binary_encode(string : String)
  PQ::Param.binary string.to_slice
end

def binary_encode(uuid : UUID)
  PQ::Param.binary uuid.bytes.to_slice
end

def binary_encode(time : Time)
  binary_encode (time.to_unix_ns // 1_000).to_i64
end

def binary_encode(point : PG::Geo::Point)
  slice = Bytes.new(sizeof(Float64) * 2)
  IO::ByteFormat::NetworkEndian.encode point.x, slice
  IO::ByteFormat::NetworkEndian.encode point.y, slice + sizeof(Float64)
  PQ::Param.binary slice
end
benchmark results
➜  crystal-pg git:(master) ✗ crystal run --release examples/bench_encode.cr
Int32
  text  72.62M ( 13.77ns) (± 1.50%)  32.0B/op   1.54× slower
binary 111.73M (  8.95ns) (± 1.19%)  32.0B/op        fastest

Float64
  text  18.43M ( 54.27ns) (± 1.85%)   176B/op   6.12× slower
binary 112.74M (  8.87ns) (± 1.19%)  32.0B/op        fastest

Time
  text   7.87M (127.01ns) (± 1.54%)   176B/op  13.33× slower
binary 104.95M (  9.53ns) (± 1.21%)  16.0B/op        fastest

UUID
  text  27.56M ( 36.28ns) (± 0.83%)  176B/op   7.66× slower
binary 211.01M (  4.74ns) (± 1.07%)  0.0B/op        fastest

String
  text 214.13M (  4.67ns) (± 1.61%)  0.0B/op   1.07× slower
binary 228.46M (  4.38ns) (± 1.81%)  0.0B/op        fastest

String
  text   4.82M (207.49ns) (± 6.31%)  0.0B/op   1.01× slower
binary   4.87M (205.24ns) (± 7.41%)  0.0B/op        fastest

PG::Geo::Point
  text  11.30M ( 88.51ns) (± 1.74%)   144B/op   9.55× slower
binary 107.91M (  9.27ns) (± 0.68%)  32.0B/op        fastest
additional notes on relative performance

In fairness, this benchmark only measures the time spent encoding the data into a slice. Presumably, we spend a trivial amount of time encoding a handful of query params compared to the time spent in TLS, the kernel segments of the network stack, and decoding an unbounded quantity of result values.

The maximum number of bind parameters that Postgres supports in a single query is 65535, so I don't imagine this is likely to have a notable impact on performance. The only way to know for sure, though, is to benchmark holistically.

@jgaskins jgaskins linked a pull request Dec 1, 2024 that will close this issue
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

1 participant