Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 31 ยท Data Modeling and Patterns

Modeling Data: Documents vs Tables

Model the same app as Firestore documents and as Postgres tables, and learn denormalization, counters, fan-out writes and how to migrate a data shape.

The hardest part of using Firebase or Supabase is not the SDK. It is deciding what shape your data has. In a relational database such as the Postgres behind Supabase, you normalize: store each fact once and join tables when you read. In Firestore, there are no joins, so you usually denormalize: copy data to where it will be read, and accept extra work on writes.

Neither style is better in general. Each one makes some operations cheap and others expensive. In this lesson you will model one small blog app both ways, then count reads and writes in a Python stand-in to see the trade-off in numbers. The SDK snippets are read-only (they need real projects); the Python is our own stand-in.

Two ways to store one fact

Normalize Store once, join on read.
Denormalize Copy to where it is read.

Reads and writes pull in opposite directions.

1 / 2

The example app

A blog has authors, posts and comments. The screen we care about is the feed: the latest posts, each with the author's name and a comment count. Two other actions matter: an author renames themselves, and a reader adds a comment.

The relational model

In Postgres you create tables and link them with foreign keys. Every fact lives in one place:

SQL
-- Supabase SQL editor (read-only)
create table authors (
  id   bigint generated always as identity primary key,
  name text not null
);

create table posts (
  id        bigint generated always as identity primary key,
  author_id bigint not null references authors(id),
  title     text not null,
  created_at timestamptz not null default now()
);

create table comments (
  id      bigint generated always as identity primary key,
  post_id bigint not null references posts(id) on delete cascade,
  body    text not null
);

The feed is one query with a join and an aggregate, and supabase-js can express the join through the foreign key:

JavaScript
// Supabase (read-only)
const { data } = await supabase
  .from("posts")
  .select("id, title, authors(name), comments(count)")
  .order("created_at", { ascending: false })
  .limit(20);

Renaming an author is a single update on one row, and every post shows the new name immediately. The price is that the database does the join work on every read, which is fine at moderate scale and is helped by indexes. If you need a refresher on joins, see the SQL tutorial.

The document model

In Firestore you decide up front what the screen reads. To avoid a second lookup per post, you copy the author's name into each post and store a counter on the post:

JavaScript
// Firebase (read-only)
// posts/{postId}
{
  title: "Why I like boats",
  authorId: "a1",
  authorName: "Ana",        // copied from authors/a1
  commentCount: 2,          // maintained on every comment write
  createdAt: serverTimestamp()
}

The feed is now one query on one collection, with no joins:

JavaScript
// Firebase (read-only)
const q = query(collection(db, "posts"), orderBy("createdAt", "desc"), limit(20));
const snapshot = await getDocs(q);

Reading is fast and predictable. Writing is where the bill comes: renaming Ana means finding every post she wrote and updating authorName in each, and adding a comment means writing the comment and incrementing commentCount, ideally in one atomic step. See Reading and Writing Documents for batches and increment.

Count the work

The stand-in below stores the same data both ways in plain Python dictionaries and counts how many "reads" and "writes" each operation costs. One lookup of one record or document counts as one read; one stored record or document changed counts as one write. This is a simplification of real billing and real performance, but it shows the shape of the trade-off.

Python Same app, two models, counted operations

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Look at the output. The document model reads three documents for the feed, while the relational stand-in reads far more (it joins and scans). On the other hand, renaming costs one write relationally and three in the document model, and adding comments costs twice as many writes. If posts have thousands of copies, the rename becomes a large fan-out job.

The numbers are tiny here, but the shape holds as data grows: document reads stay flat, document writes multiply. In real Postgres you would add an index on comments(post_id) and the join would not scan, so the relational read cost is lower than our naive stand-in shows. Real systems also differ in latency and price, so use this as a way of thinking, not a benchmark.

One-to-many

  • Relational: a foreign key on the "many" side (posts.author_id).
  • Firestore, small and bounded: an array or map inside the parent, such as a few tags or addresses. A document is limited in size, so never embed an unbounded list.
  • Firestore, large or growing: a subcollection, posts/{id}/comments/{commentId}, which you can query and paginate on its own.

Many-to-many

Students and courses are the classic case.

  • Relational: a join table enrollments(student_id, course_id) with a composite primary key.
  • Firestore: either a top-level enrollments collection (one document per pair, easy to query from both sides), or arrays of IDs on each side (student.courseIds) when the lists are short and you want to read them together. Arrays are awkward for large sets and need both sides updated together.

Counters and consistency

Counting rows with count(*) is natural in SQL. In Firestore, a stored counter such as commentCount is cheap to read but must be updated in the same atomic write as the comment, using a batch, transaction or increment. A single document is limited in how fast it can be updated, so a very hot counter (thousands of updates per second) needs sharding into several counter documents summed on read. Newer Firestore aggregation queries (count(), sum()) can count without storing a counter; see Search, Aggregations and Analytics.

Postgres gives you transactions across tables and constraints that keep copies honest (foreign keys, not null, check constraints). With denormalized documents you keep consistency yourself, in code or in Cloud Functions that react to changes.

Fan-out writes

A fan-out is one logical change that writes many documents. Typical cases: renaming an author, posting to a follower feed (write one copy per follower), or removing a user's data. Rules of thumb:

  1. Keep the source of truth in one place (authors/a1) and treat copies as a cache you can rebuild.
  2. Do large fan-outs in batches (a batch write has a documented limit on operations; check the current docs) or in a background function, not in the browser.
  3. Only copy fields that rarely change (a name, an avatar URL), never fast-moving values like a balance.
  4. Make fan-out code idempotent, so running it twice is harmless.

Migrating shapes

Your first model will not survive contact with real users. Plan for change:

  • Add before you remove. Add the new field, write both old and new, backfill old data, switch readers, then remove the old field.
  • Give documents a schemaVersion field so readers can handle old and new shapes while a backfill runs.
  • In Postgres, migrations are files run with the CLI; in Firestore, a backfill is a script that pages through a collection and updates documents.

Choosing

Question Leans toward
Do you know every screen's queries now? Documents
Will analysts ask new questions later? Tables
Is read speed at huge scale the top goal? Documents
Do many tables need strict consistency? Tables
Is the data a tree owned by one user? Documents
Is it highly connected (orders, products, customers)? Tables

Many teams use Supabase simply because SQL is forgiving when requirements change. Others pick Firestore because a feed or chat reads naturally as documents. Compare the platforms in Firebase vs Supabase: Choosing.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does the document model usually copy the author's name into each post?

    Without joins, you put the data you read together in one document. The price is extra work when the original changes.
  2. 2. What is a fan-out write?

    Renaming an author that is copied into 500 posts is a fan-out of 500 document writes.
  3. 3. Which is the safest way to change a field's shape in production?

    Adding first keeps old and new code working while data is converted.
  4. 4. Where does an unbounded list of comments belong in Firestore?

    Documents have a size limit and arrays that grow forever are hard to query or paginate. Use a collection.

Summary

  • Relational models store each fact once and join on read; document models copy data to match reads.
  • Document reads are cheap and flat, but renames, counters and other updates fan out into many writes.
  • Use foreign keys and join tables in Postgres; use subcollections, top-level collections or small arrays in Firestore.
  • Keep one source of truth, make fan-outs batched and idempotent, and copy only slow-changing fields.
  • Migrate by adding, dual-writing, backfilling, switching readers and only then removing.

Next: Multi-Tenant and Team Apps