We moved out of PlanetScale. Here's how and why

Background

We at FeatureOS have been using Planetscale since 2021 when they initially launched, one of their initial customers. The initial few years have been really great, we’ve enjoyed the product and the team has been really helpful. But things started to change in the past few months.

Why we moved

Ever since their scalar plan was discontinued things started going downhill. There were frequent pricing changes and the product stated feeling very stagnant. Thats when we started exploring other options. And thats when we found the biggest issue with Planetscale.

We couldn’t migrate with ease. There is a lot of vendor lock-in which prevented us from migrating to other database providers.

Listing the major issues

• Limit of row counts that can be read at a time reads, max 100K row can be read at a time. Even with that the maximum size of the extract can be 64GB. If we can’t handle at now, in future it would be even more difficult. Ref: Planetscale Docs

• No Binary log access, so no way to setup replication to external databases, which means we can stream data to external databases or replication services, it felted more like a vendor lock-in. This limitation prevented us from doing zero downtime migration. Ref: Planetscale Docs

• Also the passwords were more 32 characters long, which is longer than the standard mysql password length and was not supported by any of the cloudbased data migration tools like Google Cloud SQL. This was yet another red flag with Planetscale as they change the basic function of the database.

• The CPU we get for the price we pay is very less compared to what we could get from a self-hosted solution. Since our customers were mostly from two regions, we didnt need much of their global region support.

Now that we’ve seen what we faced with the product, lets see how we moved out of it (Which was quite a challenge).

How we moved

Initially we went through Planescale’s offical support to migrate our data, but we didn’t get any proper responses, then we asked them to share a data export guide stating that it was a complaince requirement, which made them answer.

Trial One: Airbyte [Planetscale Connect]

They gave us the direction to use Airbyte to export our data. It was one of the officaly tool that was meant to be used for exporting data to ETL stacks Ref: Planetscale Docs. But when we tried it, we kept getting a bunch of errors from the database server.

• One was ResourceExhausted error.
• Another was a timeout error.

After couple of days of trying to figure out the issue, we dropped the idea of using Airbyte and decided to try a different approach.

Trial Two: pscale dump

While trying Airbyte and exploring the docs and internet for solution, we found about the limitation of Bin Log access in Planetscale. Thats when we ruled out no downtime migration. So we decided to keep the migration time lower and go ahead with a dump format to export and import.

While pscale dump worked great at first, it was quite faster and a also was not hitting any of the previous errors that we were facing with Airbyte. But we noticed that everytime we dumped the data, the size of the dump was sometimes more and sometimes less. And when we ran the pscale dump in debug mode, we found there were errors while dumping the data but was just ignored and wasnt getting logged.

Again we’re back to square one.

Trial Three: Some intermediate tools which were not working

1. We tried using DBeaver to export the data, but the same issue as airbyte.
2. We tried using mysqldump, but it kept failing with timeout errors and memory issues.
3. We tried Debezium, but it was too techinical and also required bin log to stream data.

So we were still at square one, but learnt a ton about how databases work.

Trial Four: MyDumper, finally a progress.

When we were let down by pscale dump, we tried debugging what it was built and thankfully planetscale open sourced it. And we found that it was using mydumper github.com/mydumper/mydumper to dump the data. Its a pretty popular tool for dumping mysql databases written in C lang. Pretty cool.

Also, adding to our strength, mydumper had a complimentry tool myloader github.com/mydumper/mydumper to load the data and it worked seamlessly with mydumper export.

The trial import and export was a success. So we wrote a script to automate the process and it worked great.

But thats when we had another thought, “What if we used this opportunity to move not just out of Planetscale, but also to move out of MySQL and into a more scalable and performant database like Postgres”.

So we started exploring the options and read a lot about Postgres and its ecosystem. We liked it.

Now to Square One.

Trial Five: MyDumper, MyLoader and pgloader - A complete pack.

We found that pgloader is the best to migrate data into Postgress and it worked pretty well with a mysql instance but not with a mysql dump. So we had a small mysql instance as a bridge to get data out of Planetscale and dump it into Postgres.

Step 1: Export data from Planetscale using mydumper.

    mydumper --user="$SOURCE_DB_USER" \
             --password="$SOURCE_DB_PASSWORD" \
             --host="$SOURCE_DB_HOST" \
             --port="$SOURCE_DB_PORT" \
             --database="$SOURCE_DB_NAME" \
             --outputdir="$DUMP_DIR" \
             --ssl \
             --ssl-mode=REQUIRED \
             --rows=5000 \ # This is the best we could do, since we were hitting the limit of 100K rows or 64MB Limit per query.
             --clear \
             --trx-tables \
             --verbose 3 2>&1 | tee -a "$LOG_FILE"

Step 2: Import data into Postgres using myloader.

Here we had to disable the index while importing the data and enable it after the import is complete.

    myloader --user="$TARGET_DB_USER" \
             --password="$TARGET_DB_PASSWORD" \
             --host="$TARGET_DB_HOST" \
             --port="$TARGET_DB_PORT" \
             --database="$TARGET_DB_NAME" \
             --directory="$DUMP_DIR" \
             --overwrite-tables \
             --innodb-optimize-keys="AFTER_IMPORT_ALL_TABLES" \
             --verbose 3 2>&1 | tee -a "$LOG_FILE"

Step 3: Migrate data from MySQL to Postgres using pgloader. Loading the data into Postgres with pgloader was pretty straight forward and simple as the Source(MySQL) and Target(Postgres) were both in our control. There were no limits, no resource constraints and full access (Thats the beautiful part of self hosting).

export SOURCE_DB_URL="mysql://${TARGET_DB_USER}:${TARGET_DB_PASSWORD}@${TARGET_DB_HOST}:${TARGET_DB_PORT}/${TARGET_DB_NAME}"
export TARGET_DB_URL="postgres://${TARGET_DB_USER}:${TARGET_DB_PASSWORD_PG}@${TARGET_DB_HOST}:${TARGET_DB_PORT_PG}/${TARGET_DB_NAME}"

pgloader config.load

And the config.load had the steps and configuration to import the data into Postgres.

LOAD DATABASE
    FROM {{SOURCE_DB_URL}}
    INTO {{TARGET_DB_URL}}

WITH
    batch rows = 10000,
    batch size = 100 MB,
    prefetch rows = 2000,
    preserve index names,
    drop indexes

SET MySQL PARAMETERS
    net_read_timeout = '90',
    net_write_timeout = '180'

CAST
    type datetime to timestamptz drop default drop not null using zero-dates-to-null,
    type date drop not null using zero-dates-to-null

BEFORE LOAD DO
    $$ CREATE SCHEMA IF NOT EXISTS public; $$;

We’re all set for the biggest migration that has happened since the first commit of FeatureOS.

The entire flow in a nutshell.

Now that the migration scripts are ready (We had a much sophisticated script which had rollbacks, checkpoints and verification steps. Ignored them here for simplicity), we had to come up with a plan to migrate data with near zero downtime.

We used rails application that was powering our product as the API service and a NextJS frontend that consumed the API service.

One thing was clear, we can’t migrate data while the writes are happening as it will keep going to old planetscale and we would have to migrate the difference later and it will keep going on in cycle.

So we decided to migrate the data during the maintenance window with only downtime for write operations.

Phase 1:

• We made the app into read only mode (while our webhook endpoints are still running in write mode to keep integrations working)
• We started the export process from Planetscale.
• We imported the data into bridge MYSQL instance.
• Then imported the data into Postgres from the bridge MYSQL instance.

Phase 2:

• We put the app into full read write mode.
• We merged the Postgress connection branch into main and deployed the changes with new credentials.
• Extracted the data that are created between the time of migration and the time of deployment of new credentials and migrated it to Postgress. (Thankfully we kept created, modified timestamps in the database)
• Rebooted the app to load with write mode again.

Vola! We did it. And the exporting and import into Postgress took us about 10 Mins (Read downtime) and the difference migration took us about 31 seconds (to be precise) which was our write downtime.

We finished the entire migration process in 10 minutes and 31 seconds (While we bought about an hour as the maintenance window).

What we got out of it.

There was a slight regret after the migration that we hestiated to migrate out of Planetscale earlier (And to Postgres). But that was a good call.

• The app performance is much better now, we could see there was almost 80-90% increase in reponse times and also we got more features with Postgres.
• The cost of database came down to 1/2 of what we were paying to Planetscale.
• The power increased by 8x for the half the cost we paid in Planetscale.
• Full control over the database, we could do anything (Like the replication servers we setup later in different regions).
• Much frequent and faster data backups. Planetscale provided two backups a day with 12 hours window and 2 days of retention, while we can purchase more for additional credits. But with the new infrastructure, we did hourly backups with 14 day of retention without any additional cost.

What we didn’t get

• Planetscale’s query insights were a great feature which we might miss, looking for different solutions for that.
• The safe migrations is another good feature that is not available in barebone postgres. But we’re quiet happy with Rail’s way of doing migrations, so it wont bite us a lot.

So overall, it was a good decision to move out of Planetscale and into Postgres. You should also consider it before your database grows out of control and before planetscale puts in more such vendor lock-in features.