Sun Eyes PostgreSQL

Da Massive writes "Sun is looking seriously into the database market - namely PostgreSQL. It says Oracle and IBM and even Microsoft licensing fees are way too expensive for the average punter. This from John Loiacono, executive vice president of software: "We're not going to OEM Microsoft but we are looking at PostgreSQL right now," he said, adding that over time the database will become integrated into the operating system."

Comments

I doubt it

[FortKnox]

It says Oracle and IBM and even Microsoft licensing fees are way too expensive for the average punter.

An NFL punter usually makes between $250k to $1M a year. They can handle most DB costs...

Re:I doubt it

[Anonymous Coward]

An NFL punter usually makes between $250k to $1M a year. They can handle most DB costs..

No, they can't. That is the problem...

Re:I doubt it

[D'Sphitz]

Well they did say the average punter, not the average NFL punter. Even though NFL punters make a lot of money, all other punters (college, high school, little league) make nothing. There are hundreds of thousands of non-NFL punters vs. a couple hundred NFL punters at the most, so that would effectively bring the average very low, well below poverty levels.

Re:I doubt it

[iBod]

A 'punter' is common British slang for 'your average joe'.

Also used to mean a gambler or a prostitues client!

Re:I doubt it

[Dun Malg]

Does anyone know the usage of the word "punter" in the article, though?

It's a British-ism meaning about the same as "bloke", only it can apply to men or women. Tends to have shades of "lowest common denominator" to it, meaning something like "an ordinary slob off the street picked at random".

Re:I doubt it

[I confirm I'm not a]

It's a British-ism meaning about the same as "bloke", only it can apply to men or women.

I've mostly heard it used/used it myself to describe "customers", particularly gamblers. As in "I had a punt on that nag but lost my shirt". I believe politicians use it to describe their electorate, but I couldn't possibly comment.

Disclaimer: I've only heard the term used in Scotland; it's usage elsewhere in Britain may be more/less general.

Re:I doubt it

[gowen]

It's a British-ism meaning about the same as "bloke"

More usually, it means "someone who is in interested in buying something". It's most frequently used with respect to gamblers (particularly occasional horse-racing gamblers), since "having a punt" means "taking a gamble." It also means "people who frequent prostitutes", thus PunterNet, the leading online guide to "facilitate the exchange of information on prostitution in the UK"

Re:I doubt it

[iabervon]

Punting costs £14-16 per hour, which is a lot less than a database. Of course, I've got no idea what you'd use a database for while poling a boat around the river Cam.

Re:I doubt it

[nelsonal]

In this context punter is a buyer with shades of uninformed buyer. The term comes from the race tracks where betters became known as punters, and has evolved to refer to more uninformed buyers of especially tech and financial products.

Re:I doubt it

[Alejo]

Dude , I have no idea.

Re:I doubt it

[Itchy Rich]

maybe these brits should go back to boiling every piece of food to death, instead of using stupid words on slashdot.

How are we supposed to know which words you understand? Are we psychic? Sure I know a few words like 'pants' that cause confusion, but to expect people to translate for you is just childish.

As for the food 'quip', how about four out of the top ten restaurants in the world being in Britain, compared to two in the USA.

Re:I doubt it

[david duncan scott]

Since you're unfamiliar with the term, you must be unfamiliar with The Register. The BOFH alone is worth the price of admission.

Re:I doubt it

[fanfriggintastic]

That's 3 in America, actually. Two in NY, one in California.

Re:I doubt it

[Guy LeDouche]

Hell, I tend to shit my pants just for the hell of it. Too lazy to get up since there is always something obviously important to finish at the computer, such as this post. There goes another loaf.

Predictable

[AKAImBatman]

This really isn't a surprise. MySQL has both licensing problems, and feature problems in the competitive high-end markets. PostGreSQL has none of these issues, and can hold its own in a comparison with Oracle or SQL Server. These features led RedHat to PostgreSQL for their RedHat Database product, and I see little reason why they wouldn't attract Sun as well.

The only thing that slightly bothers me about their strategy is that Sun has been pushing their Java Systems hard. If they actually wanted to bolster that strategy, they'd have three major options for a Java Enterprise Database:

1. Cloudscape/Derby - This product makes the most sense from a technology and licensing perspective, but the fact that it was an IBM product (even though Cloudscape was originally a separate entity before being acquired) taints the software in such a way as to make Sun look bad if they used it.

2. Daffodil - This database is an excellent choice, but it would require the acquisition of another company, a move that the Sun shareholders might question. It would also bring quite a bit of flak in Sun's direction as Daffodil is an Indian company.

3. McKoi SQL - An excellent choice for a Java database, but lacks brand recognition. The feature levels and scalability of the database are still considerable questions. The GPL license also allows Sun less freedom to modify the database in comparison to the BSD license used by PostgreSQL.

As for the choice of Sunbird, I think it's simply a matter of "why not?" It's not like there's any particular leader in the market, and Sunbird plays nice with Firebird/Mozilla.

Re:Predictable

[MeauxToo]

1. Cloudscape/Derby - This product makes the most sense from a technology and licensing perspective, but the fact that it was an IBM product (even though Cloudscape was originally a separate entity before being acquired) taints the software in such a way as to make Sun look bad if they used it.

Derby is intended to be an embedded database, not a database server. Yes, they have a server mode, but can't hold a candle to MySQL, let alone, PostgreSQL.

3. McKoi SQL - An excellent choice for a Java database, but lacks brand recognition. The feature levels and scalability of the database are still considerable questions. The GPL license also allows Sun less freedom to modify the database in comparison to the BSD license used by PostgreSQL.

Since when can't you modify the source of a product with a BSD-based license? A BSD-based license is, in fact, far more liberal than the GPL because you can take the code, modify it, and close the source of the result. A perfect example is the Windows NT/XP TCP/IP stack -- stolen straight from BSD, and last I checked, Windows is not open source. In contrast to the GPL, where you have distribute any modifications you make and open-source any parts of your products that link to it. Hence, the description of the license as viral.

Speaking from experience, PostgreSQL is a grat product. Stable, reliable, and reasonably fast for medium to large scale, multi-user, distributed environments. The products listed above are all embedded databases intended for single user, micro environments. You are, in short, comparing apples to oranges.

Re:Predictable

[Doctor Memory]

I might also suggest Firebird, the open-source version of Borland's InterBase product. It's licensed under a variant of the Mozilla Public License called the "InterBase Public License", but it doesn't seem too onerous. It's still a young product, but it looks like a good base, and I'm sure with a little spit and polish from Sun it could be a decent system.

There's also PostgreSQL's estranged mother, CA Ingres, the commercial version of Stonebreaker's original University Ingres. This is a well-vetted commercial-grade DBMS, although under another odd-wad license (the "Computer Associates Trusted Open Source License v1.1", see here).

That said, I would prefer to see them choose PostgreSQL.

Re:Predictable

[hey!]

PostGreSQL has none of these issues, and can hold its own in a comparison with Oracle or SQL Server.

Depends, you can't exactly put a product like a RDBMS on a single scale. But in general it makes some sense to compare Postgres to SQL Server, but very little sense to compare either of those products with Oracle; although the limited attention span of most "decision makers" means that in practice the marketing departments of MS and Oracle play that game.

Oracle really really wants people to use Oracle for everything, and in truth you can use it for a lot of day to day database tasks, in the way you could use an eighteen wheeler to take your kids to soccer practice. Oracle's not very standards compatible. There's a million ways it traps you into their product. There's endless ways to shoot yourself in the foot, and getting things back requires a kind of black sorcery. In short, Oracle really sucks, unless it's the only tool that can do the job; in which case it's wonderful. Oracle's built so you can perform heart surgery on the patient while he's running a marathon, for the kind of applications where serious money is lost every time the database hiccups; the kind of applications where you have a team of DBAs who are paid six figures and it's a bargain.

SQL Server, to my mind, is mediocre. It's the choice of the departments who believe thing are easier if everthing comes from one vendor, and it's good enough to keep them out of too much trouble much of the time. From a DBA's standpoint I'd guess it's very easy to administer up to the point it becomes useless; if you never get there, you're happy. From a app develper's standpoint, it's pretty dreadful, but these days the style is to put as much as you can in the app tier so that doesn't matter much as it used to.

Don't know much about Postgres in production environemnts. It seems clean and I like the fact you have a choice of stored procedure languages.

Re:Predictable

[hey hey hey]

There's also PostgreSQL's estranged mother

More like estranged cousin. The commercial version split off from the University version long before PostgresSQL.

Re:Predictable

[david duncan scott]

A perfect example is the Windows NT/XP TCP/IP stack -- stolen straight from BSD

Smile when you say that, pardner!

"Stolen?" No, used legitimately. In fact, as I recall, you used to be able to look at the WinNT ftp client and read the credits to UC Berkely, which aren't even required any more.

"Stolen" just undermines your point that the BSD license allows -- hell, encourages -- this sort of use.

Of course, I think you misread the post to which you were replying, because that poster agreed with you that the GPL includes restrictions absent from BSD.

I'd also check again with regard to XP. I think the Redmond boys may have rewritten that stack by now.

Re:Predictable

[hey!]

what does this even mean?
"very easy to administer up to the point of being useless"?

In the same way a car with no steering wheel is very easy to drive up to the point of you having to make a turn. SQL Server's very easy to administer because it only gives you control over the most rudimentary aspects of the database physical design, e.g. creating B+ tree indexes, moving entire tables between databases etc. If this is the limit of the kind of adminsitration you do, then you really can't find an easier to manage product.

i'm no big fan of Microshaft, but it's the choice of people who want to quickly deploy a reasonably scalable RDBMS that has excellent administration tools and is not super expensive... i'm not sure where the conception came from that's "in your mind".


It comes from making feature to feature comparisons. First, let's be clear: excepting their horrible SQL implemntation, I never said SQL Server is bad -- just mediocre. Mediocre (but good enough), cheap, and easy to convince the boss to use constitute and excellent decision in many instances. Whether it is the best choice for you depends on your definition of "resaonably scalable". The admin tools are very polished I grant you. Within the scope of what they do they can be called "excellent". But as excellent as they may be, if you need to do something that the underlying platform cannot do, it doesn't matter. If don't it does.

integrating into the OS

[jbellis]

I'm not sure what they have in mind here, but if that's the direction they're going it's clear why they wouldn't go with MySQL (technical shortcomings aside). PostgreSQL's BSD license makes it much more attractive for Sun, whose CDDL license is incompatible with the GPL, IIANM.

Re:integrating into the OS

[AKAImBatman]

it's clear why they wouldn't go with MySQL (technical shortcomings aside).

Actually, I'd say that the technical shortcomings have a LOT to do with it. PostgreSQL can be placed head to head with Oracle and still pretty darn appealing. MySQL really don't have that capacity (yet), and is hampered by its non-ANSI comaptible design and SQL variant. So I'm not certain that the decision was made entirely on licensing alone. After all, Sun does support the GNOME project as well, and that is solidly under the GPL.

Re:integrating into the OS

[morgan_greywolf]

After all, Sun does support the GNOME project as well, and that is solidly under the GPL.

Actually most of the GNOME is licensed under the LGPL.

Once again, BSD == good

[tcopeland]

Sun gets to use repackage PostgreSQL however they like, more people will be using PostgreSQL and finding bugs and adding features and writing utilities, more books will be sold, more consulting opportunities - everyone wins.

I've had people contribute code to PMD and say they were only contributing it because they felt the BSD license avoided any possible obligations on their part. And the products that are based on PMD? Just means more books sold. Good times!

Question

[stoolpigeon]

If they take postgres and roll it into the OS- that means the work they do after that wont be coming back to the postgres community? I assume that is the likely course, or am I mistaken?
 
I like the BSD license, and I understand what the ramifications of it are. And I'm not trying to start a debate over whether this is a 'good' thing or not. Just hoping someone here more knowledgable will give some insight on how this is likely to go.

Re:Question

[meringuoid]

If they take postgres and roll it into the OS- that means the work they do after that wont be coming back to the postgres community? I assume that is the likely course, or am I mistaken?

Well, they don't have to give anything back, if postgres is BSD-license. But in practice, they probably will. Not everything, but quite a bit. It's in their interests to give back to the community a lot of the changes they've made, so that the work done on the free version doesn't unnecessarily duplicate the proprietary version, and so that the next release of postgres doesn't force Sun to rewrite half their modifications. Basically, if Sun want to take advantage of progress made by the community on postgres, then they'll be giving back some of their own. They don't want to diverge too far.

Re:Let the PostgreSql vs MySQL Debate Commence

[darylb]

On top of being closer to the standards Oracle uses, IIRC, PostgreSQL uses a transaction model that is essentially identical to Oracle's, even though it's implemented differently. In spite of the hype around database independence, the reality is that the differences in transactional behavior radically affect the ability to port from one database to another. The fact that PostgreSQL's native stored proc language already looks a lot like Oracle's PL/SQL, with an effort to make PostgreSQL run PL/SQL unmodified in the works elsewhere, is another big plus.

Java Enterprise System isn't all in Java...

[MosesJones]

Sun's Java Enterprise System is about programming in Java rather than the tools in Java. The technology of the product isn't hugely important its the fact that the API and development is in Java. Databases are clearly easy with Java as JDBC makes the actual choice a pure commodity. So what Sun want is a solid database, for free, that rounds out their platform effort and means that in one download and license a client can "get started"... which often means it is all they use.

Re:PostgreSQL vs MySQL

[El_Muerte_TDS]

SQL92:
    PostgreSQL > MySQL; but MySQL is improving it's feature set
SQL3:
    PostgreSQL > MySQL; PostgreSQL has a few SQL3 features
Speed:
    PostgreSQL ~= MySQL; sometimes faster, sometimes not
Database\table\row\... Size:
    PostgreSQL > MySQL; PostgreSQL has less size restrictions, or at least, the limits are much larger than those of MySQL
Stored Procedures:
    PostgreSQL > MySQL; MySQL not yet, but in 5 they have SQL:2003 like stored procedures; PostgreSQL has SQL, C, pgSQL, Tcl, Perl, Python and roll-your-own and a few not bundled with PostgreSQL
Installation\maintenance:
    MySQL > PostgreSQL; MySQL is easier to set up
OS Support:
    PostgreSQL ~= MySQL; postgres came a long way, e.g. there's now a stable Windows version.

Re:PostgreSQL vs MySQL

[Cmdr-Absurd]

Yes. They have been compared.
A quite legnthly comparison can be found here.
SQL92 compliant is a relative term.

Re:PostgreSQL vs MySQL

[rindeee]

> PostgreSQL ~= MySQL; postgres came a long way, e.g. there's now a stable Windows version

Yes indeed, now if only there were a stable Windows platfrom on which to run it. ;)

This is more realistic

[Nuttles1]

Unlike all the articles about linux and it's rise as an OS, Open Source databases do not have the same major difficulty. With an OS, every user that uses the computer has to know how to use the system. Conversely, with a database, most, if not all users will not care what database they are using. For example, for my job, I write and maintain a windows application that supports 3 different database back ends. Our clients can care less what database they are using. Only IT and whoever is in charge of the cash will probably care what database is running. In my experiance, IT will not really care what they use because DB issues don't usually take up the bulk of their time. As for whoever is shelling out the money, well that is a toss up, but the trend that I see is that more companies are opting for less expensive DB options.

Again, open source DBs have a chance because not every user works with them directly. Also, the interface, SQL, is a much more standardized interface than with an OS. As a programmmer, writing queries to DB A is pretty darnd exactly like writing queries for DB B. So, I think that their will be much better competition in the database world as in the OS world.

The other half of the article - Sunbird

[dtfinch]

I wonder if it would create any confusion if Sun started marketing Mozilla's Sunbird. It'd be nice to seem some fresh development on that project though.

Firebird anyone?

[MatD]

Anyone know why they wouldn't use http://firebird.sourceforge.net/> ? I've used interbase in the past and I thought it was pretty damn good.

Re:PostgreSQL vs MySQL

[El_Muerte_TDS]

> Installation\maintenance:
> MySQL > PostgreSQL; MySQL is easier to set up

PS, this doesn't hold up on Debian systems:

apt-get install mysql-server
  vs
apt-get install postgresql

the latter is less typing.

Fragmenting might be in their best interests.

[khasim]

Way back at the beginning of the *nix wars, there wasn't any incentive not to share the improvements and such of each version ... but they still fragmented.

The same situation exists here. Sun is not legally bound to release any improvements back to the base, but can legally use any improvements that others provide to the base.

It's in their interests to give back to the community a lot of the changes they've made, so that the work done on the free version doesn't unnecessarily duplicate the proprietary version, and so that the next release of postgres doesn't force Sun to rewrite half their modifications.

That is what fragmentation is. One vendor chooses one path while a different chooses a different path.

Over time, the minor changes and improvements pile on until the two versions are not inter-changable anymore.

Yet each individual change/improvement/fix is insignificant and does not break compatibility.

We've seen this before and it happens again and again. It's always in the company's best interest to support the code base and the community ... yet it always fragments.

Re:Sun? PostgreSQL?

[bernywork]

I do find it interesting that Telstra is a Sun software customer

Telstra are also a big Microsoft customer and also a big Linux user. They use IBM GSA extensively too. What's your point?

I know one guy who worked on an implementation of part of the Telstra Mobile billing system for IBM GSA as Telstra found out that they weren't cathing the milliseconds to seconds in cell switch time and therefore billing users for it.

This just like the comment in the article is just padding. It doesn't really add anything to the post.

IBM has DB2, Microsoft has Microsoft SQL Server, Sun has.... Oracle? No....

I doubt very highly that Sun would buy PostgreSQL Inc, they would partner with them and do some code development of PostgreSQL to get it to the level where it can definately compete head on with Oracle (Although Oracle do have a lot of other software that at present Sun doesn't have) and MS SQL and DB2. The thing they would be best off doing (And probably will do) would be to go out and hire key developers of PostgreSQL to try to prioritize more the requirements that they are after.

Smart path for Sun

[cpu_fusion]

Using Postgresql as a database makes a lot of sense for Sun. Its BSD license makes it easier to use licensing terms that fit Sun's needs, it's desiged for transaction-heavy applications, and it has a solid codebase with a growing community.
      True, it is not written in Java, but neither is Solaris. Sun uses Java pragmatically, as everyone should, and since there are JDBC drivers for Postgresql, it really doesn't need the database written in Java.
      I think it's a smart move, and this news combined with the Google collaboration is giving me hope that Sun's management has suddenly woke up and smelled the coff... er I mean java.

So?

[joib]

I've had people contribute code to PMD and say they were only contributing it because they felt the BSD license avoided any possible obligations on their part.


Just like there's plenty of people who only contribute to GPL projects since they don't want "evil corporations" stealing their code.

You can find fanatics driven by ideology rather than common sense in both camps. That's hardly something to cheer about.

Re:PostgreSQL vs MySQL

[StrawberryFrog]

MySQL > PostgreSQL; MySQL is easier to set up

I don't think this is much of an issue, I recently installed postgreSQL on my Windows XP machine in order to try it out. The installation was 100% simple and painless.

Re:PostgreSQL vs MySQL

[AKAImBatman]

the latter is less typing.

Uh, yeah. I think he was referring to the configuration steps after that to get the server running. I personally find PostgreSQL easier (just edit the security configuration files and initialize a database, whereas MySQL makes you jump through hoops inside the master database), but from a zero to executing perspective MySQL is up faster than PostgreSQL.

Derby

[ievans]

Sun already has several engineers working on Derby through Apache. Sun bundles Derby with Glassfish (the newly open-sourced Java EE 5 app server), which also integrates Derby into the app server for the EJB timer service, and bundles it with the Java Enterprise System stack. Sun is actively promoting Derby as a development database. There was a story about it here on Slashdot not too long ago.

Sun used to bundle Cloudscape before IBM bought Informix, and subsequently switched to Pointbase. For App Server 9/Glassfish, they pulled Pointbase and replaced it with Derby.

Re:Let the PostgreSql vs MySQL Debate Commence

[DrSkwid]

you spelt sucks wrong

punter == mark

[HermanAB]

American slang for 'punter' is 'mark'. A gambler, but more specifically, a loser.

Re:PostgreSQL vs Mysql

[kpharmer]

> Have you conducted tests yourself or are you merely repeating fanboi retoric?
> I've used both views and subqueries with MySQL recently. stored procedures are
> listed for V5.

Views are listed as a new feature in 5 - which is just a development release. So, yes - the original poster was correct on that account.

MySQL picked up sqlqueries in V4.1, though I haven't checked to see how well they implemented them: ie, can you:
    - select (select max(date) from a)
    - select blah from table (select blah,blah,blah from a,b where...)
    - select blah from a where a.blah in (select blah from b)
    - select blah from a where a.blah in (select blah from b where b.foo = a.foo)
And can it perform these subselects without tanking performance? Especially given their poor quality optimizer and notorious performance problems with queries of 5+ tables...

So, given the massive gulf between just doing something and doing it well, and mysql's history of shooting for the bare minimum the actual usefulness of their stored procedures, views, and subqueries will take time to determine.

Re:PostgreSQL vs MySQL

[JohanV]

Installation\maintenance: MySQL > PostgreSQL; MySQL is easier to set up

You might want to check out this lengthy review of the installation of PostgreSQL, MySQL and Oracle on Windows that has a winner that may be a bit surprising to those that have not been keeping tabs on what has been happening recently.

What happened to Clustra?

[teneighty]

Sun gained an excellent database when they acquired Clustra. What happened to it and why are they now talking about Postgres? Are they really that intent on pissing away that investment?

Re:What happened to Clustra?

[rleyton]

I agree - I can't believe Sun are quite that stupid, but I've been wrong on that score before, and they do have a reputation for buying and burying things.

If you'll excuse the shameless link, I've written up my thoughts in more detail here, and in the last /. post on the "SunDB" matter back in Feb, a bit of agreement was to be found on the Clustra theory. My disclaimer is, I suppose, that I've worked with and used Clustra, and live in hope Sun will see the sense of their purchase.

Re:If everyone has to re-write the fix ...

[TheRaven64]

So if Sun fixes a bug, they don't have to release that fix to anyone.

True, on the surface. However, if they don't then the next time someone modifies that bit of code, then they will have to re-merge their changes. If someone else fixes the bug in a different way, they have to do code review on both implementations and then decide what they want to keep.

There is a reason people like Apple contribute to BSD projects - it's cheaper to get your patches merged upstream than to maintain a fork.

Hmmm.

[hey!]

The article seems a bit heavy on posturing and light on details, almost like it's there to get the message across: fear Microsoft because it competes with its customers.

Otherwise, it seems a bit curious to me, because it juxtaposes two things that don't seem to go together in my mind: High end database management and penny pinching. Prices for Oracle on low end hardware (x86 servers) are not high at all, certainly not high enough warrant any concern at all in any project that doesn't get staff and DBA time free. Once you pay for a couple of professional staff the Oracle license fees are not worth worrying about, if they are even a bit more productive. Prices for Oracle on big iron are shocking to people whose idea of a big software procurement is a couple of dozen boxes of MS Office, but in those environments they are likewise not out of place.

Oracle's licensing model is incredibly byzantine. It takes days of study to get your brain around it. Once you do, what's obvious is that it is a reflection of the company itself: it's a complex machine designed to squeeze every last marginal dollar out of the customer. But -- the reason it works is that the prics are very carefully calibrated so you don't really save any money by going to the competitor. For example, if you just grab the biggest license you can on the x86 platform to make your life simpler, you will pay dearly. But if you are selective and understand the model resaonably well, Oracle is about the same or perhaps even cheaper than SQL Server on equivalent machines. Of course if you don't know what you're doing you'll be accidentally sending Oracle beaucoup bucks, like CA did a few years ago. I assume midrange and high end licensing for Oracle are the same: they maximize Oracle's revenue for the specific capabilities you license from them, and it behooves you to choose wisely.

Of course, no pricing model works for everyone. Perhaps there are people on high end hardware who just need something that is very fast and very reliable, not highly configurably fast and as reliable as human ingenuity can make it. Which leads me to a conclusion:

Talking about Postgres in the context of Oracle and DB2 is probably just posturing. It would be years, if ever, before Postgres gets the kind of features that make Oracle a must have for many high end applications. So I'm guessing this is really aimed at delaying the encroachment x86/Windows/SQL Server on the midrange, by giving a big vendor seal of approval to Postgres, which is plenty good for the kinds of apps you run on SQL Server, and quite a bit better if the hardware is better.

Re:Hmmm.

[oGMo]

It would be years, if ever, before Postgres gets the kind of features that make Oracle a must have for many high end applications.

Actually that's not remotely true. We're not talking about MySQL here. PostgreSQL is quickly gaining all the "high-end" features of Oracle: tablespaces, failover, replication, etc. In some cases, they aren't yet as fine-grained as Oracle. In other cases, they're superior. PostgreSQL is quickly coming into its own.

On top of this, it's a lot less painful to work with, and the SQL featureset is far nicer. After having worked with them both on a daily basis, the only reason I'd willingly use Oracle is if I was working with terabytes of data and had lots and lots of money to throw at Oracle to make it work and support it. Which I don't. Like Sun is saying, this is unjustified for most people.

Re:Hmmm.

[hey!]

Actually that's not remotely true.

Remotely true? It depends. High end is a logarithmic scale isn't it? And it's a moving target. I'd say the very idea of "High End" is only vaguely useful. If you need the fine grained control of the physical database Oracle gives you, then PG isn't high end enough. And it doesn't matter if a particular feature exists if it doesn't exist in the form you need it to. On the other hand, if PG does what you need, then you're better off without the cruft.

On top of this, it's a lot less painful to work with, and the SQL featureset is far nicer.

Agreed. As a developer I like PG better.

After having worked with them both on a daily basis, the only reason I'd willingly use Oracle is if I was working with terabytes of data and had lots and lots of money to throw at Oracle to make it work and support it

Which is my point, although it is probably not raw data volume, so much as having substantial data volume and having to manage it in a particular way. For example, I recently met some DBAs for a company that sells GIS data for a great deal of the world -- most of the developed world in any case. Maintaining this data is more complex than you can possibly imagine, unless you've actually seen it being done. On some of their tables, storing, maintianing and updating a single spatial index could be a bigger job than 99% of the complete database applications there are, and that's not even necessarily the main challgenge. Even talking about a single country, or state/province, it's managing the work flow, the locking, the publishing, the versioning of the data that means Postgres' spatial database capabilities aren't even remotely close to what they need.

PG's GIS capabilities are about 90% of the way there for me but I'm a different story.

I don't want to blow too much street cred here, so let me say: I thing Postgres is great and getting better. I find dealing with the Oracle way of doing things like giving myself a deliberate paper cut. I despise Oracle as a company and think Ellison's a vile and pompous blowhard who's probably compensating for anxiety over a shortcoming in some department or other. But I mean that in a nice way.

But, I think the "Postgres Rocks" or the "SQL Server Sucks" way of looking at things is more entertaining than enlightening. SQL Server may be the right choice for a project. The reasons may be political or marketing rather than technical, but so be it. We don't live in a world where the ultimate success of a project is completely isolated from these factors.

I happen to dislike a lot of the same things about Oracle you apparently do. But my technical likes and dislikes aren't of overriding importance, and my political likes and dislikes don't matter at all unless they rise the level of right and wrong. When I point out that Oracle has certain features that PG lacks and will continue to lack for a long time, I'm not saying Oracle is the One True Database and PG is Just a Toy, or that Postgres Isn't Ready for the Enterprise (whatever that means). I'm just saying people pay a lot of money on high end iron for things Oracle can do, that by in large products like PG aren't even close to yet, not because they are stupid (some of them may be).

Re:Hmmm.

[drew]

Talking about Postgres in the context of Oracle and DB2 is probably just posturing. It would be years, if ever, before Postgres gets the kind of features that make Oracle a must have for many high end applications.

For true high end applications this may be true, but for what >90% of Oracle customers actually need, they could switch to PostgreSQL without sacrificing speed, features, or flexibilty, and they could do it while saving not only on Oracle licensing fees but also on the six figure salary Oracle DBA's typically command.

Re:Hmmm.

[kpharmer]

> Actually that's not remotely true. We're not talking about MySQL here.
> PostgreSQL is quickly gaining all the "high-end" features of Oracle:
> tablespaces, failover, replication, etc. In some cases, they aren't yet as
> fine-grained as Oracle. In other cases, they're superior. PostgreSQL is quickly
> coming into its own.

Hmmm, as much as I like postgresql I don't see it that way:

1. replication? it's most often used as a clunky way of implementing failover - yuck. In my large data architectures, replication is almost never used: it's almost always the worst solution to some problem.

2. tablespaces? yep, they're good things to have. that's fine - i think oracle and db2 have supported them for around twenty years, so it's hardly high-end technology tho.

3. failover? ok, this is critical - but there are also many different forms & flavors. I'm not familiar with what postgresql has so I won't comment - other than to say it needs to be rock-solid.

ok, how about a few more:

4. memory management: a high-end database should give you a ton of control over how memory is handled - especially when you plan to buy tons of it. Here the big databases allow you to assign different amounts of memory to different buffer pools, which are then assigned to different tablespaces. These bufferpools (caches) are how to easily ensure that hits against some tables or indexes occur 99% of the time from memory, and others 50% because they're so much larger. I'm pretty sure that neither postgresql or mysql can do this.

5. process management: in db2 your application writes to a buffer pool, an asychronous agent picks up that change and writes it to a log file, another asynchronous agent picks it up and writes it to the table. This heavily-asychronous behavior (and yes, with memory & processor tuning available for each agent type) allows you to maximize write-throughput. Postgresql and mysql are still in the slower sychronous world.

6. parallelism: in mysql and postgresql all queries are single-threaded. In db2 and oracle a large query will actually split itself up into multiple sub-queries to maximize throughput for multiple cpus and storage arrays. This provides db2 & oracle with linear performance improvements up to 4-8 cpus. In othe words, large queries that perform table scans can take advantage of SMP hardware for the commercial products - and cut down your query time by 75% on a 4-way compared to mysql and postgresql.

7. partitioning: btree indexes only work for very selective queries - like when you want 1% or less of the data of a table. But for many queries you need to crunch 5,10,or 15% of the data. That's where range partitioning comes in: you just scan the data you absolutely need to. So, while db2 or oracle are scanning 10% of the data - postgresql or mysql still have to scan 100% of the data. That would result in a 10x increase in speed over postgresql or mysql.

that's just off the top of my head - given a little time this list would double.

Postgresql is a fine tool, and it has all the technology that db2 or oracle had 12-15 years ago. And that's a cool achievement, and qualifies it do a ton of cool projects. Plus, with time it will catch up. But it still has a *long* way to go.

Re:Hmmm.

[nconway]

Here the big databases allow you to assign different amounts of memory to different buffer pools, which are then assigned to different tablespaces.

Yeah, Postgres doesn't currently support this. IMHO it isn't that useful -- the performance improvement I'd expect would be pretty small (for one thing, all Postgres buffering is done in addition to the kernel's buffering, so the net impact will be smaller). It also adds a significant administrative burden -- you need to configure which objects go in which pools, as well as how large each pool is.

5. process management: in db2 your application writes to a buffer pool, an asychronous agent picks up that change and writes it to a log file, another asynchronous agent picks it up and writes it to the table. This heavily-asychronous behavior (and yes, with memory & processor tuning available for each agent type) allows you to maximize write-throughput. Postgresql and mysql are still in the slower sychronous world.

DB2 may well be better than Postgres here, but your explanation above doesn't make a lot of sense. In Postgres, a committing transaction only needs to wait for the WAL record describing the transaction to be flushed to disk (multiple transactions that commit concurrently can be flushed via a single fsync(2)). That is the only I/O that needs to be done synchronously -- the rest can be done async (notably, this includes the table I/O itself -- the modified buffers are just marked dirty in memory and are subsequently flushed to disk). Note that a backend may also need to wait for dirty pages to be flushed from the buffer pool if it is trying to replace a dirty page with a clean one, but (a) those flushes are done via write(2), so there is not necessarily a disk flush involved (b) the background writer in 8.0+ is intended to resolve this by ensuring that most of the work of flushing dirty pages is not done by a normal backend.

6. parallelism: in mysql and postgresql all queries are single-threaded. In db2 and oracle a large query will actually split itself up into multiple sub-queries to maximize throughput for multiple cpus and storage arrays. This provides db2 & oracle with linear performance improvements up to 4-8 cpus. In othe words, large queries that perform table scans can take advantage of SMP hardware for the commercial products - and cut down your query time by 75% on a 4-way compared to mysql and postgresql.

... assuming your table scan is CPU-bound, which is almost certainly not the case. In practice, intra-query parallelism is useful for two scenarios that I can think of: creating large indexes, and OLAP workloads in which you are running a small number of concurrent queries, each of which is extremely expensive. In more normal OLTP circumstances, the number of concurrent clients far exceeds the number of CPUs in the box, so you don't need to parallelize within each query. Still, I agree this would be useful to have in some circumstances, although it's a bit difficult to see how to implement it reasonably.

7. partitioning: btree indexes only work for very selective queries - like when you want 1% or less of the data of a table. But for many queries you need to crunch 5,10,or 15% of the data. That's where range partitioning comes in: you just scan the data you absolutely need to.

PostgreSQL 8.1 (currently in beta) includes "constraint exclusion", which is essentially a primitive form of table partitioning (using inheritence and check constraints, you divide the data into tables with distinct check constraints; the optimizer has been improved to recognize when a child table can be omitted from the query plan by looking at the check constraints involved).

Re:How do they compare?

[kpharmer]

> Someone I know is working with an MS SQL Server database that's too slow to be usable

Not true, sql server is a fine database. Its problems have more to do with being excessively gui-driven, expensive compared to OS dbms, and owned by microsoft than anything about the speed.

> and I'm wondering whether I should suggest they go with PostgreSQL instead

not having benchmarked them, i would guess that sql server would be faster on the same hardware.

> Do you still need to VACUUM your databases?

I think an automated vaccuum has been created. But it was never a real issue in my opinion anyway - basically the existance of vacuum enables postgresql to speed deletions and updates - since some table maintenance can be performed asynchronously. So, cron (or task schedule) the thing to run nightly and you're fine.

> Has MySQL grown up yet (i.e. implemented the features it has been missing, compared to standard SQL)?

Not yet, but it's getting there. 5.0 should be a big improvement, but it still has a long way to go - not necessarily implementing the essential feature set, but now making those implementations robust.

> How does Oracle's performance compare to the rest?

Depends on what you need to do: have a small database, or a medium-sized database that's purely transactional? The open source databases can do the job. But if you've got a large database, or want to do some analytics (like show simple trends of data) then oracle/db2/informix are your friend. These commercial databases can easily be 40x the speed of postgresql or mysql on the same hardware for analytical queries.

PostgreSQL vs MSSQL vs Oracle

[WebCowboy]

...is probably the most fair comparison.

Don't know much about Postgres in production environemnts. It seems clean and I like the fact you have a choice of stored procedure languages.

I have had experience with both in production environments, and I've come to the conclusion that PostgreSQL is clearly a step above MSSQL in terms of features and scalability. It is much better than MSSQL with concurrency and managing contention (MSSQL's locking strategy is quite brain dead). There is much more flexibility and power to create user functions and stored procs in PGSQL--you can do things like make user-defined AGGREGATE functions and data types in addition to having a choice of languages (none of that is possible with MSSQL). I find that all things being equal PostgreSQL is probably faster as well (largely an assumption becasue the PostgreSQL systems I've worked with are running on considerably less powerful hardware than the MSSQL systems I am doing). A lot of people comment about the ease of administration of MSSQL but I find that PGSQL really isn't that hard to manage even if you don't use GUI tools.

Oracle is certainly one step above PGSQL in power--but of course that comes with a very hefty price tag. That price isn't just in licensing either--Oracle takes more time to administer and you also pay by losing flexibility, since enterprise systems based on Oracle better do things the "Oracle way" or you are inviting trouble (just like with Microsoft products, Oracle really pushes its single-vendor solutions).

I have not played with Yukon/MSSQL 2005 yet, though I've heard a fair bit about it. From what I've heard it closes the gap a fair bit and comes much closer to PGSQL in terms of features and performance--it is supposed to handle locking/contention better and its has embraced .NET--meaning that you can write stored procs and functions in any .NET language. So, they are probably a pretty close match except in a couple of areas--PGSQL is free (libre and gratis), and PGSQL is not platform dependent. I think that the fact MSSQL only works on Windows is a major drawback when all its competitors offer products that run on Windows, Linux and various UNIX derivatives. Various "facts" notwithstanding I still think that Windows servers are a greater administrative burden and more difficult to secure than other alternatives--perhaps the next server version after 2003 will have addressed that.

Re:PostgreSQL vs MSSQL vs Oracle

[killjoe]

Why do people always forget about ingres, firebird and other great open source databases when this topic comes along. Isn't there anybody who can step in tell us their experience with those?

By the way over the years I have been convinced that MSSQL developers follow postgresql development pretty close. They slowly seem to be adding all features postgres has to sql server. I bet they are even using a bunch of the same code.

Re:PostgreSQL vs MSSQL vs Oracle

[kpharmer]

> I've come to the conclusion that PostgreSQL is clearly a step above MSSQL in terms of features and scalability

that might be true of v7.3 - when using postgresql 7.1 it was clearly not as easy to work with as MSSQL 2000. A perfect example was stored procedures - where you couldn't return a result list in postgresql, and the python stored procedure language required so many ticks and escapes it was impossible to use.

Regarding oracle, it used to be so much worse than it is today. For small databases it really doesn't take much time at all IF you are familiar with the product. If not, then you need to hit the books for a little bit. And the price isn't hefty at all - it can be cheaper than your redhat license for small databases. The much larger databases is where the cost is really at, but postgresql doesn't compete there anyway. In the middle ground - that's where you've got decisions to make - and the cost could go either direction, depending on how you architect the solution & negotiate.

Regarding yukon - the informal benchmarks i've seen indicate that it isn't going to provide anything for performance. Another major drawback with SQLServer is the entire gui orientation: it's great for demos and development databases, but if you've ever had to migrate code (such as for DTS) from test to production by recreating then you know what i'm talking about.

I'd also consider db2 & informix: together have a huge chunk of the market and are gradually merging together. The licensing is about 1/2 that of oracle, with about 98% of the functionality. Plus they'll handle more data. On the downside there are considerably fewer admin tools for db2 than oracle, and fewer talented developers or dbas as well. Ah, and perhaps the biggest issue: there's no Larry Ellison involved. :-)

Re:PostgreSQL vs MSSQL vs Oracle

[abulafia]

Oracle is certainly one step above PGSQL in power--but of course that comes with a very hefty price tag. That price isn't just in licensing either--Oracle takes more time to administer and you also pay by losing flexibility, since enterprise systems based on Oracle better do things the "Oracle way" or you are inviting trouble (just like with Microsoft products, Oracle really pushes its single-vendor solutions).

This can't be emphasized this enough.

We develop for/administer both, and it costs Oracle clients probably an order of magnitude more to run Oracle than PG. The environments and apps are different, so it really is hard to compare, but on average, admin and goofy workarounds for Oracle involve adding a zero to the consultant budget, in my experience. As the consultant in question, I must admit that it makes up for the hassle, but...

If you need Oracle, well, you need Oracle. But Postgres is more than enough for 90% of the cases where folks deploy Oracle, in my experience. And it is so much nicer to work with... psql vs. sqlplus, anyone?

Sure, Toad is great, but for $1K/desktop. And it seems like once you give someone Toad, they become incapable of solving problems in sqlplus, which, face it, you're going to have to do. I know this was happening to me for a while, until I noticed it - seems like it leeches one's memory of the contorted v$ and dba_blah joins one needs on occasion, and then you're stuck.

OS Integration is a great idea

[Thai-Pan]

As a developer who works on databases a lot, I still find it very arcane when I do have to get right down and dirty and work on files again. It seems very primitive in a world of SELECTs and INNER JOINs.

I personally think every OS should ship with some sort of a light db engine equipped to handle databases stored in files. Imagine if you could write a simple application that opened databases just like you would with a db server, only using a file instead. When it comes time to scale it to a larger application, switch one line and connect it to a server instead. Or have your application configurable so that the user can either store it in a file or on a remote server simply by changing the server info from "c:\database.db" to "server:1234".

Re:OS Integration is a great idea

[cdwiegand]

Thank goodness for SQLite, then. Just add the dll (or dependancy for linux-folks) to your package and voila. This also assumes you're using some DB-abstraction layer, such as PDO, ADO, ADO.Net, etc...

Re:MSDE is free!

[Just Some Guy]

Microsoft offers free

...as in beer, which makes it pretty much useless for many projects - such as a competitor integrating it into their OS.

Re:PostgreSQL vs MySQL

[photon317]

The biggest one that has made a difference in my life lately:

Table Partitioning:
PostgreSQL > MySQL; Mainline PostgreSQL has table partitioning as of 8.1-beta, by leveraging inheritance (Postgres is an Object-Relational Database).

Queries on the aggregate of the partitions are directed at the parent table, and optimized to only look into appropriate sub-table by checking CHECK constraints of the sub-table against the query WHERE clause.

Basically, you do it like this (contrived, but related to how I'm using them at the moment):

MyBigFatTable stores timestamped data from a bunch of a machines at regular intervals, keying off of the machine id and the timestamp of the data:

CREATE TABLE MyBigFatTable (
    machineid INTEGER REFERENCES machines(machineid),
    stamp TIMESTAMP,
    data_x FLOAT,
    data_y FLOAT,
    [... lots more data fields ...],
    PRIMARY KEY (machineid, stamp)
);

Your problem is, the table size grows and grows and grows unbounded, and database operations continue to get slower and slower (inserts, updates, and selects) as the table grows. You have a policy to expire the data after a month which limits the maximum growth, but this in turn requires lots of deletes happening all the time, which again hurts performance.

The inheritance-based partitioning solution is to leave that table definition as it is, and also define:

CREATE TABLE MyBigFatTable-2005-10-05 (
    PRIMARY KEY (machineid, stamp),
    FOREIGN KEY (machineid) REFERENCES machines(machineid),
    CHECK ( stamp >= '2005-10-05 00:00' AND stamp '2005-10-06 00:00')
) INHERITS MyBigFatTable;

As you can see, the column definitions are inherited, but you must re-specify the PK/FK stuff. The added check clause says that only data from Oct 10, 2005 is valid in this subtable.

You set up a maintenance script to create your new time-based tables ahead of time (say once a day create tables for the next day), and you do your data INSERTs into the specific subtable (you know the timestamp of the data you're inserting, so you can generate the appropriate table name from that (MyBigFatTable-2005-10-05).

You run your SELECTs against the original MyBigFatTable just as you did before. It automatically includes any rows from its child tables. Further, if your SELECT's WHERE-clause was constraining a query to a specific time-range, only those children of MyBigFatTable whose CHECK constraint indicates they could possibly have relevant data are checked.

And as for the problem of expiring data and the delete traffic you had before? You simply drop the old child tables with "DROP TABLE" from a maintenance script when they're a month old - no DELETEs neccesary.

System/38 had an integrated database

[RonVNX]

Indeed. The System/38 had this, and was way ahead of its time. I had one pretty much to myself in 1983-84.

MSDN

[kurtdg]

"What does this method do"-type documentation is something they are very, very good at.

I have used both MSDN (admittedly only for VBA) and the JDK docs, and the JDK docs are vastly superior. MSDN tends to only document the common cases, and ignore corner cases and limitations.