Backend Engineering (Intermediate)

Currently Playing: SELECT COUNT (*) can impact your Backend Application Performance, here is why

aggregating large entries in the database (to perform a count for example) is a lot of work. The database has to sort through a large number of records whether in an index or in the table itself. Doing this too often can impact the performance of both your database and your application, let us discuss why count can be slow and an alternative if you want an estimate of the count. 0:00 intro 0:30 SELECT COUNT(G) //index scan because we have to fetch g and check if its null select count(g) from grades where id between 1000 and 4000; 2:50 SELECT COUNT(*) //index only scan because we don’t have to jump back to heap select count(*) from grades where id between 1000 and 4000; 4:00 SELECT COUNT(*) WITH UPDATE //do an update update grades set g = 10 where id between 1000 and 4000; //index only scan but with heap fetches .. (slower) select count(*) from grades where id between 1000 and 4000; 7:00 COUNT gets slower more rows Estimate count explain (format json) select * from grades; analze grades 8:00 Return Count estimate https://www.cybertec-postgresql.com/en/postgresql-count-made-fast/ 🎙️Listen to the Backend Engineering Podcast https://husseinnasser.com/podcast 🏭 Backend Engineering Videos https://backend.husseinnasser.com 💾 Database Engineering Videos https://www.youtube.com/playlist?list=PLQnljOFTspQXjD0HOzN7P2tgzu7scWpl2 🏰 Load Balancing and Proxies Videos https://www.youtube.com/playlist?list=PLQnljOFTspQVMeBmWI2AhxULWEeo7AaMC 🏛️ Software Archtiecture Videos https://www.youtube.com/playlist?list=PLQnljOFTspQXNP6mQchJVP3S-3oKGEuw9 📩 Messaging Systems https://www.youtube.com/playlist?list=PLQnljOFTspQVcumYRWE2w9kVxxIXy_AMo Become a Member https://www.youtube.com/channel/UC_ML5xP23TOWKUcc-oAE_Eg/join Support me on PayPal https://bit.ly/33ENps4 Join our Thriving Backend Community on Discord https://discord.com/invite/CsFbFce Stay Awesome, Hussein