Advanced PostgreSQL: performance, replication and security
You will learn PostgreSQL rich data types, fast queries, backup and replication, and row-level security.
Level 301 · 27 lessons · 24 practice labs
Lessons
Data types and queries
- Advanced data types: array, enum, range, UUID: You will store lists, fixed choices, date ranges and unique identifiers in a column using the right types.
- JSONB: querying, indexing and updating: You will pull values from a JSONB column, speed it up with a GIN index and update a single field.
- Date and time handling: time zones and intervals: You will store time-zone-aware timestamps correctly, calculate intervals and group log records by hour.
- PostgreSQL window functions: You will rank rows, compare with the previous row and compute running totals without grouping them away.
- CTEs, recursive queries and LATERAL: You will break complex queries into steps with WITH, walk tree-shaped data and run a subquery per row.
- PostgreSQL full-text search: You will run stemmed, relevance-ranked text search using tsvector and a GIN index.
- Location data with PostGIS: You will install the PostGIS extension, store points and run location queries such as everything within 5 km.
- Short task: an events report with JSONB and window functions
Performance and maintenance
- Index types: partial, expression and GIN/GiST: You will choose the right index for each query, including indexes on only some rows or on a computed value.
- PostgreSQL table partitioning: You will split a large log table by date and detach an old partition instantly.
- VACUUM, ANALYZE and database maintenance: You will clear the bloat left by deleted rows with VACUUM and refresh planner statistics with ANALYZE.
- Connection pooling with PgBouncer: You will let many clients share a few real connections by setting up PgBouncer.
- Short task: fix a bloated, slow table
Logic inside the database
- Materialized views and view management: You will create a view that stores the result of a heavy query and manage when it refreshes.
- PL/pgSQL functions and error handling: You will write PL/pgSQL functions, catch errors and return a meaningful error message.
- Triggers and event triggers: You will keep a change trail with row triggers and watch structural changes such as new tables with event triggers.
- Transaction isolation, locks and deadlocks: You will learn how concurrent transactions affect each other, how row locks work and how to resolve deadlocks.
- Short task: build an order workflow in PL/pgSQL
Continuity: WAL, backups and replication
- WAL archiving and point-in-time recovery: You will archive the write-ahead log (WAL) and restore the database to any chosen moment in the past.
- Streaming replication: You will set up live copying from a primary to a standby server and monitor the lag.
- Short task: set up a standby and recover a point in time
Security
- Role hierarchy and default privileges: You will group roles with inheritance, set default privileges on new tables and narrow rights on the public schema.
- pg_hba.conf and SCRAM authentication: You will define in pg_hba.conf who may connect from where and how, and use SCRAM-SHA-256 passwords.
- Row-level security (RLS): You will make each user see only their own rows in a table by writing policies.
- TLS connections and column encryption with pgcrypto: You will protect the connection with TLS, encrypt a sensitive column with pgcrypto and store passwords as salted hashes.
- Audit logging with pgaudit: You will log in detail which role did what to which object using the pgaudit extension.
- Capstone: speed up and protect a multi-tenant app’s PostgreSQL
Practice labs
- Search tags in an array column: Store product tags as an array and find products carrying two given tags.
- Fix a report broken by time zones: Fix a report that puts orders around midnight on the wrong day using timestamptz and AT TIME ZONE.
- Prevent double booking with a range type: Make meeting-room bookings conflict-free with tstzrange and an exclusion constraint.
- Query inside JSONB: Filter events of a given device type from a JSONB field in event records.
- Speed up JSONB search with GIN: Choose the right GIN operator class and index a JSONB column with a slow containment query.
- Update a JSONB document in place: Change a nested field in user settings with jsonb_set, handling rows where the key is missing.
- Full-text search Turkish text: Search news headlines with to_tsvector and to_tsquery so inflected word forms match too.
- Rank results by relevance: Order search results with ts_rank, weighting the title more than the body.
- Keep a search column as a stored generated column: Speed up a search that computes tsvector on every query with a generated column and a GIN index.
- Find the five nearest branches: List the five Lale Market branches nearest to a user with their distance.
- Count deliveries inside a zone: Count Mavikuş Kargo delivery points that fall inside a drawn polygon zone.
- Speed up a spatial query with an index: Add a GiST index to a radius query slow on a hundred thousand points and show geography versus geometry.
- Index only active rows with a partial index: Build a partial index for pending orders that make up one percent of rows and compare size.
- Case-insensitive search with an expression index: Add an expression index that speeds up login by lower(email) and check the plan.
- Move an existing table to partitions: Move a growing measurements table to monthly partitions, attach old data and prove partition pruning.
- Put a heavy report in a materialized view: Turn a minutes-long dashboard query into a materialized view and schedule its refresh.
- Refresh a view without blocking reads: Add a unique index, refresh the materialized view CONCURRENTLY and show reads continue.
- Run the connection pool in the right mode: Find the bug where session settings are lost in a transaction-mode pool and align the app with the pool settings.
- Filter rows by tenant: Use an RLS policy so each tenant sees only its own rows in a multi-tenant invoice table.
- Separate the write policy from the read policy: Write a separate policy with WITH CHECK so users cannot insert rows for another tenant.
- Find and close a path around RLS: Show RLS being bypassed by the table owner and through a view, and close it with FORCE and a security-barrier view.
- Encrypt a sensitive column with pgcrypto: Encrypt an ID number column with pgcrypto and show it is readable only with the key.
- Audit who read what: Configure the audit extension to log reads of the salary table and find who queried it from the log.
- Move passwords to SCRAM: Find passwords in the old hash format, move them to SCRAM and disable the old method in pg_hba.conf.
About this course
You will learn PostgreSQL rich data types, fast queries, backup and replication, and row-level security.
What you will learn
- Data types and queries
- Performance and maintenance
- Logic inside the database
- Continuity: WAL, backups and replication
- Security
Prerequisites
You should be comfortable with the level 101 and 201 topics of the same path.
Lab environment
Lessons run in your own Kali Linux terminal in the browser, connected to a private lab network. Nothing to install.
Frequently asked questions
- Is this course right for my level?
- The course is at level 301; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.