Search Postgresql Archives

Re: Sun acquires MySQL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I love Postgresql to death, it's one of the shining stars of the Open Source movement IMHO.  It's rock solid, crashes less frequently than Oracle in my experience, and does almost everything I could ask of it (granted - I don't ask much often, just simple things like consistent behaviour, which seems to elude many other products).  My one biggest bone to pick with Postgresql is that stored procedures are not compiled.  It makes writing anything but the most trivial things in plpgsql stupid because it will slow the crap out of your queries. For example: I wrote a simple function to return the distance between two lat longs in plpgsql.  Not only did it choke on values that were part of a valid domain when calling acos() (I have a list of them someplace that I keep meaning to post as it seems like a really bad bug), it was slow.  I re-implemented in C and it was 8-12 times faster, and didn't error out on acos for the same values.  Expecting DBAs to be able to write functions in C IMHO is a bit unrealistic.  I am far from a typical DBA, I've met precious few Oracle DBAs who could write functions in C.  Trying to implement good database code that is atomic and makes good use of functions in Postgresql is an uphill battle because they slow the database down so much.

On Jan 20, 2008 12:04 PM, Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote:
Hello

>
> support for compiling and execution of Procedures in Postgres is nonexistent
> 99% of SQL code in either Oracle and MySQL DB's are written in
> Procedures..trying to port that to Postgres is a very long and tedious
> uphill climb
>

This seems like a fallacious claim as MySQL only introduced procedures in 5.0 and their implementation was incomplete and has been kind of incremental since 5.0 and still isn't complete, whereas plpgsql in Postgresql is a well advanced implementation that works very well (Other than speed).  Given that it is also possible to implement functions in Perl, C, Java and Python, it seems that you can achieve pretty much anything with a function in Postgresql, which is not true in Oracle or MySQL.  Oracle makes extensive use of views and sql files, but not so much stored procs in the core distro if I remember rightly, certainly not 99%.
 


true compilation is necessary only for some cases (note: MySQL stored
procedures are not compiled too ~ PostgreSQL has similar language
plpgpsm  with little bit faster execution
(http://www.pgsql.cz/index.php/SQL/PSM_Manual ). When plpgsql is
potentially slow, you can use  perl or write own custom function in C,
what is simpler than with Oracle.
 
Anyone who is using Perl for something that needs to be fast is seriously misguided.  My benchmarking to date shows that Perl is the slowest of the mainstream second/third gen languages.   Even python is faster and python can be a dog (Having said that, python 3 looks to be about twice as fast though which is quite an improvement)

True compilation is necessary for all cases if you care about scalability, which ultimately everybody does as they will continue to run more and more sites/databases on a set of servers until the CPU/IO limit is reached - it's called business - you maximize resources.  Plpgsql's lack of compilation dramatically lowers that threshold, which means smaller profits for hosters of Postgresql and serious limits on OLTP scalability when plpgsql functions are utilized.  Plus do you really want your hosted people writing functions in C for your Postgresql?  Hell, _I_ don't want to write functions in C for Postgresql much,  plpgsql is much less error prone and much easier to deal with.

If I had to ask the Postgresql people to put one thing on the wish list for the next major release it would be compiled functions, it's the one thing my apps would benefit the most from as I like keeping data integrity, and functions help me achieve that with elegance (I could tell you some stories about data integrity with data feeds from other companies who clearly didn't actually hire a DBA to design their database implementation, every row in it's own transaction because you never know when it's going to violate foreign key constraints, totally sucktastic). (The other thing would be cache management, I know that my system would benefit hugely from being me being able to direct certain tables to remain in memory regardless of MRU data - It seems like something might be possible with RAM disks but how do you sync it back to physical disk in a reliable way so that when your machine dies your data doesn't buy the farm?).
 
Alex

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux