Query and persist data
Data & storage
Explore queries, indexes, transactions and storage operations using synthetic records.
- concept
SQL primary keys and unique constraints
A primary key requires unique, non-null key values. A unique constraint follows its declared duplicate rule; PostgreSQL 18 default UNIQUE constraints allow multiple NULLs.
Explore the idea - concept
Database indexes and query plans
An index can let a query locate candidate rows without scanning every table row. That can reduce retrieval work, but a planner may still choose a sequential scan when its estimated cost is lower.
Explore the idea - concept
SQL joins explained
INNER JOIN returns one row for every matching left–right pair. LEFT JOIN returns those same pairs and also preserves each unmatched left row with NULLs in the right-side fields. Later filters can still remove rows from that joined result.
Explore the idea - concept
Database transactions and ACID
A transaction groups transactional changes into commit or rollback. ACID names distinct guarantees whose behavior depends on the database, isolation level and persistence configuration.
Explore the idea - comparison
Object, block and file storage
Object storage addresses named objects by key through an API; block storage exposes addressable logical blocks; file storage presents files and directories through paths and filesystem operations.
Explore the idea - comparison
Database backups, snapshots and consistency
A snapshot can be part of a backup, but its existence proves neither consistency nor recovery. The base copy must meet engine requirements, and required change logs must cover the recovery target.
Explore the idea - concept
Database connection pooling
A database connection pool reuses connections and caps how many callers can borrow them at once. When all usable connections are borrowed, additional callers must wait or fail according to the pool's policy.
Explore the idea
Read the assumptions and changed conditions before applying any conclusion to a real database or filesystem.