Skip to main content
AIDevOps
  • Learn
  • Learning Paths
  • Practice
  • Open Source
  • Books
  • Engineering

    AI DevOpsThe full map of building and operating AI servicesLLMOpsLLM deployment · evaluation · observabilityHands-on ProjectsBuild AI Agent projects

    Knowledge

    DocsTechnical documentationBlogEngineering articlesPloggerDevelopment log feed

    Validate

    Certification3-level skills certification · coming soon
AI Models
LlamaTutorial|MistralTutorial|GemmaTutorial|DeepSeekTutorial|QwenTutorial
💾 Database
DB Intro & RoadmapCore SQLOracleMySQLPostgreSQL|MongoDBVector DB
🤖 AI Applied Development
Applied AI Intro & RoadmapHugging FaceLangChainLlamaIndexLLMOps|LangGraphMCPMulti-AgentAgent Evaluation
🧠 AI Core
AI Intro & RoadmapML FundamentalsLLM Fundamentals|Python AIC++|PyTorchTensorFlowJAX
🧠 AI Agent Development
Finance AI AgentLLM API ServerStock Investing AgentAIOps AI AgentEducation AI AgentCoding AI Agent
🌱 Spring Cloud
Spring Intro & RoadmapSpring Cloud GatewaySpring BootJava|Spring AISpring SecuritySpring BatchSpring JPA
🐳 DevOps
DevOps Intro & RoadmapLinuxDockerCI/CD|Kubernetes BasicsK8s AdvancedPrometheusGrafana
🧱 Infrastructure
Infrastructure Intro & RoadmapNginxRedis
☁️ Cloud
Cloud Intro & RoadmapAWSGCPAzureNCPCloudflare
🎨 Frontend
Frontend Intro & RoadmapJavaScriptTypeScript|ReactNext.js|VueNuxt|Electron
📱 Mobile
Mobile Intro & RoadmapKotlinAndroidFlutter
⚙️ Backend
Backend Intro & RoadmapPython BasicsFastAPIDjangoFlask|CGoGinNode.js
🧪 Testing
k6JMeternGrinder
AIDevOps

Engineering AI. From Code to Production.
An engineering learning platform for building and operating AI and AI Agents

Learn

  • All Guides
  • Learning Paths
  • AI Tutorial
  • Practice
  • Books

Resources

  • AI DevOps
  • LLMOps
  • Hands-on Projects
  • Docs
  • Blog
  • Plogger
  • Open Source
  • Certification (coming soon)

Start Here

  • AI Core Roadmap
  • AI Applied Development Roadmap
  • Spring Cloud Roadmap
  • DevOps Roadmap
  • Infrastructure Roadmap

 

  • Cloud Roadmap
  • Frontend Roadmap
  • Mobile Roadmap
  • Backend Roadmap
  • Database Roadmap
© 2026 AIDevOps. All rights reserved.
Terms of ServicePrivacy PolicySitemaptestforge.kr
  1. Home
  2. Learn
  3. Database
  4. DB Intro & Roadmap
The first guide for newcomers to databases

🧭 DB Intro & Roadmap Complete Guide

Visitors

A starting point for anyone learning databases for the first time. It covers why databases are needed, how relational (RDBMS), NoSQL, and vector DBs differ, table design and normalization, how indexes and transactions work, how to choose between Oracle, MySQL, PostgreSQL, and MongoDB, and a learning roadmap from SQL basics to operations and tuning.

  • Beginner
  • Updated Oct 1, 2026
  • ~8 min read
  • 12 sections
  • 1 code examples
🧭
The big picture of DB conceptsChoosing RDBMS vs NoSQLTable design basicsUnderstanding indexes and transactionsDB learning order

Related frameworks & environments

💾Core SQL→🐘PostgreSQL→🐬MySQL→🔴Oracle→🍃MongoDB→🧠Vector DB→

Contents

0 / 15
  1. How to use this guide
  2. Architecture diagrams
  3. Why databases are needed
  4. Map of DB types
  5. How to choose a product
  6. Table design basics
  7. Your first SQL
  8. Indexes and transactions
  9. Operations basics
  10. Before you start
  11. Step-by-step roadmap
  12. 8-week study plan
  13. Common mistakes
  14. Key terms
  15. Frequently asked questions
Contents 15 sections
  1. How to use this guide
  2. Architecture diagrams
  3. Why databases are needed
  4. Map of DB types
  5. How to choose a product
  6. Table design basics
  7. Your first SQL
  8. Indexes and transactions
  9. Operations basics
  10. Before you start
  11. Step-by-step roadmap
  12. 8-week study plan
  13. Common mistakes
  14. Key terms
  15. Frequently asked questions

How to use this guide

How to read it

Understanding DB Intro & Roadmap as a real-world workflow

A starting point for anyone learning databases for the first time. It covers why databases are needed, how relational (RDBMS), NoSQL, and vector DBs differ, table design and normalization, how indexes and transactions work, how to choose between Oracle, MySQL, PostgreSQL, and MongoDB, and a learning roadmap from SQL basics to operations and tuning. Rather than listing concepts, this guide is organized so you can follow the content in the order you have to make decisions on a real project.

Key perspective

Databases

Alongside query syntax, look at schema design, indexes, transactions, permissions, and backups from an operations perspective.

The big picture of DB conceptsChoosing RDBMS vs NoSQLTable design basicsUnderstanding indexes and transactionsDB learning order

Architecture diagrams

To make what you read stick, it helps to capture the flow as a picture first. The two diagrams below are reference maps you can keep coming back to while learning DB Intro & Roadmap.

Learning flow

Rendering diagram…

Architecture view

Rendering diagram…

Why databases are needed

When you first open DB Intro & Roadmap, the big picture matters more than individual commands. This section starts with the problems the concepts ahead were created to solve.

Could you not just store data in a spreadsheet or a file? With one user, that is fine. But it is a different story when thousands of people place orders at the same time, the data must not disappear even if the server suddenly goes down, and you have to find the row you want among hundreds of millions within 0.01 seconds.

A database (DBMS) is software that has evolved over decades to solve exactly these problems — concurrency, durability, consistency, fast search, and security. Because a large share of backend performance problems end up being DB problems, the DB is an essential skill for every server developer.
RequirementFile storageDatabase
Concurrent editsData lost to overwritesHandled safely with locks and transactions
Crash recoveryThe file is corrupted if power is lost mid-writeLog-based recovery keeps it consistent
Search speedEverything has to be read from the startFound immediately through an index
Data rulesWrong values are stored as-isBlocked by constraints (NOT NULL, UNIQUE, FK)
PermissionsPer filePer table, row, and column

Map of DB types: relational · NoSQL · special purpose

Map of DB types: relational · NoSQL · special purpose is a point where the options diverge. Using the table to compare what each approach is for and how it differs in operation makes later decisions much easier.

The days of picking just one of "SQL vs NoSQL" are over. Real services mostly keep a relational DB at the center and use purpose-fit DBs alongside it.
TypeData modelStrengthsMain productsGuides
Relational (RDBMS)Tables (rows and columns) + relationshipsConsistency, transactions, complex queriesOracle, MySQL, PostgreSQLSQL, PostgreSQL
DocumentJSON-shaped documentsFlexible schema, horizontal scalingMongoDBMongoDB
Key-ValueKey → valueExtremely fast reads and writes, caches and sessionsRedisRedis
VectorEmbedding vectorsMeaning-based similarity search (AI, RAG)pgvector, Chroma, PineconeVector DB
OtherTime series, graph, search engineOptimized for specific query patternsInfluxDB, Neo4j, Elasticsearch-

Tip

A relational DB is the recommended first DB to learn. SQL and the concepts of normalization and transactions become the reference point for understanding every other DB.

How to choose a product: Oracle · MySQL · PostgreSQL · MongoDB

How to choose a product: Oracle · MySQL · PostgreSQL · MongoDB is a point where the options diverge. Using the table to compare what each approach is for and how it differs in operation makes later decisions much easier.

Unless there is a specific reason otherwise, PostgreSQL is the safest choice for a new project. Relational features, JSON storage, and even vector search (pgvector) are all covered by one DB. If you are aiming at finance or the public sector, Oracle experience helps.
ProductTraitsWhere it is mostly usedGuide
OracleCommercial, top-tier features and stability, high costFinance, public sector, large enterprise legacy systemsOracle
MySQLOpen source, light and fast, the widest user baseWeb services, startupsMySQL
PostgreSQLOpen source, standards compliant, strong JSON and extensions (pgvector)New services, AI services, analyticsPostgreSQL
MongoDBDocument NoSQL, schema flexibilityFast-changing data structures, logs, contentMongoDB

Table design basics: entities, relationships, normalization

Table design basics: entities, relationships, normalization is a point where the options diverge. Using the table to compare what each approach is for and how it differs in operation makes later decisions much easier.

Good table design starts with "not storing the same information in two places". If the member name is also stored in the orders table, a name change has to be applied in both places, and missing one leaves the data inconsistent. The process of splitting data into tables by subject to prevent this is called normalization.
RelationshipExampleHow to implement
1:1Member ↔ detailed member profileThe PK of one side as a UNIQUE FK on the other table
1:NOne member ↔ many ordersA member FK on the N side (orders) table
N:MOrders ↔ products (many products in one order, one product in many orders)Split out a join table (order items)

Tip

Normalization is the foundation of consistency, but denormalization — deliberately allowing some duplication for read performance — is also used in practice. Normalize first, measure, and denormalize only where it is needed.

Your first SQL: create tables and query them

Here you look at Your first SQL: create tables and query them alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.

SQL is a language where you declare "what" you want, not "how". The example below creates member and order tables and totals the order amount per member. With just JOIN and GROUP BY you can write more than half of real-world queries.
first.sqlSQL
CREATE TABLE members (
  id         BIGSERIAL PRIMARY KEY,
  email      VARCHAR(255) NOT NULL UNIQUE,
  name       VARCHAR(100) NOT NULL,
  created_at TIMESTAMP    NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id         BIGSERIAL PRIMARY KEY,
  member_id  BIGINT  NOT NULL REFERENCES members(id),   -- 1:N relationship
  amount     INTEGER NOT NULL CHECK (amount > 0),
  ordered_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_member ON orders(member_id);   -- JOIN and lookup performance

INSERT INTO members (email, name) VALUES ('kim@example.com', 'Dev Kim'), ('lee@example.com', 'DB Lee');
INSERT INTO orders (member_id, amount) VALUES (1, 30000), (1, 12000), (2, 8000);

-- Order count and total per member (biggest spenders first)
SELECT m.name, COUNT(o.id) AS order_count, SUM(o.amount) AS total
FROM members m
JOIN orders o ON o.member_id = m.id
GROUP BY m.id, m.name
ORDER BY total DESC;

Indexes and transactions: the two pillars of a DB

Indexes and transactions: the two pillars of a DB is a point where the options diverge. Using the table to compare what each approach is for and how it differs in operation makes later decisions much easier.

An index is like the index of a book. Just as finding a word without one means paging through the whole book, without an index the DB reads the whole table (Full Scan). On the other hand, indexes cost write performance and storage, so create them only for conditions you query often.

A transaction is a unit that groups operations which must succeed or fail together, such as "withdraw from account A + deposit to account B". The four properties that guarantee this are called ACID.
ACIDMeaningExample
AtomicityEverything succeeds or everything is rolled backA withdrawal never happens without the deposit
ConsistencyConstraints always holdA balance never goes negative
IsolationConcurrent transactions do not interfere with each otherTwo people cannot buy the last item in stock at once
DurabilityCommitted data survives failuresThe order is kept even if the server dies right after commit

Tip

When you hit a slow query, check the execution plan with EXPLAIN first. It shows right away whether an index is used or the whole table is scanned.

Operations basics: backup, permissions, monitoring

Operations basics: backup, permissions, monitoring is a point where the options diverge. Using the table to compare what each approach is for and how it differs in operation makes later decisions much easier.

A backup only means something once "you have restored from it". Include a regular rehearsal of actually restoring from backup in your operating procedures.
AreaWhat to doIf you miss it
BackupRegular full backups + log backups, recovery rehearsalsPermanent data loss in an incident
PermissionsApplication accounts get only the tables and privileges they needThe whole DB leaks if you are breached
Connection managementTune the connection pool size, clean up idle connectionsService outage from connection exhaustion
Slow queriesCollect the slow query log and improve regularlyDB overload as traffic grows
ReplicationRead-only replicas, failover on incidentA single point of failure
Schema changesVersion them with a migration tool, change large tables onlineTable locks in production

Before you start

Before you start is a point where the options diverge. Using the table to compare what each approach is for and how it differs in operation makes later decisions much easier.

Databases are a field you can start learning with no programming experience. SQL reads much like English sentences, so many people in non-developer roles (product, marketing, data analysis) learn it too.
AreaWhatHow much
A DB install or an online practice environmentRun PostgreSQL/MySQL with DockerBasic Docker commands
SQL clientDBeaver, DataGrip, psqlRun queries and view results
Set conceptsIntersection, union, differenceThe basis for understanding JOIN
One programming language (optional)Connecting to the DB from an applicationRun queries from Python, Java, or similar

Step-by-step roadmap: from SQL basics to operations and tuning

Step-by-step roadmap: from SQL basics to operations and tuning is a point where the options diverge. Using the table to compare what each approach is for and how it differs in operation makes later decisions much easier.

StepGoalWhat to learnGuidesDone check
0. SQL basicsQuery and modify dataSELECT, WHERE, JOIN, GROUP BY, INSERT/UPDATE/DELETECore SQLWrite 10 join and aggregate queries
1. DesignTable modelingERD, normalization, constraints, relationshipsCore SQLDesign an ERD for an online shop
2. Choose a productOne RDBMS in depthData types, functions, product-specific traitsPostgreSQL, MySQL, OracleBuild your ERD as a real DB
3. PerformanceFast queriesIndexes, execution plans (EXPLAIN), query tuningPostgreSQLCompare a slow query before and after
4. TransactionsConcurrency controlACID, isolation levels, locks, deadlocksMySQLA concurrent stock deduction test
5. NoSQLUsing DBs fit for purposeDocument model, caching, key-valueMongoDB, RedisCompare response time before and after caching
6. DBs in the AI eraVector searchEmbeddings, similarity, HNSW index, hybrid searchVector DBImplement semantic search with pgvector
7. OperationsStable operationsBackup and recovery, replication, permissions, monitoringOracle, PostgreSQLA backup recovery rehearsal

8-week study plan: 1 hour a day

8-week study plan: 1 hour a day is a point where the options diverge. Using the table to compare what each approach is for and how it differs in operation makes later decisions much easier.

If you need practice data, load a CSV from an open data portal such as data.gov into your DB. Real data has missing values, duplicates, and format errors, which makes it good practice.
WeekTopicDeliverable
Week 1SELECT, WHERE, ORDER BY, functions30 practice queries on a sample DB
Week 2JOIN, GROUP BY, subqueriesSales analysis report queries
Week 3Table design, normalization, constraintsAn online shop ERD + DDL
Week 4Indexes and execution plansCompare before and after an index on 100,000 rows
Week 5Transactions and isolation levelsA concurrent order scenario experiment
Week 6MongoDB · RedisRedesign the same data as a document model
Week 7Vector DBSemantic document search with pgvector
Week 8Backup, permissions, monitoringA backup recovery rehearsal + an operations checklist

Common mistakes of DB beginners

Common mistakes of DB beginners is a point where the options diverge. Using the table to compare what each approach is for and how it differs in operation makes later decisions much easier.

UPDATE/DELETE without WHERE in particular is a classic accident that even experienced developers cause. For any work on a production DB, always open a transaction and check the number of affected rows before you commit.
MistakeProblemDo this instead
The SELECT * habitUnneeded data transferred, weaker index useName only the columns you need
UPDATE/DELETE without WHEREEvery row changed or deletedCheck the target with SELECT first, inside a transaction
An index on every columnSlower writes, wasted spaceChoose based on real query patterns
Applying a function to an indexed columnThe index cannot be usedTransform the value side of the condition, not the column side
Assembling SQL from stringsSQL injectionParameter binding
Skipping foreign keys and constraintsOrphaned data, wrong values pile upEnforce the rules at the DB level
Experimenting directly on the production DBService incidentUse a replica or a staging environment

Key terms

Key terms is a point where the options diverge. Using the table to compare what each approach is for and how it differs in operation makes later decisions much easier.

TermOne-line explanation
PK (primary key)The column that uniquely identifies each row
FK (foreign key)A column that references the PK of another table to form a relationship
DDL / DMLStructure definition (CREATE, ALTER) / data manipulation (SELECT, INSERT, UPDATE, DELETE)
ERDA diagram of tables and their relationships
NormalizationThe design process of splitting tables to remove duplication
Execution planThe plan the DB makes for the order and method of processing a query
Isolation levelThe setting for how much data concurrent transactions can see of each other
DeadlockTwo transactions stuck waiting for each other to release a lock
ReplicaA read-only copy replicated from the primary DB
ShardingHorizontal partitioning that spreads data across several DB servers

Tip

The next step is the Core SQL guide. Learn the standard SQL that applies to every product first, then dig deep into one product such as PostgreSQL.

DB Intro & Roadmap: frequently asked questions

Where should I start studying databases?

Start with relational databases and SQL. Table design, joins, indexes, and transactions are common to every product and are the baseline for understanding other kinds of databases. Then learn one real product (PostgreSQL or MySQL) and widen out to document or vector databases as needed.

Should I choose a relational database or NoSQL?

For ordinary business data where relationships between records matter and consistency is required, a relational database is the default. Choose a NoSQL store that matches when the access pattern is clear: document-shaped data whose structure changes often, simple lookups at very large scale, or caching. Many services use both, each for its purpose.

Should I choose PostgreSQL or MySQL?

Both are enough for most services. PostgreSQL has strong standard SQL support and rich extensions such as JSON, full-text search, and vector search, and MySQL has a great deal of operational experience, tooling, and people who know it. If the team already knows one, using that is the safest choice.

Next guide →Standard SQL