Will switching from Concrete Table Inheritance to Single Table Inheritance improve the performance of my queries?

Question

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?

Answer 1

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.