postgresql

PostgreSQL Table Design - Database Table Design Expert Skills

Skill Overview

PostgreSQL Table Design is a professional PostgreSQL database table design assistant that helps developers follow best practices for schema design, data type selection, index planning, and performance optimization.

Use Cases

1. PostgreSQL Table Structure Design and Modeling

When you need to design new tables or optimize existing table structures for a PostgreSQL database, this skill provides expert advice on data type selection, constraint design principles, and index optimization strategies. From user, order, and complex time-series table designs, you can get guidance that aligns with PostgreSQL best practices.

2. Data Type Selection and Constraint Design

PostgreSQL offers a wide range of data types (JSONB, arrays, range types, etc.). Choosing the right types is crucial for performance and storage. This skill helps you understand when to use BIGINT vs UUID, TIMESTAMPTZ vs TIMESTAMP, TEXT vs VARCHAR, and other common choices, as well as how to correctly set constraints such as NOT NULL, CHECK, and UNIQUE.

3. High-Performance Indexing and Partition Planning

When dealing with high-volume tables or high-concurrency query requirements, this skill guides you to design efficient indexing strategies (B-tree, GIN, GiST, partial indexes, expression indexes, etc.), and when to use advanced PostgreSQL features such as partitioned tables and Row-Level Security (RLS) to improve performance and security.

Core Features

1. Data Type Best Practices

Provides a PostgreSQL-specific guide for choosing data types, covering common scenarios such as ID fields (BIGINT IDENTITY vs UUID), time fields (TIMESTAMPTZ), amount fields (NUMERIC), and strings (TEXT vs VARCHAR). It helps you avoid deprecated types (SERIAL, TIMESTAMP, MONEY, etc.).

2. Index and Constraint Design

Explains PostgreSQL index types (B-tree, GIN, GiST, BRIN) and their suitable use cases in depth. It emphasizes a commonly overlooked point: foreign keys require indexes to be created manually. It also covers advanced techniques such as partial indexes, expression indexes, and covering indexes to ensure optimal query performance.

3. Performance Optimization and Advanced Features

Offers specialized design recommendations for different workloads (high insert rates, frequent updates, and time-series data). It includes guidance on partitioning strategies, Row-Level Security (RLS), JSONB index optimization, TimescaleDB time-series extensions, PostGIS spatial data, pgvector vector search, and other advanced capabilities.

Frequently Asked Questions

What type should I use for the primary key when creating a PostgreSQL table?

For most application scenarios, it is recommended to use BIGINT GENERATED ALWAYS AS IDENTITY as the primary key type. It auto-increments, is efficient, and uses less storage. Only consider UUID when you need global uniqueness (e.g., in distributed systems) or when you require an opaque ID. UUID consumes more storage space and typically has lower index efficiency; for time-series or log tables, you may even not need to define a primary key.

Will PostgreSQL automatically create indexes for foreign keys?

No. This is an important difference between PostgreSQL and other databases. Foreign key constraints do not automatically create indexes, and missing indexes can lead to lock waits and performance issues when deleting/updating rows in the parent table. After creating a foreign key, be sure to manually add an index to the foreign key column—this can significantly improve JOIN query performance and avoid lock contention.

What data type should be used to store monetary amounts in PostgreSQL?

You must use the NUMERIC(p,s) type to store monetary amounts. Never use FLOAT or DOUBLE PRECISION. Floating-point types can cause precision issues, potentially resulting in incorrect amount calculations. For example, NUMERIC(10,2) means there are 10 total digits, including 2 decimal places, and it can store a maximum amount of 99999999.99. PostgreSQL’s MONEY type is not recommended.

What are common pitfalls when designing PostgreSQL tables?

Common pitfalls include: 1) using VARCHAR(n) instead of TEXT; 2) using TIMESTAMP instead of TIMESTAMPTZ; 3) forgetting to add indexes for foreign keys; 4) overusing UUID causing index bloat; 5) premature denormalization; 6) mixing frequently updated columns with stable columns in the same table; 7) not considering TOAST storage for large fields; 8) not accounting for the behavior of multiple NULL rows with UNIQUE constraints. Following this skill’s best practices helps avoid these problems.