Databases are used for storing, maintaining and accessing any sort of data. They collect information on people, places or things. This information is gathered in one place so it can be observed and analyzed
A database is organized storage for data. It is where applications save and retrieve information - user accounts, product catalogs, order histories, messages, photos, transaction records. Every application that stores data uses a database.
Think of a database as a digital filing cabinet. Instead of physical folders and papers, you have tables, rows, and columns of structured data that can be queried and updated instantly.
The Problem with Files: Before databases, applications stored data in files. This created issues:
Database Solution: Databases provide structured storage with search capabilities, concurrent access, data validation, and relationship management. They are optimized for speed, reliability, and scale.
Data stored in tables with rows and columns. Tables can relate to each other through keys.
Popular SQL Databases:
Master system design through bite-sized lessons built for early-career engineers. Build scalable, bulletproof systems with hands-on projects and real-world case studies that make complex concepts click.
Master the fundamental concepts of relational databases and discover why they're the backbone of modern applications
Master how databases handle multiple transactions running simultaneously and learn to control what each transaction can see and do.
Master the art of database scaling - from simple vertical upgrades to complex horizontal architectures that power the world's largest applications.
Finally understand the difference between these two terms that everyone uses interchangeably (but shouldn't!).
Sharding and Partitioning Master the two fundamental ways to split your data and learn when to use each strategy.
Sharding and Partitioning Explore the complete matrix of database architectures from simple monoliths to complex distributed systems.
Sharding and Partitioning Example Structure:
Users Table:
| id | name | email | created_at |
|----|----------|--------------------|------------|
| 1 | Priya | priya@example.com | 2024-01-15 |
| 2 | Rohan | rohan@example.com | 2024-01-16 |
Orders Table:
| id | user_id | product | amount |
|----|---------|------------|--------|
| 1 | 1 | Laptop | 50000 |
| 2 | 1 | Mouse | 500 |
| 3 | 2 | Keyboard | 2000 |
Querying Data (SQL):
-- Get all orders for Priya
SELECT * FROM orders
WHERE user_id = 1;
-- Get user names and their order totals
SELECT users.name, SUM(orders.amount) as total
FROM users
JOIN orders ON users.id = orders.user_id
GROUP BY users.name;
Strengths:
Use Cases: Banking systems, e-commerce, any application needing complex queries and guaranteed consistency.
Non-relational databases with flexible schemas. Different types for different needs:
Document Databases (MongoDB, Firestore): Store data as JSON-like documents.
{
"id": 1,
"name": "Priya",
"email": "priya@example.com",
"orders": [
{"product": "Laptop", "amount": 50000},
{"product": "Mouse", "amount": 500}
]
}
Strengths: Flexible schema, easy to scale, good for hierarchical data Use Cases: Content management, user profiles, catalogs
Key-Value Stores (Redis, DynamoDB): Simple key-value pairs, extremely fast.
user:1 -> {"name": "Priya", "email": "priya@example.com"}
session:abc123 -> {"user_id": 1, "expires": 1234567890}
Strengths: Extremely fast reads/writes, simple model, great for caching Use Cases: Session storage, caching, real-time analytics
Graph Databases (Neo4j): Store data as nodes and relationships, optimized for connected data.
Strengths: Excellent for relationship-heavy data Use Cases: Social networks, recommendation engines, fraud detection
Column-Family Stores (Cassandra, HBase): Store data in columns instead of rows, optimized for write-heavy workloads.
Strengths: High write throughput, good for time-series data Use Cases: IoT data, log analytics, messaging systems
Create: Add new data
INSERT INTO users (name, email) VALUES ('Amit', 'amit@example.com');
Read: Retrieve data
SELECT * FROM users WHERE id = 1;
Update: Modify existing data
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;
Delete: Remove data
DELETE FROM users WHERE id = 1;
Every database supports these four fundamental operations, though syntax varies.
Normalization: Organize data to reduce redundancy. Instead of storing customer address in every order, store it once in a customers table and reference it.
Indexes: Speed up queries by creating indexes on frequently searched columns. Like a book index that helps find topics quickly without reading every page.
Primary Keys: Unique identifier for each row. Usually an auto-incrementing integer or a UUID.
Foreign Keys: Link tables together. An order is user_id references the users table is id.
Constraints: Rules that enforce data validity. Email must be unique, age must be positive, required fields cannot be null.
Instagram: Uses PostgreSQL for user data and relationships. Cassandra for direct messages. Redis for caching feeds.
Netflix: Uses MySQL for billing and account data. Cassandra for viewing history and recommendations at scale.
Uber: Uses PostgreSQL for core data. Redis for real-time location tracking. Cassandra for trip history.
Large applications often use multiple databases, each optimized for different needs. This is called polyglot persistence.
Slow Query Problem: A database with millions of records becomes slow without optimization.
Indexing: Create indexes on columns used in WHERE, JOIN, ORDER BY clauses.
CREATE INDEX idx_user_email ON users(email);
Now searching by email is instant instead of scanning millions of rows.
Query Optimization: Write efficient queries. Avoid SELECT *, fetch only needed columns, use LIMIT for large result sets.
Caching: Use Redis or Memcached to cache frequently accessed data. Check cache before querying database.
Connection Pooling: Reuse database connections instead of creating new ones for every request. Opening connections is expensive.
Vertical Scaling: Upgrade to a more powerful server (more CPU, RAM, storage). Simple but limited and expensive.
Horizontal Scaling (for reads): Add read replicas. Write to primary database, read from replicas.
Sharding (for writes): Split data across multiple databases. Users A-M on database 1, users N-Z on database 2.
Managed Services: Use cloud databases (AWS RDS, Google Cloud SQL, MongoDB Atlas) that handle scaling automatically.
ACID Properties:
Example:
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
If any step fails, the entire transaction rolls back. Money is never lost.
Use SQL (Relational) When:
Use NoSQL When:
Reality: Many applications use both. SQL for core transactional data, NoSQL for caching, analytics, or flexible data.
Authentication: Users must prove identity before accessing database.
Authorization: Control what each user can access. Admin can delete, regular user can only read.
Encryption: Encrypt data at rest (stored) and in transit (transferred over network).
SQL Injection Prevention: Never concatenate user input into queries. Use parameterized queries.
Backups: Regular backups protect against data loss from failures or attacks. Test restore procedures.
Cloud-Managed Databases:
Benefits: No server management, automatic backups, easy scaling, built-in monitoring.
Trade-offs: Less control, vendor lock-in, potentially higher costs at scale.
Database Administrator (DBA): Manages database infrastructure, ensures uptime, optimizes performance, handles backups.
Data Engineer: Builds data pipelines, designs data warehouses, integrates systems.
Backend Developer: Uses databases in applications, writes queries, designs schemas.
Database Architect: Designs database systems for large organizations, plans scaling strategies.
Databases are the foundation of modern applications. Every feature that involves storing or retrieving data depends on a well-designed, properly maintained database. Understanding databases - how to design schemas, write efficient queries, and choose the right database type - is essential for any developer building real-world applications.
Whether you are building a side project or working at a large company, databases are where your application state lives. Treat them with care, optimize them thoughtfully, and back them up religiously.