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

111–120 of 201 posts

Re: Oracle vs. PostgreSQL: First Glance

#111

Post by ibejoeb »

miahi wrote: post #67
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.

To make it terser, omit `outer` because it is redundant. Now it's up to whatever you find easier to type. Definitely `left` or `right` for me. (+) is a really awkward sequence on QWERTY, at least.

Re: Oracle vs. PostgreSQL: First Glance

#112

Post by ris »

ksec wrote: post #59
dtheodor wrote: post #38

Earlier quoted context omitted.

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…

For someone not following closely, What are the reasons behind the lack of work on JIT since then?

The traditional view that the bottleneck for query performance is overwhelmingly disk throughput, which has become slightly less true with the advent of SSDs.

Re: Oracle vs. PostgreSQL: First Glance

#113

Post by ibejoeb »

davio wrote: post #74

I've been at 3 separate companies where each respective CIO had "get rid of Oracle" as a strategic initiative.

If you're really only using it as a dumb datastore, it is kinda silly to not switch to Postgres. The migration in these cases is, relatively, simple.

If you're using any advanced features, migrating to anything else is going to be a risk-ridden project. Same goes for any other product, of course.

Re: Oracle vs. PostgreSQL: First Glance

#114

Post by takeda »

munk-a wrote: post #80
bjpirt wrote: post #22

I'm a happy Postgres user and recently did some work with a government agency using Oracle - the thing that shocked me most about Oracle was the lack of transactional DDL operations which was something I'd just taken for granted in the Postgres world.

Coming from MySQL to Postgres a few years back the transactional DDL statements were a joy to work with - I've had to claw a legacy into the modern era and utilizing them has allowed me to execute live migrations from legacy into shims and then from shims into modern. I also really appreciate the transactional TRUNCATE - I pretty much never use it but at least in Postgres I never have to worry about someone else tryi…

> I also really appreciate the transactional TRUNCATE - I pretty much never use it but at least in Postgres I never have to worry about someone else trying to run one and wiping state unexpectedly.

as long as auto commit is not enabled.

These goodies are possible, because of PostgreSQL's MVCC which requires running vacuum. Nothing is for free unfortunately.

Re: Oracle vs. PostgreSQL: First Glance

#115

Post by thda »

pjmlp wrote: post #102
thda wrote: post #97

Earlier quoted context omitted.

dbeaver and plugin_debugger. For distributed transactions, it all depends on why you need them in the first place but there are multiple solutions in postgres to handle them.

So some third party solution, and not first class support from the RDMS vendor. That is not the same as using Oracle.

Plugin_debugger is availlable on postgresql.org's repo. And supported by our postgresql support provider. You can of course use pgadmin, the official tool, with this plugin.

You should reassess your critics before posting, I think. The evolution of postgresql is faster and faster.

Re: Oracle vs. PostgreSQL: First Glance

#116

Post by thda »

sbuttgereit wrote: post #105
thda wrote: post #100

Earlier quoted context omitted.

> as far as I know no work has been done on it on 13 either. You should check the latest commitfest. Improvements are coming, at a steady pace.

Are you sure? I just made a very casual search of the most recently completed commitfest and I saw one entry ("JIT expression evaluation improvements") marked as "Moved to next CF" and in the upcoming one the only item planned was the one that was moved. Maybe there's more there with less obvious names?

I am. 13 release is planned in the 3rd quarter. The next commitfest is in july.

Re: Oracle vs. PostgreSQL: First Glance

#117

Post by unclebucknasty »

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?

I would say performance is still an issue in certain cases. We had some long-running queries with views and subqueries running on a down level version of PG. We recently attempted an upgrade to 12 to address this and take advantage of the latest RDS generations. The ideal architectural solution would be to sunset the views (involving unions) in favor of a better normalized database, but you do what you can when you inherit legacy. And we're not talking about millions of rows here. It's a relatively small installment.

Anyway, to our surprise, not only did it provide little boost, but other queries that were simpler and previously caused no issues became inexplicably and intolerably slow.

We did not have time to go down the rabbit hole of query planning and explains, and being a highly dynamically queriable app, we didn't know what else was in store for other queries based on some combination of user inputs down the road.

So we rolled back. PG has otherwise been great for us. It really is the performance issue in certain situations that has caused pain.

One more note is with regard to what I would call extreme performance variability based on row counts. What I mean is there seems to be some threshold that, once crossed, causes some queries to go from perfectly fine to near-interminable. You expect some degradation as row counts go up, but here the performance behavior suddenly degrades in a nonlinear fashion. That kind of issue is difficult to tune.

Re: Oracle vs. PostgreSQL: First Glance

#118

Post by forinti »

user5994461 wrote: post #106
zozbot234 wrote: post #52

Earlier quoted context omitted.

That's what we call throwing good money after bad. Besides, PostgreSQL has actually come a long way since 3 years ago or so. It's not a slow-moving project, especially for this space.

How often do developers recompile postgresql? How often do developers go through the hassle of upgrading databases versions? Whatever postgresql may have done recently, it won't be used and available in the common distro until a while later. Bear in mind that minor versions in postgresql are breaking changes. It does not follow semver.

I've gone through Oracle and Postgresql upgrades. It is dead easy to compile Postgresql.

Our last Oracle migration lasted about 2 weeks. When it was over, I decided to upgrade Postgresql too. Including compiling it, it didn't take an hour.

Re: Oracle vs. PostgreSQL: First Glance

#119

Post by henryfjordan »

user5994461 wrote: post #106
zozbot234 wrote: post #52

Earlier quoted context omitted.

That's what we call throwing good money after bad. Besides, PostgreSQL has actually come a long way since 3 years ago or so. It's not a slow-moving project, especially for this space.

How often do developers recompile postgresql? How often do developers go through the hassle of upgrading databases versions? Whatever postgresql may have done recently, it won't be used and available in the common distro until a while later. Bear in mind that minor versions in postgresql are breaking changes. It does not follow semver.

Many of the cloud providers make it relatively easy to upgrade and might even force you to do so after a while (so they can drop support for old versions).

Re: Oracle vs. PostgreSQL: First Glance

#120

Post by peterangular »

user5994461 wrote: post #104
peterangular wrote: post #69

Earlier quoted context omitted.

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.

I thought MSSQL could only come with windows servers (until last year). What is your experience with moving folks from Oracle / Linux to MSSQL / Windows? I too think MSSQL is the best contender to replace Oracle in many regards, but I don't see the change in operations going well, different skillset and sysadmins.

I mean - it's a nightmare and it hurts a lot. My experience is always lots of planning, refactoring the original schema, and working with SWEs directly on the code. As a SWE myself, the two major Oracle to MSSQL transitions have been heavily application-specific vs. other situations that may have a large number of downstream dependencies... I guess what I'm saying is my blast-radius was limited to 2-3 services vs. some of the nightmares I know other folks deal with here.

Given that my blast-radius was small, I was capable of doing a "from the ground up" approach by building data migration code by hand (ie: manually ripping through records, often validating them, doing any transformations, and writing them to the new DB target)... the largest DB I had to move like this was only 15Gb - I mention this because what I'm discussing isn't actually that impressive vs. your typical Oracle migration looks like!!! I was also incredibly lucky that the jobs I accepted were only had 2-3 years worth of data in there. People reading this are like "ha - easy mode" and they're 100% right.

Now that all this being said - I know my techniques are not ideal, and it's only one way to skin the cat... which is to replace the cat outright in an incredibly tedious manual technique. But hey, I still came in on-time and under-budget twice! I also had incredible confidence in the new DB target (MSSQL) as I literally touched/vetted everything by-hand. Stupid like a FOX!

Now if you can't do a hard cutover - all of this doesn't really apply. If I would have had to make a gradual transition I would leave it to someone better suited for the task.

> but I don't see the change in operations going well, different skillset and sysadmins

Oracle can be incredibly expensive or incredibly cheap... In my situation it was small-to-medium sized companies doing simple logistics stuff. It was incredibly cost-effective to replace it with an easier-to-use DB and most were happy to invite the change + learn new tooling.

It also was a HUGE productivity boost to the devs... I don't think people realize just how dumb Oracle/Oracle products are when it comes to simply "building something". To the extent that I find Oracle's product offering to be truly offensive, as-in it offends me as an engineer that someone would think to hand me such a non-ideal tool in 2020 ugh.

Post reply on HN