How to design a Database Management System [System Design Sundays]
The important components of a DBMS explained
Hey, it’s your favorite cult leader here 🐱👤
On Sundays, I will go over various Systems Design topics⚙⚙. These can be mock interviews, writeups by various organizations, or overviews of topics that you need to design better systems. 📝📝
To get access to all my articles and support my crippling chocolate milk addiction, consider subscribing if you haven’t already!
p.s. you can learn more about the paid plan here.
How can you design a database management system (DBMS)?
And what are the components required to make a DBMS function?
What even is a DBMS? Does Excel Count as a DBMS?
I have no doubt that in a world racked with climate change, inflation, and mass layoffs, these are the burning questions that keep you up at night. The truly important questions in life, that we must all think about at all times.
Worry not, I could hear you crying out for answers. So I have arrived, and we will be covering this very topic today.
Important Points
What is a Database Management System- A DBMS is a tool to interact with your data. In technical words, it provides an abstraction that you can use to store and retrieve data. This way, you don’t directly have to deal with your databases.
How a Database Management System works- You can use a Query Language to interact with the DBMS. DBMSs also provide guarantees for performance.
Guarantees provided by a Database Management System- A DBMS can provide guarantees like -
durability -you won’t lose your data if the database server crashes),
strong consistency (for distributed databases - once you write some data to the database, all subsequent reads will return that value and you won’t get “stale” data)
read/write speeds.
Look over the post on the CAP Theorem and tradeoffs for more. Depending on your needs, you would choose different systems.
As mentioned, there is a lot of variation in the different DBMSs. However, there are some recurring motifs and general principles in their design that we can build up from. The rest of this post will be going into more detail on this topic.
Database management systems can serve different purposes: some are used primarily for temporary hot data, some serve as a long-lived cold storage, some allow complex analytical queries, some only allow accessing values by the key, some are optimized to store time-series data, and some store large blobs efficiently.
The Operation of a DBMS
Database Management Systems use a client/server model. To learn more about this, check out the following post. Your app is the client and the DBMS acts as the server.
At the top, we get the Transport system. It is used by the DBMS to accept client requests (queries). The transport subsystem hands the query over to a query processor, which parses, interprets, and validates it. Once our DBMS understands the query, access control checks are performed (these can only be done after the query is interpreted).
The first part of the Query Processor is the Query Parser. It parses the client’s query to ensure that the query is valid. After, the parsed query is passed to the Query Optimizer. This will just find the most efficient way to handle queries. Read the following para to understand how it works-
The parsed query is passed to the query optimizer, which first eliminates impossible and redundant parts of the query, and then attempts to find the most efficient way to execute it based on internal statistics (index cardinality, approximate intersection size, etc.) and data placement (which nodes in the cluster hold the data and the costs associated with its transfer). The optimizer handles both relational operations required for query resolution, usually presented as a dependency tree, and optimizations, such as index ordering, cardinality estimation, and choosing access methods.
-Petrov
The Optimizer creates an Execution Plan that gets passed on to the Execution Engine. The execution can be either Remote (data is stored on a different machine) or Local(data that is stored locally).
The storage engine has several components with dedicated responsibilities:
Transaction manager- Schedules transactions and ensures they cannot leave the database in a logically inconsistent state.
Lock manager- Locks on the database objects for the running transactions, ensuring that concurrent operations do not violate physical data integrity.
Access methods (storage structures)- These manage access and organize data on disk. Access methods include heap files and storage structures such as B-Trees (see “Ubiquitous B-Trees”) or LSM Trees (see “LSM Trees”).
Buffer manager- This manager caches data pages in memory (see “Buffer Management”).
Recovery manager- This manager maintains the operation log and restoring the system state in case of a failure (see “Recovery”).
Together, transaction and lock managers are responsible for concurrency control (see “Concurrency Control”): they guarantee logical and physical data integrity while ensuring that concurrent operations are executed as efficiently as possible.
To anyone interested in learning how to design a database, I would suggest the following video by FreeCodeCamp. It’s a bit long, but you can jump around the timestamps to focus on whatever you want.
That is it for this piece. I appreciate your time. As always, if you’re interested in reaching out to me or checking out my other work, links will be at the end of this email/post. If you like my writing, I would really appreciate an anonymous testimonial. You can drop it here. And if you found value in this write-up, I would appreciate you sharing it with more people.
For those of you interested in taking your skills to the next level, keep reading. I have something that you will love.
Upgrade your tech career with a premium subscription ‘Tech Made Simple’! Stay ahead of the curve in AI, software engineering, and tech industry with expert insights, tips, and resources. 20% off for new subscribers by clicking this link. Subscribe now and simplify your tech journey!
Reach out to me
Use the links below to check out my other content, learn more about tutoring, reach out to me about projects, or just to say hi.
If you like my writing, I would really appreciate an anonymous testimonial. You can drop it here.
To help me understand you fill out this survey (anonymous)
Check out my other articles on Medium. : https://rb.gy/zn1aiu
My YouTube: https://rb.gy/88iwdd
Reach out to me on LinkedIn. Let’s connect: https://rb.gy/m5ok2y
My Instagram: https://rb.gy/gmvuy9
My Twitter: https://twitter.com/Machine01776819