A Powerful Addition to Your Postgres Toolbelt: Table Inheritance

Table inheritance is a less commonly used Postgres feature, but it has the power to save time with both data retrieval and database management.

In this article, I’ll cover how inheritance works in Postgres and provide some examples of when to use inheritance.

To follow along with the examples in this article, try Arctype's free SQL editor to quickly connect to a Postgres database:

What is Table Inheritance in Postgres?

Inheritance is one of the main principles of object-oriented programming. It is a process for deriving one object from another object so that they have shared properties.

Inheritance in PostgreSQL allows you to create a child table based on another table, and the child table will include all of the columns in the parent table.

Let's take a database that's used to store blueprints for different types of homes.

There are some things that we know every home will have such as: bedrooms, bathrooms, and kitchens. We can create a parent table to store these shared attributes.

Now let's say we wanted to add a blueprint for a home with a patio. This new blueprint is identical to our existing one, but with a new room. Instead of recreating the entire blueprint, we can create a new "child" table that inherits the parent table.

We now have a copy of the main parent blueprint with a new "patio" item, without creating a duplicated blueprint.

Why should I use inheritance?

The two main benefits are:

1. More performant queries
2. Easier database management

More performant queries

Inheritance splits data into smaller tables that inherit some of the parent's fields. This in effect partitions the data, improving the speed to retrieve data.

Imagine you are fetching data that is BETWEEN two dates. There is a parent table called year_sales and inherited tables with data for each month.

A command to retrieve all sales between 2020-10-1 and 2020-10-15 would only scan the table for the month of October .

Inherited tables also creates more manageable indexes. Each individual table contains less data, which speeds up search both with and without an index.

Easier database management

Making future schema changes is easier because you only have to make one change to the parent table and then it's propagated to each inherited table. This saves time and lessens the chances of accidental divergences.

Running maintenance commands such as a full vacuum or re-index of inherited tables will also happen without blocking other data.

Example 1: Using inheritance to store table statistics by month

One of the most popular use cases for table inheritance is storing information divided by months. This gives the benefit of partitioning your data for faster queries.

I've used this solution to architect schemas for situations including:

1. Process execution audits

Inherited tables can be used to track data that is continuously loaded/unloaded into the system, user requests and computational processes, and other important information for monitoring the system's health.

2. User actions auditing for critical modules in the application

You can create an audit system to track who changed the data in the system and when did they do it. If the system has many users, then there is a lot of data. So, to speed up data access, it is more performant to use table inheritance.

Let's dive into an example.

First, create schema “example1”:

CREATE SCHEMA example1
    AUTHORIZATION postgres;

Then create a parent logging table:

CREATE TABLE example1.logging
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    event_name character varying NOT NULL,
    start_time timestamp(6) without time zone NOT NULL,
    end_time timestamp(6) without time zone NOT NULL,
    CONSTRAINT february_log_pkey PRIMARY KEY (id, start_time, end_time)
)
TABLESPACE pg_default;

Create a child logging table for a specific month and year, which inherits fields from the parent table:

CREATE TABLE example1.january_log_2021
(
    CONSTRAINT start_time CHECK (start_time BETWEEN '2021-01-01' and '2021-01-31')
)
    INHERITS (example1.logging)
TABLESPACE pg_default;

The code contains a check constraint on the “start_time” column with the CHECK command. This keeps the date and time within January.

Fill in the logging table for January 2021:

INSERT INTO example1.january_log_2021(id,
    event_name, start_time, end_time)
    VALUES 
           (1, 'Log in', '2021-01-11 03:26:11', '2021-01-11 03:26:13'),
           (2, 'Log out', '2021-01-03 12:11:17', '2021-01-03 12:11:18'),
           (3, 'Upload file xml', '2021-01-06 16:14:28', '2021-01-06 16:14:59'),
           (4, 'Delete data', '2021-01-05 23:01:55', '2021-01-05 23:01:58');

We can check if the data was successfully inserted with a SELECT command:

SELECT * FROM example1.january_log_2021;

The data in each child table will also automatically be added to the parent table.

Example #2: Using inheritance to track the movement of ships

Another instance when I used inheritance was storing information about ships and their movements based on geolocation.

Each ship had both common and unique values. Because of this quality, I decided design the schema using inheritance, and I created a separate table for each ship based on the parent table.

Using an inheritance model gave us the following benefits:

1. When the application built the map of the ship's movement, it only had to reference the individual ship's table, increasing the speed of report building and monitoring.
2. Adding new specific fields for certain ships types did not require changing all tables.
3. We could retrieve baseline data for all ships using a single request.

Here's how we did it.

First create a new schema called “test”:

CREATE SCHEMA test
    AUTHORIZATION postgres;

Create a database with ships:

CREATE TABLE test.ship
(
    AIS_name text NOT NULL,
    type text NOT NULL,
    flag text NOT NULL,
    IMO character varying NOT NULL,
    MMSI character varying NOT NULL,
    callsign character varying NOT NULL,
    year_built character varying NOT NULL,
    length character varying NOT NULL,
    width character varying NOT NULL,
    draught character varying NOT NULL,
    speed character varying NOT NULL,
    AIS_class character varying NOT NULL,
    cargo character varying,
    CONSTRAINT ship_pkey PRIMARY KEY (MMSI)
)

TABLESPACE pg_default;

Create the table "sail_ship", which is a child to table “ship”:

CREATE TABLE test.sail_ship
(
    -- Inherited from table test.ship: AIS_name text NOT NULL,
    -- Inherited from table test.ship: type text NOT NULL,
    -- Inherited from table test.ship: flag text NOT NULL,
    -- Inherited from table test.ship: IMO character varying NOT NULL,
    -- Inherited from table test.ship: MMSI character varying NOT NULL,
    -- Inherited from table test.ship: callsign character varying NOT NULL,
    -- Inherited from table test.ship: year_built character varying NOT NULL,
    -- Inherited from table test.ship: length character varying NOT NULL,
    -- Inherited from table test.ship: width character varying NOT NULL,
    -- Inherited from table test.ship: draught character varying NOT NULL,
    -- Inherited from table test.ship: speed character varying NOT NULL,
    -- Inherited from table test.ship: AIS_class character varying NOT NULL,
    -- Inherited from table test.ship: cargo character varying,
    id_sail integer NOT NULL,
    course text NOT NULL,
    navigation_status text NOT NULL,
    CONSTRAINT sail_ship_pkey PRIMARY KEY (id_sail)
)
    INHERITS (test.ship)
TABLESPACE pg_default;

Now fill in table "sail_ship":

INSERT INTO test.sail_ship(AIS_name, type, flag, IMO, MMSI, callsign, year_built, length, width, draught, speed, AIS_class, cargo, id_sail, course, navigation_status)
    VALUES ('A P T JAMES', 'Ferry', 'Trinidad and Tobado', '9877717', '362254000', '9YNM', '2020', '94 m', '26 m', '2.9 m/', '13.1 kn/20.2 kn', '-', '-', 1, '-', '-'),
           ('MOZART', 'Container ship', 'Liberia', '9337274', '636018378', 'A8MA9', '2007', '222 m', '30 m', '10.4 m /', '12.9 kn / 23.6 kn', '-', 'Containers', 2, '-', '-'),
           ('ALIANCA SKY', ' Bulk carrier', 'Liberia', '9128441', '636014513', 'A8UK6', '1997', '186 m', '30 m', '8.8 m /', '10.2 kn / 17.2 kn', '-', 'Agricultural Commodities', 3, '-', '-'),
           ('XXX7', 'Ship', 'China', '-', '412444890', 'BVMY5', '-', '-', '-', '-/', '60.5 kn / 66.1 kn', '-', '-', 4, '-', '-');

The table “sail_ship” inherits all the columns of its parent table, “ship".

Let's also add some data to the parent table and see what happens:

INSERT INTO test.ship(AIS_name, type, flag, IMO, MMSI, callsign, year_built, length, width, draught, speed, AIS_class, cargo)
    VALUES ('A P T JAMES', 'Ferry', 'Trinidad and Tobado', '9877717', '362254000', '9YNM', '2020', '94 m', '26 m', '2.9 m/', '13.1 kn/20.2 kn', '-', '-');

As you can see in the picture, the field has been added, but the primary key is repeated, and so it is no longer unique

This is one of the caveats with inheritance in Postgres. No errors were found when adding to the child table, even though it violated the unique primary key.

We can remove these duplicates from child tables, by retrieving data from the “ship” table using the ONLY operator.

Here, the ONLY keyword indicates that the query should only be applied to the “ship” table and not to tables below ship in the inheritance hierarchy.

SELECT AIS_name, type, flag, year_built FROM ONLY test.ship;

Caveats to be aware of with PostgreSQL inheritance

1. You can’t apply RENAME with ALTER TABLE commands to the child tables;
2. The uniqueness of primary keys and foreign keys is not inherited. The inheritance mechanism is not capable of automatically distributing data from INSERT or COPY commands across tables in an inheritance hierarchy. INSERT inserts only into the specified table and no other;
3. The user must have access rights to both the parent table and the child table;
4. Columns will have to be dropped manually. If using the DROP COLUMN command to the parent table under the condition of cascading deletion, it cannot affect the child table.

Conclusion

In this article we covered:

• How inheritance in Postgres works
• Why you should use inheritance in your applications
• Two examples for how inheritance is used in real applications

If you are looking for a SQL editor that makes working with databases even easier, try Arctype today for free: