4 ms·

PostgreSQL versus Oracle

21 points by craigkerstiens 15y ago

evandena 15y ago
Am I missing the joke?
- obtu 15y ago
  WordPress is MySQL-only.
vegas 15y ago
Here's the article in Bing's cache: http://cc.bingj.com/cache.aspx?q=http%3a%2f%2fdiznix.com%2f2010%2f07%2f27%2fpostgresql-versus-oracle%2f&d=5010835797704716&mkt=en-US&setlang=en-US&w=4f5aaf93,222626b0 http://cc.bingj.com/cache.aspx?q=http%3a%2f%2fdiznix.com%2f2... Microsoft: Champion of the persistent web!?
- gbaygon 15y ago
  > Microsoft: Champion of the persistent web!? what? Here is the google cache: http://webcache.googleusercontent.com/search?q=cache:Ge0OdZ2RVe4J:diznix.com/2010/07/27/postgresql-versus-oracle/+&cd=1&hl=es&ct=clnk&gl=ar http://webcache.googleusercontent.com/search?q=cache:Ge0OdZ2...
EzGraphs 15y ago
Or here: http://diznix.com/2010/07/27/ http://diznix.com/2010/07/27/
EzGraphs 15y ago
An Oracle DBA pointed out to me years ago that MySQL performed better under certain circumstances. It seems like PostGres does a pretty good job keeping up with Oracle features and is much more open and customizable. I am sure PostGres excels in many areas and is a suitable substitute for Oracle for many projects. Does it have equivalent functionality to some of the more recent Oracle features like flashback queries and analytic functions (with windowing etc)? Are there any areas where Oracle clearly excels?
- lobster_johnson 15y ago
  Postgres has window functions, and I believe it supports the full ANSI windowing function feature set. Nothing like Oracle's flashback queries. It used to have a feature called time travel which allowed you to look at historical versions of data, something they could do thanks to MVCC. It was removed, presumably because it caused performance issues or was too complex to maintain.
- lobster_johnson 15y ago
  Time travel (http://www.postgresql.org/docs/6.3/static/c0503.htm http://www.postgresql.org/docs/6.3/static/c0503.htm) was deprecated in 6.2. > There are several reasons for this: performance impact, storage size, and a pg_time file which grows toward infinite size in a short period of time.
lobster_johnson 15y ago
Presumably deleted because Oracle's terms of use prohibit the publishing of benchmarks.
- nas 15y ago
  "Well, if they’d like to bust my chops for an article that’s about 15 months old, they’re welcome to do so (or at least try)." Well, I guess they did. :-/
- tbrownaw 15y ago
  Any idea if that's everywhere, or only here in the US with our record of being completely useless about prosecuting anti-competitiveness?
  - lobster_johnson 15y ago
    Everywhere, doubt if anyone has ever bothered to challenge them on the issue. If you can afford Oracle, why blog about your benchmarks?
  - dripton 15y ago
    EULAs are only valid in some jurisdictions. Of course, even if one lives in a jurisdiction where they're not, it would take some guts to stand up to Oracle's lawyers. And to convince your ISP, your government, etc. to stand up to Oracle's lawyers.
- donatzsky 15y ago
  Not sure why, but if you go to http://diznix.com/2010/07/27/ http://diznix.com/2010/07/27/ instead, it works just fine.
avar 15y ago
Interesting that they're testing full-text search on the two, it would be very interesting to benchmark it against something like ElasticSearch or another scalable full-text search engine to see how fast that would be by comparison.
- gav 15y ago
  Oracle knows that their full-text search is lacking and have made some recent acquisitions around search. It will be interesting to see how these play out.
- lobster_johnson 15y ago
  Yep. ElasticSearch is actually quite slow, as text engines go. It seems designed to scale, rather than provide single-machine performance. Even so we are seeing very slow queries on our 8-node cluster, even for relatively simple queries and small datasets. It seems very sensitive to the result set size (ie., the maximum item number set in the query). A limit of 50 might take 500ms (which is slow but acceptable in our case) whereas 1000 results might take 3-4 seconds. The indexes are all in OS cache so disk performance is not the bottleneck.
  - avar 15y ago
    I'd be interested to know what your index looks like / how the mapping is set up / what the datasize is / what sort of queries you're making, because that's definitely not my experience with it. It's lighting fast on my workloads.
sciurus 15y ago
"the PostgreSQL version in use was 8.4.4." Does anyone know if text search performance is significantly different in the (as of 2012-04-23) latest version, 9.1.3?
IsTom 15y ago
I'm not sure about the current version of Oracle, but the one I used did not support nested WITH clauses. Pain in the lower part of the back if you want to compose and optimize queries. PostgreSQL has no such silly trouble.
hiroprot 15y ago
Mmm...so this was a test that compared a production server that is presumedly under load against a test server that is seeing no load other than the testing?

Am I missing the joke?

obtu 15y ago
WordPress is MySQL-only.

vegas 15y ago

Here's the article in Bing's cache: http://cc.bingj.com/cache.aspx?q=http%3a%2f%2fdiznix.com%2f2010%2f07%2f27%2fpostgresql-versus-oracle%2f&d=5010835797704716&mkt=en-US&setlang=en-US&w=4f5aaf93,222626b0 http://cc.bingj.com/cache.aspx?q=http%3a%2f%2fdiznix.com%2f2... Microsoft: Champion of the persistent web!?

gbaygon 15y ago
> Microsoft: Champion of the persistent web!? what? Here is the google cache: http://webcache.googleusercontent.com/search?q=cache:Ge0OdZ2RVe4J:diznix.com/2010/07/27/postgresql-versus-oracle/+&cd=1&hl=es&ct=clnk&gl=ar http://webcache.googleusercontent.com/search?q=cache:Ge0OdZ2...

EzGraphs 15y ago

Or here: http://diznix.com/2010/07/27/ http://diznix.com/2010/07/27/

EzGraphs 15y ago

An Oracle DBA pointed out to me years ago that MySQL performed better under certain circumstances. It seems like PostGres does a pretty good job keeping up with Oracle features and is much more open and customizable. I am sure PostGres excels in many areas and is a suitable substitute for Oracle for many projects. Does it have equivalent functionality to some of the more recent Oracle features like flashback queries and analytic functions (with windowing etc)? Are there any areas where Oracle clearly excels?

lobster_johnson 15y ago
Postgres has window functions, and I believe it supports the full ANSI windowing function feature set. Nothing like Oracle's flashback queries. It used to have a feature called time travel which allowed you to look at historical versions of data, something they could do thanks to MVCC. It was removed, presumably because it caused performance issues or was too complex to maintain.
lobster_johnson 15y ago
Time travel (http://www.postgresql.org/docs/6.3/static/c0503.htm http://www.postgresql.org/docs/6.3/static/c0503.htm) was deprecated in 6.2. > There are several reasons for this: performance impact, storage size, and a pg_time file which grows toward infinite size in a short period of time.

lobster_johnson 15y ago

Presumably deleted because Oracle's terms of use prohibit the publishing of benchmarks.

nas 15y ago
"Well, if they’d like to bust my chops for an article that’s about 15 months old, they’re welcome to do so (or at least try)." Well, I guess they did. :-/
tbrownaw 15y ago
Any idea if that's everywhere, or only here in the US with our record of being completely useless about prosecuting anti-competitiveness?
- lobster_johnson 15y ago
  Everywhere, doubt if anyone has ever bothered to challenge them on the issue. If you can afford Oracle, why blog about your benchmarks?
- dripton 15y ago
  EULAs are only valid in some jurisdictions. Of course, even if one lives in a jurisdiction where they're not, it would take some guts to stand up to Oracle's lawyers. And to convince your ISP, your government, etc. to stand up to Oracle's lawyers.
donatzsky 15y ago
Not sure why, but if you go to http://diznix.com/2010/07/27/ http://diznix.com/2010/07/27/ instead, it works just fine.

avar 15y ago

Interesting that they're testing full-text search on the two, it would be very interesting to benchmark it against something like ElasticSearch or another scalable full-text search engine to see how fast that would be by comparison.

gav 15y ago
Oracle knows that their full-text search is lacking and have made some recent acquisitions around search. It will be interesting to see how these play out.
lobster_johnson 15y ago
Yep. ElasticSearch is actually quite slow, as text engines go. It seems designed to scale, rather than provide single-machine performance. Even so we are seeing very slow queries on our 8-node cluster, even for relatively simple queries and small datasets. It seems very sensitive to the result set size (ie., the maximum item number set in the query). A limit of 50 might take 500ms (which is slow but acceptable in our case) whereas 1000 results might take 3-4 seconds. The indexes are all in OS cache so disk performance is not the bottleneck.
- avar 15y ago
  I'd be interested to know what your index looks like / how the mapping is set up / what the datasize is / what sort of queries you're making, because that's definitely not my experience with it. It's lighting fast on my workloads.

sciurus 15y ago

"the PostgreSQL version in use was 8.4.4." Does anyone know if text search performance is significantly different in the (as of 2012-04-23) latest version, 9.1.3?

IsTom 15y ago

I'm not sure about the current version of Oracle, but the one I used did not support nested WITH clauses. Pain in the lower part of the back if you want to compose and optimize queries. PostgreSQL has no such silly trouble.

hiroprot 15y ago

Mmm...so this was a test that compared a production server that is presumedly under load against a test server that is seeing no load other than the testing?