Yep, close to 100% of my data manipulation is done in pl/pgsql. It’s awesome. At least 50% fewer LOC, and 10-100x faster than the equivalent code written in Java or Go, due to all the round trips.
Oh yeah, I completely forgot to mention that. The performance gains are immense when you use Pl/pgSQL to eliminate round-trips to the database. That's easily one of the most important reasons to use Pl/pgSQL.
The vast majority of data-heavy web apps today must have the database running on the same server or within the same datacenter -- they can't tolerate any kind of latency between the application and the database server because they failed to reduce round-trips. Ever tried deploying MediaWiki on an application server with >20ms latency to the database server? It just doesn't work -- each page takes several seconds to load.
If you minimize round-trips to the database, it gives you more flexibility on how you can deploy/host your database server. That's flexibility you want when you're designing failover/disaster recovery schemes.
Yep, I realised I needed to give plpgsql a shot when I started thinking, from first principles, about all the effort I was wasting. Not just machine cycles - the buffer copying, context switches, network switches, latency - but also, as I was working in Java at the time, there was the immense weight of the ridiculous JPA ORM sitting on top of it all, making it worse. When I took a step back and realised what we had done, the minimalist in me went into cardiac arrest.
With plpgsql you define your schema once, in SQL alone; you don't write a million duplicate "entity objects" in your language of choice, there is no friction or "impedance mismatch", no need to catch network errors for each DB call -- you just write SQL and return values like any other Go function. Because my functions are generally self-contained, I rarely even need to bother with transaction management, which eliminates even more round trips.
It's true that I needed to write some supporting code to manage schema upgrades (one day I hope to open source it), and I'm really intrigued to see if I can use sqlc to create Go stubs for my PG functions. But my SQL code sits next to my Go code in my IDE, it's syntax and correctness checked by the GoLand IDE, and life with an SQL database is super enjoyable!
I'm looking forward to integrating plpgsql_check into my build chain.
