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)
For me to migrate my company's systems from MySQL to Postgres. Shame that my nice O'Reilly book won't cover the new features ... but I probably am not so advanced as to need them!
Now how does it compile and run on FreeBSD / x86-64?
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.
....still no native replication. MySQL has this one single advantage over Postgres.
Oh, raw disk use would be nice too.
Martin Brooks / Slayer99 #linux / UIN 2178117
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.
I run postgres on my own database servers (when I'm not making movies, that is). Now, there's a distributed database project associated with Postgres, trying to add replication into the databases' bag of tricks.
Lotus Notes implements e-mail and lots of other things on top of a database engine that performs replication. So, could Postgres be used to develop a Lotus Notes type application with replicated databased for e-mail, calendars, team rooms, etc?
This is America, damnit. Speak Spanish!
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,
because no one can effectively pronounce it.
Is it pronounced "Post Grays"?
"Post Grez?"
"Post Gress?"
"Post Gray?"
"Post Gruh?"
I've also heard people refer to it as "progress"...
Seriously, though, as an IT manager, you can't 'sell' the concept of using an OS RDBMS that you can't even pronounce. People use MySQL because its pronounceable. My Sequel. Simple. Yellow. Different.
Those guys over @ Postgreays need to figure out a better friggin name for their RDBMS if they expect it to be used in the enterprise.
Ok, this is nice - One thing that's always put me off postgres is the need for vacuuming. It seems to me it's a labour-intensive task that the computer ought to do on its own, without my help :-)
Simon.
Physicists get Hadrons!
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;
PgAdmin 3 is also ready and in now multiplatform.
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.
For some applications with a chance of growth I've had two issues with Postgresql. One is that despite the fact that they have talked about being an "enterprise level" database for ages, we found that in any kind of swift moving transaction enviroment we had to VACUUM pretty regularly. How they expected folks to leave pgsql running over extended periods of time (months -> years) is beyond me. Looks like they may have solved it. It will be interesting to see if the systems can take a pounding and stay up 24/7 for a while without slowing to a crawl.
The other issue has been replication. With mysql this has saved our bacon more then once. Nead to do intensive analysis on live data and don't want to disturb active system? Set up a nice slave and query away.
Want basic fault tolerance? Set up a slave, you have a live mirror of the data.
Have lots of queries coming in (load balance the reads at least).
PostgreSQL now has some type of replication available from PostgreSQL Inc, but it looked to me like somewhat of a hodge podge of perl, triggers and who knows what else.
I think I'll try it out, and if I can get the same replication speed as I do with a mysql array I'd switch over, but first glance it didn't look like I would. Anyone compared the replication performance yet (and ease of setup, I was very impressed with mysql in this regard).
I work with both of them, so I can compare both. Personally, I see many cases, especially when the data model is complicated enough, when PostgreSQL is faster than MySQL. But for that I am spending some extra efforts, because many OSS projects are ported to work with one DBMS, not with both.
I love PostgreSQL and it's functionality but unfortunately there are still many developers of other open source projects who heard about MySQL and did not do any research for existing alternatives and thus made his project based on MySQL.
And, again unfortunately, while PostgreSQL is very close to SQL standard, but MySQL is not that close, so you cannot just substitute the database library - you have to re-write (and thus re-test) all SQL code of the project. So, that's why I still have to use MySQL.
With all my respect to great technical quality of PostgreSQL software, I think PostgreSQL team doesn't do a great job to make PostgreSQL being popular. The athmosphere in PostgreSQL community reminds me the one of BSD (read: very unfriendly).
Less is more !
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.
Hmm. re-reading my post, I can see why you replied like you did, so let me spell out the case I have:
There's a DB with (currently) about 30 million rows in it, it's the audit trail for a clients adserver. Queries are run on the db by their clients to figure out their usage, click-throughs etc. over periods. The ad-server gets anything from 0 to ~20 inserts per second across some 200 sites, and depending on the client policy, is cleared daily,weekly,monthly or yearly of that clients data.
Now, my understanding of the vacuum command was that it effectively took the DB offline (not good with the hit-rate I have), and my understanding of 'auto-vacuum' was that it would negate that effective downtime. It appears that that is not the case.
MySQL copes with this quite happily. I was simply investigating whether postgres would cope equally happily... more strings to the bow etc...
Simon
Physicists get Hadrons!
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.
They are tools for different jobs:
MySQL is intended for systems where the data is uploaded, and thereafter never changes significantly - eg static data accessed via the web.
PostgreSQL is intended for things like payroll systems where some values persist for years, while others change daily.
Without triggers, you cannot expect to maintain data integrity with online data input and a wide range of input methods. ie any system with an expected live lifetime exceeding a few months
Sent from my ASR33 using ASCII
Untrue? Since when has MySQL had stored procedures? Do you even know what stored procedures are? Do you run 10 database queries on every web page?
How about subqueries? Do you run a query and then run queries on the results in the application?
Simple selects might be slightly faster in mysql, but very few applications do a single simple select at a time.
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
he is an idiot who doesn't understand "different tools for different tasks"
So a guy I work with was bitching about his Geo Metro the other day - he was complaining (for the umpteenth time) that it takes too long to drive the 300 miles to work each day.
So I say to him (again for the umpteenth time) "Dude, you work across the street from an airstrip, and your driveway is large enough to function as a landing field - get a plane."
So the next day, he tells me that he decided to take my advice and try a plane - a small single-prop Cessna.. then he says that it sucked! It was slower than his Metro!
"How can that be?" I asked.
"Well, every time I got the damn thing over 40, the wheels came off the ground!" he says.
"So? It's a plane, not a car - that's what's supposed to happen." I said.
"That sucks!" He says, "I don't know how to fly a plane! I'm sticking with my Metro - it's faster than that Cessna!"
Then I tell him that the Metro is only faster if you don't know how to fly a plane.
And then he went and muttered something about "different tools for different tasks" - completely missing the point that he's ignoring the correct tool because he's unwilling to learn how to use it.
There's a moral in there - I'll let you figure out how it's applicable to this conversation.
I'm thinking I should give it a while. I hope it's better than MySQL, because, in my opinion, MySQL is overrated.
I've recently started a project using MySQL because we want to get away from the ridiculously overpriced licensing that MS makes you get. I'm not trying to be a troll or anything, but I'm trying to give a realistic viewpoint by someone who's used to MS crap, but really wants to switch over to open source.
I love Open Source and I definintely try to keep an open mind about it (hey, whatever gets the job done at are more efficient and cost-effective manner is good stuff) and am by no means a Windows/Linux zealot.
Take a look at MySQL's current state. Way behind on the times... for example: it *still* doesn't have stored procedures. Do you realize how annoying it is hardcoding SQL statements? There's complete lack of subquerying, which really makes it a pain to do certain calculations often requiring additional queries, which is extremely inefficient (although I do understand that it's currently in alpha).
Things like this.. make me think twice. If these useful features haven't even been implemented yet, then how can I (someone who's used to using MS crap) trust it?
I'm gonna give PostgreSQL a try and hopefull it has more functionality (and stability) than MySQL!
We have secretly replaced these Slashdot mods' sense of humor with a rusty nail. Let's see if they notice!!
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.
PHP geeks, don't forget to check out plphp. You can create functions/triggers with php for PostgreSQL.
Robby Russell
PLANET ARGON
Robby on Rails
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
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
We are in the midst of moving our databases away from Oracle. There were three contenders: MySQL, Postgres, and Matisse (OODBMS).
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.
I spent two years lurking on the Postgres lists, and when doing performance testing, was asking for help tuning queries and the database in general; this isn't a statement made based on, "I tried it once, and it didn't work."
The guys on the list (especially Tom Lane) were very helpful and polite, but I just couldn't get reasonable performance out of the database without doing some serious SQL-rewriting (our CTO thinks that relational databases require too much tweaking already; putting optimizer hints into the queries is just too much).
Overall, the database is great - great feature set, great developers, and a good support community, but the optimizer is not efficient enough (search for the word optimizer in the PostgreSQL lists, and you'll find hundreds of posts where the optimizer is doing a sequential scan and ignoring indexes when it should be using those indexes).
MySQL (4.0.16, using InnoDB tables) has foreign keys, transactions, etc. I haven't been able to crash it yet (I miswrote a query on purpose, and let it run over 2 days at 99% CPU, and the machine stayed up, and is still up a week later).
MySQL doesn't have triggers or stored procs, but as a DBA and senior developer, I can honestly say that's a good thing.
- if you modify a table that a trigger or stored proc uses, chances are the trigger and stored procedure are invalidated quietly behind the scenese - the database doesn't tell you until you call the stored procedure or execute a statement that causes the trigger to be executed.
- debugging a stored procedure or trigger is not easy.
- people tend to forget about triggers and stored procedures; they're hidden logic that can cause no end of problems.
- triggers and stored procedures are (in most cases) database-dependant; they are a huge hinderance when moving to another database. We have 12,000 lines of Oracle stored procedures. I dislike them.
- the database is for data storage. It's not for application develoment. Keep the business logic in the application, and the data-storage logic in the database. Oracle is trying to sell their RDMS as a development tool to justify the price. Don't believe the hype.
PostgreSQL is trying to position themselves as an Oracle replacement, and thus have a similar feature set. PostgreSQL is also very good at very large databases (probably even more so than MySQL, at least until InnoDB gets multiple tablespaces in the next release).
Databases with simple queries where results are not needed instantly would do well with PostgreSQL.
Not exactly. If you try to enter a date of 'grandmas panties' as a date, postgresql will NOT accept it. MySQL will turn it into 0000-00-00.
If you try to insert 8123928392382923 into an int4 in postgresql, it will throw an error. MySQL will turn it into 2^31 (or 32 if it's unsigned).
If you make a not null column in postgresql, it will NOT ACCEPT A NULL, while MySQL will silently convert it to an acceptable default.
MySQL will allow you to define foreign key references to tables that can't support them, and silently fail to create or maintain them.
That's the problem, the silent failures / data munging. If there was a switch that I could throw that would let MySQL run ONLY on innodb tables and ONLY work the right way I might be tempted to use it, but their "what, me worry?" attitude towards data is a bit disheartening.
--- It is not the things we do which we regret the most, but the things which we don't do.
Might not always be the fastest...but I can't imagine developing a database application - and putting all the join code in the app - that could instead be a subselect, inline view, etc.
I'm not a huge fan of triggers - but often find circumstances in which their careful application has saved a huge number of hours.
Stored procedures are another example of a technology that when used wisely can result in a huge improvements to flexibility and adaptability. My favorite use of them is when on a fast-moving project - I can have the developers create an object model and almost immediately start developing to it. My first task is to knock out a set of stubbed stored procedure (or sometimes views) that map to that object model. Often within a week they've got a fully working application (with a fake backend) to continue testing and tweaking. Then I can take my time mapping the object model / stored procedures to a relational model - perhaps using some advanced features that would have been time-consuming to build and test. The net result is drastic reduction in development time, complete dba-control over all queries, and the ability to change the model occasionally (as performance or whatever demands) without any impact to the developers.
The concern that stored procedures aren't portable isn't completely valid either. Stored procs shoudn't be complex - simple ones can be easily ported between most databases.
Views, unions, and subselects are other sql capabilities that the mysql-ab team has frequently referred to as being only useful to 1% of the applications out there. And unlike the triggers and stored procs described above - I'd consider these core database capabilities.
> Anyone who needs to use COUNT() is completely out
of luck. Can't use PgSQL. This problem has not been
> fixed in the latest release:
why would you be doing count(*) on *huge* tables without a where clause anyway? I mean, sure - I run queries like that all the time - in adhocs, where a scan of 100+ million rows might take 10-30 seconds.
But I can't think of the last time I've built an application and needed to do count(*):
- without a where clause
- on a huge table
- and needed fast response time.
Also note that most databases don't seem to store the min/max values for non-index columns to speed up this situation. Informix does, IIRC, but I don't think that Oracle does.
So yeah, it would be nice if this was improved, but really - I use this functionality all the time, and am completely unaffected by this issue.