4 Table and index mistakes
In this chapter
- Table inheritance: an unusual feature
- Why partitioning is important and how to get it right
- Using the right type of key and index for your tables
Some PostgreSQL particularities give it additional flexibility compared to other Relational Database Management Systems (RDBMSs) and can enable powerful and expressive database designs. However, these same features also have the potential to become pitfalls when coming to Postgres with preconceptions from other systems or if the documentation is misunderstood.
4.1 Table inheritance
At the time of writing, PostgreSQL describes itself as a "powerful, open source object-relational database system" and this may throw some people off, as it seems to be a description from another era in computing. It may well be an outdated reference to object-oriented programming (OOP) which, as some of you may remember, used to be all the rage in past years. The PostgreSQL project was very keen on showing support for OOP and one of the advanced features that were particularly relevant to OOP was table inheritance.
Simply put, table inheritance lets you create tables that inherit columns from other tables — the same way that object classes might inherit variables and methods from their parent classes. Using it is as simple as CREATE TABLE (…) INHERITS ….
For example, if we think of a meeting as a kind of event, then we can create a table meetings that includes the attributes of table events: