PostgreSQL Zero to Advanced #10: Window Functions, JSONB, Full-Text Search & More

This is the final part of PostgreSQL Zero to Advanced. Our course platform now has users, courses, orders, instructors, wallets, an access log with half a million rows, a purchase function and an audit trigger. To finish, we explore the features that make PostgreSQL stand out: window functions, JSONB, full-text search, UPSERT, roles and permissions and backups.

Window functions

Aggregates with GROUP BY collapse rows into one row per group. Window functions compute across a set of related rows but keep every row. The syntax is function() OVER (PARTITION BY ... ORDER BY ...).

Ranking inside groups

Rank courses by price within each category:

SELECT title,
       category,
       price,
       RANK() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank
FROM courses
ORDER BY category, price_rank;

Output:

              title               | category | price  | price_rank 
----------------------------------+----------+--------+------------
 Python for Data Analysis         | data     | 300000 |          1
 PostgreSQL Performance Tuning    | database | 350000 |          1
 Advanced SQL Queries             | database | 275000 |          2
 SQL Fundamentals                 | database | 150000 |          3
 Docker Essentials for Developers | devops   | 200000 |          1
 Next.js Full Stack               | web      | 400000 |          1
 React for Beginners              | web      | 250000 |          2
 JavaScript Basics                | web      | 120000 |          3
(8 rows)

PARTITION BY category restarts the ranking for each category. The three ranking functions differ only on ties: ROW_NUMBER() always gives 1, 2, 3; RANK() gives 1, 1, 3; DENSE_RANK() gives 1, 1, 2.

Top N per group

"The best-selling course in each category" is a classic interview question. Compute a ROW_NUMBER() in a CTE and keep row 1:

WITH ranked AS (
    SELECT c.title,
           c.category,
           COUNT(o.id) AS paid_orders,
           ROW_NUMBER() OVER (
               PARTITION BY c.category
               ORDER BY COUNT(o.id) DESC, c.title
           ) AS rn
    FROM courses c
    JOIN orders o ON o.course_id = c.id AND o.status = 'paid'
    GROUP BY c.id, c.title, c.category
)
SELECT category, title, paid_orders
FROM ranked
WHERE rn = 1
ORDER BY category;

Output:

 category |          title           | paid_orders 
----------+--------------------------+-------------
 data     | Python for Data Analysis |           3
 database | SQL Fundamentals         |           3
 web      | JavaScript Basics        |           3
(3 rows)

The Docker draft has no paid orders, so the inner join leaves out the devops category. JavaScript Basics leads the web category thanks to Agus' purchase in Part 9, and Python leads data after Andi paid and Budi bought it through buy_course.

Running totals

Adding ORDER BY inside OVER makes the window grow row by row, which gives a cumulative sum. Paid revenue over July and August 2025:

SELECT id,
       ordered_at::date AS order_date,
       amount,
       SUM(amount) OVER (ORDER BY ordered_at, id) AS running_total
FROM orders
WHERE status = 'paid'
  AND ordered_at < '2025-09-01'
ORDER BY ordered_at, id;

Output:

 id | order_date | amount | running_total 
----+------------+--------+---------------
  1 | 2025-07-01 | 150000 |        150000
  3 | 2025-07-03 | 120000 |        270000
  5 | 2025-07-05 | 150000 |        420000
  2 | 2025-07-10 | 275000 |        695000
  4 | 2025-07-20 | 250000 |        945000
  6 | 2025-08-02 | 300000 |       1245000
  7 | 2025-08-04 | 120000 |       1365000
  9 | 2025-08-15 | 400000 |       1765000
 10 | 2025-08-18 | 300000 |       2065000
(9 rows)

Order 6 appears now: it was pending in Part 5, and the trigger test in Part 9 marked it paid.

Comparing with the previous row: LAG

LAG() reads a value from the previous row in the window (LEAD() reads the next one). Month-over-month revenue change:

WITH monthly AS (
    SELECT to_char(ordered_at, 'YYYY-MM') AS month,
           SUM(amount) AS revenue
    FROM orders
    WHERE status = 'paid'
      AND ordered_at < '2025-10-01'
    GROUP BY month
)
SELECT month,
       revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly
ORDER BY month;

Output:

  month  | revenue | change  
---------+---------+---------
 2025-07 |  945000 |        
 2025-08 | 1120000 |  175000
 2025-09 |  900000 | -220000
(3 rows)

August is now 1,120,000 instead of the 820,000 we saw in Part 5, again because order 6 was paid. The first month has no previous row, so its change is NULL.

JSONB

JSONB stores JSON in a binary, indexable format. It is ideal for flexible attributes that differ between rows. Let's add course details:

ALTER TABLE courses ADD COLUMN details JSONB NOT NULL DEFAULT '{}';

UPDATE courses SET details = '{"duration_hours": 12, "language": "id", "certificate": true,  "modules": ["SELECT", "JOIN", "GROUP BY"]}' WHERE id = 1;
UPDATE courses SET details = '{"duration_hours": 20, "language": "en", "certificate": true,  "modules": ["EXPLAIN", "Indexes", "Vacuum"]}' WHERE id = 2;
UPDATE courses SET details = '{"duration_hours": 10, "language": "id", "certificate": false, "modules": ["Variables", "Functions"]}' WHERE id = 3;
UPDATE courses SET details = '{"duration_hours": 18, "language": "id", "certificate": true,  "modules": ["Components", "Hooks", "State"]}' WHERE id = 4;

Output:

ALTER TABLE
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1

The essential operators:

• -> returns a JSON value (keeps quotes for strings); ->> returns text.
• ? checks whether a key exists.
• @> checks whether the left JSON contains the right JSON.

SELECT title,
       details->>'language'                AS language,
       (details->>'duration_hours')::int   AS hours,
       details->'modules'->0               AS first_module,
       jsonb_array_length(details->'modules') AS module_count
FROM courses
WHERE details ? 'duration_hours'
ORDER BY hours DESC;

Output:

             title             | language | hours | first_module | module_count 
-------------------------------+----------+-------+--------------+--------------
 PostgreSQL Performance Tuning | en       |    20 | "EXPLAIN"    |            3
 React for Beginners           | id       |    18 | "Components" |            3
 SQL Fundamentals              | id       |    12 | "SELECT"     |            3
 JavaScript Basics             | id       |    10 | "Variables"  |            2
(4 rows)

Notice first_module keeps its quotes because -> returns JSON, and we cast ->> text to int to sort numerically. Containment is the most index-friendly way to filter:

SELECT title
FROM courses
WHERE details @> '{"certificate": true, "language": "id"}';

Output:

        title        
---------------------
 SQL Fundamentals
 React for Beginners
(2 rows)

Update a single key with jsonb_set instead of rewriting the whole document:

UPDATE courses
SET details = jsonb_set(details, '{duration_hours}', '14')
WHERE id = 1
RETURNING title, details->'duration_hours' AS duration_hours;

Output:

      title       | duration_hours 
------------------+----------------
 SQL Fundamentals | 14
(1 row)

UPDATE 1

A GIN index makes @>, ? and similar operators fast on large tables, just like the array index in Part 8:

CREATE INDEX idx_courses_details ON courses USING GIN (details);

Output:

CREATE INDEX

Rule of thumb: if you filter, join or enforce constraints on a field regularly, it deserves a real column. Keep JSONB for genuinely flexible data.

Full-text search

In Part 4 we searched with ILIKE '%sql%', which cannot use a normal index and does not understand language. Full-text search turns text into normalized lexemes:

SELECT to_tsvector('english', 'Learning the Advanced SQL Queries') AS document,
       to_tsquery('english', 'queries')                         AS query;

Output:

                document                |  query  
----------------------------------------+---------
 'advanc':3 'learn':1 'queri':5 'sql':4 | 'queri'
(1 row)

"Learning" became learn, "Queries" became queri, and the stop word "the" disappeared. A query for "queries" matches "query" and "queried" too. Let's store a search vector as a generated column so it is always in sync, and index it with GIN:

ALTER TABLE courses
ADD COLUMN search tsvector
GENERATED ALWAYS AS (
    to_tsvector('english', title || ' ' || category)
) STORED;

CREATE INDEX idx_courses_search ON courses USING GIN (search);

Output:

ALTER TABLE
CREATE INDEX

websearch_to_tsquery accepts the kind of input users type into a search box (words, "quoted phrases", or, -exclude). Multiple words must all match:

SELECT title,
       ROUND(ts_rank(search, q)::numeric, 4) AS rank
FROM courses,
     websearch_to_tsquery('english', 'sql database') AS q
WHERE search @@ q
ORDER BY rank DESC, title;

Output:

        title         |  rank  
----------------------+--------
 Advanced SQL Queries | 0.0985
 SQL Fundamentals     | 0.0985
(2 rows)

"PostgreSQL Performance Tuning" is not returned: its category is database, but "PostgreSQL" becomes the single lexeme postgresql, not sql. Use or to match any word:

SELECT title
FROM courses
WHERE search @@ websearch_to_tsquery('english', 'react or python')
ORDER BY title;

Output:

          title           
--------------------------
 Python for Data Analysis
 React for Beginners
(2 rows)

UPSERT with ON CONFLICT

"Insert this row, or update it if it already exists" is extremely common, for example when syncing users from an identity provider. ON CONFLICT does it atomically, with no race condition between a SELECT and an INSERT. Ignore duplicates:

INSERT INTO users (name, email, city, joined_at)
VALUES ('Budi Santoso', 'budi@example.com', 'Bogor', '2025-10-01')
ON CONFLICT (email) DO NOTHING;

Output:

INSERT 0 0

INSERT 0 0: nothing inserted, no error. Or update the existing row. EXCLUDED refers to the row you tried to insert:

INSERT INTO users (name, email, city, joined_at)
VALUES ('Budi Santoso', 'budi@example.com', 'Bogor', '2025-10-01')
ON CONFLICT (email) DO UPDATE
SET city = EXCLUDED.city
RETURNING id, name, city, joined_at;

Output:

 id |     name     | city  | joined_at  
----+--------------+-------+------------
  1 | Budi Santoso | Bogor | 2025-01-10
(1 row)

INSERT 0 1

Budi's city changed to Bogor, while his id and joined_at stayed the same because we only updated city. Upserts also make "add to balance" easy. Siti already has a wallet; Rudi does not:

INSERT INTO wallets (user_id, balance) VALUES
    (2, 50000),
    (5, 75000)
ON CONFLICT (user_id) DO UPDATE
SET balance = wallets.balance + EXCLUDED.balance
RETURNING user_id, balance;

Output:

 user_id | balance 
---------+---------
       2 |  150000
       5 |   75000
(2 rows)

INSERT 0 2

Siti went from 100,000 to 150,000, and Rudi got a brand-new wallet with 75,000. ON CONFLICT needs a unique constraint or primary key on the conflict columns.

Roles and permissions

Your application should never connect as the postgres superuser. PostgreSQL uses roles for both users and groups. A good pattern is a group role that holds permissions plus login roles that inherit them. Here is a read-only role for a reporting tool:

-- a group role that can only read
CREATE ROLE app_readonly NOLOGIN;
GRANT CONNECT ON DATABASE course_platform TO app_readonly;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO app_readonly;

-- a login user that inherits the read-only permissions
-- TODO: Securely load this value from an environment variable or secrets vault. Do not hardcode.
CREATE ROLE report_user LOGIN PASSWORD '[REDACTED_PASSWORD]' IN ROLE app_readonly;

Output:

CREATE ROLE
GRANT
GRANT
GRANT
ALTER DEFAULT PRIVILEGES
CREATE ROLE

The password in this example is a placeholder. In real projects, generate it in your secrets manager (for example HashiCorp Vault or AWS Secrets Manager) and set it from a secure deployment script, never in a migration file committed to Git. ALTER DEFAULT PRIVILEGES makes sure tables created in the future are readable too.

SELECT rolname, rolcanlogin
FROM pg_roles
WHERE rolname IN ('app_readonly', 'report_user')
ORDER BY rolname;

Output:

   rolname    | rolcanlogin 
--------------+-------------
 app_readonly | f
 report_user  | t
(2 rows)

Let's verify the permissions by switching to that role inside our session:

SET ROLE report_user;

SELECT COUNT(*) AS courses FROM courses;
DELETE FROM courses WHERE id = 8;

RESET ROLE;

Output:

SET
 courses 
---------
       8
(1 row)

ERROR:  permission denied for table courses
RESET

Reading works and deleting is denied, exactly what a reporting user should be able to do. For your application itself, create a separate role with SELECT, INSERT, UPDATE, DELETE on the tables it needs, and keep schema changes (CREATE, ALTER, DROP) for a migration role.

Backups with pg_dump

A database without tested backups is a disaster waiting to happen. pg_dump creates a consistent snapshot of one database while it keeps running. Store the password in a ~/.pgpass file (with chmod 600) or load it from your secrets manager instead of typing it on the command line:

# TODO: Securely load this value from an environment variable or secrets vault. Do not hardcode.
# ~/.pgpass format: hostname:port:database:username:password

# Custom format (-F c): compressed, and lets pg_restore pick tables
pg_dump -h localhost -U postgres -d course_platform -F c -f course_platform.dump

# Plain SQL format: human-readable, restore with psql
pg_dump -h localhost -U postgres -d course_platform -f course_platform.sql

# Schema only, or a single table
pg_dump -h localhost -U postgres -d course_platform --schema-only -f schema.sql
pg_dump -h localhost -U postgres -d course_platform -t orders -F c -f orders.dump

Restore into a fresh database and verify the data:

createdb -h localhost -U postgres course_platform_restore
pg_restore -h localhost -U postgres -d course_platform_restore course_platform.dump
psql -h localhost -U postgres -d course_platform_restore -c "SELECT COUNT(*) AS orders FROM orders;"

Output:

 orders
--------
     16
(1 row)

All 16 orders (the original 14, plus the two purchases from Part 9) are there. Roles are cluster-wide and not included in pg_dump; back them up with pg_dumpall --roles-only. For large production databases, consider continuous archiving and point-in-time recovery (tools such as pgBackRest), or rely on your managed provider's automated backups. Whatever you choose, regularly practise restoring.

Tips and common mistakes

• Forgetting ORDER BY inside OVER for running totals or LAG. Without it, the "previous row" is undefined.
• Using -> when you need text. details->'language' = 'id' compares JSON to text and fails; use ->> or @>.
• Turning everything into JSONB. You lose types, constraints and simple joins. Use it for flexible attributes only.
• Assuming upserts never waste IDs. ON CONFLICT still consumes an identity value before detecting the conflict. Gaps, again, are normal.
• Running the app as a superuser. One SQL injection bug would give an attacker full control. Follow least privilege.
• Backups you have never restored. An untested backup is only a hope.

Exercise

1. Use DENSE_RANK() to rank users by total paid spending, and show only the top 3.
2. Add "level_hint": "start here" to the details of every beginner course using details || '{"level_hint": "start here"}'.
3. Search courses for "javascript -react" with websearch_to_tsquery. Which course matches?
4. Create a role app_writer that can read and write orders and wallets but only read courses. Test it with SET ROLE.
5. Back up only the courses table and restore it into a new database.

Summary and what's next

In this final part you used window functions for rankings, running totals and month-over-month changes; stored and queried flexible data with JSONB; built a language-aware search with full-text search and a generated column; wrote race-free upserts with ON CONFLICT; locked down access with roles; and backed up and restored the database with pg_dump and pg_restore.

Across ten parts you went from installing PostgreSQL to designing schemas, writing every kind of query, tuning performance and keeping data safe under concurrency. Good next steps: learn VACUUM and autovacuum tuning, table partitioning, logical replication, connection pooling with PgBouncer, and extensions such as pg_stat_statements and PostGIS. Most of all, keep practising on your own projects. Thanks for following the series!