2016-12-28 16:12 GMT+01:00 Christoph Moench-Tegeder <cmt@xxxxxxxxxxxxxx>:
## Guyren Howe (guyren@xxxxxxxxx):
> 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.
regards
Pavel
Regards,
Christoph
--
Spare Space
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
