TIL: crystal-pg low performance with SSL

Learning Resources
1

Hi everyone,

I wanted to share a recent experience I had with crystal-pg that might be helpful for others.

I’ve been benchmarking Crystal against Go and noticed that my Crystal implementation was performing 5-10 times slower than the Go version when running the same SQL query.

My initial tests with SQLite showed comparable performance to Go, so the bottleneck was clearly with PostgreSQL. I spent a lot of time tweaking connection parameters (retry_attempts, pool_size, max_connections, etc.) and even tried compiling with -D preview_mt, but nothing seemed to close the significant performance gap.

On a whim, I added sslmode=disable to my connection string, and the results were instantaneous and dramatic. The performance shot up, bringing it nearly on par with the Go implementation.

I haven’t had the chance to dig into why enabling SSL has such a massive performance impact with crystal-pg, but the difference is night and day.

If you’re running into similar performance issues with PostgreSQL, I highly recommend giving this a try. It might just be the solution you’re looking for. I’d be curious to hear if anyone else has encountered this or has any insights into the cause.

1 Like
2

That’s a good find. I wonder, did you try playing with the Crystal version and this to see if there was some change with Crystal that would have affected this?

3

Before trying Go, or diving into code changes I actually played with Crystal versions. Tried 1.14.0 1.16.0 1.17.1 and master. Had no luck, hence I pushed forward with building a reproducible version with Go and narrowed it down to the driver. It wasn’t easy though :rofl:

1 Like
4

It’s not clear to me from your message if Go’s implementation is using SSL or not. Could you clarify so it’s an :red_apple: to :red_apple: comparison?

1 Like
5

That’s a good question, I only used sslmode=disable with Crystal’s connection string. I assume that Go uses SSL by default…will check and verify.

6

Actually some help might be useful, this is the Go Postgresql Driver GitHub - jackc/pgx: PostgreSQL driver and toolkit for Go I did some quick code search and couldn’t see any default for sslmode. Can anyone dig into this?

7

IIUC configTLS function is responsible for configuring TLS options for a given host, and from what I can see, it uses sslmode=prefer by default.

References:

1 Like
8

Regardless of whether the Go comparison module uses TLS: It’s very surprising that toggling TLS makes a significant difference in crystal-pg.
With modern hardware and software, TLS shouldn’t have much overhead over unencrypted traffic.

Your findings suggest that something appears to be not working as performant as it should be expected.

9

Okay, so I did specify sslmode=prefer to make an equal comparison with Go version. Didn’t change anything and was slow.

For the curious I’ve created a Github repository where you can easily run and reproduce the results.

10

Just a note here: sslmode=prefer doesn’t mean it actually does use it.
Maybe find a way to check if it is active or set the thing to something like sslmode=just-do-it-or-elseOr what ever else makes Go fail if ssl is not active.

11

…is kind of interesting - seems they override to other modes. based on certain other properties. Unless I’m reading this wrong, is pgx more of a complete rewrite of libpq as opposed to using libpq - This code appears to be emulating what libpq does, but if it’s not actually using libpq I’m not sure it’s an apples-to-apples comparison.