crystal-pg icon indicating copy to clipboard operation
crystal-pg copied to clipboard

Encode query params in binary instead of text

Open jgaskins opened this issue 1 year ago • 2 comments

Been messing with this the past couple days. I'm not sure yet, but this may actually not be a good idea. Encoding values as binary doesn't seem to get the benefit of autocasting, such as from int4 up to int8. Either that or I've screwed something up.

I'll annotate some of the weirdness I saw in review comments. Maybe someone knows how to fix/work around those things.

It did cut down pretty significantly on the amount of heap memory allocated per query with bind args, though. It was a lot more than I expected for a simple query — about 44% fewer bytes allocated across the complete query execution. There are still some places I want to optimize heap allocations (like in serializing array types), but that was pretty nice to see.

Benchmark code
require "benchmark"
require "../src/pg"

pg = DB.open "postgres:///"
pg.exec <<-SQL
  CREATE TABLE IF NOT EXISTS temp_data (
    id UUID PRIMARY KEY,
    name TEXT NOT NULL,
    login_count INT4 NOT NULL,
    rating FLOAT8 NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
  )
  SQL

Benchmark.ips do |x|
  time = Time.utc
  x.report do
    pg.exec <<-SQL, UUID.v7, "User Name", 42, rand, time
      INSERT INTO temp_data (id, name, login_count, rating, created_at)
      VALUES ($1, $2, $3, $4, $5)
      SQL
  end
end

at_exit { pg.exec "DROP TABLE IF EXISTS temp_data" }

On the master branch:

➜  crystal-pg git:(master) crystal run --release bench/encoding.cr
  30.88k ( 32.38µs) (± 1.64%)  992B/op  fastest

With this PR:

➜  crystal-pg git:(use-binary-encoding) crystal run --release bench/encoding.cr
  31.65k ( 31.59µs) (± 2.15%)  560B/op  fastest

Fixes #294

jgaskins avatar Dec 01 '24 01:12 jgaskins