Licensing Woes

In one of my previous jobs, we managed many Oracle and SQL Server 🪟 databases. Licensing restrictions often forced us into making suboptimal choices. Here are two examples.

First, we had Oracle DB instances across the world that needed to stay in sync in a variety of arrangements. We should have purchased Oracle's Golden Gate, which is designed to "replicate, filter, and transform data from one database to another database". While it would have exceedingly expensive, given our needs, it would have paid for itself by saving years of effort by DBAs and software engineers and would have yielded a better outcome.

Second, we had a library that ran inside of Oracle DB. We couldn't run an ephemeral Oracle DB instance locally or in CI to test against. So, the CI runner 🏃‍♂️ connected to a live staging DB server to build the library, load it into the DB, run tests, and copy back the built artifact.

DB's at My Current Company

At my current company, every relational database is PostgreSQL, except for a single MySQL database, and a pair of SQL Server databases used by a licensed product we're phasing out. The SQL Server instances are arranged so that the secondary can replace the primary without any downtime, which is excellent for keeping availability high.

The Incident

One day, the primary database slowed to a crawl! 😱 This impacted business operations and had to be resolved quickly.

Mitigation and Diagnosis

We switched to the secondary, which seemed to have better performance. After some investigation, we determined that the table indexes were highly fragmented on both, and more pronounced on the primary. We don't have expert DBAs—just some application engineers and general-purpose IT/DevOps engineers.

We decided the causes were:

1. The automated maintenance plans stopped working reliably.

2. This is a guess. After some work was done to encrypt the disks, during which the primary and secondary couldn't communicate for an extended period of time, the index fragmentation increased.

We couldn't do maintenance on the original primary as it's only possible to connect to the active DB—you knew this was coming—due to of licensing restrictions. Sigh. I thought we would have to take a maintenance window one night to rebuild the indexes.

Insight and Resolution

A colleague asked ChatGPT what our options were. They then suggested reorganizing the indexes instead of rebuilding them. Reorganizing defragments the leaf level of clustered and non-clustered indexes, improving performance without requiring extensive resources. I tried it on the secondary and it noticeably improved the performance. Success! I switched back to the primary after hours, reorganized its indexes, and saw good improvements as well. 💪 Wahoo!

More on Reorganizing

Reorganizing is lighter 🪶 on resources than rebuilding and can be done online. Rebuilding is heavier 🚜 on resources and can resolve fragmentation that reorganizing can't. In addition, rebuilding must be done offline ⛔️, unless, you guessed it - you have the right license 🪪!

Just Use PostgreSQL

This experience, coupled with ones prior, reinforced my belief that PostgreSQL 🐘 is the correct default choice when a relational database is required. PostgreSQL doesn't have to be the best at everything. The advantage it has over licensed DBs is that all the tools one would want are freely available. What about MySQL? I'll leave that as an exercise to the reader (hint: don't use it!).