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

61–70 of 201 posts

Re: Oracle vs. PostgreSQL: First Glance

#61

Post by lukeschlather »

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…

It's very convenient for Oracle that anyone making these sorts of claims is contractually barred from sharing any evidence.

Re: Oracle vs. PostgreSQL: First Glance

#62

Post by pjmlp »

augusto-moura wrote: post #50
pjmlp wrote: post #34

Earlier quoted context omitted.

- 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…

I beg to differ, those drivers exist since Java was owned by Sun.

Also Oracle was the first RDMS to support stored procedures in Java.

So source isn't available yet you are able to judge the code quality, interesting.

No, disassembling bytecode isn't a reflection of the quality of the original source code.

Re: Oracle vs. PostgreSQL: First Glance

#63

Post by mrits »

ibejoeb wrote: post #23
rpedela wrote: post #5

Earlier quoted context omitted.

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

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

I left postgres v9 and have been working on Vertica. It's pretty surprising that Vertica came out with that WITH/WITHOUT MATERIALIZATION keywords 2 years ago.

Re: Oracle vs. PostgreSQL: First Glance

#64

Post by sjwright »

Pxtl wrote: post #8
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…

I've generally found SqlServer's query optimizer to be a hellish nightmare of broken dreams, so if Postgres' is even worse I'm giving up and going back to flat files.

I found SQL Server's query optimiser to be magical, but it relies on its table statistics being somewhat correct. Every now and again they liked to suddenly become wrong enough that queries go from magical to catastrophic mush.

(Most recent version I've used was SQL Server 2008.)

Re: Oracle vs. PostgreSQL: First Glance

#65

Post by mancini0 »

catblast wrote: post #57
markab21 wrote: post #14

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

For one, it does things that the competition is not capable of or just inferior -- and despite the bias on this site, the major consumers of RDBMSes are not price sensitive scrappy startups. There is no comparison between the HA offerings in Oracle and something like Postgres, which are comparatively toys. Replication doesn't equate to HA and the "nobody got fired for buying xxx.." actually has some justification. Wh…

I would argue that using postgres today sets you up better for HA tomorrow, as both yugabyte and cockroachdb are built with postgresql compatability in mind. I would also argue your reputation as a competent CTO / Architect / whatever is more at risk by choosing Oracle in 2020.

Re: Oracle vs. PostgreSQL: First Glance

#66

Post by anarazel »

atombender wrote: post #47
zip1234 wrote: post #35

Earlier quoted context omitted.

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.

BRIN indexes are kind of bitmap like...

Re: Oracle vs. PostgreSQL: First Glance

#67

Post by miahi »

drdec wrote: post #39

> I am confident that anyone who works with Oracle often uses the (+) inside a query to simply force an outer join. For the love of all you hold sacred, please don't do this.

For some reason I find a query that uses (+) way easier to read than verbose outer joins. Probably because it's near the field and you see immediately "hey, this can be null".

Yes, it makes the query harder to migrate to other DMBS and to collaborate with non-Oracle persons.

Re: Oracle vs. PostgreSQL: First Glance

#68

Post by da_chicken »

revel wrote: post #55

Oracle used to be by far and away the best database out there. Now I wouldn’t use it even if you paid me. It’s shocking how little Oracle invested in developing their products and services over the years. They are a distant second, if not merely an “also ran”, for everything that they do. The company largely exists as an experiment in just how far you can go with a vendor lock-in strategy. Sadly that experiment is pr…

They're a victim of their own success. They became a monopoly and the quality of their product stopped mattering. They're an example of Steve Job's comments on Xerox's failure[0]. It happened at Oracle, IBM, Cisco, and Microsoft. It's happening now at Apple, Intel and Google.

[0]: https://youtu.be/NlBjNmXvqIM

Re: Oracle vs. PostgreSQL: First Glance

#69

Post by peterangular »

snarfy wrote: post #41
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.

Same - it's such a difficult technology to deal with when outstanding flavors of DB exist (many for free): Postgres, MySQL, MSSQL.

The Oracle projects I've worked on are just people choosing it because "it's the safe business decision and a household name". I've had semi-good luck convincing folks over to MSSQL in these circumstances which is a night-and-day improvement in ergonomics/features.

Re: Oracle vs. PostgreSQL: First Glance

#70

Post by philliphaydon »

miahi wrote: post #58
philliphaydon wrote: post #18

I haven't touched Oracle in like 12 years so I can't comment on that. But some of the examples are a bit strange or atleast lacking for PostgreSQL. For example, in the partitioning, he states: > SELECT * FROM sales_p_america; But doesn't mention that if you select based on a region, it will use only the partition table. > SELECT * FROM sales WHERE sales_region IN ('USA','CANADA'); While I believe if you do the equiv…

Oracle will also use partitioning optimizations in that case. See partition pruning[1]. [1] https://docs.oracle.com/en/database/oracle/oracle-database/1...

Awesome. Thanks for the info. I wasn’t sure if it was supported or not.
Post reply on HN