Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Oracle vs. PostgreSQL: First Glance

rolkotech.blogspot.com

Post reply on HN
Print view View original

41–50 of 201 posts

Re: Oracle vs. PostgreSQL: First Glance

#41

Post by snarfy »

jcadam wrote: post #2

I've been on a project where we were forced to migrate the opposite direction: From PostgreSQL to Oracle, because the client was already paying for Oracle licenses and really, really , wanted us to use Oracle to justify the expense. It was actually a pretty big setback. We were using PostGIS to support spatial queries (a key requirement), and Oracle Spatial was just not at the same level (both in performance and feat…

I removed any mention of Oracle from my resume. I have a lot of experience with it, but I never want to work with it again.

Re: Oracle vs. PostgreSQL: First Glance

#42

Post by [deleted] »

doublesCs wrote: post #26
ibejoeb wrote: post #23

Earlier quoted context omitted.

One of the big changes in 12 allows the optimizer to work properly with CTEs, which was a major barrier to using the more expressive language features. There a good writeup here: https://paquier.xyz/postgresql-2/postgres-12-with-materializ...

Thanks for that link. > Historically we've always materialized the full output of a CTE query Do you know if this means that CTEs are written to disk? I didn't think this was the case. (In the case of materialized views that qualifier means the view is written to disk).

[deleted]

Re: Oracle vs. PostgreSQL: First Glance

#43

Post by hans_castorp »

ajuc wrote: post #20

Biggest difference for me is DDLs are transactional in Postgres, but not on Oracle. That means migration scripts for software on Postgress can just have all DDLs (alter, create, drop, grant etc.) and DMLs (inserty, update, etc.) mixed in whatever order they need to be, and if any particular line of the migration script fails - the whole thing is rolled back as if nothing happened. And then you fix the problem and run…

> Biggest difference for me is DDLs are transactional in Postgres, but not on Oracle.

The answer I received more than once from Oracle evangelists regarding transactional DDL: it's useless and if you need it, you are not testing your scripts properly

Re: Oracle vs. PostgreSQL: First Glance

#44

Post by defnotashton2 »

zozbot234 wrote: post #13
MrHamdulay wrote: post #7

Why are the table and field names in the examples between Oracle and PostgreSQL different? It makes it harder to compare the two.

IIRC, Oracle licensing forbids publishing direct comparisons with competing products. I guess they had to find a workaround.

This kind of model speaks to who actually buys it, I would never pay for a product with such limitations out of principle.

Re: Oracle vs. PostgreSQL: First Glance

#45

Post by jedieaston »

F_J_H wrote: post #24
markab21 wrote: post #14

Why anyone would use Oracle for anything other than supporting legacy systems is beyond me.

I have not been an Oracle fan in the past, especially because of their complicated (and expensive) licensing, but late last year we moved to their hosted autonomous database. The on demand pricing model makes it quite economical, and the performance is amazing. However, the killer feature for me is that it has application Express (or APEX) included, which is a complete web application development framework, as well a…

>application express

Is it smart to have all of your web app code running in the database, making it impossible to run without the Oracle Database?

Re: Oracle vs. PostgreSQL: First Glance

#46

Post by fanf2 »

zip1234 wrote: post #35
dx034 wrote: post #4

Earlier quoted context omitted.

I really don't like Oracle from a DBA perspective but it's still often far ahead of PostgreSQL when it comes to query performance. In Postgres, the query structure can make huge differences in terms of performance and it can take a lot of tuning to find the right query to optimize performance (especially when subqueries are involved). Oracle (and SQLServer) are usually pretty good at optimizing the query exactly the…

Does PostgreSQL have bitmap indexes. Very much a great feature that Oracle has for query performance

Looks like PostgreSQL only has ephemeral bitmap indexes used when making a query that combines multiple indexes - https://leopard.in.ua/2015/04/13/postgresql-indexes - https://www.postgresql.org/docs/current/indexes-bitmap-scans...

Re: Oracle vs. PostgreSQL: First Glance

#47

Post by atombender »

zip1234 wrote: post #35
dx034 wrote: post #4

Earlier quoted context omitted.

I really don't like Oracle from a DBA perspective but it's still often far ahead of PostgreSQL when it comes to query performance. In Postgres, the query structure can make huge differences in terms of performance and it can take a lot of tuning to find the right query to optimize performance (especially when subqueries are involved). Oracle (and SQLServer) are usually pretty good at optimizing the query exactly the…

Does PostgreSQL have bitmap indexes. Very much a great feature that Oracle has for query performance

No. Postgres has in-memory bitmap indexes, which are built on the fly while scanning the index and used to more efficiently combine AND/OR clauses, but that's not quite the same thing. There have been several attempts at adding on-disk bitmap index support to Postgres, but they've all been abandoned.

Re: Oracle vs. PostgreSQL: First Glance

#48

Post by ajuc »

hans_castorp wrote: post #43
ajuc wrote: post #20

Biggest difference for me is DDLs are transactional in Postgres, but not on Oracle. That means migration scripts for software on Postgress can just have all DDLs (alter, create, drop, grant etc.) and DMLs (inserty, update, etc.) mixed in whatever order they need to be, and if any particular line of the migration script fails - the whole thing is rolled back as if nothing happened. And then you fix the problem and run…

> Biggest difference for me is DDLs are transactional in Postgres, but not on Oracle. The answer I received more than once from Oracle evangelists regarding transactional DDL: it's useless and if you need it, you are not testing your scripts properly

> Oracle evangelists

There is such a thing?

> it's useless and if you need it, you are not testing your scripts properly

Is that also their response for static typing and constraints on database :) ?

Re: Oracle vs. PostgreSQL: First Glance

#49

Post by globular-toast »

markab21 wrote: post #14

Why anyone would use Oracle for anything other than supporting legacy systems is beyond me.

It's mostly used because the people who hold the purse strings in large organisations don't have a clue about databases, but the salesmen for Oracle wear fancy suits, take them out for expensive dinners and treat them like chums so they get the deal.

Re: Oracle vs. PostgreSQL: First Glance

#50

Post by augusto-moura »

pjmlp wrote: post #34
markab21 wrote: post #14

Why anyone would use Oracle for anything other than supporting legacy systems is beyond me.

- A much better developer experience for stored procedures, with proper packaging, compilation to native code, graphical debugger. - RAC and distributed transactions across a database cluster - Integration with APIs - A much better experience in Java and .NET drivers, including SQL custom data types.

> - A much better experience in Java and .NET drivers, including SQL custom data types.

I didn't have any better experience with Oracle drivers in Java. Most of the driver is a soup of hacks exploiting obscure features of both the VM and standard library (both the vm and jdk are "Oracle owned" so I guess I was expecting that), also the source code is not available, so debugging it's a hellish experience.

On the other hand the Postgres JDBC Driver is the most well written and documented driver that I ever saw in Java

Post reply on HN