This chapter highlights how PostgreSQL’s flexibility can both empower and mislead. It focuses on common mistakes around table design and indexing: leaning on legacy table inheritance, neglecting or misapplying partitioning, confusing multi-key partitioning with multi-level partitioning, and choosing unsuitable index types. The overarching message is to design with clear workloads in mind, plan for data growth and retention, and match features to real requirements rather than assumptions carried over from other systems.
Table inheritance, once attractive for object-relational modeling and pre–declarative partitioning eras, collides with modern partitioning: you cannot mix inheritance with declarative partitions. When inheritance paints you into a corner, migrate to normalized tables linked by foreign keys and retire the inheritance layer, avoiding risky catalog hacks in production. The chapter then shows why partitioning matters: it enables partition pruning for faster scans, simpler retention via dropping partitions, better VACUUM parallelism, and placement across tablespaces—while helping avoid practical size limits of massive tables. Success hinges on capacity planning and selecting the right method, key, and partition sizing; poorly chosen keys, low cardinality, or queries that omit the partition key can erase the benefits.
A frequent misconception is that “partitioning by multiple keys” equals multi-level partitioning. In reality, composite range bounds have subtle, non-intuitive overlap rules; when you need month-by-branch buckets, sub-partitioning (partitioning partitions) is the correct approach. Finally, the chapter cautions against one-size-fits-all indexing. B-Tree excels at equality, ordering, identifiers, and prefix patterns but not substrings or very long values. GIN is the right tool for full-text search and many jsonb queries; targeted expression indexes often beat large catch-all JSON indexes on size and speed. GiST and BRIN, while lossy, can be ideal for ranges, spatial-like data, or very large, sequential datasets. Index only what you query, balancing read performance against write overhead and storage.
Summary
If you discovered table inheritance and you think you need it, you’re probably wrong. Implement the parent-child relationships with foreign keys and triggers if needed.
You can leverage table partitioning to make management and maintenance of large tables easier, and speed up the queries hitting those tables.
Take care when sub-partitioning because partitioning by multiple keys is not the same thing. Unfortunately, it is not obvious from the documentation what the multiple key partitioning syntax does, when best to use it, and what its implications are.
Each index type offered in PostgreSQL has its strengths and weaknesses. By adapting your indexing plan to the type of data you have and type of queries you need to run, you can optimize performance and storage space. When you get it right, your queries can run orders of magnitude faster.
FAQ
What is table inheritance in PostgreSQL, and should I use it today?Table inheritance lets a child table automatically include columns from a parent table (CREATE TABLE ... INHERITS ...). While flexible, it’s largely obsolete for most use cases. Since PostgreSQL 10 introduced declarative partitioning, inheritance has few practical advantages and brings compatibility and maintenance pitfalls. Prefer normal parent/child tables linked by foreign keys or use partitioning where appropriate.Can I combine table inheritance with declarative partitioning?No. Inheritance and declarative partitioning are fundamentally incompatible. You can’t inherit from a partitioned table, and you can’t attach an inheritance parent as a partition. If you need partitioning, avoid inheritance or migrate away from it first.How do I migrate away from table inheritance to a safer design?Typical steps: 1) Create a new standalone child table (e.g., LIKE old_child), 2) Add a foreign key column to reference the former parent’s primary key, 3) Copy data (including the parent key) into the new table, 4) Create needed constraints, indexes, and triggers, 5) Swap table names in a single transaction block and drop the old inherited table. This preserves semantics while removing the inheritance coupling.Is it safe to “speed up” foreign key validation by modifying the catalog?No. Directly updating system catalogs (e.g., flipping pg_constraint.convalidated) is risky and can corrupt your database. If you must avoid immediate validation, create the FK as NOT VALID and then run VALIDATE CONSTRAINT when convenient. This achieves online validation without unsafe catalog edits.When is table partitioning worth it, and what are the benefits?Partitioning shines with very large tables, heavy write workloads, time- or ID-range queries, and data lifecycle/retention needs. Benefits include: faster queries via partition pruning; cheap retention using DROP PARTITION instead of large DELETEs; parallel and shorter VACUUMs on many smaller tables; ability to place partitions on different tablespaces/storage; and mitigation of practical per-table size limits.How should I choose a partitioning method, key, and partition size?Pick a method (RANGE, LIST, HASH) that matches access patterns. Choose a key with good cardinality that appears in WHERE clauses so pruning can occur (e.g., timestamp for time-series). Size partitions to balance management overhead and query performance (e.g., monthly for time-based data). Base this on ingestion rates, growth projections, and retention requirements.What are common partitioning mistakes that hurt performance?- Using a key not present in query filters, which prevents pruning. - Choosing a key with poor cardinality, causing data skew. - Creating too many tiny partitions, leading to planning overhead and excessive partition scans. - Ignoring size/retention planning, hitting per-table limits or making maintenance costly.What’s the difference between partitioning by multiple keys and sub-partitioning?Partitioning by multiple keys (e.g., RANGE (time, branch)) defines tuple bounds with lexicographic semantics; ranges can easily overlap unexpectedly. Sub-partitioning is multi-level: first partition by one key (e.g., month), then make each partition itself partitioned by another key (e.g., branch ranges). For “month × branch” layouts, use sub-partitioning.Which index type should I use: B-Tree, GIN, GiST, or BRIN?- B-Tree: default; best for equality, ordering, and prefix text searches (with text_pattern_ops). Not suitable for leading-wildcard substrings and very long values. - GIN: great for full-text (tsvector) and jsonb containment; fast lookups but heavier to maintain and larger than B-Tree. - GiST: lossy, supports ranges, geometries, and nearest-neighbor; useful when exact matching isn’t required. - BRIN: very small, good for large, naturally ordered data (timestamps); returns ranges that require rechecks; great when “good enough” filtering is acceptable.Should I index entire JSON documents with a GIN index or use targeted expression indexes?It depends. A single GIN index on jsonb supports many containment queries but can be large and costly to update. If your workload targets specific fields (e.g., data->>'doi'), a narrow expression B-Tree index is much smaller and faster for equality lookups. Use GIN for broad, varied jsonb queries; use targeted expression indexes for frequent, predictable predicates.
pro $24.99 per month
access to all Manning books, MEAPs, liveVideos, liveProjects, and audiobooks!
PostgreSQL Mistakes and How to Avoid Them ebook for free
