SQL/Postgres' Inner Workings & Optimizations - The Hidden Beauty
Although SQL and its enterprise variant Postgres are heavily used by many, I have found that due to the sheer abstraction they provide, their inner workings remain a mystery to many. The difference mainly comes from the fact that SQL is a declarative language, unlike the imperative languages that we are used to, like C++ and Python. Instead of telling it what exactly to do, we rather declare what we want, then it figures out how to achieve the same. In this post, I'll go over details of how SQL works and some findings I've found researching into optimizing Postgres queries as a part of my DBMS project at IIT Kanpur. A case can be made that imperative languages themselves are declarative at the hardware level over assembly and system calls. Just like how the sort function in Python or C++ STL abstracts which exact sorting method is used here, SQL's 'order by' keyword also abstracts the same. While there is a lot of truth to that st...