Oracle and PostgreSQL Debate 330
Mark Brunelli writes DBAs are talking about the merits of the open source PostgreSQL database management system (DBMS) as compared to Oracle - and their opinions truly run the gamut. DBAs responding to the interview said they liked the low cost and ease of use of the open source database, while others said that Oracle's rich feature cannot be ignored. Still others talked about how well the two systems play together. According to one DBA, a gateway product from Oracle would be a welcome offering."
Re:Oracle is a great database (Score:3, Informative)
You make it sound like MySQL is ahead of PostgreSQL in the features department. While it is true that MySQL is currently adding features faster than PostgreSQL, it's because most of those features that MySQL has been adding have been present in PostgreSQL for years.
MySQL is largely playing catchup.
Oracle Database 10g Express Edition for free (Score:3, Informative)
http://www.oracle.com/technology/products/databas
Oracle Database 10g Express Edition (Oracle Database XE) is an entry-level,
small-footprint database based on the Oracle Database 10g Release 2 code base
that's free to develop, deploy, and distribute; fast to download; and simple
to administer.
It is absolutely free. It has certain size-restrictions but they should be enough for a lot of usages.
Re:postgresql...ease of use? (Score:2, Informative)
I view MySQL, PostgreSQL, and Oracle as such -
MySQL - Designed for beginners. They work first and foremost on ease-of-use features. This is not to say its not a good database, it is. But its the easiest database to get into.
PostgreSQL - Initially designed for people who had a bit more experience. To get the full potential out of the system, you truley have to tweak with the configurations. More recent versions have made it easier for entry level users to get involved in it.
Oracle - Grand daddy. Designed to do everything for everyone, but it takes a full-time staff to make it happen.
MySQL and PostgreSQL were designed with a different mentallity. MySQL was designed to be left alone from the start.
From the start MySQL would just run, leave it alone and almost never worry about it. Two years later its still running.
PostgreSQL was designed to be powerful, but you had to look at it once in a while. Specifically the VACUUM command. More recent versions have made this easier with auto-VACUUMing.
Oracle - Don't leave this sucker alone. You have to keep an eye on it. Or, if you have a really good Sr. DBA on staff, they can configure it to run smoothly with little need for maintenance. But don't leave it alone for to long.
MySQL - Focused goals, less interaction needed.
PostgreSQL - Less focused, tries to do more for more people, more attention needed.
Oracle - Least focused, tries to do everything for everyone, needs the most attention.
The more complex and rich the system, the more work its going to need, but if you know how to take advantage of them, you can get 10x more out of them then you put in.
Compare the amount of tunable options between the three and you'll see what I'm talking about. MySQL you can configure in a few hours. Oracle? Good lord.
My company uses all three.
Re:Postgres tcp/ip too difficult to configure (Score:3, Informative)
Oracle:
- Create new directories for bdump, cdump, udump and archivelog.
- Add new files for the new tablespace(s), control and redo logs.
- Add the new SID to TNSNAMES.DAT and listener.ora
- Create the new tablespace(s)
PostgreSQL:
- "createdb "
Oracle's got PosgreSQL beat in terms of features (which, as someone else already noted, many Oracle users don't need), but I wouldn't try whining that PostgreSQL is "hard to configure" Not compared to Oracle it isn't!
Re:Postgres tcp/ip too difficult to configure (Score:3, Informative)
Re:If you need Oracle, you need it. (Score:3, Informative)
For one, Progress OpenEdge [progress.com]. My experience from working with both is that Progress is better, faster, cheaper (lowest TCO of the major RDMBS products), and is multi-platform (who is running SQL Server on Linux?). It has a very powerful toolset with the option of using a rich and intuitive 4GL or SQL. It takes next to nothing to maintain--just throw it over the wall and let it hum. And it has good connectors to Oracle, SQL Server, et al, so you can easily have a multi-product shop (which, in the age of acquisitions, is inevitable).
I'd pick Progress over SQL Server every day of the week and twice on Sunday, and I would pick Progress over Oracle for any project except extremely large (>1 TB) databases.
Re:There are other options.... (Score:2, Informative)
Re:Comparisons (Score:1, Informative)
Re:The advantage of Open Source (Score:1, Informative)
Oracle is very backwardly compatible. You can take old clients that access a newer version of an Oracle database just fine. That is assuming you aren't using a datatype that the old version doesn't understand. (eg timestamp) But that would be silly to add a datatype to your system that makes it incompatible with what you are using currently. (Oracle is knwn for keeping old datatypes around for a long long time. In the past 20 years I can't think of a datatype they dropped.)
I have been places where they have moved the backend to different operating systems (to or from windows to or from netware or to or from Unix ) and the client didn't have to change anything. (other than your database is on this machine vs that machine)
I don't have a lot of experience with Postgres and my assumption is that what client and what OS server the Postgres db runs on isn't really relevant either. (I would expect that the functionality would be the same and the client wouldn't care.)
Clearly you haven't a clue about Oracle or you are just an uninformed troll.
Re:Replication on PG is no good (Score:4, Informative)
Of them all, I would choose slony almost every time. Yes, you have to have a data design with PKs. As a fan or the relational model I think that's generally a good thing. But for those cases where you don't have a PK, slony lets you add one. Painlessly.
I have found building a slony replicated cluster to be way easier than with any other system. I have used slony's switchover in a live environment to upgrade the database, the server and the hardware, with only a 6 minute outage. I administer a 24*7 web-based site and hardly ever have to touch the database or slony.
It's way better than you make out. And if your database design really requires you not to have PKs, then you don't understand relational modelling.
Slony-I does not support multi-master, or synchronous replication. It is not designed to do so. It would be great to have this capability for Postgres but its lack should not be cause to criticise slony-I.
Corrections to the article (Score:5, Informative)
The article made a number of mistakes or maybe the interviewees were not that knowledgable:
Jim Allen, a longtime Oracle professional and an independent technology consultant, says he has had considerable experience with PostgreSQL 7.4 and 7.5, but not the newest version, 8.0.
7.5 never existed. It was renamed to 8.0 shortly before entering beta. Goes to show how little he knows-- like those people who used to call and ask for tech support for "Windows 97" except a DBA should know better....
On the other hand, Allen was unimpressed by the fact that in PostgreSQL, stored procedure parameters are not typed.
"Everything is passed as strings, even integer arrays," he said.
Huh??? This is plainly incorrect and has been since I have been working on stored procs in it (at least 7.0, maybe 6.5 or earlier). All parameters are typed. They may, however, be presented as text depending on the function and how it is called.
PostgreSQL doesn't behave as nicely as Oracle when the system fills up, Goulet said. In those instances, the system tends to crash quickly.
I assume he is talking about oid/xid wraparound issues. Oid wraparounds fail pretty gracefully. In 8.1, you will get plenty of warnings before the xid wraparound forces a crash. However the crash is there as a safety measure to protect your data-- if the xid was allowed to wraparound, previously committed transactions would become invisible.
The solution to the xid wraparound is simply to do regular mainetnance. With 8.1 the autovacuum capability is integrated into the database backend and so this should never be a problem.
Goulet said that setting up a TCP/IP connection capability with PostgreSQL is hardly an intuitive process. To do it, he says, one needs to modify the postgres.conf and pg_hba.conf files manually.
Prior to 7.4 I think this was the case. With 8.0 and 8.1, only the pg_hba.conf needs to be enabled though you *might* also want to allow the system to listen on addresses other than localhost. In this case, you might need to alter both files.
But then there are webmin plugins etc. that allow you to modify the pg_hba entries from a web interface
Backups backups backups! (Score:3, Informative)
PostgreSQL needs a reliable, well documented method for performing live incrememental backups. As in:
1) dump the whole database once a day
2) dump the transaction log every 5 minutes
You can then recover to any point give or take 5 minutes by loading the last full dump and each of the incrementals up to the point you need.
PostgreSQL ALMOST has this in the form of "Point In Time Recovery" but..
1) the documentation is incomplete - key details (like a definitive method for identifying the current log file) are missing. Check out the threads in the postgres-admin mailing list. It needs to be easier and users need to be 100% confident that they have the right set of files.
2) you can only backup and restore the whole server instance - ie ALL the databases at once. In practice this means you need a second server somewhere to do recovery on, then need to perform a complicated migration back to the primary server.
If backups don't really matter to you (or you're not running a transactional system) then PostgreSQL is fantastic. But if it's getting many updates a day and you care about recovery (so you're not boned when someone forgets the WHERE clause in a DELETE/UPDATE command) then it doesn't quite cut the mustard yet.
An official reference implementation backup & restore script would be a good start.
Re:I'm not convinced... (Score:2, Informative)
Re:If you need Oracle, you need it. (Score:5, Informative)
PostgreSQL doesn't behave as nicely as Oracle when the system fills up, Goulet said. In those instances, the system tends to crash quickly.
I'm, among other things, an Oracle administrator. When the filesystem that holds the databse files fills up on Oracle 9i 9.2.0.4 on both Solaris and Linux, I can tell you for sure that the Oracle instance will crash suddenly, with nothing more than a notation in the log that the disk was full trying to write to file such-and-such.
That's not any different from what they describe with PostgreSQL.
Re:Who Ya Gonna Call? (Score:4, Informative)
1) Command Prompt, Inc.
2) PostgreSQL, Inc.
3) Software Research Associates
If you want to pay for software licenses, I would suggest doing buisness with EnterpriseDB.
Other potential vendors include Fujitsu (in Australia at least) and Green Plum in CA.
Sun is also looking at offering support for PostgreSQL when it is bundled with Solaris.
Want more? My firm offers DBA-level support. If you want highly technical support, use the email lists, or call Command Prompt.
Re:postgresql...ease of use? (Score:4, Informative)
I just wish mysql could use
PostgreSQL can do this. Read up on the pam authentication method.
obviously they've never tried to dump and restore a database when upgrading to a new major release. Never goes according to the documentation. thats why I love mysql, just install the new rpms and keep on truckin'.
I just wish MySQL had transactional full text indexing, Java stored procedures, and nestable database roles (which makes administering a database with many users easy). MySQL has both technical limits and ease of use limits once you start doing anything moderately complex with it.
Of course, compared to Oracle, anything is easy to use.
Re:postGRES (Score:4, Informative)
Re:I can pronounce Oracle (Score:3, Informative)
I hear that one as Sequel-server from suits on a regular basis.
All I can think of is "sequel to what?"
ash
Re:There are other options.... (Score:3, Informative)
Re:Oracle is a great database (Score:3, Informative)
Postgres doesn't have built-in replication, non-commercial, yet MySQL has had it since 3.x and they are on 5.x now. Yes, there is SLONY, but it's an add on.
Do you really want to tie your replication version to your database version? As long as it is not so tied, it makes it possible to replicate between different versions of PostgreSQL (say for a zero-downtime upgrade, or a phasein of a new version) without worrying so much about forward/backward compatibility issues on the replication side.
Slony may be an add-on, but it is hardly third-party. It was written with the help of at least one of the core PostgreSQL developers.
Finally, PostgreSQL *does* have at least one replication technology, dbmirror, as a part of its contrib package which is shipped with the source.
Postgres has had views, triggers and stored procedures for a while now, and MySQL just got them in 5.0.
PostgreSQL still has more features in this area than does MySQL.
* Multiple triggers per table
* Stored procs in arbitrary languages
PostgreSQL also has roles, better foreign key support (there are some ways of adding foreign keys that MySQL 5.0 w/innodb silently drops even in strict mode).
PostgreSQL has had data partitioning possibilities since 1995, but the new check-constraint-based optimizations make it more useful in data warehousing operations.
MySQL had 64-bit support (I love the Opteron) since 4.0; Postgres just got it in 8.1. The ability to utilize large amounts of memory is a big deal for databases.
Funny, I was not aware that the Opteron was the only 64-bit chip out there. I was aware of plenty of huge DB's running on 64-bit Alphas and the like and the recommendations of 64-bit chips on large DB's was documented in PostgreSQL at least as far back as 2000.
Also, I would point out that source builds for PostgreSQL on the Opteron were shown as effective at least as of 8.0. I would not be surprised if it was not working on the platform even earlier.
The biggest issue for me prior to 5.0 was the silent truncation of *numbers* by the database. The database is your last line of defense against bad data and it should not be changing the data in order to avoid providing errors to the program. Even in 5.0, strict mode can be turned off by the client so it provides very little protection at all.
The other issue I find is that if you have a large number of database users, mysql permissions become a pain.
MySQL is a *perfect* database for content management. But for anything where the integrity of the data matters, I am not sure I would trust it.
Also, there is a new movement in PostgreSQL to move as much functionality out of the core distribution as possible and kernelize it. Thus, for example, pl/pgsql could be upgraded without upgrading PostgreSQL. Thus many of the community projects (such as PL/J) will become increasingly important. MySQL because of their licensing system will never be able to go this direction.
Re:I'm not convinced... (Score:3, Informative)
Seriously, I'd like someone to explain what precisely about Oracle could ever be considered absolutely necessary that cannot be found anywhere else aside from organizational bias and insipid politics.
Here are a few answers:
1) Oracle allows you to tune types of transactions very heavily. For example some tables or transaction types can log while other do not.
2) Log miner combined with archiver allow you to generate activity reports offline
3) Complex partition tables with partition indexes
4) Grid databases so you can spread a consistent database over hundreds of servers
5) Really good ISAM compatibility on their mainframe versions so that legacy cobol code will work against semi-relational structures
Re:Postgres tcp/ip too difficult to configure (Score:2, Informative)
The defaults have bad security holes, and changing things is pretty bad. They had a daemon in there who's *purpose* was to allow remote systems to execute programs without authentication for gods sake. You had to pay for encryption and it took highpriced dba to set it up (contrast with pretty simple SSL for opensource dbs)
SQLNet is *not* simple and out of the box is pretty freaking insecure.
Re:Oracle is a great database (Score:3, Informative)
Postgres has realized for many many years that February 31st is not a real date. MySQL only recently realized that.
Postgres has thrown errors for years if you entered out of bounds data. Until extremely recently, MySQL would happily silently change your data to something it liked. No errors, just bad data. Yummy. And it will still do that if you don't run it in 'strict' mode (not the default, except on windows). Postrges doesn't have any such setting to 'accept bad data and silenty change it'. Real databases don't.
Re:Availability of Source Code? Does it Matter? (Score:4, Informative)
Say, for example, Microsoft or Oracle go "belly up". It can happen, quicker than you think, for a variety of reasons. There have been many examples throughout history of this happenning, either due to external or internal reasons. So in theory, if your company relies on MSSQL or Oracle's DB product, and the vendor goes belly up, what then?
Well, your company can probably continue with the software, as is (as long as there isn't any "call home" licensing checks). Hopefully, if your company is smart, they immediately begin a crash course to migrate to a new database product and/or vendor. However, let's say they don't, because they can't convince their clients to buy an all new DB backend or whatever, or the DB software being used has a feature not available anywhere else, or something like that. Time passes, then one day, a very nasty bug in the software is found, something that could possibly take down the business, leaving all the clients in the lurch - what then?
Since your company doesn't have the source to the DB software, you can't fix it. You better pray you can find a workaround. If not, it may be curtains for the business (and maybe some of your clients, who may have went with thier own version of "proprietary software" when they went with your company, unless your code is open source). Had you instead gone with an open-source DB solution (and/or rolled your own code to bring those "needed-features" the other proprietary guy had and gave them back to the community as a note of "thanks"), and had that open-source solution gone "belly-up", and had events transpired the same way (bugs found, etc)...
In theory, at that "darkest moment", you could "save yourself", either by hunting down the offending code and fixing it (and distributing the patch to clients), or hiring someone else to do the same. THAT is the power of open source, and why it is a good thing, even if you never touch it yourself. Frankly, having been in a variety of vertical-market software development jobs over the past 15 years, the above situation happens more often than you think (although in most cases it is with other software than databases), causing companies to almost grind to a halt as they look for yet another proprietary vertical market solution in their domain (most vertical market solutions are proprietary due to the nature and size of the business domains they serve - think insurance, medical, warehousing, distribution, etc) - paying huge amounts of money in their contracts to have the lucky winner "convert them over" to the new system...
Re:Postgres tcp/ip too difficult to configure (Score:3, Informative)
How I loathe thee...
--which, incidentally, doesn't run on linux-x64 because of its reliance on an ancient java implementation
Re:I'm not convinced... (Score:3, Informative)
I've got mod points, but I'm not going to use them here because there is no category for "Cynical" which this post would most assuredly be modded up for.
However, he's mostly right. My father used to say when trying to sell an ugly piece of jewelry "If a piece doesn't sell, keep raising the price until it does". Worked for him.
Re:If you need Oracle, you need it. (Score:3, Informative)
Keep an eye on them long term. IIRC, PRGS was one of only a handful of Boston based tech firms that made money nearly every quarter since the dot-bomb.