DEV Community

Cover image for Database notes #1
Elvin Seyidov
Elvin Seyidov

Posted on • Edited on

Database notes #1

1️⃣ Fundamentals of Databases
What is a database?

RDBMS vs NoSQL (when to choose which)

ACID vs BASE

Transactions and Isolation levels

OLTP vs OLAP

2️⃣ SQL & Relational Databases (PostgreSQL, MySQL, SQLite)
🔸 SQL Basics
SELECT, INSERT, UPDATE, DELETE

WHERE, ORDER BY, LIMIT, OFFSET

GROUP BY, HAVING

Joins: INNER, LEFT, RIGHT, FULL OUTER

Subqueries and CTEs (WITH)

🔸 Schema Design
Tables, columns, primary/foreign keys

Normalization (1NF, 2NF, 3NF)

Indexes (B-Tree, GIN, BRIN)

Constraints: UNIQUE, CHECK, NOT NULL

ENUMs, composite keys

🔸 PostgreSQL-Specific Features
JSONB fields & indexing

Array fields

Full-text search

Custom data types

Upserts (ON CONFLICT)

Window functions

Materialized views

Extensions (pgcrypto, uuid-ossp, postgis, etc.)

🔸 Performance & Optimization
Query plans and EXPLAIN

Index types and when to use

Vacuum and autovacuum

Connection pooling

Partitioning and sharding

3️⃣ NoSQL Databases
🔸 Overview & Comparison
Document, Key-Value, Graph, Columnar

When to use NoSQL over RDBMS

CAP theorem

4️⃣ MongoDB (Document-Oriented NoSQL)
🔸 Core Concepts
Documents & Collections

BSON format

Schema-less design (vs schema validation)

🔸 CRUD in Mongo
find(), insertOne(), updateOne(), deleteOne()

Query operators ($gt, $in, $or, etc.)

Projection, sorting, pagination

🔸 Advanced Features
Aggregation framework (equivalent to SQL’s GROUP BY)

Indexing

Transactions in MongoDB (v4.0+)

Data modeling patterns (embedded vs referenced)

🔸 MongoDB Atlas & Cloud Tools
Backups & replication

Triggers and functions

Monitoring & performance

5️⃣ Migrations & Schema Management
🔸 SQL Tools
Manual migrations vs migration tools (Alembic, Flyway, Liquibase)

Django ORM migrations (makemigrations, migrate)

🔸 MongoDB
Schema evolution strategies

Versioning documents manually

6️⃣ Security & Best Practices
SQL injection & query safety

Proper use of parameterized queries

Role-based access control (RBAC)

Encrypting sensitive data (at rest & in transit)

Auditing & logging

7️⃣ Backup, Restore, Replication & Scaling
Backups (logical vs physical)

PITR (Point-in-time recovery)

Master-slave replication

Multi-master, read replicas

Sharding strategies

High availability setups (e.g., Patroni for Postgres)

8️⃣ Advanced Querying & Tools
Query tuning and profiling

Using ORMs (Django ORM, SQLAlchemy) vs raw SQL

Database GUI tools (DBeaver, pgAdmin, Compass for Mongo)

Writing reusable views and stored procedures

Using EXPLAIN ANALYZE, profiling slow queries

9️⃣ DevOps & Database in Production
Environment-specific configs (local, staging, prod)

Connection pooling (PgBouncer)

Using .env securely

DB monitoring tools (pg_stat_statements, Mongo Profiler)

🔟 Data Integrity & Validation
Application-level vs DB-level validation

Triggers & stored procedures for enforcing logic

Using Django or Pydantic with validation layers

🧭 Suggested Note Series
Note # Topic
DB Notes #1 RDBMS vs NoSQL – Core Concepts
DB Notes #2 SQL Deep Dive (with PostgreSQL)
DB Notes #3 MongoDB for Backend Devs
DB Notes #4 Indexing, Performance & Query Optimization
DB Notes #5 Secure & Scalable Database Architecture
DB Notes #6 Real-World Modeling: Blog, Shop, Game, etc.
DB Notes #7 Migrations, Backups, and DevOps Practices

Top comments (0)