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