On this page

What Are Databases?

12 min read TextCh. 1 — Database Fundamentals

What Is a Database?

A database is an organized collection of structured information stored and accessed electronically. Databases are the backbone of virtually every modern application — from social networks and e-commerce platforms to banking systems and healthcare records.

But a database is more than just a file full of data. It is managed by a Database Management System (DBMS), which provides the tools and guarantees needed to store, retrieve, update, and delete data safely and efficiently, even when thousands of users are accessing it simultaneously.

Think of a database as a highly sophisticated filing cabinet. Unlike a plain file or spreadsheet, a DBMS ensures that:

  • Data is stored consistently and without contradictions
  • Multiple users can read and write simultaneously without corrupting data
  • You can query millions of records in milliseconds
  • Data survives power failures and hardware crashes
  • Access is restricted to authorized users only

Relational vs. NoSQL Databases

There are two broad families of databases you will encounter:

Relational Databases (SQL)

Relational databases organize data into tables — rows and columns — and use SQL (Structured Query Language) to query that data. Tables are linked together through keys, allowing complex relationships to be expressed and queried efficiently.

Examples: PostgreSQL, MySQL, SQLite, Oracle, SQL Server.

Strengths:

  • Strong consistency and data integrity
  • Powerful querying with JOINs and aggregations
  • Mature ecosystem with decades of tooling
  • ACID transaction support built-in

NoSQL Databases

NoSQL databases break from the relational model and come in several varieties:

Type Example Best For
Document MongoDB Flexible JSON records
Key-Value Redis Caching, sessions
Column-family Cassandra Write-heavy time series
Graph Neo4j Complex relationship traversal

NoSQL databases often trade consistency for speed and scalability. They are a great choice when your data does not fit neatly into tables, or when you need extreme write throughput across many servers.

The key insight is that relational and NoSQL databases are complementary, not competing. Many production systems use both — PostgreSQL for core business data and Redis for caching, for example.

The ACID Properties

The reason relational databases are trusted with financial transactions, medical records, and other critical data is their support for ACID transactions. Understanding ACID is fundamental to understanding why databases exist.

Atomicity

A transaction is all or nothing. If you are transferring money between two bank accounts and the first UPDATE succeeds but the second fails, the database rolls back the entire operation — the money is neither deducted nor added. No partial states are ever persisted.

Consistency

A transaction brings the database from one valid state to another. If you define a rule that account balances cannot be negative, the database will enforce that rule and reject any transaction that would violate it. The database is always in a consistent, valid state.

Isolation

Concurrent transactions do not interfere with each other. If two users are modifying the same record at the same time, the database serializes the operations so the result is as if they ran one after the other. The exact behavior depends on the isolation level configured.

Durability

Once a transaction is committed, it stays committed — even if the server crashes immediately afterward. PostgreSQL writes committed data to the Write-Ahead Log (WAL) on disk before acknowledging success, so your data is safe.

Why PostgreSQL 18?

PostgreSQL (often called "Postgres") is the world's most advanced open-source relational database. It has been in continuous development since 1986 and powers some of the world's largest applications.

PostgreSQL 18 brings notable improvements:

  • Asynchronous I/O: significantly faster read and write operations through parallel I/O processing
  • Logical replication improvements: more flexible and reliable data streaming between instances
  • Better query parallelism: the query planner uses more cores automatically for large analytical queries
  • Enhanced EXPLAIN: clearer output for understanding query execution plans
  • JSON improvements: even better support for semi-structured data alongside relational data

Beyond the new features, PostgreSQL's enduring strengths make it the right database to learn:

Full SQL compliance: PostgreSQL implements the SQL standard more completely than any other open-source database, so what you learn transfers directly to any SQL environment.

Extensibility: You can create custom data types, operators, index methods, and procedural languages. PostGIS turns PostgreSQL into a full geographic database. TimescaleDB turns it into a time-series engine.

Reliability: PostgreSQL has a near-perfect track record for data safety. Its crash recovery mechanisms are among the most thoroughly tested in the industry.

Performance: Modern PostgreSQL can handle millions of transactions per second and analytical queries over billions of rows, especially with proper indexing and query tuning.

Community: A vibrant open-source community releases major versions annually, with five-year support windows and regular patch releases.

Databases in Modern Architecture

Understanding where databases fit in a modern application stack helps contextualize what you are learning:

Browser / Mobile App
        ↓
    API Layer (REST / GraphQL)
        ↓
  Business Logic (Node.js / Python / etc.)
        ↓
    PostgreSQL Database

The database is the source of truth for your application. Every piece of data that needs to outlive a single request — user accounts, orders, products, content — lives in the database. The application layer reads and writes data through SQL queries, and PostgreSQL handles all the complexity of concurrent access, durability, and consistency.

Your Learning Path

Over the next 14 lessons, you will build a complete and practical mastery of SQL and PostgreSQL:

  1. Fundamentals: Relational model, setup, first queries
  2. Core SQL: SELECT, INSERT, UPDATE, DELETE with real data
  3. Aggregations and JOINs: Summarizing and combining tables
  4. Advanced queries: Subqueries, CTEs, views, and functions
  5. Database design: Normalization, keys, and constraints
  6. Performance: Indexes, query planning, and optimization
  7. Production: Transactions, security, backups, and roles

By the end, you will be able to design a complete relational schema from scratch, write complex queries with confidence, and deploy a secure, performant PostgreSQL database to production.

Let us start with the foundation: the relational model.

Why PostgreSQL?
PostgreSQL 18 is the gold standard for open-source relational databases. It supports JSON, full-text search, geographic data, window functions, and advanced indexing — all while being 100% free and battle-tested at massive scale by companies like Apple, Instagram, and Spotify.
ACID: The Foundation of Reliable Data
ACID stands for Atomicity, Consistency, Isolation, and Durability. These four properties guarantee that database transactions are processed reliably, even in the face of errors, power failures, or concurrent access.
NoSQL Is Not a Replacement
NoSQL databases like MongoDB or Redis solve different problems. They are excellent for flexible schemas, caching, or real-time data, but they sacrifice ACID guarantees by default. Always choose the right tool for the job.
sql
-- An ACID-compliant transaction: transfer money between accounts
BEGIN;

-- Deduct from sender
UPDATE accounts
SET balance = balance - 500.00
WHERE account_id = 1;

-- Add to receiver
UPDATE accounts
SET balance = balance + 500.00
WHERE account_id = 2;

-- Only commits if BOTH updates succeed
COMMIT;

-- If anything fails, roll back everything
-- ROLLBACK;