Re: LYDB: What advice about stored procedures and other server side code?

2016-12-28 16:12 GMT+01:00 Christoph Moench-Tegeder <cmt(at)burggraben(dot)net>:

> ## Guyren Howe (guyren(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>