Postgresql – Will switching from Concrete Table Inheritance to Single Table Inheritance improve the performance of the queries

database-designinheritanceperformancepostgresql

I currently have an application that allows people to view and share 10 different types of posts (ex SportsPost, WorldNewsPost, TechnologyPost, etc). All posts types are conceptually the same, but they share very few columns. For example, all posts have user_id, description, email_on_question, and email_on_bid columns, but on top of this will have 10 – 20 unique fields. All in all, I'd say about 8 out 60 columns are shared between post types.

Because of the vast number of unique columns, and because some post types have unique behavior and children, I initially decided to go with Concrete Table Inheritance – each object is represented by a separate table, with shared columns being duplicated on each table:

-- SportsPost -- | -- WorldNewsPost -- | -- TechnologyPost --
user_id            user_id               user_id
description        description           description
team               location              industry

The problem with this approach is that it's very difficult to query and aggregate all posts. I have a dashboard view which lists the top 20 most recently created posts, and in order to retrieve the data for this view I have to execute a separate query on 10 different tables! I execute all 10 queries inside of a transaction to save myself from doing multiple roundtrips, but I'm still concerned that the performance of the query will be very poor as the application scales and new post types are created.

I'm considering switching over to Single Table Inheritance at this point. Instead of executing 10+ queries for the dashboard view, I'll only need to execute one query. Does this sound like a reasonable approach? Are there any drawbacks of STI (other than NULL columns) that I'm not aware of?

Best Answer

If you are primarily concerned with the complexity of getting the 20 most recent records, you could either write a view that does a UNION ALL of the tables, or create an empty parent table with all the common columns and have all the other table inherit using the PostgreSQL-specific inheritance feature, to hide the visual complexity of the query. Although it probably won't improve the performance of the query.

Changing the structure as you suggest would very likely make the specific query you asked about faster, but other queries that query only records of one type would probably get worse. You have to decide which queries are more common and more important.

Related Solutions

Single Table vs Multiple Table Design for Distinct Records Queried Together in PostgreSQL

I won't put all of them together. Even if you query them together, it may occur that your application will be able to post multiple images per post or multiple events per post or multiple posts per event.

In this case you would be able to save space. The JSON may be a good idea, but it would be better (in view of indices) to use multiple tables. This way you can provide indices which may improve the query process.

Another idea could be to use permutation. Which means you have a global table which holds many different types with the same structure. For example:

CREATE TABLE types(
    id int,
    type nvarchar(50),
    property1 int,
    property2 nvarchar(50)
)

This way you can store a value with Type = 'Image' which holds a property1 (order number) and a property2 (a path). And additionally you can hold an event like Type = 'Event' which holds the date in property1 and the name in property2. Just as an example.

PostgreSQL Table Inheritance Explained

There is nothing in your question that would speak against inheritance.

One major limitation of the current Postgres implementation of inheritance is that FK, PK and UNIQUE constraints only apply to individual tables, not including children. But that seems to match what you need exactly: Create PK and FK constraints on each child table individually.

nba_players, nfl_players etc. could conveniently inherit from a general parent table players (possibly in a separate schema for empty parent tables). ALTER TABLE on the parent cascades to all children (except for ALTER TABLE ... RENAME). This makes it easy to run queries on all player tables together by targeting the parent.

If you never need to look at all player tables together or cascade changes to all tables, it might be better to just keep a template table (possibly in a separate schema for template tables) and create instances with CREATE TABLE ... (LIKE ...):

CREATE TABLE nba_players(LIKE players);

Best Answer

Related Solutions

Single Table vs Multiple Table Design for Distinct Records Queried Together in PostgreSQL

PostgreSQL Table Inheritance Explained

Related Question