Databases: Part 1 — Types and Uses

A database is an organized storage of information that an application uses to write, search, modify, and delete data. A database server (DBMS) is the program that manages this storage: it accepts requests, maintains data integrity, controls access, and recovers operation after failures.

Why use a DBMS if you have files and spreadsheets

When there’s little data and only one person works with it, an Excel spreadsheet is enough. Problems begin when records number in the thousands and users in the dozens:

• concurrent work. Two managers modify the same order at the same time, and one of the changes is lost;
• integrity. An order references a customer who has already been deleted, or an amount is debited from one account but not credited to another;
• search. Finding the needed record among millions of rows without indexes is slow;
• access. The accountant needs payments but not user passwords;
• failures. After a power outage a file may be left half-written.

A DBMS solves these problems systematically: it provides transactions, indexes, access rights, and a change log by which data is recovered after a crash.

Transactions and ACID in simple terms

A transaction is a group of operations that is executed entirely or not at all. A classic example is a money transfer: debiting one account and crediting another must happen together. Transaction reliability is described by four ACID properties:

• atomicity (Atomicity) — either all operations in the transaction are executed, or none are;
• consistency (Consistency) — after a transaction the data satisfies all defined rules: constraints, foreign keys, checks;
• isolation (Isolation) — concurrent transactions do not see each other’s intermediate results; how strictly is defined by the isolation level;
• durability (Durability) — a committed transaction is preserved even if a failure occurs immediately after committing.

Many distributed NoSQL systems sacrifice parts of these guarantees for availability and scalability. This approach is called BASE: the system remains available, and consistency between nodes is achieved not instantly but after some time (eventual consistency). That’s acceptable for a shopping cart, but not for bank account balances.

Main types of databases

Relational (SQL)

Data are stored in tables with a strict schema: each column has its own type, and relationships between tables are defined by foreign keys. Queries are written in SQL, which allows joining tables (JOIN), grouping, and filtering data.

• When appropriate: accounting, CRM, online stores, billing — anywhere data are interrelated and accuracy matters.
• Examples: PostgreSQL, MySQL and MariaDB, SQLite; commercial ones include Oracle Database and Microsoft SQL Server.

A relational DBMS is a sensible default choice. Modern PostgreSQL and MySQL can store and index JSON, so flexible fields do not necessarily need to be moved to a separate database.

Key-value

The simplest model: a value is stored by a key, and it can only be retrieved by that key. Such systems are very fast because they usually keep data in memory.

• When appropriate: cache, user sessions, counters, queues, rate limiting.
• Examples: Redis and its open fork Valkey, Memcached.

Document

A record is a document in JSON (or its binary variant BSON) with an arbitrary nested structure. Documents in the same collection can differ in their set of fields.

• When appropriate: product catalogs with varying attributes, content, user profiles, data whose structure changes frequently.
• Examples: MongoDB, Couchbase.

Columnar

This name covers two different classes of systems:

• analytical columnar DBMSs store each column separately and compress it. A query like “sum of sales by region for a year” reads only the two needed columns out of hundreds and runs in seconds on billions of rows. Examples — ClickHouse, Apache Druid;
• wide-column stores distribute data across many servers and are designed for huge write streams. Examples — Apache Cassandra, ScyllaDB, HBase.

Time series

Optimized for timestamped data: server metrics, sensor readings, quotes. They can compress such data, downsample old values, and quickly build aggregates over intervals.

• Examples: Prometheus and VictoriaMetrics (monitoring metrics), InfluxDB, the TimescaleDB extension for PostgreSQL.

Graph

Store entities as nodes and relationships between them as edges. Effective where relationships are primary: recommendations, fraud-chain detection, social graphs.

• Examples: Neo4j, Memgraph.

How to choose a database

When choosing, check several things:

1. What your application supports. WordPress works with MySQL and MariaDB, 1C:Enterprise — with PostgreSQL (including the Postgres Pro build) and Microsoft SQL Server. Choosing a database against application requirements makes no sense.
2. Do you need transactions. If data are related to money, balances, or documents, you need a DBMS with full ACID.
3. How the load will grow. Most projects run for years on a single server with a replica for redundancy. Horizontal scaling (sharding) complicates operations, and you should plan for it in advance only if truly necessary.
4. License. Terms differ among popular DBMSs: PostgreSQL is distributed under a permissive open-source license, MySQL Community under the GPL, and MongoDB and Redis have changed licenses in recent years. This matters if you plan to offer the database as a service.
5. Who will maintain it. Backups, updates, and monitoring are required for any DBMS. If you don’t have your own administrator, consider a managed database from a cloud provider.

Often multiple databases are used in a single project: PostgreSQL stores orders, Redis — sessions and cache, ClickHouse — analytics. This is normal practice: each system solves its own task.