> I am inclined to advise folks to use PL/V8 on Postgres, because it is > a reasonable language, everyone knows it, it has good string functions, > decent performance and it tends to be installed everywhere (in particular, > Amazon RDF offers it).
I'd be careful with "everyone knows JavaScript" - that may hold in web development, but there are many cases where Javascript is completely off the map. And for V8 itself - it adds quite a chunk of code to your PostgreSQL installation, that may put some people off. At least, I haven't seen it installed "generally" in the wild, but my view might be as biased as anyone else's.
> Broadly, what advice should I offer that isn’t obvious? Not just about > PL/V8 but server side code in general.
Initially, running code in your database can make life easier for the developers (ise pgTap for testing, pl/profiler and pl/debugger, etc.). But once you have to change your schema, the hurt begins: you'll need downtime for that, or you'll have to deal with the possibility of changing the API of your "database side" code, and matching code and tables on the database. There have been talks about that topic (using versioned schemas and leveraging search_path), but still: a simple change to a function suddenly requires a lot of care.
you are forgot on reduction of network cost - when some task generates lot of fast queries, then main bottleneck is a network. Stored procedures removes this bottleneck.
PLpgSQL shares data formats and process with PostgreSQL database engine - there are not data conversations, there are not network/protocols/API overhead, there are not interprocess communication overhead.
The next pain point is scalability: running code on the database server puts your code on the most expensive and hardest to scale CPUs. You can (almost) always add another appserver to your setup (just spin up a VM with a tomcat or whatever-you-use). But if the bottleneck is your database CPUs, you'd have to move to a larger server (that got easier with VMs, within limits); or use replication to offload some code to standbys, keeping writing code on the primary (and hope you'll have enough horsepower there). Multi-Master introduces some special limitations and operational overhead on it's own, I'd not generally recommend that for all applications and developers just moving up from the "dump data bucket" model.
TL;DR: database side code can be a great thing in a small application, but once the application and traffic grows, "code in the database" requires specialist attention and may become a burden. Unfortunately, most large applications started small...
When you use stored procedures, you have to choose well the border - what should be done by server, what should be done by outside. Usually stored procedures should be glue of SQL - and then the overhead of stored procedures is near to zero. Surely, stupid ORM techniques has terrible impact on server side.
