Partitions

󰃭 2024-08-22

-- Create a new database
CREATE DATABSE par;

postgres=# select pg_database.oid, pg_database.datname from pg_database;
  oid  |    datname    
-------+---------------
     5 | postgres
 16388 | testdump
     1 | template1
     4 | template0
 16395 | partitiontest
 24592 | testdb
 24649 | par

postgres=# \c par
You are now connected to database "par" as user "postgres".

par=# CREATE TABLE foo (
    id int not null,
    name VARCHAR(50),
    amount INT NOT NULL
) PARTITION BY RANGE(amount);
CREATE TABLE

par=# CREATE INDEX idx_amount ON foo(amount);
CREATE INDEX

par=# select pg_relation_filepath('foo');
 pg_relation_filepath 
----------------------

(1 row)

par=# \d foo
                 Partitioned table "public.foo"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 id     | integer               |           | not null | 
 name   | character varying(50) |           |          | 
 amount | integer               |           | not null | 
Partition key: RANGE (amount)
Indexes:
    "idx_amount" btree (amount)
Number of partitions: 0


par=# select pg_relation_filepath('foo_pkey');
 pg_relation_filepath 
----------------------

(1 row)

-- If you're curious about the oid that less than your table's oid, you can query it by following SQL. And it's the ID sequence of the table.
par=# select relname, relkind 
from pg_class 
where oid = 24650;
  relname   | relkind 
------------+---------
 foo_id_seq | S
(1 row)


par=# CREATE TABLE foo_0_99 PARTITION OF foo
    FOR VALUES FROM (0) TO (99);
CREATE TABLE

par=# CREATE TABLE foo_100_199 PARTITION OF foo
    FOR VALUES FROM (100) TO (199);
CREATE TABLE

par=# select relname, relkind, oid from pg_class where relname like '%foo%' order by relname;
        relname         | relkind |  oid  
------------------------+---------+-------
 foo                    | p       | 24683
 foo_0_99               | r       | 24687
 foo_0_99_amount_idx    | i       | 24690
 foo_100_199            | r       | 24691
 foo_100_199_amount_idx | i       | 24694
(5 rows)

par=# select *  from foo;
 id | name | amount 
----+------+--------
(0 rows)

Open another terminal for obersve files in $PGDATA

cd $PGDATA/base/24649
ll
  • 8192 Bytes for each empty table cause index’s there

So far it’s an empty database with an empty table, but lots of page file are already located.

typedef struct PartitionBoundInfoData
{
	PartitionStrategy strategy; /* hash, list or range? */
	int			ndatums;		/* Length of the datums[] array */
	Datum	  **datums;
	PartitionRangeDatumKind **kind; /* The kind of each range bound datum;
									 * NULL for hash and list partitioned
									 * tables */
	Bitmapset  *interleaved_parts;	/* Partition indexes of partitions which
									 * may be interleaved. See above. This is
									 * only set for LIST partitioned tables */
	int			nindexes;		/* Length of the indexes[] array */
	int		   *indexes;		/* Partition indexes */
	int			null_index;		/* Index of the null-accepting partition; -1
								 * if there isn't one */
	int			default_index;	/* Index of the default partition; -1 if there
								 * isn't one */
} PartitionBoundInfoData;

In theory you can have INT_MAX partitions without nindexes overflow, But you will get a OOM before that cause partition isn’t free even for empty partitioned table.