Databases Core Concepts

Last updated on May 6, 2025

2 min read

Atomicity - A transaction is all or nothing. It must either complete fully or not happen at all. There are no partial or intermediate results.
Consistency - The database must remain in a valid state before and after the transaction. Any transaction must transform the database from one consistent state to another.
Isolation - Concurrent transactions must not interfere with each other. Each transaction should execute as if it is the only one running, ensuring that intermediate states are not visible to other transactions.
Durability - Once a transaction is committed, its changes are permanent. These changes will survive system failures and can be recovered even if a crash occurs immediately afterward.

Importance of SQL Tuning
- Improve response time of the queries thereby improving application performance
- Reduce server load
- Optimize resource utilization
Techniques for SQL Tuning
- Indexing - properly indexing columns can significantly improve the query times by allowing the DB to find data quickly instead of going through every record.
- Updating queries - this can involve removing parts of query, subquery, eliminating unwanted conditions or columns which are of no use. Using the right kind of join type etc.
- Query Execution plans - using this to optimize the queries by identifying bottlenecks. Looking at cost estimations, index utilization, etc
- Parametrized queries and Stored Procedures - Improves performance by reusing same execution plan, instead of creating a execution plan every time for each query. Also, these improve the security by preventing SQL injection attacks, since inputs are treated as parameters and not as executable code.
- Caching - Frequently run queries can be cached to improve response times.
- Materialized Views - These views store the physical result of the view. So, materialized view can store the response of a complex query, which can then be queried for quicker response.