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)
I use PostgreSQL extensively, and I have had a hard time convincing my-mySQL (I'm so clever) exclusive friends to give it a try.
One thing that should be noted is that the JDBC drivers (http://jdbc.postgresql.org) are now among the best I've used. For those developing Java apps, the choice is now even more clear.
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
If you ask me, I'm glad that people opine and wage war when it comes to PostgreSQL vs. MySQL. MySQL has done for PostgreSQL what windows has done for Linux: make it want to thrive, compete and prosper.
This is also why Microsoft WANTS there to be an enemy: they need someone to compete against to continue improving their product (which they do, even if we hate to admit it).
If you don't believe me, ask Dubya.
There are two kinds of people in the world: Those with good memory.
This is also why Microsoft WANTS there to be an enemy
Umm.. In the enterprise db market there is a little company called Oracle...
Trolling is a art,
I'm sure I'm gonna get modded down for this, but does anyone know when is there gonna be a version that can run in windows natively (without using Cywin)?
I ask because we are FORCED to use Windows boxes at work, and they gave all of the developers 2. We can't reformat and put linux on (or do a dual-boot) because they check to make sure everything is status-quo. And right now the atmosphere around here is not the greatest, so I'd rather not risk anything with the PHB's by trying to trick them.
I usually have my 2nd machine as a server running mySQL as a testbed for my database apps. I'd LOVE to switch to Postgresql, but I'm limited as to what I can do.
Any idea when a Windows native version will be available?
Because they care about your data, among other things! You could have the fastest database server in the world, but if you find your data is corrupt, or truncated without warning, it doesn't do you much good.
Here is huge list of MySQL Gotcha's that absolutely floored me when I first read it. In my opinion, a "gotcha" in regards to a database is a "Bad Thing(tm)"
MySQL Gotchas"
Open Source Time and Attendance, Job Costing a
I'm using Postgres in a company that analyzes statistics, and maintain a ~250GB db. Trust me, when you're talking about doing a seq. scan on 250GB of data, the preformance differences are MUCH more than minor. The reports of speed increases don't cite minor increases either, they cite really major changes.
Taken from the presskit:
PERFORMANCE
Several major performance enhancements have been added in version 7.4, enabling PostgreSQL to match or exceed the speed of other enterprise database systems. These include:
* Hash aggregation in memory to make data warehousing and OLAP queries up to 20 times faster;
* Improvements in subquery handling by the planner resulting in up to 400% speed increases in some complex queries;
* New script to set more reasonable postgresql.conf defaults for shared buffers, yielding better "out of the box" performance;
* New wire protocol (version 3) increases the speed of data transfers;
* Enhanced implementation of functional indexes allows better indexing on custom data types and composite fields;
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.
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.
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.
We have one client who uses Linux, the rest are all Windows-based... Is there an unbiased (as far as can be) comparison ?
Now, see your problem?
Neither PosgreSQL or mySQL are full, complete, and utterly perfect implemtations of a database. Neither is Oracle, BTW.
mySQL got a HUGE push some time ago. Back then, mySQL couldn't be beat for handling read-only (Actually, highly read-almost exclusively always). mySQL was a champ when you had a web site, mostly static catalog of products (for example), and had really limited demand for SQL (Like one query that read 'select * from catalog;')
That basis of comparison is no longer true.
So, at the time, hords of little corporate minions lined up and specified mySQL. Not a bad bet at the time, but open mindedness only seems to happen once in computer circles. Day 1 you have a need, day 2 you actually research available solutions, and day 3 you declare a "winner" and it is forevermore cast in stone as the "one true solution". The fact masses of people tend to go thorugh the same process at basically the same time doesn't help. Thus the broad noise that mySQL is "the Answer(tm)".
Anyway, postreSQL has always sought to compete in the full function space. Oracle was/is a much better "comparison" to postgreSQL than mySQL.
Now, both mySQL and postreSQL have improved over time - greatly. postgreSQL seems to be focused on getting things "correct", while mySQL doesn't seem so concerned. Bascially postgreSQL will not provide a feature, while mySQL will hacking it together in some bizzare way (re: early "transaction" handling). mySQL has quite a few anti-social behaviors. Over time, their refinement of those various behaviors drive certain development costs and create some degree of lock-in dependency (a continuing basis for self-justification).
Bottom line, if you invested in learning and implementing mySQL, and it is still working for you, then there is absolurely no need to be concerned with postgreSQL yet.
If you are in the database selection mode, you should surely look towards postgreSQL and try to de-hype yourself from any pro-mySQL bias. Hype has inertia and much of the pro-mySQL hype is based on old comparisons and narrow needs. Yes, evaluate both, but don't assume mySQL or postgreSQL is "better" based on what you hear.
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.