Tablespaces: where tables live

The physical storage of a PostgreSQL database: tablespaces, files, 8 kB pages, dead rows, VACUUM, fill factor, measuring sizes and moving a table, with the Oracle vocabulary alongside.

26 cards
6 topics
Free

Study this deck

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

Concepts
Questions
Sort and link
The numbers
Scenarios
Understand

A few cards from the deck

Tablespace: what is it?
Show the answer
A named storage location, tied to a directory on a disk, where the database keeps the files of some tables and indexes.
It separates the logical name from the physical path: a table moves to another disk without changing a single query.
Which function gives the size of a table with its indexes and TOAST data?
- pg_total_relation_size('commandes')
- pg_relation_size('commandes')
- pg_table_size('commandes')
- pg_database_size('commandes')
Show the answer
pg_total_relation_size('commandes')
pg_relation_size only measures the table's main file, pg_table_size adds TOAST but not the indexes, pg_database_size expects a database name. Only pg_total_relation_size adds everything up.
A PostgreSQL page is … by default; a large field that does not fit is stored aside by the … mechanism.
Show the answer
A PostgreSQL page is 8 kB by default; a large field that does not fit is stored aside by the TOAST mechanism.
A row never spans several pages: large values (long text, JSON) are compressed or moved to a TOAST table attached to the table.
Page: what is it?
Show the answer
The unit of reading and writing in data files, 8 kB by default in PostgreSQL, which holds several rows; Oracle calls it a block.
The database always reads whole pages: a 100-byte row costs a read of 8 kB. The more rows per page, the fewer reads.

Study this deck