Forgot your password?
typodupeerror

Oracle and PostgreSQL Debate 330

Posted by Zonk
from the warring-dbas dept.
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."
This discussion has been archived. No new comments can be posted.

Oracle and PostgreSQL Debate

Comments Filter:
  • by Anonymous Coward on Thursday April 06, 2006 @03:31PM (#15078948)
    90% of the people who use it don't need it. 100% of that 90% are/have been convinced they need it.
    • 100% of that 90% are/have been convinced they need it.

      Well, what's their alternative? SQL Server? You can only get by on that for so long.

      The usual transition goes like this; Access->SQL Server->(Something Better)

      List out the current list of products that qualify as "Something Better" than SQL Server.
      • Oracle and DB2. A.k.a., "what the big boys use".
      • List out the current list of products that qualify as "Something Better" than SQL Server.

        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
        • by Anonymous Coward
          I have become increasingly frustrated with Progress for about 2 versions now. The 4GL is clunky and limited, and the implementation of SQL is poor. Interoperability with free software tools, languages and databases is practically nonexistent, so you get tied into an all-progress solution. That just grinds my gears.

          But at least empty string isn't a null. WTF were Oracle thinking?

          Anyway, this is so offtopic. Postgres is entirely adequate for anything you would do with Progress, and it's relatively unenc
        • I had the "pleasure" to work on eveolutive maintenance of a large Progress project for 4 years.

          Progress, despite its name, is really a collection of relic, non-standard concepts and technologies, with bizzarre and arbitrary ideas like implicit transaction scope, rollback on memory data structures, a 4GL that grown in a sort of Frankestein monster of a language, pathetical error checking, inflexible data model and convoluted syntax to replicate stuff, like cursors, that other DBMS had sported for decades.

          Add
        • From my stint playing with Progress, I'd have to agree with you. 4GL makes data manipulation easy enough my father could get away with it. The troublewith Progress is the dearth of 3rd-party tools that would make it a true contender in the RDBMS space. As it is, it's fairly well consigned to integrated/embedded db space (somewhat like my favorite dbms, ObjectStore), and it's SQL support, last I checked, was both horribly slow and not nearly SQL-92 compliant. On the upside, the Progress purchase of Meran
      • Well, what's their alternative? SQL Server?

        I think the point is that Postgres might be a better solution for them, now that it's supported on NT.

        List out the current list of products that qualify as "Something Better" than SQL Server.

        All the products? Do I have to include encoding information on knotted cords and dispatching it over mountain footpaths via barefoot runners?

        Better depends on what for of course. SQL server is fair as a database engine, but so is Postgres. Transact SQL is utter crap, both b
    • 50% of this message was convoluted by unnecessary use of percentages. 100% of that 50% was difficult to read as a result.
    • by Chapter80 (926879)
      Ever try to sell your client (or management) on using a database whose name they can't pronounce?

      Post gre ess que ell
      Post gres SQL
      Post gres QL

      ...heard it all these ways.

    • Isn't it more complex than that? In my experience almost no one NEEDS Oracle, but in addition to having bought into the idea that they do need it, most cannot move away from it, because they've allowed their applications to become locked into proprietary features, even where open versions of those features exist.
    • I'm not convinced... (Score:3, Interesting)

      by C10H14N2 (640033)
      ...that ANYONE /needs/ Oracle.

      I will accept that someone would need something like Oracle Financials and that would be contingent upon using the Oracle database, but structurally speaking, why is it necessary for anything in particular? I mean, cripes, GOOGLE uses MySql. [computerworld.com.sg] If THEY don't need Oracle, who the hell does?

      I've run into this before trying to sell a TINY Division on using MySQL or PostgreSQL--every single !#!#%ing engineer said the same thing: we don't need _anything_ beyond MySQL, hell PostgreSQL i
      • by Anonymous Coward
        Google uses a distributed cluster approach, hardly Oracle's area. Perhaps you've never worked with big datasets, but when we're talking about several TiB of data MySQL is a complete joke and PostgreSQL can't probably handle it. Big iron (read: Sun or IBM) + Oracle is the only way to do it.
      • by RicRoc (41406) on Thursday April 06, 2006 @05:28PM (#15080072) Homepage
        I'm very happy that our company is using Oracle - it's expensive, that's why! That high expense reflects back on me, in a good way. "The software is valueable, so the people who work with it are valueable". I'm better paid because they chose Oracle over MySQL.

        Another thing is the large selection of Oracle training available. The more expensive a thing is, the more training is "worth it" -- even if it is insanely expensive. When I get this training, it is because "I am worth it" -- making me worth more in the process.

        And yet another thig is the high level of professionalism surrounding Oracle. Our Oracle DBA is fantastic, he really preaches the right practice, and management listens to him. Because he is an professional Oracle DBA, not some MySQL tweaker.

        Personally I would use PostgreSQL, but I'm happy we are using Oracle. Who needs all the features above and beyond ACID compliance? Perversly, it's Oracles high price tag that makes it better for me - personally - at work. I'm not footing the bill, and a bigger budget translates to higher saleries in the field.

        I's perverse, but that's how it is.
        • Personally, I've seen expensive solutions get undercut by commodity solutions too many times to get comfortable with that line of reasoning. Heck, we've all seen how Windows and later Linux have thrown the server operating system business on its head. At some point businesses invariably start wondering why they are paying so much for Oracle and Oracle talent when their competitors are getting the same job done with PostgreSQL.

        • The software is valueable (sic), so the people who work with it are valueable

          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.

        • by el_womble (779715) on Friday April 07, 2006 @05:56AM (#15082947) Homepage
          I hate this doublethink. I both 100% agree with you and 100% hate the fact that your right.

          Why do people associate the cost of the tool with the cost of the engineer? Surely a man who can create a masterpiece with a brush and an oil solute is worth more than some monkey with a digital camera and photoshop. I guess its just an easier metric for managers to deal with.

          What annoys me the most is that this is why big companies, that make lousy solutions, are making a killing. The project I'm working on put out a tender for its platform technologies. Unsuprisingly, the technologies that won were BEA Weblogic (Container), Sun (Servers), Cisco (Networks) and Oracle (Database).

          I know that the same product could be built using Tomcat (Container), Debian (Servers), OpenBSD (Networks) and PostgreSQL (Database) and work as well or better (the budget doesn't reflect the complexity in this case), and I know that they weren't even concidered because, as OSS solutions, they don't have a consultancy team running around making promises Dev can't keep. I used to believe that it was important that enterprise solutions came with enterprise support, however, I have yet to experience enterprise grade support from anyone, at least not in any form that was better than an OSS product.

          But who am I trying to kid. If PHBs had a clue about technology they wouldn't be PHBs. The big corporations that can afford big iron software soultions exist because of pervasive ignorance and metric crunching abilities of middle management, and the zeal of their marketing dept, not becuase they know what their doing.
        • I'm very happy that our company is using Oracle - it's expensive, that's why! That high expense reflects back on me, in a good way.

          I'm glad that raw leather is so expensive. That makes my salary look small in comparison. And our buggy-whip sales have been going up, up, up! I am so set for life.

      • by jbolden (176878)

        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 datab
    • 100% of that 90% are/have been convinced they need it.

      That's a bit of Captain Obvious point, isn't it?

      That said, Oracle has a lot of really good and unusual features that even relatively mundane appliations can use. They aren't necessary of course, and they are highly non-standard, which is why they are seldom used.

    • by morgan_greywolf (835522) on Thursday April 06, 2006 @04:54PM (#15079719) Homepage Journal
      There's plenty of misinformation going around. For instance

      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.
      • Nonsense... (Score:3, Interesting)

        by IANAAC (692242)
        ... Oracle instance will crash suddenly, with nothing more than a notation in the log that...

        That's nonsense. It doesn't crash. What it does do is wait for you to give it some more space. As an administrator, that's a fairly easy thing to do. Once you've done that, Oracle continues on its way as if nothing had happened.

      • I am Oracle DBA (RAC and single instance) and I don't know what kind of screwy setup you have going on there but there is no it should crash if the file system fills up. The worse I have seen is not be able to log into (as anyone other than sys) a database that is in archive logging mode because it can't write anymore archive log files, this is expect behaviour not crashing.

        Whem my datafiles out grow their disk I just get a warning similar to "Can't extend tablespace by 8k etc...." it _doesn't_ crash.

  • by jarich (733129) on Thursday April 06, 2006 @03:35PM (#15078980) Homepage Journal
    I've been working with Ingres recently. It's GPL and has a great enterprise-proven track record. Best of both worlds.

    http://ingres.com/ [ingres.com]

    • I didn't know that Ingres was GPL now. So I went to have a look, but all the Ingres documentation seems to be in PDF only [ingres.com]! No thanks...
    • I tried to find out something more about Ingres, which I had never heard of before. Google spits out strange remarks like: "The original RDBMS, but the current version of its QUEL language has been corrupted to acommodate SQL."

      Is there a sad and bitter ex-employee somewhere? Or what does that mean?!
      • Ingres has been around for a long time. I'm a newbie to it, but so far it's nice. I think it will fill a nice niche.

        Check out Wikipedia http://en.wikipedia.org/wiki/Ingres [wikipedia.org]

        and to the other poster, I don't like the PDF docs either! :(

        • I also noticed that the client libraries are licensed under the GPL, which effectively causes the same sorts of licensing headaches that make MySQL money.

          PostgreSQL doesn't have this problem. Nor would MySQL or Ingres if the client libs were LGPL instead of GPL.
      • QUAL was a pre-SQL relational language, which had much better conformance with relational algebra. In order to use it to its limits, though, you had to really know relational theory. I vaguely remember something about being able to specify an ordering within a subselect that was different than the ordering in the outer query, but I don't really remember. I do remember that QUEL "made sense", more so than SQL did when I learned it. Then again, my first RDBMS was University INGRES, so I learned QUEL first
  • 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 could use /etc/passwd for authentication of users/passwords, I hate that it has to use its own internal user/pass database.
    • obviously they've never tried to dump and restore a database when upgrading to a new major release. Never goes according to the documentation.

      Since this is the first time I've ever heard of that problem, and have never seen it myself during my own upgrades, care to back that up with evidence?

    • docs at http://www.postgresql.org/docs/8.1/interactive/ba c kup.html [postgresql.org]

      I do just like they say, the pg_dumpall, then upgrade the database to the new major release, then do the restore. when I restore with psql -f infile postgres. and it always errors with things like the users not existing, the database not existing, etc. so i have to go in and create the users by hand, and then create an empty database, etc. I always just have to play around until I can make it work. upgrading between postgresql major rel
    • by Anonymous Coward
      If you don't know what your doing with it, this can easily happen.

      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 le
      • I tend to disagree as my mileage has varied significantly.

        MySQL is my database of choice for small web applications that demand simplicity and speed. The database is remarkably simple to install and configure, but I've personally run in to data corruption issues more often than with any other database. That being said, recovery is pretty simple and I'm usually back up and running in no time.

        PostgreSQL is not a database I'm real strong in, but so far its OK by me. I find its feature set more complete than

    • I just wish mysql could use /etc/passwd for authentication of users/passwords, I hate that it has to use its own internal user/pass database.


      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.
  • Who Ya Gonna Call? (Score:4, Insightful)

    by Doc Ruby (173196) on Thursday April 06, 2006 @03:37PM (#15079010) Homepage Journal
    I like how I can call Oracle and get the best developers/DBAs/integrators/troubleshooters to solve my problem, and it requires only money. I like how I can look at the Postgres source code, so I don't have to call anyone to solve my problem - or I can choose who I call.
    • "I like how I can call Oracle and get the best developers/DBAs/integrators/troubleshooters to solve my problem"

      Not true for most of their "customers" since they are not Oracle's cusomters. They are customers of some vertical marker supplier that they bought their MRP/CRM/SOX/LIMS/whatever software from that just happens to use Oracle. They have zero support through Oracle. Usually if the VAR can't fix the problem you end up playing semaphore with the VAR sitting in the middle trying to mediate. This does
    • by einhverfr (238914) <chris.travers@gM ... com minus author> on Thursday April 06, 2006 @04:56PM (#15079731) Homepage Journal
      Companies that employ core PostgreSQL programmers and offer tech support include:

      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.
  • by zfalcon (69659) on Thursday April 06, 2006 @03:37PM (#15079014)
    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.

    Uhh...is editing a config file really that difficult a process? It's like two lines.

    • Apparently, when you come from a point-and-click world, editing text is unintuitive.
    • Uhh...is editing a config file really that difficult a process? It's like two lines.

      Yes! Unless it comes with a wizard and that funny animated paperclip, it's too hard!

    • Compare that to trying to create a new database:

      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!
      • 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!

        For one thing, these days, Oracle ships with an Java GUI that makes the database creation process alot less painful plus the Enterprise manager also helps to keep an overview. Not that your complaint about the DB instance creation process really makes much sense. You are comparing a Sabertooth
        • Oracle ships with an Java GUI that makes the database creation process alot less painful

          I have never seen an Oracle DBA (and I've been one) use the Java GUI for anything even remotely complex. It's too slow for most of the complicated tasks, and for simple tasks (say creating a new user/role), it's even faster to use the command line.

          You are comparing a Sabertooth tiger with a common house cat which is pretty redundant.

          Maybe you could give a shout upstairs and ask your mom what "redundant" means?

          OracleDB i
      • Oracle:
        - "dbca"
        - choose "Create a database"
        - keep on clickin'
    • But unintuitive? Yes. And of course, Goulet said the process was not "intuitive". Nobody ever said it was difficult.

      Think of it like exiting vi. It is not difficult to type :q! but it is hardly intuitive. Same with enabling TCP/IP connections in postgres. If it were a one-liner in postgres.conf, then fine. But also pg_hba.conf? What the fuck does pg_hba mean? I don't know what hba stands for. How do I know to go looking there? I don't, because it's unintuitive.

      • "But also pg_hba.conf? What the fuck does pg_hba mean? I don't know what hba stands for. How do I know to go looking there? I don't, because it's unintuitive."

        Postgres is a SQL DATABASE SERVER! it is designed to be installed by people that know what they are doing and can READ.
        That is clearly documented in the manual for setting up postgres. You only do it once in the lifetime of the server.
        That is like saying "winning the 100 million dollars was great but they wanted me to drive all the way to there office
        • Postgres is a SQL DATABASE SERVER! it is designed to be installed by people that know what they are doing and can READ.

          LMAO! Most excellent comment - put that on a t-shirt.
        • That is clearly documented in the manual for setting up postgres.

          :q! is clearly documented in the vi manual as well, but I still say it's unintuitive.

          Again, nobody said it was difficult. Just unintuitive.

          And for the record, the worst problem with Postgres is that transaction IDs can wraparound, causing massive data loss if you don't vacuum frequently enough. Massive data loss, medium data loss, and even minute data loss should never happen with an RDBMS. It is currently 2006. When I put data in the da

    • Why should it be in two separate places, though? Even if it is multiple lines, there's no good reason for configurations relating to the same thing being found in two different files.
      • They aren't. You configure postgres to listen on a TCP/IP port on postgresql.conf, and allow specific IP's to connect remotely/locally in pg_hba.
      • By default, PostgreSQL is only accessible from localhost.

        You can change the listen address in the postgresql.conf

        The Host-based authentication configuration file (pg_hba.conf) provides a very fine-grained level of control for how people can connect to the database. In it you can allow specific networks to connect but not some machines within those networks, and you can use different types of authentication for different networks.

        For example, you can allow the web server to connect only using an account nam
    • Every time I've tried to alter network access and authentication for PostgreSQL, it has broken. The format of the files, at least the last time I did it, possibly a year or so ago, is unclear, and altering it feels very fragile.
    • PostgreSQL still has a few rough edges, but these are being worked out pretty quickly.

      My main comment was....

      "Right.... And Oracle is the paradigm of intuitive behavior!"
  • A little of both? (Score:5, Insightful)

    by PCM2 (4486) on Thursday April 06, 2006 @03:41PM (#15079047) Homepage
    Why does everything have to be all or nothing? There's nothing stopping an Oracle shop from using PostgreSQL here and there. Plus you've got EnterpriseDB, [enterprisedb.com] which bolts Oracle compatibility onto PostgreSQL for a little bit of the best of both worlds. Go ahead and pay Oracle for the top end of what their feature set lets you do and use PostgreSQL for the rest.
  • as is MySQL/InnoDB and Postgres.

    MySQL and Postgres have the features that most projects need. Most databases are relatively small (in the 50 gigabyte range), and do fine with a standard database with triggers, views and stored procedures.

    Oracle has features that are absolutely essential to some projects. And MySQL and Postgres are slowly (or more quickly in the case of MySQL) adding features, turning Oracle into a niche product.

    Long gone are the days where, to paraphrase, "No one was ever fired for impleme
    • Oracle has features that are absolutely essential to some projects. And MySQL and Postgres are slowly (or more quickly in the case of MySQL) adding features, turning Oracle into a niche product.

      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.
      • Yes and no.

        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.

        Postgres has had views, triggers and stored procedures for a while now, and MySQL just got them in 5.0.

        MySQL has clusters since 4.1, and I don't believe Postgres has anything equivilent.

        Postgres just beat MySQL to data partitioning (MySQL 5.1 is in beta, but 8.1 is production I think).

        MySQL had 64-bit support (I love the Opteron) since 4.0; Po
        • > Ya, MySQL was missing more of the "core" features, but for us, replication and large database
          > buffers was more important two years ago than stored procedures, triggers or views.

          But core comes first:
          - views have been around since about 1983 or earlier in DB2 and probably earlier than that in Oracle
          - triggers, stored procs, etc have been around for 10-15 years in most commercial products
          This is all stuff that commercial products have had for decades, postgresql has had i

        • 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
        • I think you forgot a few important things.

          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
  • by ksp (203038) on Thursday April 06, 2006 @03:44PM (#15079072) Homepage
    To me, the important advantage of Open Source on the server side is that my data is in an Open Format - because I have the source. I can clean up corruptions or load old backups because I know exactly how the server reads the data.

    Also, I can use the same database version forever. I have to get someone to patch the code to run on Vista or Windows Server 2025 or whatever in the future, but the core of the database server remains the same. Database servers just keep running on some server and are forgotten until suddenly someone makes the decision to upgrade those old NT 3.51 servers ASAP. If you run an ancient version of Oracle, you are stuffed. No support for the old version, your proprietary front end application doesn't support the Oracle versions that run on Win2003 - so what do you do? Run your business critical RDBMS at an unsupported version on NT on VMWare on Win2003? With Open Source, you can patch the layer that needs fixing, without changing the rest of the product or include the feature bloat the Oracle Sales keep getting added into their products.

    • I really don't know where to begin on this one.

      "I can clean up corruptions or load old backups because I know exactly how the server reads the data."
      You need take a serious look at you backup and recovery plans. The last thing I would ever want to hear is that a DBA is using khexedit on raw database files.

      "Database servers just keep running on some server and are forgotten"- Sure fire path to disaster and reinforces my first point.

      "If you run an ancient version of Oracle, you are stuffed"
      On a 9i to 10g

  • by Anonymous Coward on Thursday April 06, 2006 @03:44PM (#15079076)
    If they want something that plays nice with Oracle, they should take a look at http://www.enterprisedb.com/ [enterprisedb.com] .

    One of the goals of the company is aimed specifically at making life easier for Oracle people on PostgreSQL.

    Company I work for runs both PostgreSQL and Oracle. Years ago we were a PostgreSQL only shop. Along comes a Sr. Developer who touts Oracle to management, and they listened to him.

    Now we have 2 Sr. Oracle DBAs, 1 Jr., and 2 PL/SQL programmers.

    Oh yeah, we don't have any PostgreSQL DBAs. But we have just as many PostgreSQL servers.

    Now we are moving some of our applications back to PostgreSQL, which of course scares the Oracle DBAs.

    Our servers are heavy-hit. Thousands of queries per-second on both systems. PostgreSQL can keep up with Oracle, and Oracle can keep up with PostgreSQL.

    One thing I've noticed about the market that is both good and bad for PostgreSQL - You can put out an Ad for an Oracle DBA and get hundreds of responces. Put one up for PostgreSQL and you get almost none. Almost a year we've had an Ad out for a PostgreSQL, there just arn't any.

    And I don't think its because there arn't any full-time DBAs. The reality is PostgreSQL just doesn't need the same amount of staff that an equal amount of Oracle databases need. The good side, it just works and requires so little maintenence. The bad side? Its hard to sell to companies when they can't have someone full-time on it.

    I'm curious with other companies experiences. How many full-time DBAs do you have for Oracle? How many for PostgreSQL?
    • Its funny - we had the same experience with Informix. My last company was responsible for about 150 critical production Informix instances, plus about 25 in-house development ones, and handled the load with 2.5 DBAs who spent most of their time tweaking for peak performance, or doing upgrades, et cetera. When we brought on an Oracle product that ratio dropped to more like 1 DBA for every 10 instances.

      Then again, Oracle is the same company that sells a multiDollar enterprise product without a hot backup sc
      • If you were a database consulting services company, which would you recommend?

        We are, and we recommend PostgreSQL. What good is it to us if our customers are paying lots of money to Oracle, Microsoft, or IBM? We can make up for a few missing features here or there for most deployments (those that don't depend on intraquery parallelism for performance) with some extra services. Customers save money, and more importantly, we make more money.

        Some people are shortsighted and let their vertical compliments (l
    • Interestingly enough, you'll have the same results when putting out an ad for a DB2 UDB dba. These people get somewhere and stay there or they're all consultants. We search 6 months for a full-time DB2 DBA and just hired one a month ago.

      In our environment, we've basically broken it down to DB2 DBA and SQL DBA (which is the dumbest thing I've heard of).

      The DB2 dba handles all the DB2 servers and the SQL dba handles EVERYTHING else (mssql, postgresql and mssql).
  • by Anonymous Coward on Thursday April 06, 2006 @03:54PM (#15079154)
    For those cost-conscious users, you may want to explore the free Oracle Database 10g Express Edition.

    http://www.oracle.com/technology/products/database /xe/index.html [oracle.com]

        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.
  • by ashpool7 (18172) on Thursday April 06, 2006 @03:54PM (#15079162) Homepage Journal
    Slony I requires a primary key on all tables in order to be able to do anything. I have tables that don't have primary keys and I don't want to ever have them. I've normalized my DB and it's the best way to keep track of multiple items for a single person. OIDs are a waste of time in this situation and a cop-out. I don't want to rely on some level of replication that runs on top of the database server, I want it to be part of the database server so everything that works with the DB is aware of replication needs.

    Postgres really needs some replication or mirroring mechanism built-in in order to even begin to attract people away from Oracle. The Slony II project will certainly require this level of integration, and I hope it succeeds, even it it takes until PostgeSQL 10.0.
    • by bloodnok (35021) on Thursday April 06, 2006 @04:44PM (#15079610)
      I have used slony-I, Mammoth Replicator, Oracle Advanced Replication, an early version of Oracle Streams in 10g (don't know if it's still called that), and an Oracle third-party replication scheme that I can not currently remember the name of.

      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.

  • So is oracle's rich feature the one the one where you pay through the nose for their software and they get rich?
  • Anyone who has done enterprise level web-enabled applications can easily tell you the faults with all of the major players in the database field. Oracle is simply 'ok', but for most tasks, it's -to be brutally honest- over kill. Do you really need the replication features of SQL when all you are doing is cacheing emails and collecting messages from your users? At this juncture, most people are relieved because they believe that they settle for a second-teir solution such as MYsql and save the licensing fees
  • by Cranky Weasel (946893) on Thursday April 06, 2006 @04:15PM (#15079320) Homepage
    "I like how I can look at the Postgres source code, so I don't have to call anyone to solve my problem - or I can choose who I call."

    In discussions like this, availability of source code always comes up.

    I want to know who has a job where they have so much extra time on their hands that they can debug the source code of their database product.

    No, seriously. I REALLY want to know. I can't imagine things operating at a pace where this kind of thing is even an option.

    The only conclusion is that people who actually do this are either (a) the top .001% of the elite programmers who can do this on the fly, (b) ex-developers from the PostgreSQL team, or (c) nerds in their basement with no time constraints because all they're doing is running their Star Trek fansites with it.
    • Yes, access to the source matters. Not because you want to hack it, but because you may want to put a debugger on it to track down a problem. With Oracle you don't have that option.

      Instead you call Oracle support who ask a lot of questions. You answer the questions, they ask for a database dump. You spend time organising this only to be told that it's too big for them to deal with. You demand help. They ask more questions. You get nowhere.

      I have been through this process many times. I have been an O
    • I want to know who has a job where they have so much extra time on their hands that they can debug the source code of their database product.

      I would guess that this is more a theoretical reassurance than an in-practice feature. Theoretically if the db broke and I had absolutely no other recourse I could debug it myself. In reality there are a lot of other options to fix the situation faster, but having that last resort available is nice.

      • There are also SEVERAL support companies who CAN understand the code (many of whom are PGSQL contributers) and fix it for you.

        If this is really an issue (to the GP), they should purchase a support contract and/or buy one of the commercial builds of PGSQL (Greenplum, Pervasive, Mammoth).

        I honestly don't know many DBAs myself who could fix the actual source itself.
    • by WebCowboy (196209) on Thursday April 06, 2006 @07:11PM (#15080802)
      I want to know who has a job where they have so much extra time on their hands that they can debug the source code of their database product.

      Nobody except the active contributers to the RDBMS I'm guessing. Certainly not be. But I'll tell you my personal experience with PostgreSQL and how it being open source directly benefited me:

      I was doing a project involving PgSQL many years ago (v6.2 I think) to manage a small inventory database. There was a problem that looked like a bug in PgSQL rather than a configuration issue (I think it was causing VACUUM to fail among other things but my memory fails me). What I clearly remember was how I resolved the issue, and it is the first time that the benefits of open source directly affected me and when I becane clearly sold on open source.

      I had given up and since there wasn't a company to turn to I looked for contact emails in what passed for the docs at the time (they are MUCH better now) and on the website. I emailed one of the core developers and described my problem. He emailed me back the next day and thanked me for my feedback and said he had a few other reports of problems somewhat similar to mine. He also ATTACHED THE SOURCE CODE OF THE PATCH he had been working on that was not yet in the release on the website! I applied the patch and recompiled and bingo...it was back to normal!

      Now I was (still am) far from a guru C programmer but as with a lot of people I can stumble my way around makefiles and GCC and patches and so forth, and I did have time to recompile PgSQL. I can also (at the instruction of one of the developers) to traces and such and send in the results and THEY can do the debugging with my help. If I was using Microsoft SQL Server and had a similar problem I'd be screwed: I'd have to call clueless tech support, or wander around the KB articles and hope to find the solution, and in this case I'd probably find a useledd KB article along the lines of "Microsoft has acknowledged this to be an issue and will provide a solution in the next available hotfix" telling me to do some kludgy, unacceptable workaround in the meantime, which could be days, or weeks...or maybe even never. I certainly would NEVER have the ear of a Microsoft programmer who wrote or reviewed the code as a lowly intern-type doing a small experimental project.

      So there you go...I'm (a) not an "elite programmer", (b) never been part of the PostgreSQL team beyond exchanging emails with a team member, and (c) though some may say I am a nerd I moved out of my parents' home when I was 17 and never lived in their basement. Despite that I have indeed directly benefited from source code availability for software that I did not write.
    • by cr0sh (43134) on Thursday April 06, 2006 @08:29PM (#15081243) Homepage
      It isn't that you can do it, or that you can hire someone else to do it (mangle/compile the code) - but the fact that you can do it at all.

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

  • by adolfojp (730818) on Thursday April 06, 2006 @04:15PM (#15079321)
    Next week on Slashdot:

    Movers: 18 Wheelers and Pickup Trucks Debate
    Grave Diggers: 360 Degree Excavators and Shovels Debate
    Firefighters: C-130s and Hoses Debate
  • I want to do this, anyone tried this yet?
  • by philovivero (321158) on Thursday April 06, 2006 @04:43PM (#15079601) Homepage Journal
    Like any profession, database administration is rife with the mediocre and downright incompetent. For those, Oracle and the like provide an awesome service. A DBMS that works half the time, and the half the time it isn't working, there are documents, online knowledge bases, and expensive tech support personnel who can read to you from their CDROMs.

    If you really want to know if PostgreSQL (or MySQL) can handle it, look at the best and brightest tech corps in the world. I'll pick two for you: Google and Yahoo!. They use MySQL extensively. IMO PostgreSQL can do whatever MySQL can (though, honestly, I'm not sure, I've only ever seen MySQL in high volume environments like Digg, where I'm currently working).

    If your org *NEEDs* Oracle or Sybase or whatever because MySQL and PostgreSQL aren't supported by some software you bought, I feel sorry for you, and recommend you either accept your company's mediocrity or get out.

    If you think MySQL/PostgreSQL just don't have what it takes on a fundamental level, I humbly suggest you rethink your competence in the field.
    • Support is a real issue though.

      Example:
      Our ETL vendor, Informatica, doesn't support PostgreSQL or MySQL and refuse to even help with the issues.
      Our BI vendor, Actuate, rebuilt our environment and provided a custom build of the package to make it work with PostgreSQL.

      Interestingly enough, Informatica costs a fortune compared to Actuate.
    • "If you really want to know if PostgreSQL (or MySQL) can handle it, look at the best and brightest tech corps in the world. I'll pick two for you: Google and Yahoo!. They use MySQL extensively. IMO PostgreSQL can do whatever MySQL can (though, honestly, I'm not sure, I've only ever seen MySQL in high volume environments like Digg, where I'm currently working)."

      "If you think MySQL/PostgreSQL just don't have what it takes on a fundamental level, I humbly suggest you rethink your competence in the field.

  • by yem (170316) on Thursday April 06, 2006 @04:49PM (#15079666) Homepage
    Just to plug my favourite itch..

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

    If you've ever had to bail out an inept Oracle DBA, this quote is rather ironic. Managing Oracle through it's GUI looks simple, but a few false steps you fall into a terrifying shadow world of arcane SQLPlus incantations and mysterious and evil grimoire config files.

Physician: One upon whom we set our hopes when ill and our dogs when well. -- Ambrose Bierce

Working...