Designing Scalable, Realtime-Ready Applications with Generated Columns

Modern applications increasingly rely on derived data—values calculated from other fields rather than entered directly. Think BMI calculated from height and weight, profit margins derived from cost and price, or latitude and longitude extracted from geospatial points.

The challenge is not how to compute these values, but how to do so at scale, without slowing down queries or overloading your database. This is where generated columns in PostgreSQL shine.

The Scalability Problem with On-the-Fly Calculations

A common pattern in database queries is to compute values dynamically:

  • Calculate BMI using weight / height²

  • Compute profit margin during a SELECT query

  • Extract coordinates from a geospatial column at query time

While this works for small datasets, it becomes expensive as tables grow. If your query computes a formula for one million rows, that calculation happens one million times per query. Sorting or filtering on that computed value only makes things worse.

This approach does not scale.

Generated Columns: Derived Data Done Right

Generated columns allow you to define a column whose value is automatically computed from other columns in the same table. Once defined, the value is:

  • Stored on disk

  • Automatically updated when source columns change

  • Queryable like a normal column

  • Fully indexable

In other words, you move computation from query time to write time.

For example, instead of recalculating BMI on every query, you define a generated bmi column once. PostgreSQL computes it whenever height or weight changes, and queries simply read the stored value .

Why Indexing Changes Everything

The real power of generated columns comes from indexing. Because generated columns are stored, you can:

  • Create B-tree indexes on them

  • Filter efficiently using WHERE

  • Sort using ORDER BY

  • Let the query planner use indexes instead of full table scans

This transforms expensive analytical queries into fast, predictable operations—no triggers, no views, no repeated calculations.

Beyond Math: Human-Readable Geospatial Data

Generated columns are not limited to arithmetic.

In applications using geospatial data (via the PostGIS extension), location data is often stored as a geography(Point) type. While efficient, this format is not frontend-friendly—especially in realtime systems.

By creating generated columns for latitude and longitude, you can:

  • Extract readable coordinates from a point

  • Expose them directly to clients

  • Avoid parsing complex GIS strings in your application code

When paired with realtime updates in Supabase, this means frontend clients receive clean, usable location data instantly whenever a row changes .

Realtime-First Applications Benefit the Most

Generated columns are especially powerful in realtime systems because:

  • Values are already computed before events are emitted

  • Realtime payloads contain derived fields without extra logic

  • Clients stay thin and simple

  • Updates propagate automatically when source data changes

This makes generated columns ideal for dashboards, live maps, leaderboards, analytics tools, and monitoring systems.

Rules and Constraints to Keep in Mind

Generated columns come with a few important limitations:

  1. They can only reference columns in the same table

  2. A generated column cannot depend on another generated column

  3. You cannot manually insert or update their values

These constraints are intentional—they guarantee determinism and make indexing possible.

A Shift in Database Design Thinking

Generated columns encourage a subtle but powerful shift:

Databases are not just for storing data—they can model derived knowledge efficiently.

By precomputing and indexing derived values, you simplify application code, improve performance, and unlock realtime capabilities without added complexity.

Practical Use Cases

🏥 Health & Fitness Apps

Key pattern: expensive calculations → stored generated columns → indexed filtering

  1. Health Risk Scoring Platform

    • Inputs: age, weight, height, blood pressure, cholesterol

    • Generated columns:

      • BMI

      • risk_score (composite formula)

    • Queries like:

      • “Show users with risk_score > 0.8”

    • Realtime alerts when risk crosses thresholds

  2. Nutrition Tracking App

    • Inputs: meals, macros, calories

    • Generated columns:

      • net_calories

      • protein_ratio

    • Fast ranking and filtering without recalculating on every query

💰 Finance & Commerce

Key pattern: profit / margin formulas that must scale to millions of rows

  1. E-commerce Profit Dashboard

    • Inputs: cost, price, shipping, tax

    • Generated columns:

      • profit

      • profit_margin

    • Indexed sorting:

      • “Top 1% most profitable SKUs”

    • Realtime updates when prices change

  2. Subscription SaaS Analytics

    • Inputs: MRR, churn, refunds

    • Generated columns:

      • net_mrr

      • ltv_estimate

    • Enables fast cohort analysis without triggers

🗺️ Location & Maps

Key pattern: PostGIS data → human-readable + realtime

  1. Realtime Delivery Tracking App

    • Inputs: geography(Point)

    • Generated columns:

      • latitude

      • longitude

    • Used directly in frontend maps

    • Realtime driver movement without parsing GIS strings

  2. Location-Based Marketplace

    • Generated columns:

      • distance_to_user (from stored point)

    • Indexed geo-queries for:

      • “Restaurants within 2km”

📊 Data Products & Internal Tools

Key pattern: replace triggers & views with indexed generated columns

  1. Feature Flag / Experimentation Platform

    • Inputs: user attributes

    • Generated columns:

      • eligibility_score

      • variant_bucket

    • Instant segmentation at scale

  2. Fraud Detection System

    • Inputs: transaction amount, frequency, velocity

    • Generated columns:

      • anomaly_score

    • Indexed queries like:

      • “Show suspicious transactions in last 5 minutes”

step-by-step guide

0) Prereqs

Option A — Run locally (recommended for learning)

You need:

  • psql (Postgres CLI)

  • A Postgres instance (local Docker is easiest)

Option B — Run in Supabase

You’ll run the SQL in:

  • Supabase Dashboard → SQL Editor
    …and optionally use:

  • Database → Realtime Inspector (for the realtime part)

1) Terminal setup (Local Postgres with Docker)

1.1 Start Postgres

docker run --name gencols-pg -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=demo -p 5432:5432 -d postgres:16

1.2 Connect with psql

psql "postgresql://postgres:postgres@localhost:5432/demo"

1.3 (Optional) Make output easier to read

Inside psql:

\x on
\timing on

2) Example 1 — “People” table + BMI (the scalability issue)

2.1 Create the table

Prompt (run in psql / SQL editor):

drop table if exists people;

create table people (
  id bigint generated by default as identity primary key,
  name text not null,
  height_m numeric not null,
  weight_kg numeric not null
);

2.2 Insert sample data

insert into people (name, height_m, weight_kg) values
('Brandy', 1.80, 50),
('Sam',    1.75, 72),
('Alicia', 1.62, 60),
('Noah',   1.90, 95);

2.3 Calculate BMI “on the fly” (what doesn’t scale)

This is the approach the video warns about: the formula runs every query, for every row.

select
  id,
  name,
  height_m,
  weight_kg,
  (weight_kg / (height_m * height_m)) as bmi
from people
order by bmi;

3) Solve it with a STORED generated column

3.1 Add a generated BMI column

This matches the “generated always … stored” pattern from the video.

alter table people
add column bmi numeric
generated always as (weight_kg / (height_m * height_m)) stored;

3.2 Confirm the table now has BMI populated

select id, name, height_m, weight_kg, bmi
from people
order by bmi;

3.3 Add an index on the generated column

The transcript calls out that generated columns are indexable because they’re stored and updated on writes.

create index people_bmi_idx on people (bmi);

3.4 Verify the index is used (EXPLAIN)

explain analyze
select id, name, bmi
from people
where bmi > 25
order by bmi;

Tip: You’re looking for an Index Scan / Bitmap Index Scan referencing people_bmi_idx (planner output varies by dataset size).

4) Prove it updates automatically

The video updates Brandy’s height and shows BMI recalculates automatically.

update people
set height_m = 1.70
where name = 'Brandy';

Now re-check:

select id, name, height_m, weight_kg, bmi
from people
where name = 'Brandy';

5) Rules to remember (from the video)

Generated columns have two important constraints mentioned in the transcript:

  1. You can only reference columns in the same table

  2. You can’t reference a generated column inside another generated column

6) Example 2 — PostGIS + Supabase realtime-friendly lat/long

This portion demonstrates turning a geography(Point) into human-readable latitude/longitude using generated columns.

6.1 Enable PostGIS (local Postgres)

create extension if not exists postgis;

(In Supabase, PostGIS is usually available; you can enable it in Database → Extensions if needed.)

6.2 Create a restaurants table

drop table if exists restaurants;

create table restaurants (
  id bigint generated by default as identity primary key,
  name text not null,
  location geography(point) not null
);

6.3 Insert sample points

Note: ST_MakePoint(longitude, latitude) order matters.

insert into restaurants (name, location) values
('Pasta Place',  ST_SetSRID(ST_MakePoint(8.5417, 47.3769), 4326)::geography), -- Zurich-ish
('Sushi Spot',   ST_SetSRID(ST_MakePoint(8.5480, 47.3700), 4326)::geography);

6.4 Add generated latitude/longitude columns

PostGIS’s ST_X and ST_Y work on geometry, so we cast geography → geometry.

alter table restaurants
add column longitude double precision
generated always as (st_x(location::geometry)) stored;

alter table restaurants
add column latitude double precision
generated always as (st_y(location::geometry)) stored;

6.5 Query readable coordinates

select id, name, latitude, longitude
from restaurants
order by id;

7) Supabase realtime inspector steps (UI walkthrough)

This mirrors the video’s “Realtime Inspector” demonstration where lat/long show up cleanly in events.

  1. In Supabase Dashboard, go to Database → Realtime Inspector

  2. Create a realtime listener / subscription for the restaurants table

    • If prompted, ensure Realtime is enabled for the table (Replication settings)

  3. Keep the inspector open

  4. Run an update:

    update restaurants
set name = name || '!'
where id = 1;

  5. In the realtime event payload, verify you see:

    • latitude

    • longitude
      …instead of only an unreadable geospatial blob

8) Quick checklist for applying this “methodology” in real apps

Use a STORED generated column when:

  • You repeatedly compute a formula in queries

  • You filter/sort by that derived value

  • You want indexes + predictable performance

  • You want realtime payloads to contain ready-to-use derived fields

If you tell me whether you’re running this locally or in Supabase, I can tailor the exact commands (e.g., project setup, where to click for enabling PostGIS/realtime replication, and the exact subscription filter you should use).

Conclusion

Generated columns are one of PostgreSQL’s most underused features, yet they solve a very real scalability problem. They replace slow, repetitive query-time computations with fast, indexed, write-time derivations.

If you are building analytics-heavy, realtime, or data-driven applications, adopting this pattern early can save you from performance bottlenecks—and unnecessary complexity—down the road.

Vibe Coding, SupabaseFrancesca Tabor