Mastering PostgreSQL Performance Tuning: Optimizing Speed, Scalability, and Efficiency

"Mastering PostgreSQL Performance Tuning: Optimizing Speed, Scalability, and Efficiency"

Buy $299.00

Course curriculum

1. Before we begin...
2. PostgreSQL log files for performance tuning and optimization
3. Quiz-1 :PostgreSQL log files for performance tuning and optimization
4. Using Logfile Key strategies for performance tuning in PostgreSQL
5. Key strategies for performance tuning in PostgreSQL
6. Key strategies for performance tuning in PostgreSQL
7. Quiz 2 -Key strategies for performance tuning in PostgreSQL
1. Optimizing Queries using pgbadger and native Tools
2. Recording :Optimizing Queries using pgbadger and native Tools
3. Demo Pgbadger
4. Quiz-3 :Optimizing Queries using pgBadger and Native PostgreSQL Tools
5. Optimizing Queries Using pg_stat_statements
6. Quiz 4 : Optimizing Queries Using pg_stat_statements
7. Optimizing queries using pg_stat_statements-1
8. Quiz 5 : Optimizing queries using pg_stat_statements-1
9. Optimizing Queries Using pg_stat_statements-2
10. Quiz 6 : Optimizing queries using pg_stat_statements-2
11. Pg-collector in PostgreSQL 16 for collecting full info about Pg cluster
12. Recording Demo Pg-collector
13. Quiz 7 : Pg-collector in PostgreSQL 16 for collecting full info about Pg cluster
14. Enabling Troubleshooting Guides and auto_Explain Extension
15. Recording auto_explain Extension
16. Quiz-8: auto_explain
17. Identifying and Managing Dead Tuples in PostgreSQL
18. Quiz 9: Identifying and Managing Dead Tuples in PostgreSQL
19. Identifying Tables for Autovacuum in PostgreSQL
20. Quiz 10 : Identifying Tables for Autovacuum in PostgreSQL
21. Quiz 11: Vacuum and Analyze in PostgreSQL
22. "Comprehending PostgreSQL Statistics"
23. Quiz 12 : Comprehending PostgreSQL Statistics
1. PostgreSQL Performance Optimization Techniques
2. Recording : PostgreSQL Performance Optimization Techniques
3. Quiz 1 : PostgreSQL Performance Optimization Techniques
4. Optimizing PostgreSQL for High-Performance Memory parameters
5. Quiz 2: Optimizing PostgreSQL for High-Performance Memory parameters
6. Recording :Optimizing PostgreSQL for High-Performance Memory parameters
7. What does the shared_buffers parameter do in PostgreSQL?
8. Quiz 3: What does the shared_buffers parameter do in PostgreSQL
9. Demo : Shared buffers and impact on performance tuning
10. Quiz 4: Shared buffers and impact on performance tuning
11. Demo: Understanding and Configuring shared_buffers in PostgreSQL"
12. What does the temp_buffers parameter do in PostgreSQL
13. Quiz 5: What does the temp_buffers parameter do in PostgreSQL
14. Recording : What does the temp_buffers parameter do in PostgreSQL
15. Demo temp_buffers
16. What is the role of the effective_cache_size parameter in PostgreSQL
17. Quiz 6: What is the role of the effective_cache_size parameter in PostgreSQL
18. Recording :What is the role of the effective_cache_size parameter in PostgreSQL
19. Demo : Understanding effective_cache_size Utilization in Complex Query Execution
20. Understanding the work_mem in postgresql
21. Quiz 7: What is the role of the work_mem parameter in PostgreSQL
22. Demo :Understanding the work_mem in PostgreSQL
23. Recording :Understanding the work_mem in postgresql
24. Understanding maintenance_work_mem in PostgreSQL Optimizing Performance for Indexing and Vacuum Operations
25. Quiz 8: Understanding maintenance_work_mem in PostgreSQL for Optimizing Performance in Indexing and Vacuum Operations
26. Recording :Understanding maintenance_work_mem in PostgreSQL
27. Quiz 9: maintenance_work_mem in PostgreSQL
28. Demo : maintenance_work_mem
29. "Understanding random_page_cost in PostgreSQL: How It Impacts Query Performance and Optimization"
30. Quiz 10: "Understanding random_page_cost in PostgreSQL: How It Impacts Query Performance and Optimization"
31. Recording :random_page_cost in PostgreSQL: How It Impacts Query Performance and Optimization"
32. Quiz 11 :Random_page_cost in PostgreSQL
33. Demo :random_page_cost in PostgreSQL
34. Quiz 12 :random_page_cost in PostgreSQL
35. What is seq_page_cost in postgresql
36. Recording :What is seq_page_cost in PostgreSQL
37. Quiz 13 :What is seq_page_cost in PostgreSQL
38. "Demo: Exploring seq_page_cost in PostgreSQL and Its Role in Query Optimization"
39. Managing Idle Transactions in PostgreSQL Understanding idle_in_transaction_session_timeout and Its Impact on Performance
40. Quiz 14 :Managing Idle Transactions in PostgreSQL Understanding idle_in_transaction_session_timeout and Its Impact on Performance
41. Recording :Managing Idle Transactions in PostgreSQL Understanding idle_in_transaction_session_timeout
42. Demo: idle_in_transaction_session_timeout
43. Quiz 15 : idle_in_transaction_session_timeout
44. Demo: statement_timeout in PostgreSQL 16
45. Quiz 16 -: statement_timeout in PostgreSQL
46. What is search_path in postgresql
47. Recording :What is search_path in postgresql
48. Quiz 17 -: search_path parameter
49. Table Access Approaches in PostgreSQL
50. Quiz 18 :- Table Access Approaches in PostgreSQL
51. What is max_connections in PostgreSQL in postgresql
52. Recording :What is max_connections in PostgreSQL in PostgreSQL
53. Quiz 19 :- What is max_connections in PostgreSQL in postgresql
54. Quiz 20 :- Why We Must Be on the Latest Version of PostgreSQL (pg17)
55. Recording : index scan and index-only scan
56. Demo : Index scan and index only scan
57. Quiz 21 :- index scan and index-only scan
58. enable_seqscan in postgresql
59. Recording :enable_seqscan in postgresql
60. Quiz 22: - enable_seqscan in postgresql
61. Demo : enable_seqscan in postgresql
62. How Parallel Query Execution Works -max_parallel_workers ?
63. Quiz 23:- How Parallel Query Execution Works -max_parallel_workers
64. Recording : How Parallel Query Execution Works -max_parallel_workers
65. Demo : max_parallel_workers in PostgreSQL
66. Quiz 24 -: max_parallel_workers in PostgreSQL
1. Create separate reader and writer endpoints
2. Quiz 1:- Create separate reader and writer endpoints
1. Use proxy / Connection pooler wherever required
2. Quiz 1:- Use proxy / Connection pooler wherever required
1. Partitioning in PostgreSQL
2. Recording : Partitioning in PostgreSQL
3. Range Partitioning in PostgreSQL
4. Recording : Range partitioning
5. List Partitioning in PostgreSQL
6. List partitioning
7. Hash Partitioning in PostgreSQL
8. Hash partitioning
9. Demo : List Partitioning Demo
10. range partitioning Demo
11. Demo: Hash partition
12. Recording Demo Hash partition
13. Recording List Partition demo
14. Recording : range partition
15. Partition pruning in PostgreSQL
16. Quiz 1:- Partitioning in PostgreSQL
17. Quiz 2:- Range Partitioning in PostgreSQL
18. Quiz 3:- List Partitioning in PostgreSQL
19. Quiz 4:- hash partition in PostgreSQL
20. Quiz 5:- Partition pruning in PostgreSQL
1. "An Introduction to pg_hint_plan in PostgreSQL"
2. Recording : "An Introduction to pg_hint_plan in PostgreSQL"
3. demo 1: on Automating the Setup of pg_hint_plan Extension in PostgreSQL
4. Demo 2: Exploring the pg_hint_plan Extension in PostgreSQL"
5. Demo 3 : Utilizing the pg_hint_plan Extension for Query Optimization in PostgreSQL"
6. Demo 4: Utilizing the pg_hint_plan Extension for Query Optimization in PostgreSQL
7. Quiz 1:- An Introduction to pg_hint_plan in PostgreSQL
1. What is Parallel Query in PostgreSQL ?
2. Quiz :- What is Parallel Query in PostgreSQL
1. What are Indexes in PostgreSQL
2. Recording : What are Indexes in PostgreSQL
3. Optimizing read operations in PostgreSQL
4. Recording : Optimizing read operations in PostgreSQL
5. Mastering Multicolumn Indexes: Enhancing Query Performance with Composite Keys
6. Recording :Mastering Multicolumn Indexes Enhancing Query Performance with Composite Keys
7. Demo : Multicolumn Indexes
8. Optimizing Query Performance with Indexes and ORDER BY in PostgreSQL
9. Recording: Optimizing Query Performance with Indexes and ORDER BY in PostgreSQL
10. Demo : Optimizing Query Performance with Indexes and ORDER BY in PostgreSQL
11. Combining Multiple Indexes: Leveraging Index Merging for Complex Queries
12. Demo : Combining Multiple Indexes: Leveraging Index Merging for Complex Queries
13. Ensuring Data Integrity with Unique Indexes in PostgreSQL
14. Recording : Ensuring Data Integrity with Unique Indexes in PostgreSQL
15. Indexes on Expressions: Creating Efficient Query Optimizations for Complex Calculations
16. Recording :Indexes on Expressions: Creating Efficient Query Optimizations for Complex Calculations
17. Demo : Indexes on Expressions: Creating Efficient Query Optimizations for Complex Calculations
18. Maximizing Query Efficiency with Partial Indexes in PostgreSQL
19. Recording :Maximizing Query Efficiency with Partial Indexes in PostgreSQL
20. Demo : Maximizing Query Efficiency with Partial Indexes in PostgreSQL
21. Unlocking Index-Only Scans for Faster Query Execution
22. Recording Unlocking Index-Only Scans for Faster Query Execution
23. Demo : Unlocking Index-Only Scans for Faster Query Execution
24. Unlocking Covering Indexes for Faster Query Execution in PostgreSQL
25. Recording Unlocking Covering Indexes for Faster Query Execution in PostgreSQL
26. Demo :Unlocking Covering Indexes for Faster Query Execution in PostgreSQL
27. Understanding Operator Classes and Families in PostgreSQL: Customizing Index Behavior
28. Recording :Understanding Operator Classes and Families in PostgreSQL: Customizing Index Behavior
29. Using Indexes with Collations in PostgreSQL: Optimizing Text Search and Comparison
30. Recording: Using Indexes with Collations in PostgreSQL: Optimizing Text Search and Comparison
31. Demo : Using Indexes with Collations in PostgreSQL 16 - Optimizing Text Search and Comparison
32. Examining Index Usage: Analyzing and Monitoring Index Performance in PostgreSQL
33. Recording Examining Index Usage Analyzing and Monitoring Index Performance in PostgreSQL
34. Exploring PostgreSQL Index Types: Choosing the Right Index for Your Data
35. Recording Exploring PostgreSQL Index Types: Choosing the Right Index for Your Data
36. B-Tree Indexes: The Default Index Type for General Purpose Querying
37. Recording B-Tree Indexes The Default Index Type for General Purpose Querying
38. Demo :B-Tree Indexes: The Default Index Type for General Purpose Querying
39. What is an Index Scan Backward in PostgreSQL
40. Recording : What is an Index Scan Backward in PostgreSQL
41. Quiz 1:- What are Indexes in PostgreSQL
42. Quiz 2:- Optimizing Read Operations in PostgreSQL
43. Quiz 3:- Mastering Multicolumn Indexes: Enhancing Query Performance with Composite Keys
44. Quiz 4:-Optimizing Query Performance with Indexes and ORDER BY in PostgreSQL
45. Quiz 5:- Combining Multiple Indexes: Leveraging Index Merging for Complex Queries
46. Quiz 6:- Ensuring Data Integrity with Unique Indexes in PostgreSQL
47. Quiz 7:- Indexes on Expressions: Creating Efficient Query Optimizations for Complex Calculations
48. Quiz 8:- Maximizing Query Efficiency with Partial Indexes in PostgreSQL
49. Quiz 9:- Unlocking Index-Only Scans for Faster Query Execution
50. Quiz 10:- Unlocking Covering Indexes for Faster Query Execution in PostgreSQL
51. Quiz 11:- Understanding Operator Classes and Families in PostgreSQL: Customizing Index Behavior
52. Quiz 12:- Using Indexes with Collations in PostgreSQL: Optimizing Text Search and Comparison
53. Quiz 13:- Examining Index Usage: Analyzing and Monitoring Index Performance in PostgreSQL
54. Quiz 14:- Exploring PostgreSQL Index Types: Choosing the Right Index for Your Data
55. Quiz 15:- B-Tree Indexes: The Default Index Type for General Purpose Querying
56. Quiz 16:- GiST Indexes: Advanced Indexing for Complex Data Types and Queries
57. Quiz 17:- What is an Index Scan Backward in PostgreSQL
1. Topics
2. 1. Introduction to EXPLAIN: Understanding PostgreSQL Query Plans
3. Recording Introduction to EXPLAIN Understanding PostgreSQL Query Plans
4. Demo: Introduction to EXPLAIN: Understanding PostgreSQL Query Plans in PostgreSQL 16
5. Deep Dive into EXPLAIN ANALYZE Measuring Query Execution Time
6. Recording Deep Dive into EXPLAIN ANALYZE Measuring Query Execution Time
7. Demo : Deep Dive into EXPLAIN ANALYZE for Measuring Query Execution Time in PostgreSQL 16
8. Demo : Optimizing Joins with EXPLAIN and EXPLAIN ANALYZE (Single Table)
9. Demo : Proc slowness
10. Quiz 1:- Introduction to EXPLAIN: Understanding PostgreSQL Query Plans
11. Quiz 2:- Deep Dive into EXPLAIN ANALYZE: Measuring Query Execution
12. Quiz 3:- Using Indexes Effectively with EXPLAIN in PostgreSQL
13. Quiz 4:- Advanced Query Optimization – Interpreting Complex Execution Plans in PostgreSQL
1. Migrate to latest version of PostgreSQL cluster (16.*) or 17.*
2. Quiz 1:- Migrate to latest version of PostgreSQL cluster 17.*
1. Wait events
2. Quiz 1:- lock wait events
3. Topics
4. Essential Concepts for Aurora PostgreSQL Tuning
5. Common Aurora PostgreSQL Wait Events and Their Causes
6. ClientClientRead Event-Correct
7. ClientClientWrite Wait event
8. CPU wait event
9. IO:BufFileRead and IO:BufFileWrite
10. IODataFileRead Event ( Wait events )
11. IO:XactSync Wait event
12. IPC:DamRecordTxAck
13. Lock:advisory
14. Lock:extend wait event
15. Lock:Relation Wait Event Overview
16. Lock:transactionid Wait Event Overview
17. Lock:tuple
18. LWLock:buffer_content (BufferContent)
19. LWLock:buffer_mapping Wait Event Overview
20. LWLock:BufferIO (IPC:BufferIO)
1. Topics
2. Understanding Different Types of Joins in PostgreSQL Inner, Left, Right, and Full
3. Optimizing SQL Queries with Joins in PostgreSQL: Tips and Best Practices
4. Using JOIN with Subqueries and CTEs in PostgreSQL
5. Joining Multiple Tables in PostgreSQL: Challenges and Solutions
6. Demo cross join
7. Null Joins in PostgreSQL
1. Postgresql query best practices
2. best practice of selecting only necessary columns in PostgreSQL
1. Aurora PostgreSQL Query Plan Management
2. Key Concepts of Aurora PostgreSQL Query Plan Management
3. Best Practices for Aurora PostgreSQL Query Plan Management
4. Overview of Aurora PostgreSQL Query Plan Management
5. Capturing Aurora PostgreSQL Execution Plan
6. Examining Aurora PostgreSQL query plans in the dba_plan
7. Deleting Aurora PostgreSQL Query Plans
1. Should You Use LOBs in Aurora PostgreSQL
2. manual Vcauum tall tables in a schema
3. reindex all tables across all schemas
4. Check Statistics and Analyze Tables
5. script to check the size of all memory-related parameters in Amazon Aurora
6. Overview of Aurora Optimized Reads
7. pg_info_collector
8. Choosing the Right AWS Aurora PostgreSQL Instance for Oracle Migration
9. Oracle to Amazon Aurora PostgreSQL Cost Savings & Resource Allocation Formula
10. What is ReadySet
11. Demo: readyset
12. Migrating from Oracle to PostgreSQL may require replicating Oracle hints
13. Pgbadger with AI
14. pgbadger_test.sh
15. auto_explain in Aurora
16. Step-by-Step Benchmarking Using pgbench on 2 Aurora Instances (r6.large vs r8.large)
17. PostgreSQL parameters and their respective values/settings for logging configuration in Amazon RDS and Aurora PostgreSQL:
18. onfiguring PostgreSQL logs to be published to CloudWatch Logs for monitoring, alerting, and analysis.
19. Accessing PostgreSQL Log Files via AWS Management Console
20. get_pg_log_files.sh ( Aurora to Ec2 instance )
21. Optimizing Aurora PostgreSQL Insert Operations for Better Performance

About this course

$299.00
258 lessons
9 hours of video content

Discover your potential, starting today

Enroll today

Mastering PostgreSQL Performance Tuning: Optimizing Speed, Scalability, and Efficiency

Course curriculum

Section 1 : PostgreSQL Tuning straight to the point

Section 2 : Performance Tunning Support Tools

Section 3: Tuning parameters for Optimizing PostgreSQL for High-Performance

Create separate reader and writer endpoints

Use proxy / Connection pooler wherever required

Section 4: Partitioning in PostgreSQL

Section 5: Understanding pg_hint_plan"

Section 6 : Parallel Query in PostgreSQL

Section 7 - Indexes and performance tuning

Section 8 : Query Explain Plan

Migrate to latest version of PostgreSQL cluster (16.) or 17.

Wait Events in postgresql

Section 10: Joins in postgresql

Additional Topics

Section 11:- Optimizing and managing query execution plans in Aurora PostgreSQL

Best Practices Aurora

About this course

Discover your potential, starting today

Section 1 : PostgreSQL Tuning straight to the point

Section 2 : Performance Tunning Support Tools

Section 3: Tuning parameters for Optimizing PostgreSQL for High-Performance

Create separate reader and writer endpoints

Use proxy / Connection pooler wherever required

Section 4: Partitioning in PostgreSQL

Section 5: Understanding pg_hint_plan"

Section 6 : Parallel Query in PostgreSQL

Section 7 - Indexes and performance tuning

Section 8 : Query Explain Plan

Migrate to latest version of PostgreSQL cluster (16.*) or 17.*

Wait Events in postgresql

Section 10: Joins in postgresql

Additional Topics

Section 11:- Optimizing and managing query execution plans in Aurora PostgreSQL

Best Practices Aurora

About this course

Migrate to latest version of PostgreSQL cluster (16.) or 17.