Re: [GENERAL] LYDB: What advice about stored procedures and otherserver side code?

## Guyren Howe (guyren@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.

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...

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