<?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 Akash C on Medium]]></title>
        <description><![CDATA[Stories by Akash C on Medium]]></description>
        <link>https://medium.com/@akashcsharma?source=rss-6ec2f960a4a4------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*JoyMOduvtkK4d0ZyuZETWw.jpeg</url>
            <title>Stories by Akash C on Medium</title>
            <link>https://medium.com/@akashcsharma?source=rss-6ec2f960a4a4------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 11:12:50 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@akashcsharma/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[Demystifying Locks in PostgreSQL: A Guide for Backend Developers and Database Engineers]]></title>
            <link>https://akashcsharma.medium.com/demystifying-locks-in-postgresql-a-guide-for-backend-developers-and-database-engineers-12884965d182?source=rss-6ec2f960a4a4------2</link>
            <guid isPermaLink="false">https://medium.com/p/12884965d182</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[concurrency]]></category>
            <category><![CDATA[database-locks]]></category>
            <category><![CDATA[engineering]]></category>
            <dc:creator><![CDATA[Akash C]]></dc:creator>
            <pubDate>Fri, 02 Feb 2024 10:00:28 GMT</pubDate>
            <atom:updated>2024-02-02T12:27:02.918Z</atom:updated>
            <content:encoded><![CDATA[<p>Imagine this scenario, you’re developing an e-commerce platform where thousands of users are simultaneously browsing products, adding items to their carts, and checking out. Behind the scenes, your database(in this case postgres) is bombarded with queries competing for access to critical resources. Without proper management, chaos ensues — orders get lost, inventory becomes inconsistent, and customers are left frustrated. This is where PostgreSQL locks come into play. They serve as the gatekeepers, controlling access to database objects and preventing conflicting operations from causing mayhem. Whether it’s ensuring two users don’t simultaneously update the same row or preventing a report from interfering with real-time transactions, understanding PostgreSQL locks is essential for building robust and scalable applications. In this article, We’ll delve deep into the intricacies of different lock types, explore special cases like non-blocking queries, and arm you with best practices to navigate the labyrinth of concurrency with confidence.</p><figure><img alt="locks in postgres" src="https://cdn-images-1.medium.com/max/720/0*Bvcq3RgnQ3INa_v0" /><figcaption>src: <a href="https://images.app.goo.gl/yBa76JfMefdQY8116">https://images.app.goo.gl/yBa76JfMefdQY8116</a></figcaption></figure><p>Assume you are a backend engineer or a database engineer for that matter and you have designed the database schema for maintaining the products, inventory, users, orders, carts etc… Now you have added a recently launched product let’s say iPhone 15 into your catalog and it goes on sale on a particular day and you have limited stock. When all the users rush in on that particular day, it’ll be a difficult job for you to manage which customer should get the phone and which customer should not.</p><p>Enter the <strong>row-level locks, </strong>For simplicity sake let’s assume we are working with only one database instance and not multiple instances or some crazy architecture like multi-master architecture or distributed data center based master-master architecture. Assume the table structure as below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vO1I6jKR9bmCF6WO47lbXw.png" /></figure><p>In real world the table will have many more columns and will have relation with many other tables for pricing, location etc… What happens when multiple users wants to buy iPhone15? Let’s say for example there are 2870 quantity of iPhone15 present in inventory and 3000 people want to buy it. There is a sudden increase in load on the DB, but as the fulfilment needs to happen to the first 2870 customers who clicked on BUY button, the logic needs to be handled at either application end or at Database end. But how does row-level locks help us in here? One way is to take a row-level lock on the table and will decrement the quantity column by 1. But do you see any problem with this approach? Pause for a while, think of any problems you might face with this approach and then proceed with the article.</p><p>Did you get it? Row-level write locks in PostgreSQL are not inherently serialized. When multiple transactions attempt to acquire row-level write locks on the same row concurrently, PostgreSQL employs a mechanism known as “<a href="https://minervadb.xyz/understanding-pessimistic-locking-and-optimistic-locking-happening-to-postgresql/">optimistic concurrency control</a>.” (Click to learn more)</p><blockquote>In optimistic concurrency control, transactions are allowed to proceed with their operations assuming no conflicts will occur. Each transaction performs its updates independently without waiting for other transactions to complete. However, PostgreSQL checks for conflicts at the point of commit. If a conflict is detected during commit, such as another transaction modifying the same row concurrently, PostgreSQL will abort one of the conflicting transactions and prompt it to retry its operation.</blockquote><p>I hope you read more about OCC. Then how can we solve this issue? Time for the savior to enter the picture. FOR UPDATE is a command which is used in conjuction to SELECT statement which serializes row-level locks.</p><pre>BEGIN; -- Start a new transaction<br><br>-- Select rows and acquire exclusive locks on them<br>SELECT * FROM inventory WHERE id = 1 FOR UPDATE;<br><br>-- Perform operations on the selected rows within the transaction<br>ALTER TABLE inventory SET quantity = &lt;previous_quantity - 1&gt; WHERE id = 1;<br><br>COMMIT; -- Commit the transaction</pre><ul><li>BEGIN; starts a new transaction.</li><li>The SELECT ... FOR UPDATE statement selects rows from the inventory table on the given id and acquires exclusive row-level locks on them.</li><li>Within the transaction, you can perform any necessary operations on the selected rows, knowing that they are locked and serialized.</li><li>COMMIT; commits the transaction, releasing the locks acquired by the SELECT ... FOR UPDATE statement.</li></ul><p>One more thing to note about this is in most of the companies these kinds of reads won’t completely happen through database but sometimes also from a cache layer like Redis, wherein inventory count of a product is stored and the same is read and updated at the cache layer. We’ll discuss more about caching (external like Redis) in upcoming articles.</p><p><strong>Conclusion</strong>: All these statements holds true for certain ISOLATION LEVELS of Postgres and not for all. At the end of the day it’s the decision of the Database Engineer or Backend Engineer to make these choices of concurrency handling mechanisms, maintaining a proper table schema, handling locks and much more. There are some cases wherein we want to just reject concurrent transactions which are trying to acquire lock on a particular datapoint, that can be done using appending NOWAIT at the end of the query which returns error for all the transactions which tries to take a lock on a already locked row. In my next articles, let’s discuss more about internals of Postgres with respective to distributed environments. Stay tuned!!!! Also please follow and share it to as many people as possible so that the reach of article increases. Thank you!!!</p><ul><li><a href="https://akashcsharma.medium.com/garbage-collection-in-postgresql-d833b80b6ef5">Garbage Collection in PostgreSQL</a></li><li><a href="https://akashcsharma.medium.com/demystifying-database-internals-part-2-deep-dive-into-storage-engine-of-dbms-episode-ii-query-4f9d6ba35670">Demystifying Database Internals: Part 2 -Deep dive into Storage Engine of DBMS Episode II(Query…</a></li><li><a href="https://akashcsharma.medium.com/demystifying-database-internals-part-2-deep-dive-into-storage-engine-of-dbms-episode-i-paging-2f748a078d22">Demystifying Database Internals: Part 2 -Deep dive into Storage Engine of DBMS Episode I(Paging…</a></li></ul><p>Follow me on linkedin : <a href="https://www.linkedin.com/in/akashc1999/">https://www.linkedin.com/in/akashc1999/</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=12884965d182" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Garbage Collection in PostgreSQL]]></title>
            <link>https://akashcsharma.medium.com/garbage-collection-in-postgresql-d833b80b6ef5?source=rss-6ec2f960a4a4------2</link>
            <guid isPermaLink="false">https://medium.com/p/d833b80b6ef5</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[postgres-vacuum]]></category>
            <category><![CDATA[garbage-collection]]></category>
            <category><![CDATA[mvcc]]></category>
            <category><![CDATA[database-optimization]]></category>
            <dc:creator><![CDATA[Akash C]]></dc:creator>
            <pubDate>Sun, 28 Jan 2024 10:44:12 GMT</pubDate>
            <atom:updated>2024-01-30T06:42:53.530Z</atom:updated>
            <content:encoded><![CDATA[<p>Postgres uses a concurrency control mechanism called Multi Version Concurrency Control (MVCC) due to which Garbage Collection (GC) becomes an important factor while maintaining large scale database operations. In this article let’s dig deep into how garbage collection works under the hood and how does it concern you as a Database Engineer or a developer for that matter.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZXszM5CNz89XowCADo7kvg.jpeg" /><figcaption><a href="http://freepik.com/">picture credits: freepik.com</a></figcaption></figure><blockquote>Databases, especially those with frequent updates and deletions, can experience fragmentation over time. This means data becomes scattered across the storage file, leading to inefficiency.</blockquote><p><em>Abit about </em><strong><em>Multi Version Concurrency Control (MVCC) </em></strong><em>before understanding garbage collection.</em> Let’s say you have a table named employee in your database. Imagine you want to update a employee’s salary based in their ID. What Postgres does is it takes a exclusive write lock on the row on which the update needs to take place. But during this time let’s say a read query wants to read that particular employee’s salary. The read query will succeed in reading the salary but, the older salary. How you ask? It’s because in Postgres every data is versioned and whenever there is an update to a datapoint a new row is inserted to the table with updated version and the older row will be marked inactive and considered as a dead tuple by Postgres. <a href="https://www.postgresql.org/docs/7.1/mvcc.html"><em>Click for more info</em></a><em>.</em></p><blockquote>Remember, In Postgres, Readers never block Writers and Writers never block Readers</blockquote><p>And now as we have understood how MVCC in Postgres works, we can understand the need of garbage collection in Postgres. As a row gets updated, as the previous row still exists in the DB itself doesn’t matter whether its marked active or inactive it still takes physical storage. As database scales and when a particular table get’s a lot of updates there arises a case of storage getting filled (which happens in the rarest of the cases) but there is also a case where in the size of the index also increases. <em>PostgreSQL indexes are primarily built upon a data structure called a B-tree. </em>As B-trees’ are normally stored on disk, at times postgres’ buffer pool manager pulls the frequently accessed index blocks into RAM and cache them for faster retrieval of data. But there is a catch w.r.t index, Postgres keeps the inactive/dead tuples as well in it’s index which ensure efficient query execution, even when filtering for active data. And when there are lot of indexes active and frequently accessed, the <a href="https://akashcsharma.medium.com/demystifying-database-internals-part-2-deep-dive-into-storage-engine-of-dbms-episode-i-paging-2f748a078d22"><em>buffer pool manager</em></a><em> </em>pulls them into memory thereby increasing the memory usage.</p><blockquote><strong>Impact on Indexing </strong>: Indexes on tables with a high volume of updates or deletions can grow larger due to inactive tuples. This can impact performance and storage space.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2KyVPr4ozlJ4WdEL1RVM4w.jpeg" /></figure><p><strong>VACUUM</strong> is a process which deletes the inactive or dead tuples from the storage and reclaims the space and that space will be given back to the operating system to use it for upcoming inserts/updates.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/650/1*V0Z8srMM1cl2fbMVT19JIw.png" /><figcaption>vacuum process</figcaption></figure><p>Just as shown in the above picture, VACUUM deletes the inactive row from the storage and is marked as FSM. But what the heck is a FSM? In PostgreSQL, a Free Space Map (FSM) is a tree-structured organization of pages that provides hints about the location of free space within a relation, which is then taken care of by the OS itself. Well this is good then, everything is sorted? Not exactly, when a VACUUM process is run, only the storage is freed but not the dead tuples which are already existing in the index. In order to remove the dead tuples from the index as well there is a process called VACUUM FULL which needs to be executed.</p><p>What VACUUM FULL does is it rebuilds the whole table and rebuilds the index. One problem which we missed to address is fragmentation. There are two main types of fragmentation:</p><ul><li>Table fragmentation: Data pages within the table file become interspersed with free spaces, increasing read/write head movement and slowing down data access.</li><li>Index fragmentation: Similar to table fragmentation, free spaces become scattered within the index file, impacting search performance.</li></ul><p>But the question at hand is, will VACUUM FULL aid in defragmentation of a table? The answer is both yes and no. PostgreSQL can indirectly contribute to defragmentation, but it’s not a true defragmentation method and shouldn’t be solely relied upon for addressing fragmentation issues. But what are the ways in which I can perform defrag? For true defragmentation, utilize other dedicated methods suggested below:</p><ul><li>CLUSTER command: This physically reorders table data based on an indexed column, placing related data together and significantly improving access performance for queries using that column.</li><li>Database reorganization tools: Specific tools like pg_repack can be used to physically reorganize tables and indexes for optimal performance and reduced fragmentation.</li></ul><p>It seems that VACUUM FULL coupled with these methods is a really good choice for improving the performance of database. The answer is “it depends…” While VACUUM FULL does do a lot of stuff there are certain limitations while using it. Some of them are, it takes an exclusive lock on the whole table which makes the table inaccessible or unavailable for both reads and writes. The above statement is only partially true. Why?</p><ul><li><strong>Reads</strong> are not entirely blocked during VACUUM FULL. However, they can experience slowdowns due to several factors:</li><li>Increased I/O activity: VACUUM FULL performs intensive disk operations to read and rewrite table data. This can compete with read queries for disk resources, leading to delays.</li><li>Table locking: Depending on the isolation level used, VACUUM FULL might acquire exclusive locks on tables or indexes, temporarily preventing concurrent reads.</li><li>Similar to reads, <strong>writes</strong> are not completely blocked during VACUUM FULL. However, they can be significantly impacted in several ways:</li><li>Delayed execution: Writes might need to wait until VACUUM FULL finishes processing relevant table segments. This can lead to increased write latency and potential queuing of write operations.</li><li>Deadlock risks: In rare cases, conflicting locks between VACUUM FULL and writes can lead to deadlocks, causing both operations to stall.</li></ul><p>Granted all these operations, Postgres by default takes the effort off of the developers by exposing options to perform vacuum using a config called auto_vacuum This config is exposed by all the popular cloud providers. But how to tune it in local instance of postgres? In Mac system navigate to /opt/homebrew/var/postgres@14 version number in the path changes depending on the version you are running.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ud0xWi3sdRlUmmcvGUj22g.png" /></figure><p>Search for fine tuning the config according to your needs or contact a Database adminitrator to do it.</p><p><em>Conclusion is, garbage collection is a very important process in part of scaling your database system’s performance and throughput. There are hell lot of other things which needs to be implied other than garbage collection for optimizing the database. We’ll discuss all those in upcoming articles. Thank you!!! Do read my previous articles get the gist of internals of postgres. Also follow me on linkedin for easy 1 min knowledge filled with fun.</em></p><ul><li><a href="https://akashcsharma.medium.com/demystifying-database-internals-part-2-deep-dive-into-storage-engine-of-dbms-episode-i-paging-2f748a078d22">Demystifying Database Internals: Part 2 -Deep dive into Storage Engine of DBMS Episode I(Paging…</a></li><li><a href="https://akashcsharma.medium.com/demystifying-database-internals-part-2-deep-dive-into-storage-engine-of-dbms-episode-ii-query-4f9d6ba35670">Demystifying Database Internals: Part 2 -Deep dive into Storage Engine of DBMS Episode II(Query…</a></li><li><a href="https://akashcsharma.medium.com/demystifying-database-internals-part-3-in-depth-exploration-of-indexes-in-relational-databases-5a00dcf900e3">Demystifying Database Internals: Part 3-In-Depth Exploration of Indexes in Relational Databases</a></li></ul><p>Link to linkedin profile: <a href="https://www.linkedin.com/in/akashc1999/">https://www.linkedin.com/in/akashc1999/</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d833b80b6ef5" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[In-Depth Exploration of Indexes in Relational Databases]]></title>
            <link>https://akashcsharma.medium.com/demystifying-database-internals-part-3-in-depth-exploration-of-indexes-in-relational-databases-5a00dcf900e3?source=rss-6ec2f960a4a4------2</link>
            <guid isPermaLink="false">https://medium.com/p/5a00dcf900e3</guid>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[database-index]]></category>
            <category><![CDATA[optimization]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Akash C]]></dc:creator>
            <pubDate>Sun, 02 Jul 2023 11:00:05 GMT</pubDate>
            <atom:updated>2024-02-03T08:25:49.314Z</atom:updated>
            <content:encoded><![CDATA[<blockquote><strong>Introduction</strong></blockquote><blockquote>Welcome to this comprehensive exploration of indexes in relational databases. In this article, we will take an in-depth look at the world of indexes, covering various types, creation and modification techniques, indexing strategies, the impact of cardinality, downtime considerations, covering indexes, and index usage in partitioned tables. By the end of this journey, you will have a solid understanding of how indexes work and how to leverage their power to optimize your database systems.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*3uaGVdHIIu0qS9TE" /><figcaption>Img src : <a href="https://planetscale.com/blog/how-do-database-indexes-work">https://planetscale.com/blog/how-do-database-indexes-work</a></figcaption></figure><blockquote><strong>Part 1: Introduction to Indexes. Indexes play a crucial role in optimizing query performance and accelerating data retrieval in relational databases.</strong></blockquote><p>In this section, we will delve into the significance of indexes, their relationship with data organization, and the fundamental principles that govern their functioning. We will explore how indexes facilitate faster data access, reduce disk I/O, and improve the overall efficiency of database operations.</p><p><strong>B-Tree Index Underlying Data Structure</strong>: The B-Tree data structure serves as the foundation for most indexing mechanisms in relational databases. B-Trees efficiently organize and store large amounts of data, allowing for efficient data retrieval operations. Basically B tree is a self balancing m-ary tree. So, searching in a B-Tree is relatively cheaper than scanning through all the records sequentially. The image below shows a pictoral representation of a B-Tree</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*04xTgQQA1Ovr1hwJ.png" /></figure><p><strong>B+Tree Index A Variation for Enhanced Performance:</strong> Building upon the concepts of B-Trees, the B+Tree index offers further performance enhancements for relational databases. In B+Tree all the data are stored in the leaf nodes, so the data retrieval is much easiser. Wonder why most databases use B+ trees to store the indexes? Checkout this link to the youtube video <a href="https://youtu.be/09E-tVAUqQw">https://youtu.be/09E-tVAUqQw</a>. Also keep in mind there are a lot of other strategies to maintain the indexes which are out of scope of the article. If you are interested to learn more plz follow the <a href="https://www.freecodecamp.org/news/postgresql-indexing-strategies/">this link</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*72B9mHqiDeohHrlZ.png" /><figcaption>Image Courtesy : javatpoint.com</figcaption></figure><p><strong>Why an Index required?</strong></p><p>As we know in Data structures and Algorithm terms the big O’h notation roughly determines the time taken by an algorithm or a piece of code to execute. Let’s say I have an array of elements(repeated). If I want to know the total number of occurrences of a particular element in an array, it’ll take me O(n) time i.e., liner time, because we are sequentially scanning through the array for simplicity sake I am assuming the array is not sorted. Let’s say if we have a hash-map to store all the unique keys in the array and the key corresponding to that will contain the count of the repeated elements. This way we can decrease the time required to get our results. As hash-map reduces the time complexity, here the Index reduces the time complexity for obtaining the data.</p><p><em>Scans, Navigating through Indexes</em>: Index scans play a crucial role in efficiently retrieving data from indexes. We will explore optimization techniques for index scans, such as prefetching and caching, to further enhance query performance.</p><blockquote><strong>Index Scans and Index-Only Scans(Eliminating Table Lookups)</strong></blockquote><p>Index scan is a technique wherein the index maintains a pointer to the location where the particular row is present on the Disk so as to get a instant access to the data. If a data is cached in memory or in buffer pool then that is a whole another discussion (<a href="https://dba.stackexchange.com/questions/316327/what-is-the-benefit-of-indexing-assuming-that-sql-server-has-huge-memory-to-acco">link to the discussion</a>). In index scan only the index will be cached Index-only scans provide a significant performance boost by eliminating the need for table lookups during query execution. Index only scan is performed when the data you are looking for is entirely present as a part of index. Some database designers go with this approach to stripoff of millisecond latency of fetching data in some crucial small tables. An <a href="https://www.postgresql.org/docs/current/indexes-index-only-scans.html">index-only scan</a> is faster, but it’s not always available as an alternative to a regular index scan. It has two restrictions: the index type must support Index-Only Scans and (somewhat obviously) the query must only project columns included in the index. By referencing data directly, we can reduce the number of disk seeks, but have to pay a cost of updating the pointers whenever the record is updated or relocated during a maintenance process. Index only scans can be achieved by using a technique called <a href="https://www.postgresql.org/docs/current/indexes-index-only-scans.html">covering indexes</a>. More things to keep in mind is that a index is not only cerated on a single column but also on multiple columns. Read through the first and second link in in the references page to know more about them.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*EY7YN9CfQH2M53oioFBG_g.png" /><figcaption>Image courtesy : Database Internals: A Deep Dive Into How Distributed Data Systems Work <a href="https://www.databass.dev/">https://www.databass.dev/</a></figcaption></figure><p><strong>Index Creation: </strong>Have you ever wondered how indexes are built/created on a table which frequently changes? Creating an index involves analyzing the data and constructing the index structure to facilitate faster data retrieval. Creating indexes on large tables can be a resource-intensive operation that may require database downtime. I myself have tried this and database management system does take a lock on the table you are indexing for. So, how can we get over it? How can me guarantee 99.9999% of SLA for our database? Modern databases uses techniques called online indexing, incremental indexing and parallel indexing to achieve this. More on this in the next articles (DBs in a high performing and distributed environments.)</p><p><strong>Modifying Indexes:</strong> Adding, Updating, and Deleting Entries. As data in the database changes, indexes need to be updated to maintain their accuracy and relevance. Let’s break down all these scenarios one by one. At first when there is a new data addition to the table, a worker gets active in background which when a transaction get’s committed, updates the index entries by added a new key to the index table(let’s assume a B+ tree), and adds the pointer to the location of the data present. While updating a row in a database doesn’t affect the index much but the pointer does get’s changes because the updation of data is noting but deactivating the previous record and adding a new record and marking it active in most of the databases. While deleting a record from a table does have a same effect as addition because the index structure get’s changed if you are using a B tree kind of a structure. Because deleting a node from a self balancing tree or even a tree could lead to multiple reassigns of pointers in many a places.</p><p><strong>Impact of Cardinality on Indexes: </strong>Cardinality, representing the uniqueness of values in a column, has a significant impact on index performance. Index works best when the data/column we are indexing is discrete in nature that is, it should be as unique as possible so that the hash value which derives from the same will be different and will lead to a single pointer location on the disk, otherwise if there are lot of records pointing to a single index then after reaching the disk, you may need to involve one more index or need to scan the whole block to get the desired result. <a href="https://medium.com/u/b1fb0cb8c8e">SwiggyBytes</a> have mentioned the same in one of their articles explaining how one bad indexing costed them 1 hour of downtime. <a href="https://bytes.swiggy.com/behindthebug-indexing-gone-wrong-6b4d682fd805">Link to the article</a>.</p><blockquote><strong>Conclusion</strong>:</blockquote><blockquote>In this comprehensive exploration of indexes in relational databases, we have covered various aspects of indexing, from underlying data structures like B-Trees and B+Trees to index scans, index creation and modification techniques, and considerations for downtime and cardinality.</blockquote><p>We have discussed the pros and cons of indexes, covering indexes. By understanding these concepts, you are equipped to make informed decisions about index design and implementation, optimizing the performance and efficiency of your database systems.</p><p>My next articles will cover some of the major design principles of databases, how do they perform in a distributed environment, more different kinds of databases etc… Keep crunching…………. Bye.</p><p><em>References</em>:</p><ol><li><a href="https://www.oreilly.com/library/view/database-internals/9781492040330/">https://www.oreilly.com/library/view/database-internals/9781492040330/</a></li><li><a href="https://www.freecodecamp.org/news/database-indexing-at-a-glance-bb50809d48bd/">https://www.freecodecamp.org/news/database-indexing-at-a-glance-bb50809d48bd/</a></li><li><a href="https://www.javatpoint.com/b-tree">https://www.javatpoint.com/b-tree</a></li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5a00dcf900e3" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Deep dive into Storage Engine of DBMS Episode II(Query Planning/ Optimization, Execution…]]></title>
            <link>https://akashcsharma.medium.com/demystifying-database-internals-part-2-deep-dive-into-storage-engine-of-dbms-episode-ii-query-4f9d6ba35670?source=rss-6ec2f960a4a4------2</link>
            <guid isPermaLink="false">https://medium.com/p/4f9d6ba35670</guid>
            <category><![CDATA[dbms]]></category>
            <category><![CDATA[locks-in-dbms]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[garbage-collection]]></category>
            <category><![CDATA[query-optimization]]></category>
            <dc:creator><![CDATA[Akash C]]></dc:creator>
            <pubDate>Fri, 26 May 2023 12:49:37 GMT</pubDate>
            <atom:updated>2024-02-03T08:26:23.290Z</atom:updated>
            <content:encoded><![CDATA[<h3>Deep dive into Storage Engine of DBMS Episode II(Query Planning/ Optimization, Execution, Transaction Manager, Lock Manager, Concurrency control).</h3><p>Introduction:</p><p>Have you ever wondered in a high write and read environment database setup, whenever you write a query how does the data gets written into the disk without any changes in the underlying information and how one can read from a database when the other is writing, what happens when concurrent requests of reads/writes are fired onto a single data point? Well if you haven’t asked this question yet, do now and once you have given it some thought, continue with the article.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*3YIBzmxR59q21vIq.png" /><figcaption><a href="https://vladmihalcea.com/wp-content/uploads/2018/05/StatementLifeCycle.png">https://vladmihalcea.com/wp-content/uploads/2018/05/StatementLifeCycle.png</a></figcaption></figure><blockquote><strong>Part 1: Query Planning<br></strong>Query planning is a crucial step in the execution of a database query, where the database management system determines the most efficient strategy to retrieve the requested data based on the query and the available database resources. It involves analyzing the query, understanding the data access patterns, and generating an optimized execution plan that minimizes the overall cost of executing the query.</blockquote><p>During query planning, the DBMS performs the following tasks:<br>1. <strong>Query Parsing</strong>: The DBMS parses the SQL or query language statement to understand its structure and syntax. It verifies the statement’s validity and identifies the tables, columns, and conditions involved.</p><p>2. <strong>Query Optimization</strong>: The DBMS evaluates various strategies and algorithms to determine the most efficient way to execute the query. The goal is to minimize the time and resources required to retrieve the requested data.</p><p>3. <strong>Query Plan Generation</strong>: The plan defines the sequence of operations and the access methods/paths to retrieve the required data efficiently. The DBMS first prepares a logical plan and then maps that to the physical layout of the data, considering factors such as indexes(will be covered in later parts), storage structures, and available hardware resources. The physical plan outlines the specific data access methods, join algorithms, and other operations necessary to execute the query.</p><p>4. <strong>Plan Cost Estimation</strong>: The DBMS estimates the execution cost of the generated query plan, considering factors like disk I/O, CPU usage, memory requirements, and network communication. This estimation helps the DBMS compare and select the most efficient plan among the available alternatives.</p><p>Once the query planning phase is complete, the DBMS proceeds to the query execution phase, where it executes the generated query plan to retrieve and process the requested data from the database. Efficient query planning is crucial for optimizing query performance and ensuring the timely retrieval of accurate results from the database.</p><p>These are all just some high level overview of what the query planner does, lets now dive deep into the internals where we will get to explore the query lifecycle how it’s run, how data is gathered. Let’s go then…</p><blockquote><strong>Part 2: Query Execution<br></strong>Well there’s nothing much to define about query execution, as the name says, after a plan is set to execute a query, the query execution process begins.</blockquote><p>While executing a query, DBMS employs different strategies to perform the operations known as Processing Models. There are 3 types of the same, they are :</p><ol><li>Iterator Model</li><li>Materialization model (suitable for in-memory DBs)</li><li>Vectorized model (suitable for analytical workloads)</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/0*GiJX5KER5ZJ50N6R.gif" /><figcaption>Image src : <a href="https://aandds.com/blog/images/sql_volcano.gif">https://aandds.com/blog/images/sql_volcano.gif</a> (CMU Database Group)</figcaption></figure><p>Basically in an iterator model whenever a query is executed, under the hood a series of for loops will be created in a tree based structure, where the parent node will call the child node and asks it to access some part of the data from the disk and when the operation is completed the child returns the data to the parent node with an emit event, this way the required data is moved up the tree and the finalised data will be present at the very parent.</p><p>Materialization is a technique used to improve the performance of queries by precomputing and storing the results of complex or expensive operations.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1004/0*On753Q18Sj54W3Ix.jpg" /></figure><p>On a broader view there are 3 types of materialization which will be used according to the query plan. They are <strong>Eager Materialization, Lazy Materialization, On-Demand Materialization. </strong>If you want to know more please refer <a href="https://fromdual.com/mysql-materialized-views">this</a>.</p><p>One of the ways in which DMBS optimizes the data reading from cache pages is through paging. As Paging is a broader concept and will be covered in upcoming articles, now we’ll discuss one more method which is a subset of indexing by which this can be achieved. The concept is <strong><em>Zone Map. </em></strong>They are basically pre-computed aggregates for the attribute values in a page. The storage manager first checks the Zone map to decide whether the data it is looking for is present in that particular page or not. Zone map maintains kind of statistical data about the page like the minimum value of a column in the page, the maximum value etc… Lets say this is the query which is being executed SELECT * FROM table WHERE val &gt; 600; As the val column is sorted and indexed and the sorted version is stored in the page, using a zone map will really speeds up the data access.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/906/1*5kJSJDExlUeENBQ4ANV9vw.png" /></figure><blockquote><strong>Part 3: Transaction Manager and Lock Manager and Concurrency Control<br></strong>A transaction manager is a piece of software which takes care of a single transaction from start to end i.e., from the very first operation of the query till the very end (committing) of the query. Lock manager on the other hand, helps the transaction manager in writing or reading data from the disk/cache without worrying about the changes in data which might happen while reading/updating the data. Concurrency control is a technique where in the isolation level of DB is defined so that the readers will either get the most latest uncommitted data or only committed data.</blockquote><p>Transaction is a single block of operation which is bound by rules. A transaction will be performed atomically so that the durability of the system is maintained. If you are using a distributed architecture where in there are multiple read write replicas then consistency would be a major concern after durability. More about maintaining consistency will be explained further in the article. The below diagram shows the life cycle of a transaction and it’s stages in DBMS.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/743/0*EpvqE_d-A9f_uNVC.png" /><figcaption>Img src : <a href="https://media.geeksforgeeks.org/wp-content/uploads/20200501195048/Tt7.png">https://media.geeksforgeeks.org/wp-content/uploads/20200501195048/Tt7.png</a></figcaption></figure><p>When a transaction or a query comes in, the transaction manager will be set to active state irrespective of whether the query is a read or write operation. If the query is a write operation it’ll bring in a change to the underlying data so a <a href="https://www.ibm.com/docs/en/cics-ts/5.5?topic=locking-exclusive-locks-shared-locks">EXCLUSIVE</a> lock is taken on the data which needs to be modified if it’s a modification, so that any concurrent read operation on the same dataspace is restricted so as to maintain the consistency and durability of data across the systems. This is also dependent on the database <strong><em>Isolation Levels. </em></strong>More on Isolation levels in the further parts of the article. Whenever a transaction fails to update the data, in any point if it fails then the whole transaction needs to be cancelled and the partially committed changes also needs to be brought back to the state when the transaction got started. This mechanism of reverting back the state of the database is called <strong><em>Rollback. </em></strong>If the updation went through perfectly then, the transaction is considered as success the it will be terminated accordingly only after committing the same. Whereas while reading the data from the source, the dbms will take a <a href="https://www.ibm.com/docs/en/cics-ts/5.5?topic=locking-exclusive-locks-shared-locks">SHARED</a> lock on the dataspace so that other threads which want to read the same dataspace can read as well. In database like PostgreSql, it has a concurrency control mechanism widely known as <a href="https://en.wikipedia.org/wiki/Multiversion_concurrency_control"><strong><em>Multi Version Concurrency Control(MVCC)</em></strong></a><strong><em>. </em></strong>Lets say a thread is writing/modifying a data, and simultaneously a reader comes and tries to read the same data, in normal isolation levels the readers will be blocked while a process is writing to the same dataspace, but in MVCC, all the readers will see a snapshot of the data even if the writers have taken a lock so that the readers won’t be blocked at any time. It is also widely known as readers won’t block writers and writers won’t block readers. With this same approach comes a headache of how to clean the snapshot after the query is committed? The answer to this is through a special function in PostgreSQL called VACUUM and VACUUM FULL . Vacuum is a process by which all the dead tuples are deleted from the disk thereby gaining more disk space. Usually this is run periodically as a background process.</p><p>Some of the isolation levels are:</p><ol><li><strong><em>Serializable Isolation: </em></strong>Each query(within transaction) could see only data committed <em>before that transaction started</em>, plus changes made in the transaction itself. So, there are no phantoms here.</li><li><strong><em>Read Committed: </em></strong>Each query(within transaction) could see only data committed <em>before that query started</em>. So, if you run the same query twice in the same transaction, you could see different results and phantoms.</li><li><strong><em>Read Uncommitted: </em></strong>Each query(within transaction) could see all the uncommitted data by other transaction being performed on the same data.</li><li><strong><em>Repeatable Read: </em></strong>This is the strictest possible isolation level. In this even when a reader is reading a data, the writers will be blocked from modifying the data hence there will be no phantom or dirty reads possible at all.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/0*69p4iLcwBG_EA1l9.png" /><figcaption>Img src : <a href="https://foojay.io/wp-content/uploads/2022/11/image2-700x308.png">https://foojay.io/wp-content/uploads/2022/11/image2-700x308.png</a></figcaption></figure><p>While garbage collection (GC) in PostgreSQL uses Vacuum and Full Vacuum both of them are different in nature, while simple vacuum regains the space after deleting the dead tuples, it won’t return it to the Operating System and it doesn’t take any exclusive lock on anything. Whereas Full Vacuum takes an exclusive lock on the whole table on which it is performing garbage collection, it’ll also release the regained space back to the operating system.</p><p><strong>Conclusion:</strong></p><p>In this article we learnt about life cycle of a query, how the reads / writes take place in a Database System, how transactions guarantee atomic reads/writes, locks in a database system, different isolation levels in DB and specifically in depth about PostgreSQL’s implementation of MVCC which is a game changer in some of the application. We also took a look into garbage collection process of PostgreSQL. In next article I’ll be explaining about indexes and it’s importance. Stay tuned !!!!!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4f9d6ba35670" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Deep dive into Storage Engine of DBMS Episode I(Paging, Buffer Pool Manager, Write Ahead Log)]]></title>
            <link>https://akashcsharma.medium.com/demystifying-database-internals-part-2-deep-dive-into-storage-engine-of-dbms-episode-i-paging-2f748a078d22?source=rss-6ec2f960a4a4------2</link>
            <guid isPermaLink="false">https://medium.com/p/2f748a078d22</guid>
            <category><![CDATA[database-internals]]></category>
            <category><![CDATA[write-ahead-logging]]></category>
            <category><![CDATA[system-architecture]]></category>
            <category><![CDATA[buffer-pool]]></category>
            <category><![CDATA[paging]]></category>
            <dc:creator><![CDATA[Akash C]]></dc:creator>
            <pubDate>Thu, 18 May 2023 11:47:40 GMT</pubDate>
            <atom:updated>2024-02-03T08:26:59.171Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Introduction</strong>:</p><p><em>Have you ever wondered whenever you </em><em>INSERT or </em><em>UPDATE something into a database where does it get stored and how it is retrieved so fast and given back to the application/user?</em> In the realm of database systems, there exists a crucial but often overlooked component that powers the efficiency and performance of data management: storage engines. These engines form the backbone of database systems, responsible for storing, retrieving, and manipulating vast amounts of information. Understanding the inner workings of storage engines is key to unlocking the full potential of databases. In this article, we will embark on a journey to demystify storage engines, exploring their architecture, functionalities, and their impact on database performance.</p><p><strong>What is Storage Manager?<br></strong>The storage manager is responsible for maintaining a database’s files. Some do their own scheduling for reads and writes to improve spatial and temporal locality of pages. It organizes the files as a collection of pages. Tracks data read/written to pages. Tracks the available space.</p><blockquote><strong>Part 1: Paging</strong><br>A Page is a fixed-size block of data which contains anything from meta-data, log records to indexes as well. Each page has its own unique identifier which is used to identify a particular page in the cache.</blockquote><p>In Database Analogy, cache may be of many a types. The underlying operating system has it’s own cache and the database also maintains it’s own cache. More about this in the Buffer Pool part of this article. There are basically 3 different notion of pages in Database they are,</p><ol><li>Hardware Page (usually 4KB)</li><li>OS Page (usually 4KB)</li><li>Database Page (upto 16KB)</li></ol><p>Hardware Page is the smallest size of data or chunk of data that the storage device can guarantee it can write out atomically. You must be wondering what does atomically means? And why is that important? The answer to that is, In a database management system, we can define an atomic transaction as an indivisible as well as irreducible series of database actions in which either everything happens or nothing happens. Database pages serve as containers for storing data within the database. They are used to hold a specific amount of information, such as rows or records from database tables. The size of a page is determined based on factors like disk architecture and performance considerations. Pages play a crucial role in data retrieval and storage efficiency. When data is accessed from the database, the DBMS typically loads one or more pages from disk into memory. This process is known as disk I/O (input/output).</p><blockquote><strong>Part 2: Buffer Pool Manager<br></strong>The buffer pool is an area in main memory(RAM) where Database caches table and index data as it is accessed. The buffer pool permits frequently used data to be accessed directly from memory, which speeds up processing of the same data eventually.</blockquote><p>Basically the database system stores the data in persistent storage(disk). While repetitive access of data from the same is expensive, the frequently accessed pages are cached in in memory. When the same page containing the required data is requested by the storage manager, instead of going to disk and fetch the data from there, the page will be directly returned from the buffer pool itself, it means the cached version of the page is returned.</p><p><em>You might be wondering what happens when a page is cached in memory and there are two near concurrent transactions comes into database where in the first transaction wants to update one of the records present in the page and the second transaction wants to read the same record. What will happen at that time?</em></p><p>Answer to that depends on the implementation of the database strategies. Basically there are two types of cache based writes, they are:</p><ol><li>Write-Through cache : In this type of implementation, whenever an updation request comes, the update is first done to the page present in the cache and then synchronously blocks the client to make the same update in disk as well. The advantages of this implementation is that the data is durable and consistent across cache and disk. But one of the major disadvantages of this implementation is the increased number of I/O calls.</li><li>Write-Back cache : In this type of implementation, whenever an updation request comes, the update is done only at the cache end and later some point in time the cache turns around and updates the same in the disk as well. Advantages of this approach are the decreased I/O calls which significantly improves the performance of the database but the major disadvantage is data is consistent across cache and disk. <em>More on this in the Write Ahead Log section of the article.</em></li></ol><p>A Page table keeps track of all the pages that are currently in memory. It also maintains dirty flags for cache updations and also is responsible for keeping the pages pinned in memory. Why pinning is required? In a distributed environment there’ll be lot of queries coming in to database which might access the same page or the different ones. If all the frames in the buffer pool are full and one more page needs to be cached, if none of the pages are pinned, the buffer pool manager just evicts any random page from the memory and puts the new page. If the page which got evicted was getting used by some other thread, this eviction will cause performance problem. In order to avoid that, whenever a thread reads a page, first it pins the page which will tell the manager that this page is currently being accessed by some other query. If there is an empty frame in the buffer, before writing a new page into the buffer, a ‘<a href="https://dba.stackexchange.com/questions/252888/differences-of-latch-and-lock#:~:text=Latches%20protect%20various%20memory%20structures,can%20have%20to%20those%20objects.">latch</a>’ is taken so that no other thread tries to write to the same frame. In a real world database there may exists more than one buffer pool. In that case any request which comes in will first be hashed and will be routed to particular buffer pool just like nginx(reverse proxy). Buffer Pool Manager can also prefetch the pages from the disk if a continuous scan is happening on the neighbour page.</p><p>As we discussed earlier even Operating System also maintains it’s own cache. When you open a file/page OS tries to cache it in RAM or in some other cache. This may lead to duplicated cache as if the same page is being cached by both OS and the Database, it’s just a waste of memory. So many a databases advise the user to turnoff the system cache by using the O_DIRECT flag. (PostgreSQL on the other hand actually uses the OS cache)</p><blockquote><strong>Part 3: Write Ahead Log<br></strong>Write-ahead logging (WAL) is a technique used in database management systems to ensure durability and recoverability of transactions. It involves recording changes to the database in a log file before they are applied to the actual data files.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/991/0*KuLUivWIWBgJOFlS.png" /><figcaption>Image src : <a href="https://www.intel.com/content/www/us/en/developer/articles/technical/optimizing-write-ahead-logging-with-intel-optane-persistent-memory.html">https://www.intel.com/content/www/us/en/developer/articles/technical/optimizing-write-ahead-logging-with-intel-optane-persistent-memory.html</a></figcaption></figure><p>Assume you are a small kid and your mom/dad have asked you to do some tasks for the day. You’ll first write down that task on a piece of paper and then eventually completes those tasks right? That exactly what WAL does. In this reference the piece of paper is the WAL.</p><p>Here’s how write-ahead logging works:</p><ol><li>Before Modifications: The key principle of write-ahead logging is that the log records must be written to disk before the corresponding modifications are applied to the actual data files. This ensures that the log reflects the changes made by transactions and serves as a reliable record of the database’s state.</li><li>Sequential Write: The log records are written sequentially to a dedicated log file, often stored on disk. Sequential writes are typically more efficient than random writes because they minimize disk seek times.</li><li>Durability and Recoverability: The write-ahead log provides durability by ensuring that committed changes are persistently stored on disk, even in the event of a system crash or failure. It enables database recovery by allowing the system to reconstruct the database state from the log records during the recovery process.</li><li>Checkpoints: To manage the size of the log file, periodic checkpoints may be performed. A checkpoint is a synchronization point where all modified data pages in memory are written to disk, and the corresponding log records are flushed from memory to the log file. This process allows for the truncation or recycling of old log records.</li></ol><p><em>You might ask what happens when a user </em><em>COMMIT their transaction. Will that be flushed to disk instantly?</em></p><p>The answer is No. Database does not guarantee the user when they COMMIT their transaction that’ll be flushed to the disk instantly. Instead, the flushing of the WAL to disk can occur based on different strategies and configurations, which can vary across database systems. Some of the common approaches are as follows.</p><p><strong>Synchronous Commit:<br></strong>With synchronous commit, the database system ensures that the WAL records are flushed to disk before confirming the transaction’s commit.<br>This guarantees that the changes made by the transaction are durably stored on disk, providing a higher level of reliability.<br>However, synchronous commit can introduce additional latency as the transaction must wait for the disk I/O operation to complete before being confirmed.</p><p><strong>Asynchronous Commit:<br></strong>In asynchronous commit, the database system may delay or defer the flushing of the WAL to disk after the transaction is committed.<br>The advantage of asynchronous commit is improved performance since the transaction can be acknowledged as committed without waiting for disk I/O.<br>However, there is a small window of time during which the changes made by the transaction are stored only in the WAL and not yet persisted on disk.</p><p><strong>Conclusion:<br></strong>In this second part of our series on database internals, we explored the concept of storage manager, different type of pages, internal workings of buffer pool manager and write ahead logs. I have added all the reference materials in the references section below. If you want to explore more on the above topics, please go through the [3]reference book. In the subsequent parts of this series, we will delve further into the intricacies of database internals, uncovering more fascinating aspects of indexing, query processing, and optimization. And will surely include some cool commands/queries you can run in order to get to see these concepts being used in Database. Stay tuned for Episode2!</p><p><strong>References:</strong></p><ol><li><a href="https://www.youtube.com/watch?v=_vRG1ksPlXs">https://www.youtube.com/watch?v=_vRG1ksPlXs</a></li><li><a href="https://www.postgresql.org/docs/current/pgbuffercache.html">https://en.wikipedia.org/wiki/Translation_lookaside_buffer</a></li><li><a href="https://www.oreilly.com/library/view/database-internals/9781492040330/">https://www.oreilly.com/library/view/database-internals/9781492040330/</a></li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2f748a078d22" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Demystifying Database Internals: Part 1 — DBMS Architecture, Storage Hierarchy, Sequential and…]]></title>
            <link>https://akashcsharma.medium.com/demystifying-database-internals-part-1-dbms-architecture-storage-hierarchy-sequential-and-6e9f0adafcaa?source=rss-6ec2f960a4a4------2</link>
            <guid isPermaLink="false">https://medium.com/p/6e9f0adafcaa</guid>
            <category><![CDATA[system-architecture]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[operating-systems]]></category>
            <category><![CDATA[memory-management]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Akash C]]></dc:creator>
            <pubDate>Tue, 16 May 2023 05:53:30 GMT</pubDate>
            <atom:updated>2024-02-03T08:27:12.289Z</atom:updated>
            <content:encoded><![CDATA[<h3>DBMS Architecture, Storage Hierarchy, Sequential and Random Access, and mmap</h3><p><strong>Introduction:<br></strong>Databases are the backbone of modern applications, storing and managing vast amounts of data. Understanding the internals of a database management system (DBMS) is crucial for developers, administrators, and anyone working with data-driven applications. In this series on database internals, we will explore the fundamental concepts that power DBMS and shed light on their inner workings. In this first part, we will dive into DBMS architecture, the storage hierarchy in computers, the difference between sequential and random access, and examine the mmap function and its limitations within a DBMS context.</p><blockquote><strong>Part 1: DBMS Architecture<br></strong>At the heart of a DBMS lies its architecture, which orchestrates the storage, retrieval, and manipulation of data. A typical DBMS architecture consists of several interconnected components, each with its specific role:</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/970/1*V12S6wqQ2Y0K7PRujwhDog.png" /><figcaption>DBMS Architecture (Image taken from Database Internals: A Deep Dive Into How Distributed Data Systems Work: Book by Alex Petrov)</figcaption></figure><p><em>1. User Interface:<br></em>The user interface serves as a bridge between users and the DBMS. It allows users to interact with the database system by issuing queries, commands, and transactions.</p><p><em>2. Query Processor:<br></em>The query processor receives user queries and performs essential tasks like query optimization and execution planning. It analyzes the query, determines the most efficient way to retrieve or manipulate data, and creates an execution plan accordingly.</p><p><em>3. Storage Manager:<br></em>The storage manager is responsible for managing data storage on disk or in memory. It interacts with the file system, abstracting away the complexities of low-level I/O operations. It efficiently reads and writes data, ensuring data integrity and durability.</p><p><em>4. Buffer Manager:<br></em>The buffer manager acts as a middle layer between the storage manager and the main memory. It transfers data between the disk and memory using buffers, reducing disk I/O operations and improving performance. The buffer manager employs strategies like caching and replacement policies to optimize data retrieval.</p><p><em>5. Data Manager:<br></em>The data manager ensures data integrity and consistency within the database. It enforces data constraints, handles concurrent access, and manages transaction execution. It plays a critical role in maintaining the ACID properties (Atomicity, Consistency, Isolation, Durability) of database transactions.</p><p><em>6. Disk Space Manager:<br></em>The disk space manager handles the allocation and deallocation of disk space. It ensures efficient space utilization by managing data fragmentation and optimizing storage allocation.</p><blockquote><strong>Part 2: Storage Hierarchy and Access Speeds<br></strong>Computers employ a storage hierarchy with different levels of memory, each offering varying access speeds and capacities. Let’s explore the storage hierarchy and their corresponding access speeds:</blockquote><p><em>1. CPU Registers:<br></em>CPU registers are the fastest memory units directly accessible by the CPU. They store immediate data and instructions needed for immediate processing.</p><p><em>2. Cache Memory:<br></em>Cache memory resides close to the CPU, providing fast access to frequently used data and instructions. It acts as a buffer between the CPU and main memory, minimizing the time needed to fetch data.</p><p><em>3. Main Memory (RAM):<br></em>Main memory, or Random Access Memory (RAM), serves as the primary memory for programs and data currently being processed. It offers faster access compared to disk storage but is volatile, meaning data is lost when the power is turned off.</p><p><em>4. Solid-State Drives (SSDs):<br></em>Solid-State Drives (SSDs) are non-volatile storage devices that use flash memory technology. SSDs provide faster access speeds than traditional hard disk drives (HDDs) due to the absence of moving mechanical parts. They offer a balance between speed and storage capacity.</p><p><em>5. Hard Disk Drives (HDDs):<br></em>Hard Disk Drives (HDDs) are magnetic storage devices with rotating platters. They offer larger storage capacities but have slower access speeds.</p><p><em>6. Remote Storage:<br></em>Remote storage refers to network-based storage, such as cloud services, accessed over a network connection. The access speeds depend on the network infrastructure and latency.</p><p>The main thing to note about the storage hierarchy is their persistence. Till RAM the memory is volatile but at the same time it is byte addressable<em>(Random Access)</em>, meaning we can access any byte of data in average complexity of constant time. Whereas the non-volatile memory is block addressable<em>(Sequential Access),</em> where in assuming we have to access 1Mb of data which is stored as a contiguous block in disk, it would be faster for us to store it on disk instead of storing it in a byte addressable storage. (It’s quite arguable)</p><blockquote><strong>Part 3: Sequential Access and Random Access<br></strong>In data access patterns, two primary methods are sequential access and random access. Let’s explore the characteristics of each:</blockquote><p><strong><em>- Sequential Access:</em><br></strong>Sequential access involves reading or writing data in a linear manner, following a specific order. It is efficient when accessing data sequentially, such as during full table scans or reading data from a file in a sequential manner. Sequential access is suitable when the entire dataset needs to be processed or retrieved.</p><p><strong><em>- Random Access:</em></strong><em><br></em>Random access allows direct access to any data element, irrespective of its physical position. It leverages indexing or hashing techniques to locate specific data elements quickly. Random access is ideal when the application needs to access specific data records without scanning the entire dataset.</p><blockquote><strong>Part 4: mmap and its Limitations in DBMS[4]<br></strong>Memory-mapped files (mmap) provide a mechanism to map files directly into memory, enabling efficient read and write operations. However, in the context of a DBMS, mmap has certain limitations:</blockquote><p><em>1. Concurrent Access:<br></em>mmap lacks built-in mechanisms to handle concurrent access to the same memory-mapped file. In a multi-user DBMS environment, where multiple processes may simultaneously access the data, additional synchronization mechanisms must be implemented.</p><p><em>2. Consistency and Durability:<br></em>mmap bypasses the DBMS’s buffer management system, making it challenging to ensure data consistency and durability. The DBMS relies on the buffer manager to optimize I/O operations, maintain cache coherency, and enforce transactional guarantees.</p><p>Additionally, the use of mmap may bypass the DBMS’s write-ahead logging(WAL will explained in later parts) mechanisms, which can compromise data recovery in the event of system failures.</p><p><strong>Understanding Translation Lookaside Buffer (TLB):<br></strong>The Translation Lookaside Buffer (TLB) is a cache that stores recently accessed virtual-to-physical address translations. It speeds up memory access by reducing the overhead of address translation during memory accesses. TLB is an essential component in virtual memory systems, enhancing overall performance[2].</p><p><strong>Conclusion:<br></strong>In this first part of our series on database internals, we explored the architecture of a DBMS, the storage hierarchy in computers, the differences between sequential and random access, and discussed the mmap function and its limitations within a DBMS context. A deep understanding of these concepts lays a solid foundation for comprehending the inner workings of database systems. In the subsequent parts of this series, we will delve further into the intricacies of database internals, uncovering more fascinating aspects of data storage, indexing, query processing, and optimization. Stay tuned for Part 2!</p><p><strong>References:</strong></p><ol><li><a href="https://youtu.be/df-l2PxUidI">https://youtu.be/df-l2PxUidI</a></li><li><a href="https://en.wikipedia.org/wiki/Translation_lookaside_buffer">https://en.wikipedia.org/wiki/Translation_lookaside_buffer</a></li><li><a href="https://www.oreilly.com/library/view/database-internals/9781492040330/">https://www.oreilly.com/library/view/database-internals/9781492040330/</a></li><li><a href="https://en.wikipedia.org/wiki/Mmap">https://en.wikipedia.org/wiki/Mmap</a></li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6e9f0adafcaa" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Exploring Python’s Numpy package]]></title>
            <link>https://akashcsharma.medium.com/exploring-pythons-numpy-package-80afd2359851?source=rss-6ec2f960a4a4------2</link>
            <guid isPermaLink="false">https://medium.com/p/80afd2359851</guid>
            <category><![CDATA[python-vs-cpp]]></category>
            <category><![CDATA[arrays]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[cvspython]]></category>
            <category><![CDATA[numpy]]></category>
            <dc:creator><![CDATA[Akash C]]></dc:creator>
            <pubDate>Sat, 25 Jul 2020 09:20:06 GMT</pubDate>
            <atom:updated>2020-07-25T09:20:06.236Z</atom:updated>
            <content:encoded><![CDATA[<h4>Python</h4><p>Python has been the favorite language of all Machine Learning practitioners and Data Scientists. Python is my favorite language. I use Python for coding interview questions, for competitive coding, Machine Learning stuffs etc..</p><figure><img alt="python" src="https://cdn-images-1.medium.com/max/1024/0*MRuVZHJETGD07pld" /><figcaption>Photo by <a href="https://unsplash.com/@hiteshchoudhary?utm_source=medium&amp;utm_medium=referral">Hitesh Choudhary</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>While Python is a very flexible language it is actually easy to code in Python rather than to code in C++ or JAVA (no offence). Python being a dynamically typed language doesn’t have any compilers instead it uses an interpreter. The interpreter takes the command(code) we write to OS and executes it.</p><h3><em>Okay then why C++, JAVA, C and all exist but not only Python???</em></h3><blockquote>The simple answer to the above question is they are fast compared to Python. Yes that’s the hard truth one should accept.</blockquote><h3>Fast how?</h3><p>Python is more of a memory hog.</p><p>a) Python is interpreted, while C++ is compiled</p><p>b) Python has no primitives, everything including the builtin types (int, float, etc.) are objects</p><p>c) a Python list can hold objects of different type, so each entry has to store additional data about its type</p><h3>OKAY now why do we use Numpy while Python itself offers a Data Structure called List?</h3><blockquote>The answer lies in the documentation of the <a href="https://numpy.org/">Numpy package</a></blockquote><p>Haha jokes aside, while Python offers its own List Data Structure there is no explicit Data Structure Array in Python. We try to implement Array using the inbuilt List Data Structure. We can use it but as I already said Python is bit of a memory consumer. So we use a much better implementation of a list provided by Numpy for arrays. The Numpy package is basically C optimized. It offers the flexibility provided by Python at the same time trying to offer the performance offered by C.</p><h3>Some of the coding stuffs related to Numpy</h3><pre>import numpy as np<br>import array</pre><pre># Generate a list of numbers from 0-9 inclusive<br>pythonlist = list(range(10))<br>numpylist = np.array([0,1,2,3,4,5,6,7,8,9])<br>arraylist = array.array(&#39;i&#39;, pythonlist)</pre><pre>print(&quot;Size of python list: &quot;,pythonlist.__sizeof__())<br>print(&quot;Size of numpy list: &quot;,numpylist.__sizeof__())<br>print(&quot;Size of array list: &quot;,arraylist.__sizeof__())</pre><pre>Output:<br>Size of python list:  176<br>Size of numpy list:  136<br>Size of array list:  104</pre><p>Did you see the difference between the sizes of the array? Basically all the type of lists contains the same elements but their sizes are different.</p><pre>In[10]: np.array([1, 2, 3, 4], dtype=&#39;float32&#39;)</pre><pre>Out[10]: array([ 1., 2., 3., 4.], dtype=float32)</pre><pre>In[11]: # nested lists result in multidimensional arrays<br>        np.array([range(i, i + 3) for i in [2, 4, 6]])</pre><pre>Out[11]: array([[2, 3, 4],<br>                [4, 5, 6],<br>                [6, 7, 8]])</pre><p>In the above example we can use we have used a ‘dtype’. What is it? They are the datatypes provided by the Numpy package. Below Image describes the datatypes of Numpy.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/578/1*79ts7o00iCgcooCkqHM5Ig.jpeg" /><figcaption>Image source : Python Data Science Handbook by Jake VanderPlas</figcaption></figure><blockquote>We can concatenate arrays in Numpy quite easily. We can stack two arrays either vertically or horizontally using ‘vstack’ and ‘hstack’ functions. Numpy also provides one more function ‘dstack’ which will stack arrays along the third axis.</blockquote><pre>In[43]: x = np.array([1, 2, 3])<br>        y = np.array([3, 2, 1])<br>        np.concatenate([x, y])</pre><pre>Out[43]: array([1, 2, 3, 3, 2, 1])</pre><pre>In[48]: x = np.array([1, 2, 3])<br>        grid = np.array([[9, 8, 7],<br>                         [6, 5, 4]])</pre><pre>        # vertically stack the arrays<br>        np.vstack([x, grid])</pre><pre>Out[48]: array([[1, 2, 3],<br>                [9, 8, 7],<br>                [6, 5, 4]])</pre><pre>In[49]: # horizontally stack the arrays<br>        y = np.array([[99],<br>                     [99]])<br>        np.hstack([grid, y])</pre><pre>Out[49]: array([[ 9, 8, 7, 99],<br>                [ 6, 5, 4, 99]])</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=80afd2359851" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>