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

161–170 of 201 posts

Re: Oracle vs. PostgreSQL: First Glance

#161

Post by outworlder »

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…

> It can't be that much work,

Seriously?

Syntax is always the trivial part, everywhere you look. Semantics is what bites you.

Re: Oracle vs. PostgreSQL: First Glance

#162

Post by takacsroland »

dx034 wrote: post #3

> -- By EXCLUDED.email we could refer to the "old" email value that we are updating. Small nitpick: The excluded table contains the values proposed for insertion, not the values already present in the table (as described in [1]). [1] https://www.postgresql.org/docs/current/sql-insert.html

You are right, I already corrected this. Thanks.

Re: Oracle vs. PostgreSQL: First Glance

#163

Post by tomnipotent »

takacsroland wrote: post #151
krakatau1 wrote: post #96

I don't have a lot of experience with Oracle but I can tell you that Postgres optimizer is shit compared to Db2 zOS or Db2 LUW. When I worked in a large bank we tried to migrate core system from Db2 zOS to Postgres and it went nowhere. I was a in-house developer working with Postgres consultants and they were amazed by db2 performance in OLTP scenarios. So if your organization is already spending cash on Oracle, Db2…

In fact, my company wants to spare the expenses on Oracle. This is the main reason for migrating. We'll see how it works out. You are not the first one to point out postgre's optimizer. Is it really that bad?

> Is it really that bad?

It's not that it's bad, but Oracle/MSSQL have had the benefit of decades of corporate muscle, researchers, and Fortune 500 clients to help pave the way.

Re: Oracle vs. PostgreSQL: First Glance

#164

Post by takacsroland »

sbuttgereit wrote: post #17

The article seems to misunderstand what table inheritance is in PostgreSQL. CREATE TABLE new_table AS TABLE existing_table; Doesn't create any PostgreSQL inheritance relationship between the parent and child tables. It merely makes a new non-inherited table with a copy of the data whereas with true table inheritance you're working with the same data (there's some visibility rules to consider between parent and child,…

Thanks, I will check out your input on table inheritance and update it.

Re: Oracle vs. PostgreSQL: First Glance

#165

Post by corpMaverick »

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…

A company I was working with, also did this. Their excuse? The Oracle licenses were very cheap because the CIO is a genius negotiator. Never mind that PostgresSQL is free and much better product. Either they are really stupid or they are getting something from Oracle under the table.

Re: Oracle vs. PostgreSQL: First Glance

#166

Post by takacsroland »

corpMaverick wrote: post #165
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…

A company I was working with, also did this. Their excuse? The Oracle licenses were very cheap because the CIO is a genius negotiator. Never mind that PostgresSQL is free and much better product. Either they are really stupid or they are getting something from Oracle under the table.

After all these comments, I am getting just happier that we will migrate. :)

Re: Oracle vs. PostgreSQL: First Glance

#167

Post by F_J_H »

thda wrote: post #121
F_J_H wrote: post #24

Earlier quoted context omitted.

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…

> 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 as Oracle restful data services (ORDS). With built-in application development and deployment, it is the only complete, full-stack data management platform I am aware of (enterprise level). Yeah those architectures were nice in the 90s. We don't do that anymore,…

Who's "we"?

Re: Oracle vs. PostgreSQL: First Glance

#168

Post by to11mtm »

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.

So I'll admit, I haven't used the Oracle Provider for .NET since 2017.

Oracle.DataAccess is not the friendliest library to work with and I've seen some weird issues with pooling in past use. Devart has a good provider, but somewhat limited in free features (still a better general experience than Oracle's provider, but you don't get all the custom bits unless you pay).

PostgreSQL on the other hand has a very nice ADO Provider in NpgSql. It can look a bit daunting with all the config options offered but overall I'd still say it's a better API experience than Oracle.DataAccess.

> - A much better developer experience for stored procedures, with proper packaging, compilation to native code, graphical debugger.

Oh I do miss Oracle Packages so so much. Yeah, they could be a bit annoying to deal with from a 'gobs of code in one file' standpoint, but it's -so- nice to just have PKG_CUSTOMER, PKG_LOCATION instead of having to scroll through all the individual stored procedures, having to guess whether people named things in a way you could find them...

Edit: For a long time, you could have added - Arguments about how to store a boolean value

But thankfully Oracle finally took care of that in 12.

Re: Oracle vs. PostgreSQL: First Glance

#169

Post by F_J_H »

cryptonector wrote: post #141
F_J_H wrote: post #24

Earlier quoted context omitted.

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…

PostgreSQL + PostgREST == blinding light. You get a RESTful interface to PG. All you need to add is a static page w/ some JS, for which you can use react-admin or similar. Presto: web apps written in PG.

Yep, you could do that and I'm sure it will work well, just like you don't need dropbox because "you can already build such a system yourself quite trivially by getting an FTP account, mounting it locally with curlftpfs, and then using SVN or CVS on the mounted filesystem."1

Once we signed up with Oracle Autonomous DB, we immediately could start developing and deploying web apps and web services all within the platform, without installing or integrating any other tools, and I'm not aware of another enterprise level platform where that's possible "out of the box". So, re: the original comment, that's why we chose Oracle for a reason other than supporting legacy systems. It helps us go fast, performance is great, and the on-demand licensing gets us all that without breaking the bank. There are definitely other ways to achieve the same thing (as you suggest), but I'd rather not spend my time doing any of that, just like I'd rather not roll my own dropbox, trivial though it may be.

1. see "Infamous Dropbox Comment": https://news.ycombinator.com/item?id=9224

Re: Oracle vs. PostgreSQL: First Glance

#170

Post by AmericanChopper »

ashtonkem wrote: post #9
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…

This is actually my experience with Oracle; I had a DBA to take the management pain from us, and we had the budget for the correct licenses and hardware. The query performance was phenomenal considering the absolutely crazy amount of data we threw at that thing. That being said, I would not recommend Oracle without all of the above factors already in place. For most use cases, Oracle is more pain than it’s worth.

As somebody who does a fair amount of DBA work, Oracle is my favorite RDBMS to operate. Low level administration is easy to manage, the optimizer is very performant, and the plan management features make tuning easy, and statistics management much less risky (and it can be very risky).

I wouldn't use it on any of my own projects though, but only because I wouldn't want to pay for it, and because I have enough faith in myself to be able to manage the trickier bits of Postgres.

Post reply on HN