Chapter 5 part 3 - The Tables

Tables

A database is the logical container of the relations. A relation is the relational object making the data accessible. The first kind of relation we'll take a look is the table.
This is the fundamental storage unit for the data. PostgreSQL implements various kind of tables having the full support, a partial implementation or no support at all for the durability.
The table creation is performed using the standard SQL command CREATE TABLE.
The PostgreSQL implementation does not guarantee the data is stored in a particular order. This is a straight MVCC consequence Take a look to 5.7 for more information.

Logged tables

If executed without options, CREATE TABLE creates a logged table. This kind of table implements fully the durability being WAL logged at any time. The data is managed in the shared buffer, logged to the WAL and finally consolidated to the data file.

Unlogged tables

This kind of table were introduced in the 9.1. The data is still consolidated to the data file but the blocks aren't WAL logged. This make the write operations considerably faster at the cost of the data consistency. This kind of table is not crash safe and the database truncate any existing data during the crash recovery. Also, because there's no WAL record the unlogged tables aren't replicated to the physical standby.

Temporary tables

A temporary table's lifespan lasts the time of the connection. This kind of tables are useful for any in memory operation. The temporary table stays in memory as long as the amount of data is no bigger than temp_buffers seen in .

Table inheritance

As PostgreSQL is an Object Relational Database Management System, some of the object oriented programming concepts are implemented. The relations are referred generally as classes and the columns as attributes. The inheritance binds a parent table to one or more child tables which have the same parent's attribute structure. The inheritance can be defined at creation time or later. If a manually defined table shall inherit another the attribute structure shall be the same as the parent's.
The PostgreSQL implementation is rather confusing as the unique constraints aren't globally enforced on the inheritance tree and this prevents the foreign key to refer inherited tables. This limitation makes the table partitioning tricky.

Foreign tables

The foreign tables were first introduced with PostgreSQL 9.1, improving considerably the way the remote data can be accessed. A foreign table requires a called foreign data wrapper to define a foreign server. This can be literally anything. Between the contrib modules PostgreSQL has the file_fdw to create foreign tables referring CSV or COPY formatted flat files. In the the version 9.3 finally appeared the postgres_fdw with the read write for the foreign tables. The postgres_fdw implementation is similar to dblink with a more efficient performance management and the connection caching.