iTranslated by AI
Migrating from Firestore to Cloud SQL After 7 Years: Improving Speed, Cost, and Development Experience with Over 100 Million Records
Introduction
Hello, I'm @ymdkit from QuizMarket Inc.
In this article, I'll look back on the data migration we performed in 2025 for our learning management service "Anki Maker" and the effects it brought, including improvements in speed, cost, and development experience.
Main targets for this article:
- Those who are using a free-tier service but are struggling with increasing pay-as-you-go costs as the service scales.
- Those considering moving a locally-contained app to the cloud.
- Those who want to understand the process of migrating a database with zero downtime.
Service Overview
"Anki Maker" is a simple learning management service specialized in "creating," "answering," and "sharing" question sets.
It is used by junior high and high school students for test preparation, as well as by working adults for acquiring qualifications. Since its release approximately 10 years ago, it has surpassed 1.8 million downloads.
Background of Introducing Firestore
Initially, "Anki Maker" stored created question data locally on the device. This was fine in terms of no maintenance cost, but over time the following issues became apparent:
- Inability to share question sets among friends, which was a marketing disadvantage.
- Inability to synchronize data across multiple devices, making it impossible to carry over data when changing devices.
- Inability to view data created by users, increasing the burden on customer support.
At first, we tried to compensate with import/export features, but we decided that storing data in the cloud would be best for both ourselves and the users, leading to the introduction of Firestore.
As far as I can recall, the reasons for choosing Firestore as the database at that time were:
- Generous free tier (up to 50,000 reads / 20,000 writes per day for free).
- No need to build a server; implementation could be completed within the app using the client SDK.
- No server costs.
- Since my technical stack at the time leaned toward the client side, it seemed like it could be introduced with low learning costs.
Since I was a university student at the time of introduction, I remember placing importance on "keeping maintenance costs low."
Background of Deciding to Migrate from Firestore
The introduction of Firestore solved the above issues, allowing us to operate the service while keeping costs low. For a while after that, we operated without any particular complaints, but as the service grew, we began to feel the following challenges:
Speed
We had built Firestore in the default region (United States), and even simple queries required about 1 second of latency. (In 2018, the Tokyo region was not yet supported.)
Note: This had been the same since the introduction, but at that time I was not very familiar with server-side performance, so I didn't worry much about it.
As of 2025, the Tokyo region is available, so this issue should not occur.
Cost
- We exceeded the free tier limits, and the cost became higher than using other database services.
- Due to the nature of the service, the amount of user-created "question set" data kept accumulating, so especially the storage cost became a significant burden. (Over 100 million questions were registered.)
- Since billing was based on the number of reads/writes, we always had to consider whether a new feature would cause the Firestore billing to explode.
Development
- There were several things that were possible with RDB but difficult or not feasible with Firestore, which became a hindrance when adding features.
- Examples: many-to-many relationships, queries considering multiple columns.
- "No need to build a server" meant "business logic is needed for each client," which was not compatible with multi-platform development (app and web).
- Being schema-less with no migration mechanism, data consistency was ensured on the client side, leading to bloated code on the client that "deals with historical issues" (and needed to be implemented for each platform).
Looking back, I think NoSQL databases like Firestore should be chosen by those who understand the basics of RDB, weigh performance and consistency, and then select based on use cases. It should not be chosen just because "I don't know much about the server side, but it has a free tier and can be called directly from the app, which is convenient." (Lesson learned.)
Choosing the Migration Destination
To solve the challenges we felt while operating Firestore, we decided to search for a migration destination based on the following conditions:
- Supports a Japanese region (to reduce latency)
- No pay-as-you-go billing for reads/writes; costs are easy to predict
- Managed infrastructure so we don't have to spend time on infrastructure maintenance
- A straightforward RDB without quirks (doesn't require extensive database-specific knowledge)
As a service that meets these conditions, we chose Cloud SQL for PostgreSQL. The reasons for this choice are:
- We were already using Cloud Run as a wrapper for accessing external services, so compatibility is good (private connections within Google Cloud provide good speed)
- Storage costs are cheaper than Firestore, and the fixed billing model based on memory and CPU makes costs predictable
- Infrastructure tasks unrelated to the service, such as backups and patching, can be offloaded
Migration Flow
When performing a data migration, the first decision is whether to "temporarily stop the service and migrate all at once" or "gradually migrate without stopping the service."
For this migration, we chose to migrate with zero downtime for the following reasons:
- The databases are of different types, so a simple import/export wouldn't work
- If a problem occurs midway, we can quickly roll back (by migrating incrementally)
- No one set a deadline for us, so we can proceed at our own pace
Based on this, we proceeded with the actual data migration according to the following flow.
Step 1: Remove Client Dependence on Firestore and Switch to Reading/Writing Data via an API Server

Before the migration, we were using Firebase's client SDK to read and write data. Therefore, as a preliminary step to migrating the database, we needed to port all data read/write operations to the server side. The scope and workload of this part were one of the major reasons we had hesitated to migrate until now. However, I thought, "This is the time to repay the technical debt I've accumulated," and stopped all new feature development for one month to focus on the migration work. Most of the work itself was a repetition of routine tasks:
- Define APIs in
openapi.yaml(type generation) - Port the client-side repository layer implementation to the server side
- Implement server-side handlers
- Replace the client-side repository layer implementation (from Firestore calls to API server calls)
Since most of this was repetitive, being able to leverage AI agents was also a key factor in completing the work in a relatively short period.
Additionally, regarding the benefit often mentioned in architecture documentation — "defining a repository layer makes it easier to replace the database" — I honestly had doubts about whether such a scenario would actually occur during real service operation. However, through this migration work, I truly experienced its value firsthand.
Step 2: Write Data to Both Firestore and Cloud SQL on the Server Side

When migrating data without stopping the service, a problem arises: "What to do with data added/updated during the migration?" As a solution, we chose the "dual write" method. In the dual write method, data writes are performed on both the old and new databases, so write operations that occur between the start and end of the data migration can be synchronized to the new database.
However, when adopting this method, the following considerations are necessary, so personally, I think it's better to use maintenance mode for data that can be migrated quickly.
- If a write to either database fails, both need to be rolled back
- You need to handle "updates" and "deletes" for data that doesn't exist in the new database
- Example: "If data doesn't exist, treat it as a new insert" or "If data doesn't exist, skip without deleting"
Step 3: Migrate All Data Stored in Firestore to Cloud SQL

After confirming that new write data is being saved to the migration destination, we proceed to migrate existing data. However, since the data in Firestore and Cloud SQL (PostgreSQL) are not compatible, we had to write our own script to perform the migration. In this script, we also transformed the data stored in Firestore, aiming to eliminate the "code to fix historical issues through data transformation" that had accumulated on the client side.
Additionally, Firestore charges based on the number of reads, so we needed to avoid "delete all data and start over if it fails" as much as possible. I'll share the insights gained from this.
Firestore Query Cursors
Firestore provides a query cursor feature for pagination. Since this query can also be used with documentId, we could migrate data sequentially with an implementation like the one below (if an error occurred, we would write the last output cursor to nextCursor and re-run):
// Conceptual implementation
let nextCursor = null;
while (true) {
console.log(`cursor: ${cursor} => migrating...`)
let query = db
.collection('items')
.orderBy(FieldPath.documentId(), 'asc')
if (cursor) {
query = query.startAfter(cursor)
}
/*
...Fetch data from Firestore
...Transform data
...Store in Cloud SQL
*/
nextCursor = items[items.length - 1]?.id
if (nextCursor === null) {
break
}
}
Idempotent Writes
When performing a phased data migration, a key concern is "how to avoid duplicate writes of the same data."
In this migration, we used Prisma. However, simply calling the createMany method without any configuration would cause the script to abort when data conflicted with existing records (e.g., data already written by the dual write). To solve this, we used the skipDuplicates option, which writes if the data doesn't exist and skips if it does (without error):
await prisma.user.createMany({
data: users.map(user => ({
id: user.id,
name: user.name,
})),
skipDuplicates: true,
})
Another point to be careful about is handling arrays within Firestore documents. When migrating such records to an RDB, they need to be added to child tables. If no identifier is set, idempotency is not preserved, and only the child table records will multiply. To avoid this, we set a temporary identifier combining the parent record and an index to ensure idempotency.
// Implementation sample (assuming a "question" holds multiple "answers" in an array)
await prisma.question.createMany({
data: question.map(question => ({
id: question.id,
...
})),
skipDuplicates: true,
})
await prisma.answer.createMany({
data: question.answers.map((answer, index) => ({
id: `${question.id}-${index}`, // Add a unique identifier for idempotency
text: answer,
...
})),
skipDuplicates: true,
})
Step 4: Switch Read Destination to Cloud SQL on the Server Side

After confirming that all data in the table has been migrated to Cloud SQL, we switched the data reference source to Cloud SQL. Note that to allow immediate rollback in case of trouble, writes to Firestore were continued for a while.
Step 5: Stop Writing to Firestore on the Server Side

After confirming that there were no particular problems with reading data from Cloud SQL, we stopped writing to Firestore. In my case, I proceeded based on the criterion of "no errors or inquiries resulting from the data migration occurring after one week of operation."
Step 6: Repeat the Same Process for Each Firestore Table (Collection)
We proceeded with the above flow in order from the tables with the smallest impact scope. Particularly for the migration of the "question" data, which is the core of the service, we had to resolve errors that occurred along the way, and it took about three days to migrate all the data. In the end, I'm glad we chose the zero-downtime migration approach.
Migration Results
Approximately three months have passed since the data migration was completed, and I am currently feeling the effects in the following areas:
Speed
Thanks to the private connection between Cloud Run and Cloud SQL in the Japan region, data retrieval that previously took 1 second can now be achieved in under 100ms.
(It's more accurate to say it was too slow originally, rather than it being fast now.)
Cost
- Storage costs were reduced by 80%.
- The pay-per-use model for Read/Write operations was replaced with a fixed fee for CPU/Memory, making cost projections much easier.
Although the cost of the API server (Cloud Run) increased due to this migration, the combined cost of Cloud Run + Cloud SQL was lower than using Firestore alone. When considering the cost breakeven point, I believe I should have started the migration much sooner.
Development
- Database constraints were removed, expanding the range of possibilities when designing new features (it became easier to design features that rely on relationships).
- User-specific permission management, analysis of answer records, etc.
- Through the migration process, the scope of what could be managed as code expanded, making collaboration with AI agents easier.
- API schema (types): OpenAPI
- DB schema (types): Prisma
- Infrastructure configuration: Terraform
- Business logic was consolidated on the server side, allowing the client-side implementation to remain thin.
- This eliminated the need to write the same processing in multiple places, speeding up feature implementation.
In summary, I believe we were able to break away from the design that was skewed for cost savings (a client-centric architecture) and take a step closer to the ideal form of web service development.
Conclusion
This article looked back at the process of migrating from Firestore, which I had used for a long time, to Cloud SQL. I would be happy if this content proves useful for someone's future technology decisions.
Furthermore, this data migration required modifications across "App," "Web Frontend," "Server," and "Infrastructure," which I feel not only improved the service but also contributed to the growth of my own technical skills.
While we are currently reaping the benefits of the migration, there is no guarantee that this technology choice will continue to be the best one forever. Moving forward, I want to stay attuned to technology trends and continue to be mindful of incorporating new technologies into my products at the right time.
Reference Articles
Discussion