From MariaDB to PostgreSQL: migrating AutoSuperMarket

AutoSuperMarket started life on MariaDB for a very simple reason: it was the database we knew best, so that’s what we began with. Years, hundreds of tables and a few million listings later, the statistics database had been on PostgreSQL since 2024, but everything else hadn’t.

Since 20 September 2026, just after one in the morning, the main database runs on PostgreSQL 18 too. I worked on it on my own for about six weeks, from the first tests written in early August to the night of the switch. This is the story of how it went, what broke and what I learnt. It’s long, and full of SQL.

The starting point

• 165 GB of data and 12.9 GB of indexes across 265 tables, on three MariaDB 11.8 nodes: one master and two replicas.
• 3,597,655 rows in the listings table, the one that matters most. For weeks I’d been reasoning about 2.2 million, because that was the development copy: production had half as many again.
• A Symfony application on Doctrine, 30 web pods on Kubernetes, import feeds from dozens of dealer management systems, and a huge internal console.
• At peak, 454 connections open at once on MariaDB.

The target was a three-machine cluster (8 vCPU, 32 GB of RAM, Debian 13) running PostgreSQL 18, with Patroni for failover, etcd, PgBouncer in transaction mode and haproxy behind a virtual IP managed by keepalived. PgBouncer wasn’t optional: 454 connections are business as usual for MariaDB, but for PostgreSQL on eight cores they’re 454 processes.

The risk wasn’t the schema

The first thing I realised is that the schema was the easy part. Doctrine generates it for any platform, with a handful of fixes: six index names reused across tables (unique per table in MySQL, per schema in PostgreSQL), a TINYINT type that had to fall back to SMALLINT, and user and end needing quotes because they’re reserved words in PostgreSQL.

The real risk was hand-written SQL: native queries in repositories, DQL calling MySQL functions, exports, reports, migrations. Things PHPStan can’t see and that don’t fail until someone opens the right page.

So before touching a line, I wrote tests. Repository tests, plus a smoke test that opens the console pages, to pin down the expected results on MariaDB. Then I ran the same suite against PostgreSQL too, in a non-blocking CI job I called “the probe”.

The first time it didn’t even start. After that, the failing tests went down like this:

317 → 138 → 103 → 75 → 65 → 47 → 35 → 31 → 16 → 12 → 5 → 1 → 0.

In the end all 1046 tests passed on both engines. Today the suite runs on PostgreSQL only, and it’s blocking.

An embarrassing detail: for a week the CI job measured nothing at all. While making the script runnable from my own terminal I’d changed the default hosts, so the pipeline pointed at a port where nobody was listening and stopped before running a single test. Since the job was allowed to fail without blocking anything, nobody noticed, me included. A non-blocking job that breaks in its own setup looks exactly like one that’s simply still red.

Things MySQL forgives

Most of the errors fell into a few families.

Booleans. PostgreSQL has a real boolean type and won’t compare it with an integer. I read the 183 boolean fields out of the Doctrine metadata and fixed 46 comparisons against 0 and 1. Aggregating over a predicate, so handy in MySQL, doesn’t exist in PostgreSQL:

-- MySQL only
SUM(b.type = 'pro')
-- both
SUM(CASE WHEN b.type = 'pro' THEN 1 ELSE 0 END)

Functions. RAND, GROUP_CONCAT, DATE_FORMAT, JSON_CONTAINS, DATEDIFF, INTERVAL 3 DAY and friends became DQL functions that emit different SQL per platform. The translation of DATE_FORMAT into to_char throws on a format token it doesn’t know, rather than formatting wrongly in silence. The one that scared me most was %w, the day of the week: MySQL counts Sunday as 0, to_char(..., 'D') as 1. The switchboard filters calls by weekday, and the obvious translation would have shifted every result by a day without a single error.

GROUP BY. PostgreSQL always enforces the equivalent of ONLY_FULL_GROUP_BY. The search count was cloned from the main query and dragged along a hidden CASE that existed only for ordering (the Pro+ listing at the top, the price bands). With the ordering removed, the CASE stayed in the select next to the COUNT with nothing grouping it. This one reached production the day after the switch, on the listing search pages.

DISTINCT over an entity with json columns. In PostgreSQL json has no equality operator, so a DISTINCT over an entity holding one fails. It turned up a month after the switch, on fourteen console pages at once: every page with a salesperson picker. I fixed it with a correlated EXISTS instead of the join, so no row repeats and there’s nothing to deduplicate.

Numbers. Bounds like intval($x) * 0.95 were bound as floats against integer columns. MySQL compares them numerically; PostgreSQL refuses to read 104.5 as an integer and throws away the whole query. In one feed importer that meant 135 listings imported out of 210, with no visible error.

Integer division. SUM(pay.price) / 100 returns a decimal in MySQL and does integer division in PostgreSQL: 4999 cents become 49 euros instead of 49.99. No error, just a smaller number on a revenue dashboard. Seven queries in all. Writing / 100.0 is enough.

The second pass, reading the code

When the probe went green I made the mistake of relaxing. Then I did another pass reading the code instead of running it: every DQL string, every hand-built SQL string, every comparison against a boolean column. I found fourteen more places, none of them covered by a test.

Some were anything but obscure. YEAR is the search’s registration-year filter, YEAR_MONTH the monthly grouping behind every console chart: they were still registered from the MySQL extensions library.

The line I wrote in my notes is this: a green probe means the covered paths are portable, not that the code is. Every new test on a method that had never been tested found a new incompatibility. The first test on the method that picks similar listings for automatic leads immediately found a query PostgreSQL rejects, on a path that sends emails to customers.

And one at my own expense: for a while I was convinced PostgreSQL handed booleans back to PHP as the string 'f', which is truthy in PHP, and I’d imagined catastrophic scenarios. Then I measured it: with PHP 8.4 and DBAL 4 you get a real bool. The fixes stayed, because they’re the right shape anyway; the catastrophe didn’t. Measure before you tell yourself a story.

The bugs that were already there

The part I enjoyed most. The migration was worth more for what it found than for what it moved: PostgreSQL rejects queries that MariaDB happily ran and answered with nonsense.

The salespeople leaderboard was grouped by a boolean. A query did SELECT ... AS seller ... GROUP BY seller. But the operators table has a seller column, the flag marking someone as a salesperson, and GROUP BY resolves the columns of the FROM clause before the aliases. MariaDB answered with three rows for the whole company: salespeople, non-salespeople and nobody, each labelled with whichever name came first. PostgreSQL refused it.

Distances were wrong. ST_Distance_Sphere reads the first coordinate as a longitude, but the point was stored as POINT(latitude longitude). Milan to Rome measured 535 km instead of 477, with the error growing along the east-west axis. Since distances are used to rank nearby cities and dealers, it was the ranking that was wrong. No test had noticed: every coordinate in the fixtures was POINT(0,0). It’s now a haversine formula written out in full, identical on both engines.

The new-driver filter never applied the power-to-weight ratio. Italian traffic law allows drivers in their first year at most 70 kW and 55 kW per tonne. The code computed power / weight * 100 with the weight in kilograms, which is kW per 100 kg, and compared it with 55: an effective limit of 550 kW/t that no car on sale comes close to. On top of that the DQL version used DIV, MySQL’s integer division, which brought almost every pair down to zero. It’s now a cross-multiplication, exact on integers and the same on both engines:

power * 1000 <= 55 * net_weight

Saved-search notifications could fire for nothing. The results hash was SHA2(GROUP_CONCAT(listing.id), 256), but the ordering sat outside the aggregate. A change of query plan was enough to change the hash and email the customer the same listings as before.

Listings were filed under a random model. When a feed left the model empty, the lookup became LIKE '%%' with setMaxResults(1) and no ORDER BY. A Ford Transit Connect was filed as an Escort on MariaDB and as an F-650 on PostgreSQL. No ordering rescues a query like that; it only makes it consistently wrong.

Then there were the dealer page’s “Top brands”, grouped by listing id and so always counting 1; deleting a branch, which failed on six relations that were never deleted; and 1032 portal configurations out of 1038 holding an empty string in a json column, which the entity couldn’t read even on MariaDB.

Upper case, lower case and accents

MySQL compares text case-insensitively, PostgreSQL doesn’t. findByName('HYUNDAI') found Hyundai on one and nothing on the other. You can’t fix that in the queries: every findOneBy on a text column depends on it.

On PostgreSQL 16 the only real choice was citext, because nondeterministic collations didn’t support LIKE, and I had about forty queries using it. But citext doesn’t ignore accents, and one case showed me clearly: in the feeds, Serie 8 Gran Coupe without the accent ended up under Serie 8 instead of Serie 8 Gran Coupé. And citext has no length, so I was losing the varchar limits.

PostgreSQL 18 lifted that restriction, and on 20 September I converted 1117 text columns to a nondeterministic ICU collation that ignores both case and accents:

CREATE COLLATION case_accent_insensitive (
  provider = icu, locale = 'und-u-ks-level1', deterministic = false
);

Two lessons learnt the hard way, pulling in opposite directions:

• Both sides of a foreign key must share a collation, and PostgreSQL checks it at the ALTER, not at commit. So the keys have to come off and go back on around the whole conversion, in a single transaction.
• The length conversion, on the other hand, must not be a single transaction. The first attempt took an exclusive lock on 198 tables and deadlocked against an ordinary application query. One transaction per table, with a short lock_timeout and a second pass for whatever was busy, is what works on a live database.

The fallback search

The site searches listings through Typesense; the database only steps in when Typesense doesn’t answer in time. That path used MATCH ... AGAINST and REGEXP, neither of which exists in PostgreSQL.

My first idea, pg_trgm, was wrong, and it’s worth saying because it’s the one everybody reaches for. Measured on the same 20,000 listings, for bmw x4 xdrive:

Trigrams get it wrong both ways: they return irrelevant rows and miss three quarters of what they should find. PostgreSQL’s full-text search with a GIN index does exactly what’s needed. Incidentally, on MySQL the same fallback with REGEXP took 4.2 seconds, on the very path that only runs because the search was already too slow.

A footnote: a branch opened before the port had added its own REGEXP to the dealer listing list. It was merged after I’d removed the function’s registration, and the first sign was a fatal error in production. A function that’s dead on master isn’t dead in every open branch.

Copying 165 GB

For the data I used pgloader, but only for the data. The schema it generates on its own is clean and wrong: for instance it turns tinyint(1) into boolean, and the listings’ pro column holds 0, 1 and 2. The 457 Pro+ listings would have become Pro without an error. So Doctrine builds the schema and pgloader runs in data only mode.

Things I found out the hard way:

• One row stops the whole table. 0000-00-00 dates in the assistant bot, year-zero dates from feeds that had dropped a digit, empty strings in json columns. MySQL had accepted them, PostgreSQL wouldn’t. I cleaned them up with a migration on MariaDB before the load.
• pgloader has a 1 GB heap. The first full-size load died after four minutes with Heap exhausted, game over. The config file had the settings that worked for the development copy, while my notes already listed the right ones for production. The file always wins, because it’s the one that runs.
• pgloader doesn’t speak SCRAM. I ran it on the primary node, over the local socket with peer authentication.
• A btree index has a limit of 2704 bytes per row. The mapping declared a “fulltext” index that becomes a plain btree on PostgreSQL, over very long descriptions. A single row that was too long rolled back all 3.6 million listings, while everything else had loaded cleanly.

Loading everything except the listings took 1 hour and 19 minutes, for 126 million rows; then the listings on their own. To get a consistent snapshot I stopped one of the MariaDB replicas at a precise GTID, and Debezium started from that point.

When I checked table by table, the counts matched on all 262 tables, apart from orphan rows deleted on purpose. 16,191 of them turned up, in eleven tables, all pointing at listings that no longer existed. A few hours earlier I’d looked for them against a list of constraints I’d written down, and found none. Putting the foreign keys back for real is what made them appear. Test the constraints, not a list of constraints.

Debezium and the binlog

Between the load and the switch, writes to MariaDB had to reach PostgreSQL too. I used Debezium with the MariaDB connector and the JDBC sink, after moving the master to binlog_format = ROW.

It works, but there are a few landmines:

• Timestamps shift silently according to the Connect worker’s time zone. A TIMESTAMP at 23:30 arrived as 01:30 the next day. The schema only had DATETIMEs, so I got away with it.
• The connector status lies. After a failure the connector says RUNNING while the task is FAILED.
• A poison message doesn’t clear itself, and errors.tolerance=all doesn’t help. The only way out I found was recreating the sink under a new name.
• UUIDs in binary(16) need binary.handling.mode=hex on the source and ?stringtype=unspecified on the sink’s JDBC URL. Either one alone isn’t enough.
• The sink writes with session_replication_role = replica, which disables foreign keys as well. So a few deletes that never got applied left 51 orphan rows behind without anything complaining, until I put the constraints back.

The night

I was at home on my own. On 20 September at 00:48 I put MariaDB in read-only mode with SET GLOBAL read_only = ON, waited for the sink to drain, paused the connectors, moved the sequences past the replicated ids and deployed the configuration pointing at PostgreSQL.

The length of the window was set by the rolling update. Every pod still on the old configuration kept writing to a read-only MariaDB, and the errors stopped at 01:01, when the last of the 30 web pods restarted. Twelve and a half minutes of refused writes, all of it rollout time.

Straight afterwards: 112,353 commits in 45 seconds on PostgreSQL, not a single PostgreSQL error in Sentry, and MariaDB down to two sleeping connections. I went to bed.

The day after

Of course, that wasn’t the end of it.

Around two in the morning a burst of server closed the connection unexpectedly came in, from parts of the code that had nothing to do with each other. When a Twig template, a feed and three repositories all fail at once, it isn’t the code: it’s the pool underneath going away. The haproxy health check pulled a replica out of the read pool when it fell more than 16 MB behind, which is a single WAL segment, and one autovacuum of the listings table writes 3 GB of it. On top of that the replica pool had on-marked-down shutdown-sessions, which killed reads in flight. That makes sense on the primary, where a session on a freshly demoted node would be writing to a read-only server; on a replica, a slightly stale read is still a correct one. Threshold raised to 256 MB and option removed. Both my mistakes.

Over the following days the last cases came in one at a time: the search count with the hidden CASE, and the insolvent dealers list comparing a timestamp column with literals inside a CASE. PostgreSQL types them as text before it even looks at the comparison; MySQL converted them silently.

On performance, the surprise was the lack of surprises. Across the fifteen busiest routes, thirteen hours after the switch, the traffic-weighted median went from 172 to 184 ms, while p99 dropped by 14%. The search’s price histogram more than halved its p99, from 22.2 to 9.9 seconds. That morning, though, traffic was well above average, and the home page, which spends 91% of its time rendering Twig and 4% in SQL, had got slower just like the others: a good part of those 12 ms comes from load, not from the database. PostgreSQL behaves like the more predictable engine, and perhaps a touch slower.

What I’m taking away

• Write the tests first, on the old engine. Without them, the migration would have shown me these problems in production, as wrong charts or error pages.
• A green suite tells you what you’ve covered, not what works. You find the next problems by widening coverage, not by reading error messages.
• Silent errors matter more than loud ones. A rejected query shows up straight away. An integer division, a weekday off by one or a distance with swapped coordinates doesn’t.
• Measure, don’t assume. Both times I took a cause for granted (an accent issue, a corrupt feed) I was wrong, and the answer came from importing the same feed on both engines and comparing the results.
• Keep notes as you go. I updated a migration document after every finding, and more than once it had to correct itself. A document carrying a wrong diagnosis is worse than no document at all.

MariaDB is now read-only and out of the path. PostgreSQL is holding up, and so, it seems, am I.