How does partitioning work under the hood in PostgreSQL?

Partitioning in PostgreSQL is implemented through the concept of parent tables and child tables (partitioned tables and partitions). Under the hood, when a partitioned table is created, a parent table without data is created, and data is physically stored in child tables, each corresponding to a specific range or list of values (range/list partitioning).

When an insert or select query is issued, the query planner determines which partition the data will go into, using partitioning rules. This significantly reduces the amount of data scanned and improves performance.

Example: if there is an orders table partitioned by date, queries for orders in a specific month will only access the relevant partition, not the entire table.

Internally, PostgreSQL uses table inheritance and triggers for data routing, and starting from version 10, it employs a more optimized mechanism with support for declarative partitioning.