Обсуждение: [GENERAL] Performance PLV8 vs PLPGSQL
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 this uniformly true or is it just in certain circumstances?
Is there any benefit to choosing PL-PGSQL?
Is there work going on to make PL-PGSQL more performant or has it gotten significantly faster in the last two years or so (some of the links are a from a while ago).
Thanks.
Hi
2016-12-28 10:15 GMT+01:00 Tim Uckun <timuckun@gmail.com>:
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 this uniformly true or is it just in certain circumstances?
It depends on usage
Is there any benefit to choosing PL-PGSQL?
there are more benefits
0. this language has integrated SQL - the code with lot of SQL is more readable.
1. it is everywhere, where is Postgres
2. it uses same data types like Postgres, there is not any conversion related overhead
3. this engine has quick start, faster than anything else.
Is there work going on to make PL-PGSQL more performant or has it gotten significantly faster in the last two years or so (some of the links are a from a while ago).
What I know no. There is not any reason why to do it. This language is designed be glue of SQL statements. Nothing more, nothing less. I did tests, and it is significantly faster than SQL engine.
Some years ago I though about compilation to C language, but the target was better obfuscation not performance.
PLpgSQL is not designed for hard calculation - the critical path is in SQL always. If you need different performance, than you can use PLV8, PLPython or native C extension. Postgres has very good API for writing C extensions.
Regards
Pavel
Thanks.
2016-12-28 10:46 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:
Hi2016-12-28 10:15 GMT+01:00 Tim Uckun <timuckun@gmail.com>: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 this uniformly true or is it just in certain circumstances?It depends on usageIs there any benefit to choosing PL-PGSQL?there are more benefits0. this language has integrated SQL - the code with lot of SQL is more readable.1. it is everywhere, where is Postgres2. it uses same data types like Postgres, there is not any conversion related overhead3. this engine has quick start, faster than anything else.
4. It is not slow language - it is AST interpret - it is significantly faster than byte code based interpreter without JIT. The expression are slower - the PostgerSQL expression interpret. This is bottle neck of PLpgSQL performance - on second hand - it is main benefit of PLpgSQL - there is full integration with PostgreSQL runtime without any exceptions.
Years ago PLpgSQL has very slow operations on arrays - this was fixed in 9.5. Still PLpgSQL has slow string updates - Strings are immutable in PLpgSQL - so update means generating new string. But usually it is not bottleneck in PL environment.
Regards
Pavel
Is there work going on to make PL-PGSQL more performant or has it gotten significantly faster in the last two years or so (some of the links are a from a while ago).What I know no. There is not any reason why to do it. This language is designed be glue of SQL statements. Nothing more, nothing less. I did tests, and it is significantly faster than SQL engine.Some years ago I though about compilation to C language, but the target was better obfuscation not performance.PLpgSQL is not designed for hard calculation - the critical path is in SQL always. If you need different performance, than you can use PLV8, PLPython or native C extension. Postgres has very good API for writing C extensions.RegardsPavelThanks.
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)
On Wednesday, December 28, 2016 6:02:51 AM EST Mike Sofen wrote: > the natural lashup of plpgsql to postgres (I liked Alban’s term, > “impedance”), is a key aspect. Not to deprive Alban of any of his credit, but the term "impedance mismatch" is at least 25 year old; as far as I know it was coined to describe the problems arising from attempting to shoehorn an OO model onto a relational database. And despite the smart people in academia warning us about that mismatch in the early 90s, we bravely soldiered (I'm taking full blame myself here) on and 10-15 years later came up with abominations like Hibernate... History lesson over, carry on...
On Wed, Dec 28, 2016 at 5:53 PM, Jan de Visser <jan@de-visser.net> wrote: > .....but the term "impedance mismatch" > is at least 25 year old; Much older, I was told it in class at least 32 years ago. > as far as I know it was coined _Borrowed_ from electrical engineering / communication techs. It is used to highlight how signals 'bounce' at the points of a transmision path where impedances do not match. It extrapolates the fact that if you have a battery with an internal resistance R the way to extract the maximum energy on a load is for it to match the impedance, be R too. Higher load impedance and the fraction of energy in the load goes up, the total down. Lower load impedance and the fraction in the load goes down, the total up. In either case absolute power in the load goes down. Match the impedance and the energy in the load is the maximum ( and equal to the internal loss in the battery ). The term has been used in radio texts since the dawn of ( radio ) times. It's used a lot as a similar problem appears when mixing to different technology, each time you cross the barrier you loose something, or hit a problem. > And despite the smart people in academia warning us about that mismatch in the > early 90s, we bravely soldiered (I'm taking full blame myself here) on and > 10-15 years later came up with abominations like Hibernate... > History lesson over, carry on... I think that goes together with "everyone can be a programmer" and "every Java ( a language with several apparent concessions made to people who did not even understand objects, like String.format, and targeted to enable "everyone" to do OO ) coder can do databases". Well, rant mode off. Today is "dia de los Inocentes", spanish version of April Fools I was tempted to write something about different impedances in the copper tracks used for DB data traffic when entering the CPU silicon interconnects via golden cables. Francisco Olarte.
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@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)
My recommendation. See them as tools in a toolkit, not a question of what is best.
For places where you have SQL statements as primary do SQL or PLPGSQL functions.
For places where you are manipulating values (parsing strings for example) use something else (I usually use pl/perl for string manipulation but ymmv).
For places where you are manipulating values (parsing strings for example) use something else (I usually use pl/perl for string manipulation but ymmv).
PLPGSQL works best where you have a large query and some procedurally supporting logic. It becomes a lot less usable, performant, and maintainable the further you get away from that.
So there is no best just different tools in a toolkit.
So there is no best just different tools in a toolkit.
2016-12-29 9:23 GMT+01:00 Tim Uckun <timuckun@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 <msofen@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)
I think it's awesome that postgres allows you to code in different languages like this. It really is a unique development environment and one that is overlooked as a development platform. It would be nice if more languages were delivered in the default package especially lua, V8 and mruby.
On Thu, Dec 29, 2016 at 9:31 PM, Chris Travers <chris.travers@gmail.com> wrote:
My recommendation. See them as tools in a toolkit, not a question of what is best.For places where you have SQL statements as primary do SQL or PLPGSQL functions.
For places where you are manipulating values (parsing strings for example) use something else (I usually use pl/perl for string manipulation but ymmv).PLPGSQL works best where you have a large query and some procedurally supporting logic. It becomes a lot less usable, performant, and maintainable the further you get away from that.
So there is no best just different tools in a toolkit.
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.stehule@gmail.com> wrote:
2016-12-29 9:23 GMT+01:00 Tim Uckun <timuckun@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?RegardsPavelOn Thu, Dec 29, 2016 at 3:02 AM, Mike Sofen <msofen@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)
2016-12-29 10:04 GMT+01:00 Tim Uckun <timuckun@gmail.com>:
Mostly generating SQL statements to execute. Like for example deciding which partition to insert into.
Then you don't find any possible performance difference - the query is about 10-100x slower than expression - so the plpgsql should be good.
More you can use a "format" function - implemented in C.
Regards
Pavel
On Thu, Dec 29, 2016 at 10:00 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:2016-12-29 9:23 GMT+01:00 Tim Uckun <timuckun@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?RegardsPavelOn Thu, Dec 29, 2016 at 3:02 AM, Mike Sofen <msofen@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)
2016-12-29 10:03 GMT+01:00 Tim Uckun <timuckun@gmail.com>:
I think it's awesome that postgres allows you to code in different languages like this. It really is a unique development environment and one that is overlooked as a development platform. It would be nice if more languages were delivered in the default package especially lua, V8 and mruby.
It is about dependencies and maintenance. There are not too much people who has good experience with C embedding Lua, V8 and others. Any people who can do some work are welcome.
The living outside main package has disadvantages - only enthusiast knows about it, but some advantages too - you are not fixed on PostgreSQL development cycle, and development can be faster.
Regards
Pavel
On Thu, Dec 29, 2016 at 9:31 PM, Chris Travers <chris.travers@gmail.com> wrote:My recommendation. See them as tools in a toolkit, not a question of what is best.For places where you have SQL statements as primary do SQL or PLPGSQL functions.
For places where you are manipulating values (parsing strings for example) use something else (I usually use pl/perl for string manipulation but ymmv).PLPGSQL works best where you have a large query and some procedurally supporting logic. It becomes a lot less usable, performant, and maintainable the further you get away from that.
So there is no best just different tools in a toolkit.
On 12/29/2016 10:35 AM, Pavel Stehule wrote: > 2016-12-29 10:03 GMT+01:00 Tim Uckun <timuckun@gmail.com > <mailto:timuckun@gmail.com>>: > > I think it's awesome that postgres allows you to code in different > languages like this. It really is a unique development environment > and one that is overlooked as a development platform. It would be > nice if more languages were delivered in the default package > especially lua, V8 and mruby. > > > It is about dependencies and maintenance. There are not too much people > who has good experience with C embedding Lua, V8 and others. Any people > who can do some work are welcome. > > The living outside main package has disadvantages - only enthusiast > knows about it, but some advantages too - you are not fixed on > PostgreSQL development cycle, and development can be faster. I'll add my 2 cents. Postgresql and in general SQL are about integrity and coherency. Checking coherency is much easier with strict data type. PL/PGSQL gives you that, JS is far far away from that. Postgresql is a very flexible database and you can stretch it to do "MEAN like"[1] stuff but that's going to increase your "impedance mismatch". If you think there is some space for JS in your application stack that's nearer to the client rather than to the DB. Or possibly you need to do "MEAN like" stuff but you don't want to install another "database". As other said using stored procedures is a two edged sword. It can decouple DB schema from the application or it can increase the coupling. Choosing JS for performance in the stored procedure realm is going to encourage coupling and make scalability harder and it is going to become a mess when you'll need to refactor. [1] https://en.wikipedia.org/wiki/MEAN_(software_bundle) -- Ivan Sergio Borgonovo http://www.webthatworks.it http://www.borgonovo.net
On 29/12/16 09:12, Francisco Olarte wrote: > On Wed, Dec 28, 2016 at 5:53 PM, Jan de Visser <jan@de-visser.net> wrote: >> .....but the term "impedance mismatch" >> is at least 25 year old; > Much older, I was told it in class at least 32 years ago. > >> as far as I know it was coined > _Borrowed_ from electrical engineering / communication techs. > > It is used to highlight how signals 'bounce' at the points of a > transmision path where impedances do not match. It extrapolates the > fact that if you have a battery with an internal resistance R the way > to extract the maximum energy on a load is for it to match the > impedance, be R too. Higher load impedance and the fraction of energy > in the load goes up, the total down. Lower load impedance and the > fraction in the load goes down, the total up. In either case absolute > power in the load goes down. Match the impedance and the energy in the > load is the maximum ( and equal to the internal loss in the battery ). [...] From my distant memory of studying AC stuff at University many moons ago... You want the two impedances to be complex conjugates of each other (this means the MAGNITUDES will be equal) - which means the phase change should be equal & opposite, and the resistance to match. Cheers, Gavin
Honestly I don't even like JS. Having said that I am not too crazy about PL-PGSQL either. I am willing to put up with either given that they are supported widely in default installs of postgres in AWS, Linux and MacOSX,
As I said before, I think posgres gives a unique and underutilized language platform. You can code in different languages, it has a good variety of built in types, and of course you get persistance and caching built in! Using DBLINK you might even be able to separate out your code from the bulk of your data in another database. Postgres all the way down!
It's fun to play around with. There is a lot of missing pieces though. A good IDE like thing would be good, version control would be nice, deeper namespacing (hierarchical schemas?), easier testing etc would go a long way.
Thanks for all the input guys!
On Fri, Dec 30, 2016 at 12:14 AM, Ivan Sergio Borgonovo <mail@webthatworks.it> wrote:
On 12/29/2016 10:35 AM, Pavel Stehule wrote:2016-12-29 10:03 GMT+01:00 Tim Uckun <timuckun@gmail.com
<mailto:timuckun@gmail.com>>:
I think it's awesome that postgres allows you to code in different
languages like this. It really is a unique development environment
and one that is overlooked as a development platform. It would be
nice if more languages were delivered in the default package
especially lua, V8 and mruby.
It is about dependencies and maintenance. There are not too much people
who has good experience with C embedding Lua, V8 and others. Any people
who can do some work are welcome.
The living outside main package has disadvantages - only enthusiast
knows about it, but some advantages too - you are not fixed on
PostgreSQL development cycle, and development can be faster.
I'll add my 2 cents.
Postgresql and in general SQL are about integrity and coherency.
Checking coherency is much easier with strict data type.
PL/PGSQL gives you that, JS is far far away from that.
Postgresql is a very flexible database and you can stretch it to do "MEAN like"[1] stuff but that's going to increase your "impedance mismatch".
If you think there is some space for JS in your application stack that's nearer to the client rather than to the DB.
Or possibly you need to do "MEAN like" stuff but you don't want to install another "database".
As other said using stored procedures is a two edged sword.
It can decouple DB schema from the application or it can increase the coupling.
Choosing JS for performance in the stored procedure realm is going to encourage coupling and make scalability harder and it is going to become a mess when you'll need to refactor.
[1] https://en.wikipedia.org/wiki/MEAN_(software_bundle)
--
Ivan Sergio Borgonovo
http://www.webthatworks.it http://www.borgonovo.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I would hope Postgres core folk take no more than a nanosecond to reject the idea that they work on an IDE. Focus on reading and writing faster and faster ACID all the while.
Honestly I don't even like JS. Having said that I am not too crazy about PL-PGSQL either. I am willing to put up with either given that they are supported widely in default installs of postgres in AWS, Linux and MacOSX,As I said before, I think posgres gives a unique and underutilized language platform. You can code in different languages, it has a good variety of built in types, and of course you get persistance and caching built in! Using DBLINK you might even be able to separate out your code from the bulk of your data in another database. Postgres all the way down!It's fun to play around with. There is a lot of missing pieces though. A good IDE like thing would be good, version control would be nice, deeper namespacing (hierarchical schemas?), easier testing etc would go a long way.Thanks for all the input guys!On Fri, Dec 30, 2016 at 12:14 AM, Ivan Sergio Borgonovo <mail@webthatworks.it> wrote:On 12/29/2016 10:35 AM, Pavel Stehule wrote:2016-12-29 10:03 GMT+01:00 Tim Uckun <timuckun@gmail.com
<mailto:timuckun@gmail.com>>:
I think it's awesome that postgres allows you to code in different
languages like this. It really is a unique development environment
and one that is overlooked as a development platform. It would be
nice if more languages were delivered in the default package
especially lua, V8 and mruby.
It is about dependencies and maintenance. There are not too much people
who has good experience with C embedding Lua, V8 and others. Any people
who can do some work are welcome.
The living outside main package has disadvantages - only enthusiast
knows about it, but some advantages too - you are not fixed on
PostgreSQL development cycle, and development can be faster.
I'll add my 2 cents.
Postgresql and in general SQL are about integrity and coherency.
Checking coherency is much easier with strict data type.
PL/PGSQL gives you that, JS is far far away from that.
Postgresql is a very flexible database and you can stretch it to do "MEAN like"[1] stuff but that's going to increase your "impedance mismatch".
If you think there is some space for JS in your application stack that's nearer to the client rather than to the DB.
Or possibly you need to do "MEAN like" stuff but you don't want to install another "database".
As other said using stored procedures is a two edged sword.
It can decouple DB schema from the application or it can increase the coupling.
Choosing JS for performance in the stored procedure realm is going to encourage coupling and make scalability harder and it is going to become a mess when you'll need to refactor.
[1] https://en.wikipedia.org/wiki/MEAN_(software_bundle)
--
Ivan Sergio Borgonovo
http://www.webthatworks.it http://www.borgonovo.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I am not saying the postgres core people should work on an IDE, just that an IDE like thing would be nice.
On Fri, Dec 30, 2016 at 12:51 PM, Rob Sargent <robjsargent@gmail.com> wrote:
I would hope Postgres core folk take no more than a nanosecond to reject the idea that they work on an IDE. Focus on reading and writing faster and faster ACID all the while.Honestly I don't even like JS. Having said that I am not too crazy about PL-PGSQL either. I am willing to put up with either given that they are supported widely in default installs of postgres in AWS, Linux and MacOSX,As I said before, I think posgres gives a unique and underutilized language platform. You can code in different languages, it has a good variety of built in types, and of course you get persistance and caching built in! Using DBLINK you might even be able to separate out your code from the bulk of your data in another database. Postgres all the way down!It's fun to play around with. There is a lot of missing pieces though. A good IDE like thing would be good, version control would be nice, deeper namespacing (hierarchical schemas?), easier testing etc would go a long way.Thanks for all the input guys!On Fri, Dec 30, 2016 at 12:14 AM, Ivan Sergio Borgonovo <mail@webthatworks.it> wrote:On 12/29/2016 10:35 AM, Pavel Stehule wrote:2016-12-29 10:03 GMT+01:00 Tim Uckun <timuckun@gmail.com
<mailto:timuckun@gmail.com>>:
I think it's awesome that postgres allows you to code in different
languages like this. It really is a unique development environment
and one that is overlooked as a development platform. It would be
nice if more languages were delivered in the default package
especially lua, V8 and mruby.
It is about dependencies and maintenance. There are not too much people
who has good experience with C embedding Lua, V8 and others. Any people
who can do some work are welcome.
The living outside main package has disadvantages - only enthusiast
knows about it, but some advantages too - you are not fixed on
PostgreSQL development cycle, and development can be faster.
I'll add my 2 cents.
Postgresql and in general SQL are about integrity and coherency.
Checking coherency is much easier with strict data type.
PL/PGSQL gives you that, JS is far far away from that.
Postgresql is a very flexible database and you can stretch it to do "MEAN like"[1] stuff but that's going to increase your "impedance mismatch".
If you think there is some space for JS in your application stack that's nearer to the client rather than to the DB.
Or possibly you need to do "MEAN like" stuff but you don't want to install another "database".
As other said using stored procedures is a two edged sword.
It can decouple DB schema from the application or it can increase the coupling.
Choosing JS for performance in the stored procedure realm is going to encourage coupling and make scalability harder and it is going to become a mess when you'll need to refactor.
[1] https://en.wikipedia.org/wiki/MEAN_(software_bundle)
--
Ivan Sergio Borgonovo
http://www.webthatworks.it http://www.borgonovo.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
If you want an IDE, Jetbrains, the makers of great IDEs like IntelliJ, PyCharm. and AppCode, among others, have recently come out with what is arguably the BEST IDE for DBAs, DataGrip. It runs on most major platforms, and is so good that I have bitten the bullet and paid the yearly subscription for it.
Leave the Postgres core alone focus on what they do best, and that is making the very BEST database environment that can be had at any price, period. Is Postgres perfect? No, not at all. But no other group is so focused on performance, scalability and security as these folks are. And the rate of development, enhancement and continual improvement is, quite honestly, astounding.
So here is my hat tip to the Postgres team for an awesome job they are doing!
On Dec 29, 2016, at 7:19 PM, Tim Uckun <timuckun@gmail.com> wrote:I am not saying the postgres core people should work on an IDE, just that an IDE like thing would be nice.On Fri, Dec 30, 2016 at 12:51 PM, Rob Sargent <robjsargent@gmail.com> wrote:I would hope Postgres core folk take no more than a nanosecond to reject the idea that they work on an IDE. Focus on reading and writing faster and faster ACID all the while.Honestly I don't even like JS. Having said that I am not too crazy about PL-PGSQL either. I am willing to put up with either given that they are supported widely in default installs of postgres in AWS, Linux and MacOSX,As I said before, I think posgres gives a unique and underutilized language platform. You can code in different languages, it has a good variety of built in types, and of course you get persistance and caching built in! Using DBLINK you might even be able to separate out your code from the bulk of your data in another database. Postgres all the way down!It's fun to play around with. There is a lot of missing pieces though. A good IDE like thing would be good, version control would be nice, deeper namespacing (hierarchical schemas?), easier testing etc would go a long way.Thanks for all the input guys!On Fri, Dec 30, 2016 at 12:14 AM, Ivan Sergio Borgonovo <mail@webthatworks.it> wrote:On 12/29/2016 10:35 AM, Pavel Stehule wrote:2016-12-29 10:03 GMT+01:00 Tim Uckun <timuckun@gmail.com
<mailto:timuckun@gmail.com>>:
I think it's awesome that postgres allows you to code in different
languages like this. It really is a unique development environment
and one that is overlooked as a development platform. It would be
nice if more languages were delivered in the default package
especially lua, V8 and mruby.
It is about dependencies and maintenance. There are not too much people
who has good experience with C embedding Lua, V8 and others. Any people
who can do some work are welcome.
The living outside main package has disadvantages - only enthusiast
knows about it, but some advantages too - you are not fixed on
PostgreSQL development cycle, and development can be faster.
I'll add my 2 cents.
Postgresql and in general SQL are about integrity and coherency.
Checking coherency is much easier with strict data type.
PL/PGSQL gives you that, JS is far far away from that.
Postgresql is a very flexible database and you can stretch it to do "MEAN like"[1] stuff but that's going to increase your "impedance mismatch".
If you think there is some space for JS in your application stack that's nearer to the client rather than to the DB.
Or possibly you need to do "MEAN like" stuff but you don't want to install another "database".
As other said using stored procedures is a two edged sword.
It can decouple DB schema from the application or it can increase the coupling.
Choosing JS for performance in the stored procedure realm is going to encourage coupling and make scalability harder and it is going to become a mess when you'll need to refactor.
[1] https://en.wikipedia.org/wiki/MEAN_(software_bundle)
--
Ivan Sergio Borgonovo
http://www.webthatworks.it http://www.borgonovo.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I have datagrip and it's OK but it doesn't really do everything I want.
I don't understand why it doesn't fetch all objects from the database and then put them into the disk in a directory so I can put it all under git and then let me work on them syncing the files back as they change. For example today I just renamed a function. It didn't refactor properly by identifying stored procs that reference it. If I was using another jetbrains IDE it would have built an index of the project files and did a proper refactor.
This would also allow you to make wholesale disk changes and then sync them up properly to get around postgres dependency issues.
On Fri, Dec 30, 2016 at 1:40 PM, Michael Sheaver <msheaver@me.com> wrote:
If you want an IDE, Jetbrains, the makers of great IDEs like IntelliJ, PyCharm. and AppCode, among others, have recently come out with what is arguably the BEST IDE for DBAs, DataGrip. It runs on most major platforms, and is so good that I have bitten the bullet and paid the yearly subscription for it.Leave the Postgres core alone focus on what they do best, and that is making the very BEST database environment that can be had at any price, period. Is Postgres perfect? No, not at all. But no other group is so focused on performance, scalability and security as these folks are. And the rate of development, enhancement and continual improvement is, quite honestly, astounding.So here is my hat tip to the Postgres team for an awesome job they are doing!On Dec 29, 2016, at 7:19 PM, Tim Uckun <timuckun@gmail.com> wrote:I am not saying the postgres core people should work on an IDE, just that an IDE like thing would be nice.On Fri, Dec 30, 2016 at 12:51 PM, Rob Sargent <robjsargent@gmail.com> wrote:I would hope Postgres core folk take no more than a nanosecond to reject the idea that they work on an IDE. Focus on reading and writing faster and faster ACID all the while.Honestly I don't even like JS. Having said that I am not too crazy about PL-PGSQL either. I am willing to put up with either given that they are supported widely in default installs of postgres in AWS, Linux and MacOSX,As I said before, I think posgres gives a unique and underutilized language platform. You can code in different languages, it has a good variety of built in types, and of course you get persistance and caching built in! Using DBLINK you might even be able to separate out your code from the bulk of your data in another database. Postgres all the way down!It's fun to play around with. There is a lot of missing pieces though. A good IDE like thing would be good, version control would be nice, deeper namespacing (hierarchical schemas?), easier testing etc would go a long way.Thanks for all the input guys!On Fri, Dec 30, 2016 at 12:14 AM, Ivan Sergio Borgonovo <mail@webthatworks.it> wrote:On 12/29/2016 10:35 AM, Pavel Stehule wrote:2016-12-29 10:03 GMT+01:00 Tim Uckun <timuckun@gmail.com
<mailto:timuckun@gmail.com>>:
I think it's awesome that postgres allows you to code in different
languages like this. It really is a unique development environment
and one that is overlooked as a development platform. It would be
nice if more languages were delivered in the default package
especially lua, V8 and mruby.
It is about dependencies and maintenance. There are not too much people
who has good experience with C embedding Lua, V8 and others. Any people
who can do some work are welcome.
The living outside main package has disadvantages - only enthusiast
knows about it, but some advantages too - you are not fixed on
PostgreSQL development cycle, and development can be faster.
I'll add my 2 cents.
Postgresql and in general SQL are about integrity and coherency.
Checking coherency is much easier with strict data type.
PL/PGSQL gives you that, JS is far far away from that.
Postgresql is a very flexible database and you can stretch it to do "MEAN like"[1] stuff but that's going to increase your "impedance mismatch".
If you think there is some space for JS in your application stack that's nearer to the client rather than to the DB.
Or possibly you need to do "MEAN like" stuff but you don't want to install another "database".
As other said using stored procedures is a two edged sword.
It can decouple DB schema from the application or it can increase the coupling.
Choosing JS for performance in the stored procedure realm is going to encourage coupling and make scalability harder and it is going to become a mess when you'll need to refactor.
[1] https://en.wikipedia.org/wiki/MEAN_(software_bundle)
--
Ivan Sergio Borgonovo
http://www.webthatworks.it http://www.borgonovo.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
The reason that you cannot use git for that is that it was never intended for live data and stuff that resides on a database.
That said, I agree with you that all table DDLs and stored procedures should be kept under version control. And in fact I do keep them under VC on my database. How do I do this? I have a directory in which I keep all my SQL scripts, and this directory is a git repo. In this directory/project I keep:
1. For every table that is created on the database, I have an SQL script file with the DDL that creates it
2. For every stored proc that I create, I put it in its own SQL script as well
3. For any (relatively) static lookup tables (i.e. state-region associations, etc.), they are put in a SQL script
This workflow gives me several important benefits, including:
1. Everything needed to recreate the database is kept under version control, including the DDL, stored procedures and lookup tables
2. This script folder does not need to reside on the DB server, can be kept anywhere, even on my local laptop; I pull them up in DataGrip and run them on the server (my scripts are on my laptop)
3. All these scripts are VC'd under git
4. They are easily and quickly pushed to my remote repo on Github and Bitbucket for backup
5. When I need to create a new fresh, empty copy of the database somewhere, I just run these scripts and it is done quickly and easily
One more little trick I have is to use Gitkraken for my git GUI. It. is free, and is absolutely the best git GUI available. The devs made it an absolute joy to use, and I never need to use the command line anymore.
Hope this helps!
On Dec 29, 2016, at 10:43 PM, Tim Uckun <timuckun@gmail.com> wrote:I have datagrip and it's OK but it doesn't really do everything I want.I don't understand why it doesn't fetch all objects from the database and then put them into the disk in a directory so I can put it all under git and then let me work on them syncing the files back as they change. For example today I just renamed a function. It didn't refactor properly by identifying stored procs that reference it. If I was using another jetbrains IDE it would have built an index of the project files and did a proper refactor.This would also allow you to make wholesale disk changes and then sync them up properly to get around postgres dependency issues.On Fri, Dec 30, 2016 at 1:40 PM, Michael Sheaver <msheaver@me.com> wrote:If you want an IDE, Jetbrains, the makers of great IDEs like IntelliJ, PyCharm. and AppCode, among others, have recently come out with what is arguably the BEST IDE for DBAs, DataGrip. It runs on most major platforms, and is so good that I have bitten the bullet and paid the yearly subscription for it.Leave the Postgres core alone focus on what they do best, and that is making the very BEST database environment that can be had at any price, period. Is Postgres perfect? No, not at all. But no other group is so focused on performance, scalability and security as these folks are. And the rate of development, enhancement and continual improvement is, quite honestly, astounding.So here is my hat tip to the Postgres team for an awesome job they are doing!On Dec 29, 2016, at 7:19 PM, Tim Uckun <timuckun@gmail.com> wrote:I am not saying the postgres core people should work on an IDE, just that an IDE like thing would be nice.On Fri, Dec 30, 2016 at 12:51 PM, Rob Sargent <robjsargent@gmail.com> wrote:I would hope Postgres core folk take no more than a nanosecond to reject the idea that they work on an IDE. Focus on reading and writing faster and faster ACID all the while.Honestly I don't even like JS. Having said that I am not too crazy about PL-PGSQL either. I am willing to put up with either given that they are supported widely in default installs of postgres in AWS, Linux and MacOSX,As I said before, I think posgres gives a unique and underutilized language platform. You can code in different languages, it has a good variety of built in types, and of course you get persistance and caching built in! Using DBLINK you might even be able to separate out your code from the bulk of your data in another database. Postgres all the way down!It's fun to play around with. There is a lot of missing pieces though. A good IDE like thing would be good, version control would be nice, deeper namespacing (hierarchical schemas?), easier testing etc would go a long way.Thanks for all the input guys!On Fri, Dec 30, 2016 at 12:14 AM, Ivan Sergio Borgonovo <mail@webthatworks.it> wrote:On 12/29/2016 10:35 AM, Pavel Stehule wrote:2016-12-29 10:03 GMT+01:00 Tim Uckun <timuckun@gmail.com
<mailto:timuckun@gmail.com>>:
I think it's awesome that postgres allows you to code in different
languages like this. It really is a unique development environment
and one that is overlooked as a development platform. It would be
nice if more languages were delivered in the default package
especially lua, V8 and mruby.
It is about dependencies and maintenance. There are not too much people
who has good experience with C embedding Lua, V8 and others. Any people
who can do some work are welcome.
The living outside main package has disadvantages - only enthusiast
knows about it, but some advantages too - you are not fixed on
PostgreSQL development cycle, and development can be faster.
I'll add my 2 cents.
Postgresql and in general SQL are about integrity and coherency.
Checking coherency is much easier with strict data type.
PL/PGSQL gives you that, JS is far far away from that.
Postgresql is a very flexible database and you can stretch it to do "MEAN like"[1] stuff but that's going to increase your "impedance mismatch".
If you think there is some space for JS in your application stack that's nearer to the client rather than to the DB.
Or possibly you need to do "MEAN like" stuff but you don't want to install another "database".
As other said using stored procedures is a two edged sword.
It can decouple DB schema from the application or it can increase the coupling.
Choosing JS for performance in the stored procedure realm is going to encourage coupling and make scalability harder and it is going to become a mess when you'll need to refactor.
[1] https://en.wikipedia.org/wiki/MEAN_(software_bundle)
--
Ivan Sergio Borgonovo
http://www.webthatworks.it http://www.borgonovo.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Yes I use a migrations tool as well. I like ruby so I use either the Sequel migrations or the ActiveRecord migrations depending on the project. That's a great way to work. Having said that I think it might be an interesting thing to do to create the PGAdmin hierarchy of objects on disk. Not the data itself, just the table defs, views, code etc. It might be interesting to see what ctags could do with that. Being able to grep to find something would be useful. A proper IDE could also keep track of dependencies and when you came to deploy it could deploy the children changes first etc.
Might be an interesting way to work, treat your database schema as if it was programming poject.
On Sat, Dec 31, 2016 at 2:32 AM, Michael Sheaver <msheaver@me.com> wrote:
The reason that you cannot use git for that is that it was never intended for live data and stuff that resides on a database.That said, I agree with you that all table DDLs and stored procedures should be kept under version control. And in fact I do keep them under VC on my database. How do I do this? I have a directory in which I keep all my SQL scripts, and this directory is a git repo. In this directory/project I keep:1. For every table that is created on the database, I have an SQL script file with the DDL that creates it2. For every stored proc that I create, I put it in its own SQL script as well3. For any (relatively) static lookup tables (i.e. state-region associations, etc.), they are put in a SQL scriptThis workflow gives me several important benefits, including:1. Everything needed to recreate the database is kept under version control, including the DDL, stored procedures and lookup tables2. This script folder does not need to reside on the DB server, can be kept anywhere, even on my local laptop; I pull them up in DataGrip and run them on the server (my scripts are on my laptop)3. All these scripts are VC'd under git4. They are easily and quickly pushed to my remote repo on Github and Bitbucket for backup5. When I need to create a new fresh, empty copy of the database somewhere, I just run these scripts and it is done quickly and easilyOne more little trick I have is to use Gitkraken for my git GUI. It. is free, and is absolutely the best git GUI available. The devs made it an absolute joy to use, and I never need to use the command line anymore.Hope this helps!On Dec 29, 2016, at 10:43 PM, Tim Uckun <timuckun@gmail.com> wrote:I have datagrip and it's OK but it doesn't really do everything I want.I don't understand why it doesn't fetch all objects from the database and then put them into the disk in a directory so I can put it all under git and then let me work on them syncing the files back as they change. For example today I just renamed a function. It didn't refactor properly by identifying stored procs that reference it. If I was using another jetbrains IDE it would have built an index of the project files and did a proper refactor.This would also allow you to make wholesale disk changes and then sync them up properly to get around postgres dependency issues.On Fri, Dec 30, 2016 at 1:40 PM, Michael Sheaver <msheaver@me.com> wrote:If you want an IDE, Jetbrains, the makers of great IDEs like IntelliJ, PyCharm. and AppCode, among others, have recently come out with what is arguably the BEST IDE for DBAs, DataGrip. It runs on most major platforms, and is so good that I have bitten the bullet and paid the yearly subscription for it.Leave the Postgres core alone focus on what they do best, and that is making the very BEST database environment that can be had at any price, period. Is Postgres perfect? No, not at all. But no other group is so focused on performance, scalability and security as these folks are. And the rate of development, enhancement and continual improvement is, quite honestly, astounding.So here is my hat tip to the Postgres team for an awesome job they are doing!On Dec 29, 2016, at 7:19 PM, Tim Uckun <timuckun@gmail.com> wrote:I am not saying the postgres core people should work on an IDE, just that an IDE like thing would be nice.On Fri, Dec 30, 2016 at 12:51 PM, Rob Sargent <robjsargent@gmail.com> wrote:I would hope Postgres core folk take no more than a nanosecond to reject the idea that they work on an IDE. Focus on reading and writing faster and faster ACID all the while.Honestly I don't even like JS. Having said that I am not too crazy about PL-PGSQL either. I am willing to put up with either given that they are supported widely in default installs of postgres in AWS, Linux and MacOSX,As I said before, I think posgres gives a unique and underutilized language platform. You can code in different languages, it has a good variety of built in types, and of course you get persistance and caching built in! Using DBLINK you might even be able to separate out your code from the bulk of your data in another database. Postgres all the way down!It's fun to play around with. There is a lot of missing pieces though. A good IDE like thing would be good, version control would be nice, deeper namespacing (hierarchical schemas?), easier testing etc would go a long way.Thanks for all the input guys!On Fri, Dec 30, 2016 at 12:14 AM, Ivan Sergio Borgonovo <mail@webthatworks.it> wrote:On 12/29/2016 10:35 AM, Pavel Stehule wrote:2016-12-29 10:03 GMT+01:00 Tim Uckun <timuckun@gmail.com
<mailto:timuckun@gmail.com>>:
I think it's awesome that postgres allows you to code in different
languages like this. It really is a unique development environment
and one that is overlooked as a development platform. It would be
nice if more languages were delivered in the default package
especially lua, V8 and mruby.
It is about dependencies and maintenance. There are not too much people
who has good experience with C embedding Lua, V8 and others. Any people
who can do some work are welcome.
The living outside main package has disadvantages - only enthusiast
knows about it, but some advantages too - you are not fixed on
PostgreSQL development cycle, and development can be faster.
I'll add my 2 cents.
Postgresql and in general SQL are about integrity and coherency.
Checking coherency is much easier with strict data type.
PL/PGSQL gives you that, JS is far far away from that.
Postgresql is a very flexible database and you can stretch it to do "MEAN like"[1] stuff but that's going to increase your "impedance mismatch".
If you think there is some space for JS in your application stack that's nearer to the client rather than to the DB.
Or possibly you need to do "MEAN like" stuff but you don't want to install another "database".
As other said using stored procedures is a two edged sword.
It can decouple DB schema from the application or it can increase the coupling.
Choosing JS for performance in the stored procedure realm is going to encourage coupling and make scalability harder and it is going to become a mess when you'll need to refactor.
[1] https://en.wikipedia.org/wiki/MEAN_(software_bundle)
--
Ivan Sergio Borgonovo
http://www.webthatworks.it http://www.borgonovo.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Dec 28, 2016 at 3:15 AM, Tim Uckun <timuckun@gmail.com> wrote: > 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 this uniformly true or is it just in certain circumstances? > > Is there any benefit to choosing PL-PGSQL? > > Is there work going on to make PL-PGSQL more performant or has it gotten > significantly faster in the last two years or so (some of the links are a > from a while ago). Pavel covered it pretty well but I'll chime in also. In typical usage of pl/pgsql the overhead of the language itself is minimal. Most of the heavy lifting is done by the SQL engine. pl/pgsql is glue that sticks SQL statements together basically. Heavy computation in pl/pgsql can be problematic but is rarely necessary. pl/v8 is definitively a better choice for such cases but not being in core can be an issue for some people. The advantage of pl/pgsql is that SQL statements, types and errors are native to the language itself. This makes many things much easier to do...pl/pgsql is maybe the most efficient data processing language (in terms of efficiency of coding) in existence. That may smack of overstatement but my entire career is built around being good at it so I'm inclined to effusiveness :-D. merlin