140 ngày PostgreSQL
Table of contents
- Tuần 1: Làm quen PostgreSQL và Cài đặt
- Tuần 2: Views, Functions, Triggers và Quản lý Transaction
- Tuần 3: Sao lưu và phục hồi, High Availability, Load Balancing, Replication
- Tuần 4: Performance Tuning, PostgreSQL Internals, và Extensibility
- Tuần 5: Advanced SQL, Window Functions, Common Table Expressions (CTE), và Stored Procedures
- Tuần 6: PostgreSQL Performance Tuning, Security, and PostgreSQL Internals
- Ngày 40
- Ngày 41
- Ngày 42
- Tuần 8: PostgreSQL Query Optimization, System Catalogs, and Frontend/Backend Protocol
- Tuần 9: PostgreSQL Native Language Support, Procedural Language Handlers, and Foreign Data Wrappers
- Tuần 10: PostgreSQL Internals và Performance Tuning Nâng cao
- Tuần 11: PostgreSQL Replication, Monitoring, and Security
- Tuần 12: PostgreSQL Internals và Performance Tuning Nâng cao (tiếp)
- Tuần 13: PostgreSQL Extensibility, High Availability, và Cloud
- Tuần 14: PostgreSQL Security Nâng cao và PostgreSQL Cluster Management
- Tuần 15: PostgreSQL Internals Nâng cao và PostgreSQL in Cloud
- Tuần 16: PostgreSQL Extensions, Advanced Security, và PostgreSQL for Data Warehousing
- Tuần 17: PostgreSQL Internals nâng cao, PostgreSQL cho Machine Learning và IoT
- Tuần 18: PostgreSQL Internals nâng cao và Tối ưu hóa Hiệu suất
- Tuần 19: PostgreSQL Extensibility Nâng cao và PostgreSQL cho Blockchain
- Tuần 20: Dự án cá nhân và tổng kết
Tuần 1: Làm quen PostgreSQL và Cài đặt
Ngày 1
Giới thiệu về PostgreSQL:
PostgreSQL là gì? Ưu điểm và nhược điểm so với các hệ quản trị CSDL khác.
Lịch sử phát triển của PostgreSQL.
Các tính năng nổi bật của PostgreSQL (ACID, MVCC, khả năng mở rộng, hỗ trợ nhiều kiểu dữ liệu).
Các phiên bản PostgreSQL và sự khác biệt giữa chúng.
Kiến trúc PostgreSQL:
Tổng quan về kiến trúc client-server.
Các tiến trình chính (postgres, bgwriter, wal writer, autovacuum).
Cấu trúc bộ nhớ chia sẻ (shared buffers, WAL buffers).
Bài tập:
Nghiên cứu và so sánh PostgreSQL với MySQL và SQL Server về các tính năng và ứng dụng.
Tìm hiểu về lịch sử phát triển của PostgreSQL và các phiên bản chính.
Vẽ sơ đồ kiến trúc của PostgreSQL và giải thích chức năng của từng thành phần.
Ngày 2
Cài đặt PostgreSQL:
Các phương pháp cài đặt (từ mã nguồn, từ gói cài đặt).
Các yêu cầu hệ thống để cài đặt PostgreSQL.
Hướng dẫn chi tiết cài đặt PostgreSQL trên các hệ điều hành khác nhau (Windows, macOS, Linux).
Sử dụng các công cụ quản lý gói (apt, yum, brew) để cài đặt PostgreSQL.
Bài tập:
Cài đặt PostgreSQL từ mã nguồn trên một máy ảo Linux.
Cài đặt PostgreSQL trên máy tính cá nhân của bạn (Windows hoặc macOS).
Tìm hiểu về các tùy chọn cấu hình PostgreSQL trong postgresql.conf và pg_hba.conf.
Ngày 3
Cấu hình PostgreSQL:
Các tham số quan trọng trong postgresql.conf (listen_addresses, port, max_connections, shared_buffers, work_mem, maintenance_work_mem, wal_level, checkpoint_segments, archive_mode).
Các phương thức xác thực (trust, password, md5, scram-sha-256, ident, peer) và cách cấu hình trong pg_hba.conf.
Cấu hình logging (log_destination, logging_collector, log_directory, log_filename, log_rotation_age, log_rotation_size).
Bài tập:
Điều chỉnh các tham số cấu hình để tối ưu hóa hiệu suất PostgreSQL cho ứng dụng web của bạn.
Cấu hình PostgreSQL để sử dụng xác thực SCRAM-SHA-256 và mã hóa mật khẩu.
Thiết lập PostgreSQL để ghi log vào một file syslog từ xa và cấu hình xoay vòng log.
Ngày 4
Kết nối đến PostgreSQL:
Sử dụng psql để kết nối đến server PostgreSQL.
Các tham số kết nối (hostname, port, username, database).
Các lệnh cơ bản trong psql (\l, \dt, \du, \d, \df, \ef).
Các công cụ quản lý PostgreSQL khác (pgAdmin, DBeaver).
Kiểu dữ liệu:
Các kiểu dữ liệu số (integer, bigint, smallint, serial, numeric).
Các kiểu dữ liệu ký tự (char, varchar, text).
Các kiểu dữ liệu thời gian (timestamp, date, time, interval).
Bài tập:
Kết nối đến PostgreSQL bằng psql và thực hiện các lệnh cơ bản để liệt kê các database, bảng, view, function.
Sử dụng pgAdmin để kết nối đến PostgreSQL và thực hiện các thao tác cơ bản.
Tạo một bảng mới với các cột có kiểu dữ liệu khác nhau (số, ký tự, thời gian) và chèn dữ liệu vào bảng đó.
Ngày 5
Kiểu dữ liệu nâng cao:
Kiểu dữ liệu Boolean.
Kiểu dữ liệu enum.
Kiểu dữ liệu mảng.
Kiểu dữ liệu JSON và JSONB.
Kiểu dữ liệu UUID.
Kiểu dữ liệu hình học (point, line, polygon).
Kiểu dữ liệu mạng (inet, cidr, macaddr).
Kiểu dữ liệu tiền tệ (money).
Bài tập:
Tạo một bảng chứa thông tin sản phẩm với một cột kiểu enum cho trạng thái sản phẩm và một cột kiểu mảng để lưu trữ danh sách các màu sắc của sản phẩm.
Sử dụng các hàm và toán tử để làm việc với dữ liệu JSON và JSONB.
Tạo một bảng chứa thông tin địa chỉ IP và thực hiện các truy vấn tìm kiếm dựa trên địa chỉ IP.
Tạo một bảng chứa thông tin giao dịch với cột kiểu money và thực hiện các tính toán trên cột này.
Ngày 6
Toán tử:
Toán tử số học (+, -, *, /, %).
Toán tử so sánh (=, <>, <, >, <=, >=).
Toán tử logic (AND, OR, NOT).
Toán tử chuỗi (||, LIKE, ILIKE, SIMILAR TO).
Toán tử pattern matching (~, ~, !~, !~).
Toán tử phạm vi (BETWEEN, IN).
Toán tử bitwise (&, |, #, ~, <<, >>).
Bài tập:
Viết các truy vấn sử dụng các toán tử khác nhau để thực hiện các phép tính số học, so sánh, logic, chuỗi, pattern matching và bitwise trên dữ liệu.
Tìm hiểu về thứ tự ưu tiên của các toán tử và sử dụng dấu ngoặc đơn để kiểm soát thứ tự thực hiện các phép toán.
Sử dụng toán tử phạm vi để tìm kiếm các bản ghi nằm trong một khoảng giá trị hoặc thuộc một tập hợp giá trị.
Sử dụng toán tử bitwise để thực hiện các thao tác trên các giá trị bit.
Ngày 7
Index:
Khái niệm về index và lợi ích của việc sử dụng index.
Các loại index (B-tree, hash, GiST, GIN, BRIN).
Tạo index (CREATE INDEX).
Quản lý index (REINDEX, DROP INDEX).
Sử dụng EXPLAIN để phân tích kế hoạch truy vấn và xem PostgreSQL có sử dụng index hay không.
Các chiến lược lập index hiệu quả.
Bài tập:
Tạo các index trên các cột thường được sử dụng trong điều kiện WHERE của các truy vấn.
So sánh hiệu suất của các truy vấn trước và sau khi tạo index.
Tìm hiểu về các công cụ để giám sát hiệu suất của index (pg_stat_user_indexes, pg_buffercache).
Thiết kế một hệ thống index hiệu quả cho một cơ sở dữ liệu mẫu.
Tuần 2: Views, Functions, Triggers và Quản lý Transaction
Ngày 8
Views:
Khái niệm về view và lợi ích của việc sử dụng view.
Tạo view (CREATE VIEW).
Sửa đổi view (CREATE OR REPLACE VIEW).
Xóa view (DROP VIEW).
Các loại view (materialized view, updatable view, security view).
Sử dụng view để đơn giản hóa truy vấn và bảo mật dữ liệu.
Bài tập:
Tạo một view để hiển thị thông tin tổng hợp từ nhiều bảng, ví dụ như tổng doanh thu theo tháng.
Tạo một view với các điều kiện lọc để giới hạn dữ liệu được hiển thị cho người dùng cụ thể.
Tìm hiểu về các materialized view và cách sử dụng chúng để cải thiện hiệu suất truy vấn.
Ngày 9
Functions:
Khái niệm về function và lợi ích của việc sử dụng function.
Các loại function (scalar function, aggregate function, table function, window function).
Tạo function (CREATE FUNCTION).
Các thành phần của function (tham số, kiểu trả về, thân hàm).
Sử dụng các ngôn ngữ thủ tục (PL/pgSQL, PL/Python, PL/Perl) để viết function.
Bài tập:
Viết một scalar function để tính toán tuổi của một người dựa vào ngày sinh.
Viết một aggregate function để tính toán trung bình cộng của một cột số.
Viết một table function để trả về danh sách các sản phẩm có giá nằm trong một khoảng cho trước.
Ngày 10
Triggers:
Khái niệm về trigger và lợi ích của việc sử dụng trigger.
Các loại trigger (BEFORE, AFTER, INSTEAD OF).
Các sự kiện kích hoạt trigger (INSERT, UPDATE, DELETE).
Tạo trigger (CREATE TRIGGER).
Sử dụng trigger để kiểm tra tính hợp lệ của dữ liệu, đồng bộ dữ liệu giữa các bảng, hoặc thực hiện các hành động khác.
Bài tập:
Viết một trigger để tự động cập nhật ngày sửa đổi cuối cùng của một bản ghi khi bản ghi đó được cập nhật.
Viết một trigger để kiểm tra tính hợp lệ của dữ liệu trước khi chèn hoặc cập nhật một bản ghi.
Viết một trigger để đồng bộ dữ liệu giữa hai bảng.
Ngày 11
Quản lý transaction:
Khái niệm về transaction và các thuộc tính ACID.
Các lệnh quản lý transaction (BEGIN, COMMIT, ROLLBACK, SAVEPOINT).
Các mức cô lập transaction (READ COMMITTED, REPEATABLE READ, SERIALIZABLE).
Xử lý deadlock.
Bài tập:
Viết một đoạn mã PL/pgSQL để thực hiện một transaction chuyển tiền giữa hai tài khoản ngân hàng.
Thử nghiệm các mức cô lập transaction khác nhau và quan sát kết quả.
Tìm hiểu về các kỹ thuật để tránh và xử lý deadlock.
Ngày 12
Tìm kiếm toàn văn (Full Text Search):
Khái niệm về tìm kiếm toàn văn và lợi ích của nó.
Các thành phần của tìm kiếm toàn văn (document, query, rank).
Các kiểu dữ liệu và toán tử tìm kiếm toàn văn (tsvector, tsquery, @@, @>, <@).
Cấu hình tìm kiếm toàn văn (ngôn ngữ, từ điển, parser).
Bài tập:
Tạo một bảng chứa các bài báo và sử dụng tìm kiếm toàn văn để tìm kiếm các bài báo chứa các từ khóa cụ thể.
Sử dụng các toán tử tìm kiếm toàn văn để thực hiện các truy vấn phức tạp hơn (ví dụ: tìm kiếm các bài báo chứa cả hai từ khóa "PostgreSQL" và "performance").
Tìm hiểu về các kỹ thuật để cải thiện hiệu suất tìm kiếm toàn văn.
Ngày 13
Phân quyền (Authorization):
Khái niệm về phân quyền và lợi ích của nó.
Các đối tượng có thể được phân quyền (bảng, view, function, schema).
Các loại quyền (SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER, EXECUTE).
Cấp quyền và thu hồi quyền (GRANT, REVOKE).
Quyền mặc định (PUBLIC).
Bài tập:
Tạo một người dùng mới và cấp cho người dùng đó quyền SELECT trên một bảng cụ thể.
Tạo một schema mới và cấp cho một người dùng quyền CREATE trên schema đó.
Tìm hiểu về các role mặc định trong PostgreSQL (pg_read_all_data, pg_write_all_data) và cách sử dụng chúng.
Ngày 14
Quản lý database:
Tạo database (CREATE DATABASE).
Xóa database (DROP DATABASE).
Thay đổi database (ALTER DATABASE).
Các template database (template0, template1).
Kết nối đến một database khác (psql -d).
Bài tập:
Tạo một database mới từ template1.
Thay đổi chủ sở hữu của một database.
Tìm hiểu về các công cụ để quản lý database (pgAdmin, DBeaver).
Tuần 3: Sao lưu và phục hồi, High Availability, Load Balancing, Replication
Ngày 15
Sao lưu và phục hồi (Backup and Restore):
Các loại sao lưu (physical backup, logical backup).
Các công cụ sao lưu (pg_dump, pg_dumpall, pg_basebackup).
Các chiến lược sao lưu (full backup, incremental backup).
Phục hồi từ bản sao lưu (pg_restore).
Point-in-time recovery (PITR).
Bài tập:
Thực hiện một bản sao lưu đầy đủ của một cơ sở dữ liệu PostgreSQL.
Thực hiện một bản sao lưu tăng dần của một cơ sở dữ liệu PostgreSQL.
Phục hồi một cơ sở dữ liệu PostgreSQL từ bản sao lưu đầy đủ và bản sao lưu tăng dần.
Ngày 16:
High Availability (HA):
Khái niệm về HA và tầm quan trọng của nó.
Các giải pháp HA phổ biến cho PostgreSQL (pg_auto_failover, Patroni, repmgr).
So sánh các giải pháp HA khác nhau.
Bài tập:
Nghiên cứu về pg_auto_failover và cách thiết lập một cụm PostgreSQL HA sử dụng pg_auto_failover.
Tìm hiểu về Patroni và cách thiết lập một cụm PostgreSQL HA sử dụng Patroni.
So sánh ưu nhược điểm của pg_auto_failover và Patroni.
Ngày 17
Load Balancing:
Khái niệm về load balancing và lợi ích của nó.
Các kỹ thuật load balancing (round robin, weighted round robin, least connections).
Các công cụ load balancing cho PostgreSQL (Pgpool-II, HAProxy).
Bài tập:
Cài đặt và cấu hình Pgpool-II để load balancing cho một cụm PostgreSQL.
Cài đặt và cấu hình HAProxy để load balancing cho một cụm PostgreSQL.
So sánh ưu nhược điểm của Pgpool-II và HAProxy.
Ngày 18
Replication:
Khái niệm về replication và các loại replication (physical replication, logical replication).
Cấu hình physical replication (streaming replication, file-based log shipping).
Cấu hình logical replication (publisher, subscriber, publication, subscription).
Quản lý xung đột trong logical replication.
Bài tập:
Thiết lập một hệ thống physical replication giữa hai node PostgreSQL.
Thiết lập một hệ thống logical replication giữa hai node PostgreSQL.
Tìm hiểu về các công cụ để quản lý replication (pg_recvlogical, pg_stat_replication).
Ngày 19
Monitoring:
Các chỉ số cần giám sát (CPU, memory, disk, network, database activity).
Các công cụ giám sát PostgreSQL (pg_stat_activity, pg_stat_statements, pgBadger, pgFouine).
Thiết lập cảnh báo khi có sự cố.
Bài tập:
Sử dụng pg_stat_activity để giám sát các hoạt động đang diễn ra trên server PostgreSQL.
Sử dụng pg_stat_statements để xác định các truy vấn tốn nhiều tài nguyên nhất.
Thiết lập cảnh báo để nhận thông báo khi có sự cố xảy ra trên server PostgreSQL.
Ngày 20
Logging:
Các loại log (server log, client log, CSV log).
Cấu hình logging (log_destination, logging_collector, log_directory, log_filename, log_rotation_age, log_rotation_size).
Phân tích log bằng các công cụ (pgBadger, pgFouine).
Bài tập:
Cấu hình PostgreSQL để ghi log vào một file syslog từ xa.
Sử dụng pgBadger để phân tích log PostgreSQL và tìm ra các vấn đề hiệu suất.
Tìm hiểu về các công cụ khác để phân tích log PostgreSQL (ví dụ: ELK stack).
Ngày 21
Bảo mật:
Các mối đe dọa bảo mật đối với PostgreSQL.
Các biện pháp bảo mật cơ bản (cập nhật phiên bản, sử dụng mật khẩu mạnh, phân quyền chặt chẽ).
Cấu hình tường lửa (firewall) để bảo vệ PostgreSQL.
Mã hóa dữ liệu nhạy cảm.
Bài tập:
Thực hiện kiểm tra bảo mật trên hệ thống PostgreSQL của bạn bằng các công cụ như pgaudit hoặc pg_stat_activity.
Tìm hiểu về các lỗ hổng bảo mật phổ biến trong PostgreSQL và cách khắc phục chúng.
Cài đặt và cấu hình tường lửa để bảo vệ PostgreSQL khỏi các cuộc tấn công từ bên ngoài.
Tuần 4: Performance Tuning, PostgreSQL Internals, và Extensibility
Ngày 22
Performance Tuning:
EXPLAIN và EXPLAIN ANALYZE:
Sử dụng EXPLAIN để xem kế hoạch truy vấn của PostgreSQL.
Sử dụng EXPLAIN ANALYZE để xem chi tiết về thời gian thực thi của mỗi bước trong kế hoạch truy vấn.
Hiểu các thông tin trong kết quả EXPLAIN (cost, rows, width, actual time).
Các chỉ số hiệu suất:
pg_stat_activity: Giám sát các hoạt động đang diễn ra trên server.
pg_stat_statements: Theo dõi thống kê về các câu lệnh SQL đã thực thi.
pg_stat_user_tables, pg_statio_user_tables: Giám sát hoạt động đọc/ghi trên các bảng.
Bài tập:
Sử dụng EXPLAIN và EXPLAIN ANALYZE để phân tích hiệu suất của các truy vấn phức tạp và tìm cách tối ưu hóa chúng.
Sử dụng pg_stat_statements để xác định các truy vấn tốn nhiều tài nguyên nhất và tối ưu hóa chúng.
Tìm hiểu về các công cụ khác để giám sát hiệu suất PostgreSQL (ví dụ: pgBadger, pgFouine).
Ngày 23
Performance Tuning (tiếp):
Cấu hình hiệu suất:
Các tham số quan trọng ảnh hưởng đến hiệu suất (shared_buffers, work_mem, effective_cache_size, maintenance_work_mem, checkpoint_segments, wal_buffers).
Các kỹ thuật caching (shared buffers, operating system cache).
Quản lý bộ nhớ (memory overcommit, huge pages).
Bài tập:
Điều chỉnh các tham số cấu hình để tối ưu hóa hiệu suất PostgreSQL cho ứng dụng của bạn.
Thử nghiệm các giá trị khác nhau cho shared_buffers và work_mem để tìm ra giá trị tối ưu.
Tìm hiểu về cơ chế memory overcommit và huge pages trong Linux và cách sử dụng chúng để cải thiện hiệu suất PostgreSQL.
Ngày 24
PostgreSQL Internals:
Quá trình xử lý truy vấn:
Parser, analyzer, rewriter, planner, optimizer, executor.
Các loại kế hoạch truy vấn (sequential scan, index scan, bitmap scan, nested loop join, hash join, merge join).
System catalogs:
pg_class, pg_attribute, pg_index, pg_database, pg_tablespace.
Cách truy vấn system catalogs để lấy thông tin về cấu trúc cơ sở dữ liệu.
Bài tập:
Sử dụng EXPLAIN để xem kế hoạch truy vấn của PostgreSQL và hiểu cách PostgreSQL thực hiện các truy vấn.
Truy vấn system catalogs để lấy thông tin về các bảng, cột, index trong cơ sở dữ liệu.
Tìm hiểu về các cấu trúc dữ liệu bên trong của PostgreSQL (ví dụ: heap, index, buffer pool).
Ngày 25
PostgreSQL Internals (tiếp):
Write-Ahead Logging (WAL):
Khái niệm về WAL và tầm quan trọng của nó trong việc đảm bảo tính nhất quán dữ liệu.
Cấu trúc của WAL (WAL records, WAL segments).
Quá trình WAL archiving.
Multi-Version Concurrency Control (MVCC):
Khái niệm về MVCC và cách nó giải quyết vấn đề đồng thời trong PostgreSQL.
Transaction ID (XID) và Command ID (CID).
Snapshot và visibility rules.
Bài tập:
Tìm hiểu về các tham số cấu hình liên quan đến WAL (wal_level, checkpoint_segments, archive_mode) và tác động của chúng đến hiệu suất và độ tin cậy.
Thử nghiệm các mức cô lập transaction khác nhau (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) và quan sát hành vi của MVCC.
Tìm hiểu về các công cụ để giám sát WAL (pg_stat_archiver, pg_stat_wal_receiver).
Ngày 26
- Extensibility:
User-defined functions:
Tạo hàm bằng các ngôn ngữ thủ tục (PL/pgSQL, PL/Python, PL/Perl, PL/Tcl).
Các loại hàm (scalar, aggregate, table, window).
Sử dụng hàm trong truy vấn.
User-defined types:
Tạo kiểu dữ liệu mới.
Định nghĩa các toán tử và hàm cho kiểu dữ liệu mới.
Bài tập:
Viết một hàm PL/pgSQL để tính toán lãi suất kép.
Viết một hàm aggregate để tính trung vị của một tập hợp số.
Tạo một kiểu dữ liệu mới để biểu diễn số phức và định nghĩa các toán tử số học cho kiểu dữ liệu này.
Ngày 27
Extensibility (tiếp):
Extensions:
Khái niệm về extension và lợi ích của việc sử dụng extension.
Cài đặt và quản lý extension (CREATE EXTENSION, ALTER EXTENSION, DROP EXTENSION).
Các extension phổ biến (PostGIS, pg_trgm, hstore).
Foreign Data Wrappers (FDW):
Khái niệm về FDW và cách sử dụng FDW để truy cập dữ liệu từ các nguồn bên ngoài.
Tạo và cấu hình FDW.
Các FDW phổ biến (mysql_fdw, postgres_fdw, oracle_fdw).
Bài tập:
Cài đặt và sử dụng extension PostGIS để làm việc với dữ liệu không gian.
Cài đặt và sử dụng extension pg_trgm để thực hiện tìm kiếm gần đúng trên các chuỗi văn bản.
Tạo một FDW để truy cập dữ liệu từ một cơ sở dữ liệu MySQL.
Ngày 28
Logical Replication:
Khái niệm:
So sánh logical replication với physical replication.
Ưu điểm và nhược điểm của logical replication.
Cấu trúc:
Publications (các bảng và dữ liệu cần sao chép).
Subscriptions (các bảng đích để nhận dữ liệu sao chép).
Logical replication slots (theo dõi các thay đổi trên publisher).
Cấu hình:
Thiết lập publisher và subscriber.
Tạo publication và subscription.
Giám sát hoạt động của logical replication.
Bài tập:
Thiết lập logical replication giữa hai node PostgreSQL.
Tạo một publication để sao chép một bảng cụ thể.
Theo dõi hoạt động của logical replication bằng pg_stat_replication.
Tuần 5: Advanced SQL, Window Functions, Common Table Expressions (CTE), và Stored Procedures
Ngày 29
Advanced SQL:
Joins:
INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN.
CROSS JOIN, NATURAL JOIN.
Sử dụng ON và USING để xác định điều kiện join.
Hiểu về các loại join và cách sử dụng chúng để kết hợp dữ liệu từ nhiều bảng.
Subqueries:
Khái niệm về subquery và các loại subquery (correlated, non-correlated).
Sử dụng subquery trong mệnh đề WHERE, FROM, SELECT.
Hiểu về các vấn đề hiệu suất liên quan đến subquery và cách tối ưu hóa chúng.
Bài tập:
Viết một truy vấn sử dụng INNER JOIN để lấy thông tin về khách hàng và đơn hàng của họ.
Viết một truy vấn sử dụng LEFT JOIN để lấy thông tin về tất cả khách hàng, kể cả những người chưa đặt hàng.
Viết một truy vấn sử dụng subquery để tìm ra sản phẩm có giá cao nhất trong mỗi danh mục.
Ngày 30
Window Functions:
Khái niệm về window function và lợi ích của việc sử dụng window function.
Các hàm window phổ biến:
RANK, DENSE_RANK, ROW_NUMBER.
SUM, AVG, MIN, MAX, COUNT.
FIRST_VALUE, LAST_VALUE, LAG, LEAD.
Cú pháp của window function (OVER, PARTITION BY, ORDER BY).
Sử dụng window function để tính toán xếp hạng, tổng chạy, trung bình động, và các giá trị khác trong một tập hợp các hàng.
Bài tập:
Viết một truy vấn sử dụng RANK để xếp hạng nhân viên theo doanh số bán hàng.
Viết một truy vấn sử dụng SUM để tính tổng doanh thu tích lũy theo tháng.
Viết một truy vấn sử dụng LAG để tính toán sự thay đổi doanh số so với tháng trước.
Ngày 31
Common Table Expressions (CTE):
Khái niệm về CTE và lợi ích của việc sử dụng CTE.
Cú pháp của CTE (WITH).
Sử dụng CTE để chia nhỏ truy vấn phức tạp thành các phần nhỏ hơn, dễ quản lý hơn.
Sử dụng CTE để tạo đệ quy.
Bài tập:
Viết một truy vấn sử dụng CTE để tính toán tổng doanh thu theo từng danh mục sản phẩm.
Viết một truy vấn sử dụng CTE để tìm ra tất cả các cấp quản lý của một nhân viên.
Viết một truy vấn sử dụng CTE để tạo ra một dãy số Fibonacci.
Ngày 32
Stored Procedures:
Khái niệm về stored procedure và lợi ích của việc sử dụng stored procedure.
Cú pháp của stored procedure (CREATE PROCEDURE).
Các thành phần của stored procedure (tham số, biến, điều khiển luồng, xử lý ngoại lệ).
Sử dụng ngôn ngữ PL/pgSQL để viết stored procedure.
Bài tập:
Viết một stored procedure để chèn một bản ghi mới vào bảng nhân viên.
Viết một stored procedure để cập nhật thông tin của một nhân viên.
Viết một stored procedure để tính toán tổng doanh thu theo từng danh mục sản phẩm và lưu kết quả vào một bảng mới.
Ngày 33
Cursors:
Khái niệm về cursor và lợi ích của việc sử dụng cursor.
Các loại cursor (implicit, explicit).
Các lệnh làm việc với cursor (DECLARE, OPEN, FETCH, CLOSE).
Sử dụng cursor để duyệt qua từng hàng kết quả của một truy vấn.
Bài tập:
Viết một đoạn mã PL/pgSQL sử dụng cursor để duyệt qua từng hàng trong bảng nhân viên và in ra thông tin của từng nhân viên.
Viết một stored procedure sử dụng cursor để cập nhật lương của tất cả nhân viên trong một phòng ban.
Tìm hiểu về các vấn đề hiệu suất liên quan đến việc sử dụng cursor và cách tối ưu hóa chúng.
Ngày 34
Error Handling:
Khái niệm về xử lý ngoại lệ trong PL/pgSQL.
Các khối lệnh BEGIN, EXCEPTION, END.
Các loại ngoại lệ (SQLSTATE, SQLERRM).
Sử dụng RAISE để ném ra ngoại lệ.
Bài tập:
Viết một đoạn mã PL/pgSQL để xử lý ngoại lệ khi chèn một bản ghi trùng lặp vào bảng.
Viết một stored procedure để cập nhật lương của một nhân viên và xử lý ngoại lệ nếu nhân viên không tồn tại.
Tìm hiểu về các ngoại lệ phổ biến trong PostgreSQL và cách xử lý chúng.
Ngày 35
Ôn tập và thực hành:
Xem lại các kiến thức đã học trong tuần.
Thực hiện các bài tập tổng hợp để củng cố kiến thức.
Tìm hiểu thêm về các tính năng nâng cao của PostgreSQL.
Tuần 6: PostgreSQL Performance Tuning, Security, and PostgreSQL Internals
Ngày 36
Performance Tuning:
Indexing:
Các loại index (B-tree, Hash, GiST, GIN, BRIN).
Chiến lược lập index hiệu quả (chọn cột, loại index, index đa cột).
Giám sát hiệu suất index (pg_stat_user_indexes, pg_buffercache).
Index-only scans.
Bài tập:
Tối ưu hóa các truy vấn bằng cách tạo các index phù hợp trên các cột thường được sử dụng trong điều kiện WHERE.
Sử dụng EXPLAIN để phân tích kế hoạch truy vấn và xác định xem index có được sử dụng hay không, và nếu không, tại sao.
Tìm hiểu về index-only scans và cách sử dụng chúng để cải thiện hiệu suất truy vấn.
Ngày 37
Performance Tuning (tiếp):
Table partitioning:
Khái niệm về table partitioning và lợi ích của nó (cải thiện hiệu suất truy vấn, quản lý dữ liệu dễ dàng hơn).
Các loại partitioning (range partitioning, list partitioning, hash partitioning).
Tạo và quản lý các bảng phân vùng.
Bài tập:
Tạo một bảng phân vùng theo phạm vi (range) để lưu trữ dữ liệu bán hàng theo tháng.
Tạo một bảng phân vùng theo danh sách (list) để lưu trữ dữ liệu khách hàng theo quốc gia.
Tìm hiểu về các chiến lược phân vùng khác nhau và cách chọn chiến lược phù hợp cho ứng dụng của bạn.
Ngày 38
Security:
Row Level Security (RLS):
Khái niệm về RLS và cách sử dụng nó để hạn chế quyền truy cập vào các hàng dữ liệu dựa trên các chính sách.
Tạo các chính sách RLS (USING).
Áp dụng các chính sách RLS cho các bảng và view.
Column Level Security (CLS):
Khái niệm về CLS và cách sử dụng nó để hạn chế quyền truy cập vào các cột dữ liệu dựa trên các chính sách.
Tạo các chính sách CLS (USING).
Áp dụng các chính sách CLS cho các bảng và view.
Bài tập:
Tạo một chính sách RLS để chỉ cho phép người dùng xem dữ liệu của phòng ban mà họ thuộc về.
Tạo một chính sách CLS để chỉ cho phép người dùng xem một số cột nhất định trong một bảng.
Tìm hiểu về các công cụ và kỹ thuật khác để bảo mật dữ liệu trong PostgreSQL.
Ngày 39
PostgreSQL Internals:
Buffer Management:
Khái niệm về buffer pool và cách PostgreSQL sử dụng nó để lưu trữ các trang dữ liệu trong bộ nhớ.
Các thuật toán thay thế trang (LRU, clock sweep).
Giám sát hoạt động của buffer pool (pg_buffercache).
Background Processes:
Các tiến trình nền quan trọng trong PostgreSQL (background writer, checkpointer, autovacuum).
Chức năng của từng tiến trình nền.
Cấu hình các tiến trình nền.
Bài tập:
Tìm hiểu về các tham số cấu hình liên quan đến buffer pool (shared_buffers, effective_cache_size) và tác động của chúng đến hiệu suất.
Sử dụng pg_buffercache để giám sát hoạt động của buffer pool và xác định các vấn đề tiềm ẩn.
Tìm hiểu về cách cấu hình các tiến trình nền để tối ưu hóa hiệu suất và độ tin cậy của PostgreSQL.
Ngày 40
PostgreSQL Internals (tiếp):
Query Planning and Optimization:
Các bước trong quá trình lập kế hoạch truy vấn (parsing, rewriting, planning, optimization).
Các chiến lược tối ưu hóa truy vấn (join order, index selection, query rewriting).
Sử dụng genetic query optimizer (geqo) để tìm kế hoạch truy vấn tốt nhất.
Bài tập:
Sử dụng EXPLAIN để xem kế hoạch truy vấn của PostgreSQL và hiểu cách PostgreSQL tối ưu hóa các truy vấn.
Thử nghiệm các tùy chọn cấu hình khác nhau của geqo để xem tác động của chúng đến hiệu suất truy vấn.
Tìm hiểu về các kỹ thuật viết truy vấn hiệu quả để giúp PostgreSQL tạo ra kế hoạch truy vấn tốt nhất.
Ngày 41
PostgreSQL Internals (tiếp):
Transaction Management:
Khái niệm về transaction và các thuộc tính ACID.
Các giai đoạn của transaction (begin, active, prepared, committed, aborted).
Quản lý transaction trong PostgreSQL (pg_xact).
Concurrency Control:
Các vấn đề về đồng thời (dirty read, non-repeatable read, phantom read).
Các mức cô lập transaction (READ COMMITTED, REPEATABLE READ, SERIALIZABLE).
Cơ chế Multi-Version Concurrency Control (MVCC) trong PostgreSQL.
Bài tập:
Sử dụng pg_xact để giám sát các transaction đang hoạt động trên server PostgreSQL.
Thử nghiệm các mức cô lập transaction khác nhau và quan sát hành vi của chúng.
Tìm hiểu về các kỹ thuật để tránh và xử lý deadlock trong PostgreSQL.
Ngày 42
Ôn tập và thực hành:
Xem lại các kiến thức đã học trong tuần.
Thực hiện các bài tập tổng hợp để củng cố kiến thức.
Tìm hiểu thêm về các tính năng nâng cao của PostgreSQL.
Tuần 7: Procedural Languages, Server Programming Interface, and Logical Decoding
Ngày 43
Procedural Languages (PL):
PL/pgSQL:
Cấu trúc cơ bản của PL/pgSQL (DECLARE, BEGIN, END).
Các kiểu dữ liệu trong PL/pgSQL (scalar, composite, record, refcursor).
Các cấu trúc điều khiển (IF, CASE, LOOP, FOR, WHILE).
Xử lý ngoại lệ (EXCEPTION).
Các hàm và thủ tục trong PL/pgSQL.
Bài tập:
Viết một hàm PL/pgSQL để tính tổng của hai số.
Viết một thủ tục PL/pgSQL để cập nhật thông tin của một nhân viên.
Viết một trigger PL/pgSQL để kiểm tra tính hợp lệ của dữ liệu trước khi chèn hoặc cập nhật một bản ghi.
Ngày 44
Procedural Languages (PL) (tiếp):
PL/Python:
Cài đặt và cấu hình PL/Python.
Cấu trúc cơ bản của PL/Python.
Sử dụng các thư viện Python trong PL/Python.
Truy cập dữ liệu PostgreSQL từ PL/Python.
Bài tập:
Viết một hàm PL/Python để tính giai thừa của một số.
Viết một hàm PL/Python để gửi email thông báo.
Tìm hiểu về các thư viện Python phổ biến được sử dụng trong PL/Python (ví dụ: NumPy, pandas).
Ngày 45
Server Programming Interface (SPI):
Giới thiệu về SPI và cách sử dụng SPI để thực thi các câu lệnh SQL từ bên trong một hàm C.
Các hàm SPI cơ bản (SPI_connect, SPI_exec, SPI_fetchrow, SPI_finish).
Xử lý kết quả trả về từ các câu lệnh SQL.
Quản lý bộ nhớ trong SPI.
Bài tập:
Viết một hàm C sử dụng SPI để lấy danh sách tất cả các bảng trong một schema.
Viết một hàm C sử dụng SPI để chèn một bản ghi mới vào một bảng.
Tìm hiểu về các hàm SPI nâng cao (ví dụ: SPI_prepare, SPI_execp).
Ngày 46
Logical Decoding:
Khái niệm về logical decoding và lợi ích của nó (sao chép dữ liệu, kiểm toán, tích hợp hệ thống).
Các thành phần của logical decoding (output plugin, logical replication slot).
Cấu hình logical decoding.
Sử dụng pg_recvlogical để nhận các thay đổi từ logical decoding.
Bài tập:
Thiết lập logical decoding trên một server PostgreSQL.
Sử dụng pg_recvlogical để nhận các thay đổi từ logical decoding và ghi chúng vào một file.
Tìm hiểu về các output plugin khác nhau cho logical decoding (ví dụ: test_decoding, wal2json).
Ngày 47
Background Worker Processes:
Khái niệm về background worker process và cách sử dụng chúng để thực hiện các tác vụ nền trong PostgreSQL.
Ví dụ về các background worker process (autovacuum, logical replication apply worker).
Tạo và quản lý background worker process.
Bài tập:
Tìm hiểu về các background worker process có sẵn trong PostgreSQL.
Tạo một background worker process đơn giản để in ra một thông báo log mỗi phút.
Tìm hiểu về cách sử dụng background worker process để thực hiện các tác vụ phức tạp hơn, chẳng hạn như sao lưu hoặc đồng bộ dữ liệu.
Ngày 48
Database Administration:
Quản lý users và roles:
Tạo, sửa đổi, và xóa users và roles.
Cấp và thu hồi quyền.
Quản lý mật khẩu.
Quản lý tablespaces:
Tạo, sửa đổi, và xóa tablespaces.
Gán tablespaces cho các đối tượng database (bảng, index).
Giám sát sử dụng tablespace.
Bài tập:
Tạo một user mới và cấp cho người dùng đó quyền truy cập vào một database cụ thể.
Tạo một tablespace mới và di chuyển một bảng hiện có vào tablespace đó.
Sử dụng các công cụ để giám sát dung lượng đĩa của các tablespaces.
Ngày 49
Ôn tập và thực hành:
Xem lại các kiến thức đã học trong tuần.
Thực hiện các bài tập tổng hợp để củng cố kiến thức.
Tìm hiểu thêm về các tính năng nâng cao của PostgreSQL.
Tuần 8: PostgreSQL Query Optimization, System Catalogs, and Frontend/Backend Protocol
Ngày 50
Query Optimization:
Cost-based optimization:
PostgreSQL ước tính chi phí của các kế hoạch truy vấn khác nhau dựa trên các yếu tố như kích thước bảng, số lượng hàng, phân phối giá trị, và các index có sẵn.
PostgreSQL chọn kế hoạch truy vấn có chi phí thấp nhất.
Các tham số cấu hình ảnh hưởng đến ước tính chi phí (cpu_tuple_cost, cpu_index_tuple_cost, cpu_operator_cost, random_page_cost, seq_page_cost).
Bài tập:
Sử dụng EXPLAIN để xem chi tiết về chi phí của các kế hoạch truy vấn khác nhau.
Điều chỉnh các tham số cấu hình chi phí và quan sát tác động của chúng đến kế hoạch truy vấn được chọn.
Tìm hiểu về các chiến lược để viết truy vấn hiệu quả và giúp PostgreSQL tạo ra kế hoạch truy vấn tối ưu.
Ngày 51
Query Optimization (tiếp):
Genetic Query Optimizer (GEQO):
GEQO sử dụng thuật toán di truyền để tìm kế hoạch truy vấn tốt nhất cho các truy vấn phức tạp.
Cấu hình GEQO (enable_geqo, geqo_threshold, geqo_effort, geqo_pool_size, geqo_generations, geqo_selection_bias).
So sánh hiệu suất của GEQO với traditional query optimizer.
Bài tập:
Kích hoạt GEQO và thử nghiệm hiệu suất của nó với các truy vấn phức tạp.
Điều chỉnh các tham số cấu hình của GEQO để tối ưu hóa hiệu suất.
Tìm hiểu về các trường hợp sử dụng GEQO hiệu quả nhất.
Ngày 52
System Catalogs:
Tổng quan về system catalogs:
System catalogs là các bảng đặc biệt trong PostgreSQL chứa siêu dữ liệu về cơ sở dữ liệu (cấu trúc bảng, index, view, function, trigger, v.v.).
Các system catalog quan trọng (pg_class, pg_attribute, pg_index, pg_database, pg_tablespace).
Truy vấn system catalogs:
Sử dụng các câu lệnh SQL để truy vấn system catalogs và lấy thông tin về cấu trúc cơ sở dữ liệu.
Các view hệ thống (pg_tables, pg_indexes, pg_views, pg_matviews) cung cấp giao diện dễ sử dụng hơn để truy vấn system catalogs.
Bài tập:
Truy vấn system catalogs để lấy danh sách tất cả các bảng trong một schema.
Truy vấn system catalogs để lấy danh sách tất cả các index trên một bảng.
Tạo một view tùy chỉnh để hiển thị thông tin về các bảng và index trong cơ sở dữ liệu.
Ngày 53
System Catalogs (tiếp):
Cấu trúc của system catalogs:
Các cột quan trọng trong các system catalog (oid, relname, relkind, reloptions, relnatts, relhasindex).
Các ràng buộc và index trên system catalogs.
Cập nhật system catalogs:
Các câu lệnh DDL (CREATE, ALTER, DROP) tự động cập nhật system catalogs.
Không nên cập nhật trực tiếp system catalogs bằng các câu lệnh DML (INSERT, UPDATE, DELETE).
Bài tập:
Tìm hiểu về cấu trúc chi tiết của các system catalog quan trọng (pg_class, pg_attribute, pg_index).
Sử dụng pg_dump để tạo một bản sao lưu của system catalogs.
Tìm hiểu về các công cụ để giám sát và quản lý system catalogs (pg_catcheck).
Ngày 54
Frontend/Backend Protocol:
Tổng quan về giao thức frontend/backend:
Giao thức frontend/backend là giao thức mà các client sử dụng để giao tiếp với server PostgreSQL.
Giao thức này dựa trên TCP/IP và sử dụng một định dạng tin nhắn đơn giản.
Các loại tin nhắn:
- StartupMessage, Authentication, Query, Parse, Bind, Execute, Describe, Close, Sync, Terminate.
Bài tập:
Sử dụng một công cụ bắt gói tin (ví dụ: Wireshark) để quan sát các tin nhắn được trao đổi giữa client và server PostgreSQL.
Tìm hiểu về định dạng chi tiết của các loại tin nhắn khác nhau.
Tìm hiểu về cách PostgreSQL xử lý các tin nhắn từ client và gửi phản hồi lại.
Ngày 55
PostgreSQL Coding Conventions:
Quy tắc đặt tên:
Tên biến, hàm, kiểu dữ liệu, bảng, cột.
Sử dụng các tiền tố để phân biệt các loại đối tượng (ví dụ: p_, Pg_, pg_).
Định dạng mã nguồn:
Khoảng trắng, thụt lề, dấu ngoặc nhọn.
Độ dài dòng tối đa.
Bình luận.
Bài tập:
Đọc và tìm hiểu về PostgreSQL Coding Conventions.
Xem xét mã nguồn của PostgreSQL và xác định cách các quy tắc đặt tên và định dạng được áp dụng.
Viết một đoạn mã C đơn giản tuân thủ PostgreSQL Coding Conventions.
Ngày 56
Ôn tập và thực hành:
Xem lại các kiến thức đã học trong tuần.
Thực hiện các bài tập tổng hợp để củng cố kiến thức.
Tìm hiểu thêm về các tính năng nâng cao của PostgreSQL.
Tuần 9: PostgreSQL Native Language Support, Procedural Language Handlers, and Foreign Data Wrappers
Ngày 57
Native Language Support (NLS):
Khái niệm về NLS:
Hỗ trợ nhiều ngôn ngữ trong PostgreSQL (giao diện người dùng, thông báo lỗi, sắp xếp, định dạng).
Các thành phần của NLS (locale, encoding, collation).
Cấu hình NLS:
Thiết lập locale và encoding cho cluster, database, client.
Sử dụng các hàm NLS (to_char, to_date, to_timestamp, to_number).
Bài tập:
Thay đổi locale của một database PostgreSQL sang tiếng Việt.
Sử dụng các hàm NLS để định dạng ngày tháng và số theo locale của người dùng.
Tìm hiểu về các collation khác nhau và cách sử dụng chúng để sắp xếp dữ liệu theo các quy tắc ngôn ngữ cụ thể.
Ngày 58
Writing a Procedural Language Handler:
Tổng quan về procedural language handler:
Procedural language handler cho phép bạn tích hợp các ngôn ngữ lập trình khác (Python, Perl, Tcl, v.v.) vào PostgreSQL.
Các thành phần của một procedural language handler (call handler, inline handler, validator).
Các bước tạo một procedural language handler:
Viết mã nguồn của handler bằng C.
Biên dịch và cài đặt handler.
Đăng ký handler với PostgreSQL.
Bài tập:
Tìm hiểu về các procedural language handler có sẵn trong PostgreSQL.
Nghiên cứu mã nguồn của một procedural language handler đơn giản (ví dụ: PL/pgSQL).
Thử viết một procedural language handler đơn giản cho một ngôn ngữ kịch bản (ví dụ: Lua).
Ngày 59
Writing a Foreign Data Wrapper (FDW):
Tổng quan về FDW:
FDW cho phép PostgreSQL truy cập dữ liệu từ các nguồn dữ liệu bên ngoài (các hệ quản trị CSDL khác, file CSV, web service, v.v.).
Các thành phần của một FDW (handler, validator, các hàm FDW).
Các bước tạo một FDW:
Viết mã nguồn của FDW bằng C.
Biên dịch và cài đặt FDW.
Đăng ký FDW với PostgreSQL.
Tạo server và user mapping cho FDW.
Tạo foreign table.
Bài tập:
Tìm hiểu về các FDW có sẵn trong PostgreSQL.
Nghiên cứu mã nguồn của một FDW đơn giản (ví dụ: file_fdw).
Thử viết một FDW đơn giản để truy cập dữ liệu từ một file CSV.
Ngày 60
Genetic Query Optimizer (GEQO):
Hoạt động của GEQO:
GEQO sử dụng thuật toán di truyền để tìm kế hoạch truy vấn tốt nhất.
GEQO tạo ra một quần thể các kế hoạch truy vấn và sử dụng các phép toán di truyền (đột biến, lai ghép) để tạo ra các kế hoạch mới.
GEQO đánh giá chi phí của từng kế hoạch và chọn ra kế hoạch có chi phí thấp nhất.
Cấu hình GEQO:
enable_geqo: Bật hoặc tắt GEQO.
geqo_threshold: Ngưỡng độ phức tạp của truy vấn để sử dụng GEQO.
geqo_effort: Mức độ nỗ lực của GEQO để tìm kế hoạch truy vấn tốt nhất.
Các tham số khác (geqo_pool_size, geqo_generations, geqo_selection_bias).
Bài tập:
Thử nghiệm GEQO với các truy vấn phức tạp và so sánh hiệu suất với traditional query optimizer.
Điều chỉnh các tham số cấu hình của GEQO để tối ưu hóa hiệu suất.
Tìm hiểu về các trường hợp sử dụng GEQO hiệu quả nhất (ví dụ: truy vấn có nhiều join).
Tuần 10: PostgreSQL Internals và Performance Tuning Nâng cao
Ngày 61:
PostgreSQL Internals - Hệ thống lưu trữ:
Heap: Cấu trúc lưu trữ dữ liệu trên đĩa (page, tuple, item pointer).
Index: Cấu trúc và các loại index (B-tree, Hash, GiST, GIN, BRIN).
Buffer Pool: Quản lý bộ nhớ đệm cho các trang dữ liệu.
Bài tập:
Tìm hiểu về các tham số cấu hình ảnh hưởng đến hệ thống lưu trữ (fillfactor, autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor).
Sử dụng các công cụ như pg_filedump để xem cấu trúc của một file heap trên đĩa.
Tìm hiểu về các kỹ thuật để giảm thiểu bloat trong heap.
Ngày 62:
PostgreSQL Internals - Quản lý khóa (Lock Manager):
Các loại khóa (shared lock, exclusive lock, access exclusive lock).
Cấp độ khóa (row-level lock, table-level lock).
Deadlock và cách giải quyết.
Bài tập:
Sử dụng pg_locks để xem các khóa đang được giữ bởi các tiến trình.
Tạo một tình huống deadlock và quan sát cách PostgreSQL giải quyết nó.
Tìm hiểu về các kỹ thuật để tránh deadlock trong ứng dụng của bạn.
Ngày 63:
PostgreSQL Internals - Quản lý transaction (Transaction Manager):
Các giai đoạn của một transaction (begin, active, prepared, committed, aborted).
Write-Ahead Logging (WAL).
Cơ chế Multi-Version Concurrency Control (MVCC).
Bài tập:
Tìm hiểu về các tham số cấu hình liên quan đến quản lý transaction (default_transaction_isolation, transaction_read_only, transaction_deferrable).
Sử dụng pg_xact để xem thông tin về các transaction đang hoạt động.
Tìm hiểu về cơ chế snapshot isolation trong PostgreSQL.
Ngày 64:
PostgreSQL Internals - Quá trình checkpoint:
Checkpoint là gì và tại sao nó quan trọng.
Các loại checkpoint (regular, immediate, restartpoint).
Cấu hình checkpoint (checkpoint_timeout, checkpoint_completion_target, max_wal_size).
Bài tập:
Tìm hiểu về tác động của checkpoint đến hiệu suất hệ thống.
Điều chỉnh các tham số cấu hình checkpoint để tối ưu hóa hiệu suất và độ tin cậy.
Tìm hiểu về các công cụ để giám sát quá trình checkpoint (pg_stat_bgwriter).
Ngày 65:
Performance Tuning Nâng cao:
Just-in-Time Compilation (JIT):
Sử dụng LLVM để biên dịch các biểu thức và hàm trong truy vấn thành mã máy.
Cấu hình JIT (jit_above_cost, jit_inline_above_cost, jit_optimize_above_cost).
Parallel Query:
Cho phép thực hiện các truy vấn trên nhiều tiến trình để tăng tốc độ xử lý.
Cấu hình parallel query (max_parallel_workers, max_parallel_workers_per_gather, parallel_leader_participation).
Bài tập:
Kích hoạt JIT và đánh giá tác động của nó đến hiệu suất truy vấn.
Thử nghiệm parallel query với các truy vấn lớn và phức tạp.
Tìm hiểu về các hạn chế và lưu ý khi sử dụng JIT và parallel query.
Ngày 66:
Performance Tuning Nâng cao (tiếp):
Connection Pooling:
- Sử dụng PgBouncer hoặc pgbouncer-rr để giảm thiểu chi phí tạo kết nối mới.
Query Tuning:
Sử dụng các kỹ thuật viết truy vấn hiệu quả (tránh subquery, sử dụng CTE, viết lại truy vấn, v.v.).
Sử dụng các công cụ như pg_hint_plan để hướng dẫn PostgreSQL chọn kế hoạch truy vấn tốt hơn.
Bài tập:
Cài đặt và cấu hình PgBouncer để quản lý pool kết nối đến PostgreSQL.
Thử nghiệm các kỹ thuật viết truy vấn khác nhau và so sánh hiệu suất của chúng.
Sử dụng pg_hint_plan để hướng dẫn PostgreSQL sử dụng một index cụ thể cho một truy vấn.
Ngày 67:
Ôn tập và thực hành:
Xem lại các kiến thức đã học trong tuần.
Thực hiện các bài tập tổng hợp để củng cố kiến thức.
Tìm hiểu thêm về các tính năng nâng cao của PostgreSQL.
Tuần 11: PostgreSQL Replication, Monitoring, and Security
Ngày 68
Replication (tiếp):
Logical Replication (tiếp):
Quản lý xung đột (conflict resolution) trong logical replication.
Các kỹ thuật lọc dữ liệu trong logical replication (row filters, column filters).
Sử dụng logical replication để tạo các hệ thống báo cáo hoặc phân tích dữ liệu.
Bài tập:
Tìm hiểu về các chiến lược giải quyết xung đột khác nhau trong logical replication và cách chọn chiến lược phù hợp cho ứng dụng của bạn.
Sử dụng row filters và column filters để lọc dữ liệu được sao chép trong logical replication.
Thiết lập một hệ thống logical replication để sao chép dữ liệu từ một cơ sở dữ liệu sản xuất sang một cơ sở dữ liệu báo cáo.
Ngày 69
Monitoring:
pg_stat_activity:
Giám sát các truy vấn đang chạy, thời gian thực thi, trạng thái chờ (wait event).
Xác định các truy vấn gây tắc nghẽn hoặc chạy chậm.
Hủy bỏ các truy vấn không cần thiết.
pg_stat_statements:
Theo dõi thống kê về các câu lệnh SQL đã thực thi (tổng thời gian, số lần gọi, thời gian trung bình).
Xác định các truy vấn tốn nhiều tài nguyên nhất.
Tối ưu hóa các truy vấn dựa trên thông tin thống kê.
Bài tập:
Sử dụng pg_stat_activity để tìm ra các truy vấn đang chạy lâu và xác định nguyên nhân gây chậm.
Sử dụng pg_stat_statements để xác định các truy vấn được gọi nhiều nhất và tối ưu hóa chúng.
Tìm hiểu về các công cụ giám sát khác như pgBadger và pgFouine.
Ngày 70
Monitoring (tiếp):
pg_stat_database:
- Theo dõi thống kê về các hoạt động trên cơ sở dữ liệu (số lượng transaction, số lượng xung đột, số lượng checkpoint).
pg_stat_bgwriter:
- Theo dõi hoạt động của background writer (số lượng checkpoint, số lượng trang được ghi vào đĩa).
pg_stat_user_tables, pg_statio_user_tables:
- Theo dõi số lượng đọc/ghi trên các bảng.
Bài tập:
Sử dụng pg_stat_database để giám sát hoạt động tổng thể của cơ sở dữ liệu.
Sử dụng pg_stat_bgwriter để đánh giá hiệu quả của quá trình checkpoint.
Sử dụng pg_stat_user_tables và pg_statio_user_tables để xác định các bảng có hoạt động đọc/ghi nhiều nhất.
Ngày 71
Security:
Mã hóa dữ liệu:
Transparent Data Encryption (TDE): Mã hóa toàn bộ cơ sở dữ liệu.
Column-Level Encryption: Mã hóa các cột riêng lẻ.
Client-Side Encryption: Mã hóa dữ liệu trước khi gửi đến server.
Quản lý truy cập dựa trên vai trò (RBAC):
Phân quyền cho các vai trò (roles) thay vì cho từng người dùng.
Cấp quyền cho các vai trò trên các đối tượng (bảng, view, function).
Gán người dùng vào các vai trò.
Bài tập:
Tìm hiểu về các công cụ và kỹ thuật mã hóa dữ liệu trong PostgreSQL (pgcrypto, OpenSSL).
Thiết lập một hệ thống RBAC đơn giản trong PostgreSQL.
Tìm hiểu về các best practices về bảo mật PostgreSQL.
Ngày 72
Security (tiếp):
Kiểm toán (Audit Logging):
Ghi lại các hoạt động trên cơ sở dữ liệu (các câu lệnh SQL, kết nối, đăng nhập, v.v.).
Sử dụng pgAudit hoặc các công cụ khác để ghi log kiểm toán.
Phân tích log kiểm toán để phát hiện các hoạt động bất thường hoặc trái phép.
Các biện pháp bảo mật khác:
Cập nhật phiên bản PostgreSQL thường xuyên.
Sử dụng mật khẩu mạnh và thay đổi mật khẩu định kỳ.
Hạn chế quyền truy cập từ xa.
Sử dụng tường lửa.
Bài tập:
Cài đặt và cấu hình pgAudit để ghi log kiểm toán.
Phân tích log kiểm toán để tìm ra các hoạt động bất thường.
Đánh giá mức độ bảo mật của hệ thống PostgreSQL của bạn và đề xuất các biện pháp cải thiện.
Ngày 73
Ôn tập và thực hành:
Xem lại các kiến thức đã học trong tuần.
Thực hiện các bài tập tổng hợp để củng cố kiến thức.
Tìm hiểu thêm về các tính năng nâng cao của PostgreSQL.
Tuần 12: PostgreSQL Internals và Performance Tuning Nâng cao (tiếp)
Ngày 74
PostgreSQL Internals - WAL Internals:
Cấu trúc của WAL record (resource manager, block number, data).
Các loại WAL record (XLOG, XACT, SMGR, etc.).
Quá trình WAL replay (recovery).
Bài tập:
Sử dụng pg_waldump để xem nội dung của các WAL record.
Tìm hiểu về các công cụ để phân tích WAL (pg_xlogdump).
Tìm hiểu về các tham số cấu hình ảnh hưởng đến WAL (wal_compression, wal_buffers, wal_writer_delay).
Ngày 75
PostgreSQL Internals - Buffer Manager Internals:
Cấu trúc của buffer pool (shared buffers, buffer descriptors, buffer headers).
Các thuật toán thay thế trang (LRU, clock sweep, 2Q).
Giám sát hoạt động của buffer pool (pg_buffercache).
Bài tập:
Tìm hiểu về các tham số cấu hình ảnh hưởng đến buffer pool (shared_buffers, effective_cache_size, bgwriter_delay).
Sử dụng pg_buffercache để phân tích hoạt động của buffer pool và tìm ra các vấn đề tiềm ẩn.
Tìm hiểu về các kỹ thuật để cải thiện hiệu suất của buffer pool.
Ngày 76
PostgreSQL Internals - Query Executor Internals:
Các giai đoạn của quá trình thực thi truy vấn (plan, scan, join, sort, aggregate, materialize).
Cấu trúc dữ liệu được sử dụng trong quá trình thực thi truy vấn (tuplestore, hash table, sort buffer).
Bài tập:
Sử dụng EXPLAIN ANALYZE để xem chi tiết về thời gian thực thi của từng giai đoạn trong kế hoạch truy vấn.
Tìm hiểu về các kỹ thuật tối ưu hóa truy vấn (ví dụ: sử dụng index, giảm thiểu số lượng join, sử dụng các hàm aggregate hiệu quả).
Tìm hiểu về các công cụ để giám sát hiệu suất của query executor (pg_stat_activity, pg_stat_statements).
Ngày 77
Performance Tuning - Memory Tuning:
Các thành phần bộ nhớ chính trong PostgreSQL (shared buffers, work_mem, maintenance_work_mem, wal_buffers).
Các kỹ thuật tuning bộ nhớ (tối ưu hóa shared_buffers, work_mem, sử dụng huge pages).
Giám sát sử dụng bộ nhớ (pg_top, top).
Bài tập:
Điều chỉnh các tham số cấu hình bộ nhớ để tối ưu hóa hiệu suất PostgreSQL cho ứng dụng của bạn.
Sử dụng pg_top để giám sát sử dụng bộ nhớ của PostgreSQL.
Tìm hiểu về các công cụ khác để giám sát và phân tích sử dụng bộ nhớ (ví dụ: pgbadger, pg_buffercache).
Ngày 78
Performance Tuning - Disk Tuning:
Cấu hình hệ thống file (ext4, XFS, ZFS).
Tối ưu hóa I/O (RAID, SSD, alignment).
Giám sát hoạt động đĩa (iostat, vmstat, sar).
Bài tập:
Tìm hiểu về các hệ thống file được khuyến nghị cho PostgreSQL và cách cấu hình chúng.
Sử dụng iostat để giám sát hoạt động I/O của PostgreSQL.
Tìm hiểu về các kỹ thuật để cải thiện hiệu suất I/O của PostgreSQL (ví dụ: sử dụng asynchronous I/O, prefetch, RAID).
Ngày 79
Performance Tuning - Network Tuning:
Tối ưu hóa cấu hình mạng (TCP/IP parameters).
Sử dụng PgBouncer hoặc pgbouncer-rr để quản lý pool kết nối.
Giám sát lưu lượng mạng (tcpdump, Wireshark).
Bài tập:
Tìm hiểu về các tham số TCP/IP ảnh hưởng đến hiệu suất của PostgreSQL và cách điều chỉnh chúng.
Cài đặt và cấu hình PgBouncer hoặc pgbouncer-rr để quản lý pool kết nối đến PostgreSQL.
Sử dụng tcpdump hoặc Wireshark để giám sát lưu lượng mạng giữa client và server PostgreSQL.
Ngày 80
Ôn tập và thực hành:
Xem lại các kiến thức đã học trong tuần.
Thực hiện các bài tập tổng hợp để củng cố kiến thức.
Tìm hiểu thêm về các tính năng nâng cao của PostgreSQL.
Tuần 13: PostgreSQL Extensibility, High Availability, và Cloud
Ngày 81
Extending PostgreSQL - Custom Data Types:
Khái niệm về kiểu dữ liệu tùy chỉnh và lợi ích của việc sử dụng chúng.
Các bước tạo một kiểu dữ liệu tùy chỉnh:
Định nghĩa cấu trúc dữ liệu trong C.
Viết các hàm input/output.
Viết các hàm xử lý (binary I/O, text I/O, nhận dạng loại, so sánh, sắp xếp, hash).
Đăng ký kiểu dữ liệu với PostgreSQL.
Bài tập:
Tạo một kiểu dữ liệu tùy chỉnh để biểu diễn số phức (complex number).
Tạo một kiểu dữ liệu tùy chỉnh để biểu diễn địa chỉ IP (IP address).
Tạo một kiểu dữ liệu tùy chỉnh để biểu diễn một khoảng thời gian (time range).
Ngày 82
Extending PostgreSQL - Custom Operators:
Khái niệm về toán tử tùy chỉnh và lợi ích của việc sử dụng chúng.
Các loại toán tử (unary, binary, ternary).
Các bước tạo một toán tử tùy chỉnh:
Chọn tên và ký hiệu cho toán tử.
Định nghĩa các kiểu dữ liệu đầu vào và đầu ra của toán tử.
Viết hàm thực hiện toán tử (C function).
Đăng ký toán tử với PostgreSQL.
Bài tập:
Tạo một toán tử tùy chỉnh để tính toán khoảng cách giữa hai điểm trên mặt phẳng.
Tạo một toán tử tùy chỉnh để kiểm tra xem một địa chỉ IP có nằm trong một mạng con (subnet) hay không.
Tạo một toán tử tùy chỉnh để so sánh hai khoảng thời gian.
Ngày 83
Extending PostgreSQL - Custom Functions and Aggregates:
Tổng quan về hàm và hàm aggregate tùy chỉnh.
Các bước tạo một hàm tùy chỉnh:
Chọn tên và tham số cho hàm.
Định nghĩa kiểu trả về của hàm.
Viết hàm thực hiện bằng C hoặc một ngôn ngữ thủ tục (PL/pgSQL, PL/Python, etc.).
Đăng ký hàm với PostgreSQL.
Các bước tạo một hàm aggregate tùy chỉnh:
Định nghĩa trạng thái (state) của hàm aggregate.
Viết các hàm transition function, final function, combine function (nếu cần).
Đăng ký hàm aggregate với PostgreSQL.
Bài tập:
Viết một hàm tùy chỉnh để tính toán số Fibonacci thứ n.
Viết một hàm aggregate tùy chỉnh để tính trung bình nhân của một tập hợp số.
Viết một hàm aggregate tùy chỉnh để nối các chuỗi trong một nhóm.
Ngày 84
Extending PostgreSQL - Access Methods:
Khái niệm về access method và vai trò của chúng trong PostgreSQL.
Các loại access method (heap, B-tree, hash, GiST, GIN, BRIN).
Cấu trúc của một access method (AM handler, AM support functions).
Các bước tạo một access method tùy chỉnh:
Viết mã nguồn của access method bằng C.
Biên dịch và cài đặt access method.
Đăng ký access method với PostgreSQL.
Bài tập:
Tìm hiểu về các access method có sẵn trong PostgreSQL.
Nghiên cứu mã nguồn của một access method đơn giản (ví dụ: heap).
Thử viết một access method đơn giản để lưu trữ dữ liệu theo một cấu trúc cụ thể.
Ngày 85
Extending PostgreSQL - Table Storage Formats:
Khái niệm về table storage format và các loại table storage format (heap, append-optimized, columnar).
Ưu điểm và nhược điểm của từng loại table storage format.
Cách chọn table storage format phù hợp cho ứng dụng của bạn.
Bài tập:
Tạo một bảng sử dụng table storage format append-optimized và so sánh hiệu suất của nó với một bảng sử dụng table storage format heap.
Tìm hiểu về các extension cung cấp các table storage format khác (ví dụ: Citus, ZomboDB).
Tìm hiểu về các kỹ thuật để tối ưu hóa hiệu suất của các bảng sử dụng các table storage format khác nhau.
Ngày 86
High Availability - PostgreSQL Cluster Management:
Patroni:
Giới thiệu về Patroni và cách hoạt động của nó.
Cài đặt và cấu hình Patroni.
Quản lý một PostgreSQL cluster sử dụng Patroni.
Tích hợp Patroni với các công cụ khác (ví dụ: etcd, Consul).
Bài tập:
Thiết lập một PostgreSQL cluster HA sử dụng Patroni.
Thử nghiệm failover và switchover trong PostgreSQL cluster.
Tìm hiểu về các tính năng nâng cao của Patroni (ví dụ: backup automation, monitoring).
Ngày 87
PostgreSQL Cloud:
Tổng quan về PostgreSQL trên các nền tảng đám mây (AWS, Azure, Google Cloud).
Các dịch vụ PostgreSQL được quản lý (ví dụ: Amazon RDS, Azure Database for PostgreSQL, Google Cloud SQL).
Ưu điểm và nhược điểm của việc sử dụng PostgreSQL trên đám mây.
Bài tập:
Tạo một instance PostgreSQL trên Amazon RDS.
Tạo một instance PostgreSQL trên Azure Database for PostgreSQL.
So sánh các tính năng và giá cả của các dịch vụ PostgreSQL được quản lý khác nhau.
Tuần 14: PostgreSQL Security Nâng cao và PostgreSQL Cluster Management
Ngày 88
Security - Advanced Authentication Methods:
LDAP Authentication:
Giới thiệu về LDAP và cách tích hợp LDAP với PostgreSQL để xác thực người dùng.
Cấu hình LDAP authentication trong pg_hba.conf.
Các tùy chọn cấu hình LDAP (ldapserver, ldapbasedn, ldapbinddn, ldapbindpasswd).
Bài tập:
Thiết lập LDAP authentication cho PostgreSQL.
Tạo một người dùng trong LDAP và cấp cho người dùng đó quyền truy cập vào PostgreSQL.
Tìm hiểu về các vấn đề bảo mật liên quan đến LDAP authentication và cách giải quyết chúng.
Ngày 89
Security - Advanced Authentication Methods (tiếp):
Kerberos Authentication:
Giới thiệu về Kerberos và cách tích hợp Kerberos với PostgreSQL để xác thực người dùng.
Cấu hình Kerberos authentication trong pg_hba.conf.
Các tùy chọn cấu hình Kerberos (krb_server_hostname, krb_server_port, krb_service_name).
GSSAPI Authentication:
Giới thiệu về GSSAPI và cách tích hợp GSSAPI với PostgreSQL để xác thực người dùng.
Cấu hình GSSAPI authentication trong pg_hba.conf.
Các tùy chọn cấu hình GSSAPI (gss_server_hostname, gss_server_port, gss_service_name).
Bài tập:
Thiết lập Kerberos authentication cho PostgreSQL.
Tạo một người dùng trong Kerberos và cấp cho người dùng đó quyền truy cập vào PostgreSQL.
Tìm hiểu về GSSAPI và cách thiết lập GSSAPI authentication cho PostgreSQL.
Ngày 90
Security - Encryption:
PGP Encryption:
Giới thiệu về PGP và cách sử dụng PGP để mã hóa dữ liệu trong PostgreSQL.
Các hàm PGP trong PostgreSQL (pgp_sym_encrypt, pgp_sym_decrypt, pgp_pub_encrypt, pgp_pub_decrypt).
Quản lý khóa PGP.
Bài tập:
Sử dụng các hàm PGP để mã hóa và giải mã dữ liệu trong PostgreSQL.
Tạo một khóa PGP và sử dụng nó để mã hóa một cột trong một bảng.
Tìm hiểu về các best practices về quản lý khóa PGP.
Ngày 91
Security - Security Auditing:
pgAudit:
Giới thiệu về pgAudit và cách sử dụng nó để ghi lại các hoạt động trên cơ sở dữ liệu PostgreSQL.
Cấu hình pgAudit (pgaudit.log, pgaudit.log_catalog, pgaudit.role).
Phân tích log pgAudit.
Bài tập:
Cài đặt và cấu hình pgAudit.
Xem xét log pgAudit và tìm hiểu về các sự kiện được ghi lại.
Tìm hiểu về các công cụ khác để phân tích log pgAudit (ví dụ: ELK stack).
Ngày 92
PostgreSQL Cluster Management:
pg_auto_failover:
Giới thiệu về pg_auto_failover và cách sử dụng nó để quản lý một PostgreSQL cluster có khả năng tự động chuyển đổi dự phòng (failover).
Cài đặt và cấu hình pg_auto_failover.
Quản lý PostgreSQL cluster sử dụng pg_auto_failover.
Bài tập:
Thiết lập một PostgreSQL cluster sử dụng pg_auto_failover.
Thử nghiệm failover và switchover trong PostgreSQL cluster.
Tìm hiểu về các tính năng nâng cao của pg_auto_failover (ví dụ: backup automation, monitoring).
Ngày 93
PostgreSQL Cluster Management (tiếp):
repmgr:
Giới thiệu về repmgr và cách sử dụng nó để quản lý một PostgreSQL cluster replication.
Cài đặt và cấu hình repmgr.
Quản lý PostgreSQL cluster sử dụng repmgr.
Bài tập:
Thiết lập một PostgreSQL cluster replication sử dụng repmgr.
Thử nghiệm failover và switchover trong PostgreSQL cluster replication.
Tìm hiểu về các tính năng nâng cao của repmgr (ví dụ: node monitoring, automatic failover).
Ngày 94
Ôn tập và thực hành:
Xem lại các kiến thức đã học trong tuần.
Thực hiện các bài tập tổng hợp để củng cố kiến thức.
Tìm hiểu thêm về các tính năng nâng cao của PostgreSQL.
Tuần 15: PostgreSQL Internals Nâng cao và PostgreSQL in Cloud
Ngày 95
PostgreSQL Internals - Transaction ID Wraparound:
Hiểu về khái niệm Transaction ID (XID) và giới hạn của nó.
Vấn đề Transaction ID wraparound và cách PostgreSQL xử lý nó.
Các công cụ và kỹ thuật để giám sát và ngăn chặn Transaction ID wraparound.
Bài tập:
Tìm hiểu về các tham số cấu hình liên quan đến Transaction ID wraparound (autovacuum_freeze_max_age, vacuum_freeze_min_age).
Sử dụng pg_xact để giám sát các transaction và xác định các transaction có nguy cơ gây ra wraparound.
Lập kế hoạch và thực hiện vacuuming để ngăn chặn Transaction ID wraparound.
Ngày 96
PostgreSQL Internals - Hot Standby Internals:
Khái niệm về hot standby và cách thức hoạt động của nó.
Các thành phần của hot standby (WAL sender, WAL receiver, apply process).
Cấu hình và quản lý hot standby.
Bài tập:
Thiết lập một hệ thống hot standby cho PostgreSQL.
Giám sát hoạt động của hot standby bằng các công cụ như pg_stat_replication.
Tìm hiểu về các kỹ thuật để tối ưu hóa hiệu suất của hot standby.
Ngày 97
PostgreSQL Internals - Background Worker Internals:
Tổng quan về các background worker trong PostgreSQL (autovacuum, background writer, checkpointer, WAL writer, logical replication worker).
Cách thức hoạt động của từng background worker.
Cấu hình và giám sát các background worker.
Bài tập:
Tìm hiểu về các tham số cấu hình liên quan đến từng background worker.
Sử dụng pg_stat_activity để giám sát hoạt động của các background worker.
Tìm hiểu về các kỹ thuật để tối ưu hóa hiệu suất của các background worker.
Ngày 98
PostgreSQL Cloud - Amazon RDS for PostgreSQL:
Giới thiệu về Amazon RDS for PostgreSQL và các tính năng của nó (automated provisioning, backups, software patching, high availability).
Các tùy chọn cấu hình cho Amazon RDS for PostgreSQL (instance types, storage types, parameter groups).
Quản lý và giám sát Amazon RDS for PostgreSQL.
Bài tập:
Tạo một instance PostgreSQL trên Amazon RDS.
Cấu hình các tham số cho instance PostgreSQL trên Amazon RDS.
Sử dụng Amazon CloudWatch để giám sát instance PostgreSQL trên Amazon RDS.
Ngày 99
PostgreSQL Cloud - Azure Database for PostgreSQL:
Giới thiệu về Azure Database for PostgreSQL và các tính năng của nó (automated provisioning, backups, software patching, high availability).
Các tùy chọn cấu hình cho Azure Database for PostgreSQL (pricing tiers, compute generations, storage types).
Quản lý và giám sát Azure Database for PostgreSQL.
Bài tập:
Tạo một instance PostgreSQL trên Azure Database for PostgreSQL.
Cấu hình các tham số cho instance PostgreSQL trên Azure Database for PostgreSQL.
Sử dụng Azure Monitor để giám sát instance PostgreSQL trên Azure Database for PostgreSQL.
Ngày 100
PostgreSQL Cloud - Google Cloud SQL for PostgreSQL:
Giới thiệu về Google Cloud SQL for PostgreSQL và các tính năng của nó (automated provisioning, backups, software patching, high availability).
Các tùy chọn cấu hình cho Google Cloud SQL for PostgreSQL (machine types, storage types).
Quản lý và giám sát Google Cloud SQL for PostgreSQL.
Bài tập:
Tạo một instance PostgreSQL trên Google Cloud SQL for PostgreSQL.
Cấu hình các tham số cho instance PostgreSQL trên Google Cloud SQL for PostgreSQL.
Sử dụng Google Cloud Monitoring để giám sát instance PostgreSQL trên Google Cloud SQL for PostgreSQL.
Ngày 101
Ôn tập và thực hành:
Xem lại các kiến thức đã học trong tuần.
Thực hiện các bài tập tổng hợp để củng cố kiến thức.
Tìm hiểu thêm về các dịch vụ PostgreSQL trên đám mây khác (ví dụ: Heroku Postgres, DigitalOcean Managed Databases).
Tuần 16: PostgreSQL Extensions, Advanced Security, và PostgreSQL for Data Warehousing
Ngày 102
PostgreSQL Extensions:
PostGIS:
Giới thiệu về PostGIS và các tính năng của nó (kiểu dữ liệu không gian, chỉ mục không gian, hàm không gian).
Cài đặt và cấu hình PostGIS.
Sử dụng PostGIS để lưu trữ, truy vấn và phân tích dữ liệu không gian.
Bài tập:
Cài đặt PostGIS trên PostgreSQL.
Tạo một bảng chứa dữ liệu địa lý (điểm, đường, đa giác) và sử dụng PostGIS để thực hiện các truy vấn không gian.
Tìm hiểu về các ứng dụng của PostGIS trong các lĩnh vực như GIS, quản lý tài sản, và định vị.
Ngày 103
PostgreSQL Extensions (tiếp):
pg_trgm:
Giới thiệu về pg_trgm và cách sử dụng nó để thực hiện tìm kiếm gần đúng trên các chuỗi văn bản.
Cài đặt và cấu hình pg_trgm.
Sử dụng các toán tử tìm kiếm gần đúng (%, <%, <<%, >>%, %>).
Bài tập:
Cài đặt pg_trgm trên PostgreSQL.
Tạo một bảng chứa dữ liệu văn bản và sử dụng pg_trgm để thực hiện tìm kiếm gần đúng.
Tìm hiểu về các kỹ thuật để cải thiện hiệu suất tìm kiếm gần đúng.
Ngày 104
Security - Advanced Security Hardening:
SELinux:
Giới thiệu về SELinux và cách sử dụng nó để tăng cường bảo mật cho PostgreSQL.
Cấu hình SELinux cho PostgreSQL.
AppArmor:
Giới thiệu về AppArmor và cách sử dụng nó để tăng cường bảo mật cho PostgreSQL.
Cấu hình AppArmor cho PostgreSQL.
Bài tập:
Tìm hiểu về SELinux và cách cấu hình nó cho PostgreSQL.
Tìm hiểu về AppArmor và cách cấu hình nó cho PostgreSQL.
So sánh ưu nhược điểm của SELinux và AppArmor.
Ngày 105
Security - Penetration Testing:
Tổng quan về penetration testing và tầm quan trọng của nó trong việc đánh giá bảo mật của PostgreSQL.
Các công cụ và kỹ thuật penetration testing cho PostgreSQL (ví dụ: sqlmap, Metasploit).
Các bước thực hiện penetration testing.
Bài tập:
Tìm hiểu về các công cụ và kỹ thuật penetration testing cho PostgreSQL.
Thực hiện một cuộc kiểm tra penetration testing đơn giản trên một hệ thống PostgreSQL.
Tìm hiểu về các biện pháp giảm thiểu rủi ro bảo mật sau khi thực hiện penetration testing.
Ngày 106
Security - Security Best Practices:
Sử dụng mật khẩu mạnh và thay đổi mật khẩu định kỳ.
Hạn chế quyền truy cập từ xa.
Cập nhật phiên bản PostgreSQL thường xuyên.
Mã hóa dữ liệu nhạy cảm.
Kiểm tra và giám sát log thường xuyên.
Bài tập:
Đánh giá mức độ bảo mật của hệ thống PostgreSQL của bạn dựa trên các best practices.
Đề xuất các biện pháp cải thiện bảo mật cho hệ thống PostgreSQL của bạn.
Tìm hiểu về các tiêu chuẩn bảo mật liên quan đến PostgreSQL (ví dụ: PCI DSS, HIPAA).
Ngày 107
PostgreSQL for Data Warehousing:
Giới thiệu về data warehousing và các khái niệm liên quan (dimensional modeling, fact table, dimension table).
Các tính năng của PostgreSQL hỗ trợ data warehousing (columnar storage, parallel query, materialized views).
Các công cụ ETL (Extract, Transform, Load) để tích hợp dữ liệu vào PostgreSQL data warehouse.
Bài tập:
Thiết kế một data warehouse đơn giản cho một doanh nghiệp bán lẻ.
Sử dụng PostgreSQL để xây dựng data warehouse đó.
Tìm hiểu về các công cụ ETL phổ biến (ví dụ: Talend, Pentaho).
Ngày 108
Ôn tập và thực hành:
Xem lại các kiến thức đã học trong tuần.
Thực hiện các bài tập tổng hợp để củng cố kiến thức.
Tìm hiểu thêm về các ứng dụng của PostgreSQL trong lĩnh vực data warehousing.
Tuần 17: PostgreSQL Internals nâng cao, PostgreSQL cho Machine Learning và IoT
Ngày 109
PostgreSQL Internals - PostgreSQL internals for performance optimization:
Hiểu sâu hơn về cách PostgreSQL thực thi truy vấn:
Cấu trúc của cây truy vấn (query tree).
Các giai đoạn thực thi truy vấn (parse, rewrite, plan, optimize, execute).
Vai trò của planner và optimizer trong việc chọn kế hoạch truy vấn tối ưu.
Các kỹ thuật tối ưu hóa truy vấn:
Sử dụng index hiệu quả.
Tránh các phép join không cần thiết.
Sử dụng các hàm aggregate hiệu quả.
Viết lại truy vấn để đơn giản hóa.
Bài tập:
Sử dụng EXPLAIN ANALYZE để phân tích chi tiết về thời gian thực thi của từng giai đoạn trong kế hoạch truy vấn.
Sử dụng các công cụ như pg_hint_plan để hướng dẫn PostgreSQL chọn kế hoạch truy vấn tốt hơn.
Tìm hiểu về các kỹ thuật tối ưu hóa truy vấn khác như materialized view, common table expressions (CTE), và window functions.
Ngày 110
PostgreSQL Internals - Query Execution Internals:
Các thành phần của query executor:
Node (đại diện cho một phép toán trong kế hoạch truy vấn).
Path (đại diện cho một cách thực hiện một node).
Plan (đại diện cho toàn bộ kế hoạch truy vấn).
Các loại node:
Scan nodes (Sequential Scan, Index Scan, Bitmap Heap Scan).
Join nodes (Nested Loop, Hash Join, Merge Join).
Materialization nodes (Sort, Aggregate, Hash).
Bài tập:
Tìm hiểu về các loại node khác nhau trong query executor và cách chúng hoạt động.
Sử dụng EXPLAIN để xem các node trong kế hoạch truy vấn của bạn.
Tìm hiểu về các kỹ thuật để tối ưu hóa từng loại node.
Ngày 111
PostgreSQL for Machine Learning:
pgvector:
Giới thiệu về pgvector, một extension PostgreSQL hỗ trợ lưu trữ và truy vấn các vector nhúng (embedding vectors).
Cài đặt và sử dụng pgvector.
Các hàm và toán tử vector trong pgvector (l2_distance, inner_product, cosine_distance).
Bài tập:
Cài đặt pgvector trên PostgreSQL.
Tạo một bảng chứa các vector nhúng và sử dụng pgvector để tìm các vector tương tự.
Tìm hiểu về các ứng dụng của pgvector trong machine learning, như tìm kiếm hình ảnh hoặc phân cụm văn bản.
Ngày 112
PostgreSQL for Machine Learning (tiếp):
PL/Python:
Sử dụng PL/Python để tích hợp các thư viện machine learning của Python (scikit-learn, TensorFlow, PyTorch) vào PostgreSQL.
Viết các hàm PL/Python để thực hiện các tác vụ machine learning (huấn luyện mô hình, dự đoán).
Bài tập:
Viết một hàm PL/Python để huấn luyện một mô hình hồi quy tuyến tính đơn giản.
Viết một hàm PL/Python để dự đoán giá trị của một biến dựa trên mô hình đã huấn luyện.
Tìm hiểu về các thư viện Python khác có thể được tích hợp với PostgreSQL để thực hiện các tác vụ machine learning.
Ngày 113
PostgreSQL for IoT:
TimescaleDB:
Giới thiệu về TimescaleDB, một extension PostgreSQL được tối ưu hóa cho dữ liệu chuỗi thời gian.
Cài đặt và sử dụng TimescaleDB.
Các tính năng của TimescaleDB (hypertables, continuous aggregates, compression).
Bài tập:
Cài đặt TimescaleDB trên PostgreSQL.
Tạo một hypertable để lưu trữ dữ liệu cảm biến từ các thiết bị IoT.
Sử dụng TimescaleDB để thực hiện các truy vấn phân tích trên dữ liệu chuỗi thời gian.
Ngày 114
PostgreSQL for IoT (tiếp):
Apache Kafka:
Giới thiệu về Apache Kafka, một nền tảng stream processing phân tán.
Tích hợp PostgreSQL với Apache Kafka để xử lý dữ liệu IoT theo thời gian thực.
Sử dụng Kafka Connect để truyền dữ liệu giữa PostgreSQL và Kafka.
Bài tập:
Tìm hiểu về kiến trúc và các thành phần của Apache Kafka.
Cài đặt và cấu hình Kafka Connect để kết nối PostgreSQL với Kafka.
Xây dựng một ứng dụng đơn giản để truyền dữ liệu cảm biến từ các thiết bị IoT đến PostgreSQL thông qua Kafka.
Ngày 115
Ôn tập và thực hành:
Xem lại các kiến thức đã học trong tuần.
Thực hiện các bài tập tổng hợp để củng cố kiến thức.
Tìm hiểu thêm về các ứng dụng của PostgreSQL trong machine learning và IoT.
Tuần 18: PostgreSQL Internals nâng cao và Tối ưu hóa Hiệu suất
Ngày 116
PostgreSQL Internals - Replication Internals:
Physical Replication:
Cơ chế hoạt động của streaming replication và file-based log shipping.
Các thành phần liên quan (WAL sender, WAL receiver, startup process).
Giám sát và quản lý replication (pg_stat_replication).
Bài tập:
Tìm hiểu chi tiết về các tham số cấu hình liên quan đến physical replication (wal_level, max_wal_senders, hot_standby).
Thiết lập một hệ thống streaming replication và thực hiện các bài kiểm tra failover.
Tìm hiểu về các công cụ giám sát replication như pg_stat_replication và pg_current_xlog_location.
Ngày 117
PostgreSQL Internals - Concurrency Control Internals:
Multi-Version Concurrency Control (MVCC):
Hiểu sâu hơn về cơ chế MVCC và cách nó hoạt động trong PostgreSQL.
Transaction ID (XID), Command ID (CID) và snapshot.
Các quy tắc hiển thị (visibility rules) trong MVCC.
Lock Manager:
Các loại khóa (shared lock, exclusive lock, access exclusive lock).
Cấp độ khóa (row-level lock, table-level lock).
Deadlock và cách giải quyết.
Bài tập:
Tìm hiểu về các tham số cấu hình ảnh hưởng đến MVCC (default_transaction_isolation, transaction_isolation).
Sử dụng pg_locks để xem thông tin về các khóa đang được giữ.
Tìm hiểu về các kỹ thuật để tránh và xử lý deadlock trong PostgreSQL.
Ngày 118
PostgreSQL Internals - Background Worker Internals (tiếp):
Autovacuum:
Cơ chế hoạt động của autovacuum (vacuum, analyze).
Các tham số cấu hình autovacuum (autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor, autovacuum_vacuum_threshold, autovacuum_analyze_threshold).
Giám sát hoạt động của autovacuum (pg_stat_activity, pg_stat_progress_vacuum).
Bài tập:
Tìm hiểu về tác động của autovacuum đến hiệu suất hệ thống.
Điều chỉnh các tham số cấu hình autovacuum để tối ưu hóa hiệu suất.
Tìm hiểu về các công cụ để giám sát và quản lý autovacuum (pg_autovacuum).
Ngày 119
PostgreSQL Internals - Background Worker Internals (tiếp):
Background Writer:
Cơ chế hoạt động của background writer (viết các dirty page từ shared buffers xuống đĩa).
Các tham số cấu hình background writer (bgwriter_delay, bgwriter_lru_maxpages, bgwriter_lru_multiplier).
Giám sát hoạt động của background writer (pg_stat_bgwriter).
Bài tập:
Tìm hiểu về tác động của background writer đến hiệu suất hệ thống.
Điều chỉnh các tham số cấu hình background writer để tối ưu hóa hiệu suất.
Tìm hiểu về các công cụ để giám sát hoạt động của background writer.
Ngày 120
Performance Tuning - Query Tuning Nâng cao:
Sử dụng câu lệnh
EXPLAIN (ANALYZE, BUFFERS)để phân tích chi tiết về kế hoạch truy vấn và sử dụng bộ nhớ đệm.Hiểu về các khái niệm như "work_mem", "temp_buffers", và "shared_buffers" và cách chúng ảnh hưởng đến hiệu suất truy vấn.
Tối ưu hóa truy vấn bằng cách sử dụng các kỹ thuật như "Common Table Expressions" (CTEs), "Window Functions", và "Materialized Views".
Bài tập:
Sử dụng
EXPLAIN (ANALYZE, BUFFERS)để phân tích một truy vấn phức tạp và xác định các nút thắt cổ chai.Tối ưu hóa truy vấn bằng cách điều chỉnh các tham số cấu hình liên quan đến bộ nhớ đệm và sử dụng các kỹ thuật truy vấn nâng cao.
Tạo một materialized view để tăng tốc độ truy vấn thường xuyên sử dụng.
Ngày 121
Performance Tuning - Index Tuning Nâng cao:
Hiểu về các khái niệm như "index selectivity", "index cardinality", và "index bloat".
Tối ưu hóa index bằng cách sử dụng các kỹ thuật như "partial indexes", "expression indexes", và "covering indexes".
Giám sát và bảo trì index (REINDEX, VACUUM).
Bài tập:
Tạo một partial index để tăng tốc độ truy vấn trên một tập hợp con của dữ liệu.
Tạo một expression index để tăng tốc độ truy vấn trên một biểu thức tính toán.
Sử dụng REINDEX và VACUUM để bảo trì các index và giảm thiểu index bloat.
Ngày 122
Ôn tập và thực hành:
Xem lại các kiến thức đã học trong tuần.
Thực hiện các bài tập tổng hợp để củng cố kiến thức.
Tìm hiểu thêm về các khía cạnh nâng cao của PostgreSQL internals và performance tuning.
Tuần 19: PostgreSQL Extensibility Nâng cao và PostgreSQL cho Blockchain
Ngày 123
Extending PostgreSQL - Custom Background Workers:
Khái niệm về background worker và lợi ích của việc sử dụng chúng.
Cấu trúc của một background worker (registration, main function, signal handlers).
Các bước tạo một background worker tùy chỉnh:
Viết mã nguồn của background worker bằng C.
Biên dịch và cài đặt background worker.
Đăng ký background worker với PostgreSQL.
Bài tập:
Tìm hiểu về các background worker có sẵn trong PostgreSQL (autovacuum, logical replication apply worker).
Viết một background worker đơn giản để thực hiện một tác vụ định kỳ (ví dụ: gửi email thông báo).
Tìm hiểu về cách sử dụng background worker để thực hiện các tác vụ phức tạp hơn, chẳng hạn như sao lưu hoặc đồng bộ dữ liệu.
Ngày 124
Extending PostgreSQL - Procedural Languages (tiếp):
PL/Perl:
Cài đặt và cấu hình PL/Perl.
Cấu trúc cơ bản của PL/Perl.
Sử dụng các module Perl trong PL/Perl.
Truy cập dữ liệu PostgreSQL từ PL/Perl.
PL/Tcl:
Cài đặt và cấu hình PL/Tcl.
Cấu trúc cơ bản của PL/Tcl.
Sử dụng các lệnh Tcl trong PL/Tcl.
Truy cập dữ liệu PostgreSQL từ PL/Tcl.
Bài tập:
Viết một hàm PL/Perl để xử lý chuỗi.
Viết một hàm PL/Tcl để thực hiện các thao tác trên file.
So sánh ưu nhược điểm của PL/pgSQL, PL/Python, PL/Perl, và PL/Tcl.
Ngày 125
Extending PostgreSQL - Extensions for Specific Use Cases:
pg_stat_monitor:
Giới thiệu về pg_stat_monitor, một extension để giám sát hiệu suất PostgreSQL chi tiết hơn.
Cài đặt và cấu hình pg_stat_monitor.
Sử dụng pg_stat_monitor để thu thập thông tin về hoạt động của cơ sở dữ liệu, truy vấn, index, và các đối tượng khác.
HypoPG:
Giới thiệu về HypoPG, một extension để tạo các index giả định (hypothetical index) và đánh giá tác động của chúng đến hiệu suất truy vấn.
Cài đặt và sử dụng HypoPG.
Bài tập:
Cài đặt và cấu hình pg_stat_monitor.
Sử dụng pg_stat_monitor để phân tích hiệu suất của cơ sở dữ liệu PostgreSQL.
Sử dụng HypoPG để đánh giá tác động của việc tạo thêm index lên hiệu suất truy vấn.
Ngày 126
PostgreSQL for Blockchain:
Tổng quan về công nghệ blockchain và các ứng dụng của nó.
PostgreSQL và blockchain:
Sử dụng PostgreSQL để lưu trữ dữ liệu blockchain (ví dụ: các block, transaction).
Sử dụng PostgreSQL để xây dựng các ứng dụng blockchain (ví dụ: smart contract).
Tích hợp PostgreSQL với các nền tảng blockchain (ví dụ: Ethereum, Hyperledger Fabric).
Bài tập:
Tìm hiểu về các khái niệm cơ bản của công nghệ blockchain (ví dụ: block, transaction, consensus).
Thiết kế một cơ sở dữ liệu PostgreSQL để lưu trữ dữ liệu blockchain đơn giản.
Tìm hiểu về các dự án blockchain sử dụng PostgreSQL (ví dụ: BigchainDB).
Ngày 127
PostgreSQL for Blockchain (tiếp):
Các thách thức khi sử dụng PostgreSQL cho blockchain (ví dụ: hiệu suất, khả năng mở rộng).
Các giải pháp để giải quyết các thách thức này (ví dụ: sharding, partitioning, indexing).
Các trường hợp sử dụng PostgreSQL trong blockchain (ví dụ: quản lý danh tính, quản lý tài sản, chuỗi cung ứng).
Bài tập:
Tìm hiểu về các thách thức khi sử dụng PostgreSQL cho blockchain.
Nghiên cứu các giải pháp để giải quyết các thách thức này.
Tìm hiểu về các trường hợp sử dụng PostgreSQL trong blockchain.
Ngày 128
Ôn tập và thực hành:
Xem lại các kiến thức đã học trong tuần.
Thực hiện các bài tập tổng hợp để củng cố kiến thức.
Tìm hiểu thêm về các ứng dụng của PostgreSQL trong blockchain.
Ngày 129
Dự án cá nhân:
Bắt đầu làm một dự án cá nhân sử dụng PostgreSQL.
Dự án có thể là một ứng dụng web, một ứng dụng di động, hoặc một công cụ dòng lệnh.
Áp dụng các kiến thức đã học về PostgreSQL để xây dựng dự án.
Tuần 20: Dự án cá nhân và tổng kết
Ngày 130
Thiết kế cơ sở dữ liệu:
Xác định các thực thể (entities) cần thiết cho hệ thống (ví dụ: sản phẩm, danh mục, khách hàng, đơn hàng, chi tiết đơn hàng).
Xác định các thuộc tính (attributes) của mỗi thực thể.
Xác định các mối quan hệ (relationships) giữa các thực thể.
Vẽ sơ đồ ERD (Entity-Relationship Diagram) để mô tả cấu trúc cơ sở dữ liệu.
Ngày 131
Tạo cơ sở dữ liệu và các bảng:
Sử dụng PostgreSQL để tạo cơ sở dữ liệu mới.
Tạo các bảng dựa trên sơ đồ ERD đã thiết kế.
Định nghĩa các ràng buộc (constraints) cho các bảng (ví dụ: khóa chính, khóa ngoại, check constraint).
Ngày 132
Cài đặt backend (ví dụ: sử dụng Python và Flask):
Thiết lập môi trường phát triển.
Cài đặt các thư viện cần thiết (Flask, psycopg2).
Tạo cấu trúc thư mục cho dự án.
Viết các API (Application Programming Interface) để tương tác với cơ sở dữ liệu PostgreSQL.
Ngày 133
Xây dựng chức năng quản lý sản phẩm:
Tạo các API để:
Thêm sản phẩm mới.
Cập nhật thông tin sản phẩm.
Xóa sản phẩm.
Lấy danh sách sản phẩm.
Lấy chi tiết sản phẩm.
Xây dựng các chức năng tìm kiếm và lọc sản phẩm.
Ngày 134
Xây dựng chức năng quản lý danh mục:
Tạo các API để:
Thêm danh mục mới.
Cập nhật thông tin danh mục.
Xóa danh mục.
Lấy danh sách danh mục.
Lấy chi tiết danh mục.
Xây dựng các chức năng phân cấp danh mục (danh mục cha, danh mục con).
Ngày 135
Xây dựng chức năng giỏ hàng:
Tạo các API để:
Thêm sản phẩm vào giỏ hàng.
Cập nhật số lượng sản phẩm trong giỏ hàng.
Xóa sản phẩm khỏi giỏ hàng.
Lấy danh sách sản phẩm trong giỏ hàng.
Lưu trữ thông tin giỏ hàng trong session hoặc database.
Ngày 136
Xây dựng chức năng đặt hàng:
Tạo các API để:
Tạo đơn hàng mới.
Cập nhật trạng thái đơn hàng.
Lấy danh sách đơn hàng của khách hàng.
Lấy chi tiết đơn hàng.
Xây dựng quy trình thanh toán (tích hợp với cổng thanh toán).
Ngày 137
Xây dựng chức năng quản lý khách hàng:
Tạo các API để:
Đăng ký tài khoản khách hàng mới.
Đăng nhập.
Quản lý thông tin cá nhân.
Xem lịch sử đơn hàng.
Ngày 138
Cài đặt frontend (ví dụ: sử dụng HTML, CSS, JavaScript):
Thiết kế giao diện người dùng cho các trang (trang chủ, trang danh mục, trang sản phẩm, trang giỏ hàng, trang thanh toán).
Sử dụng JavaScript để gọi các API backend và hiển thị dữ liệu trên frontend.
Ngày 139
Tích hợp frontend và backend:
Kết nối frontend với backend thông qua các API.
Xây dựng các chức năng tương tác giữa frontend và backend (ví dụ: thêm sản phẩm vào giỏ hàng, đặt hàng).
Ngày 140
Kiểm thử và triển khai:
Kiểm thử toàn bộ hệ thống để đảm bảo các chức năng hoạt động đúng.
Sửa lỗi và cải thiện hiệu suất.
Triển khai hệ thống lên một môi trường production (ví dụ: Heroku, AWS, DigitalOcean).