Re: Performance PLV8 vs PLPGSQL

I am not doubting the efficacy of stored procs, just wondering which
language is better. From the sound of it string manupilation is slow in
PL-PGSQL but looking at my procs there does seem to be a lot of string
manipulation going on so maybe I better do some tests.

On Thu, Dec 29, 2016 at 3:02 AM, Mike Sofen <msofen(at)runbox(dot)com> wrote:

> *From:* Tim Uckun
> I have seen various links on the internet which indicate that PLV8 is
> significantly faster than PL-PGSQL sometimes an order of magnitude faster.
>
>
>
> Is there any benefit to choosing PL-PGSQL?
>
> ------------------------
>
> I can’t speak to PLV8. However, I can speak to plpgsql, and specifically
> stored functions (procs). I use it exclusively to create a database API
> for real-time web applications to hit. My API calls (procs) are hitting
> large tables, sometimes doing complex logic within the sproc. It allows me
> to provide a simple, standardized interface to the web devs, allowing them
> to focus on the app code work.
>
>
>
> Performance is superb and continues to surprise me (I came from the SQL
> Server world). As others have mentioned, the natural lashup of plpgsql to
> postgres (I liked Alban’s term, “impedance”), is a key aspect. Also:
>
>
>
> - stored procs provide another security layer against sql
> injection attacks.
>
> - Caching SEEMS to be more efficient/effective with stored procs
> (that could be wishful thinking too).
>
> - Stored procs allow skilled sql practitioners to provide far more
> sophisticated sql solutions than the typical python developer is capable
> of…my experience is that most web devs don’t really understand databases
> (or even care about them – they are a necessary evil), so providing a pure
> encapsulated sql solution (via stored procs) removes that mental impedance
> mismatch.
>
> - Performance? Simple “get” procs that return data for a specific
> indexed query against larger tables (50m+ rows) in a few milliseconds…I can
> live with that kind of performance.
>
> - I’m also doing some heavy lifting in the sql, calculating
> histograms and boxplots for data visualizations. This is an unusual
> scenario, but the other option is sending a massive chunk of data to
> another server for processing – just the transit time would kill the deal.
> I am mindful that at a certain point, there won’t be enough memory and i/o
> to go around, but the web app is a low user count/high user task complexity
> app, so I’ve tailored the model to match.
>
>
>
> Mike Sofen (Synthetic Genomics)
>