Performance Ep.8: Adding 3M Rows & Tuning Indexes With pgHero скачать в хорошем качестве

Performance Ep.8: Adding 3M Rows & Tuning Indexes With pgHero

This video details database performance testing using PgHero and PgAdmin, focusing on how a large dataset of 3 million rows affects read and write behavior. We explore the critical role of data integrity in ensuring accurate performance metrics and discuss effective software testing strategies. Understanding "observability" is key to interpreting these results and optimizing database management for realistic load conditions. In the previous episode, the system was running with a very small dataset — roughly 100 rows in the database — which was enough to validate correctness, but not representative for performance analysis. In this episode, the goal was simple: make the workload realistic and observe how the system behaves at scale. To do that, I added approximately 3 million rows to the database and reran the same read-focused workload. Instead of tuning immediately, the focus here is on observability and interpretation using PGHero and pgAdmin. During the analysis, a seemingly harmless SELECT COUNT(*) query started dominating database activity and significantly influenced how the workload appeared under load. This changed the way performance metrics had to be interpreted and, at one point, made it look like the entire performance plan might be invalid. In this episode, I show: How PGHero surfaces query-level behavior under load How pgAdmin helps trace and validate what’s actually happening inside PostgreSQL Why COUNT(*) queries become problematic at scale How increasing the dataset size can completely change performance conclusions And why, somewhat luckily, we still managed to land with near-the-same results after adjusting the interpretation This is not a tuning episode and not a tutorial. It’s a documented engineering experiment focused on understanding system behavior before making further changes. This video is part of the ongoing API & Database Performance series on IggyCloud, where I document real performance investigations on a production-like system. Resources Codebase and experiment setup: https://github.com/IggyCloud/eShop Performance series resources: https://github.com/IggyCloud/resources #PostgreSQL #PGHero #pgAdmin #Database #Performance #SQL