← Cursos
🎓
AvanzadocourseAcceso por bootcamp

Advanced PostgreSQL for Backend Guide

64

Lecciones

8

Módulos

🎓

Acceso por bootcamp

Lo que aprenderás

✓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

¿Para quién es?

  • •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

Requisitos

  • •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)

Contenido del curso

1Módulo 1: JSONB Operators e Indexing — Guía para el Creador8 lecciones
2Módulo 2: JSONB con SQLAlchemy + Patterns — Guía para el Creador8 lecciones
3Módulo 3: Full-Text Search + pg_trgm — Guía para el Creador8 lecciones
4Módulo 4: Partitioning Nativo — Guía para el Creador8 lecciones
5Módulo 5: Materialized Views — Guía para el Creador8 lecciones
6Módulo 6: Advisory Locks + Savepoints — Guía para el Creador8 lecciones
7Módulo 7: Extensiones Útiles — Guía para el Creador8 lecciones
8Módulo 8: CTEs Recursivas + Proyecto Final — Guía para el Creador8 lecciones
Reviews

What students say

Sign in to leave a review.

No approved reviews yet.

Be the first to share your experience!