CodeCrush

## glossary

What is Database?

A database is an organized collection of information, structured to allow efficient storage, querying and updating. The software that manages this access is the DBMS (Database Management System) — PostgreSQL, MySQL, MongoDB and Oracle are examples.

The DBMS does far more than store files. It guarantees integrity, controls concurrent access by thousands of users, optimizes queries, keeps consistent backups and ensures that a power failure in the middle of an operation does not leave the data corrupted.

Database models

  • Relational — data in tables with explicit relationships, queried via SQL. The dominant model since the 1970s.
  • Document — flexible JSON records, without a rigid schema. See NoSQL.
  • Key-value — direct access by key, optimized for speed.
  • Graph — nodes and edges, for data where the relationships are the focus.
  • Time series — optimized for time-indexed metrics, such as monitoring and IoT.

ACID: the guarantees of transactions

A transaction groups operations that must happen as one unit. The four ACID properties define what the database guarantees:

  • Atomicity — either everything is applied, or nothing is. There is no halfway.
  • Consistency — the database never ends up in a state that violates its rules and constraints.
  • Isolation — concurrent transactions do not see each other's intermediate states.
  • Durability — once committed, the change survives power failures or process crashes.
A transaction: the transfer that cannot fail halfway
BEGIN;

UPDATE contas SET saldo = saldo - 500 WHERE id = 1;
UPDATE contas SET saldo = saldo + 500 WHERE id = 2;

-- if any statement fails, ROLLBACK undoes everything
COMMIT;

Indexes: what separates milliseconds from minutes

Without an index, finding a record requires scanning the entire table. An index is an auxiliary structure — usually a B-tree — that locates the data directly. The gain is huge on reads and the cost is real on writes: every index must be updated on each insert, and it takes up disk space.

Investigating why a query is slow
-- shows the execution plan chosen by the database
EXPLAIN ANALYZE
SELECT * FROM pedidos
WHERE cliente_id = 42 AND status = 'pendente';

-- Seq Scan   = full table scan (bad on a large table)
-- Index Scan = used an index (good)

CREATE INDEX idx_pedidos_cliente_status
  ON pedidos (cliente_id, status);

Normalization and the practical balance

Normalizing means organizing the data to eliminate redundancy, guaranteeing that each piece of information exists in a single place. That prevents inconsistencies, but requires joins to rebuild the full picture. Under read-heavy workloads, it is common to deliberately denormalize — duplicating data to speed up queries — accepting the cost of keeping the copies in sync.

## faq

Frequently asked questions

What is the difference between a database and a DBMS?

The database is the collection of stored information. The DBMS is the software that manages that collection — it controls access, executes queries, guarantees integrity and handles backups. In everyday use the terms blur together, but technically they are distinct things.

PostgreSQL or MySQL: which one to choose?

PostgreSQL tends to be the default choice today, thanks to more advanced features such as robust JSON types, extensions and closer adherence to the SQL standard. MySQL remains widespread, with good read performance and broad availability in hosting providers. Both are mature and production-ready.

When should I create an index?

On columns frequently used in WHERE, JOIN and ORDER BY, especially in large tables. Avoid indexing everything: too many indexes degrade writes and consume disk. The right path is to measure with EXPLAIN ANALYZE before and after, rather than guessing.

## read next

Related articles