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.