Back to blog

The Hidden Quirks of PostgreSQL Partitions, Schemas, Subpartitions, and Permissions

The Hidden Quirks of PostgreSQL Partitions, Schemas, Subpartitions, and Permissions

PostgreSQL partitioning looks simple when you first meet it.

You create one big logical table. PostgreSQL quietly spreads the rows across smaller physical tables. Queries stay clean. Old data can be detached. New data can land in fresh partitions. Everyone is happy.

Then one day a routine PostgreSQL operation fails.

Or a reporting user can query the parent table but cannot read one subpartition directly. Or pg_dump works for one schema and fails for another. Or a new monthly partition is created successfully, but the application role cannot insert into it.

Nothing in the table definition looks wrong. The parent table exists. The data exists. The role has permissions. And still, something breaks.

That is the strange part of PostgreSQL partitioning: the feature is elegant at the SQL level, but the real system is made of many separate database objects. Partitions, subpartitions, schemas, roles, grants, default privileges, constraints, indexes, and catalog entries all have to line up.

This article is a practical tour of those sharp edges.

The main idea is simple:

A partitioned table is not one object. It is a family of objects. Problems appear when we treat the family like a single table.

1. Understanding PostgreSQL Partition Basics

When you write this:

CREATE TABLE orders (
  id bigint,
  customer_id bigint,
  created_at date,
  amount numeric
) PARTITION BY RANGE (created_at);
PostgreSQL creates a partitioned table called orders. But this parent table does not store rows in the normal way.
It is more like a routing layer, or a logical slot in the database.

That difference matters.
The statement above tells PostgreSQL:
“Rows for this table will be divided by created_at."

It does not create a physical place where January rows, February rows, or March rows can actually live. The parent table defines the partitioning rule, but it is not a storage bucket for matching rows.

So if you stop here and try to insert data, PostgreSQL has nowhere to put the row:

INSERT INTO orders VALUES (1, 42, '2026-01-15', 99.50);

You will get an error like:

ERROR:  no partition of relation "orders" found for row
DETAIL: Partition key of the failing row contains (created_at) = (2026-01-15).

This is one of the first partitioning surprises. The parent exists, the SQL is valid, and the row has a partition key. But no real partition has been created for that key range, so PostgreSQL refuses the insert.

The real rows live in child tables:

CREATE TABLE orders_2026_01
  PARTITION OF orders
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');


CREATE TABLE orders_2026_02
  PARTITION OF orders
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

Now pause and predict again:

If you insert this row, where does it go?

INSERT INTO orders VALUES (1, 42, '2026-01-15', 99.50);

It goes into orders_2026_01, not into orders.

That sounds obvious. But it has a big consequence: each partition is still a real table. It has its own name, schema, object ID, storage, indexes, statistics, privileges, and dependencies in the PostgreSQL system catalog.

Partition metadata lives in the system catalog. Tools like pg_dump, migration engines, schema analyzers, and database assessment tools all read this metadata to understand the shape of your database.

If the metadata is complex, the tooling has to be careful.

Ways to Partition

2. Understanding the World of Schema

The Word “Schema” Means Two Different Things

Before going further, it is worth clearing up a confusion that trips up almost everyone who works across databases, ORMs, and migration tools.

In most of the database world, “schema” means the structural definition(DDL) of a database: what tables exist, what columns they have, what types those columns use, what constraints are in place.

When someone says “export the schema,” they mean “give me the DDL — the CREATE TABLE, CREATE INDEX, and ALTER TABLE statements." This is how MySQL uses the term, how ORMs use it, how migration frameworks use it, and how most documentation and Stack Overflow answers use it.

PostgreSQL uses “schema” that way too — but it also uses the same word for something completely different.

In PostgreSQL, a schema is a namespace object inside a database. It is a container you create with CREATE SCHEMA, it has a name, it owns objects, and it has its own privilege grants. It is closer to what other databases call a "database" or a "namespace" than to what most people mean when they say "schema."

Throughout this article, when we say “schema” we mean the PostgreSQL namespace — the object you create with CREATE SCHEMA and grant USAGE on. When we mean the structural definition, we say "DDL" or "structure."

Schemas As Namespace And Permission Boundary

Many developers explain a PostgreSQL schema as “a folder for tables.” That is useful at the beginning, but it is incomplete.

A schema is a namespace and a permission boundary.
This means two tables can have the same name if they live in different schemas:

CREATE SCHEMA sales;
CREATE SCHEMA archive;

CREATE TABLE sales.orders (...);
CREATE TABLE archive.orders (...);

It also means a role may be allowed to use one schema but not another:

GRANT USAGE ON SCHEMA sales TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO app_user;

That USAGE grant matters. Without it, the role cannot resolve object names inside that schema, even if table-level privileges look correct.

Why Schemas are Needed?

They solve problems that “just use different table names” handles only awkwardly.

You need schemas mainly for:

  • Avoiding name collisions: sales.customers and support.customers can both exist without naming them sales_customers and support_customers.
  • Organizing large databases: schemas group related tables, views, functions, types, and sequences. A database with hundreds of objects is much easier to navigate as app.*, audit.*, staging.*, etc.
  • Permissions: you can grant access at the schema level. For example, analysts may read reporting but not internal, or an app user may use only app objects.
  • Multi-tenancy or isolation: some systems put each tenant in its own schema, like tenant_123.orders, while keeping the same table structure per tenant.
  • Search path convenience: Postgres can resolve unqualified names using search_path, so users or apps can refer to orders while Postgres looks in the right schema first.

Different tables only separate data by table name. Schemas separate whole groups of database objects and control how names, access, and organization work.

Think of tables as files and schemas as folders/packages within the same database.

Combining schemas with partitioning:

CREATE TABLE sales.orders (
  id bigint,
  created_at date,
  region text
) PARTITION BY RANGE (created_at);
CREATE TABLE sales.orders_2026
  PARTITION OF sales.orders
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

So far, everything lives in sales.

But PostgreSQL does not require every partition to live in the same schema as the parent(This is where it gets tricky ):

CREATE SCHEMA sales_hot;
CREATE SCHEMA sales_cold;
CREATE TABLE sales_hot.orders_2026_01
  PARTITION OF sales.orders
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE sales_cold.orders_2025_12
   PARTITION OF sales.orders
  FOR VALUES FROM ('2025-12-01') TO ('2026-01-01');

This can be intentional. Maybe hot partitions live in one schema, archived partitions live in another, or tenant-specific partitions live under tenant schemas.

But it can also create a quiet trap.

The parent table says sales.orders. The actual child table might be sales_cold.orders_2025_12. If your role, dump tool, migration process, or maintenance job only has access to sales, it may not have enough access to work with the partition family.

3. The Subpartition Trap

Subpartitioning means a partition is itself partitioned.

For example, you might first partition by month, then subpartition by region:

CREATE TABLE sales.orders (
  id bigint,
  created_at date,
  region text,
  amount numeric
) PARTITION BY RANGE (created_at);


CREATE TABLE sales.orders_2026_01
  PARTITION OF sales.orders
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01')
  PARTITION BY LIST (region);

CREATE TABLE sales_us.orders_2026_01_us
  PARTITION OF sales.orders_2026_01
  FOR VALUES IN ('US');

CREATE TABLE sales_eu.orders_2026_01_eu
  PARTITION OF sales.orders_2026_01
  FOR VALUES IN ('EU');

This design is powerful.
It can improve data lifecycle management, partition pruning, and operational isolation.

It also makes the object graph deeper:

sales.orders
  -> sales.orders_2026_01
       -> sales_us.orders_2026_01_us
       -> sales_eu.orders_2026_01_eu

Here is the interesting question:

If a PostgreSQL user has permissions on sales.orders and sales.orders_2026_01, does it automatically have everything it needs for sales_us.orders_2026_01_us?

Not always.
It depends on what the role is doing.

If the application only queries the parent table, PostgreSQL can often route the query through the parent. But many operational tools do not only touch the parent. They inspect partitions directly, export individual partition data, load into child tables, recreate DDL, validate row counts, check indexes, or query catalog metadata for each relation.

That is where the subpartition in a different schema becomes dangerous.

The parent table may look fine:

GRANT USAGE ON SCHEMA sales TO postgres_user;
GRANT SELECT ON sales.orders TO postgres_user;

But the leaf subpartition lives somewhere else:

sales_us.orders_2026_01_us

If the PostgreSQL user does not have schema and table privileges there, a direct operation can fail:

GRANT USAGE ON SCHEMA sales_us TO postgres_user;
GRANT SELECT ON sales_us.orders_2026_01_us TO postgres_user;

This is one of those bugs that feels unfair because the parent table is visible, but the physical data is hiding behind another permission boundary.

4. A Small Audit Query You Can Run

Here is a useful query to list a partition tree with schemas:

WITH RECURSIVE partition_tree AS (
  SELECT
    parent.oid AS root_oid,
    parent.oid AS relid,
    parent_ns.nspname AS schema_name,
    parent.relname AS table_name,
    0 AS depth
  FROM pg_class parent
  JOIN pg_namespace parent_ns ON parent.relnamespace = parent_ns.oid
  WHERE parent_ns.nspname = 'sales'
    AND parent.relname = 'orders'

  UNION ALL

  SELECT
    partition_tree.root_oid,
    child.oid AS relid,
    child_ns.nspname AS schema_name,
    child.relname AS table_name,
    partition_tree.depth + 1
  FROM partition_tree
  JOIN pg_inherits ON pg_inherits.inhparent = partition_tree.relid
  JOIN pg_class child ON child.oid = pg_inherits.inhrelid
  JOIN pg_namespace child_ns ON child_ns.oid = child.relnamespace
)
SELECT
  repeat('  ', depth) || schema_name || '.' || table_name AS partition_object
FROM partition_tree
ORDER BY depth, partition_object;

Use it as a map. If you see schemas you did not expect, that is where you should check permissions next.

For privileges, you can inspect table access like this:

SELECT
  table_schema,
  table_name,
  privilege_type,
  grantee
FROM information_schema.table_privileges
WHERE grantee = 'postgres_user'
ORDER BY table_schema, table_name, privilege_type;

And schema access like this:

SELECT
  nspname AS schema_name,
  has_schema_privilege('postgres_user', oid, 'USAGE') AS has_usage
FROM pg_namespace
WHERE nspname IN ('sales', 'sales_us', 'sales_eu')
ORDER BY nspname;

These queries turn a vague permissions problem into a visible checklist.

Originally published on Medium.