PostgreSQL Advanced

Dalam pengembangan aplikasi berbasis database, banyak tantangan yang sering dihadapi oleh para profesional, khususnya ketika bekerja dengan PostgreSQL di tingkat lanjutan. Beberapa masalah yang umum muncul antara lain performa query yang lambat, kesulitan dalam melakukan replikasi data, masalah dalam menjaga konsistensi data, hingga kompleksitas dalam proses backup dan restore. Menurut penelitian dari Percona (2023), sekitar 40% pengembang database melaporkan bahwa masalah performa query dan indeks merupakan hambatan utama dalam mencapai skalabilitas aplikasi mereka. Selain itu, banyak organisasi yang mengalami kendala dalam mengimplementasikan monitoring dan diagnosis yang efektif pada sistem PostgreSQL mereka, sehingga sulit untuk mendeteksi dan memperbaiki masalah secara proaktif.

Training PostgreSQL Advanced ini dirancang khusus untuk menjawab berbagai tantangan tersebut. Dalam sesi ini, peserta akan mempelajari teknik-teknik lanjutan dalam penggunaan statement SQL, termasuk cara memanfaatkan Common Table Expressions (CTE), Window Functions, dan teknik Query Tuning untuk meningkatkan performa query. Selain itu, peserta juga akan dibekali kemampuan dalam mengelola logging dan auditing, memastikan keamanan dan pelacakan perubahan data. Untuk kebutuhan maintenance dan optimasi, training ini juga mencakup topik-topik seperti backup & restore, replikasi fisik dan logis, serta strategi monitoring dan diagnosis menggunakan alat seperti pgBadger dan pgAdmin.

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