Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2004
    Posts
    1

    Unanswered: Postgres vs Oracle

    I'm currently looking into performance/price comparison between Oracle and third party distributions of Postgres.

    What we need is a database that can handle large volume transactions (I don't know how much yet), failover solution, as well as clustering possibly. A lot of network infrastructure hasn't been decided on yet, so I can't give much more detail to the setup.

    Some third party distros of Postgres I've looked at already are: eRserver, PostgreSQL Replicator, C-JDBC, and Mammoth PostgreSQL.

    In particular, I'd like to know the benchmarks/statistics performance of say this many CPUs with this much RAM, how Oracle runs on it versus Postgres.

    Wondering if anyone can point me in the right direction. Thanks.

  2. #2
    Join Date
    May 2002
    Location
    Florida, USA
    Posts
    49

    Re: Postgres vs Oracle

    In particular, I'd like to know the benchmarks/statistics performance of say this many CPUs with this much RAM, how Oracle runs on it versus Postgres.
    A lot of people would love to have those kinds of benchmarks. Unfortunately most are unaware of the cost and effort it takes to do any sort of rigorous comparison. Add to that that fact that Oracle can be configured in so many (bewhilderingly many) ways and that each has its own strengths and weaknesses and you have no easy way to do a side-by-side comparison.

    But, I think I can pretty safely say that --except for the most extreme situations (massive scaleability, etc...) PostgreSQL easily offers the better price per performance/features. The only real question is whether PostgreSQL has all the features you need. Better first to do a feature-by-feature comparison for such things as:

    - transaction methods (for example, PostgreSQL doesn't yet support nested transactions, or savepoints)
    - types of clustering/failover available
    - SQL standards conformance (PostgreSQL might just beat Oracle there)
    - Integrity-maintenance features (constraints, triggers, stored procedures, etc...)
    - datatypes, and size constraints of these datatypes
    - size limitations of tables and databases, and how to manage continued growth

    In all of the above, generally Oracle supports the most extreme cases, as I said, but because of that, also requires a far greater learning curve to handle all of its ins and outs.

    If you are satisfied that PostgreSQL has the features you need, then I would suggest asking for more help, in the pgsql-performance mailing list, and mentioning far more particulars, such as expected transactions per hour, table sizes, etc...

  3. #3
    Join Date
    Jan 2004
    Posts
    19

    Re: Postgres vs Oracle

    There is no comparison between Oracle and PostgreSQL. Oracle rules. The discriminator is: do you want to pay $40,000.00 for it. Out of all he databases I have worked with, PostgreSQL by far is as about as close you can get for free. I don't know about all of the distributions, but the one that comes with Gentoo Linux is good. I don't recommend using it under Windows commercially. Before you inquire about performance, maybe you should measure your requirements. What are they exactly?
    That will determine if you should go PostgreSQL or Oracle. Are you 100% sure you are going to use Windows or Linux or Unix?

    Originally posted by CaptainTofu
    I'm currently looking into performance/price comparison between Oracle and third party distributions of Postgres.

    What we need is a database that can handle large volume transactions (I don't know how much yet), failover solution, as well as clustering possibly. A lot of network infrastructure hasn't been decided on yet, so I can't give much more detail to the setup.

    Some third party distros of Postgres I've looked at already are: eRserver, PostgreSQL Replicator, C-JDBC, and Mammoth PostgreSQL.

    In particular, I'd like to know the benchmarks/statistics performance of say this many CPUs with this much RAM, how Oracle runs on it versus Postgres.

    Wondering if anyone can point me in the right direction. Thanks.

  4. #4
    Join Date
    May 2002
    Location
    Florida, USA
    Posts
    49
    I have to say, PostgreSQL does have one other advantage over Oracle: simplicity. I can have a nicely tuned DB set up on Linux or FreeBSD server within an hour, and that includes compiling from source with processor optimizations, etc... Try doing that with Oracle ;-).

    Here's a nice look at PostgreSQL's size limitations, but the way: http://www.postgresql.org/users-lounge/limitations.html
    Apparently, there are companies out there with Terabyte-size PostgreSQL databases, and there are companies using PostgreSQL for mission-critical tasks. (for example, the .ORG domain registry uses PostgreSQL)

    And for a quick note on the 'con' side, here is an extract from one of the more serious PostgreSQL newsgroup:

    ... If someone comes to me and asks for a HA scenario with zero transaction loss during failover, we can discuss a little if this is really what he needs or not, but if he needs that, the solution will be Oracle or DB2, for sure I will not claim that PostgreSQL can do that, because it cannot.

    -- Jan Wieck

    As I said, in the most extreme cases, Oracle is still your best choice , but of course, they also have the most extreme prices.

    I think it is worth noting though, that when the PGReplication project is finished, that gap between PostgreSQL and Oracle will be a lot smaller. There are other replication systems for PostgreSQL, but PGReplication is the only one that will implement true "eager replication", as does Oracle.

  5. #5
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482
    Originally posted by rycamor
    I have to say, PostgreSQL does have one other advantage over Oracle: simplicity. I can have a nicely tuned DB set up on Linux or FreeBSD server within an hour, and that includes compiling from source with processor optimizations, etc... Try doing that with Oracle ;-).

    Here's a nice look at PostgreSQL's size limitations, but the way: http://www.postgresql.org/users-lounge/limitations.html
    Apparently, there are companies out there with Terabyte-size PostgreSQL databases, and there are companies using PostgreSQL for mission-critical tasks. (for example, the .ORG domain registry uses PostgreSQL)

    And for a quick note on the 'con' side, here is an extract from one of the more serious PostgreSQL newsgroup:

    ... If someone comes to me and asks for a HA scenario with zero transaction loss during failover, we can discuss a little if this is really what he needs or not, but if he needs that, the solution will be Oracle or DB2, for sure I will not claim that PostgreSQL can do that, because it cannot.

    -- Jan Wieck

    As I said, in the most extreme cases, Oracle is still your best choice , but of course, they also have the most extreme prices.

    I think it is worth noting though, that when the PGReplication project is finished, that gap between PostgreSQL and Oracle will be a lot smaller. There are other replication systems for PostgreSQL, but PGReplication is the only one that will implement true "eager replication", as does Oracle.
    Don't forget to commercial support. Oracle has better commercial support than PostgreSQL which is important for applications in business area.

  6. #6
    Join Date
    Feb 2004
    Posts
    4
    I m checking Postgresql and MS-SQl database server for our new
    development. On a very first query Postresql is out performed and I think it is
    very disappointing. My query consists on a single table only on both
    machines.
    Table Structure
    Table "inv_detail"
    Attribute | Type | Modifier
    --------------+-----------------------+--------------------
    inv_no | integer | not null
    unit_id | character(4) | not null
    item | character varying(90) | not null
    qty | double precision | not null default 0
    rate | double precision | not null default 0
    unit | character varying(20) | not null
    vl_ex_stax | double precision | not null default 0
    stax_prc | double precision | not null default 0
    adl_stax_prc | double precision | not null default 0
    package | character varying(12) |
    Having 440,000 Records.
    My Query
    --------
    select count(*), sum(vl_ex_stax) , sum(qty) , unit from inv_detail
    group by unit;
    on both databases.
    PostgreSQL return result in 50 sec every time.
    MS-SQL return result in 2 sec every time.
    MS-SQL Machine
    **************
    Athlon 600Mhz. (Unbranded)
    256 MB Ram. ( 133 Mhz)
    40 GB Baracude NTFS File System.
    Windows 2000 Server Enterprise.
    MS-SQL 2000 Enterprise. (Default Settings)
    PostgreSQL Machine
    ******************
    P-III 600Mhz (Dell Precision 220)
    256 MB Ram (RD Ram)
    40 GB Baracuda Ext2 File System.
    RedHat 7.2
    PostgreSQL 7.1.3-2
    My PostgreSQL Conf is
    *********************
    log_connections = yes
    syslog = 2
    effective_cache_size = 327680
    sort_mem = 10485760
    max_connections = 64
    shared_buffers = 512
    wal_buffers = 1024
    NOTICE: QUERY PLAN:
    ********************
    Aggregate (cost=inf..inf rows=44000 width=28)
    -> Group (cost=inf..inf rows=440000 width=28)
    -> Sort (cost=inf..inf rows=440000 width=28)
    -> Seq Scan on inv_detail (cost=0.00..11747.00
    rows=440000 width=28)
    EXPLAIN
    Even if I dont compare postgres with any other database server the time
    taken is alarmingly high. These settings are not good I know, but the
    Postgres result is very un-acceptable. I m looking forward for comments
    to change the conf setting for acceptable results.

  7. #7
    Join Date
    Feb 2004
    Posts
    14
    Are there any indexes? The query plan is obviously non-optimal, as it has to scan the _entire_ table.

  8. #8
    Join Date
    May 2002
    Location
    Florida, USA
    Posts
    49
    In addition to the standard question of indexes, I think you will find that PostgreSQL with the proper settings will perform much faster than what you are experiencing.

    1. What environment were you querying from? (Were you using the 'psql' command-line environment, or were you connecting via ODBC, or some other SQL client, etc...)

    2. RedHat 7.2 is fairly old, and older Linux distributions default to some very conservative settings for such things as shared memory, disk usage (32-bit, DMA not enabled), and max filehandles, etc..., which can severely affect database performance.

    3. I notice that you are running PostgreSQL 7.1, which is much slower than later versions. And PostgreSQL 7.4, the most recent version, was even faster than previous versions, especially on queries using GROUP BY, subselects, etc...

    4. Another thing to note about PostgreSQL: in a view based on a query that uses SQL aggregate functions you can even create an index on the use of those functions.

    5. Also, if you use a parameterized query or stored procedure, you can have the query plan cached. It is incredibly fast.

    Anyway, I just ran a similar query to yours on one of my older Celeron machines (800Mhz), running FreeBSD 4.4 and PostgreSQL 7.2.1 (much slower than PG 7.4), and my results returned in less than a second.
    Last edited by rycamor; 02-18-04 at 11:41.

  9. #9
    Join Date
    Feb 2004
    Posts
    4
    Yes there is indexes. No matter what type of query it is, my point is MS SQL Server giving results in more faster then the postgresql.

    - I tested this from psql from the DB Server directly.
    - Which distro you recommend me to use?
    - Ok, will gonna check with 7.4
    - Good to hear that the indexes capability at views.
    - Great results, how many rows you have in it?

  10. #10
    Join Date
    May 2002
    Location
    Florida, USA
    Posts
    49
    Originally posted by farhantoqeer
    Yes there is indexes. No matter what type of query it is, my point is MS SQL Server giving results in more faster then the postgresql.
    Sure, you may have indexes, but from the query plan, it is obvious they are not being used, ergo they are not the right indexes to support your query. Please post the SQL table creation statement.


    - I tested this from psql from the DB Server directly.
    - Which distro you recommend me to use?
    Any new version of the standard distributions: RedHat 9, Mandrake 9 or 10, Suse 9, etc... My personal favorite is Slackware 9.1, using the ReiserFS filesystem. (Actually I really prefer FreeBSD, but that is not Linux )


    - Ok, will gonna check with 7.4
    - Good to hear that the indexes capability at views.
    - Great results, how many rows you have in it?
    My table only had 12,000 rows, but given that this is a heavily worked server, running all standard services, (Apache/PHP, MySQL, PostgreSQL, Sendmail, DNS, etc...), and the Celeron only has a 128K cache, these results are pretty good.

    If your results were truly typical of PostgreSQL, then there is NO WAY companies would be running PostgreSQL databases with 100 million rows and more, but PostgreSQL databases of that size do exist. (for example, the .ORG domain registry runs PostgreSQL)

    Speed is not really the issue with most modern SQL systems running on modern hardware. I'm sure that for some queries SQL server is faster than PostgreSQL, and for some, PostgreSQL is faster. But really, speed is more dependent on good database design and administration than on whether you run PostgreSQL, Oracle, SQL Server, or Sybase.

  11. #11
    Join Date
    Feb 2004
    Posts
    4
    well i have freebsd 5.2 and slackware 9.1 and i can check it.
    I know that DB Design plays a vital role in extracting data but what if you really require the same query to get the results?

  12. #12
    Join Date
    Feb 2004
    Posts
    4
    I have checked it on redhat 8.0 and 7.4 of postgresql, got good result in 4 seconds . Thank you so much rycamor for your replies.

    Regards
    Farhan Toqeer
    Karachi - Pakistan.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •