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.
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:
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.
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....)
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.
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
"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.
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