2016-12-28 16:12 GMT+01:00 Christoph Moench-Tegeder <c...@burggraben.net>:
> ## Guyren Howe (guy...@gmail.com): > > > 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >