PostgreSQL Query Rewriting: Views

Tip: Create View

CREATE VIEW active_posts AS
SELECT id, title, author_id FROM posts WHERE published = true;

Gotcha: Updatable Views

Simple views (single table, no aggregates) are automatically updatable.

Tip: INSTEAD OF Trigger

CREATE OR REPLACE RULE update_active_posts AS
ON UPDATE TO active_posts DO INSTEAD
UPDATE posts SET title = NEW.title WHERE id = NEW.id AND published = true;

Gotcha: View Performance

Views don't improve performance. They're just stored queries. Use materialized views for performance.

Tip: Security Through Views

GRANT SELECT ON active_posts TO analyst;

Restrict access to sensitive columns.

Gotcha: CHECK OPTION

CREATE VIEW active_posts AS
SELECT * FROM posts WHERE published = true
WITH CHECK OPTION;

Prevents inserting non-published posts through the view.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

PostgreSQL views are powerful for abstracting complex queries, but they're not performance magic. A view is a stored query that the planner expands inline — it doesn't cache results like a materialized view. I use views for: (1) column-level security (exposing only certain columns), (2) backwards compatibility during schema refactoring, and (3) simplifying application queries. What I don't use them for: performance optimization.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)