New In Postgres 12: Generated Columns (pgdash.io)

Article summary

PostgreSQL 12 introduces a new feature called generated columns, which allows for computed columns that can be stored and updated automatically. This feature can simplify application code and improve performance by reducing the need for triggers and other workarounds. Generated columns can be used for various purposes, such as full-text search and geospatial calculations. The article provides examples and explanations of how to use generated columns in PostgreSQL.

Main themes

• Generated Columns
• PostgreSQL 12
• Database Performance
• Application Code Simplification
• Data Modeling

What commenters say

• Some developers prefer to keep business logic in the application code, rather than in the database, for easier testing and maintenance. Others argue that certain types of calculations are better suited for the database, where they can be performed more efficiently and consistently. The use of generated columns can introduce additional complexity to the database schema, making it harder to understand and maintain. Having business logic in the database can be beneficial when there are multiple applications accessing the same data, ensuring consistency and accuracy across all applications. The decision to use generated columns depends on the specific use case and the trade-offs between application code simplicity, database performance, and data consistency. Some argue that database logic can be tested and validated just like application code, using tools and frameworks specifically designed for database testing. Others believe that placing business logic in the database can be a layering violation, while others see it as a natural fit for certain types of domain logic.