<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Akshay Parab on Medium]]></title>
        <description><![CDATA[Stories by Akshay Parab on Medium]]></description>
        <link>https://medium.com/@parab.akshay57?source=rss-45b62464ad20------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*yWoW4U6UTEa5X6Tzx_gTew.jpeg</url>
            <title>Stories by Akshay Parab on Medium</title>
            <link>https://medium.com/@parab.akshay57?source=rss-45b62464ad20------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 05:37:49 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@parab.akshay57/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Journey of an SQL Query]]></title>
            <link>https://medium.com/@parab.akshay57/journey-of-an-sql-query-856d66edfc70?source=rss-45b62464ad20------2</link>
            <guid isPermaLink="false">https://medium.com/p/856d66edfc70</guid>
            <category><![CDATA[working-of-sql]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[sql-optimization]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[remote-sql-server]]></category>
            <dc:creator><![CDATA[Akshay Parab]]></dc:creator>
            <pubDate>Thu, 27 Jul 2023 21:06:54 GMT</pubDate>
            <atom:updated>2023-08-01T15:19:19.294Z</atom:updated>
            <content:encoded><![CDATA[<h4>An intermediate-level guide that delves into the workings behind an SQL query</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*w-bmGrNktFFGwUJP" /><figcaption>Photo by <a href="https://unsplash.com/@thisisengineering?utm_source=medium&amp;utm_medium=referral">ThisisEngineering RAEng</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>I always assumed that firing an SQL query and receiving the corresponding result must be a straightforward task due to the sheer speed of the database management systems. I’ve been living with that assumption for the past 5 years while working with relational databases until recently when I started exploring the fundamentals behind this activity. Now I can truly assert that understanding the journey of an SQL query from an application to the remote database server and back holds paramount importance for anyone dealing with databases. By delving into this process, we can gain valuable insights and opportunities to enhance various aspects of our work since most systems, if not all, revolve around data. For example, there are certain advantages of following the journey of an SQL query that come to my mind:</p><ul><li><strong>Optimizing Performance</strong>: Helps us identify potential bottlenecks and optimize query execution for better performance.</li><li><strong>Effective Debugging and Troubleshooting</strong>: As developers inevitably encounter issues with SQL queries, such as unexpected results or errors, understanding the query’s journey becomes invaluable for troubleshooting. By pinpointing the specific stage or component at which issues occur, developers can expedite the resolution process and ensure smoother functioning.</li><li><strong>Enhancing Application Performance</strong>: Recognizing that the journey of an SQL query involves not only the database server but also the application code, developers can pinpoint areas for improvement and mitigate pain points within the application.</li><li><strong>Fostering Communication and Collaboration</strong>: It enhances communication quality between various stakeholders, including Application Developers, Database Administrators, System Engineers, and SRE Engineers. This shared understanding enables more effective collaboration when diagnosing and resolving issues.</li></ul><p>Seems pretty important right? So, let’s jump right into the journey of SQL queries together!</p><p>For simplicity, let’s assume that we have a Node.js application that accepts API requests from users to implement specific business logic by processing data stored in a PostgreSQL database hosted on a remote server.</p><h4><strong>Stage 0: Application Initialization</strong></h4><p>When the Node.js application starts up, it loads up modules and configurations, resolves dependencies, and initializes the event loop and other necessary resources required for its working. Since our application seeks to communicate with a PostgreSQL database server, our application makes use of a database client library, in our case PostgreSQL client library “<a href="https://node-postgres.com/">node-postgres</a>” also known as “<a href="https://www.npmjs.com/package/pg">pg</a>” for Node.js. The database client libraries help the application execute queries on the database server. They act as an intermediary between the application and the database, abstracting away the low-level details of network communication and query execution, making it easier for developers to interact with the database. One of the most important functions of such libraries is to perform “Connection Management“.</p><p>What are these “connections” you ask? Well, connections serve as a vital link between two systems, enabling the seamless exchange of information as a stream of zeroes and ones, i.e. to send and receive bytes. Since our application and database server are running on different hosts, they will communicate over <a href="https://en.wikipedia.org/wiki/Transmission_Control_Protocol">TCP</a> [1].</p><p>The way TCP establishes a connection (standard TCP three-way handshake) is as follows:</p><p><strong>Step 1 (SYN)</strong>: The application sends a TCP packet with the SYN flag set to the database server to request a connection.</p><p><strong>Step 2 (SYN-ACK)</strong>: The database server responds with a TCP packet that has both the SYN and ACK flags set, acknowledging the connection request.</p><p><strong>Step 3 (ACK)</strong>: The application sends an ACK packet to the server, finalizing the TCP handshake and establishing the connection.</p><p>You must be wondering, wait why is this turning into a Networks class?</p><p>Here’s Why and it’s a big one!</p><p>So establishing a TCP connection is a resource-intensive process and can be a time-consuming activity since passwords are negotiated during this period, <a href="https://en.wikipedia.org/wiki/Transport_Layer_Security">TLS</a>/<a href="https://en.wikipedia.org/wiki/Transport_Layer_Security#SSL_1.0,_2.0,_and_3.0">SSL</a> may be established, and configuration information is shared between the client and server [2]. Hence, to increase the efficiency of connection usage, most applications along with database client libraries make use of a technique known as “Connection Pooling”, which is used to manage and reuse a set of established database connections to improve the efficiency and performance of database interactions. In the context of Node.js applications connecting to a remote database server, establishing a connection pool can significantly reduce the overhead of creating and tearing down connections for each database request.</p><p>In a nutshell, here is how Connection Pooling works:</p><p><strong>Connection Pool Creation:</strong> When the Node.js application starts, it creates a pool of database connections with the help of the database client library.</p><p><strong>Connection Reuse</strong>: Whenever a database query needs to be executed, the application requests a connection from the pool.</p><p><strong>Query Execution</strong>: The query is executed on the assigned connection, and the results are returned to the application.</p><p><strong>Connection Release</strong>: After the query is completed, the connection is released back to the pool, where it becomes available for reuse.</p><p>Now at this stage, assume that our application is ready to accept API requests as it has initialized and equipped itself with a collection of pre-established database connections that it can reuse for executing database queries without having to establish a new connection each time a query is executed.</p><h4><strong>Stage 1: The application prepares &amp; sends a query to the database server</strong></h4><p>The Node.js application prepares the database query in SQL format using the database client library. The application sends the SQL query as a JavaScript string to the db client library, indicating the database operation to be performed (e.g., SELECT, INSERT, UPDATE, DELETE), the target table, and any conditions or parameters.</p><p>The application requests a connection from the connection pool; if an idle connection is available in the pool, the connection pool provides the application with a reference to this connection. Since the connection has already been established as part of setting up the connection pool, there is no need for a new TCP handshake for reused connections.</p><blockquote><strong>Note</strong>: Programs usually work with data in multiple representations. In memory, data is stored in optimized data structures like objects, lists, arrays, hash tables, and trees, designed for efficient access and manipulation by the CPU using pointers. When data needs to be stored in a file or sent over the network, it is encoded as a self-contained sequence of bytes (e.g., a JSON document) since pointers are not meaningful to other processes. To switch between these representations, encoding/serialization/marshalling translates data from the in-memory format to a byte sequence while decoding/parsing/deserialization/unmarshalling does the reverse [3].</blockquote><p>Before sending the SQL query over the network, the database client library serializes the query into a format that the PostgreSQL server can understand. The serialization process usually involves converting the SQL query from a JavaScript string to a binary format or a specific text-based format (depending on the chosen protocol and configuration) [4].</p><p>When the application sends the query to the database server, the serialized data (SQL query) is encapsulated into packets at the network layer. The OS forwards these packets to the destination (database server) based on the IP address and port information associated with the established connection.</p><blockquote><strong>Note</strong>: Communication between the client and the SQL server typically takes place over a specialized network protocol specifically designed for efficient database communication. For instance, PostgreSQL employs a message-based protocol known as Frontend/Backend Protocol for seamless communication between applications (clients) and backends (database servers). This protocol is supported over both TCP/IP and Unix-domain sockets, providing flexibility for different networking environments [5]. The message-based protocol is optimized to transmit SQL queries and data effectively, ensuring smooth and fast interactions between the client application and the database server. Whether it’s transmitting SQL queries to the server or fetching query results back to the client, these protocols are engineered to streamline the process and enhance overall performance.</blockquote><p>Take a break here, you deserve it! Eat your favorite snack, drink up that coffee, and think about what has happened so far. At this stage, our application has sent a query to the database server over the pre-established database connection and is awaiting a response.</p><h4><strong>Stage 2: The database server receives the query, executes it, and responds with the result to the application</strong></h4><p>The database server’s network layer notifies the application’s network layer of successful packet reception and proceeds to decapsulate them, extracting relevant data from packet headers and passing the payload to the database server for further processing.</p><p><strong>Query Parser:</strong></p><p>After receiving the serialized data (SQL query), PostgreSQL’s query parser takes charge of deserializing the received data into a format that the database server can comprehend. This essential component converts the binary or text-based representation of the SQL query into an internal format that the server can readily process. The query parser meticulously processes the decoded SQL query, inspecting it for syntax mistakes and preparing it for subsequent processing by constructing a query tree, a structured representation of the query’s components [6].</p><p><strong>Query Planner/Optimizer:</strong></p><p>The planner/optimizer plays a crucial role in the query execution process by taking the query tree and transforming it into an optimized query plan, which serves as input for the executor. To achieve this, the planner explores various possibilities and creates all potential execution paths leading to the same result. For instance, when dealing with a relation that has an index, two paths can be considered: a simple sequential scan or utilizing the index for more efficient access. Each path is evaluated based on its estimated cost of execution. During the evaluation, the planner estimates the cost of each potential path, considering factors such as data distribution, access methods, join strategies, and other relevant statistics. It then selects the least costly path, ensuring the execution plan is optimal for performance. The chosen path is further expanded into a complete query plan, detailing the step-by-step execution strategy. This comprehensive plan is then handed over to the executor, which efficiently carries out the query’s execution [6].</p><blockquote><strong>Note:</strong> Among all relational operators, joins pose the most significant challenges in terms of processing and optimization. As the number of joins in a query increases, the possible query plans grow exponentially, creating a complex optimization landscape. The diversity of join methods (e.g., nested loop, hash join, merge join) and available indexes (e.g., B-tree, hash, GiST, GIN) for relation access further complicates the optimization effort. To address this complexity, the standard PostgreSQL query optimizer employs a near-exhaustive search algorithm, initially introduced in IBM’s System R database. This algorithm aims to produce a nearly optimal join order, but it comes with a drawback: it demands extensive time and memory resources, especially when dealing with queries involving a large number of joins. Consequently, the standard PostgreSQL query optimizer becomes less suitable for queries joining a significant number of tables [7]. To tackle the join ordering problem more efficiently, PostgreSQL leverages a heuristic optimization technique called Genetic Algorithm. This approach adopts a heuristic-based strategy to find satisfactory join orders, particularly for queries involving a high number of joins. The Genetic Algorithm employs principles inspired by natural selection, allowing it to explore a broader range of potential join orders in a more scalable and resource-efficient manner.</blockquote><p><strong>Executor:</strong></p><p>The executor is responsible for executing the detailed query plan generated by the planner. It traverses the plan, accessing data from tables and indexes, and performs filtering, joins, aggregations, sorting, and other required operations to produce the final result set. Throughout the execution process, the executor manages system resources and handles any errors that may occur. Its role is crucial in efficiently executing complex queries and ensuring accurate and timely retrieval of data based on the provided plan [6].</p><p><strong>File System Activity:</strong></p><p>When a query for a specific row is received, the database server initiates the read process. The database checks if the required page is cached in the buffer pool. The buffer pool acts as a dedicated shared memory area used by the database processes to store frequently accessed data. If the page is not already in the buffer pool, the database server issues a read command with the correct offset and length to the file system, requesting the page in which the row resides (with the help of the index). The OS then takes over and maps those bytes to the logical block address (LBA) of the file system.</p><p>The logical block address is first checked against the file system cache; if absent, the read command is then transferred to the storage controller. The storage controller, along with the disk driver, converts the logical block address to the physical address and checks it against the disk cache. If the requested page is not found in the disk cache, it retrieves the requested page from the disk and places it in the disk cache.</p><p>The requested page is then handed back to the OS, and the OS places it in the file system page cache. Control is then transferred back to the database server. The database server further moves the page from the file system page cache to the shared buffer pool.</p><p>Once the page is in the shared buffer pool, the database server processes the data and extracts the requested rows, which are then returned to the user as the result of the query [8].</p><blockquote><strong>Note</strong>: Most databases utilize a Write-Ahead Logging (WAL) mechanism as a part of transaction management and crash recovery process. A transaction refers to a logical unit of work that consists of one or more database operations. These operations can include reading, writing, modifying, or deleting data in the database. The fundamental characteristic of a transaction is that it must be executed as an indivisible and atomic unit of work, meaning it either completes all its operations successfully or is rolled back to its initial state if any part of it fails. The primary purpose of WAL is to provide durability and consistency guarantees for transactions and ensure the database’s integrity in the event of a system crash or failure. When a transaction is committed (i.e., it has been successfully executed and should be permanently saved to the database), instead of immediately writing the changes to the actual data files on disk, the database writes the changes to a special log called the Write-Ahead Log (WAL). The WAL records the changes sequentially made by the transaction, effectively creating a log of all data modifications. Generally, databases offer both asynchronous and synchronous commit modes. In asynchronous commit, the transaction is considered committed as soon as the changes are written to the WAL, and the actual data files are updated later in the background. In synchronous commit, the transaction is considered committed only after the changes are written to both the WAL and the data files, ensuring greater durability [9][10]. Hence, depending on the SQL operation (i.e., SELECT, INSERT, UPDATE, DELETE, etc.) and durability requirement, the file system activity may occur before or after the database sends a response back to the application.</blockquote><p>The PostgreSQL server serializes the query result into a format compatible with the Node.js application. Once the data is retrieved, it transforms to ensure it is in an appropriate format for transmission through the network layer back to the Node.js application using the pre-established network connection.</p><p>At this stage, the database server has responded with the results of the query requested by the application over the pre-established database connection and has completed its part of this journey.</p><p>Stage 2 was so long (yawns)! Did you take a break when I asked you to? Well, I did warn you! But, c’mon don’t give up yet!</p><h4><strong>Stage 3: The application receives the response</strong></h4><p>Informing the database’s network layer that the packets were successfully received, the application’s network layer then decapsulates the packets, extracts the necessary data from the headers, and sends the payload to the application.</p><p>The PostgreSQL client library in the Node.js application deserializes the data received in the response. The deserialization process involves converting the binary format or a specific text-based format (depending on the chosen protocol and configuration) sent by the database server into a format that the Node.js application can understand and process.</p><p>The connection is returned to the pool after the query has been finished and is then made available for reuse. To save on the overhead of opening a new connection for subsequent queries, the pool keeps the connection open but idle. The pool may decide to remove connections that have been inactive for too long to maximize resource consumption. To make sure there are enough open connections for upcoming queries, this removal is often done selectively and conservatively.</p><p>At this stage, the Node.js application can now send a successful response back to the API request which concludes our long journey.</p><h4><em>I shall end this with an afterthought:</em></h4><p>This journey we discussed happily assumes that all the participating components like software, hardware, network, and humans (I know right!) are working perfectly! Unfortunately, failures in computing systems are unsurprisingly common. I strongly advise exploring the <a href="https://en.wikipedia.org/wiki/Fallacies_of_distributed_computing">Fallacies of distributed computing</a>, as described by L Peter Deutsch, which highlights the mistaken assumptions often made by programmers who are new to distributed applications and the subsequent effects of these assumptions. And as <a href="https://arpitbhayani.me/">Arpit Bhayani</a> (An amazing distributed systems enthusiast) frequently reiterates while quoting Murphy’s Law [11]:</p><blockquote>“Hardware failure in large infra is not a question of ‘if’, but a question of ‘when’</blockquote><blockquote>While building a service that spans 100s of servers, never build with an assumption that the servers will remain up 24x7; instead, be pessimistic and assume failures at and after every single line of code.</blockquote><blockquote>Whatever that can go wrong, will go wrong. This is the secret of designing great systems.”</blockquote><p><strong>Skadoosh!</strong></p><h4>References:</h4><p>[1] Understanding Connections &amp; Pools. (n.d.).<a href="https://sudhir.io/understanding-connections-pools"> https://sudhir.io/understanding-connections-pools</a></p><p>[2] <em>Pooling — node-postgres</em>. node. (n.d.).<a href="https://node-postgres.com/features/pooling"> https://node-postgres.com/features/pooling</a></p><p>[3] Kleppmann, M. (2017). <em>Designing data-intensive applications</em>. O’Reilly.</p><p>[4] <em>55.1. overview</em>. PostgreSQL Documentation. (2023c, May 11).<a href="https://www.postgresql.org/docs/15/protocol-overview.html#PROTOCOL-FORMAT-CODES"> https://www.postgresql.org/docs/15/protocol-overview.html#PROTOCOL-FORMAT-CODES</a></p><p>[5] <em>Chapter 55. Frontend/Backend protocol</em>. PostgreSQL Documentation. (2023d, May 11).<a href="https://www.postgresql.org/docs/15/protocol.html"> https://www.postgresql.org/docs/15/protocol.html</a></p><p>[6] <em>52.1. the path of a Query</em>. PostgreSQL Documentation. (2023, May 11).<a href="https://www.postgresql.org/docs/15/query-path.html"> https://www.postgresql.org/docs/15/query-path.html</a></p><p>[7] <em>62.1. query handling as a complex optimization problem</em>. PostgreSQL Documentation. (2023d, May 11).<a href="https://www.postgresql.org/docs/15/geqo-intro.html"> https://www.postgresql.org/docs/15/geqo-intro.html</a></p><p>[8] Nasser, H. (2023, April 24). <em>Following a database read to the metal</em>. Medium.<a href="https://medium.com/@hnasr/following-a-database-read-to-the-metal-a187541333c2"> https://medium.com/@hnasr/following-a-database-read-to-the-metal-a187541333c2</a></p><p>[9] <em>Write-ahead log</em>. martinfowler.com. (n.d.).<a href="https://martinfowler.com/articles/patterns-of-distributed-systems/wal.html"> https://martinfowler.com/articles/patterns-of-distributed-systems/wal.html</a></p><p>[10] Nasser, H. (2023a, March 3). <em>Why databases need write-ahead log (WAL) — a deep dive</em>. Medium.<a href="https://medium.com/@hnasr/what-is-wal-write-ahead-log-a-deep-dive-a2bc4dc91170"> https://medium.com/@hnasr/what-is-wal-write-ahead-log-a-deep-dive-a2bc4dc91170</a></p><p>[11] #AsliEngineering, A. B. |. (2023, July 25). <em>Hardware failure in large infra is not a question of “if”, but a question of “when” </em>⚡<em>while building a service that spans 100s of servers, never build with an assumption that the servers will remain up 24x7; instead, be pessimistic and assume failures at and after every single…</em> Twitter. <a href="https://twitter.com/arpit_bhayani/status/1683821038076059649?s=20">https://twitter.com/arpit_bhayani/status/1683821038076059649?s=20</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=856d66edfc70" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>