Slashdot Log In
PostgreSQL 7.4 Released
Posted by
michael
on Tue Nov 18, 2003 01:57 PM
from the vacation's-over dept.
from the vacation's-over dept.
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."
This discussion has been archived.
No new comments can be posted.
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
Rock on! (Score:5, Interesting)
One thing that should be noted is that the JDBC drivers (http://jdbc.postgresql.org [postgresql.org]) are now among the best I've used. For those developing Java apps, the choice is now even more clear.
Re:Rock on! (Score:5, Interesting)
MySQL Gotcha's [sql-info.de]
If they still don't seriously consider at least switching away from MySQL, I would be very scared.
Parent
Re:Rock on! (Score:5, Insightful)
Trading stability for speed is stupid.
Period. End of story. The job of a RDBMS is not "be fast".
Maybe then, they need to stop claiming they're building a relational database management system [mysql.com] because they're obviously not and anyone who thinks they are is addled in the head.
If the application layer has to handle data integrity, the system behind it isn't relational and it's arguably not even doing the job of a DMBS. More like a convenient indexing tool.
MySQL makes a fine database management system where it doesn't matter if your data gets mangled and all you want to do is fast, simple SELECTs, but what irritates the "zealots" like me is that MySQL folks will actually sit and argue that MySQL is even remotely close to a RDBMS. pgsql and their ilk aren't truly relational either.. but they're a heck of a lot closer.
Parent
Re:Rock on! (Score:3, Informative)
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 mai
Impressive but... (Score:3, Interesting)
Oh, raw disk use would be nice too.
Re:Impressive but... (Score:5, Informative)
One of the new features in 7.4 is the replication technology contributed from eServer.
Parent
Re:Impressive but... (Score:3)
"New
I can't seem to find it any place else? The reason I went with MySQL over PostgreSQL was the desire/need for easy replication. From what I know, PostgreSQL has some third party tools, but I wanted something that was apart of PostgreSQL itself, and this would be nice to see.
Re:Impressive but... (Score:4, Insightful)
ERServer was released open source months ago. Check out GBorg [postgresql.org] for more information.
And why do YOU need raw disk access? The PostgreSQL developers belive (and rightly I think) that the operating system can do the caching better than they can. Why re-write the wheel? Operating systems have came a long way in the last 10 or 12 years. Days of slow access to the disk are long gone.
BWP
Parent
Re:Impressive but... (Score:3, Insightful)
Yes I have, and it's not all that hard to set up. Not a simple drool and click interface, but no harder than setting up the ftsearch or a few other projects I've put online
And actually, I'd rather have the database decide what gets cached and what doesn't.
Really? Even if it makes the wrong decision? What if the decisions it made would be exactly the same, is it still worth the 100s of man hours to create such a cache ma
Re:Impressive but... (Score:3, Informative)
Looking for replication? Use the eRServer replication that comes with PostgreSQL now.
Yummy, or about time. (Score:5, Informative)
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?
Re:Yummy, or about time. (Score:3, Interesting)
They needed to change the protocol to support such nifty features as proper error codes (SQLSTATE I belive) and better transaction control. I'm hoping that we'll see multi-stage transactions in 7.5.
Older clients can still talk to newer servers, they just won't support the new features.
Question: discuss among yourselves (Score:4, Interesting)
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?
Re:Question: discuss among yourselves (Score:3, Insightful)
The Monks [gracenote.com] said it all (about Notes): Nice Legs, Shame About Her Face.
Autovacuum (Score:3, Interesting)
Simon.
Re:Autovacuum (Score:5, Informative)
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.
Parent
Re:Autovacuum (Score:4, Informative)
Not true. Starting in 7.3, the default version of VACUUM no longer locks the table. From the 7.3 docs [postgresql.org]:
Parent
Not really what I meant... (Score:3, Interesting)
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 understand
Re:Not really what I meant... (Score:3, Interesting)
Normal VACUUM commands do not lock tables as of 7.3. Only the full vacuum command does this, which you probably only need to use when permanently retiring a client, rather than just rotating out their data, as the lock is used to actually repack the database
Windows (Score:5, Interesting)
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?
Re:Windows (Score:5, Informative)
http://www.commandprompt.com/entry.lxp?lxpe=295 [commandprompt.com]
dbExperts PostgreSQL for Windows, Mac OS X, Linux
http://www.dbexperts.net/postgresql [dbexperts.net]
PowerGres (threaded Windows PostgreSQL
http://osb.sra.co.jp/PowerGres/introduction-en.ph
Parent
Re:Windows (Score:5, Informative)
Parent
Re:Windows (Score:5, Informative)
http://techdocs.postgresql.org/guides/Installin
Parent
Re:Windows (Score:3, Informative)
Performance.
Why use PostgreSQL over MySQL? (Score:5, Interesting)
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" [sql-info.de]
Mysql speed (Score:3, Informative)
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.
Re:Why use PostgreSQL over MySQL? (Score:3, Interesting)
You're right. The fact that it will silently accept bad data, and change it to something else, means it's unreliable.
Full text searching improved and other goodness (Score:5, Informative)
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!)
Re:Full text searching improved and other goodness (Score:3, Informative)
The big issues now are safety. For example, sync() has traditionally been used for checkpoints to ensure everything is on disk. Windows does not seem to have an equivelant (though several close options).
If your interested in helping out, please send a note to pgsql-hackers for a list of current issues for win32.
Re:Full text searching improved and other goodness (Score:3, Informative)
Several. [postgresql.org] Take your pick. (Check the administrative link off the nav, and also look through the developer tools.)
I understand that PostgreSQL requires cygwin to run on a Windows platform. Since my company is a Windows shop for now (I have not yet been able to convince them to move to Linux) this is an important issue for me. How big of a performance hit is there for running through the cygwin interface? In other words, is Postgre
Re:Full text searching improved and other goodness (Score:3, Informative)
Traditionally, this has been the job of third party developers. SQL Server is the one exception as it tries to make you dependent on the GUI tools. Still, if you want a GUI tool, both pgAdmin [postgresql.org] and my own DataDino [datadino.com] are good alternatives.
Re:Enough of the anti-MySQL garbage (Score:5, Informative)
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....)
Parent
Re:Enough of the anti-MySQL garbage (Score:3, Insightful)
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 plan
Replication Replication (Score:4, Insightful)
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).
Re:Replication Replication (Score:3, Insightful)
Strange. That's how MySQL looks to me...
Best New Feature (Score:5, Informative)
The new autovacuum tool in "contrib/autovacuum" monitors the database statistics tables for "INSERT"/"UPDATE"/"DELETE" activity and automatically vacuums tables when needed.
Did anyone notice today that.... (Score:3, Informative)
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!
PostgresQL is slow... (Score:4, Insightful)
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.
Re:This could be good... (Score:5, Insightful)
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.
Parent
Re:This could be good... (Score:4, Funny)
This is also why Microsoft WANTS there to be an enemy
Umm.. In the enterprise db market there is a little company called Oracle...
Parent
Re:This could be good... (Score:5, Interesting)
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;
Parent
There is no such thing as "faster" (Score:4, Insightful)
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).
Parent
Re:This could be good... (Score:4, Informative)
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
Parent
Re:This could be good... (Score:3, Interesting)
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,
Re:postgres isn't used in the enterprise (Score:3, Informative)
Re:postgres isn't used in the enterprise (Score:4, Interesting)
http://www-106.ibm.com/developerworks/db2/libra
Parent
Re:PgAdmin 3 (Score:4, Insightful)
The postgreSQL community is extremely helpful, key developers are very active in helping out users and addressing issues rapidly. It is a project that just exemplifies what is good about open source.
I am compiling 7.4 on my development server right now in preparation for moving our production server soon. I guess maybe I sound like a fan boy but as a database administrator I just can't over emphasize my joy at getting to work with such an excellent product.
Parent
No flames... (Score:5, Interesting)
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.
Parent
Re:Ok, so which is faster *now* (Score:3, Insightful)
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
Re:Never used PostgreSQL (Score:3, Informative)
No, stored proceedures are in Alpha. Sub queries have been in production for a year.