Back to Devoured - May 14, 2026
DEVOURED
Strong views on PostgreSQL VIEWs

Strong views on PostgreSQL VIEWs

Data databasebackendsqlpostgresql boringSQL
PostgreSQL views, while offering logical abstraction, create hidden complexities through rigid schema dependencies, attribute number coupling, and the "SELECT *" trap, making schema evolution painful despite transactional DDL and query planner improvements.
What: The article by boringsql.com argues that PostgreSQL views are essentially stored rewrite rules (macros), not physical tables. This design choice leads to challenges like views being tied to underlying table column attribute numbers rather than names, preventing common DDL operations (DROP, ALTER TYPE) on referenced columns without dropping and recreating the entire view dependency tree. The SELECT * in views freezes the column list at creation, failing to pick up new columns automatically and still blocking DDL, making it a "trap."
Why it matters: PostgreSQL's design prioritizes compile-time safety and consistency over the runtime flexibility offered by some other databases, resulting in high friction for schema evolution when views are heavily used. This trade-off forces developers into manual dependency management and reinforces an "architect's stigma" against views, despite their potential for abstraction and security.
Takeaway: When using PostgreSQL views, always specify explicit column lists instead of SELECT *, keep view dependency chains shallow, and be prepared to script out full drop-alter-recreate sequences within a transaction for any structural changes to underlying tables.
Deep dive
  • PostgreSQL views are effectively stored SQL rewrite rules (macros) that the planner expands at query time, not physical tables.
  • This design means views are coupled to underlying table column attribute numbers, not names, leading to rigidity.
  • DDL operations like ALTER TABLE customers DROP COLUMN name or ALTER COLUMN email TYPE TEXT are blocked if a view depends on that column, requiring the view and its dependents to be dropped and recreated.
  • SELECT
  • in a view body is a "trap" because it expands and freezes the column list at view creation time, ignoring subsequent column additions to the base table and still blocking column drops.
  • PostgreSQL's DDL is transactional, allowing the entire drop-alter-recreate-regrant sequence for views to be wrapped in a BEGIN/COMMIT block for safety, which is a key advantage over some other databases that auto-commit DDL.
  • However, this transactional DDL still requires AccessExclusiveLock on the objects, leading to potential "stop-the-world" windows on busy systems.
  • Writable views are only supported for simple cases (single base table, no joins/aggregates) and lose writability with complex transformations, requiring manual INSTEAD OF triggers.
  • The article uses recursive CTEs on pg_depend to demonstrate how to programmatically discover view dependencies, which is a workaround for the lack of user-facing tools for view evolution.
  • The author suggests a "missing primitive": a real ALTER VIEW DROP COLUMN, ADD COLUMN, ALTER COLUMN TYPE that would make views safer to evolve, noting that pg_dump uses a similar placeholder trick for handling view cycles.
Decoder
  • DDL (Data Definition Language): SQL statements used to define or modify database structure, such as CREATE TABLE, ALTER TABLE, CREATE VIEW, DROP VIEW.
  • Transactional DDL: The ability to wrap DDL statements within a transaction (BEGIN/COMMIT/ROLLBACK), allowing all changes to be committed or rolled back atomically.
  • Attribute number: An internal PostgreSQL identifier for a column's position within a table or view, which can lead to rigidity when schema changes occur.
  • AccessExclusiveLock: The highest level of lock in PostgreSQL, preventing all concurrent access (reads and writes) to a table or view, typically acquired during DDL operations.
  • pg_depend: A PostgreSQL system catalog table that stores dependencies between database objects.
  • pg_rewrite: A PostgreSQL system catalog table that stores rewrite rules, including the definitions of views.
  • Recursive CTE (Common Table Expression): A SQL query construct that can refer to itself, allowing for traversal of hierarchical or graph-like data, used here to find transitive view dependencies.
Original article

Full article content is not available for inline reading.

Read the original article →

Explore all 40 articles in today's digest

Fresh dev news, AI-digested daily. Never miss a trend at devoured.fyi