Verified:GitHub

postgresql-table-design

Guide PostgreSQL schema design covering normalization, indexing, constraints, and data-type selection for OLTP and analytics workloads.

Updated Mar 29, 2026

Search Tags:#indexing #postgresql #schema #constraints #normalization #database-design #datatypes

Ready to run

npx skills add https://github.com/jamesogunsan/prod-eng-skills --skill postgresql-table-design-jamesogunsan

💬 Prompt for ChatGPT / Claude Chat▼

Please help me install this Agent Skill.
Skill: postgresql-table-design
Source: https://github.com/jamesogunsan/prod-eng-skills/tree/main/plugins/database-design/skills/postgresql
Command: npx skills add https://github.com/jamesogunsan/prod-eng-skills --skill postgresql-table-design-jamesogunsan

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

PostgreSQL table design is often a source of subtle defects: improper keys, data types, and indexing choices that degrade data integrity and performance. This guide helps teams design robust schemas that scale with growing workloads.

Core Features & Use Cases

Guidance on selecting primary keys, normalization to 3NF, and meaningful constraints to ensure data integrity.
Recommendations for data types, indexing strategies, and partitioning suitable for OLTP and analytics workloads.
Practical examples for common patterns such as reference tables, time-series data, and controlled JSONB usage with constraints.

Quick Start

Start by outlining your tables with primary keys, appropriate data types, and sensible constraints, then add indexes and timing considerations for best performance.

Frequently Asked Questions about postgresql-table-design

High-intent search queries and answers about installing and using this skill.

FAQPage Schema

How do I design a PostgreSQL schema for OLTP workloads?▼

PostgreSQL schema design for OLTP balances data integrity and performance by selecting BIGINT primary keys, applying 3NF normalization, and adding sensible constraints. You then implement targeted indexing strategies to handle high-volume transaction workloads efficiently.

What is the best way to use JSONB in PostgreSQL table design?▼

The best way to use JSONB in PostgreSQL table design is with careful, controlled usage paired with meaningful constraints. This prevents subtle data integrity defects while allowing flexible schema patterns for analytics or reference data workloads.

When do I need partitioning in PostgreSQL schema design?▼

You need partitioning in PostgreSQL schema design when managing time-series data or scaling growing analytics workloads. It distributes large tables to maintain query performance alongside proper data types and indexing strategies.

How do I normalize PostgreSQL tables to 3NF without hurting performance?▼

Normalize PostgreSQL tables to 3NF by structuring reference tables and applying meaningful constraints to ensure data integrity. You then maintain performance by adding thoughtful foreign key indexing and selecting appropriate data types like TIMESTAMPTZ.

Why should I use TIMESTAMPTZ and BIGINT IDs in PostgreSQL schemas?▼

You should use TIMESTAMPTZ and BIGINT IDs in PostgreSQL schemas to prevent subtle defects and ensure scalability. These data type best practices enforce data integrity and maintain performance across growing OLTP and analytics workloads.

Does PostgreSQL schema design require foreign key indexing?▼

PostgreSQL schema design requires thoughtful foreign key indexing to maintain query performance and data integrity. Proper FK indexing prevents degradation as workloads scale, complementing normalization and careful constraint selection.