Slashdot Mirror
PostgreSQL 7.4 Released
Christopher Kings-Lynne writes "PostgreSQL 7.4 has just been released. The list of new features is impressive and includes greatly improved OLAP performance among many other speed improvements."
← Back to Stories (view on slashdot.org)
IN/NOT IN subqueries are now much more efficient.
Queries using the explicit JOIN syntax are now better optimized.
New multikey hash join capability.
Cursors conform more closely to the SQL standard.
Sounds like they pushed closer to the SQL standards, good job guys.
Does anyone here know more about this "New client-to-server protocol" they speak of?
TruePunk | Games
Full text searching also got another overhaul- I plan on messing around with it when I get some free time. They've included a .sql file you can just import into an existing DB.
The real power here is that the index is quick to update, and as a result, can be done in real-time via triggers and stored procedures- neither of which you can do with MySQL :-) The new release is also even more SQL compliant- something else MySQL can't claim. PostgreSQL is both SQL92+98 compliant if I recall.
It can't be said enough- PostgreSQL is now MUCH faster...and due to features like stored procedures, triggers, and some of the best locking available combined with some of the best transaction support, it's actually far faster at many of the same tasks if you take advantage of these greater abilities.
Even back as early as '99, PostgreSQL absolutely mopped the floor with MySQL when as little as 10% inserts or updates were thrown into a select test. Why? Piss-poor locking and zero transaction support. The stuff you have to do in the application layer to make up for proper(or ANY) transaction support will make most benchmarks completely pointless.
MySQL always has, and always will be, a DB best suited for blogs and 2-guys-in-a-garage; it's slapped together, has a low featureset, and is not standard-compliant. PostgreSQL is not an enterprise fish(replication still needs work if I understand it correctly)- Oracle, DB2 etc have that market pretty well covered- but it's great for everyone else who isn't, say, a multibillion $ company...if those people just bothered to have an open mind instead of pointing their fingers at benchmarks showing MySQL running out of an in-ram-only table can select 50,000 rows faster than PostgreSQL can, and whining about how they need to make a cron job to vacuum/vacuum analyze tables at an appropriate time(with autovacuum, also in this release, there goes that excuse!)
Please help metamoderate.
Ellison purchased his version of SQL off of IBM in the late 1970's or early 1980's for about $150K. Oracle has no ties to PostgreSQL.
And here's a link to the native Windows 7.2.1 release. I've tried it on NT4 and 2000. It seems to works just fine.
g On Windows
http://techdocs.postgresql.org/guides/Installin
ON DELETE CASCADE
New autovacuum tool
The new autovacuum tool in "contrib/autovacuum" monitors the database statistics tables for "INSERT"/"UPDATE"/"DELETE" activity and automatically vacuums tables when needed.
Not true. Starting in 7.3, the default version of VACUUM no longer locks the table. From the 7.3 docs:
That's true if you're doing a VACUUM FULL. But that is now something that generally needs to be done only rarely (this will depend greatly on how you use the database). The database will now re-use freed tuples (VACUUM FULL compacts the physical table, thus eliminating such free space entirely and causing new data to be appended to the end of the physical table), so all that's usually needed is to find such tuples and mark them as being free.
The autovacuum process invokes the standard VACUUM (without FULL) which does the "lightweight" VACUUM processing. It will also automatically update the statistics associated with the tables so that the planner will make better choices about things like whether to do a full-table scan or an index scan, the type of join to use, etc.
VACUUM is much easier on the database than it used to be. It still isn't optimal, but if you read the pgsql-hackers mailing list you'll find that the developers are discussing at length how best to minimize VACUUM's effect on the database performance.
Use 'slashdot stuff' in the subject line in any email you send me if you want to get past the spam filter.
still no native replication.
One of the new features in 7.4 is the replication technology contributed from eServer.
I use Friend/Foe + mod-point modifiers as a karma/reputation system.
See eRServer on GBorg
Jan Wieck is also just in the process of re-writing replication based on the experiences of eRServer, again, as an OSS project, and it can be viewed at:
Slony-1 on GBorg
I beg to disagree on the JDBC claim.
As long as Statement.setFetchSize() or Statement.setFetchDirection() are not supported, it's close to useless to me.
Try going over a couple of milion records if you doubt it's really needed.
Not that MySQL's driver is any better in this regard.
Real JDBC drivers like the ones for Oracle, DB2 and (gasp) even SQL Server support these hints without a problem.
Matt
News about the Kettle Open Source project: on my blog
Performance.
Now before I get modded down, I be to remind whoever might read this that what I am saying is FACT. - bogaboga
MaxDB is a sort of merger between sapdb and mysql. As of today, MaxDB includes features such as:
* Views * Server-side cursors * Stored procedures and triggers * Automatic failover (to a standby server) * Scheduling and automatic messaging on alerts * Snapshots * Archive tables * Synonyms
And these are features in addition to mysql's feature set!
Check it out guys! http://www.mysql.com/products/maxdb/index.html
But don't be in too much of a rush to upgrade, mysql interoperability is slated for Q1 of 2004.
Enjoy!
Sure, row-level locking is nice -- even MSSQL has that. PostgreSQL has MVCC - so that writers never block readers and likewise. Complete data consistency (i.e. repeated reads give the same results) from the start of a transaction to the end of it. Can MySQL do that? (I am actually asking....)
What? You didn't know MySQL ran via cygwin?
- C:\mysql\bin\cygwinb19.dll
MySQL just has a better installation process is all.BTW, I run cygwin on my WinXPPro laptop (the only way I'll get UNIX-like OS features on my Dell Inspiron 5150 sadly) along with KDE3. Yep and uh-huh.
-- @rjamestaylor on Ello
MySQL has raw disk usage thanks to InnoDB. The only draw back is that it's slower than using regular InnoDB files in an actual filesystem... So, MySQL clearly doesn't have any advantage over PostgreSQL since it has raw disk support.
Looking for replication? Use the eRServer replication that comes with PostgreSQL now.
Gabriel Ricard
Ok, there is a lot of talk about vacuum what it does / doesn't do and what effect autovacuum has. Here are the details (FYI, I wrote pg_autovacuum).
Recent versions of postgresql don't take your database offline during vacuum. However, the vacuum process is an I/O intense process and can still, even 7.4, slow the server significantly while it's running. Work is has alredy been done in the 7.5 development tree to address the I/O storm created by vacuum.
Typically, you setup cron to run vacuum your entire database nightly. This is fine, except it has two main problems. 1) It wastes time vacuuming large tables that probably don't need it (think audit train table that only gets inserted into). 2) It probably doesn't vacuum tables that are constantly updated often enough, which results in bloated data files, and slower queries.
The new pg_autovacuum daemon addresses both of these concerns by monitoring database activity (using the stats system). When it sees that a table has has been modified enough to warrant a vacuum then it does so, when it sees that a table might benifit from a analyze only, then it does that. And when a large table doesnt' need to be vacuumed, it doesn't vacuum.
Cursors are supported now. Nic Ferrier wrote a patch for this back in April, and I think it got applied in the beginning of May. There's a trick I can't remember right now to enable it (maybe setting the result set type/scrollability first with JDBC2 methods), since it's not quite as efficient for smaller queries. I'm sure the people on the pgsql-jdbc mailing list would help you if you can't figure it out.
Keep in mind that the JDBC drivers shipping with any given version of PostgreSQL are likely not the best available drivers to use with that version. The JDBC people don't make changes to the release branches, unless they started recently. The best available drivers tend to be the ones from their website or in CVS HEAD.
Also, if you stumble on a page listing the compliance features/misfeatures of PostgreSQL, it's hopelessly out of date. I hope they're not still linking to it. The picture is much brighter than what that page suggests.
In the PostgreSQL FAQ, PostgreSQL is pronounced Post-Gres-Q-L.
If Tyranny and Oppression come to this land,
it will be in the guise of fighting a foreign enemy. -James Madison
See http://momjian.postgresql.org/main/writings/pgsql/ win32.html
with the latest update on this issue.
CommandPrompt Mammoth PostgreSQL for Win32, Mac OS X, Linux
p
http://www.commandprompt.com/entry.lxp?lxpe=295
dbExperts PostgreSQL for Windows, Mac OS X, Linux
http://www.dbexperts.net/postgresql
PowerGres (threaded Windows PostgreSQL
http://osb.sra.co.jp/PowerGres/introduction-en.ph
Gabriel Ricard
Originally it was hoped that 7.4 would have a native win32 port. In fact much work has been done, but it didn't get done in time for 7.4. Work continues, and it's getting closer. Everyone working on it hopes that it will be included in the next version of PostgreSQL, but no one will promise. Such is life in an Open Source project.
"Create native Win32 port" is one of three "urgent" items on the PostgreSQL to-do list, and Bruce Momjian publishes a detailed status report on the ongoing work. No one on earth can tell you when the work will be complete.
No, stored proceedures are in Alpha. Sub queries have been in production for a year.
Yes, MySQL is small, light and fast and I use it as my general light duty DBMS, but I'm not religous about it. When the going gets tough I switch to something tougher.
Looks, it's not because you can't do things with MySQL. It's how you have to go about doing them. That lightness and speed comes at a price, it's an engineering tradeoff. There's no such thing as a free lunch and all that.
What it gives up is intergrety constraints. If you don't spend the cycles to insure data integrity you can be smaller and faster.
So let me ask you, how fast do you want your data munged?
If you don't want your data munged at all and you're using MySQL you need to pass off integrity issues to your app. Well, there you are using cycles again. The DBMS is faster, but now your app is slower (yes, you're still saving a bit of disk access time, which can add up. That's a flaw in SQL itself. There are alternatives.). More importantly you're using your time as a developer to reinvent the integrity constraint wheel in every app. Coding time goes up. Bugs go up. Support issues go up. All to accomplish something that is a logical function of the DBMS. That's why we call them a DBMS in the first place. It has been argued that MySQL doesn't even meet the definition of a DBMS.
Once I had data
My DBMS munged it
But damn it was fast!
Again, don't get me wrong, I use MySQL, but I use it in full knowledge of what it does and does not do and what it does not do is guaruntee the consistency and integrity of my data.
And I have better things to do with my time than recoding DBMS functions into my apps. I use MySQL where data integrity isn't a critical issue.
KFG
WITH RECURSIVE is under development, and we may have it for the next version.
See contrib/tablefunc in your PostgreSQL source for an implementation of CONNECT BY as a Postgresql function. We've had this for a year.
-Josh
Yes, if you run simple queries in a single user scenario you do get better performance with mysql than with pg. With more complex queries and more users however, the simplistic query optimizer and concurrency manager that mysql has makes it perform worse than pg.
The Raven
This is just not true. If you were unwilling or unable to tune your DBMS, you can hardly go blaming PostgreSQL!
All you need to do is reduce your random_page_cost a bit, set your effective_cache to an appropriate value and bob's your uncle.
PostgreSQL can do optimisations that MySQL can only dream of. The reasons most of those posts are complaining about the optimiser is, let's face it, the users in question don't know about indexes. Another whole lot of them are about the slowness of IN () queries - which is now hundreds of times faster in PostgreSQL 7.4.
The deal with outer joins in PostgreSQL is that they are executed in the order you specify in your query. This means that you simply just have to have a couple of tries with it to make sure you haven't written a totally degenerate query.
There is discussion on allowing the optimiser to re-arrange outer joins, however it is a rather difficult problem theoretically to be able to prove that a certain rearrangement will still be an equivalent query.
Chris
Speedwise, PosgreSQL trails the pack by a fair bit. Sometimes it would be comparible to Oracle, and other times it wouldn't be without a fair bit of tuning. Outer-joins, for example; the optimizer can't seem to make heads or tails of it.
The 7.4 release is much better on explicit join optimization (the formerly behavior of using explicit joins as optimizer hints has been disabled by default). So using outer-joins should be improved. PostgreSQL is advancing.
In my experience the PostgreSQL user mailing list pgsql-general@postgresql.org is one of the most friendly mailing lists I subscribe to.
You get answers to trivial questions as well as very complex ones. I have not seen anyone flamed for asking something. People are very helpful. I have seen a couple of cases where problems affective live databases were sorted in the mailing list threads within hours.
I do not understand what do you mean by "very unfriendly")
(I use postgresql for our application)
yAthum UrE yAvarum kELir All the places are our place, everybody is our kin. (A Tamil Poet - 2000 years ago)