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

31–40 of 201 posts

Re: Oracle vs. PostgreSQL: First Glance

#31

Post by sbuttgereit »

devit wrote: post #29

How come nobody has implemented an Oracle compatibility mode for PostgreSQL? Or in general, why don't databases support each others SQL dialect? It can't be that much work, at least if one is content with only supporting the majority of applications, and seems pretty essential for popularizing a specific database. Looking at the article, supporting Oracle syntax seems trivial in all cases except for adding full MERGE…

You mean like...

https://www.enterprisedb.com/enterprise-postgres/database-co...

It's been around for years. Earlier on PostgreSQL did make some efforts of being recognizable to Oracle users... look at Oracle PL/SQL and PostgreSQL PL/pgSQL... very similar and I recall that similarity being intentional.

Also, there is the SQL standard. Rather than supporting all vendors' syntax and features, which can change on the whim of some competitor that probably doesn't have your best interests at heart, it's better to adhere to the standard if you want the broadest applicability. PostgreSQL does exactly that with few deviations from the standard, relative to the industry as a whole. At the end of the day it's really about goals and not every RDBMS has the same goals; with PostgreSQL standards compliance is a goal.

Re: Oracle vs. PostgreSQL: First Glance

#32

Post by teilo »

devit wrote: post #29

How come nobody has implemented an Oracle compatibility mode for PostgreSQL? Or in general, why don't databases support each others SQL dialect? It can't be that much work, at least if one is content with only supporting the majority of applications, and seems pretty essential for popularizing a specific database. Looking at the article, supporting Oracle syntax seems trivial in all cases except for adding full MERGE…

Two reasons.

> It can't be that much work

You're right, IF you so overly simplify the translation that it also doesn't work with the majority of Oracle applications.

Second: Have you heard of the Android/Java/API lawsuit?

Re: Oracle vs. PostgreSQL: First Glance

#33

Post by __s »

rpedela wrote: post #5
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…

PG community has put a lot effort into performance the last few years, including JIT compilation in PG12. Is that criticism still true today?

& more's coming. PG13 has improved hash aggregation[0] & List (which is used throughout) has been updated from being a linked list to being an array

0: https://www.postgresql.org/message-id/507ac540ec7c20136364b5...

Re: Oracle vs. PostgreSQL: First Glance

#34

Post by pjmlp »

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.

Re: Oracle vs. PostgreSQL: First Glance

#35

Post by zip1234 »

dx034 wrote: post #4
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 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

Re: Oracle vs. PostgreSQL: First Glance

#36

Post by pjmlp »

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…

For me, MS SQL Server is the only real alternative, regarding the overall tooling around the database.

Re: Oracle vs. PostgreSQL: First Glance

#38

Post by dtheodor »

rpedela wrote: post #5
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…

PG community has put a lot effort into performance the last few years, including JIT compilation in PG12. Is that criticism still true today?

It's very true today. The problem discussed here is performance on complex queries (e.g. subqueries), and the query planner plays a huge role in that. The Postgres query planner has various issues. Here are two recent posts talking about planner issues:

https://medium.com/@rbranson/10-things-i-hate-about-postgres...

https://www.cybertec-postgresql.com/en/things-could-be-impro...

Also JIT compilation, while very nice and a step in the right direction, is very barebones at the moment, hardly achieving its potential. Here's a long todo list of what and how to make efficient use of JIT in postgres, by the main author of the feature. https://twitter.com/AndresFreundTec/status/10025899696161996... Postgres release 12 did not add any JIT related improvements, and as far as I know no work has been done on it on 13 either.

Re: Oracle vs. PostgreSQL: First Glance

#40

Post by dtheodor »

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

It's in-memory materialization, which will spill to disk if it doesn't fit in memory.

Search for "Materialize node" in https://www.postgresql.org/docs/12/using-explain.html

Post reply on HN