Sefism Choosing the Right Database Roadmap

What this roadmap is

Choosing the right database is Sefism's short, nine-stage path for students and developers who keep hearing about SQL, NoSQL, Redis, vector databases and warehouses and want to know which one their project actually needs. It does not try to make you an expert in ten products. It teaches you to describe your data first, then walks through each database type with what it is good at, what it costs you, and one small hands-on task, so the choice comes from your data instead of from whatever is popular this month.

By the end you should be able to:

• describe any app's data by its shape, how it is read and written and how correct it must be, before naming a product
• explain why a relational database such as PostgreSQL is the usual default, and use JSON columns for the parts that do not fit a fixed shape
• say when a document store, a key-value store or a cache is the better fit, and what you give up with each
• add search and vector similarity to an app, starting inside PostgreSQL and knowing when a dedicated engine is worth it
• tell OLTP from OLAP, know where graphs, time series and files belong, and defend a written database decision for a real project

Who it suits, and who should start elsewhere

It suits students planning a final year project, developers starting a new app or side project, and anyone who has been told to "just use MongoDB" or "add a vector database" and wants to know whether that is right. It also helps freelancers who need to explain a choice to a client in plain words.

It is a decision roadmap, not a deep course on any one database. If you want to learn SQL properly, from queries to query plans, take SQL and databases. For document modelling, indexes and aggregation in depth, take MongoDB. For building retrieval and RAG features end to end, AI engineering goes much further than the vector stage here, and Data engineering picks up where the warehouse stage stops.

Time and pace

The estimate is about 58 hours across 9 stages, with no editions: everyone follows the same path. How that turns into weeks depends on the hours you set on your dashboard:

• A student with about 8 hours a week finishes in roughly 7 weeks. A sensible week is one stage: two evenings on the topics and one weekend block on the hands-on task, using the same project throughout.
• Someone working full time with about 5 hours a week should plan for around 12 weeks. One evening on the topics and a longer weekend session on the task works well.

The first stage is a foundation stage: if you already describe data by access patterns and consistency needs, it is skipped or becomes a short review. Prerequisites are light: you can write a little code in any language and run a database locally or with Docker. Free tiers and local tools cover every hands-on task.

Stage by stage

1. Start from the data, not the product (5 hours)

Structured, semi-structured and unstructured data, access patterns (the questions your app asks), reads, writes and how much data, how correct the data must be and how fast, the questions to ask before naming a product, and SQL versus NoSQL without the myths. Milestone: a one-page data brief for a project you care about, with data shapes, the top five access patterns, rough sizes, which data must be exact and any search, similarity or reporting needs.

2. Relational databases: the sensible default (8 hours)

What a relational database gives you, why PostgreSQL is usually the right default, when MySQL, SQLite and SQL Server fit, JSON columns for the flexible parts, and drivers, ORMs and migrations. Milestone: a PostgreSQL or MySQL database with three related tables, keys and constraints, a JSON column, and saved queries for a join, a JSON filter and a daily total.

3. Document databases: MongoDB and Firestore (6 hours)

The document model, embed or reference, MongoDB and Firestore in brief, and why "schemaless" still needs a schema. Milestone: the same small shop modelled in a document database, with embedded order items, an aggregation for daily totals and a written comparison with the SQL version.

4. Key-value stores and caching (6 hours)

The key-value model, Redis and its data types, cache-aside, keeping a cache honest, and when a store such as DynamoDB is the primary database rather than a cache. Milestone: one slow endpoint cached with a TTL and invalidated on writes, with before-and-after timings, a working Redis rate limiter, and an app that still runs when the cache is down.

5. Search: from Postgres full-text to search engines (7 hours)

Why LIKE is not search, inverted indexes and relevance, PostgreSQL full-text search, Elasticsearch, OpenSearch, Meilisearch and Typesense, and keeping a search index in sync. Milestone: the same dataset searchable in PostgreSQL and in Meilisearch or Typesense, tested with ten real queries including typos, with one facet and a written recommendation.

6. Vector databases for AI features (8 hours)

Embeddings and similarity in plain words, approximate nearest neighbour indexes, pgvector inside PostgreSQL, dedicated vector databases such as Qdrant, Pinecone, Chroma, Weaviate and Milvus, metadata filters and hybrid search, and when pgvector is enough. Milestone: semantic search over at least 200 of your own texts in Chroma and in pgvector, with filters, a hybrid keyword-plus-vector version and results on ten fixed test questions.

7. Columnar databases and warehouses (6 hours)

OLTP versus OLAP, why columnar storage is fast for analytics, BigQuery, Snowflake and Redshift, ClickHouse and DuckDB, and getting app data into analytics. Milestone: one report run on PostgreSQL with millions of generated rows, on a Parquet file with DuckDB and in the BigQuery sandbox, with timings and a note on when each fits.

8. Graph, time-series and object storage (7 hours)

Graph databases and Neo4j, time-series data, retention and roll-ups, object storage for files, and what Postgres already covers. Milestone: a Cypher friends-of-friends query next to its SQL version, hourly averages with a retention policy on TimescaleDB, and a private bucket serving a file through a presigned URL, with cloud resources cleaned up afterwards.

9. Choosing, combining and running it (5 hours)

Managed or self-hosted, the matching services on AWS, Azure and Google Cloud, free tiers to learn on, what drives database cost, why one Postgres is often enough, Firebase versus Supabase, and a decision checklist you can defend. Milestone: a written database decision for your own project: the data brief, the database for each job with reasons and rejected alternatives, hosting, free tier, cost lever, backups and an export plan.

Projects that fit

The roadmap suggests five final year project ideas where the database choice is a real part of the work:

• URL shortener with click analytics and a load test report: Postgres for links, Redis for hot redirects and an analytics store for clicks, each for a clear reason
• Multi-vendor product catalogue with faceted search: varied product attributes, where documents, JSON columns and a search engine can be compared on one dataset
• Retrieval assistant for university regulations: chunking, embeddings, pgvector or a vector database, filters and hybrid search on a real retrieval feature
• IoT sensor readings store with bucketed time series: retention and roll-ups, with the same readings tried on TimescaleDB to compare
• Batch ELT pipeline with Airflow, dbt and a cloud warehouse: the OLTP to OLAP step from stage 7, done properly

How it works on Sefism

Everything on Sefism is free to learn from. The public page at Choosing the right database shows the full stage list, with the first two stages open: their topics, milestones and recommended material. Later stages show their title, summary, hours and topic count.

To open every stage, add the roadmap to your dashboard. Adding a roadmap uses credits, new members get starting credits when they join, and your first roadmap is free. Once it is on your dashboard you can:

• open every stage, its topics, checks and recommended material
• tick stages off and track your progress, at a pace set by the hours you have each week
• keep the companion Choosing the right database cheat sheet, which comes with the roadmap
• ask Tauseef a question about your own project's choice, or book a 1:1 session if you want to talk it through

If you are new, create a free account first. Credits are there so the platform goes to people who are actually learning, and if you run out while learning seriously, you can ask for more.

The companion cheat sheet

The Choosing the right database cheat sheet has a decision chart that turns your data and access patterns into a database type, one at-a-glance card per type (use it when, avoid it when, example products, managed options), and tables of the matching managed services on AWS, Azure and Google Cloud. Keep it open whenever you start a new project.

Related on Sefism

• Which final year project helps you get a job?
• Which AI topics are worth learning: agents, RAG, MCP or LLMs?
• Sixteen things that break a backend
• Library picks used in the stages: PostgreSQL documentation, Redis documentation, pgvector, DuckDB documentation, Grokking Relational Database Design and Modeling and Designing Data-Intensive Applications
• Related roadmaps: SQL and databases, MongoDB, Backend development, Data engineering

Frequently asked questions

Should I use SQL or NoSQL? Start from your data, not the label. For most apps a relational database such as PostgreSQL is the right default, with JSON columns for flexible parts. A document database fits when records really are document-shaped and read whole, or when Firestore's real-time and offline features save you real work on a mobile app.

Do I need a vector database for an AI feature? Often not at first. If you already use PostgreSQL, pgvector covers most early apps, and answer quality comes more from chunking, filters and a fixed set of test questions than from the database.

When should I add Redis? When you can name the slow thing and measure it. A cache must always be rebuildable from the main database, so it never holds the only copy of important data.

How long does it take? About 58 hours: roughly 7 weeks at 8 hours a week, or 12 weeks at 5 hours a week.

Do I need to pay for cloud accounts? No. Every hands-on task can run locally or on a free tier. The roadmap's advice is to set a billing alert before you create anything on a cloud account and delete test resources the same day.