PostgreSQL Advanced

Pengelolaan basis data pada skala menengah hingga besar menuntut kemampuan analisis kueri, optimasi performa, serta pengendalian konkurensi yang matang. Sistem basis data modern tidak hanya berfungsi sebagai penyimpan data, tetapi juga sebagai fondasi pengambilan keputusan bisnis yang bergantung pada kecepatan, konsistensi, dan keandalan akses data. Pendekatan lanjutan terhadap PostgreSQL berfokus pada efisiensi eksekusi, pemanfaatan fitur analitik, serta pemeliharaan berkelanjutan agar sistem tetap stabil pada beban kerja tinggi (PostgreSQL Global Development Group, 2024).

Course ini membekali peserta dengan kemampuan menyusun kueri kompleks, menganalisis pola eksekusi, serta meningkatkan performa melalui strategi optimasi yang tepat. Peserta mempelajari cara membaca rencana eksekusi, memanfaatkan mekanisme analitik baris, serta mengelola struktur penyimpanan agar respons sistem tetap optimal. Pembahasan juga mencakup pengelolaan pencatatan aktivitas, audit, serta strategi pencadangan dan pemulihan untuk menjaga integritas data pada berbagai skenario kegagalan.

Materi lanjutan menitikberatkan pengelolaan replikasi, pemantauan real-time, serta perawatan rutin guna mencegah degradasi performa jangka panjang. Peserta memahami cara mendeteksi hambatan akses data, mengidentifikasi sumber perlambatan, serta menerapkan strategi konkurensi yang aman dan efisien. Dengan fondasi ini, peserta siap mengelola PostgreSQL sebagai sistem basis data produksi yang andal, terukur, serta adaptif terhadap kebutuhan aplikasi modern dan lingkungan cloud-native (EDB, 2023).

OBJECTIVES

1. Meningkatkan kemampuan peserta dalam menulis dan mengoptimalkan query PostgreSQL tingkat lanjut.
2. Membekali peserta dengan teknik-teknik monitoring dan diagnosis untuk mendeteksi dan memperbaiki masalah performa database.
3. Memberikan pemahaman praktis mengenai replikasi data dan proses backup & restore yang aman dan efisien.
4. Menyiapkan peserta untuk melakukan maintenance database secara berkala guna menjaga performa sistem.

AUDIENCE

1. Database Administrator (DBA)
2. Data Engineer
3. Backend Developer
4. System Analyst
5. IT Specialist

PREREQUISITES

1. PostgreSQL Fundamentals

CONTENT

1. Advanced Statements

1.1 Exploring the Select Statement
1.2 Using the Like Clause
1.3 Using Ilike
1.4 Using Distinct
1.5 Using Limit and Offset
1.6 Using Subqueries
1.7 Learning About Joins
1.8 Aggregate Functions
1.9 Learning the Returning clause for Insert
1.10Exploring CTES
1.11 CTE in PostgreSQL Since Version 12

2. Window Functions

2.1 Using Basic Statement Window Functions
2.2 Using the Partition by Function and Window Clause
2.3 Using Advanced Statement Window Functions

3. Query Tuning, Indexes, and Performance Optimization

3.1 Execution of a Statement
3.2 Indexes
3.3 The Explain statement
3.4 Examples of Query Tuning
3.5 Analyze and How to Update Statistics
3.6 Auto-Explain

4. Logging and Auditing

4.1 Introduction to Logging
4.2 Extracting Information from Logs – pgBadger
4.3 Implementing Auditing

5. Backup and Restore

5.1 Introducing Types of Backups and Restores
5.2 Exploring Logical Backups
5.3 Exploring Physical Backups
5.4 Basic Concepts Behind PITR

6. Physical Replication

6.1 Exploring Basic Replication Concepts
6.2 Managing Streaming Replication

7. Logical Replication

7.1 Understanding the Basic Concepts of Logical Replication
7.2 Comparing Logical Replication and Physical Replication
7.3 Exploring a Logical Replication Setup and New Logical Replication Features on PostgreSQL 16

8. Monitoring and Diagnosis

8.1 Cloud-Native Monitoring
8.2 Providing PostgreSQL Information to Monitoring Tools
8.3 Real-Time Viewing using pgAdmin
8.4 Monitoring the PostgreSQL Message Log
8.5 Checking Whether a User is Connected
8.6 Checking Whether a Computer is Connected
8.7 Repeatedly Executing a Query in psql
8.8 Checking which Queries are Running
8.9 Monitoring the Progress of Commands
8.10Checking which Queries are Active or Blocked
8.11 Knowing who is Blocking a Query
8.12 Killing a Specific Session
8.13 Knowing Whether Anybody is Using a Specific Table
8.14 Knowing when a Table was Last Used
8.15 Monitoring I/O Statistics
8.16 Usage of Disk Space by Temporary Data
8.17 Understanding Why Queries Slow Down
8.18 Analyzing the Real-time Performance of Your Queries
8.19 Tracking Important Metrics Over Time

9. Regular Maintenance

9.1 Controlling Automatic Database Maintenance
9.2 Avoiding Auto-Freezing
9.3 Removing Issues that Cause Bloat
9.4 Actions for Heavy Users of Temporary Tables
9.5 Identifying and Fixing Bloated Tables and Indexes
9.6 Monitoring and Tuning a Vacuum
9.7 Maintaining Indexes
9.8 Finding Unused Indexes
9.9 Carefully Removing Unwanted Indexes
9.10 Planning Maintenance

10. Performance and Concurrency

10.1 Finding Slow SQL Statements
10.2 Finding Out What Makes SQL Slow
10.3 Reducing the Number of Rows Returned
10.4 Simplifying Complex SQL Queries
10.5 Speeding Up Queries without Rewriting Them
10.6 Discovering Why a Query is Not Using an Index
10.7 Forcing a Query to Use an Index
10.8 Using Parallel Query
10.9 Using Just-In-Time (JIT) Compilation
10.10 Creating Time-Series Tables Using Partitioning
10.11 Using Optimistic Locking to Avoid Long Lock Waits
10.12 Reporting Performance Problems