SQL Database Types
Begin
14 pages · ~28 min
Interactive digital-human course

SQL Database Types

Learn about different SQL database types and their use cases, suitable for developers and data professionals seeking to choose the right database.

A digital instructor presents all 14 pages. Hold “Ask” at any point and ask out loud — the answer comes from this course. No sign-up needed.

28 minFree to watchDownloads

What you’ll learn

  1. 01SQL Database Types: An Introduction and Course RoadmapWelcome. Over the next fourteen slides, we're going to build a practical map of SQL database types, so you can pick a database with confidence instead of by habit. First, we'll sort the landscape along three axes: engine family, deployment model, and SQL dialect. Next, we'll group engines into four families: self-managed, cloud-managed, distributed SQL, and embedded. Then we'll look at why this matters in daily work: dialect skills, analytics tooling, cost, and lock-in. For most new transactional projects in 2026, the sane default is a managed PostgreSQL variant, so we'll treat that as our baseline and compare everything against it. Finally, our roadmap runs from foundations, to families, to dialects, to workload fit, to selection criteria, and then hands-on practice. So let's open with the core relational concepts that still underpin every SQL engine.SQL Database Types: An Introduction and Course Roadmapciopages.comcloudtoolstack.comdsa-research.org+22 min
  2. 02Core Relational Concepts Still Underpin Every SQL EngineLet's move on to the core relational concepts that still underpin every SQL engine, from PostgreSQL to Spanner. First, tables, rows, keys, and foreign keys define the relationships between your data, and good modeling is what makes joins meaningful. Next, A C I D transactions guarantee atomicity, consistency, isolation, and durability, so a transfer either fully completes or fully rolls back, on any engine you choose. On schema design, normalize to third normal form first, and denormalize only when you can state a concrete reason, such as a measured read bottleneck. Then, constraints like NOT NULL, CHECK, UNIQUE, and foreign keys enforce correctness at the storage layer, not just in application code. Finally, indexes trade read speed for write cost, and on PostgreSQL, for example, foreign key columns are not indexed automatically. A missing foreign key index is a silent performance bug that shows up as slow joins and cascading deletes. So treat these fundamentals as the shared vocabulary across every engine. Next, we'll look at the traditional self-managed relational engines, the incumbents.Core Relational Concepts Still Underpin Every SQL Enginekindatechnical.commetabase.comstepbystepsql.com+22 min
  3. 03Traditional Relational Engines: The Self-Managed IncumbentsNext, let's look at the traditional relational engines that many teams still self-manage: PostgreSQL and MySQL. First, version and support planning. PostgreSQL 18 is the current major release, with version 18.6 as the latest minor, and PostgreSQL 19 is in beta. PostgreSQL gives each major release five years of support. MySQL has moved to calendar versioning, so you will see names like 26.10 in Early Access, alongside the 9.7 and 8.4 Long Term Support lines. In contrast, MySQL 8.0 has been in Sustaining Support since the twenty-first of April, 2026, which means no new fixes, only existing ones. And a date worth putting in your calendar now: PostgreSQL 14 support ends on the twelfth of November, 2026. Next, licensing. PostgreSQL uses a permissive BSD or MIT style license, which is friendly for embedding in products. MySQL uses GPLv2, so embedding it commercially may require a commercial license. Finally, the trade-offs. Both offer strong maturity, tooling, and hiring pools. The limits are single-primary writes, so scaling writes means sharding or moving on, plus real operational toil: backups, patching, and upgrades are yours to run. With that baseline in mind, let's turn to Cloud-Managed and DBaaS: What You Are Actually Buying.Traditional Relational Engines: The Self-Managed Incumbentsdev.mysql.comdev.mysql.comblogs.oracle.com+22 min
  4. 04Cloud-Managed and DBaaS: What You Are Actually BuyingNow let's talk about cloud-managed and DBaaS options, and what you're actually buying. When you choose a managed database service, you're mainly buying operational relief. The provider handles provisioning, patching, backups, point-in-time recovery, failover, and replica wiring. Point-in-time recovery, or PITR, means you can restore your database to a specific moment. Failover means automatic switching to a standby when the primary fails. But here's the key distinction: you still own schema design, query tuning, indexing behavior, and of course, the monthly bill. Think of it as renting a well-maintained apartment. The landlord fixes the plumbing, but you still arrange your furniture and pay rent. Next, the three major ecosystems. On AWS, you have RDS and Aurora. On Azure, Azure SQL plus PostgreSQL Flexible Server. On Google Cloud, Cloud SQL, AlloyDB, and Spanner. Cloud-native engines like Aurora and AlloyDB add custom storage layers and columnar speed while keeping PostgreSQL or MySQL compatibility. The trade-offs are real: lower operational toil versus cost, egress charges, portability concerns, and provider-specific lock-in. For small teams, managed almost always wins because it frees engineering time. Self-hosting only makes sense with a defensible reason, like strict regulatory control or specialized tuning needs. Coming up next: distributed SQL and NewSQL, and how they deliver horizontal scale with relational semantics.Cloud-Managed and DBaaS: What You Are Actually Buyingcloudtoolstack.comtech-champion.comdevtoolreviews.com+22 min
  5. 05Distributed SQL and NewSQL: Horizontal Scale with Relational SemanticsNow let's move up a level, to distributed SQL and NewSQL, where you add horizontal scale while keeping relational semantics. First, the architecture. These systems use shared-nothing nodes, and each range of data is replicated through a consensus protocol, usually Raft or Paxos. In plain terms, a majority of replicas must agree before a write commits. Now the products. Spanner is Google Cloud only, and uses TrueTime for external consistency. That gives the strongest guarantee, at the highest cost and with the most lock-in. In contrast, CockroachDB speaks the PostgreSQL wire protocol, is serializable by default, and runs on multiple clouds. TiDB speaks MySQL, and adds TiFlash for HTAP, meaning transactional and analytical queries on the same data. YugabyteDB offers the highest PostgreSQL compatibility and is Apache two point zero licensed. Next, the trade-off. PACELC reminds us that in normal operation, the daily tension is latency versus consistency: consensus round trips add a few milliseconds to writes. So when should you reach for distributed SQL? Only when writes genuinely exceed one primary, or must stay consistent across regions. Otherwise, a well-managed single-primary database is simpler and cheaper. Coming up next, we look at the other end of the spectrum, embedded and local engines with SQLite and DuckDB.Distributed SQL and NewSQL: Horizontal Scale with Relational Semanticsdesigngurus.iociopages.comcloudtoolstack.com+22 min
  6. 06Embedded and Local Engines: SQLite and DuckDBNext, let's look at embedded and local engines, specifically SQLite and DuckDB. First, the shared trait. Both are in-process, serverless, single-file databases. That means no server to run, and the database is just a library linked into your application. But these two are complements, not competitors, because each sits at a different pipeline layer. SQLite is a row-oriented engine built for transactional workloads, often called O L T P. It is fully A C I D compliant, supports write-ahead logging, or WAL mode, and allows one writer alongside multiple readers. DuckDB, in contrast, is a columnar engine built for analytical workloads, or O L A P. It can query CSV, Parquet, and JSON files directly, with no load step. Practically, DuckDB often aggregates a million rows ten to two hundred times faster than SQLite, because it reads only the columns it needs. The important boundary: neither replaces a multi-writer server. SQLite serializes writes, and DuckDB allows a single read-write process. Moving on, let's cover SQL dialects and compatibility, and how to read and port queries.Embedded and Local Engines: SQLite and DuckDB1 min
  7. 07SQL Dialects and Compatibility: Reading and Porting QueriesNow let's talk about something you will run into constantly: SQL dialects. The ANSI and ISO standard exists, but no vendor implements it fully. Every database extends the core language, and those extensions tend to stick around in your codebase. So here are the differences that matter most when you read or port queries. First, row limiting. MySQL and PostgreSQL use LIMIT. SQL Server uses TOP. Oracle historically used ROWNUM, and now supports FETCH FIRST. Next, string concatenation. PostgreSQL and Oracle use the double pipe operator. SQL Server uses the plus sign. MySQL prefers CONCAT. Then NULL fallback. COALESCE is the standard and works almost everywhere, but MySQL also has IFNULL, SQL Server has ISNULL, and Oracle has NVL. Pause here, because this one is worth remembering. Two type and constraint gotchas. Oracle's DATE type stores both date and time, unlike other databases where DATE is date only. And MySQL silently ignored CHECK constraints before version eight point zero point sixteen. In practice, prefer ANSI forms like COALESCE and FETCH FIRST. But always verify collation and casting behavior manually, because identical syntax can still produce different results. Next, we look at matching engines to workloads in Workload Fit: OLTP, OLAP, and HTAP Explained.SQL Dialects and Compatibility: Reading and Porting Queriesdev.mysql.comdev.mysql.comblogs.oracle.com+22 min
  8. 08Workload Fit: OLTP, OLAP, and HTAP ExplainedNow let's match engines to workload shape, because this is a decision you will actually make. First, OLTP: many short transactions touching a few rows by key, with a latency target of one to ten milliseconds. Think charging a card or updating one balance. In contrast, OLAP runs few queries that scan millions to billions of rows, taking seconds to minutes, like revenue by country for the last quarter. Storage follows the access pattern. Row stores keep whole records together for fast point access. Columnar stores keep each column together, so a wide scan reads far fewer bytes, often running aggregation up to forty-six times faster. Remember: access pattern, not data size, decides the engine. A ten gigabyte table full of GROUP BY queries belongs in a column store. Next, HTAP, which serves both workloads from one system. It works below roughly one hundred gigabytes of warm analytical data. Beyond that, separate engines connected by change data capture usually win. And co-locating analytics has a cost: in one measured test, the worst-case write moved from thirty-eight milliseconds to three hundred thirty-two milliseconds. So watch your latency tail, not your average. Analytical Engines and the Composed Data Stack.Workload Fit: OLTP, OLAP, and HTAP Explaineddesigngurus.iociopages.comcloudtoolstack.com+22 min
  9. 09Analytical Engines and the Composed Data StackNow let's look at analytical engines and the composed data stack. First, cloud columnar warehouses such as Redshift, BigQuery, Snowflake, Databricks SQL, and ClickHouse all share one idea: columnar storage, which keeps each column's values together so scans and aggregations read only the columns they need. Next, the 2026 default architecture is an OLTP system plus a separate columnar OLAP system, joined by change data capture, or CDC, which streams row-level changes with latency under ten seconds. Zero-ETL features like Aurora to Redshift or Fabric Mirroring make that pipeline operationally cheap. In contrast, converged HTAP stays a compromise, because storage, I/O, and latency pull in opposite directions. The same split repeats at local scale with SQLite for row-based transactions and DuckDB for columnar analytics. The practical rule: above roughly one hundred gigabytes of warm analytical data, split into dedicated engines. Next, we'll compare deployment models and serverless options.Analytical Engines and the Composed Data Stackcloudtoolstack.comtech-champion.comdevtoolreviews.com+22 min
  10. 10Deployment Models and Serverless Options ComparedLet's move on to deployment models and serverless options. There are five common shapes to choose from: self-hosted, a cloud virtual machine, a managed instance, serverless, and embedded in-process. First, be clear that a database on a cloud virtual machine is not managed. Patching, backups, and failover are still yours. Next, the serverless options behave quite differently. Aurora Serverless version two scales between half and one hundred twenty-eight capacity units, with no scale-to-zero and no cold start, so it stays warm but always carries a minimum cost. In contrast, Azure SQL Serverless auto-pauses when idle, then takes about one minute to resume. Spanner has a floor of one hundred processing units, roughly ninety cents an hour, so it never goes to zero. Then match your billing model to your traffic shape: per-instance for steady load, scale-to-zero for spiky or development workloads, per-request, or resource-based. And remember, for chatty applications, egress and cross-zone transfer often exceed compute. Let's turn now to selection criteria and a practical decision framework.Deployment Models and Serverless Options Comparedcloudtoolstack.comtech-champion.comdevtoolreviews.com+21 min
  11. 11Selection Criteria and a Practical Decision FrameworkSo, how do we actually decide? Weigh candidates across five dimensions: workload, scalability, consistency, operations, and cost. Workload means your read and write patterns. Scalability is how the database grows, vertically or horizontally. Consistency covers transaction guarantees. Operations is your day two toil. And cost is total cost of ownership, not just the sticker price. For most new systems in 2026, start with managed PostgreSQL, and make every alternative argue you off it. Four questions change the answer. First, do you need multi-record transactions? If so, stay relational. Next, are analytical scans a real share of the workload? Plan a columnar store beside it. Does an AI retrieval path exist? Start with pgvector. And does write volume genuinely exceed a single primary? Only then consider distributed SQL. Deviate only for concrete reasons: document-shaped data, global writes, license blocks, or an existing Oracle estate. Finally, always build the exit: standard SQL and wire compatibility preserve portability. Keep the default, demand evidence for the exception. Let's see this framework applied in real scenarios.Selection Criteria and a Practical Decision Frameworkciopages.comcloudtoolstack.comdsa-research.org+22 min
  12. 12Scenario Walkthroughs: Startup, Analytics Team, and EnterpriseLet's ground all of this in real scenarios. First, an early-stage product under one hundred gigabytes. Managed PostgreSQL on any major cloud is the right call here. Don't over-optimize. The engineering time you save on operations matters far more than squeezing out a few dollars of infrastructure cost. Next, a mid-scale product with analytics. A common pattern is Postgres for transactions, a columnar engine fed through change data capture for analytics, and Redis for caching. Watch your egress and cross-zone transfer costs, because those often exceed compute charges. For enterprise governance, think polyglot persistence: a portfolio of approved databases with one clear system of record and paved roads for each option. Global active-active writes are a big step up. Distributed SQL like Spanner, CockroachDB, or Aurora DSQL, or sharded MySQL, only when genuinely justified. And for regulated or air-gapped environments, Azure SQL Managed Instance or self-managed PostgreSQL with real platform expertise are your practical paths. Let's now try running the same query across engines in our hands-on practice.Scenario Walkthroughs: Startup, Analytics Team, and Enterpriseciopages.comcloudtoolstack.comtech-champion.com+22 min
  13. 13Hands-On Practice: Running the Same Query Across EnginesNow let's get hands on. The fastest way to feel these dialect differences is to run the same query across engines. For a sandbox, you have easy options: DuckDB through pip install, SQLite directly in Python, PostgreSQL in a container, or a cloud free tier. First, load one dataset into both SQLite and DuckDB, then compare GROUP BY and window-function timings. You will see DuckDB's columnar storage and vectorized execution pull ahead sharply on aggregations, often by an order of magnitude, while SQLite stays strong on small transactional work. Next, practice porting a PostgreSQL query. Swap LIMIT for SQL Server's TOP, use plus instead of the double pipe for concatenation, and replace NOW with GETDATE on SQL Server and SYSDATE on Oracle. In contrast to that syntax translation, the more important habit is running EXPLAIN ANALYZE before shipping. Watch for sequential scans on large tables, stale statistics, and large nested loops. And here is your practical decision rule: if a GROUP BY on your transactional primary takes seconds, move that report to a columnar engine. That single habit prevents most production slowdowns. Coming up next, we cover common pitfalls and best practices for each audience.Hands-On Practice: Running the Same Query Across Engineskindatechnical.commetabase.comstepbystepsql.com+22 min
  14. 14Common Pitfalls and Best Practices for Each AudienceSo let's close with the mistakes that show up again and again, and the habits that prevent them. First, in application code, never use SELECT star. Name the columns you need, because SELECT star breaks when the schema changes and pulls far more data than necessary. Next, remember that WHERE filters rows before grouping, while HAVING filters aggregated groups. If your condition uses COUNT, SUM, or AVG, it belongs in HAVING. Then, handle NULL carefully. Compare with IS NULL, never with an equals sign, and remember that COUNT star counts all rows while COUNT of a column ignores NULLs. For money, always use DECIMAL or NUMERIC, never FLOAT, because floating point introduces rounding errors. Model many to many relationships with junction tables, not comma separated text columns. On performance, index your foreign keys, since PostgreSQL does not do this automatically, and avoid wrapping indexed columns in functions, which prevents index use. Finally, on operations and security: use parameterized queries, apply least privilege, set statement timeouts, and create indexes concurrently in production so you don't lock tables. That wraps up our tour of SQL database types. Thank you for learning with me, and I encourage you to apply just one of these practices in your next query.Common Pitfalls and Best Practices for Each Audiencekindatechnical.commetabase.comstepbystepsql.com+22 min

Take the deck with you

Download this course as a file — free, no sign-up needed.

Free to use in your own training — please keep the PersonWise credit page at the end.

Have your own deck? Turn it into a course

Sources consulted

Web sources consulted while building this course.