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

Comments

This could be good...

[PurdueGraphicsMan]

I wonder if the minor speed improvements will be enough to silence the people that talk about how MySQL is better because "it's faster"? If there was neglibable difference in speed before, shouldn't this new release of Postgres be faster than MySQL?

This might also be a good time for the developers working on phpPgAdmin to create a new version that rivals phpMyAdmin in functionality.

Re:This could be good...

[phlyingpenguin]

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;

Re:This could be good...

[Sxooter]

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.

Ah! Just in time

[hattig]

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?

Rock on!

[JohnA]

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.

Re:Rock on!

[IpSo_]

Show them this:

MySQL Gotcha's

If they still don't seriously consider at least switching away from MySQL, I would be very scared.

Re:Rock on!

[SirChris]

What about FirebirdSQL. In my opinoin, one of the best databases out there, albeit not as well known.

Impressive but...

[slayer99]

....still no native replication. MySQL has this one single advantage over Postgres.
Oh, raw disk use would be nice too.

Question: discuss among yourselves

[Uma Thurman]

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?

Re:Question: discuss among yourselves

[IANAAC]

Not sure why you were modded insightful, but... Care to share why Notes shouldn't be ported? Fact is, the server already runs on Linux. All that would be needed is the client, which is a fair bit better than Outlook. But if you can get away from big clients, I agree that web-based is the way to go.

Autovacuum

[Space cowboy]

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.

Windows

[kannibal_klown]

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?

Re:Yummy, or about time.

[Anonymous Coward]

I helped debug the new client/server protocol along with Anders Johnson. Query results > 4k (eg, retrieving everything at once, or large binary fields) can be compressed with gzip or bzip2. If you run postgresql on a dedicated box, this can make a significant difference in network transport speed.

Why use PostgreSQL over MySQL?

[IpSo_]

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"

Re:Why use PostgreSQL over MySQL?

[schon]

Just because mySQL fails to implement many parts that standard SQL programmers consider important doesn't mean it's unreliable.

You're right. The fact that it will silently accept bad data, and change it to something else, means it's unreliable.

Re:postgres isn't used in the enterprise

[drgroove]

Actually, Sequel was the name for the original Standard English Query language invented by IBM. SQL was the name of the second version of the language, also invented by IBM.

http://www-106.ibm.com/developerworks/db2/librar y/ techarticle/0301jones/0301jones.html

PgAdmin 3

[DAldredge]

PgAdmin 3 is also ready and in now multiplatform.

Re:Full text searching improved and other goodness

[phlyingpenguin]

I hear that. When you're talking about a large database (More than 50k rows, things closer to 400M rows/table) Postgres is the only way to go after you figure out that MySQL has NO functionality in the realm of RDBMS. In fact Postgres is even better when you find all of the different things you can do with your data afterwards (via custom aggregates, custom sequences, functions, triggers, views, etc.) that the dorks in their garages who only 'SELECT * FROM BLOG' are even further pushed out of the picture.

Not really what I meant...

[Space cowboy]

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

Re:Not really what I meant...

[Qzukk]

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.

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 on the filesystem to reduce the filesize.

For your needs the normal vacuum provided by auto-vacuum should be sufficient, as you're going to fill up the empty filespace with the next period's data anyway.

No flames...

[Anonymous Coward]

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.

Re:Yummy, or about time.

[bovinewasteproduct]

Does anyone here know more about this "New client-to-server protocol" they speak of?

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.

plphp

[matchboy]

PHP geeks, don't forget to check out plphp. You can create functions/triggers with php for PostgreSQL.

on the other hand

[kpharmer]

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.

not broken

[kpharmer]

> 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.