Re: Performance PLV8 vs PLPGSQL

2016-12-29 9:23 GMT+01:00 Tim Uckun <timuckun(at)gmail(dot)com>:

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

It is interesting, what string operations you are doing in stored
procedures?

Regards

Pavel

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