A practical reference for designing, building, and maintaining PostgreSQL databases — with real examples using Prisma.
This guide is not a database administration manual. It is a reference for application developers who are responsible for designing schemas, writing queries, and managing migrations in production — and who want to do it in a way that holds up over time.
Most database problems that appear in production were caused by decisions made at design time. A schema that was thrown together quickly, an index that was never added, a migration that was not thought through — these are the things that cause slow queries, data inconsistencies, and painful deployments six months later. Getting the data model right early is one of the highest-leverage investments you can make in a project.
Every recommendation here is grounded in literature and practice. The examples use PostgreSQL and Prisma throughout, which maps directly to a Node.js/TypeScript stack.
| Audience | How to use this |
|---|---|
| Backend developers designing a schema for the first time | Read Sections 1 through 5 before writing any Prisma schema |
| Developers inheriting a database with performance problems | Sections 6 and 7 on indexing and query patterns are the most relevant starting points |
| Teams preparing to take an application to production | Sections 8 through 10 cover migrations, soft delete, and audit fields — things that are easy to retrofit but painful to do so |
| Anyone who has been told "the database is slow" and does not know where to start | Section 7 on query patterns and Section 6 on indexing will give you a structured approach |
| # | Section | Summary |
|---|---|---|
| 1 | Relational Thinking | Why relational databases, what PostgreSQL brings, when to consider alternatives |
| 2 | Entity & Relationship Modeling | Identifying entities, mapping relationships, drawing ERDs |
| 3 | Normalization | 1NF through 3NF with practical examples, when to denormalize deliberately |
| 4 | Schema Design in Prisma | Translating a data model to a Prisma schema, types, constraints, conventions |
| 5 | Primary Keys & Identifiers | Serial integers vs UUIDs vs ULIDs, trade-offs, recommendations |
| 6 | Indexing Strategy | How indexes work, what to index, composite indexes, partial indexes, full-text search |
| 7 | Query Patterns | N+1, eager loading, pagination, aggregation, avoiding common inefficiencies |
| 8 | Migration Strategy | Prisma migrate workflow, backwards-compatible migrations, zero-downtime changes |
| 9 | Soft Delete | What soft delete is, how to implement it, the trade-offs |
| 10 | Audit Fields & Change Tracking | Created/updated timestamps, created-by tracking, full audit log tables |
| 11 | Multi-tenancy Patterns | Row-level vs schema-level vs database-level isolation, trade-offs |
| 12 | Transactions & Concurrency | ACID, isolation levels, optimistic vs pessimistic locking, Prisma transactions |
| 13 | Reference Library | All books and resources cited throughout this guide |
Each section includes working Prisma schema examples, SQL where relevant, and explicit trade-offs.
If you are starting a new project, read Sections 1 through 5 before writing your first Prisma schema. The decisions made in these sections — how to model entities, how to normalize, what type of primary key to use — are the ones that are most expensive to change later.
If you have an existing schema with problems, Sections 6 and 7 will help you identify and fix slow queries and missing indexes. Section 8 covers how to apply changes safely in production.
If you are adding features to a working system, Sections 9, 10, and 12 cover the patterns that are easy to add early but painful to retrofit: soft delete, audit trails, and proper transaction handling.
This guide is part of a series on engineering and product development:
- Product Development Playbook — The complete 17-phase guide from idea to launch
- PERN Stack Architecture Guide — Clean architecture, layering, and project structure for PERN apps
- API Design Playbook — REST best practices, OpenAPI, pagination, versioning, and idempotency
- System Design for Web Developers — Architecture decisions from a web developer's perspective
This guide draws from:
- Books: Designing Data-Intensive Applications (Kleppmann), Database Design for Mere Mortals (Hernandez), SQL Antipatterns (Karwin), The Art of PostgreSQL (Fontaine) — all cited inline
- Official documentation: PostgreSQL 16, Prisma
- Engineering resources: Braintree Engineering, PostgreSQL wiki, USE Method (Gregg)
The full list is in Section 13 — Reference Library.
See CONTRIBUTING.md for guidelines on fixing errors, adding references, or suggesting new sections.
MIT License — free to use, adapt, and distribute. See LICENSE.
Kuril
A dreamer… who still working on it to make it real.
If this guide helped you, consider giving it a ⭐ — it helps others find it too.