Mostly generating SQL statements to execute. Like for example deciding which partition to insert into.
On Thu, Dec 29, 2016 at 10:00 PM, Pavel Stehule <pavel.steh...@gmail.com> wrote: > > > 2016-12-29 9:23 GMT+01:00 Tim Uckun <timuc...@gmail.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 <mso...@runbox.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) >>> >> >> >
