PostgreSQL Features: JSONB, Full-Text Search, Arrays and Safe Migrations

Lessons 0 to 6 used plain tables and the shop. This lesson adds the features that make PostgreSQL popular, then shows how to evolve a table while users are writing to it. Locks and transactions come from lesson 6, indexes from lesson 5, and constraints from lesson 4. For migrations with Prisma see Full-Stack Q&A, Lesson 08; for how to roll a release out safely (feature flags, canaries, rollback) see Full-Stack Q&A, Lesson 26.

Words used in this lesson

• JSONB is a column type that stores a JSON document in a binary form that PostgreSQL can index and search. (The older json type stores the text as is and is rarely the right choice.)
• A GIN index (Generalized Inverted Index) maps each piece inside a value (a JSON key, a word, an array element) to the rows that contain it. It suits columns that hold many things.
• A generated column is a column whose value is computed from other columns of the same row. STORED means it is written to disk.
• A tsvector is a document prepared for search: a sorted list of normalised words with positions. A tsquery is a search condition on words.
• Stemming cuts a word to its root, so "running" and "runs" both match "run".
• An upsert is "insert, or update if the row already exists".
• A migration is a script that changes the schema (and sometimes data). A table rewrite means PostgreSQL copies the whole table to new files.
• Expand and contract is a two-phase way to change a schema: add the new thing next to the old, move over, then remove the old.

JSONB: documents inside a row

Say each product has a few attributes that differ by type (a laptop has RAM, a shirt has a size). A jsonb column holds them without a new column for each. I built a 500,000-row table (83 MB):

CREATE TABLE products_j (  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  name        text NOT NULL,  price_cents integer NOT NULL,  attrs       jsonb NOT NULL DEFAULT '{}');-- 500,000 rows, e.g.:-- {"tags": ["basic"], "brand": "Zenith", "color": "green", "weight_g": 179, "wireless": true}

The operators you need:

I ran the first three on row 1 and got exactly the values shown in the table. The counts on the full table: @> '{"brand":"Acme","wireless":true}' matched 18,696 rows, attrs->'tags' ? 'eco' matched 24,800.

A GIN index and a real EXPLAIN

Contains (@>) with no index is a sequential scan. A very selective question: red, Acme, wireless and tagged "eco" (128 rows of 500,000):

EXPLAIN (ANALYZE, BUFFERS)SELECT id FROM products_j WHERE attrs @> '{"brand":"Acme","color":"red","wireless":true,"tags":["eco"]}';

 Seq Scan on products_j  (cost=0.00..16888.00 rows=50 width=8) (actual time=0.457..173.240 rows=128 loops=1)   Filter: (attrs @> '{"tags": ["eco"], "brand": "Acme", "color": "red", "wireless": true}'::jsonb)   Rows Removed by Filter: 499872   Buffers: shared hit=10638 Execution Time: 173.355 ms

Now the index:

CREATE INDEX products_j_attrs_gin ON products_j USING gin (attrs);

 Bitmap Heap Scan on products_j  (cost=82.10..272.44 rows=50 width=8) (actual time=16.151..16.610 rows=128 loops=1)   Recheck Cond: (attrs @> '{"tags": ["eco"], "brand": "Acme", "color": "red", "wireless": true}'::jsonb)   Heap Blocks: exact=128   Buffers: shared hit=750   ->  Bitmap Index Scan on products_j_attrs_gin  (cost=0.00..82.08 rows=50 width=0) (actual time=16.108..16.109 rows=128 loops=1)         Index Cond: (attrs @> '{"tags": ["eco"], "brand": "Acme", "color": "red", "wireless": true}'::jsonb)         Buffers: shared hit=622 Execution Time: 16.658 ms

From 173 ms to 16.7 ms, and 10,638 buffers to 750. Read it like lesson 5: the Bitmap Index Scan finds the 128 matching rows through the GIN index, the heap scan reads only their 128 pages. The index is 7.7 MB. A second flavour, gin (attrs jsonb_path_ops), was 4.9 MB here. It supports only @> (and the path operators), not ?, so it is smaller and a bit faster when containment is all you need.

When JSONB is the wrong choice

JSONB is good for data that really is a bag: webhook payloads, per-type attributes you rarely filter, settings. It is the wrong choice when:

1. You filter, join or sort on a field all the time. That field should be a real column. A column has a type, a NOT NULL, a CHECK, a foreign key, and statistics. The JSON key has none of these. A typo ("colour") is accepted forever.
2. The planner cannot see inside. PostgreSQL keeps statistics per column, not per JSON key. For the plain filter on one key it guessed a fixed small share:

 Seq Scan on products_j  (cost=0.00..18138.00 rows=2500 width=8) (actual time=0.017..111.193 rows=62517 loops=1)   Filter: ((attrs ->> 'brand'::text) = 'Acme'::text)

It expected 2,500 rows and got 62,517, 25 times more. Wrong estimates are how a good join becomes a slow one (lesson 5). A real brand column would have known that Acme is 12.5% of the table. 3. You need relations. You cannot put a foreign key on a key inside a document. Use a table. 4. Documents are big and updated often. An update of one small key writes a new copy of the whole JSONB value (from the documentation, not measured here), and big values are stored out of line. Many small writes to big documents are expensive.

A good compromise: keep stable, queried fields as columns, and put the long tail in one jsonb column.

Generated columns

A generated column keeps a derived value always correct, with no trigger and no way for the app to forget it:

CREATE TABLE items (  id                   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  name                 text NOT NULL,  price_cents          integer NOT NULL,  tax_pct              numeric(4,1) NOT NULL DEFAULT 18.0,  price_with_tax_cents integer GENERATED ALWAYS AS (round(price_cents * (1 + tax_pct / 100))::int) STORED,  name_lower           text GENERATED ALWAYS AS (lower(name)) STORED);INSERT INTO items (name, price_cents) VALUES ('Notebook', 350), ('Pen', 120);UPDATE items SET tax_pct = 5 WHERE name = 'Pen';SELECT name, price_cents, tax_pct, price_with_tax_cents, name_lower FROM items;

   name   | price_cents | tax_pct | price_with_tax_cents | name_lower ----------+-------------+---------+----------------------+------------ Notebook |         350 |    18.0 |                  413 | notebook Pen      |         120 |     5.0 |                  126 | pen

(Before the UPDATE, the Pen showed 142.) The database recomputed 126 when the tax changed. You cannot write to the column:

ERROR:  cannot insert a non-DEFAULT value into column "price_with_tax_cents"DETAIL:  Column "price_with_tax_cents" is a generated column.

In PostgreSQL 16 only STORED generated columns exist (they take disk space and are computed on write). The expression must be immutable: only the row's own columns and deterministic functions. A generated column can be indexed, which is a tidy alternative to an expression index. The best use is the next topic.

Full-text search

WHERE body ILIKE '%waterproof%' finds text, but it has no stemming, no ranking and cannot use a normal index. On 200,000 reviews (108 MB) it took 394 ms by sequential scan. PostgreSQL has real search built in.

Two types do the work. to_tsvector turns text into normalised words, dropping common ones ("stop words") and stemming:

SELECT to_tsvector('english', 'The quick brown foxes were jumping over lazy dogs');

 'brown':3 'dog':9 'fox':4 'jump':6 'lazi':8 'quick':2

The numbers are word positions. A tsquery is the question, and @@ means "matches". Three ways to build one:

SELECT websearch_to_tsquery('english', '"noise cancelling" headphones -cheap');SELECT plainto_tsquery('english', 'wireless headphones');

 'nois' <-> 'cancel' & 'headphon' & !'cheap' 'wireless' & 'headphon'

websearch_to_tsquery reads the syntax people type into a search box (quotes mean "these words next to each other", - means "not") and never raises a syntax error, so it is the one to use for user input. to_tsquery is stricter and is for queries you build yourself.

The table, the generated column, the index

Store the prepared document in a generated column, with title words weighted higher than body words, and put a GIN index on it:

CREATE TABLE docs (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title text NOT NULL, body text NOT NULL);-- 200,000 rows of random review wordsALTER TABLE docs ADD COLUMN search tsvector  GENERATED ALWAYS AS (setweight(to_tsvector('english', title), 'A') ||                       setweight(to_tsvector('english', body),  'B')) STORED;CREATE INDEX docs_search_gin ON docs USING gin (search);

The index is 15 MB. Without it (a cold cache, so it includes reading from disk) the query for two words took 69 ms; with it:

EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM docs WHERE search @@ websearch_to_tsquery('english', 'waterproof refund');

 Bitmap Heap Scan on docs  (cost=33.72..2308.93 rows=697 width=8) (actual time=0.084..0.093 rows=26 loops=1)   Recheck Cond: (search @@ '''waterproof'' & ''refund'''::tsquery)   Heap Blocks: exact=4   Buffers: shared hit=15   ->  Bitmap Index Scan on docs_search_gin  (cost=0.00..33.55 rows=697 width=0) (actual time=0.077..0.077 rows=26 loops=1)         Index Cond: (search @@ '''waterproof'' & ''refund'''::tsquery) Execution Time: 0.118 ms

Fifteen buffers and 0.12 ms for the 26 matches. (Note the estimate rows=697 against 26 actual: text search estimates are rough.)

Ranking and snippets

ts_rank scores how well a document fits; weights (the A and B above) change the score. ts_headline cuts a snippet and wraps the hits in <b>:

SELECT id, title, round(ts_rank(search, q)::numeric, 4) AS rankFROM docs, websearch_to_tsquery('english', 'waterproof refund') qWHERE search @@ q ORDER BY rank DESC, id LIMIT 3;

   id   |     title     |  rank  --------+---------------+-------- 180000 | Review 180000 | 0.3972  60000 | Review 60000  | 0.3894  85000 | Review 85000  | 0.3810

 180000 | <b>refund</b> delivery delivery ink bass paper great <b>waterproof</b>

Rank everything that matches, not the whole table: the WHERE ... @@ filter uses the index first, then you sort the few hits. Limits: the dictionary decides the language ('english' above), ts_rank is not as clever as a search engine, and typo tolerance needs an extension such as pg_trgm (not run here). For millions of documents with facets and typo tolerance, a dedicated search service may be better. For many apps, this is enough and has no second system to keep in sync.

Arrays

A column can hold an array: tags text[]. It is handy for a small set of labels that belong to one row and are not their own entity. (If you need to join on tags or add data to them, use the junction table from lesson 4.)

CREATE TABLE posts (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title text, tags text[] NOT NULL DEFAULT '{}');INSERT INTO posts (title, tags) VALUES ('A', '{sql,postgres}'), ('B', ARRAY['go','postgres','perf']), ('C', '{}');SELECT title FROM posts WHERE 'postgres' = ANY (tags);              -- A, BSELECT title FROM posts WHERE tags @> ARRAY['postgres','perf'];     -- B      (contains all)SELECT title FROM posts WHERE tags && ARRAY['sql','go'];            -- A, B   (overlaps: any)SELECT title, t FROM posts, unnest(tags) AS t ORDER BY title, t;    -- one row per element

I ran all four and got the rows in the comments. Arrays start at index 1: tags[1] is the first. cardinality(tags) is the length. = ANY (...) also works on the other side: WHERE id = ANY (ARRAY[1,3,99]) is the neat, safe way to pass a list of ids as one parameter (found rows 1 and 3).

Indexing is where the trap is. On 500,000 rows, searching for one tag:

 WHERE tags @> ARRAY['tag777']  no index:           Seq Scan   ... Execution Time: 115.176 ms CREATE INDEX ON posts_big USING gin (tags); WHERE tags @> ARRAY['tag777']  with GIN:           Bitmap Index Scan ... Execution Time: 0.293 ms WHERE 'tag777' = ANY (tags)    with the same GIN:  Seq Scan   ... Execution Time: 66.398 ms

The GIN index serves @> and && but not value = ANY (column). Write the contains form when the column is indexed.

Upsert: INSERT ... ON CONFLICT

"Insert, or update if it exists" done as SELECT then INSERT has a race (lesson 6). ON CONFLICT does it atomically in one statement, using a unique index to decide what a conflict is:

CREATE TABLE stock_levels (  product_id int PRIMARY KEY,  qty        int NOT NULL CHECK (qty >= 0),  updated_at timestamptz NOT NULL DEFAULT now());INSERT INTO stock_levels (product_id, qty) VALUES (1, 10);INSERT INTO stock_levels (product_id, qty) VALUES (1, 99);

ERROR:  duplicate key value violates unique constraint "stock_levels_pkey"DETAIL:  Key (product_id)=(1) already exists.

Two answers to that conflict. DO NOTHING skips the row (silently: 0 rows inserted, no error; good for idempotency keys in lesson 6). DO UPDATE changes the existing row, and EXCLUDED names the row you tried to insert:

INSERT INTO stock_levels (product_id, qty) VALUES (1, 5), (2, 7)ON CONFLICT (product_id) DO UPDATE SET qty = stock_levels.qty + EXCLUDED.qty, updated_at = now()RETURNING product_id, qty, (xmax = 0) AS inserted;

 product_id | qty | inserted ------------+-----+----------          1 | 15 | f          2 |  7 | t

Product 1 existed, so its quantity became 10 + 5 = 15. Product 2 was new. Two notes. (xmax = 0) is a common trick to tell an insert from an update in RETURNING; it relies on an internal column, so use it for logging, not for core logic. And a DO UPDATE ... WHERE can skip the update when a condition fails: for product_id 2 with WHERE stock_levels.qty < 5 I got (0 rows) back, because 7 is not less than 5.

RETURNING

INSERT, UPDATE and DELETE can all return the rows they touched, so you do not need a second query (which also saves a round trip and avoids a race):

UPDATE stock_levels SET qty = qty - 3 WHERE product_id = 1 RETURNING qty AS new_qty;   -- 12WITH gone AS (DELETE FROM stock_levels WHERE product_id = 2 RETURNING *)INSERT INTO items (name, price_cents) SELECT 'moved-' || product_id, qty FROM gone RETURNING id, name;

The second statement moved a row from one table to another in one atomic statement and returned 3 | moved-2. ORMs use RETURNING to get the generated id after an insert.

Safe migrations

Now the second half. A migration that is fine on your laptop can freeze production. From lesson 6: most ALTER TABLE commands take an ACCESS EXCLUSIVE lock, which blocks everything, and any lock request that waits blocks the requests behind it. So a safe migration has two aims: hold strong locks only for milliseconds, and never rewrite or scan a big table while holding one.

I used an orders table with 2,000,000 rows (117 MB), the same shape as lesson 5.

Adding a column

ALTER TABLE orders ADD COLUMN note text;ALTER TABLE orders ADD COLUMN channel text NOT NULL DEFAULT 'web';ALTER TABLE orders ADD COLUMN created_check timestamptz NOT NULL DEFAULT clock_timestamp();

Time: 2.311 ms       -- nullable column, no defaultTime: 2.485 ms       -- NOT NULL with a constant defaultTime: 3843.403 ms    -- NOT NULL with a volatile default (clock_timestamp())

I also compared the file of the table (pg_class.relfilenode) before and after. After the first two it was the same file; after the third it changed, and the table grew from 117 MB to 148 MB.

• A nullable column, or a column with a constant default, is a change of the catalog only (since PostgreSQL 11). The default is remembered and filled in when old rows are read. Milliseconds on any table size.
• A volatile default (clock_timestamp(), random(), gen_random_uuid()) must be computed for every existing row, so PostgreSQL rewrites the whole table while holding ACCESS EXCLUSIVE. Nearly 4 seconds of total outage here for 2 million rows; on a 200 GB table it is hours.

Backfill in batches

An UPDATE orders SET region = 'EU' on all 2,000,000 rows is one huge transaction: long locks on rows, a huge amount of WAL, bloat, and a long snapshot that blocks vacuum (lesson 6). Do small batches, each its own transaction:

ALTER TABLE orders ADD COLUMN region text;      -- 4 msDO $$DECLARE lo bigint := 0; maxid bigint;BEGIN  SELECT max(id) INTO maxid FROM orders;  WHILE lo < maxid LOOP    UPDATE orders SET region = 'EU' WHERE id > lo AND id <= lo + 100000 AND region IS NULL;    COMMIT;                                     -- each batch is its own transaction    lo := lo + 100000;  END LOOP;END $$;

Time: 8088.925 ms (00:08.089)       -- 20 batches of 100,000 rows

Total time is longer than a single UPDATE would be, but nobody waits for more than one batch, and you can stop and restart it (the region IS NULL condition makes it safe to rerun). In a real system, add a short sleep between batches and run it from a script or job, not inside the deploy.

NOT NULL on a big table

Now I want region to be NOT NULL. The direct way:

ALTER TABLE orders ALTER COLUMN region SET NOT NULL;     -- 354 ms

It worked, but it scans the whole table to prove there are no NULLs, while holding ACCESS EXCLUSIVE. 354 ms for 2 million rows, blocking reads and writes for that time, growing with the table. The safe way uses a CHECK constraint that is added without scanning, then validated with a weaker lock:

ALTER TABLE orders ADD CONSTRAINT region_not_null CHECK (region IS NOT NULL) NOT VALID;  -- 3.6 msALTER TABLE orders VALIDATE CONSTRAINT region_not_null;                                  -- 236 msALTER TABLE orders ALTER COLUMN region SET NOT NULL;                                     -- about 1 msALTER TABLE orders DROP CONSTRAINT region_not_null;

NOT VALID means "enforce for new writes, do not check old rows yet", so it is instant. VALIDATE scans the table, but I looked at pg_locks inside the validating transaction and the lock was ShareUpdateExclusiveLock, which lets reads and writes continue. Once a validated CHECK (region IS NOT NULL) exists, PostgreSQL (version 12 and later) trusts it and SET NOT NULL skips the scan, so it took about a millisecond. The same NOT VALID then VALIDATE trick works for foreign keys and other CHECKs.

CREATE INDEX CONCURRENTLY

A plain CREATE INDEX takes a SHARE lock: reads continue, writes wait. I started the build in one session and an INSERT in another:

A| CREATE INDEX orders_cust_idx ON orders (customer_id);     -- Time: 949.567 msB| INSERT INTO orders (...) VALUES (...);                      -- Time: 849.525 ms   (waited for the build)A| CREATE INDEX CONCURRENTLY orders_cust_idx ON orders (customer_id);   -- Time: 1372.105 msB| INSERT INTO orders (...) VALUES (...);                      -- Time: 1.486 ms     (not blocked)

The insert waited 850 ms for the plain build and 1.5 ms during the concurrent one. CONCURRENTLY is slower (1.4 s against 0.95 s here, it scans the table twice) but does not block writes. The rules:

1. It cannot run inside a transaction block. I tried: ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Many migration tools wrap every file in a transaction, so check how yours lets you turn that off.
2. If it fails, it leaves an invalid index behind that still costs writes. I created a unique index on a column with duplicates:

ERROR:  could not create unique index "orders_cust_uq"DETAIL:  Key (customer_id)=(30061) is duplicated.

      index      | indisvalid -----------------+------------ orders_pkey     | t orders_cust_uq  | f

\d orders listed it as UNIQUE, btree (customer_id) INVALID. Drop it (DROP INDEX CONCURRENTLY) and try again. 3. Drop big indexes with DROP INDEX CONCURRENTLY too.

Lock timeouts: do not queue behind someone

Even a 2 ms ALTER can freeze the table if it waits for a lock (lesson 6: the queue). Set a short lock_timeout so the migration gives up and retries later, instead of blocking everyone. I held a lock in one session and ran the migration in a loop:

for i in 1 2 3 4 5; do  if psql -d mig -c "SET lock_timeout = '500ms'" -c "ALTER TABLE orders ADD COLUMN flag boolean"; then echo "attempt $i: done"; break; fi  echo "attempt $i: gave up, sleeping"; sleep 1done

ERROR:  canceling statement due to lock timeoutattempt 1: gave up, sleepingERROR:  canceling statement due to lock timeoutattempt 2: gave up, sleepingattempt 3: done

After I ended the blocking transaction the third attempt went through. Put SET lock_timeout (and statement_timeout as a ceiling) at the top of every migration, and retry on failure. Also check there are no long-running transactions before you start (lesson 6).

Expand and contract

Some changes cannot be one step: renaming a column, changing a type, splitting a table. A rename done in one ALTER breaks every running server that still uses the old name, so during a rolling deploy (old and new code live at the same time) you get errors. Expand and contract makes each step safe for both versions of the code:

Each deploy can be rolled back on its own. I ran this to "rename" amount_cents to total_cents with a trigger doing the sync:

ALTER TABLE orders ADD COLUMN total_cents integer;                       -- expandCREATE FUNCTION orders_sync_total() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN  NEW.total_cents := NEW.amount_cents;     -- the old column is still the source of truth  RETURN NEW;END $$;CREATE TRIGGER orders_sync_total BEFORE INSERT OR UPDATE ON orders  FOR EACH ROW EXECUTE FUNCTION orders_sync_total();-- backfill in batches: UPDATE orders SET total_cents = amount_cents WHERE id > lo AND id <= lo + 200000 AND total_cents IS NULL;SELECT count(*) FILTER (WHERE total_cents IS DISTINCT FROM amount_cents) AS mismatches, count(*) FROM orders;

 mismatches |  count  ------------+---------          0 | 2000003

An old-style UPDATE orders SET amount_cents = 2000 WHERE id = 2 came back with total_cents = 2000, and a new insert of 888 came back with 888 in both. After the new code was live I would run the contract step: DROP TRIGGER, DROP FUNCTION, ALTER TABLE orders DROP COLUMN amount_cents; (I ran these too; the drop of a column is fast because PostgreSQL only marks it hidden). I did not run a real application against it, so the "deploy" steps are the plan, not a measurement.

How to test a migration

1. Run it on production-sized data, not on the 10-row dev database. A restored copy (or a database cloned with CREATE DATABASE test TEMPLATE prod_copy) shows the real time. The 2-million-row table made the 3.8 second rewrite and the 354 ms scan obvious; a 1,000-row table would hide both.
2. Watch the locks. Inside a transaction, run the statement and read pg_locks for your session. I ran ADD COLUMN on a copy and saw AccessExclusiveLock for 1.3 ms. Anything ACCESS EXCLUSIVE that is not milliseconds needs a different plan.
3. Diff the schema. pg_dump -s before and after, then diff. Mine showed exactly one added line (coupon text) plus a random token that newer pg_dump versions add to each dump.
4. Test the rollback too. Know what "down" is, and that the old code still works against the new schema (and the new code against the old one).
5. Run with timeouts (lock_timeout, statement_timeout) in the test, as production will.
6. Do it in the pipeline, with a deploy step that runs the migration before the new code and fails the release if it times out (see Full-Stack Q&A, Lesson 26).

Try it

Use any scratch database.

1. Create a table with a jsonb column and 100,000 rows. Compare a @> query before and after a GIN index. Then query ->> on one key and look at the row estimate.
2. Build a tsvector generated column and a GIN index on a table of text, then search with websearch_to_tsquery, ranked with ts_rank.
3. Write an upsert that counts page views: one row per page, add 1 on each visit. Run it twice.
4. Add DEFAULT gen_random_uuid() as a new NOT NULL column to a 1,000,000-row table. Predict whether the table is rewritten, then check relfilenode before and after.
5. Make email NOT NULL on a big table without a long ACCESS EXCLUSIVE scan.

Answers

1. With the index the plan is a Bitmap Index Scan on the GIN index (for selective filters). For ->> the estimate is a fixed guess unless there is a matching expression index (my numbers: expected 2,500, got 62,517).
2. SELECT id, ts_rank(search, q) AS rank FROM docs, websearch_to_tsquery('english','your words') q WHERE search @@ q ORDER BY rank DESC LIMIT 10; and check EXPLAIN shows the GIN index.
3. INSERT INTO page_views (page, views) VALUES ('/home', 1) ON CONFLICT (page) DO UPDATE SET views = page_views.views + 1; with page as primary key. The first run inserts 1, the second makes it 2. (Reasoned from the stock example; not run with this table.)
4. It is rewritten: gen_random_uuid() is volatile, so relfilenode changes. (Reasoned from the clock_timestamp() test, same rule; I did not run this exact statement.) The safe way: nullable column, batch backfill, then CHECK ... NOT VALID, VALIDATE, SET NOT NULL.
5. ADD CONSTRAINT ... CHECK (email IS NOT NULL) NOT VALID, VALIDATE CONSTRAINT, then SET NOT NULL, then drop the check. Backfill the NULLs first.

Interview questions

Key takeaways

• JSONB is for the long tail of flexible data. Fields you query, join or constrain should be real columns, because JSON keys have no statistics, types or foreign keys.
• A GIN index serves containment-style operators (@>, ?, full-text @@, array @> and &&), not every expression. Use a B-tree on an expression for one hot key.
• Full-text search is built in: tsvector in a generated column, a GIN index, websearch_to_tsquery for user input, ts_rank to sort.
• INSERT ... ON CONFLICT is the atomic upsert; RETURNING gives the row back without a second query.
• On a big table, check what each ALTER does: volatile defaults rewrite the table, SET NOT NULL scans it, plain CREATE INDEX blocks writes.
• Use NOT VALID then VALIDATE, CREATE INDEX CONCURRENTLY, batched backfills with a commit per batch, and always a short lock_timeout with retries.
• Change names and types with expand and contract, so each deploy is safe alone and can be rolled back.
• Test migrations on production-sized data and look at the locks.

What's next

That is the end of this series. You can now read a query, join and aggregate, design a schema, read a plan, reason about concurrent writes, and change a live database safely. To use these skills under an API see Backend, Lesson 07, for the ORM side Full-Stack Q&A, Lesson 08, and for the theory behind replication and scale, the database part of the System Design series. The best practice now is your own project: build a schema, load a million rows, and break it on purpose with two psql windows.