Learn
Software Engineering
Data
Databases
Overview

Overview

Databases are specialized systems for storing, organizing, and retrieving data efficiently. This section covers relational databases (SQL, PostgreSQL) and immutable databases (Datomic).

What You’ll Learn

  • SQL - The standard language for relational database querying and manipulation
  • PostgreSQL - Advanced open-source relational database with powerful features
  • Datomic - Immutable database with time-travel queries and datalog
  • Database Design - Schema design, normalization, and indexing strategies
  • Query Optimization - Performance tuning and query analysis
  • Transactions - ACID properties, isolation levels, and concurrency control

Available Databases

SQL - Universal Database Language

SQL provides the foundation for working with relational databases:

  • Query Fundamentals - SELECT, WHERE, JOIN for data retrieval
  • Data Manipulation - INSERT, UPDATE, DELETE operations
  • Schema Definition - CREATE TABLE, indexes, constraints
  • Aggregation - GROUP BY, HAVING, aggregate functions
  • Advanced Queries - Subqueries, window functions, CTEs
  • Transactions - BEGIN, COMMIT, ROLLBACK

PostgreSQL - Production Database System

PostgreSQL extends SQL with advanced features:

  • Advanced Data Types - JSON, arrays, ranges, custom types
  • Performance Features - Parallel queries, partitioning, materialized views
  • Full-Text Search - Built-in text search capabilities
  • Extensions - PostGIS for geospatial, pg_trgm for fuzzy matching
  • Replication - Streaming replication, logical replication
  • Administration - Backup strategies, performance tuning, monitoring

Datomic - Immutable Database

Datomic provides immutable data storage with powerful temporal features:

  • Immutability - Facts are never deleted, only accumulated over time
  • Time-Travel Queries - Query database at any point in history with as-of, since, history
  • Datalog Query Language - Declarative, composable queries based on logic programming
  • ACID Transactions - Strong consistency with serializable isolation
  • Flexible Schema - Schema evolves incrementally without migrations
  • Audit Trail - Every fact timestamped and preserved forever

Learning Path

For Relational Databases

  1. Start with SQL fundamentals - Learn the universal language of relational databases
  2. Explore PostgreSQL - Apply SQL knowledge to a production database with advanced features

For Immutable Databases

  1. Start with Datomic - Learn immutable data storage, datalog queries, and time-travel

All databases provide By Example tutorials:

  • Beginner - Core concepts and basic operations (0-40% coverage)
  • Intermediate - Production patterns and optimization (40-75% coverage)
  • Advanced - Expert techniques and administration (75-95% coverage)

Database Operations

Relational Databases (SQL, PostgreSQL)

  • Schema Design - Normalization, relationships, constraints
  • Query Optimization - Indexes, EXPLAIN plans, query tuning
  • Transactions - ACID guarantees, isolation levels, deadlocks
  • Backup & Recovery - pg_dump, WAL archiving, point-in-time recovery
  • Security - User management, role-based access, SSL connections
  • Monitoring - pg_stat views, performance metrics, logging

Immutable Databases (Datomic)

  • Schema Evolution - Additive schema changes without migrations
  • Datalog Queries - Pattern matching, joins, rules, aggregates
  • Time Queries - as-of, since, history for temporal navigation
  • Transaction Functions - Database functions executing inside transactions
  • Audit Trails - Transaction metadata and change tracking
  • Performance - Index selection, caching, query optimization

Getting Started

Relational Databases

Begin with SQL to build a strong foundation in relational database concepts. Then explore PostgreSQL to learn production database features and administration.

Immutable Databases

Start with Datomic to learn immutable data storage, datalog queries, and time-travel capabilities.

Choosing Your Database

  • Use SQL/PostgreSQL for traditional CRUD applications, complex queries, mature ecosystem
  • Use Datomic for audit trails, event sourcing, temporal queries, immutable data requirements

All paths include practical, annotated examples you can run immediately.

Last updated