database Introduced in L3

Postgres

The serious open-source relational database. The default choice for most production apps that need structured data.

Mindmap

hover · click to navigate
this tech depends on / used by alternative Shipyard anchor
What it is

The plain-English version

Postgres (formally PostgreSQL) is an open-source relational database, in continuous development since 1996. It speaks SQL, supports transactions (ACID), constraints, indexes, JSON columns, full-text search, geospatial types, and an enormous extension ecosystem. It is the default choice for most modern production apps.

Why it exists

The problem it solves

Three reasons most teams pick Postgres: it's hard to outgrow (handles real workloads from prototype to billions of rows), it's portable (every cloud has a managed Postgres; you can self-host if you want), and the ecosystem is huge (extensions like PostGIS, pgvector, TimescaleDB do specialized work in the same database).

What it competes with

Alternatives

AlternativeTypeWhen it wins
MongoDBdatabaseThe dominant document database. Schemaless flexibility, JSON-shaped documents, harder consistency tradeoffs.
Redisin-memory storeThe in-memory key-value store. Fast cache, fast queue, fast everything that doesn't need durability.
PrismaORMThe TypeScript-first ORM. Schema-driven, type-safe, the default for most modern Node apps.
Where it shows up in Shipyard

Deep links

Vocabulary

The words you'll hear

ACID
Atomicity, Consistency, Isolation, Durability — the transactional guarantees.
Index
A precomputed lookup structure. Makes queries fast at the cost of write speed and space.
Query plan
How Postgres will execute a query. EXPLAIN / EXPLAIN ANALYZE show it.
Connection pool
A pool of pre-opened DB connections shared by app instances. PgBouncer is the dominant tool.
Replica
A copy of the database for read scaling or failover.
Migration
A versioned schema change. Tools: Prisma, Drizzle, Flyway, Liquibase, raw SQL.
VACUUM
Postgres's housekeeping that reclaims space from deleted rows. Mostly automatic.
Prompting

Bad vs. good prompt for Postgres

✕ Bad prompt
design my database
✓ Good prompt
Design a Postgres schema for Tasklane: users, projects, tasks, comments. Include foreign keys with appropriate ON DELETE behavior, indexes on the common query patterns (tasks by project_id, tasks by assignee_id, comments by task_id), and a CHECK constraint on task.status (todo|doing|done). Show the SQL with comments explaining each decision.

Why it works: Asks for the schema as SQL with rationale, not just an ORM model. Names the indexes you actually need. Forces deliberate FK behavior decisions instead of defaults.

Pitfalls

What bites real teams

⚠ Missing indexes

A query is fast at 1k rows, slow at 100k, dead at 10M. Add indexes for your common query patterns. EXPLAIN ANALYZE tells you.

⚠ Connection exhaustion

Each app process holding open connections eventually exhausts the DB's max. PgBouncer or the cloud-provided pooler is essential at scale.

⚠ Long-running transactions

Transactions hold locks. A web request that wraps a long external API call inside a transaction blocks other writes. Keep transactions short.

References

Official docs only