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: This is used to decrease the whole table size by splitting rows over multiple tables; it is supported by table inheritance...