Partitioning is another term for physically dividing large tables in YugabyteDB into smaller, more manageable tables to improve performance.
In the following example, I have a primary table named MOVIES that is partitioned by genre:
yugabyte=# \d+ movies
Table "public.movies"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-------------------+-----------+----------+--------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('movie_seq'::regclass) | plain | |
genre | character varying | | not null | | extended | |
title | character varying | | | | extended | |
Partition key: LIST (genre)
Indexes:
"movies_pkey" PRIMARY KEY, lsm (id HASH, genre ASC)
Partitions: movies_action FOR VALUES IN ('action'),
movies_comedy FOR VALUES IN ('comedy'),
movies_drama FOR VALUES IN ('drama'),
movies_horror FOR VALUES IN ('horror'),
movies_romance FOR VALUES IN ('romance'),
movies_scifi FOR VALUES IN ('scifi')
When selecting rows from the partition table, YugabyteDB transparently queries the underlying tables.
yugabyte=# SELECT title, genre FROM movies ORDER BY title;
title | genre
---------------------------------------+---------
Anchorman: The Legend of Ron Burgundy | comedy
Die Hard | action
Lethal Weapon | action
Oppenheimer | drama
Psycho | horror
Smile | horror
The Exorcist | horror
The Notebook | romance
This is Spinal Tap | comedy
(9 rows)
If you’d like to see the source table for each row, you can like this:
yugabyte=# SELECT title, genre, tableoid::regclass AS partition_source_table FROM movies ORDER BY title;
title | genre | partition_source_table
---------------------------------------+---------+------------------------
Anchorman: The Legend of Ron Burgundy | comedy | movies_comedy
Die Hard | action | movies_action
Lethal Weapon | action | movies_action
Oppenheimer | drama | movies_drama
Psycho | horror | movies_horror
Smile | horror | movies_horror
The Exorcist | horror | movies_horror
The Notebook | romance | movies_romance
This is Spinal Tap | comedy | movies_comedy
(9 rows)
Have Fun!
