Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 31 ยท Data Modeling and Patterns

Search, Aggregations and Analytics

Count and sum in Firestore, run Postgres full-text and fuzzy search in Supabase, know when to use external search or BigQuery, and use SQL views.

Sooner or later someone asks for a search box and a dashboard: "find posts about boats" and "how many orders per country this month?" These two requests are where the two platforms differ the most. Postgres was built for questions like these. Firestore was built for fast, indexed lookups and has no built-in full-text search.

In this lesson you will see what each platform offers for aggregations (counting, summing, grouping) and search (words, typos), when to reach for an external service or a data warehouse, and how SQL views package a report. The platform code is read-only; the Python TryIt is a stand-in that shows how a search index and a group-by work inside.

Four kinds of questions

Aggregate Count, sum, average, group.
Search Words, prefixes, typos.
Analytics Trends over lots of history.
Views Save a query as a virtual table.

Choose the tool that was built for the question.

1 / 2

Aggregations in Firestore

Counting by downloading every document is slow and expensive, since you pay a read per document. Firestore offers server-side aggregation queries that compute the answer without sending the documents:

JavaScript
// Firebase (read-only)
import { collection, query, where, getCountFromServer, getAggregateFromServer, sum, average } from "firebase/firestore";

const paid = query(collection(db, "orders"), where("status", "==", "paid"));

const countSnap = await getCountFromServer(paid);
console.log(countSnap.data().count);

const totals = await getAggregateFromServer(paid, {
  revenue: sum("total"),
  avgOrder: average("total"),
});
console.log(totals.data().revenue, totals.data().avgOrder);

Aggregations use the same indexes as normal queries, and they are billed by the index entries they scan, not by documents transferred, which is much cheaper than reading every document. Check the current pricing page for the exact rule. What you cannot do is group by: there is no "revenue per country" in one query. For that you either run one aggregate per group, or keep summary documents that you update on every write (a counter per day, per country), as discussed in Modeling Data: Documents vs Tables.

Aggregations in Postgres

In Supabase you have all of SQL. Grouping is one statement:

SQL
-- Supabase SQL editor (read-only)
select country,
       count(*)           as orders,
       sum(total)         as revenue,
       round(avg(total), 2) as avg_order
from orders
where status = 'paid'
  and created_at >= date_trunc('month', now())
group by country
order by revenue desc;

The client library reads rows rather than running arbitrary reports, so for aggregates you usually wrap the SQL in a function and call it with rpc (see Querying with supabase-js):

SQL
-- Supabase (read-only)
create function revenue_by_country(since timestamptz)
returns table (country text, orders bigint, revenue numeric)
language sql stable
as $$
  select country, count(*), sum(total)
  from orders
  where status = 'paid' and created_at >= since
  group by country;
$$;
JavaScript
// Supabase (read-only)
const { data, error } = await supabase.rpc("revenue_by_country", {
  since: "2026-11-01T00:00:00Z",
});

A function runs with the caller's rights unless you mark it security definer, so Row Level Security still applies by default. Think carefully before bypassing it; see Row Level Security Policies.

Search in Firestore: not built in

Firestore queries can match a field exactly, compare ranges, or test array membership. There is no "contains the word boat". The classic trick for prefix search is a range query:

JavaScript
// Firebase (read-only)
const q = query(
  collection(db, "products"),
  orderBy("nameLower"),
  startAt("boa"),
  endAt("boa\uf8ff")
);

That finds names starting with "boa" and nothing else. It is case-sensitive unless you store a lowercase copy, and it knows nothing about typos or word order. For real search the usual answer is an external search service (Algolia, Typesense, Meilisearch, Elasticsearch or similar) fed by a Cloud Function that mirrors documents on every write. Firebase does not ship one itself, so you own the sync: index on create and update, remove on delete, and backfill when you change the index.

Search in Postgres

Postgres includes full-text search. You convert text to a tsvector (normalized word list) and match it with a tsquery:

SQL
-- Supabase (read-only)
alter table posts
  add column search tsvector
  generated always as (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')),  'B')
  ) stored;

create index posts_search_idx on posts using gin (search);

select id, title, ts_rank(search, query) as rank
from posts, websearch_to_tsquery('english', 'boat repair') as query
where search @@ query
order by rank desc
limit 10;

English stemming means a search for "repairing" can match "repair". Weights let titles count more than bodies. With supabase-js, the textSearch filter does the match for you:

JavaScript
// Supabase (read-only)
const { data } = await supabase
  .from("posts")
  .select("id, title")
  .textSearch("search", "boat repair", { type: "websearch", config: "english" });

For typo tolerance and "contains" matching, enable the pg_trgm extension. It splits strings into three-letter pieces (trigrams) and compares overlap:

SQL
-- Supabase (read-only)
create extension if not exists pg_trgm with schema extensions;
create index products_name_trgm on products using gin (name extensions.gin_trgm_ops);

select name, similarity(name, 'kayka') as score
from products
where name % 'kayka'          -- fuzzy match
order by score desc
limit 5;

This is enough for many sites. When you need advanced ranking, facets, synonyms and instant results across millions of records, an external search engine is still the better tool, on either platform. Postgres can also do semantic search with the pgvector extension; that is a topic of its own.

Analytics and BigQuery

Reports over a long history are a different workload from serving your app. Running huge scans on your production database competes with your users. The usual pattern is to copy data to a warehouse.

  • Firebase can export Firestore data to BigQuery (through an extension or managed export) so analysts can run SQL over it without touching production. Firebase Analytics events also export to BigQuery.
  • Supabase is already Postgres, so start with SQL, views and a read replica or a separate reporting role if you have them on your plan. When data outgrows that, replicate to a warehouse with an ETL or CDC tool.

Check each product's current documentation for setup, cost and limits.

SQL views

A view is a saved query that behaves like a table. It keeps report logic in one place:

SQL
-- Supabase (read-only)
create view daily_revenue as
select date_trunc('day', created_at) as day,
       count(*) as orders,
       sum(total) as revenue
from orders
where status = 'paid'
group by 1;

Be careful with security. By default a view runs with the view owner's rights, which can bypass RLS on the underlying tables and leak rows. Newer Postgres versions support security_invoker, which makes the view obey the caller's policies:

SQL
-- Supabase (read-only)
alter view daily_revenue set (security_invoker = on);

A view over sensitive data in the public schema is exposed through the API like a table, so decide who may select it. This is one of the "common leaks" from the security lessons.

A stand-in for search and group-by

The Python below builds, in miniature, what a search engine and a database do. It makes an inverted index (word to document IDs) for word search, uses trigram overlap for typo tolerance like pg_trgm, and runs a group by with a dictionary. It is simplified (no stemming, ranking or scaling) and is not how Postgres is implemented, but it shows why an index makes lookups cheap.

Python Stand-in search index and group-by

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.

The index answers "which documents contain this word?" with a dictionary lookup instead of scanning every text. The typo kayka shares many trigrams with kayak, so it scores highest. And the group-by is just a dictionary keyed by country: each row is visited once, and the result is the same table the SQL query would return. Postgres does the same job with indexes and a query planner, on data that does not fit in memory.

Choosing a tool

Need Firebase Supabase
Count or sum with a filter getCountFromServer, getAggregateFromServer SQL aggregate through rpc or a view
Group by Summary documents, or BigQuery group by
Word search External service Full-text search
Typos and contains External service pg_trgm
Long-history analytics BigQuery export SQL, views, then a warehouse

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is getCountFromServer better than reading all documents and counting them?

    Aggregation queries run on the server using indexes, so you avoid transferring and paying for every document.
  2. 2. Which pair is correct for Postgres search?

    Full-text search uses tsvector and tsquery with stemming. pg_trgm compares trigrams for similarity.
  3. 3. What is the safest way to add search to Firestore for rich word matching?

    Firestore has no full-text search. An external service fed by function triggers is the usual answer.
  4. 4. What is the danger of a plain view in the public schema?

    Views run with the owner's rights by default. Use security_invoker or restrict who may select from the view.

Summary

  • Firestore has server-side count, sum and average, but no group by; keep summary documents or export to BigQuery for grouped reports.
  • Postgres gives you group by, functions called through rpc, and views for reports.
  • Firestore has no full-text search; use an external search service kept in sync by functions.
  • Postgres has full-text search (tsvector, tsquery) and fuzzy matching (pg_trgm).
  • Protect views with security_invoker or explicit access rules, and keep heavy analytics off the production path.

Next: Analytics, Crashlytics and Performance Monitoring