PostgreSQL

Using stored procedures for complex query patterns makes sense, but I don't have an intuition for the kind of query complexity necessary before seeing tangible benefits switching from prepared statements. 

Glad someone is experimenting!

Using stored procedures for complex query patterns makes sense, but I don't have an intuition for the kind of query complexity necessary before seeing tangible benefits switching from prepared statements. 

Glad someone is experimenting! https://github.com/pg-nano/pg-nano/

Building @warpcast and @farcaster. Previously Coinbase, Brigade, Causes.

What are the benefits of using stored procedures? I feel like there's a lot of observability loss from moving logic out of server code and into stored procedures, so I'm curious as to know what the benefits are to justify that con.

* sponsored by Supercast, 25,000 $ANON at a time
* higher rank in Tae Kwon Do than Vladimir Putin
* hobbies include finding new lows of dad jokes

At a high level, my understanding is it allows you to execute code “closer” to your data. You get:

- Query plan caching (database wide, not per connection like prepared statements)
- Sessions don’t “pin” to a specific connection (unlike prepared statements), so it can work well with tools like PgBouncer
- Fewer round trips (assuming your logic can’t be expressed as a single SQL statement, e.g. entirely different subqueries are executed depending on a condition)

Similar reasons you might opt to use server-side Lua scripts with Redis.

But yes, observability tooling isn’t as mature. You could write your routines in JavaScript/Rust/etc, but you lose query plan caching, which is a significant.