Databases
PostgreSQL
PostgreSQL, usually called Postgres, is a free, open-source relational database with strong ACID transaction guarantees, a strict and consistent approach to data types, and a genuinely deep feature set — JSONB columns for semi-structured data, full-text search, and an extension system that adds capabilities like geospatial queries or vector search directly inside the same database.
Why it matters
- It's a common default for new backend projects
- Its combination of correctness, feature depth, and an open license makes it a frequent first choice when a team isn't already locked into another engine.
- JSONB narrows the gap with document databases
- A team can keep most data relational and still store a handful of genuinely variable fields as JSONB, without standing up a second database just for that.
- It's supported everywhere
- Every major cloud provider offers a managed Postgres option, and essentially every ORM and language has mature drivers for it.
Tables, constraints, and a query
A table's columns each have a declared type, and constraints — NOT NULL, foreign keys, uniqueness — are enforced by the database itself at write time, not left to application code to get right every time.
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id),
status TEXT NOT NULL DEFAULT 'pending',
total NUMERIC(10,2)
);
SELECT o.id, o.status, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
ORDER BY o.id DESC
LIMIT 10;JSONB and the extension ecosystem
A JSONB column stores JSON in a binary, indexable form, so a record can have a handful of genuinely variable fields without a schema migration for every new attribute, while the rest of the row stays properly typed and constrained. Beyond that, Postgres's extension system lets it take on capabilities like geospatial queries or full-text search as installable extensions running inside the same database, rather than requiring a separate specialized service for each.
MVCC: how Postgres handles concurrent writes
Postgres uses multi-version concurrency control, meaning a reader never blocks a writer and a writer never blocks a reader — each transaction sees a consistent snapshot of the data rather than fighting over locks for ordinary reads. This is a big part of why Postgres handles mixed read/write workloads well without readers and writers constantly stepping on each other.
Mistakes people make here
- Not indexing foreign key columns
- Postgres doesn't automatically index a foreign key the way some engines do — without an explicit index, joins and deletes that touch that relationship get slower as the referencing table grows.
- Using SELECT * in application code
- It breaks quietly when columns are added, removed, or reordered, and it pulls data the application doesn't actually need — naming the columns you use keeps the query's contract explicit.
- Treating JSONB as a way to skip schema design entirely
- It's meant for genuinely variable data, not as a substitute for thinking through a table's structure — overusing it gives up the type checking, constraints, and query planning that make a relational database worth using in the first place.
- Not looking at the query planner when a query is unexpectedly slow
- EXPLAIN ANALYZE shows exactly what the database is doing — a missing index or an unexpectedly large sequential scan is usually visible immediately once you actually look, rather than guessed at.
Strengths and trade-offs
Where it is strong
- Strict standards compliance and strong data-integrity guarantees enforced by the database itself.
- JSONB gives flexible, nested fields without leaving the relational model for the rest of the schema.
- A mature extension ecosystem — full-text search, geospatial queries via PostGIS, vector search via pgvector — runs inside the same database instance rather than as bolted-on separate services.
- Free and open source, with no licensing cost regardless of scale.
The trade-offs
- Vertical scaling has real limits — very write-heavy workloads eventually need read replicas, partitioning, or sharding, each of which adds operational complexity.
- Default configuration is conservative and usually needs tuning (connection limits, memory settings) before it's ready for real production load.
- Setting up replication and high availability takes more manual work than some managed NoSQL products advertise as built in.
- A schema change on a very large table can lock it or take real time to complete if it isn't done carefully.
Who needs this
A reasonable default recommendation for most new backend projects that need real transactions and enforced relational integrity.
Questions about postgresql
- Is PostgreSQL better than MySQL?
- Both are solid, mature engines; Postgres tends to have a richer type system and extension ecosystem, while MySQL has a long history of simple operational defaults. For most applications, either is a perfectly competent choice, and the decision often comes down to team familiarity or hosting constraints.
- What is JSONB, exactly?
- A binary-stored JSON column type that can be indexed and queried efficiently, meant for genuinely variable or nested fields — it isn't a reason to skip designing a schema for the rest of the table.
- Do I need an ORM to use Postgres?
- No, raw SQL works fine on its own. An ORM trades some direct control for convenience and less boilerplate, which is a reasonable trade for many teams, but it's never a requirement.
- Can Postgres replace a document database like MongoDB?
- For most workloads, JSONB columns cover a lot of what a document database is used for. A pure document store can still fit better for access patterns that are entirely document-shaped from top to bottom, but the gap has narrowed considerably.