Database Views
Named SQL queries stored in the database that act as virtual tables, simplifying complex queries and providing a stable abstraction layer over the underlying schema.
Description
A database view is a stored SELECT query that behaves like a read-only table. When you query a view, the database executes the underlying query and returns the results. Views do not store data themselves -- they are computed on every access. This makes them useful for encapsulating complex JOINs, aggregations, or filters behind a simple interface. For example, a view called active_subscriptions can hide the multi-table JOIN and status filtering logic, so application code queries it like a single table.
Views serve as an abstraction layer that decouples application code from the physical schema. If you refactor the underlying tables (splitting a table, renaming columns), you can update the view definition to maintain the same interface, avoiding changes in application code. This is particularly valuable for reporting queries and API endpoints that should remain stable while the schema evolves. Views also support row-level security by exposing only specific columns or filtered rows to certain database roles.
Views have limitations: they cannot be indexed (the underlying tables' indexes are used), they add a layer of query planning complexity, and deeply nested views (views referencing other views) can be difficult to debug and may produce suboptimal execution plans. Updatable views (views that support INSERT/UPDATE/DELETE) are possible in PostgreSQL but come with restrictions. For performance-critical read paths, consider materialized views instead.
Prompt Snippet
Create views for complex reporting queries and multi-table aggregations to provide a stable API surface over the physical schema. Name views with a v_ prefix or _view suffix to distinguish them from tables. Define views using CREATE OR REPLACE VIEW for safe updates. For multi-tenant systems, create row-level security views filtered by current_setting('app.tenant_id'). Avoid nesting views more than 2 levels deep -- if the planner produces suboptimal plans, flatten the query or switch to a materialized view. Document each view's purpose and refresh contract in a schema comment using COMMENT ON VIEW.Tags
Related Terms
Materialized Views
Database views that physically store their query results on disk, trading freshness for dramatically faster read performance on expensive aggregations.
Denormalization Strategies
Intentionally introducing redundant data into a normalized schema to optimize read performance by reducing expensive JOIN operations.
Database Normalization (1NF-3NF)
The process of organizing database columns and tables to reduce data redundancy and improve data integrity through progressive normal forms.