Lessons Learned Migrating from PostgreSQL to MariaDB

When I first embarked on migrating a production application from PostgreSQL to MariaDB, I assumed it would be mostly a matter of exporting data, switching out drivers, and letting the ORM do the rest. After all, both are relational databases with solid SQL support, active communities, and decent ORM compatibility, right?

Well—yes, but not quite. As I quickly discovered, the migration wasn't just a matter of technical reconfiguration; it was an unraveling of subtle and not-so-subtle differences between two database philosophies. Here’s what I learned.

SQL Isn’t as Portable as You Think

One of the earliest surprises came from the schema itself. PostgreSQL’s SERIAL and UUID types don’t map cleanly into MariaDB’s syntax or type system. MariaDB expects AUTO_INCREMENT for primary keys and treats UUIDs as plain strings (CHAR(36)) or as binary blobs (BINARY(16)), depending on your indexing needs. This required carefully auditing and rewriting table definitions to ensure data integrity and proper indexing.

Also, PostgreSQL’s support for BOOLEAN types turned out to be more robust. MariaDB doesn’t have a true boolean — instead, it maps TRUE/FALSE to TINYINT(1), which led to awkward conversions in the application layer.

Datetime Hell: One Letter Broke Everything

I hit a wall when my app started throwing mysterious OperationalError exceptions during inserts. The culprit? Datetime formatting.

PostgreSQL handles ISO 8601 with ease, including strings like 2025-06-04T01:12:27.875702Z. MariaDB, on the other hand, absolutely refuses to accept that trailing Z, which denotes Zulu time (UTC). It also has stricter expectations about microseconds. This meant I had to clean every datetime value going into the database, stripping the Z and ensuring they were native Python datetime objects — not strings.

ORMs like SQLAlchemy don’t automatically sanitize this unless you’re careful. I learned to always use .isoformat(sep=' ') when serializing datetimes and never pass raw ISO strings into a DATETIME column unless the format is exact.

UUIDs and JSON: The Feature Gap

Coming from PostgreSQL, I took for granted the elegance of native UUID types and JSONB fields. In MariaDB, both of these features exist in some form, but not with the same depth. You can store JSON in MariaDB, but you lose the powerful indexing and partial match querying. UUIDs? They’re just strings unless you manually optimize them with BINARY(16).

This changed how I designed certain models and forced me to compromise on query efficiency in favor of compatibility.

Bulk Inserts, Less Helpful Errors

Another adjustment was working with bulk insert operations. PostgreSQL tends to give very descriptive error messages. When something goes wrong during a bulk_save_objects() call in SQLAlchemy with MariaDB, you're often left with vague PyMySQL errors that only make sense if you manually reproduce the SQL. Logging became essential. I had to wrap every insert in verbose try/except blocks and add extra debug printouts for malformed payloads.

Case Sensitivity and Collation Confusion

A particularly subtle issue was collation. PostgreSQL is case-sensitive by default. MariaDB? Not so much. Its default collation (utf8_general_ci) is case-insensitive, which led to odd bugs when querying by ID or username. Changing the collation to utf8mb4_bin helped, but I had to revisit every field where case mattered.

Not All Performance is Equal

From a performance perspective, the switch was a mixed bag. For basic reads and writes, MariaDB handled things fine, and I appreciated its simpler replication setup. But for more complex queries — especially those involving JSON fields or window functions — PostgreSQL was clearly ahead. This reminded me that databases are not just interchangeable components; they're engines optimized for different kinds of work.

ORM Assumptions Are Dangerous

SQLAlchemy worked with both backends, but I had to make some conditional adjustments. PostgreSQL dialects allow richer types and constraints. MariaDB required some model field simplifications — especially around UUIDs, JSON fields, and datetime precision.

If your codebase targets both, you’ll want to abstract model definitions or enforce dialect-specific types conditionally.

Final Thoughts

This migration taught me to be cautious about assuming portability between databases. While PostgreSQL and MariaDB both adhere to SQL standards in broad strokes, their divergences show up in the edges — datetime formats, default types, collation behaviors, and JSON handling.

I wouldn't call it a painful experience, but it was far from seamless. Still, with careful refactoring, logging, and a few sanity-saving helper functions, the app is now happily running on MariaDB — and I’m a lot more aware of what true cross-database compatibility really means.