GuideAdvanced
Advanced PostgreSQL for Backend Guide
Master PostgreSQL's advanced features for production backend apps: deep JSONB with operators and GIN indexes, full-text search in Spanish as an Elasticsearch replacement, native partitioning for massive tables, materialized views for in-DB analytics, advisory locks as a Redis replacement, savepoints for nested transactions, useful extensions (`pg_trgm`, `citext`, `uuid-ossp`, `hstore`), and recursive CTEs for hierarchies and graphs.
- 64
- lessons
- 8
- modules
- English · Spanish
- available in
- Yes
- certificate
- Free
- access
NIEVA
Outcomes
What you'll be able to do
- Use JSONB at depth: operators (`->`, `->>`, `@>`, `?`, `#>`), JSON Path queries, GIN indexes (`jsonb_ops` vs `jsonb_path_ops`), partial and expression indexes
- Translate JSONB patterns idiomatically to SQLAlchemy 2.0: `Mapped[dict]`, `func.jsonb_extract_path_text`, `MutableDict`, partial updates with `jsonb_set`
- Implement full-text search in Spanish with `tsvector`/`tsquery`, `ts_rank_cd` ranking, multilingual dictionaries, and `unaccent` for accent-insensitive search
- Combine FTS with `pg_trgm` for fuzzy matching, autocomplete, and typo tolerance — and decide consciously when PostgreSQL FTS replaces Elasticsearch
- Partition massive tables (>10M rows) with range/list/hash partitioning, leverage partition pruning, and automate maintenance with `pg_partman`
- Build in-DB analytics with materialized views, `CONCURRENTLY` refresh, indexes on MVs, and refresh strategies (cron, app-driven, trigger-driven)
- Replace Redis for distributed locks with `pg_advisory_lock` / `pg_try_advisory_lock` (session vs transaction-level) — and know when Redis is still the better choice
- Handle nested transactions with savepoints and `session.begin_nested()` for batch processing where partial failures don't roll back the entire batch
- Choose between extensions: `pg_trgm` vs FTS, `citext` vs `LOWER`, `uuid-ossp` vs `gen_random_uuid` (PG 13+), `hstore` vs JSONB
- Write recursive CTEs (`WITH RECURSIVE`) for hierarchies (nested categories, org charts), graphs, and breadcrumbs
Before you start
What you need to bring
It's for you if...
- Senior backend Python developers with FastAPI apps in production where the standard PostgreSQL feature set is no longer enough
- Devs evaluating whether to add Elasticsearch to the stack just for search — and want to verify PostgreSQL FTS is viable first
- Teams managing massive tables (>10M rows) deciding between partitioning, sharding, or migrating to a specialized OLTP engine
- Developers building in-DB dashboards and analytics that need well-designed materialized views
- Devs using Redis only for distributed locks who want to simplify the stack
- Senior devs preparing for technical interviews where "when JSONB vs MongoDB?", "how to scale a 100M-row table?", or "how would you implement search?" are common
Requirements and materials
- PostgreSQL & SQLAlchemy guide completed (or equivalent: SQL, ACID, B-tree indexes, basic EXPLAIN, SQLAlchemy ORM with relationships, Alembic)
- Database Performance & Query Tuning guide completed (or equivalent: deep EXPLAIN, advanced indexing, N+1, profiling, pooling)
- SQL Patterns for Production APIs guide completed (or equivalent: cursor pagination, soft deletes, audit logs, multitenancy with RLS, zero-downtime migrations)
- Functional FastAPI app with SQLAlchemy 2.0 async and real data
- PostgreSQL 14+ installed locally or in Docker (16+ recommended for some features)
Content
The syllabus, module by module
Open any of them to see its lessons.
- Introduction: JSONB Operators and Indexing
- JSONB vs JSON vs TEXT: the mental model before the operators
- Core JSONB operators: access, search, and manipulation
- JSONB Path queries: the syntax for what the basic operators don't express
- GIN indexes on JSONB: the module's most important decision
- Complex queries: filters, JOINs, and aggregations over JSONB
- JSONB Anti-Patterns: when NOT to use JSONB and how to spot it
- Module project: replicating the 4s → 12ms case with your own 5M rows
- Module 2: JSONB with SQLAlchemy and usage patterns
- `Mapped[dict]` and JSONB types in SQLAlchemy 2.0
- Mutations and `MutableDict`: the gotcha that loses your changes silently
- Validation with Pydantic v2 and JSONB: typed JSONB
- Pattern: dynamic configuration with JSONB
- Pattern: extensible metadata with JSONB
- Pattern: polymorphic data with JSONB and discriminated unions
- Module project: extend the Blog API with `posts.metadata` JSONB
- Module 3: Full-Text Search + pg_trgm — a quality search engine without Elasticsearch
- `tsvector` and `tsquery`: the two types that make FTS possible
- Multilingual FTS: the `spanish` dictionary and `unaccent`
- GIN indexes for FTS and generated columns: the jump from Seq Scan to Bitmap Index Scan
- Ranking with `ts_rank` and `ts_rank_cd`: ordering by relevance, not by date
- `pg_trgm`: fuzzy search, similarity, and autocomplete without Elasticsearch
- FTS vs Elasticsearch: when PostgreSQL is enough and when it isn't
- Module project: the blog's Spanish-language search
- Module 4: Native Partitioning in PostgreSQL
- When to partition and when not to: the decision matrix before the syntax
- Range partitioning by date: the 80% case
- List partitioning by category/tenant: the multi-tenant SaaS case
- Hash partitioning for uniform distribution: the third type
- Partition pruning and constraint exclusion: how to verify the planner is doing its job
- `pg_partman` and automated maintenance: the operational piece that makes partitioning sustainable
- Module project: partition `events` with 50M rows and a zero-downtime migration
- Module 5 — Materialized Views: dashboards and reports that fly without adding another service to your stack
- Views vs materialized views: when each one
- Creation and refresh: fundamentals for your first runnable MV
- Refresh `CONCURRENTLY` vs `FULL`: the locking trade-off that defines production
- Indexes on materialized views: the MV is a table, index it as such
- Analytics use cases: dashboards and reports with materialized views
- MVs vs application cache (Redis): decision matrix with quantitative criteria
- Module 5 deliverable: blog dashboard with materialized views
- Module 6: Advisory Locks + Savepoints
- Advisory locks: the basic pattern
- Session-level vs transaction-level: the central decision
- Pattern from SQLAlchemy with a context manager
- Decision matrix: advisory locks vs Redis vs ZooKeeper/etcd
- Savepoints: partial rollback within a transaction
- Combined pattern: advisory lock + savepoints
- Mini-project: complete job runner with benchmarks
- Module 7: Useful Extensions
- Installing extensions + gotchas with cloud providers
- `citext` vs `LOWER()`: case-insensitive text
- UUIDs: `gen_random_uuid()` vs `uuid-ossp`
- `hstore` vs JSONB: why JSONB wins almost every time
- `pg_trgm` advanced cases: deduplication, "did you mean"
- Large extensions (mention): `pgcrypto`, `postgis`, `pgvector`
- Mini-project: refactoring a user system with extensions
- Module 8: Recursive CTEs + Final Project
- Recursive CTEs: anatomy and simple case
- 3 canonical patterns: downward, upward, concatenated path
- The `CYCLE` clause and graphs: avoiding infinite loops
- Closure table: the alternative when a recursive CTE isn't enough
- Final project — phase 1: setup, baseline, 3 components (JSONB, FTS, MV)
- Final project — phase 2: partitioning, advisory lock, recursive categories
- Final documentation + wrap-up of the guide
Where it fits
This guide is part of something bigger
It's studied inside these programs, with support and dates.
Common questions
What people usually ask
No limit. It's a free guide: come in whenever you like, as often as you like.
No. Modules run from easier to harder, but you can jump to the one you need. Progress is saved per lesson.
Whatever is needed is listed under “What you need to bring”, above. If nothing is listed there, you can start from zero.
In the Club's WhatsApp group, and every two weeks there's a live with an instructor where questions get worked through.
Yes. It's issued automatically once you finish every lesson, with a verifiable code you can share on LinkedIn.
No. This guide is self-paced with no dates. The bootcamp is live, by cohort, with work someone reviews.
Start whenever you like
Reviews
What students say
These reviews are from enrolled students who completed at least 50% of the course. We moderate reviews only on content grounds (spam, offensive language, personal data), never for being critical or negative.
No approved reviews yet.
Be the first to share your experience!