Karl-Sander Erss published this on 28 September 2023. He is a co-founder of Teamdash, recruitment software with one logical database per tenant. Schemas match. Each tenant connects with their own credentials. The app talks through an ORM that can emit more than one SQL dialect. This is his post, not a cloud vendor’s.
The blocker was vector search on Amazon RDS for MySQL. They want embeddings plus ordinary filters, so a recruiter can rank a talent pool and still apply the usual fields. A second database was the alternative. He tried Weaviate. The SQL side was weak, merging two result sets in the app would not scale, and someone would have to keep the copy in sync.
The MySQL → PostgreSQL playbook is the general type map. This write-up is the multi-tenant version, with the bugs that survived the window.
What else he wanted to stop doing
MySQL will not use an index merge on a full-text index. A full-text search plus a join became a table scan. Around 5,000 by 5,000 rows, those searches passed one second. They had already replaced full-text with LIKE as a workaround.
He also names date math that wants date_trunc, and JSON access through col->"$.mascot" plus json_unquote(json_extract(...)). Those are irritations. The vector feature is the reason they changed engines.
One database per tenant, plus a pooler
MySQL has no schemas in the PostgreSQL sense, so Teamdash already used a database per tenant. He kept that shape on PostgreSQL. He cites an AWS database post. Schemas are logical, they share a directory on disk, and a large tenant count can exhaust inodes. He also cites an AWS forum note that some RDS and Aurora customers run 10,000 databases. I have not re-fetched those AWS pages. The decision he records is database-per-tenant.
Cross-database UNION died with that choice. On MySQL, admin queries could read database1.table and database2.table in one statement. PostgreSQL needs dblink, and each dblink opens a connection. He accepts that because a future multi-region setup would need a link across servers anyway.
PHP opens a connection per request and closes it. His own timing put a new PostgreSQL connection around 10 milliseconds, and a pooled one around 1 millisecond. He also wanted a ceiling. PostgreSQL connection counts get uncomfortable in the hundreds, and he did not want to pass 1,000.
pgbouncer pools per database and user. With many tenants active at once, you still need many server connections, because one tenant’s connection cannot serve another. They still turned the pooler on, with a lower server idle timeout and a smaller default pool. Busy tenants stay quick. Background scripts that walk every tenant connect directly, so a cron run does not open a pool per tenant.
New tenants were the sharp edge. pgbouncer wants the database list in a file. Creating a tenant means updating that file. He says they found a way and he is not publishing it.
Making new tenants match the moved ones
Laravel builds schema from migrations. A fresh tenant runs that list from the start. An nmig import of an old MySQL database will not match that history row for row. His rule was one shape.
He ran the migrations against a tenant database, dumped the PostgreSQL schema with pg_dump, and created later databases from that file. Only migrations dated after the move run on a new tenant.
He also decided to move every tenant in one window. A tenant-by-tenant flip would mean two copies of the app.
The window
nmig moved the rows. A shell script in GNU Screen walked the database list, created the new database and user, wrote an nmig config, and ran it.
They took the app down. Queue workers stopped, nginx stopped, cron stopped. New code went out, then the script. About 200 databases took about four hours. Customers sit mostly in UTC+1 to UTC+3, which is why that window was tolerable. He wishes he had tuned it. The database box looked underused.
MySQL tinyint(1) stood in for booleans. nmig maps a type, not a column, and other tinyint columns were real small integers. He could not map every tinyint to boolean. He converted the boolean-shaped columns after the load.
That late convert shipped bugs into production. Laravel still had where('is_active', 1). The front end still had item.is_active === 1. PostgreSQL will not treat boolean and integer as the same thing. The error he quotes is operator does not exist: boolean = integer.
MySQL had also coerced bad input. PostgreSQL rejects it. invalid input syntax for type integer: "3.2" is the example. The app has to cast before the query.
pgbouncer in transaction pooling mode cannot see a server-side prepared statement. Eloquent prepares by default. The fix on the connection was:
\PDO::ATTR_EMULATE_PREPARES => trueHe says the performance hit was not meaningful.
Collation changed the search predicates. MySQL utf8mb4_unicode_ci folded case, so LIKE treated l and L as the same. PostgreSQL is case-sensitive unless you ask. They rewrote those predicates to ILIKE. He notes ILIKE is less index-friendly. Most of their patterns were already '%term%', so the index was not the win they lost. A case-insensitive collation per column was the other option. They did not take it in this post.
The next day was noisy in a limited way. Sentry caught a couple of errors and they fixed them. A week later, a bug showed up in a configuration screen. His lesson is that the odd SQL lives in the quiet features. Average response time did not move. Individual endpoints moved by up to 15%, some faster and some slower.
The post ends on the search work they can now build. It does not report a pgvector recall number, an embedding dimension, or a bill.
What I would copy
If the feature you need is vectors plus SQL filters, time a sidecar before you change the primary engine. He did, with Weaviate, and the merge in the application is why he refused it.
Freeze writes for a multi-tenant move unless you are ready to run two apps. His four hours covered about 200 databases. That only works if you can say when those tenants are asleep.
Map booleans yourself when the tool only maps types. The production failures were 1 compared with true, in the API and in the browser. Grep for === 1 and for integer literals in where on every flag column before the window, not after Sentry pages you.
Set ATTR_EMULATE_PREPARES before the first request if pgbouncer is in transaction mode. The prepared-statement error is a pooler mode, not a bad query.
The MySQL → PostgreSQL playbook covers the type and collation checklist. Use Karl’s post for the per-tenant pooler and for the boolean bugs that waited until production.