It can be intimidating at first when you enter the database world and hear so many terms for the first time: index, schema, generated column, enum, B-tree etc.., but in this series we will demistify common concepts and terms to make the job easier.

1. Schema

Think of a schema as a folder inside your database. Your database can hold many tables, and a schema is just a way to group related tables (and views, functions, etc.) together under one namespace.

By default, every table you create in Postgres lands in a schema called public. You’ve probably been using it this whole time without knowing it existed.

CREATE SCHEMA billing;

CREATE TABLE billing.invoices (
  id serial PRIMARY KEY,
  amount numeric NOT NULL
);

Why bother? A few reasons come up in practice:

• Organization — separate billing, analytics, and auth tables instead of dumping everything into public.
• Permissions — you can grant access to a whole schema at once, so a reporting tool only sees analytics and nothing else.
• Avoiding name clashes — two teams can both have a table called users as long as they live in different schemas (sales.users vs support.users).

If you don’t create one yourself, you don’t need to worry about it — but once your database grows past a handful of tables, schemas are usually the first thing that make it feel organized again.

2. Index

An index is a separate data structure Postgres maintains alongside your table, built specifically to make lookups faster. Without one, finding a row means scanning the entire table top to bottom — fine for a hundred rows, painful for ten million.

The easiest way to think about it is the index at the back of a textbook. You don’t flip through every page looking for “B-tree” — you jump to the index, find the page number, and go straight there. A database index does the same thing for a column.

CREATE INDEX idx_users_email ON users (email);

Now a query like this can jump straight to the matching rows instead of scanning the whole table:

SELECT * FROM users WHERE email = 'jane@example.com';

The catch is that indexes aren’t free. Every time you insert, update, or delete a row, Postgres also has to update the index — so more indexes mean slower writes and extra disk space. The rule of thumb: index the columns you filter, join, or sort by often, and don’t index everything just in case.

Most indexes in Postgres use a structure called a B-tree by default — a balanced tree that keeps data sorted so it can be searched in logarithmic time. It’s the right choice for the vast majority of cases (equality checks, ranges, sorting), which is why it’s the default and you’ll rarely need to think about it further.

3. Generated Column

A generated column is a column whose value Postgres computes for you from other columns in the same row, instead of you writing the value yourself on every insert.

CREATE TABLE products (
  id serial PRIMARY KEY,
  price numeric NOT NULL,
  tax_rate numeric NOT NULL,
  total_price numeric GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED
);

Here, total_price isn’t something you set — Postgres calculates it automatically every time price or tax_rate changes. You can query it just like any other column:

SELECT total_price FROM products WHERE id = 1;

Try to insert or update total_price directly and Postgres will stop you — that’s the whole point, it keeps the derived value in sync with the source columns so it can never drift out of date.

The STORED keyword means the value is actually written to disk and kept up to date automatically, as opposed to being recalculated on every read. As of Postgres 18, STORED is not the only option available — virtual generated columns (computed on the fly, not saved to disk) are introduced.

Generated columns are handy for things like full names (first_name || ' ' || last_name), search-friendly text combinations, or — as above — values you’d otherwise be recalculating in application code on every write.

That’s it for part 1. Next time we’ll get into enums and take a closer look at how B-trees actually work under the hood.