Each test is 5 questions with varying difficulty.
AI Prep covers AI Agents, Generative AI, ML Fundamentals, NLP & LLMs and a lot more, with adaptive tests and daily challenges. Fully offline on Android. Free to try, one-time unlock for lifetime access.
PostgreSQL table partitioning is a critical architectural pattern for managing multi-terabyte datasets by breaking large tables into smaller, more manageable physical pieces. In 2026, as AI-driven applications generate massive volumes of time-series and event data, mastering partitioning is essential for database engineers, backend developers, and SREs. Interviewers focus on this topic to assess your ability to design systems that maintain high query performance as data scales. At a junior level, candidates are expected to understand the basic syntax and the difference between inheritance-based and declarative partitioning. At a senior level, expectations shift toward complex partition strategies, performance trade-offs, query planner behavior, and operational maintenance tasks like detached partition archiving and index management.
Partitioning is the primary mechanism for avoiding the 'performance cliff' in PostgreSQL. Without it, B-Tree indexes on massive tables grow beyond RAM capacity, leading to disk-bound random I/O that cripples latency. By using declarative partitioning, engineers can achieve partition pruning, where the query planner ignores irrelevant partitions entirely, reducing the search space from billions of rows to millions. This is vital for systems like time-series logging or multi-tenant SaaS platforms where data can be naturally segmented by time or tenant ID. A strong interview answer demonstrates an understanding of the trade-offs: while partitioning improves read performance for specific queries, it introduces complexity in global index management and cross-partition constraints. Candidates who can articulate when to use range vs. hash partitioning, and how to handle partition maintenance without downtime, signal they can build systems that remain performant over years of data growth.
PostgreSQL partitioning uses a parent table as an interface, with the actual data residing in child tables. The query planner evaluates the WHERE clause against the partition bounds to determine which child tables to scan.
[Client Query]
↓
[Query Planner]
↓ ↓
[Pruning Logic] ← [Partition Metadata]
↓
[Active Partitions]
↓
[Table Scans]
↓
[Result Set]Use range partitioning on timestamps, automating the creation of future partitions and dropping of old ones via pg_partman.
Trade-offs: Simplifies maintenance but requires careful automation to prevent data loss.
Use list or hash partitioning on tenant_id to isolate tenant data, allowing for easier migration or deletion of specific tenants.
Trade-offs: Provides strong isolation but can lead to data skew if tenants are uneven.
Use unique indexes that include the partition key to ensure uniqueness across partitions without full table scans.
Trade-offs: Ensures integrity but adds overhead to cross-partition operations.
| Reliability | Use declarative partitioning to ensure data integrity; monitor partition bounds to prevent insertion errors. |
| Scalability | Partitioning allows horizontal scaling by offloading older data to cheaper storage or separate instances. |
| Performance | Pruning is the key; ensure queries always filter by the partition key to minimize disk I/O. |
| Cost | Move old partitions to cold storage or drop them to reduce storage costs and backup times. |
| Security | Apply Row Level Security (RLS) policies to partitions if needed, though this adds complexity. |
| Monitoring | Track partition count, growth rates, and pruning efficiency using EXPLAIN ANALYZE. |
Declarative partitioning is the modern, native way to implement partitioning in PostgreSQL (v10+). It uses specific SQL syntax to define partitions and handles routing automatically. Inheritance-based partitioning is the legacy approach requiring manual triggers or rules to route data, which is more error-prone and harder to maintain.
No, you cannot change the partition key of an existing table. To change it, you must create a new table with the desired partition key, migrate the data, and then rename the tables. This is why choosing the correct partition key during the initial design phase is so critical.
Partition pruning is an optimization where the PostgreSQL query planner analyzes the WHERE clause of a query and excludes partitions that cannot possibly contain the requested data. This significantly improves performance by reducing the amount of data scanned.
While there is no hard limit, having thousands of partitions can significantly increase query planning time. A good rule of thumb is to keep the number of partitions in the hundreds. If you need more, consider sub-partitioning or re-evaluating your partitioning strategy.
No. Partitioning only improves performance for queries that include the partition key in the WHERE clause, allowing for partition pruning. Queries that do not filter by the partition key may perform worse than on a non-partitioned table due to the overhead of checking multiple partitions.
The default partition is a special partition that catches all rows that do not fit into any other defined partition. It is useful for data integrity but can become a performance bottleneck because it cannot be pruned effectively.
Since PostgreSQL 11, an UPDATE that changes a row's partition key value is handled automatically: the database deletes the row from its current partition and inserts it into the correct target partition as part of the same UPDATE statement, no manual intervention required. If no partition matches the new value and there is no default partition, the update raises an error.
No. Partitioning divides a table into smaller pieces within a single database instance. Sharding distributes data across multiple database instances or servers. Partitioning is a local strategy, while sharding is a distributed strategy.
pg_partman is a popular extension that automates the creation and management of partitions. It is highly recommended for time-series data where you need to regularly create new partitions and drop old ones to keep the database size manageable.
Yes, but with caveats. A unique index on a partitioned table must include the partition key. This ensures that the uniqueness constraint can be enforced across all partitions without requiring a full table scan.
AI Prep covers AI Agents, Generative AI, ML Fundamentals, NLP & LLMs and a lot more, with adaptive tests and daily challenges. Fully offline on Android. Free to try, one-time unlock for lifetime access.