Over the past a few years, I haven't worked as a data modeler, and as a result, I felt my skills in data modeling had become quite rusty. To refresh my knowledge and stay up to date, I recently purchased a book on data modeling.
The book I picked up is "알기 쉬운 실전 데이터 모델링". It is a relatively short book, so it doesn't cover data modeling in detail. I was able to read through it in just 4 days. However, it provides practical, actionable insights into effective data modeling techniques.
If you're a developer who doesn't have much time to dive deep into data modeling but still wants a comprehensive overview, I highly recommend this book. It concisely explains key concepts and offers good sample data models.
Here are some of the core topics covered in the book:
- The fundamentals of data modeling
- Data standardization
- Designing efficient data structures
- Data warehouse modeling
In addition to brushing up on my data modeling, I also wanted to enhance my SQL tuning skills. For that, I turned to "PostgreSQL 튜닝 기술(상) and PostgreSQL 튜닝 기술(하)". These two books are dedicated to SQL performance tuning in PostgreSQL.
These two books are packed with practical examples of optimized queries, which make them invaluable resources for developers looking to improve their SQL tuning expertise. The author demonstrates various query optimizations with real-world scenarios, which help readers understand both the why and how behind query performance improvements.
Key topics addressed include:
- Understanding the PostgreSQL optimizer
- Index Design strategies
- Join methods and execution plans
- Common mistakes developers make
- SQL optimization techniques such as query transformation and query rewrite
- Table partitioning strategies
- Parallel processing in PostgreSQL
- Optimizing user-defined-functions
- Table design and its impact on performance
- Installing PostgreSQL 16, dbreaver, and pg_hint_plan.
Final Comments
By reading these books, you won't just learn how to design efficient data models - you will also gain the expertise to optimize SQL queries for maximum performance in PostgreSQL. Whether you are a developer or database administrator, these books provide practical, real-world knowledge that will significantly enhance your database skills.
If you are looking to improve your database modeling and PostgreSQL tuning abilities, these books are a must-read.
Added on MAR 23, 2025
Here is a summary of "PostgreSQL Tuning Techniques"
1. Introduction
- The importance of SQL performance tuning.
- A practical case study illustrating the benefits of SQL tuning.
- Instructions for setting up testing environments and table.
2. Understanding the Optimizer
- Brief explanation of PostgreSQL's cost-based optimizer, including logical and physical optimization process.
- Importance of statistical information and its limitations
- Common execution plans and their optimal use cases.
- Techniques for query rewrting to improve execution plans.
3. Index Fundamentals
- Structure and types of indexes (Index Scan, Bitmap Index Scan, Index Only Scan).
- Guidelines for designing efficient indexes(multi-column, covering, partial, function-based indexes).
4. Joins
- Analysis of different join methods (Nested Loop, Hash, Sort-Merge, CROSS joins) and the impact of join order on performance.
5. Common Mistakes in SQL Development
- Highlights frequent SQL writing mistakes like inappropriate use of COUNT, COALESCE, and built-in functions that degrade performance.
- Importance of implicit data type conversion.
6. SQL Tuning Techniques
- Practical SQL rewriting strategies (query transformation, subquery optimization, sorting minimization, using UNION ALL and then some).
- Advanced techniques such as using PostgreSQL-specific hints (pg_hint_plan), managing join orders, optimizing pagination, and leveraging analytics functions effectively.
7. Table Partitioning and Parallel Processing
- Effective use of paritioning (range, list, hash, and composite partitions) to enhance query efficiency.
- Parallel processing techniques to leverage multiple CPU cores.
8. User-Defined Function Optimization
- Recommendations for efficient function creation and management, highlighting differences betwen PL/pgSQL and SQL functions.
- Best practices to avoid performance pitfalls in function design, including dynamic SQL usage.
9. Table Design and Performance
- Insights into designing optimal database tables, considering data types and frequently accessed columns to minimize I/O operations and enhance performance.
10 Appendices
- Instructions for installing PostgreSQL 16, DBeaver, and pg_hint_plan.
The author emphasizes repeated hands-on practice, suggesting readers run provided queries, observe execution plans, and experiment with variations to deepen understanding and build intuition for SQL tuning.
The author analogizes PostgreSQL's optimizer operations with Daniel Kahneman's "Thinking, Fast and Slow," noting:
- Simple queries are optimized quickly with minial processing ("fast thinking").
- Complex queries require detailed statistical analysis and multiple execution plans evaluation ("slow thinking").
- Recognizing optimizer limitations allows developers to intervene effectively with better tuning decisions.
