30 ms·

Oracle vs. PostgreSQL: First Glance

180 points by takacsroland 6y ago

jcadam 6y ago
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 features). The development experience with Oracle was also awful. The licensing for Oracle was highly granular, down to the feature level. More than once I'd identify a feature that provided a solution to an issue through online research only to be prevented from using it due to the customer not having the requisite license for it. And the support was useless. Oracle was so complex (by design) we resorted to contacting support a couple of times - they would send out an "engineer" who could turn any technical troubleshooting session into a sales presentation for some Oracle product or feature that would "solve" whatever the issue was. I will never work on a project involving Oracle again (barring obscene amounts of money to assuage my frustration, of course).
- dx034 6y ago
  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 right way, reducing development time by quite a bit.
  - rpedela 6y ago
    PG community has put a lot effort into performance the last few years, including JIT compilation in PG12. Is that criticism still true today?
    - asah 6y ago
      I'd really like to know this as well !!
    - ibejoeb 6y ago
      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-materialize/ https://paquier.xyz/postgresql-2/postgres-12-with-materializ...
      - doublesCs 6y ago
        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).
        dtheodor 6y ago
        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 https://www.postgresql.org/docs/12/using-explain.html
        deleted 6y ago
        [deleted]
        da_chicken 6y ago
        Here "materialization" just means that the query planner treats it as an optimization fence. That means the system doesn't do any optimization between the CTE and the query referencing it. It would prepare the output of the CTE as a completely separate entity essentially as if you had dumped it to a temp table. It may not be written to disk if there was sufficient memory, but either way you're sacrificing any optimization. I believe that it would even materialize the CTE multiple times if it was referenced multiple times, but don't quote me on that. On the other hand, if the same query were written with subqueries instead of with CTEs, then the query optimizer would not treat it as an optimization fence. If it could utilize indexes or rewrite the query to be relationally equivalent, it would do so. Note that sometimes that optimization fence is beneficial. There are situations where it's better to create temp tables and run smaller simpler queries instead of running an extremely complex monolithic query because the query planner isn't perfect even with hints. You can still enable that optimization fence functionality in PostgreSQL if you need to, but it's generally pretty rare that it happens like this. Still, you'll see stored procedures for reports still using temp tables and even cursors sometimes because they can be made to perform better in certain situations.
  - jcadam 6y ago
    Specifically for geospatial queries we found Oracle Spatial to be inferior to PostGIS (which is much more widely used). Regular relational queries seemed to be no worse (admittedly, our needs in a database were somewhat modest in that regard). Though, most of our team's expertise as far as databases went was in Postgres (including our DBA).
  - Pxtl 6y ago
    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.
    - carlosf 6y ago
      You might even call it a Data Lake and get away with it.
    - da_chicken 6y ago
      I've found that SQL Server's is generally pretty good, with the big pitfall being the system's timeout for the query planner/compiler. Query compilation timeouts can be really frustrating to work on because, often, the query's complexity is a requirement. The only other problem is the parameter sniffing problem for stored procedures, although OPTIMIZE FOR UNKNOWN or specified values seem to work fairly well in my experience, though obviously not always. The real failing is that a common solution to a view query hitting the compiler timeout is to replace it with a stored procedure of some kind. However, if you're not careful you'll run into the parameter sniffing problem with stored procedures! So you run into one caveat and your attempted solution runs into the other one.
    - sjwright 6y ago
      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.)
      - ants_a 6y ago
        All cost based optimisers rely on statistics being correct. And inevitably there will be a case where they aren't. The problem with PostgreSQLs optimiser, and I assume with others too, is that it's too risk happy. It's optimizing for the average case based on the statistics, when people actually tend to care about the worst case. As an example, say you have a database of all cars ever produced with indexes on model and production date. If you are looking for the latest Ford F-150 then the best plan is to just start looking backwards by date and you will find one soon enough. Much faster than looking up all F-150s and picking the latest one. On the other hand, if you are looking for latest Ford Model-T, that plan is going to be catastrophically terrible, going through 93 years of car production before finding the correct one.
  - ashtonkem 6y ago
    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.
    - AmericanChopper 6y ago
      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.
      - ashtonkem 6y ago
        To be fair, I have no idea how hard it was to manage Oracle vs. how much my DBA just liked to gripe. But I largely agree with you, except I would lean towards a managed service because I’m no DBA.
  - zip1234 6y ago
    Does PostgreSQL have bitmap indexes. Very much a great feature that Oracle has for query performance
    - fanf2 6y ago
      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://leopard.in.ua/2015/04/13/postgresql-indexes - https://www.postgresql.org/docs/current/indexes-bitmap-scans.html https://www.postgresql.org/docs/current/indexes-bitmap-scans...
    - atombender 6y ago
      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.
      - anarazel 6y ago
        BRIN indexes are kind of bitmap like...
  - lukeschlather 6y ago
    It's very convenient for Oracle that anyone making these sorts of claims is contractually barred from sharing any evidence.
- drchopchop 6y ago
  I've spent a fair amount of time at the Sofitel in Redwood Shores, and I'd often chat with Oracle "sales engineers" at the bar. A simple "so, what do you work on" question would inevitably generate an hour's worth of them explaining some convoluted acronym-heavy product with a pushy sales model, and I'd eventually be like "ok... so it's a database?".
  - arethuza 6y ago
    To be fair to Oracle (not something I say very often) - I suspect most of their sales are business applications (ERP, CRM, financial) that happen to use their database engine as a back end.
    - deleted 6y ago
      [deleted]
    - cryptonector 6y ago
      At Oracle everything uses Oracle DBs. Their bug DB is a thin layer on top of an Oracle DB. Their email server too. Everything that can be a thin layer on top of Oracle... is.
- forinti 6y ago
  You could connect Oracle to Postgresql using HA and from Postgresql to Oracle using FDW. If your client was paying Oracle already, he should take steps to move away from it, not setup himself up for paying licences forever. I cringe at having to call Oracle Support. It takes forever and they make you send a ton of files before someone even looks at it.
  - simonebrunozzi 6y ago
    > he should take steps to move away from it Unless - as it often happens - someone in high places need to keep justifying a business decision taken 1-2-3 years before.
    - zozbot234 6y ago
      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.
      - user5994461 6y ago
        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.
        forinti 6y ago
        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.
        henryfjordan 6y ago
        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).
- maxdo 6y ago
  I think it's a company culture overall. The same experience with Oracle cloud, I saw low prices, decided to try... Their Kubernetes engine was purely horrible not even alpha comparing to Google offering. My bare-metal installation was much more mature.
- snarfy 6y ago
  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.
  - folkhack 6y ago
    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.
    - user5994461 6y ago
      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.
      - takeda 6y ago
        EnterpriseDB seems like PostgreSQL version that can emulate most Oracle's features, although I would personally use it as a step in migrating (i.e. move from Oracle to EDB, then gradually convert your data to be PostgreSQL only, once done move to pure PostgreSQL)
        boshomi 6y ago
        With EDB ships a full featured PostgreSQL with some additional useful time-savers and a lot of experience for a fair amount of money. A part of this money goes direct into PostgreSQL development. So it's a fair deal.
        takeda 6y ago
- thijsvandien 6y ago
  Ah, the Sunk Cost Fallacy at its finest.
- takacsroland 6y ago
  Thanks, based on everyone's comment it seems it is a great decision to migrate to Postgre. Cool!
  - pasha_golub 6y ago
    Postgres or PostgreSQL... Yeah, I know :D https://wiki.postgresql.org/wiki/FAQ#What_is_PostgreSQL.3F_How_is_it_pronounced.3F_What_is_Postgres.3F https://wiki.postgresql.org/wiki/FAQ#What_is_PostgreSQL.3F_H...
- corpMaverick 6y ago
  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.
  - takacsroland 6y ago
    After all these comments, I am getting just happier that we will migrate. :)
dx034 6y ago
> -- 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 https://www.postgresql.org/docs/current/sql-insert.html
- takacsroland 6y ago
  You are right, I already corrected this. Thanks.
MrHamdulay 6y ago
Why are the table and field names in the examples between Oracle and PostgreSQL different? It makes it harder to compare the two.
- zozbot234 6y ago
  IIRC, Oracle licensing forbids publishing direct comparisons with competing products. I guess they had to find a workaround.
  - jordigh 6y ago
    I wish antitrust legislation in the US was brought back or enforced. Forbidding direct comparisons is an anticompetitive practice if I've ever seen one.
  - defnotashton2 6y ago
    This kind of model speaks to who actually buys it, I would never pay for a product with such limitations out of principle.
    - paulmd 6y ago
      Microsoft SQL Server has the same clause in their license, unfortunately. https://www.brentozar.com/archive/2018/05/the-dewitt-clause-why-you-rarely-see-database-benchmarks/ https://www.brentozar.com/archive/2018/05/the-dewitt-clause-... So, you wouldn't be considering any commercial SQL offering, basically.
  - ajuc 6y ago
    In my headcanon Dilbert works at Oracle.
jonahbenton 6y ago
Hmm, looks like EnterpriseDB is still a thing: https://www.enterprisedb.com/ https://www.enterprisedb.com/
markab21 6y ago
Why anyone would use Oracle for anything other than supporting legacy systems is beyond me.
- wil421 6y ago
  Oracle and SAP have products that touch niche areas of businesses. Oil company with complex shipping and receiving looking for accounting software? Global metal foundry who needs to track raw materials to finished goods and forecast everything? SAP and Oracle can sell your VP overly complex products for almost anything. For the DB, corporate executives types feel much more comfortable choosing Oracle or IBM. It usually bites them in the ass down the road due to licensing or support costs.
  - ibejoeb 6y ago
    This is certainly all true, and with Oracle, absolutely everything is negotiable. Nobody pays list. It still isn't cheap. If you are going the Oracle route, you might even consider hiring a consultant to do the buying, because relationships and knowing the Oracle way can make a huge difference. Also, Oracle Database itself is more than just an RDBMS and has an enormous amount of features that have no analogs in Postgres or any other non-commercial system. Take a look Oracle's data warehousing components, like advanced analytical SQL, pattern matching, and the especially cool modeling: https://docs.oracle.com/database/121/DWHSG/sqlmodel.htm#DWHSG8762 https://docs.oracle.com/database/121/DWHSG/sqlmodel.htm#DWHS...
- F_J_H 6y ago
  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 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). YRMV, but it has been incredible for us, both to support our data science initiatives, and for rapidly deploying applications. I couldn't imagine going back to anything else.
sbuttgereit 6y ago
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, but that's different than a copy). I'm also uncomfortable with too simply stating that you should think of this like OOP inheritance; while I agree that in some respects there's passing similarity, it is its own beast and needs to be understood outside of the OOP paradigm to be useful. Many of the Object Relational aspects of PostgreSQL are very powerful, but can not be understood in OOP terms. For inheritance, it's better to read about this from the documentation: https://www.postgresql.org/docs/12/tutorial-inheritance.html https://www.postgresql.org/docs/12/tutorial-inheritance.html Also, another part of the article talks about the ramifications of not having Oracle "packages". So while it's not completely the same concept and there are different sets of trade-offs, one option includes using PostgreSQL schema for this sort of logical namespace organization. Both Oracle and PostgreSQL have the concept of different schemas, but Oracle has a much more rigid idea about schema usage (related to database users) and PostgreSQL has a much more fluid idea about usage. As a former Oracle guy, I can see how that organizational tool might not be front of mind when coming to PostgreSQL, but I've used PostgreSQL schema for this sort of organizational purpose with good success.
- takacsroland 6y ago
  Thanks, I will check out your input on table inheritance and update it.
philliphaydon 6y ago
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 in Oracle it wont use the partition table? --- The section on table inheritance isn't right either. https://www.postgresql.org/docs/12/tutorial-inheritance.html https://www.postgresql.org/docs/12/tutorial-inheritance.html What he demonstrated was just a way of making additional tables based on existing ones. While inheritance works sort of like partitioning except the child tables can contain additional data. Selecting from the parent will display all data from the child.
- miahi 6y ago
  Oracle will also use partitioning optimizations in that case. See partition pruning[1]. [1] https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/partition-pruning.html https://docs.oracle.com/en/database/oracle/oracle-database/1...
  - philliphaydon 6y ago
    Awesome. Thanks for the info. I wasn’t sure if it was supported or not.
- takacsroland 6y ago
  Oracle will also use the partitions. Regarding your other input, thanks, I will have a look.
ajuc 6y ago
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 migration again. Easy. In comparison writing migration scripts on Oracle is a nightmare - DDLs aren't transactional (THEY COMMIT ON EACH LINE...), so you have to separate them from DMLs and ensure that only the scripts that haven't passed yet are re-run later. I've worked in 3 different companies that used oracle, and there were 3 different approaches to that problem, and all 3 of them sucked :) In one company we had several big customers each with 1 production db, and software was written on separate branches for each customer, and helpdesk staff was dealing with migrations - programmers just asked helpdesk to add a column and worked on the test db for that customer. It was a lot of unnecessary work to port changes and bugfixes between branches, but at least we knew exactly what is on each db and could fix problems by ourselves. There was no migration to speak of, just manual changes on dbs and documenting them in svn (it was before git was popular). In another company there was one development branch and several customers, and there were migration scripts written by all developers when they made changes, which were merged into development branch for db by 1 guy whose whole job was to merge these scripts and check if migration works. It slowed down development (because when you finished your task on local db you had to make a migration script(s) and send them to be verified. And even "that guy" sometimes made mistakes and then if you fetched db scripts in the morning you couldn't work until stuff was fixed (or you had to recreate oracle db from scratch which took several hours). That was before docker BTW, now they probably use docker so that can be less of a problem. In the third company we had one customer but with hundreds of installations, and we had one development branch with frequent releases. Developers maintained migration scripts between release, major and minor versions. There was no "that guy" - we had smoke tests instead, and it sometimes took more time to write that migration script(s) than to change the code. So you want to add 3 columns to 3 tables and fill them? And it has to be done in order because of dependencies? Write no less than 6 migration scripts (alter table 1, update table 1, alter table 2, ...). Add them with proper names and some boilerplate to the migration scripts for minor versions (3.4.5 -> 3.4.6). But that's not all! We also have migration scripts for major versions (3.4.0 -> 3.5.0), so you also need to add them there. You have to check the migration separately because these scripts often use shortcuts to run faster. So your scripts might break despite working for minor version migration. Then there's the scripts for release version migration (3.0.0->4.0.0). Add your scripts there as well, and test once again. Oh, and testing these scripts on test data doesn't mean they will work - each installation of db changes slightly over time - people add stuff from ui. There are rules what they can change and what they cannot, but if you don't think about it you might break something with your migration scripts on production despite it working on test data. When that happens you have to write migration fixes which need to detect that problem and fix it on data you don't have direct access to :) It was a nightmare. Meanwhile Postgress is just doing the right thing, write 1 migration script with everything in it, if it works it works, if not - it rollbacks. Nobody thinks twice about it.
bjpirt 6y ago
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.
- munk-a 6y ago
  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 trying to run one and wiping state unexpectedly.
  - takeda 6y ago
    > 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.
    - ants_a 6y ago
      I don't think undo vs. heap based MVCC choice has any big impact with regards to transactional truncate, nor transactional DDL in general. You are likely to see proof of that within a couple of years.
    - jsmith45 6y ago
      Interestingly even though MSSQL server uses an extremely different implementation of MVCC, it internally has a vacuum equivalent. (Which is required even when all MVCC support is disabled! It is used to enable efficient implementation of deletes, without having to use absurdly coarse locks). MSSQL just handles doing that cleanup silently in the background while exposing basically no no configuration except a trace flag that can turn it off.
stuff4ben 6y ago
Great article if I ever get back into DB-based development again...
devit 6y ago
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 support.
- sbuttgereit 6y ago
  You mean like... https://www.enterprisedb.com/enterprise-postgres/database-compatibility-oracle 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.
- teilo 6y ago
  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?
- outworlder 6y ago
  > It can't be that much work, Seriously? Syntax is always the trivial part, everywhere you look. Semantics is what bites you.
trollied 6y ago
One RDBMS that I don't see mentioned much is Tibero https://www.tmaxsoft.com/products/tibero/ https://www.tmaxsoft.com/products/tibero/ It's a clone of Oracle & I'm surprised that Oracle legal have never tried to splat it! Interesting blog post discussing it here: https://www.tmaxsoft.com/products/tibero/ https://www.tmaxsoft.com/products/tibero/
- fl0wenol 6y ago
  That's not a blog post, that's just marketing material.
  - trollied 6y ago
    Damn, posted the wrong link by accident. Wondered why I was getting downvoted. The link I meant to post was https://blog.dbi-services.com/tibero-i/ https://blog.dbi-services.com/tibero-i/
drdec 6y ago
> 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.
- miahi 6y ago
  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.
  - ibejoeb 6y ago
    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.
revel 6y ago
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 proving to be a remarkably successful one
- da_chicken 6y ago
  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 https://youtu.be/NlBjNmXvqIM
  - toyg 6y ago
    I agree on the overall theory (dominance in a sector tends to shift internal incentives in such a way that the result is an ossified development structure), but I think Jobs' terminology is imprecise. Larry Ellison is not really a product guy first and foremost, he's the definition of a tough salesman. The "bad guys" are a more generic variety of "corporate type" who can materialize in any department, really. Typically they power themselves up the ladder with "cost efficiencies". The most recent Oracle CEOs (Hurd and Catz) fit that profile to a T. En passant: another big name that suffered from this phenomenon was Nokia.
  - cryptonector 6y ago
    I've a feeling that Oracle succeeded by accident. They won the race to win mindshare without understanding that that's what they were doing, and have since rested on their laurels and high pressure salesmanship. You see their lack of attention to mindshare in... everything they do.
masklinn 6y ago
> IF EXISTS for DDL operations It's a super convenient operation but it has one big drawback which might be unexpected: `if exists` first acquires the relevant lock then checks. This means a "ALTER TABLE table_name DROP COLUMN IF EXISTS column_name" will first acquire an ACCESS EXCLUSIVE lock, then check if the column exist. Since DDL is transactional the lock will not be released until the transaction is committed or rollbacked, therefore even if the column doesn't exist it will prevent all concurrent operations on the table.
ksec 6y ago
Whenever I see comment making comparison between Oracle and Postgre, I cant help bug wonder why isn't it compared to Enterprise DB, which is sort of like the unofficially official Postgre for Enterprise products.
- pasha_golub 6y ago
  Postgres! You're not saying Orac, aren't you? :)
davio 6y ago
I've been at 3 separate companies where each respective CIO had "get rid of Oracle" as a strategic initiative.
- davidgerard 6y ago
  Absolutely the best part of moving from Oracle to PG is never worrying about licensing ever again. PG does 99% of what Oracle does. If you have one of the 1% cases, that will be a remarkable circumstance. Always test a move from Oracle to PG, see how it performs.
- tandr 6y ago
  It mirrors my (anecdotal) experience in the last 2 companies that were dependent on it too. With that said... Makes me wonder how many (if any) companies are moving opposite direction that Oracle survives and doing so nicely.
- ibejoeb 6y ago
  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.
- aserafini 6y ago
  Amazon even made a promotional video when they shut down their last Oracle database https://m.youtube.com/watch?v=9yBP5gnnZi4 https://m.youtube.com/watch?v=9yBP5gnnZi4 Perhaps it was retaliation for Larry’s comments about Amazon in this interview https://m.youtube.com/watch?v=xrzMYL901AQ https://m.youtube.com/watch?v=xrzMYL901AQ
- takacsroland 6y ago
  Thanks for everyone's comment, it really looks like migrating will be one of the best decisions. :)
CodeSheikh 6y ago
As a dev you would use Oracle only if your execs have cut a sweet licensing deal with the Oracle.
- takacsroland 6y ago
  :)
krakatau1 6y ago
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 or MSSQL, use them for superior performance. Migration off them is costly and risky process. If your working at a startup there is absolutely no reason to choose anything but Postgres if you need relational.
- takacsroland 6y ago
  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?
  - outworlder 6y ago
    YMMV. That depends on your workload, how your data is structured and a million other things. I know a few shops with heavy usage that couldn't be happier. It may require handholding if you are not happy with the plans it is generating. Also the quality of the query planner results will depend a lot on how up to date the statistics are.
    - takacsroland 6y ago
      I see, thanks!
  - tomnipotent 6y ago
    > 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.
- derefr 6y ago
  I've never understood why RDBMSes don't offer a low-level protocol where, rather than a SQL statement, you can just send over the exact query-plan AST you want the DB to prepare in some binary encoding. Then you could pre-compile your hot OLTP queries and heavy OLAP reports offline against your existing DB schema, using the same sort of techniques that got Lucene its optimized Levenstein-automata JVM bytecode. You could even tweak the resulting plan, op by op, before letting it go to the DB—as if you were doing final ASM tweaks on a game in the 90s.
iracic 6y ago
Some good points in article. There are some things that may need more attention. 1) Update from another table is not safe as it should be (in case of multiple values, final value will be sort of random) 2) Schemas as namespace separators (grouping tables inside database) 3) Extern join syntax in Oracle is actually more vulnerable (in case of error in multicolumn syntax it fallbacks to normal join). So, it's not better or easier - it is just created before standard JOIN existed. 4) Crucial difference how buffer-vs-filesystem cache works 5) Miss of plan stability - no solution out of the box in standard installation 6) Batch operation (in)efficiency 7) Pros/cons in undo/rollback handling [likely some more that can't think of right now]
simonebrunozzi 6y ago
I would bet the next "Oracle" (as in, large IT company) of the decade 2020-2030 will be based on PostgreSQL. Can't see an obvious candidate, yet. Perhaps someone has seen some interesting companies heading in this direction?

180 points by takacsroland 6y ago

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 features). The development experience with Oracle was also awful. The licensing for Oracle was highly granular, down to the feature level. More than once I'd identify a feature that provided a solution to an issue through online research only to be prevented from using it due to the customer not having the requisite license for it. And the support was useless. Oracle was so complex (by design) we resorted to contacting support a couple of times - they would send out an "engineer" who could turn any technical troubleshooting session into a sales presentation for some Oracle product or feature that would "solve" whatever the issue was. I will never work on a project involving Oracle again (barring obscene amounts of money to assuage my frustration, of course).

dx034 6y ago
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 right way, reducing development time by quite a bit.
- rpedela 6y ago
  PG community has put a lot effort into performance the last few years, including JIT compilation in PG12. Is that criticism still true today?
  - asah 6y ago
    I'd really like to know this as well !!
  - ibejoeb 6y ago
    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-materialize/ https://paquier.xyz/postgresql-2/postgres-12-with-materializ...
    - doublesCs 6y ago
      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).
      - dtheodor 6y ago
        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 https://www.postgresql.org/docs/12/using-explain.html
      - deleted 6y ago
        [deleted]
      - da_chicken 6y ago
        Here "materialization" just means that the query planner treats it as an optimization fence. That means the system doesn't do any optimization between the CTE and the query referencing it. It would prepare the output of the CTE as a completely separate entity essentially as if you had dumped it to a temp table. It may not be written to disk if there was sufficient memory, but either way you're sacrificing any optimization. I believe that it would even materialize the CTE multiple times if it was referenced multiple times, but don't quote me on that. On the other hand, if the same query were written with subqueries instead of with CTEs, then the query optimizer would not treat it as an optimization fence. If it could utilize indexes or rewrite the query to be relationally equivalent, it would do so. Note that sometimes that optimization fence is beneficial. There are situations where it's better to create temp tables and run smaller simpler queries instead of running an extremely complex monolithic query because the query planner isn't perfect even with hints. You can still enable that optimization fence functionality in PostgreSQL if you need to, but it's generally pretty rare that it happens like this. Still, you'll see stored procedures for reports still using temp tables and even cursors sometimes because they can be made to perform better in certain situations.
- jcadam 6y ago
  Specifically for geospatial queries we found Oracle Spatial to be inferior to PostGIS (which is much more widely used). Regular relational queries seemed to be no worse (admittedly, our needs in a database were somewhat modest in that regard). Though, most of our team's expertise as far as databases went was in Postgres (including our DBA).
- Pxtl 6y ago
  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.
  - carlosf 6y ago
    You might even call it a Data Lake and get away with it.
  - da_chicken 6y ago
    I've found that SQL Server's is generally pretty good, with the big pitfall being the system's timeout for the query planner/compiler. Query compilation timeouts can be really frustrating to work on because, often, the query's complexity is a requirement. The only other problem is the parameter sniffing problem for stored procedures, although OPTIMIZE FOR UNKNOWN or specified values seem to work fairly well in my experience, though obviously not always. The real failing is that a common solution to a view query hitting the compiler timeout is to replace it with a stored procedure of some kind. However, if you're not careful you'll run into the parameter sniffing problem with stored procedures! So you run into one caveat and your attempted solution runs into the other one.
  - sjwright 6y ago
    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.)
    - ants_a 6y ago
      All cost based optimisers rely on statistics being correct. And inevitably there will be a case where they aren't. The problem with PostgreSQLs optimiser, and I assume with others too, is that it's too risk happy. It's optimizing for the average case based on the statistics, when people actually tend to care about the worst case. As an example, say you have a database of all cars ever produced with indexes on model and production date. If you are looking for the latest Ford F-150 then the best plan is to just start looking backwards by date and you will find one soon enough. Much faster than looking up all F-150s and picking the latest one. On the other hand, if you are looking for latest Ford Model-T, that plan is going to be catastrophically terrible, going through 93 years of car production before finding the correct one.
- ashtonkem 6y ago
  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.
  - AmericanChopper 6y ago
    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.
    - ashtonkem 6y ago
      To be fair, I have no idea how hard it was to manage Oracle vs. how much my DBA just liked to gripe. But I largely agree with you, except I would lean towards a managed service because I’m no DBA.
- zip1234 6y ago
  Does PostgreSQL have bitmap indexes. Very much a great feature that Oracle has for query performance
  - fanf2 6y ago
    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://leopard.in.ua/2015/04/13/postgresql-indexes - https://www.postgresql.org/docs/current/indexes-bitmap-scans.html https://www.postgresql.org/docs/current/indexes-bitmap-scans...
  - atombender 6y ago
    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.
    - anarazel 6y ago
      BRIN indexes are kind of bitmap like...
- lukeschlather 6y ago
  It's very convenient for Oracle that anyone making these sorts of claims is contractually barred from sharing any evidence.
drchopchop 6y ago
I've spent a fair amount of time at the Sofitel in Redwood Shores, and I'd often chat with Oracle "sales engineers" at the bar. A simple "so, what do you work on" question would inevitably generate an hour's worth of them explaining some convoluted acronym-heavy product with a pushy sales model, and I'd eventually be like "ok... so it's a database?".
- arethuza 6y ago
  To be fair to Oracle (not something I say very often) - I suspect most of their sales are business applications (ERP, CRM, financial) that happen to use their database engine as a back end.
  - deleted 6y ago
    [deleted]
  - cryptonector 6y ago
    At Oracle everything uses Oracle DBs. Their bug DB is a thin layer on top of an Oracle DB. Their email server too. Everything that can be a thin layer on top of Oracle... is.
forinti 6y ago
You could connect Oracle to Postgresql using HA and from Postgresql to Oracle using FDW. If your client was paying Oracle already, he should take steps to move away from it, not setup himself up for paying licences forever. I cringe at having to call Oracle Support. It takes forever and they make you send a ton of files before someone even looks at it.
- simonebrunozzi 6y ago
  > he should take steps to move away from it Unless - as it often happens - someone in high places need to keep justifying a business decision taken 1-2-3 years before.
  - zozbot234 6y ago
    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.
    - user5994461 6y ago
      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.
      - forinti 6y ago
        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.
      - henryfjordan 6y ago
        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).
maxdo 6y ago
I think it's a company culture overall. The same experience with Oracle cloud, I saw low prices, decided to try... Their Kubernetes engine was purely horrible not even alpha comparing to Google offering. My bare-metal installation was much more mature.
snarfy 6y ago
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.
- folkhack 6y ago
  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.
  - user5994461 6y ago
    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.
    - takeda 6y ago
      EnterpriseDB seems like PostgreSQL version that can emulate most Oracle's features, although I would personally use it as a step in migrating (i.e. move from Oracle to EDB, then gradually convert your data to be PostgreSQL only, once done move to pure PostgreSQL)
      - boshomi 6y ago
        With EDB ships a full featured PostgreSQL with some additional useful time-savers and a lot of experience for a fair amount of money. A part of this money goes direct into PostgreSQL development. So it's a fair deal.
        takeda 6y ago
thijsvandien 6y ago
Ah, the Sunk Cost Fallacy at its finest.
takacsroland 6y ago
Thanks, based on everyone's comment it seems it is a great decision to migrate to Postgre. Cool!
- pasha_golub 6y ago
  Postgres or PostgreSQL... Yeah, I know :D https://wiki.postgresql.org/wiki/FAQ#What_is_PostgreSQL.3F_How_is_it_pronounced.3F_What_is_Postgres.3F https://wiki.postgresql.org/wiki/FAQ#What_is_PostgreSQL.3F_H...
corpMaverick 6y ago
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.
- takacsroland 6y ago
  After all these comments, I am getting just happier that we will migrate. :)

dx034 6y ago

> -- 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 https://www.postgresql.org/docs/current/sql-insert.html

takacsroland 6y ago
You are right, I already corrected this. Thanks.

MrHamdulay 6y ago

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

zozbot234 6y ago
IIRC, Oracle licensing forbids publishing direct comparisons with competing products. I guess they had to find a workaround.
- jordigh 6y ago
  I wish antitrust legislation in the US was brought back or enforced. Forbidding direct comparisons is an anticompetitive practice if I've ever seen one.
- defnotashton2 6y ago
  This kind of model speaks to who actually buys it, I would never pay for a product with such limitations out of principle.
  - paulmd 6y ago
    Microsoft SQL Server has the same clause in their license, unfortunately. https://www.brentozar.com/archive/2018/05/the-dewitt-clause-why-you-rarely-see-database-benchmarks/ https://www.brentozar.com/archive/2018/05/the-dewitt-clause-... So, you wouldn't be considering any commercial SQL offering, basically.
- ajuc 6y ago
  In my headcanon Dilbert works at Oracle.

jonahbenton 6y ago

Hmm, looks like EnterpriseDB is still a thing: https://www.enterprisedb.com/ https://www.enterprisedb.com/

markab21 6y ago

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

wil421 6y ago
Oracle and SAP have products that touch niche areas of businesses. Oil company with complex shipping and receiving looking for accounting software? Global metal foundry who needs to track raw materials to finished goods and forecast everything? SAP and Oracle can sell your VP overly complex products for almost anything. For the DB, corporate executives types feel much more comfortable choosing Oracle or IBM. It usually bites them in the ass down the road due to licensing or support costs.
- ibejoeb 6y ago
  This is certainly all true, and with Oracle, absolutely everything is negotiable. Nobody pays list. It still isn't cheap. If you are going the Oracle route, you might even consider hiring a consultant to do the buying, because relationships and knowing the Oracle way can make a huge difference. Also, Oracle Database itself is more than just an RDBMS and has an enormous amount of features that have no analogs in Postgres or any other non-commercial system. Take a look Oracle's data warehousing components, like advanced analytical SQL, pattern matching, and the especially cool modeling: https://docs.oracle.com/database/121/DWHSG/sqlmodel.htm#DWHSG8762 https://docs.oracle.com/database/121/DWHSG/sqlmodel.htm#DWHS...
F_J_H 6y ago
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 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). YRMV, but it has been incredible for us, both to support our data science initiatives, and for rapidly deploying applications. I couldn't imagine going back to anything else.

sbuttgereit 6y ago

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, but that's different than a copy). I'm also uncomfortable with too simply stating that you should think of this like OOP inheritance; while I agree that in some respects there's passing similarity, it is its own beast and needs to be understood outside of the OOP paradigm to be useful. Many of the Object Relational aspects of PostgreSQL are very powerful, but can not be understood in OOP terms. For inheritance, it's better to read about this from the documentation: https://www.postgresql.org/docs/12/tutorial-inheritance.html https://www.postgresql.org/docs/12/tutorial-inheritance.html Also, another part of the article talks about the ramifications of not having Oracle "packages". So while it's not completely the same concept and there are different sets of trade-offs, one option includes using PostgreSQL schema for this sort of logical namespace organization. Both Oracle and PostgreSQL have the concept of different schemas, but Oracle has a much more rigid idea about schema usage (related to database users) and PostgreSQL has a much more fluid idea about usage. As a former Oracle guy, I can see how that organizational tool might not be front of mind when coming to PostgreSQL, but I've used PostgreSQL schema for this sort of organizational purpose with good success.

takacsroland 6y ago
Thanks, I will check out your input on table inheritance and update it.

philliphaydon 6y ago

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 in Oracle it wont use the partition table? --- The section on table inheritance isn't right either. https://www.postgresql.org/docs/12/tutorial-inheritance.html https://www.postgresql.org/docs/12/tutorial-inheritance.html What he demonstrated was just a way of making additional tables based on existing ones. While inheritance works sort of like partitioning except the child tables can contain additional data. Selecting from the parent will display all data from the child.

miahi 6y ago
Oracle will also use partitioning optimizations in that case. See partition pruning[1]. [1] https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/partition-pruning.html https://docs.oracle.com/en/database/oracle/oracle-database/1...
- philliphaydon 6y ago
  Awesome. Thanks for the info. I wasn’t sure if it was supported or not.
takacsroland 6y ago
Oracle will also use the partitions. Regarding your other input, thanks, I will have a look.

ajuc 6y ago

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 migration again. Easy. In comparison writing migration scripts on Oracle is a nightmare - DDLs aren't transactional (THEY COMMIT ON EACH LINE...), so you have to separate them from DMLs and ensure that only the scripts that haven't passed yet are re-run later. I've worked in 3 different companies that used oracle, and there were 3 different approaches to that problem, and all 3 of them sucked :) In one company we had several big customers each with 1 production db, and software was written on separate branches for each customer, and helpdesk staff was dealing with migrations - programmers just asked helpdesk to add a column and worked on the test db for that customer. It was a lot of unnecessary work to port changes and bugfixes between branches, but at least we knew exactly what is on each db and could fix problems by ourselves. There was no migration to speak of, just manual changes on dbs and documenting them in svn (it was before git was popular). In another company there was one development branch and several customers, and there were migration scripts written by all developers when they made changes, which were merged into development branch for db by 1 guy whose whole job was to merge these scripts and check if migration works. It slowed down development (because when you finished your task on local db you had to make a migration script(s) and send them to be verified. And even "that guy" sometimes made mistakes and then if you fetched db scripts in the morning you couldn't work until stuff was fixed (or you had to recreate oracle db from scratch which took several hours). That was before docker BTW, now they probably use docker so that can be less of a problem. In the third company we had one customer but with hundreds of installations, and we had one development branch with frequent releases. Developers maintained migration scripts between release, major and minor versions. There was no "that guy" - we had smoke tests instead, and it sometimes took more time to write that migration script(s) than to change the code. So you want to add 3 columns to 3 tables and fill them? And it has to be done in order because of dependencies? Write no less than 6 migration scripts (alter table 1, update table 1, alter table 2, ...). Add them with proper names and some boilerplate to the migration scripts for minor versions (3.4.5 -> 3.4.6). But that's not all! We also have migration scripts for major versions (3.4.0 -> 3.5.0), so you also need to add them there. You have to check the migration separately because these scripts often use shortcuts to run faster. So your scripts might break despite working for minor version migration. Then there's the scripts for release version migration (3.0.0->4.0.0). Add your scripts there as well, and test once again. Oh, and testing these scripts on test data doesn't mean they will work - each installation of db changes slightly over time - people add stuff from ui. There are rules what they can change and what they cannot, but if you don't think about it you might break something with your migration scripts on production despite it working on test data. When that happens you have to write migration fixes which need to detect that problem and fix it on data you don't have direct access to :) It was a nightmare. Meanwhile Postgress is just doing the right thing, write 1 migration script with everything in it, if it works it works, if not - it rollbacks. Nobody thinks twice about it.

bjpirt 6y ago

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.

munk-a 6y ago
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 trying to run one and wiping state unexpectedly.
- takeda 6y ago
  > 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.
  - ants_a 6y ago
    I don't think undo vs. heap based MVCC choice has any big impact with regards to transactional truncate, nor transactional DDL in general. You are likely to see proof of that within a couple of years.
  - jsmith45 6y ago
    Interestingly even though MSSQL server uses an extremely different implementation of MVCC, it internally has a vacuum equivalent. (Which is required even when all MVCC support is disabled! It is used to enable efficient implementation of deletes, without having to use absurdly coarse locks). MSSQL just handles doing that cleanup silently in the background while exposing basically no no configuration except a trace flag that can turn it off.

stuff4ben 6y ago

Great article if I ever get back into DB-based development again...

devit 6y ago

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

sbuttgereit 6y ago
You mean like... https://www.enterprisedb.com/enterprise-postgres/database-compatibility-oracle 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.
teilo 6y ago
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?
outworlder 6y ago
> It can't be that much work, Seriously? Syntax is always the trivial part, everywhere you look. Semantics is what bites you.

trollied 6y ago

One RDBMS that I don't see mentioned much is Tibero https://www.tmaxsoft.com/products/tibero/ https://www.tmaxsoft.com/products/tibero/ It's a clone of Oracle & I'm surprised that Oracle legal have never tried to splat it! Interesting blog post discussing it here: https://www.tmaxsoft.com/products/tibero/ https://www.tmaxsoft.com/products/tibero/

fl0wenol 6y ago
That's not a blog post, that's just marketing material.
- trollied 6y ago
  Damn, posted the wrong link by accident. Wondered why I was getting downvoted. The link I meant to post was https://blog.dbi-services.com/tibero-i/ https://blog.dbi-services.com/tibero-i/

drdec 6y ago

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

miahi 6y ago
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.
- ibejoeb 6y ago
  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.

revel 6y ago

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 proving to be a remarkably successful one

da_chicken 6y ago
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 https://youtu.be/NlBjNmXvqIM
- toyg 6y ago
  I agree on the overall theory (dominance in a sector tends to shift internal incentives in such a way that the result is an ossified development structure), but I think Jobs' terminology is imprecise. Larry Ellison is not really a product guy first and foremost, he's the definition of a tough salesman. The "bad guys" are a more generic variety of "corporate type" who can materialize in any department, really. Typically they power themselves up the ladder with "cost efficiencies". The most recent Oracle CEOs (Hurd and Catz) fit that profile to a T. En passant: another big name that suffered from this phenomenon was Nokia.
- cryptonector 6y ago
  I've a feeling that Oracle succeeded by accident. They won the race to win mindshare without understanding that that's what they were doing, and have since rested on their laurels and high pressure salesmanship. You see their lack of attention to mindshare in... everything they do.

masklinn 6y ago

> IF EXISTS for DDL operations It's a super convenient operation but it has one big drawback which might be unexpected: `if exists` first acquires the relevant lock then checks. This means a "ALTER TABLE table_name DROP COLUMN IF EXISTS column_name" will first acquire an ACCESS EXCLUSIVE lock, then check if the column exist. Since DDL is transactional the lock will not be released until the transaction is committed or rollbacked, therefore even if the column doesn't exist it will prevent all concurrent operations on the table.

ksec 6y ago

Whenever I see comment making comparison between Oracle and Postgre, I cant help bug wonder why isn't it compared to Enterprise DB, which is sort of like the unofficially official Postgre for Enterprise products.

pasha_golub 6y ago
Postgres! You're not saying Orac, aren't you? :)

davio 6y ago

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

davidgerard 6y ago
Absolutely the best part of moving from Oracle to PG is never worrying about licensing ever again. PG does 99% of what Oracle does. If you have one of the 1% cases, that will be a remarkable circumstance. Always test a move from Oracle to PG, see how it performs.
tandr 6y ago
It mirrors my (anecdotal) experience in the last 2 companies that were dependent on it too. With that said... Makes me wonder how many (if any) companies are moving opposite direction that Oracle survives and doing so nicely.
ibejoeb 6y ago
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.
aserafini 6y ago
Amazon even made a promotional video when they shut down their last Oracle database https://m.youtube.com/watch?v=9yBP5gnnZi4 https://m.youtube.com/watch?v=9yBP5gnnZi4 Perhaps it was retaliation for Larry’s comments about Amazon in this interview https://m.youtube.com/watch?v=xrzMYL901AQ https://m.youtube.com/watch?v=xrzMYL901AQ
takacsroland 6y ago
Thanks for everyone's comment, it really looks like migrating will be one of the best decisions. :)

CodeSheikh 6y ago

As a dev you would use Oracle only if your execs have cut a sweet licensing deal with the Oracle.

takacsroland 6y ago
:)

krakatau1 6y ago

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 or MSSQL, use them for superior performance. Migration off them is costly and risky process. If your working at a startup there is absolutely no reason to choose anything but Postgres if you need relational.

takacsroland 6y ago
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?
- outworlder 6y ago
  YMMV. That depends on your workload, how your data is structured and a million other things. I know a few shops with heavy usage that couldn't be happier. It may require handholding if you are not happy with the plans it is generating. Also the quality of the query planner results will depend a lot on how up to date the statistics are.
  - takacsroland 6y ago
    I see, thanks!
- tomnipotent 6y ago
  > 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.
derefr 6y ago
I've never understood why RDBMSes don't offer a low-level protocol where, rather than a SQL statement, you can just send over the exact query-plan AST you want the DB to prepare in some binary encoding. Then you could pre-compile your hot OLTP queries and heavy OLAP reports offline against your existing DB schema, using the same sort of techniques that got Lucene its optimized Levenstein-automata JVM bytecode. You could even tweak the resulting plan, op by op, before letting it go to the DB—as if you were doing final ASM tweaks on a game in the 90s.

iracic 6y ago

Some good points in article. There are some things that may need more attention. 1) Update from another table is not safe as it should be (in case of multiple values, final value will be sort of random) 2) Schemas as namespace separators (grouping tables inside database) 3) Extern join syntax in Oracle is actually more vulnerable (in case of error in multicolumn syntax it fallbacks to normal join). So, it's not better or easier - it is just created before standard JOIN existed. 4) Crucial difference how buffer-vs-filesystem cache works 5) Miss of plan stability - no solution out of the box in standard installation 6) Batch operation (in)efficiency 7) Pros/cons in undo/rollback handling [likely some more that can't think of right now]

simonebrunozzi 6y ago

I would bet the next "Oracle" (as in, large IT company) of the decade 2020-2030 will be based on PostgreSQL. Can't see an obvious candidate, yet. Perhaps someone has seen some interesting companies heading in this direction?