06. PostgreSQL - Database Management

Mục Tiêu Module

Nắm vững PostgreSQL để quản lý databases hiệu quả cho production microservices.

Danh Sách Bài Học

Part 1: PostgreSQL Fundamentals

1. Introduction to PostgreSQL

  • PostgreSQL là gì?
  • PostgreSQL vs MySQL vs MongoDB
  • ACID properties
  • PostgreSQL features
  • Use cases
  • Installation (macOS, Linux, Windows)

2. PostgreSQL Architecture

  • Client-server architecture
  • Database cluster
  • Databases và schemas
  • Tables, rows, columns
  • Tablespaces
  • Processes và memory

3. Installation và Setup

  • Installing PostgreSQL
  • psql command-line tool
  • GUI tools (pgAdmin, DBeaver, TablePlus)
  • Environment setup
  • Connection configuration
  • Authentication methods

4. Basic SQL - Data Types

  • Numeric types (integer, numeric, real)
  • Character types (char, varchar, text)
  • Date/time types (date, time, timestamp, interval)
  • Boolean type
  • UUID type
  • JSON/JSONB types
  • Array types
  • Custom types

5. Basic SQL - DDL (Data Definition Language)

  • CREATE DATABASE
  • CREATE SCHEMA
  • CREATE TABLE
  • ALTER TABLE
  • DROP TABLE
  • TRUNCATE
  • Table constraints

6. Basic SQL - DML (Data Manipulation Language)

  • INSERT statements
  • SELECT queries
  • UPDATE statements
  • DELETE statements
  • RETURNING clause
  • UPSERT (INSERT ... ON CONFLICT)

7. SQL Queries - Basic

  • SELECT columns
  • WHERE clause
  • ORDER BY
  • LIMIT và OFFSET
  • DISTINCT
  • Aggregate functions (COUNT, SUM, AVG, MIN, MAX)

8. SQL Queries - Intermediate

  • GROUP BY
  • HAVING clause
  • Subqueries
  • Common Table Expressions (CTE)
  • UNION, INTERSECT, EXCEPT
  • CASE expressions

9. SQL Queries - Advanced

  • Window functions
  • Recursive queries
  • Lateral joins
  • Array operations
  • JSON operations
  • Full-text search

Part 2: Database Design

10. Database Design Principles

  • Normalization (1NF, 2NF, 3NF, BCNF)
  • Denormalization
  • Entity-Relationship diagrams
  • Primary keys và foreign keys
  • Composite keys
  • Surrogate keys

11. Relationships

  • One-to-one
  • One-to-many
  • Many-to-many
  • Self-referencing relationships
  • Implementing relationships
  • Cascading actions

12. Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • NOT NULL
  • CHECK constraints
  • DEFAULT values
  • Constraint naming

13. Indexes

  • Index types (B-tree, Hash, GiST, GIN)
  • Creating indexes
  • Partial indexes
  • Multi-column indexes
  • Index performance
  • When to use indexes
  • Index maintenance

14. Views

  • Creating views
  • Updatable views
  • Materialized views
  • Refreshing materialized views
  • Use cases
  • Performance considerations

Part 3: Advanced Features

15. Transactions

  • ACID properties
  • BEGIN, COMMIT, ROLLBACK
  • Transaction isolation levels
  • Savepoints
  • Two-phase commit
  • Transaction best practices

16. Concurrency Control

  • MVCC (Multi-Version Concurrency Control)
  • Locking mechanisms
  • Lock types
  • Deadlocks
  • SELECT FOR UPDATE
  • Advisory locks

17. Stored Procedures

  • PL/pgSQL introduction
  • Creating functions
  • Function parameters
  • Return types
  • Control structures
  • Error handling
  • Use cases

18. Triggers

  • Trigger types (BEFORE, AFTER, INSTEAD OF)
  • Trigger events (INSERT, UPDATE, DELETE)
  • Row-level vs statement-level
  • Creating triggers
  • Trigger functions
  • Use cases và best practices

19. JSON và JSONB

  • JSON vs JSONB
  • Storing JSON data
  • Querying JSON data
  • JSON operators
  • JSON functions
  • Indexing JSON columns
  • Use cases

20. Full-Text Search

  • tsvector và tsquery
  • Creating text search indexes
  • Search queries
  • Ranking results
  • Multi-language support
  • Search configuration

Part 4: Performance & Optimization

21. Query Performance Analysis

  • EXPLAIN command
  • EXPLAIN ANALYZE
  • Reading query plans
  • Cost estimation
  • Execution time
  • Query optimization tips

22. Index Optimization

  • Index usage monitoring
  • Unused indexes
  • Missing indexes
  • Index bloat
  • Reindexing
  • Covering indexes

23. Query Optimization

  • Query rewriting
  • Join optimization
  • Subquery optimization
  • Function optimization
  • Avoiding N+1 queries
  • Pagination optimization

24. Configuration Tuning

  • postgresql.conf
  • Memory settings (shared_buffers, work_mem)
  • Connection settings (max_connections)
  • Checkpoint settings
  • WAL settings
  • Autovacuum tuning

25. Vacuum và Maintenance

  • VACUUM command
  • VACUUM FULL
  • ANALYZE command
  • Autovacuum
  • Bloat management
  • Table maintenance

Part 5: Security

26. User Management

  • Creating users/roles
  • ALTER ROLE
  • DROP ROLE
  • Role attributes
  • Role membership
  • Password management

27. Permissions

  • GRANT và REVOKE
  • Object privileges
  • Schema privileges
  • Database privileges
  • Default privileges
  • Row-level security

28. Authentication

  • pg_hba.conf
  • Authentication methods (trust, password, md5, scram-sha-256)
  • SSL connections
  • Certificate authentication
  • LDAP authentication

29. Security Best Practices

  • Principle of least privilege
  • Strong passwords
  • Encryption at rest
  • Network security
  • Audit logging
  • Security updates

Part 6: Backup & Recovery

30. Backup Strategies

  • Logical backup (pg_dump)
  • Physical backup (pg_basebackup)
  • Continuous archiving
  • Point-in-time recovery (PITR)
  • Backup scheduling
  • Backup testing

31. pg_dump và pg_restore

  • Dumping databases
  • Dump formats (plain, custom, directory, tar)
  • Selective dumps
  • Restoring databases
  • Parallel dump/restore
  • Large database handling

32. Point-in-Time Recovery

  • WAL archiving
  • Creating base backups
  • Recovery process
  • recovery.conf
  • Recovery testing
  • Automated recovery

33. Replication

  • Streaming replication
  • Logical replication
  • Setting up replicas
  • Monitoring replication
  • Failover procedures
  • Read replicas

Part 7: PostgreSQL with Django

34. Django Database Configuration

  • psycopg2 vs psycopg3
  • DATABASES setting
  • Connection pooling
  • Multiple databases
  • Database routers
  • Settings best practices

35. Django Migrations

  • Migration system
  • PostgreSQL-specific fields
  • Custom migrations
  • Data migrations
  • Migration squashing
  • Migration conflicts

36. Django QuerySet Optimization

  • select_related() optimization
  • prefetch_related() optimization
  • only() và defer()
  • Query debugging
  • Django Debug Toolbar
  • Query count reduction

37. Django + PostgreSQL Features

  • JSONField
  • ArrayField
  • HStoreField
  • Full-text search
  • PostgreSQL-specific lookups
  • Custom database functions

38. Connection Pooling

  • Why connection pooling?
  • pgBouncer setup
  • Django configuration
  • Pool sizing
  • Monitoring connections
  • Troubleshooting

Part 8: Production & Docker

39. PostgreSQL in Docker

  • Official PostgreSQL image
  • Dockerfile customization
  • Environment variables
  • Volume management
  • Network configuration
  • Health checks

40. Docker Compose PostgreSQL

  • Service definition
  • Multiple databases
  • Initialization scripts
  • Backup containers
  • Monitoring containers
  • Production considerations

41. PostgreSQL Performance Monitoring

  • pg_stat_statements
  • System views
  • Query statistics
  • Connection monitoring
  • Slow query log
  • Monitoring tools (pgAdmin, Grafana)

42. High Availability

  • Replication setup
  • Automatic failover
  • Load balancing
  • Health checks
  • Split-brain prevention
  • Disaster recovery

43. Scaling Strategies

  • Vertical scaling
  • Horizontal scaling
  • Read replicas
  • Sharding concepts
  • Partitioning
  • Caching strategies

44. PostgreSQL Best Practices

  • Schema design
  • Query patterns
  • Index strategy
  • Monitoring approach
  • Backup strategy
  • Security practices
  • Upgrade procedures

45. Troubleshooting Common Issues

  • Connection issues
  • Performance problems
  • Lock conflicts
  • Disk space issues
  • Replication lag
  • Corrupted data

Prerequisites

  • ✅ SQL cơ bản
  • ✅ Command line proficiency
  • ✅ Django ORM (Module 03-04)
  • ✅ Docker basics (Module 05)

Thời Gian Học

  • Ước tính: 4-5 tuần
  • Thời lượng mỗi bài: 60-90 phút
  • Thực hành: 2-3 giờ/ngày

Hands-On Labs

Lab Projects

  1. User Database Design: Complete schema for user service
  2. E-commerce Database: Products, orders, inventory
  3. Analytics Database: Time-series data với partitioning
  4. Search Engine: Full-text search implementation
  5. Replication Setup: Master-slave configuration

Performance Labs

  • Query optimization challenges
  • Index tuning exercises
  • Scaling simulations
  • Backup/restore drills

Tools & Resources

Required Tools

  • PostgreSQL 15+
  • psql
  • pgAdmin 4 hoặc DBeaver
  • Docker
  • Python psycopg2

Optional Tools

  • pgBouncer
  • pg_stat_statements
  • pgBackRest
  • Grafana
  • Prometheus postgres_exporter

Documentation

Books

  • PostgreSQL: Up and Running
  • Mastering PostgreSQL 15
  • High Performance PostgreSQL

Certification Path

  • PostgreSQL Associate Certification (optional)
  • Hands-on capstone project
  • Production deployment experience

Final Project

Multi-tenant SaaS Database:

  • Complete schema design
  • Row-level security
  • Partitioning strategy
  • Backup/restore procedures
  • Replication setup
  • Performance monitoring
  • Documentation

Module Completion

Sau khi hoàn thành module này, bạn sẽ:

  • ✅ Thiết kế database schemas hiệu quả
  • ✅ Viết complex SQL queries
  • ✅ Optimize database performance
  • ✅ Implement backup/recovery
  • ✅ Setup replication
  • ✅ Manage PostgreSQL trong production
  • ✅ Integrate PostgreSQL với Django
  • ✅ Troubleshoot database issues

Chúc mừng! Bạn đã hoàn thành toàn bộ khóa học Django Microservices! 🎉