Why Leave Oracle?
Oracle is the fast, beastly sports car that will quickly outpace that Honda Accord you’re driving ... so long as the sports car has an expert driver, constant maintenance, and an expensive, highly-trained pit crew to look after it.
A couple of decades ago I was working for a company that was using Oracle on Linux. At the time, this was unusual, though becoming more common. It was a time when open source software was still looked upon with mistrust, so while we told our investors we used Oracle, we didn’t trumpet the fact that we used Linux.
One day we discovered a problem. An Oracle SELECT statement was sometimes returning different
results. The query in question was extremely complex, but the results should
not have changed as the underlying data was static. After much debugging, the
company gave up trying to solve the issue and called Oracle. We were informed
that while Oracle could run on Linux, it needed to be Red Hat Advanced Server.
So we switched to Red Hat Advanced Server. And the bug was still there.
“No, you need be on version X of Red Hat Advanced server.”
So we switched to version X of Red Hat Advanced Server. And the bug was still there.
“No, you need be on version X, point release Y, of Red Hat Advanced server.”
So we switched to version X, point release Y of Red Hat Advanced Server. And the bug was still there.
“That’s a known bug. We’re working on it.”
Words were said. They were not nice words.
Time Has Not Been Kind to Oracle
In the early 2000s, switching from Oracle to PostgreSQL was a gamble. Today we’ve had several clients making the switch or in the process of it, but what holds them back is no longer “can we trust PostgreSQL?“, it’s “our entire technology stack assumes Oracle.”
And the driver behind the switch? It’s invariably Oracle’s insane costs with
no clear return on investment for most companies. But there are plenty of
technical reasons, too. Oracle DDL is not transaction safe. Oracle violates the ANSI standard by often
treating empty strings as NULL, causing all sorts of subtle bugs. And Oracle
was built in an ASCII world, not a Unicode one, and it shows, painfully.
Added Benefits of PostgreSQL
First, PostgreSQL is generally chosen as the migration target due to its strong compatibility with Oracle and its excellent data handling. Fortunately, it’s now easy to find PostgreSQL admins (as opposed to Oracle admins) in the wild because PostgreSQL is both open source and free, so it’s much easier for a would-be DBA to get experience with the stack. In fact, one of our DBAs commented:
I never met anyone who knew how to use Oracle RAC (and debug its problems) unless they had already endured the hell of a broken Oracle RAC installation at least once ... on someone’s production server.
Cloud support for PostgreSQL is fantastic, with Amazon, Azure, and Google all supporting PostgreSQL.
And because PostgreSQL is SQL-92 (and increasingly, SQL-99 compliant) with an open extension library, its geospatial tooling and JSON support are arguably much stronger than Oracle’s. If you need those tools, you save a ton of money and get a better product.
Starting the Process
As with any project of this scope, you need to start out with a solid plan and that plan starts with a problem statement clearly outlining:
- The problem you are trying to solve.
- Why you want to solve the problem.
- What your proposed solution is.
- Measurable success criteria.
- A SWOT analysis (or something comparable).
While points 1 through 3 are the most straightforward, it’s worth considering unusual ways of meeting your goals. For example, merely contacting Oracle and explaining that you’re considering leaving Oracle for a more affordable solutions might get enough of a discount on licensing that you don’t feel the immediate pressure to migrate. High-profile companies, or organizations that hook people young (such as universities) can often command hefty discounts.
You may also find that hiring an expert to review licensing is worthwhile. For example, hot standby servers need licensing, but cold standby with minimal use may not. Or you may find yourself paying for too many licenses because you have separate databases which can be merged, or you may find yourself paying for too many CPUs when a more powerful server may be a more cost-effective solution.
There are also many edge cases to consider. One company we know uses Oracle for a public facing application so that they can reassure investors that “they use Oracle”, but use PostgreSQL exclusively for everything internal.
Or there are the cases where clients use Oracle Enterprise applications but find many internal cases where they’re unnecessarily spending on Oracle licenses. The Enterprise applications are often difficult to extricate from, while individual applications still eat up enormous costs for no valid reason.
Success Criteria
However, it’s points 4 and 5 above (success criteria and SWOT analysis) that we find many clients don’t address. For example, the success criteria needs to be measurable and you should be able to address:
- Performance
- Functional requirements
- Schedule
- Budgeting
- Resource allocation
- Minimized disruption
All of those will need flexibility when you encounter surprising obstacles to the migration. Resource allocation is particularly important if you are partway through a migration and find yourself maintaining Oracle and PostgreSQL systems simultaneously. This is where having a dedicated team to oversee the process is helpful because they are focused on successful delivery and are less likely to be pulled away on internal “distractions” that arise throughout the migration process.
SWOT Analysis
A SWOT analysis is simply a listing of Strengths, Weaknesses, Opportunities and Threats. For example, a clear strength of moving to PostgreSQL is the significant cost savings. Weaknesses may include poor internal knowledge of PostgreSQL. Opportunities can involve the custom data types and DDL-safe transactions, while threats might involve a need for write-heavy multi-master replication.
The above weaknesses or threats can be addressed, but you need to understand them and plan for them up front, with a signicant focus on mitigating weaknesses and threats.
Developing a Migration Plan
The migration plan that you develop will need to be fine-tuned for how your company operates, so a “one-size fits all” plan is not possible to develop. However, a successful migration plan should generally include the following steps:
- Audit
- Planning
- Migration
- Testing
- Adjusting
- Deployment
- Rollback
1. Audit
The audit stage starts with identifying every area in your organization where Oracle is used, identifying all licenses and expirations (you may need to renew during the migration process), and how Oracle is being used. We’ve found many clients are merely using Oracle as a data store and use few, if any, stored procedures or functions. This is underutilizing the power of the database but has the advantage of simplifying migrations.
The software audit is a critical portion of the overall audit. Many applications using sophisticated ORMs may find they need few, if any, changes when switching from Oracle to PostgreSQL. However, other applications—particularly those building SQL directly—will need a deeper audit to understand how the migration is to be accomplished.
Further, because the migration process is time-consuming, make sure you’re only migrating what needs to be migrated. Do you have historical data that doesn’t need migration? Do you have tables or stored procedures which are no longer used? You may find it simpler to skip those.
2. Planning
With the primary audit finished, it’s time to develop a detailed plan on migrating to PostgreSQL. We know of one company in Portland, Oregon, which managed to get 90% of their software tests on a large application passing in a single evening. More typical, however, is the case where the “Oracle-only” assumption runs deep. This is where PostgreSQL shines, because its SQL is mostly compatible with Oracle’s.
The planning for most common use cases involves the schema migration, data migration, and software migration. If you have a small schema (and this is almost always a case where PostgreSQL is preferable to Oracle), the schema migration can be done by hand. However, there are many open source and commercial tools which can migrate an Oracle schema to a PostgreSQL schema. Care will still need to be taken, however, especially if you have Unicode data.
3. Migration
The actual migration at the database level will take some time, but it in the software level that you’ll particularly experience grief. Having a robust test is imperative, as is a strong QA department.
One issue we routinely encounter is Unicode. Unicode is problematic for
Oracle because it was developed in an ASCII world while PostgreSQL fully
understands and supports Unicode. For example, in Oracle, depending on the
value of NLS_LENGTH_SEMANTICS, you may find that VARCHAR2(10) holds 10
characters or 10 bytes. If it’s bytes, you cannot store 日本, because that’s
actually twelve bytes! In PostgreSQL, VARCHAR(10) is guaranteed to hold 10
characters, not bytes, so 日本 is just fine. (Note: you can also use
NVARCHAR2 in Oracle to enforce character semantics, but NVARCHAR2 assumes a maximum of two-bytes per character , so you still
can’t store 日本 in an NVARCHAR2(10) field).
Another common pitfall is that Oracle often treats the empty string as NULL.
This is in violation of the ANSI-SQL standard and causes no end of headaches
for developers. For example, in Oracle you can often find cases of outer joins
with ambiguous semantics because you don’t know if field you’re asking for was
missing (not joined), or is the empty string.
Or one surprising issues we’ve stumbled across: Oracle allows you to declare
integers (INT), but internally it’s a NUMBER(38,0) and conversion tools
generally have NUMERIC(38,0) in the PostgreSQL version of the schema. While
there are those who
insist there’s no performance difference between the two , when ported to
PostgreSQL, you should use the appropriate integer datatypes and not their
NUMBER(p,0) equivalents. We’ve found significant performance improvements by
converting NUMERIC(p,0) types to appropriate integer types.
There are many other subtle differences (TIMESTAMP WITH TIME ZONE, join
differences, transactions, function incompatibilities, and so on). The free,
open source Orafce tool module will make
much of the pain go away, but not all (and it may prove to be a crutch you
shouldn’t rely on). Again, comprehensive automated testing backed by a QA
department is helpful here.
4. Testing
While it’s improved dramatically over the past decade or two, testing is the bane of many IT companies. Developers tend to test that their software does what they want it to do, while strong QA teams try to test that their software doesn’t do what they don’t want it to do. The difference is subtle, but it’s the difference between creation and destruction.
A key example comes from your issue and bug tracking systems. If you find that certain bugs are recurrent, this is often the case of a developer fixing the general issue for a specific case. A strong QA department should recognize the general nature of the problem and extend their tests to cover the general issue, not the specific one.
While this problem is not specific to an Oracle to PostgreSQL conversion, it is an issue which can mean the difference between a successful and unsuccessful migration. You will need to be sure that your testing approach is solid and that you’re not just testing that everything works with valid inputs, but that they fail in an expected way with invalid inputs.
But be aware that testing isn’t just about whether or not your software works as expected. Can you restore from a backup? How long does it take for you to failover? What’s your throughput like? Do you have “stealth” applications which assume Oracle and you’ve just switched? Perfectly functioning software doesn’t matter when your infrastructure catches fire.
And please keep in mind that this testing isn’t just a “one off” for the migration. A solid testing plan allows you to ensure that regressions don’t occur in the PostgreSQL implementation as it undergoes further development. Spending this time and money up front will save you time and money later.
5. Adjusting
The migration and testing aspects of your transition will likely need to be repeated more than once. There’s not much to say about the “adjustment” phase other than:
- You must have measurable success criteria
- Those criteria should be automated as far as is possible
- Rejected work must document the success criteria and how to achieve them
However, keep in mind that you must not let the perfect be the enemy of the good. Are you excelling in some areas and under performing in others? Is this an acceptable trade off? Are all success criteria really that important? Remember: your primary goal is to facilitate the Oracle to PostgreSQL migration. You can take full advantage of PostgreSQL’s power later.
6. Deployment
Congratulations! Your migration and testing have worked. You’ve successfully met your success criteria. You’ve made adjustments here and there, but you’re ready to roll things out. At this point, it’s very hard to give specific advice because the details of the your deployment will vary widely from company to company.
This deployment should follow your standard patterns as much as possible to avoid confusion, but it needs to be planned carefully, with care taken to minimize downtime as much as possible (especially if your database-backed applications are public facing). However, because this deployment will likely be more significant than most, it does need to be set up in such a way that when obstacles are encountered that cannot be easily overcome, you can immediately abandon the deployment.
If you’re not using it yet, something like a blue-green deployment approach is recommended here. Changing live systems is hard and it’s easy to paint yourself into a corner. Instead, build a duplicate of the system on production (containers work brilliantly here, of course), and run your tests against your changes. When you’re satisfied that all is well, point everything over to the new system, being ready to switch it back in a hurry, if needed.
And if you do this on a Friday, you deserve the pain you’re going to inevitably going to face.
7. Rollback
If you’ve followed the steps above and gotten to the point of rollout, you’re probably in a good place but you still need to plan for rollback. Will you need it? Probably not. But if you haven’t planned for it and you need it, it’s like having a fire without fire insurance. Discovering a critical production problem a week after deployment could involve losing a lot of data.
There are a few things to note about the rollback plan.
- It must be thoroughly documented
- It must be thoroughly tested
- It is developed in concert with the migration and deployment plans
- You must decide if data loss is acceptable or if data migration is part of the plan
The last point is very tricky. If you have something catastrophic happen, you may want to accept data loss rather than risk having to rollback your rollback because you couldn’t migrate new data to the old system. If a rollback is necessary, it’s like to be spotted right away and not a week later, so you’re unlikely to face this decision, but prepare for it.
Remember that a key flaw of release management is that people tend to expect success, not prepare for failure. By expecting success but planning for failure, you’ll put yourself in a great position.
Success!
Congratulations! You’ve successfully replaced Oracle with PostgreSQL. You’ve saved yourself a lot of money, you have a database that’s easier to manage, and you get to take advantage of many of the powerful features of PostgreSQL.