Firestore vs Postgres for App Data

Firestore vs Postgres comes down to how your data is shaped and queried. Firestore is a document database that is excellent for mobile apps with simple, predictable reads, live listeners and offline-first clients, billed per document read and write. Postgres is a relational database that handles joins, ad-hoc queries, reporting and strict constraints far better, billed for the server or plan rather than per operation. If your app has relationships between users, teams, orders and permissions, Postgres usually ends up simpler.

This article compares them on the things that decide day-to-day development, then builds the same feature in both so you can see the difference in code. Firestore facts are from Google's Firestore docs as of November 2026.

#Side by side

#The same feature in both: team chat

A team chat has teams, members, rooms and messages. Users should see only rooms of teams they belong to.

#In Firestore

You design around the screens. Messages live in a subcollection of each room, and membership is copied into the room document so Security Rules can check it:

import { collection, query, orderBy, limit, getDocs, addDoc, serverTimestamp } from 'firebase/firestore'

// Load the latest 50 messages of a room
const q = query(
  collection(db, 'rooms', roomId, 'messages'),
  orderBy('createdAt', 'desc'),
  limit(50),
)
const snap = await getDocs(q)

// Send a message: author name is copied in so the list needs no second lookup
await addDoc(collection(db, 'rooms', roomId, 'messages'), {
  text,
  authorId: user.uid,
  authorName: user.displayName,
  createdAt: serverTimestamp(),
})

// Security Rules
rules_version = '2';
service cloud.firestore {
  match /databases/{database}/documents {
    match /rooms/{roomId}/messages/{messageId} {
      allow read, create: if request.auth != null
        && request.auth.uid in get(/databases/$(database)/documents/rooms/$(roomId)).data.memberIds;
    }
  }
}

This is fast and simple for the chat screen. The costs appear later: when a user changes their display name, every message with the old authorName is stale unless you rewrite them all. When someone leaves a team, every room's memberIds array has to be updated. The get() in the rule is billed as an extra read. And "how many messages did each team send last month?" has no direct query: you'd keep a counter document or export the data to another system.

#In Postgres

You design around the data, then write the query each screen needs:

create table teams (id uuid primary key default gen_random_uuid(), name text not null);
create table team_members (
  team_id uuid references teams on delete cascade,
  user_id uuid references auth.users on delete cascade,
  role text not null default 'member',
  primary key (team_id, user_id)
);
create table rooms (id uuid primary key default gen_random_uuid(),
  team_id uuid not null references teams on delete cascade, name text not null);
create table messages (
  id bigint generated always as identity primary key,
  room_id uuid not null references rooms on delete cascade,
  author_id uuid not null default auth.uid() references auth.users,
  body text not null check (char_length(body) <= 4000),
  created_at timestamptz not null default now()
);
create index on messages (room_id, created_at desc);

-- Row Level Security: members of the room's team can read and post
alter table messages enable row level security;

create policy "Team members read and post" on messages
  for all to authenticated
  using (exists (
    select 1 from rooms r join team_members m on m.team_id = r.team_id
    where r.id = messages.room_id and m.user_id = (select auth.uid())))
  with check (author_id = (select auth.uid()));

The chat screen joins the author's profile at read time, so a name change is one update:

const { data } = await client
  .from('messages')
  .select('id, body, created_at, author:profiles(display_name)')
  .eq('room_id', roomId)
  .order('created_at', { ascending: false })
  .limit(50)

And the report is one query:

select t.name, count(*) as messages
from messages m join rooms r on r.id = m.room_id join teams t on t.id = r.team_id
where m.created_at >= date_trunc('month', now()) - interval '1 month'
  and m.created_at <  date_trunc('month', now())
group by t.name order by messages desc;

Removing a member is deleting one row in team_members; access disappears everywhere at once because the policy checks it live.

#Queries: where the difference shows most

Firestore queries are fast because they're restricted. Some rules from its docs:

• Combining equality and range filters needs a composite index. A database can have 200 composite indexes without billing and 1,000 with billing enabled.
• in and array-contains-any take up to 30 values; queries are limited to 30 disjunctions in disjunctive normal form; not-in takes up to 10 values.
• A != query only matches documents where the field exists.
• offset is billed: you pay a read for every skipped document.

Postgres has none of these structural limits. Any filter, join, sort or aggregate is valid SQL; indexes are a performance tool, not a precondition. The trade-off is that a bad query in Postgres can be slow, where Firestore would have refused it. Running explain analyze on your hot queries is part of using Postgres well.

#Limits to know before you choose

Firestore: documents up to 1 MiB, nested maps or arrays up to 20 levels deep, 40,000 index entries per document, subcollections up to 100 deep, transactions up to 270 seconds with a 60-second idle timeout, API requests up to 10 MiB.

Postgres: practical limits are set by the server: memory, disk and connections. On a hosted backend, plan limits apply too (database size, connections, statement timeout).

#Offline and realtime

This is Firestore's strongest point. Its mobile and web SDKs cache data on the device, serve reads offline and sync writes when the connection comes back, and listeners make live UIs nearly free to build.

Postgres itself has neither, but hosted Postgres backends add them. Realtime over logical replication with RLS applied per subscriber is standard in Supabase-style stacks. Offline sync is less common. StitchDB, for example, has an offline sync layer that keeps a copy of chosen tables on the device, queues writes, and resolves conflicts with a rule per table (latest edit, server wins, device wins, or your own SQL function), while every write is still checked against RLS (offline sync). If offline-first is the core of your app, compare these carefully; Firestore's offline support is mature and built into its SDKs.

#Cost model

Firestore charges per operation. Google Cloud lists single-region Standard edition rates of $0.03 per 100,000 reads and $0.09 per 100,000 writes as of November 2026, after free daily quotas of 50,000 reads and 20,000 writes. Small apps cost very little; costs then grow with every screen view, listener update and fan-out write. Our Firebase pricing breakdown works through an example.

Postgres charges for capacity. On a hosted Postgres backend you pay for a plan or server, and reads and writes are not metered individually. On StitchDB, Pro is $15.99 a month with a 10 GB database per project, 1 TB of egress and 100,000 monthly active users (pricing). A feed that is refreshed ten times as often costs CPU, not a bigger invoice, until you outgrow the plan.

#When Firestore is the better choice

• An offline-first mobile app with simple, screen-shaped data
• Live listeners everywhere, with small documents
• Little relational data and no reporting needs
• You already use Firebase Auth, Analytics and Cloud Messaging and want one console
• You want a managed multi-region location (such as nam5 or eur3) without running replicas yourself

#When Postgres is the better choice

• Data with relationships: users, teams, roles, orders, invoices
• Permissions that depend on other rows (membership, sharing)
• Reporting, admin dashboards, ad-hoc questions
• Constraints you want the database to enforce
• Predictable costs and the option to move providers

Many apps end up with a mix of both shapes. Postgres covers that with jsonb columns for the flexible parts. See SQL vs NoSQL for apps for a broader view.

#Getting Postgres with the parts Firestore gives you

The usual reason teams choose Firestore over Postgres is everything around the database: auth, an API you can call from the app, realtime and file storage. Supabase-style backends bundle these with Postgres. StitchDB gives each project its own Postgres with Auth, a REST API generated from your tables, Realtime, Storage and Edge Functions, plus SDKs for JavaScript, Flutter, Swift and Kotlin (SDK overview). Policies like the one above use auth.uid() directly (Row Level Security). It hosts in the EU only and is younger than Firebase, so weigh that against the data-model benefits.

If you're moving an existing Firestore app, the main work is modeling: turn each collection into a table, subcollections into tables with a foreign key, and copied fields back into joins. Migrate Firebase to Postgres walks through it.

#FAQ

#Is Firestore faster than Postgres?

For the simple, indexed reads Firestore allows, both are fast. Firestore restricts queries so they stay index-backed; Postgres can run far more complex queries, which you need to index well.

#Can Firestore do joins?

No. Each query reads one collection or collection group. You copy data into documents (denormalize) or run several queries and combine them in the client.

#Can Postgres work offline like Firestore?

Not by itself. You need a sync layer on the device. Some hosted Postgres backends, StitchDB among them, provide one with conflict rules and RLS.

#Which is cheaper, Firestore or Postgres?

Firestore is usually cheaper for small, low-traffic apps thanks to its free quotas. Postgres on a fixed plan becomes more predictable and often cheaper as reads grow, because operations aren't billed one by one.

#Can I store documents in Postgres?

Yes. jsonb columns store and index JSON documents, so you can keep flexible fields next to relational ones.

Try the Postgres version of your data model on a free StitchDB project.