What a database is
A database is an organized collection of data managed by a database management system (DBMS). The DBMS handles storing, querying and changing the data for many users and programs at once without corrupting it, and recovers it after a crash.
People often say database when they mean the DBMS, such as PostgreSQL, MySQL, SQLite, MongoDB or Redis. The main families are relational, document and key-value stores, and each makes different trade-offs.
What a database gives you beyond files
Plain files are often the right choice for configuration or static content. They get hard to manage once several processes write at once, or once you need to find records without reading everything.
The difference lies in the software rather than the storage format: SQLite keeps a whole database in a single file. Large binary files such as images and videos usually stay in file or object storage, with the database holding their paths.
A database adds these on top of storage:
- Concurrent reads and writes without one writer corrupting another's data
- Queries that find, filter, join and aggregate records without loading the whole data set
- Indexes that keep lookups fast as the data grows
- Constraints, such as types, unique values and required fields, that reject bad data when it is written
- Transactions that apply a group of changes completely or not at all
- Durability and crash recovery, usually through a write-ahead log
- Access control, backups and replication
Relational, document and key-value databases
Relational databases, such as PostgreSQL, MySQL, SQLite and SQL Server, store data in tables of rows and columns with a declared schema. You query them with SQL, combine tables with joins and link records with foreign keys. They suit data with many relationships and queries you can't predict in advance.
Document databases, such as MongoDB, store records as JSON-like documents, and each document can have its own shape. They fit data you read and write as one unit, such as a product with its variants. The schema still exists, but your application code enforces most of it.
Key-value stores, such as Redis, map each key to a value and fetch it by key quickly. Querying by anything else is limited. Redis keeps its data in memory, with optional persistence to disk, and is often used for caches, sessions and rate-limit counters.
NoSQL is the umbrella name for non-relational databases, which also include wide-column stores such as Cassandra and graph databases such as Neo4j. The lines have blurred: PostgreSQL can store and index JSON, and MongoDB supports multi-document transactions.
Transactions and ACID
A transaction groups several changes so they succeed or fail together. Moving money between accounts is the classic case: the debit and the credit must both happen, or neither should.
ACID names four properties a transaction provides:
- Atomicity means all of a transaction's changes apply, or none do.
- Consistency means each transaction takes the data from one valid state to another, respecting constraints such as foreign keys and unique values.
- Isolation means concurrent transactions don't interfere with each other. Most databases default to a level weaker than full isolation, so read-then-write code can still lose updates under load.
- Durability means a committed transaction survives a crash or a power loss.
Indexes and why queries get slow
An index is a separate data structure, usually a B-tree, that keeps a column's values sorted with pointers to the matching rows. With an index on email, finding one user among millions takes a few page reads instead of a scan of the whole table.
Indexes have a cost. Each one takes disk space and slows inserts and updates, because the database must update the index too. Add them for the columns your queries filter, join and sort on.
Before adding an index, ask the database how it runs the query. EXPLAIN in PostgreSQL and MySQL shows the plan, and a full scan of a large table is the usual culprit.
Common reasons a query gets slow:
- A filter or join on a column with no index, so the database scans the whole table
- A function wrapped around an indexed column, such as lower(email), which a plain index on email can't use unless you index lower(email) itself
- A pattern that starts with a wildcard, such as LIKE '%smith', which a regular B-tree index can't use
- A multi-column index whose first column isn't in the query, which often leaves the index unused
- The N+1 pattern: one query for a list, then one more query for each row
- Fetching far more rows or columns than the page needs
-- Ask PostgreSQL how it runs a query
EXPLAIN ANALYZE
SELECT id, total FROM orders WHERE customer_id = 42;
-- "Seq Scan on orders" means every row was read.
-- An index on the filtered column gives the planner a faster option:
CREATE INDEX orders_customer_id_idx ON orders (customer_id);How to choose a database
Start from your data and the questions you will ask of it. A relational database is a sensible default for most business applications. It handles relationships, ad hoc queries and transactions, and PostgreSQL, MySQL and SQLite are mature and widely supported.
Choose a document store when records are self-contained, their shape varies and you know your access patterns up front. Choose a key-value store for fast lookups by key, such as caching or sessions, usually alongside a primary database rather than instead of one.
Practical factors count as much as the data model: what your team knows, whether a managed service is available and how backups and restores work. Many systems combine stores, with a relational database as the source of truth, Redis as a cache and a search engine for full-text queries.