Interns delve into optimizing, writing advanced queries, and ensuring the safety and performance of the PostgreSQL database management system.
Required knowledge to grasp
Fundamental SQL knowledge: CRUD statements, JOINs, GROUP BY, HAVING, and Subqueries.
RDBMS concepts: Data integrity (Primary Key, Foreign Key, Constraints), ACID properties of Transactions.
Execution sequence
Initialize Database Server: Go to https://dev.mdcgroup.vn to request a PostgreSQL environment.
Database Design & Large Data Initialization: Design a normalized table structure (Schema) (3NF). Use scripts to generate a large amount of simulated data (millions of records) for optimization practice.
Query Optimization with Index: Execute EXPLAIN ANALYZE statements to read the SQL statement execution tree. Proceed to create Indexes (B-Tree, Hash, GIN) to optimize slow search queries.
In-Database Programming: Write Stored Procedures, Functions to handle complex logic, and create Triggers to automatically update data (e.g., automatically log when the main table changes).
Advanced Queries (Window Functions): Practice writing complex data analysis statements using ROW_NUMBER(), RANK(), LEAD(), LAG().
Backup and Maintenance: Perform data backup using pg_dump, restore using pg_restore. Learn the VACUUM command to clean up excess database space.
Result acceptance
Multiple-choice test: Complete the advanced PostgreSQL multiple-choice test on the internal web.
Questions & Proposals: Propose solutions for data concurrency control / locking when multiple users modify the same record.
Report results: Submit an SQL script file (.sql) containing the entire table structure, functions, triggers, and a comparison of speeds before/after indexing.
Weekend discussion: Present how to interpret the results of the EXPLAIN command during the weekly meeting.