The Oracle rdbms was at least for a while unmatched. It had programmability, functionality, and tunability that no other database had. If you needed what it provided, and could afford it, there was no real substitute.

Today, Postgres has come a long way, and distributed databases are much more developed. There is probably much less technical justification for using Oracle than there was 10+ years ago.

Also many companies would pay for Oracle and just use it for CRUD operations. That was throwing money away, no doubt.

> The Oracle rdbms was at least for a while unmatched. It had programmability, functionality, and tunability that no other database had. If

That's never really been true. From day 1 it's been neck-and-neck with DB2.

from developer perspective, that's far from true. oracle was a polished product, DB2 was, well... IBM product, PITA in various forms (toolset, easy to use etc.)

Uh, doesn't everything you do with a database reduce to CRUD?

I understand that sometimes you do the composition of many CRUD operations, but this is still CRUD.

Literally everything you do with memory and disks can also be reduced to CRUD, but that is an extreme example to show my point: a DB does a lot more. A well written DB, in this case a good RDBMS, gives you stronger guarantees that data is saved and accessed in certain ways. That by itself is worth using a better one that gives you better guarantees.

But they also provide stronger typing and checking of the data. They provide a programming language neutral way to access the data quickly and efficiently. They index data to make queries on it faster. They do much of this automatically so you think it's all simple CRUD operations, but they also provide ways to explain what they're doing if it isn't working as expected. I can count the number of programs (not DBs) on my hand that I know of that can explain themselves accurately without needing a debugger.

Edit: I don't mean to be annoying. Most people on HN knew this and I'm not assuming you didn't, but while most people just use RDBMS for simple CRUD operations they're losing out on the power that these databases have been designed with.

Amen to everything you and arnarbi wrote.

However, I've come to dislike building business rules using the proprietary languages of the RDBMS vendors. In particular, I use Oracle and have been making a case with my employer to stop creating ties to Oracle by using PL/SQL as our house language. While it provides some efficiencies and can sometimes make coding easier, it can stifle development as easily.

I'm all for expressing data relationships in the RDBMS and using efficiency mechanisms like indexes and cost-based optimization. However, I now prefer business rules to be expressed outside the database.

[EDIT: Corrected anarbi to arnarbi.]

As someone working at a company that decided to write most of their SQL code at the application layer, there are definite tradeoffs. Our application performance suffers because of the fear of vendor lock-in and the flexibility others thought that writing code at the application level would provide.

The question I pose to anyone consider this type of architecture is, how often do you switch database platforms?

The answer is almost never because the cost in time is too high for such a switch. It's the same question I posed 10 years ago. My view changed since coming to my current employer.

I work for a moderate-sized university. The applications I support require up-to-date information in order to test modifications. They are read-intensive with few writes within the database. We can afford only one test database for the entire university. We refresh the database from production only once a quarter, at most. My applications would benefit greatly by being able to switch URL's to the production database in order to test a mod. Since our code, written in PL/SQL, gets stored in the database that it runs on, we can't do such a thing without significant effort: dblinks or modifying package names to store in production for testing or a test schema or whatever else you can think up.

Another issue is that PL/SQL is not a robust language for modern development. Its type-system was designed for strictness, which is great in life-critical systems. But, there is no notion of inheritance and no ability to write generic collections.

To mimic inheritance, you can create objects backed by tables and inherit one from the other. It has the feel of being bolted on and yet another tie to Oracle.

One final issue is that PL/SQL is highly subject to the resource management of the Oracle kernel. Often, the kernel is extremely efficient. But, there are times when you need code running in a separate address space, preferably on a separate server. For instance, one application I wrote is a batch system that has a lot of processing rules built into it. Our database is tuned for OLTP. My application is categorized by the DBA's as data warehouse-oriented. Yet my application has to run hourly throughout the day and compete with resources necessary for the OLTP stuff. Putting it onto a separate server, away from Oracle's kernel management, would more than likely help a performance issue we have.

So, the answer to your question is the one you were looking for. But, my concern isn't necessarily switching database vendors.

I understand the cost of switching now. Was Postgres never an option? Do the uptime requirements call for RAC etc.?

Our Banner student information system is tied to Oracle. My applications all go against Banner data. Given the licensing cost for Oracle and our current round of cost-cutting, if Postgres was an option, we'd probably take it.

Several years ago at an .edu I had to deal with an upgrade from our current system to Banner. I truly feel for you. :)

Never. You have sufficient expertise to make the solution work and enough exposure to the code base to modify core if necessary. Oracle: never an option.

postgre got a lot of adoption being a drop in replacement for mysql a while ago.

>gives you stronger guarantees

Your DB either supports ACID or it doesn't. The term "stronger guarantees" is snake oil. It's either guaranteed or is not. That simple.

Yes, but it's made by fallible people, who may make mistakes in previously good software you update to, and it assumes a computer doesn't fail which can still happen (although very rarely now).

Do people really not mean types, referential integrity, indexes, transactions, queries, etc. when they say CRUD?

If table stakes for a database are more sophisticated than CRUD, what the hell is trivial enough to be CRUD then? Seems like a straw man.

And referential integrity, transactions, and SQL types are only dimly important in a Rails app or something in Node using Sequelize.

Plenty of apps--perhaps more apps than not--use a database as a vaguely typed (MySQL out of strict mode barely gives a nod to them) multiplayer notepad.

Not at all. RDBMS features were critical in getting performance and scale once upon a time. Sharding, access control, authentication, indexed views, fk relationships and other constraint enforcement, cascading updates and deletes, efficient data management with stored procs, caching, etc etc.

My first job was even writing dynamic web apps entirely in Informix.

> once upon a time. RDBMS features were critical in getting performance and scale once upon a time. Sharding, access control, authentication, indexed views, fk relationships and other constraint enforcement, cascading updates and deletes...

Do you think of better ways to handle this nowadays? Is it more effective/maintainable to do this in the app layer?

I think it depends on the situation. In many cases these can be handled at the app level, in particular relationships. If you use any kind of ORM, most of your queries will be on primary keys so there is less need for other indexes. Views are perhaps replaced by pre-processing data pipelines to some extent. I haven't seen database-level users or ACLs for a long time.

But I don't know, I don't really do this stuff anymore. At Google we certainly have storage and database systems with a lot of non-CRUD features (look at Spanner for example).

The product that built Oracle the company, their RDBMS, is frankly one of the worst pieces of "enterprise" software I've ever encountered.

Oracle's only redeeming quality seems to be free golf at nice country clubs paid for by Oracle salespeople.

Open source databases still don't have a developer environment that matches what Oracle or SQL Server offer.

Same applies to their stored procedures languages and deployment options.

See, once I actually started using pl/pgsql, I found it really powerful and a match for T-SQL. Never used PL/SQL though, so can't compare.

And which PostgreSQL developer editor can match SQL Server Management Studio?

Because pgAdmin isn't it, specially after it became an Electron app.

You're not wrong there. I miss SSMS.

pgAdmin3 wasn't a terrible application. Nowhere near SSMS, but not terrible (other than stability... it's shit there).

pgAdmin4 is unusable.

Who actually uses these glorified IDEs? No one I've hired.

JetBrain's DataGrip is getting there for a wide variety of databases, including Postgres.

+1 for DataGrip, I use in nearly exclusively for MSSQL and PostgreSQL (I only fire up SSMS when I need to manipulate some features of the database I can never remember the T-SQL incantation for, because Microsoft has to make shit as obtuse as possible).

Unfortunately the world doesn't run off technical ability though. Oracle is a mammoth because it's proficient at extracting value and profit from its businesses. Don't get me wrong, I agree with you in principle, but it's the equivalent of someone destroying artwork and making a gain from it and wondering how they get away with it when the world doesn't care about art, they care about money.

IIRC, Oracle got a huge boost from US government as it was the semi-official database engine developed (for the Navy?) with taxpayer funds, then deployed (again with taxpayer funds). I'm not complaining, the US probably needed a better DB engine, but it was partly luck, partly backroom agreements that launched and supported Oracle for years. That said, maybe I got my facts badly mixed up.

CIA. (Source: Larry Ellison, as quoted in Business Insider: http://www.businessinsider.com/the-cia-made-larry-ellison-a-...)

Which is why the villain in Charlie Stross's _The Jennifer Morgue_ is "Ellis Billington".

I think it's SQLite that had Navy origins.

You are probably right. I recall reading that Oracle development was funded by a DARPA-style mechanism, but I may have confused it with something. As MrMorden says in another comment it was likely the CIA who funded the development.