Databases Notes

Partitioning Sharding and Data Locality

EN

Large datasets can be divided so queries touch less data and maintenance works on smaller units. The terms are related but not interchangeable...

Quizes

EN

Try answering each question before opening its answer. SQL exercises identify their dialect; syntax and guarantees vary between engines...

Aws Services

EN

Choose a service by data model, access pattern, transaction requirements, and operational constraints. A keyword such as “financial,” “serverless,” or “high scale” is a clue, not a complete architecture decision. Check service availability and compatibility before designing a new deployment...

Transactions Intro

EN

A bookstore checkout changes several facts: stock decreases, an order is created, and the purchased items are recorded. Those changes belong together. An order without its items is incomplete; a stock reduction without an order loses inventory...

Consistency

EN

In ACID, consistency means that correct transactions take the database from a valid state to another valid state. A valid state satisfies the system's required rules. The database enforces declared constraints; the application must correctly implement rules that are not fully expressed by those cons...

Durability

EN

Durability means that a successfully committed transaction survives the failures covered by the database's configuration and storage assumptions. It is the reason an application can rely on a successful commit acknowledgment after a crash. It does not promise survival after every durable copy is des...

Atomicity

EN

Atomicity means that a transaction's changes are committed as one unit. If the transaction is rolled back, none of those changes remain committed. The operations still execute one after another; atomicity does not mean they take place at the same instant...

Indexing

EN

An index is an additional access path to data. It can reduce the rows and pages examined by a query, but it also consumes storage and must be maintained when indexed data changes. Start with the queries you need to support, then verify the resulting plans...

Primary Key vs Secondary Key

EN

A key expresses an identification rule. An index is a physical access structure. The two are related because engines often use indexes to enforce keys, but they are not interchangeable...

Query Optimization Techniques

EN

A query can return the correct result and still do far more work than necessary. Optimization means reducing that work while preserving the result the application needs. Start with one measured problem, not a checklist of indexes to add everywhere...

Working with Billion Row Table

EN

A billion rows is not a magic threshold at which one architecture becomes mandatory. A narrow, well-indexed billion-row table with selective queries can be easier to operate than a much smaller table with wide rows, poor access patterns, heavy churn, or global scans. Start from data size, row width...

Indexing Strategies

EN

Index access methods suit different operations. This note compares B-tree, bitmap, hash, and full-text indexes using named engines. For composite index design, covering queries, and maintenance, see Database Indexing...

Introduction to Orm

EN

An object-relational mapper (ORM) is a library that maps application objects to relational tables and generates SQL for common database operations...

Popular Orm Tools

EN

ORMs solve similar problems, but they expose very different programming models. Some are tightly integrated with a web framework, some implement a language ecosystem standard, and some deliberately stay close to SQL...

Relationship Loading and Query Performance

EN

ORM performance problems usually come from the SQL workload generated by application code: too many round trips, too many rows, large joins, missing indexes, or transactions that stay open too long...

Transactions Concurrency and Unit of Work

EN

ORMs make database access convenient, but concurrent requests still operate on a shared database. Two application workers can read the same row, make different decisions, and attempt conflicting writes...

Deadlocks

EN

A deadlock occurs when two or more transactions are waiting for one another in a cycle and none can continue unless one of the transactions is aborted or otherwise forced to release its resources...

Serializable vs Repeatable Read

EN

Imagine two requests hit an application at almost exactly the same time...

Shared vs Exclusive Locks

EN

Locks coordinate concurrent access to database resources...

Two Phase Locking

EN

Two-Phase Locking, usually abbreviated 2PL, is a concurrency-control protocol for making interleaved transactions behave like some serial execution...

Data Control Language Dcl

EN

Data Control Language (DCL) manages database privileges through statements such as GRANT and REVOKE. A privilege is permission to perform a particular operation on a particular resource...

Intro to Sql

EN

SQL, or Structured Query Language, lets you describe the data you want or the change you want to make. For example, “show Alice's orders” becomes a SELECT query; “record a new customer” becomes an INSERT statement...

Data Manipulation Language Dml

EN

Data Manipulation Language (DML) works with table data. SELECT reads rows, INSERT adds them, UPDATE changes them, and DELETE removes them. The table's structure remains in place unless a separate schema-changing command alters it...

Aggregate Functions

EN

An aggregate calculates one result from several rows. SUM adds values, COUNT counts them, and AVG calculates their average. Without grouping, one aggregate can summarize the whole input. With GROUP BY, it summarizes each group separately...

Data Definition Language Ddl

EN

Data Definition Language (DDL) defines database objects. A table definition says which columns exist, what kinds of values they accept, and which rules apply. It is different from inserting the current rows into that table...

Transaction Control Language Tcl

EN

A checkout involves more than one write. The bookstore must record an order, record its lines, and reduce stock. If an application stops between those operations, a partially recorded purchase can be misleading...

Triggers

EN

A trigger is database logic invoked automatically by a specified event. Unlike a procedure, which a caller explicitly invokes, a trigger can run because an application inserts, updates, or deletes rows...

Stored Procedures and Functions

EN

A view names a query. A stored routine names database code that can accept inputs and perform work. Two common kinds are procedures and functions. Their syntax, return values, transaction rules, and privileges depend on the engine...

Window Functions

EN

A window function calculates a value across rows related to the current row while retaining those rows in the result. Use it for rankings, running totals, and comparisons with earlier or later rows. OVER defines the partition, order, and, where applicable, frame...

Hierarchical Data

EN

A bookstore's categories form a hierarchy: Books contains Computing, and Computing contains Databases and Programming. A tree represents that structure using nodes and parent–child relationships. A root has no parent. A leaf has no children. A node's descendants are its children, their children, and...

Joins Subqueries and Views

EN

The bookstore's tables separate customers, orders, products, and order lines. A query puts the relevant facts together without permanently copying them. This note explains three tools: a join combines matching rows, a subquery uses another query inside a statement, and a view names a reusable query...

Sqlite

EN

SQLite is a self-contained, serverless, and zero-configuration SQL database engine that's known for its simplicity and efficiency. Unlike traditional databases that require a separate server to operate, SQLite operates directly on ordinary disk files. This makes it an ideal choice for small to mediu...

Clickhouse

EN

ClickHouse is a column-oriented SQL database designed for analytical workloads over large datasets. It is commonly used for...

Postgresql

EN

PostgreSQL is an open-source object-relational database maintained by the PostgreSQL Global Development Group. It supports relational queries, extensible types, and transactions for a wide range of applications...

Elasticsearch and Opensearch

EN

OpenSearch and Elasticsearch are search-oriented distributed data engines built around Apache Lucene concepts such as inverted indexes, analyzers, mappings, shards, and replicas...

Distributed Sql

EN

Distributed SQL databases keep a relational SQL model while distributing data and replication across multiple nodes...

Cassandra

EN

Apache Cassandra is a distributed wide-column database designed for high write throughput, horizontal scale, multi-node availability, and predictable key-oriented access...

Sql Server

EN

Microsoft SQL Server is a relational database engine widely used in enterprise applications, business systems, reporting, and Microsoft-centric environments. It supports transactional workloads, analytical features, mature administration tooling, and deep integration with the .NET and Azure ecosyste...

Mongodb

EN

MongoDB is a popular open-source NoSQL database management system that offers a flexible and scalable approach to data storage. Instead of using the traditional table-based relational database structure, MongoDB stores data in flexible, JSON-like documents, which means fields can vary from document ...

Mysql

EN

MySQL is a popular open-source RDBMS known for its reliability, performance, and ease of use. Developed by Oracle Corporation, it powers numerous web applications, content management systems, and enterprise solutions. Its robust architecture efficiently manages large databases, making it a top choic...

Neo4J

EN

Neo4j is a leading open-source graph database management system that specializes in handling data with complex and interconnected relationships. Unlike traditional relational databases that use tables and rows, Neo4j stores data in nodes and relationships, allowing for more natural and efficient mod...

Redis

EN

Redis is an in-memory data store built around specialized data structures such as strings, hashes, lists, sets, sorted sets, streams, JSON, geospatial indexes, probabilistic structures, and newer vector-oriented capabilities...

Indexing Strategies

EN

An index is an additional structure that helps the database find selected rows. It is comparable to a book's index: look up a topic, find its location, and then read the relevant pages. The database still has to retrieve and return the answer...

Denormalization

EN

Normalization separates facts so each has a clear home. Denormalization deliberately stores an additional copy or precomputed result to make a particular read cheaper. It creates a new responsibility: keep that copy correct, or explicitly allow it to lag...

Distributed Database Systems

EN

A distributed database system stores and processes logically related data across multiple machines, processes, availability zones, or geographic locations while trying to present an application with a coherent database abstraction...

Cap Theorem

EN

The CAP theorem describes what happens to a distributed read/write service when parts of the system can no longer communicate...

Performance Monitoring and Tuning

EN

Performance tuning should start with a measured problem...

Identity Authentication and Access Control

EN

Database security starts with answering three different questions...

Sql Injection

EN

SQL injection occurs when untrusted input changes the structure of a SQL statement...

Querying Nosql Databases

EN

There is no universal NoSQL query language. A document database, key-value store, wide-column database, and graph database expose different query shapes because they organize data differently...

Crud in Sql vs Nosql

EN

Comparing common CRUD operations in SQL (relational databases) and MongoDB (a NoSQL document store) provides valuable insights into the differences between relational and non-relational databases. Understanding these differences is crucial for developers and database administrators when designing an...

Types of Nosql Databases

EN

NoSQL databases are commonly grouped by data model: key-value, document, wide-column, and graph. These categories describe how data is represented and accessed; they do not by themselves guarantee horizontal scalability, eventual consistency, low latency, or schema flexibility. Those properties depe...

Key Value Modeling

EN

A key-value store exposes a simple idea...

Graph Modeling

EN

Graph databases model connected data explicitly...

Wide Column Modeling

EN

Wide-column databases such as Cassandra and HBase are designed around partitioned, distributed access patterns. The central modeling rule is different from a relational database...

Types of Databases

EN

The first note used tables to connect customers and orders. Other databases represent data as documents, keyed values, or graph relationships. Each representation changes what is easy to read, what must be maintained during a write, and which operations need additional structures...

Databases Intro

EN

A bookstore needs to remember its customers, books, orders, and remaining stock. At first, a spreadsheet or a CSV file might be enough. Once a website, a checkout desk, and an import job all change the same information, storing it becomes only part of the problem. The system also needs to answer que...

Database Management Systems Dbms

EN

A database is the stored data and its definitions. A database management system (DBMS) is the software that accepts requests, checks rules, chooses execution strategies, coordinates concurrent work, and manages storage and recovery. A database driver, command-line client, and cloud hosting service s...

Data Models

EN

A data model describes which facts exist, how they relate, and which rules apply. Choosing a DBMS does not automatically create that model. PostgreSQL can store an excellent design or a confused one; a MongoDB document can group a useful record or duplicate facts that are difficult to maintain...

Multi Master Replication

EN

Multi-primary replication—also called multi-master or active-active replication—allows more than one database node to accept writes...

Master Standby Replication

EN

Primary–standby replication is a replication topology in which one database server acts as the authoritative writer and one or more standby servers follow the primary's changes...

Intro to Replication

EN

Replication keeps database data on more than one node by transferring changes from one database instance to another...

Synchronous vs Asynchronous Replication

EN

The fundamental difference between synchronous and asynchronous replication is...

Spark Sql

EN

Spark SQL is Apache Spark's structured-data engine. It lets you express work with SQL or DataFrame operations while Spark distributes the computation across partitions...

Data Warehousing

EN

A data warehouse is an analytical system built to answer questions across large amounts of historical data. Operational databases are optimized for day-to-day application work such as creating an order, updating inventory, or authenticating a user. Warehouses are optimized for scans, joins, aggregat...

Data Pipelines Orchestration and Quality

EN

A data platform is more than storage and queries. Data has to move from sources to destinations reliably, transformations must run in the correct order, failures must be retried safely, and downstream users need to know whether the resulting tables are fresh and trustworthy...

Streaming Kafka and Cdc

EN

Batch systems process bounded data: "all orders from yesterday." Streaming systems process data that continues arriving: clicks, payments, sensor readings, database changes, and application events...

Data Lakes and Lakehouses

EN

A data lake stores large analytical datasets as files, usually in object storage. A lakehouse keeps that open-file storage model but adds table metadata so readers and writers can treat groups of files more like database tables...

Database Caching

EN

A cache keeps a reusable copy of data or a computed result. Reading the copy can avoid repeating a more expensive operation. The difficulty is knowing whether that copy is still appropriate for the request...

Double Booking Problem

EN

The double-booking problem is a common issue in database systems, particularly in applications like booking platforms, reservation systems, and inventory management. It occurs when multiple transactions simultaneously attempt to reserve or modify the same resource, leading to conflicts and inconsist...

Choosing Database

EN

Choosing the right database can significantly influence your project’s reliability, performance, cost, and ability to scale. A good database choice is not only about picking a popular engine; it is about matching the database to your data shape, query patterns, consistency needs, latency requirement...

Consistent Hashing

EN

Imagine you're organizing books in a vast library with shelves arranged in a circle. Each book’s position is chosen by the first letter of its title, looping back to the beginning after Z. When you install a new shelf or remove one, you’d prefer not to reshuffle every book—only a small, predictable ...

Capacity Planning

EN

Capacity planning is the strategic process of determining the necessary resources required to meet current and future demands of an application or system. It involves analyzing workloads, forecasting growth, and ensuring that the infrastructure can handle anticipated loads while maintaining optimal ...

Backup and Recovery Strategies

EN

A backup is useful only if it can restore the required data within the required time. Choose backup types and schedules against recovery point and recovery time objectives, then test restoration. Production impact matters, but a fast backup that cannot be recovered does not meet the goal...

Crash Recovery in Databases

EN

Crash recovery is an important component of database systems that ensures data consistency and durability despite unexpected events like power outages, hardware failures, or software crashes. By design, databases must be capable of returning to a reliable state after a failure occurs. This is largel...

Database Security

EN

Database security encompasses a comprehensive set of measures designed to protect database management systems against threats that could compromise their confidentiality, integrity, and availability. As databases often store sensitive and critical information, safeguarding them is important for prot...

Database Migration

EN

Database migration is the process of transferring data, schema, and database objects from one database environment to another. This complex undertaking is crucial when organizations aim to upgrade technology, shift infrastructures, enhance performance, or reduce costs. A successful database migratio...

Glossary

EN

Use this page as a reference when a term interrupts your reading. You do not need to memorize it before starting SQL. The groups follow the learning path: data, queries, transactions, storage, distributed systems, and analytics...

Clustered Indexes Heap Tables and Row Locality

EN

A table's primary key does not imply one universal physical layout...

Composite Indexes and Column Order

EN

A composite index contains more than one key column...

Btree Internals and Page Splits

EN

B-tree-family indexes are the default general-purpose index in many relational databases because they support ordered search efficiently...

How Tables and Indexes Are Stored on Disk

EN

The SQL table is a logical view of data. The engine must represent its rows, indexes, and recovery information as bytes. Understanding that representation helps explain why returning a few rows can require many reads, why indexes cost space, and why committed data can survive a crash...

Index Maintenance Bloat and Online Operations

EN

Indexes have a lifecycle...

Covering Partial and Expression Indexes

EN

A basic index answers...

Specialized Index Structures

EN

B-tree is the default general-purpose index for many relational workloads, but not every predicate is naturally ordered...

Database Pages

EN

SQL presents a table as rows and columns. Storage works with chunks of bytes. In many database engines, a page is a fixed-size unit used to organize data and transfer it between storage and memory. A table or index usually spans many pages...

Pagination and Large Result Sets

EN

A query is not finished when the database finds matching rows. The system still has to order them, transfer them, allocate client memory, deserialize them, and render/process them...

Execution Plans Statistics and Cardinality

EN

A database optimizer estimates costs from table metadata, statistics, predicates, indexes, join algorithms, and its cost model. It does not execute every possible plan and pick the fastest one...

Connection Pooling Batching and N Plus One

EN

Database performance is not only query execution time...

Write Performance Vacuum and Bloat

EN

Read performance is only half of database performance...

Accessing Database in Code

EN

Application code sits between users and the database. A request can be slow even when the SQL engine executes its statements quickly...

Benchmarking Load Testing and Capacity

EN

A performance claim without a workload definition is incomplete...

Materialized Views

EN

A materialized view stores the result of a query so readers can reuse work that has already been performed...

Database Hardening and Patch Management

EN

Hardening reduces the number of ways a database can be attacked or accidentally misused...

Auditing Compliance and Data Governance

EN

A secure database should answer...

Incident Response and Disaster Recovery Drills

EN

A security or reliability incident is the wrong time to discover that...

Encryption Secrets and Key Management

EN

Database security depends on protecting data in several different states...

Document Modeling

EN

A document database stores records as documents rather than normalized rows spread across many tables. The main modeling question is not "can this be JSON?" but...

Operating Nosql and Polyglot Persistence

EN

NoSQL systems are often introduced through data models, but production success depends just as much on operations. A database choice adds responsibilities for deployment, backups, monitoring, security, capacity, failure recovery, schema evolution, and synchronization with other systems...

Consistency Transactions and Replication

EN

Choosing a NoSQL data model does not automatically choose a consistency model. Document, key-value, wide-column, and graph describe how data is represented. Replication, transaction scope, and read/write guarantees are separate design choices...

Nosql Databases Intro

EN

NoSQL is an umbrella label for databases built around models such as documents, key-value pairs, wide-column records, and graphs. It is not one query language or one set of guarantees. Some products support transactions, joins, validation, or SQL-like queries; others expose different operations...

Isolation

EN

Isolation defines how concurrent transactions interact. The strongest common level, Serializable, makes their committed outcome equivalent to some serial execution. Lower levels permit certain anomalies to reduce coordination costs; simply wrapping statements in BEGIN and COMMIT does not make all ra...

Row Based vs Column Based Databases

EN

A table's logical shape does not determine its physical layout. Both row-oriented and column-oriented systems can expose rows and columns to SQL. The difference is which values they keep together in storage...

Schema Migrations

EN

An ORM maps application code to a database schema, but the schema changes over time. Schema migrations are versioned changes that move the database from one known structure to another...

Azure Services

EN

Azure provides several managed relational, NoSQL, cache, and distributed data services. The correct service depends on the data model, access pattern, transaction requirements, compatibility needs, recovery objectives, and team expertise...

Gcp Services

EN

Match the workload to a data model and consistency requirement before selecting a managed product. Relational transactions, large analytical scans, document queries, and cached state have different needs. Managed operations reduce some administrative work but do not remove schema design, capacity pl...

Normalization

EN

Normalization is a way to organize related facts so a change does not require editing many copies of the same information. Begin with the problems in a combined table, then use the normal forms to explain how to separate it safely...

Data Integrity

EN

Data integrity means the stored data satisfies the rules the system relies on. A constraint is a rule declared in a table's definition so the database can check it whenever relevant data changes...

Requirements Analysis

EN

Before choosing tables, find out what the application must do. A requirement is a statement about a needed behavior or rule. “Use PostgreSQL” is a technology choice; “an order must retain the price originally charged” is a requirement that the design must support...

Sharding

EN

Sharding is a method of horizontally partitioning data in a database, so that each shard contains a unique subset of the data. This approach allows a database to scale by distributing data across multiple servers or clusters, effectively handling large datasets and high traffic loads...

Partitioning

EN

Partitioning involves dividing a large database table into smaller, more manageable pieces called partitions. This method helps improve query performance because the database can access only the relevant partitions when executing queries, rather than scanning the entire table. It also simplifies dat...

Eventual Consistency

EN

Imagine a distributed system with multiple nodes—servers or databases—that share data. When an update occurs on one node, it doesn't instantly reflect on the others due to factors like network latency or processing delays. Eventual consistency requires convergence when writes stop and communication ...

Partitioning vs Sharding

EN

Partitioning divides data into pieces. Sharding is horizontal partitioning across separate database nodes, with routing to the node responsible for each key. Local table partitions can improve pruning and maintenance without distributing work across servers...

Hadoop and Hdfs

EN

Hadoop is an ecosystem for storing and processing data across many machines. Its original core components solve different problems...