Lesser-known Postgres features (hakibenita.com)

Article summary

The article discusses lesser-known features of PostgreSQL, including getting the number of updated and inserted rows in an upsert, granting permissions on specific columns, and using dollar quoting. These features can improve database management and querying. The article also highlights the importance of understanding database-specific features. PostgreSQL 14 has recently been released, making it a good time to explore these features.

Main themes

• PostgreSQL features
• database management
• portability vs performance
• access control
• database migration
• OLTP systems

What commenters say

• Using database-specific features can make an application less portable, but this may not be a significant concern in practice. Some argue that the benefits of using database-specific features, such as improved performance and security, outweigh the potential drawbacks. Others believe that managing access control lists in the application layer is generally better for OLTP systems. Database-specific features can actually make it easier to manage complex data and improve application performance. The idea of portable SQL is unrealistic, and using database-specific features is necessary for optimal performance. Migrating databases is rare, and when it happens, it is usually a complex process that requires significant effort regardless of the database features used.
• comments on the article highlight the trade-offs between using database-specific features and maintaining portability. The importance of understanding database-specific features is emphasized, as well as the need to consider the specific use case and requirements of the application. Some commenters share their experiences with migrating databases and the challenges that come with it.