Table partitioning is used to increase performance by physically arranging data in the hard disk based on a certain grouping criteria. There are two techniques for table partitioning:
- Vertical table partitioning: The table is divided into several tables in order to decrease the row size. This allows a faster sequential scan on divided tables, as a relation page holds more rows. To explain, let's assume that we want to store pictures for each client in the database by adding a column of the byte or blob type to the client table. Now, since we have a table that might contain a big row, the number of rows per page is reduced. To solve this, one can create another table that references the client table, as follows:
CREATE TABLE client_picture (
id int primary key,
client_id int references client(id),
picture bytea NOT NULL
);
- Horizontal table partitioning:...
