Learn How to Choose Between Cosmos DB, Firestore and Postgres

Introduction

This article shares how we choose between Azure Cosmos DB, Google Firestore and PostgreSQL when a new feature needs a place to store data.

We run all three across our platform. That is not a plan we made on day one. It happened one decision at a time, and each decision had a reason. This article is those reasons in one place.

If you have not read the earlier articles in this series, they cover each database on its own. This one is about the choice between them.

The Short Answer

Pick the database that matches how you will read the data, not the one you like most.

Everything below is that sentence with details.

What We Actually Use

Data	Database	Why
Blog posts and comments	Cosmos DB	Read one document by slug, thousands of times a day
Contact form messages	Firestore	Shared by several sites in the group
Batch job output, before review	Postgres	Needs GROUP BY and COUNT to review it

Three types of data, three different read patterns, three databases.

With the following questions, you can make the same choice.

1. How will you read it?
2. Who else needs it?
3. Does it need review before anybody sees it?
4. Can you afford one more database?

How Will You Read It?

This is the first question and it decides most cases.

"Give me this one thing by its id." Use a document store. In Cosmos DB a point read costs about 1 RU, the cheapest thing the service does. Our blog reads a post by slug, and the slug is the document id, so the URL is a point read. No index lookup, no join, no query.

"Give me everything matching these conditions, grouped and counted." Use a relational database. Not because a document store cannot do it, but because you will write a script each time instead of one line of SQL. Our batch review asks "how many rows today compared to yesterday, and how many warnings by reason". That is GROUP BY. Stage it where you can query it.

"Give me a live list that updates on many screens." Firestore, if you want its realtime listeners. We do not use that feature, but it is the reason many teams choose it, and it is a genuine difference.

My suggestion: write down the two or three sentences your app will actually ask the database, in plain English, before you choose. The answer is usually obvious once those sentences exist.

Who Else Needs It?

This is the question people forget, and it is the one that moved our contact form.

We already ran Cosmos DB. Adding a contacts container there was the easy choice. But several sites in our group collect contact messages, and they already shared one Firestore project.

So the real comparison was not "Cosmos DB vs Firestore". It was:

• Put it in Cosmos DB, and build a screen later that merges messages from several sites, or
• Put it in the collection the group already reads, with a domain field on every message.

We chose the second. The inbox for one site is one filter. The inbox for the whole group is the same query without the filter. No merge layer, ever.

The blog stayed in Cosmos DB, because nothing else in the group reads it.

Note the shape of this decision. It has nothing to do with which database is better. "Which cloud runs my code" and "where does this data belong" are two separate questions, and the second one is about who else touches the data.

Does It Need Review Before Anybody Sees It?

If a person must approve the data before users see it, you need two places — a staging place and a serving place.

We use Postgres for staging and Firestore for serving, with one promote step between them.

The rule is simple. The job that produces the data never writes to the store your users read. Only the promote step does.

Without that split, a job that fails halfway leaves half a dataset visible, and no document store gives you a transaction across 900 documents to undo it.

If your job is small and its output is obviously right or obviously broken, skip this. You do not need a staging layer to store five rows.

Can You Afford One More Database?

Now the honest part, because the first three questions will happily talk you into four databases.

Every extra database costs you:

• One more client and one more cold start path in your API.
• One more secret to store, rotate and keep out of Git.
• One more mental model. Cosmos DB has RU budgets and partition keys. Firestore has composite indexes and a different failure message. Postgres has connection limits. Whoever is on call at 2am has to hold all of them.
• No transactions across the boundary. Nothing in our platform needs one today. We would think hard before designing something that did.
• One more thing to decommission. When one of our projects retired its batch backend, the staging tables stayed behind. Nothing writes to them and nobody dropped them. A database you forgot about is still a database you pay for and still a place your data sits.

So the fourth question is real. If the first three do not give you a clear answer, stay where you are. A slightly awkward query in the database you already run beats a perfect query in a database nobody on the team knows.

The Rule We Follow Now

Multi-cloud as a plan is usually a mistake. You get the complexity of both clouds and the benefit of neither.

Multi-cloud as a result is fine. One dataset had a clear reason to live somewhere specific, the line between datasets is clean, and the reason is written down.

The last part is the one that keeps it maintainable. Every one of our three choices has a paragraph in CLAUDE.md explaining it. Six months later nobody has to guess whether the contact form is in Firestore by accident.

Conclusion

In this article we learned four questions to ask before choosing a database — how you will read the data, who else needs it, whether it needs review before publishing, and whether you can afford one more system.

The short version stays the same. Match the database to the read pattern, follow the data when somebody else already owns it, and write down why you chose it.

Reference

• Azure Cosmos DB partitioning
• Firestore data model
• PostgreSQL JSON types