The SQL Query Roadtrip: Query Rewriting

By Jesús Espino • • 11 min read
#PostgreSQL #Database #SQL
📚 The SQL Query Roadtrip (4 of 4) ▼
  1. 1. Overview
  2. 2. Connections and Communication
  3. 3. Parsing and Analysis
  4. 4. Query Rewriting You are here
Query Rewriting

In the previous article, we explored how PostgreSQL transforms SQL text into a validated Query tree through parsing and semantic analysis. By the end of that journey, PostgreSQL knows that your tables exist, your columns are valid, your types match up, and your query makes sense.

But before the planner can figure out how to execute your query, there’s one more critical transformation step: query rewriting.

Let me show you why this matters. When you write this simple query:

SELECT name FROM user_view WHERE active = true;

PostgreSQL doesn’t necessarily execute what you wrote. If your database has views, row-level security policies, or other rewrite rules, your query might actually be rewritten to something like:

SELECT u.name FROM users u
JOIN departments d ON u.dept_id = d.id
WHERE u.status = 'active' AND d.active = true
  AND u.region = current_user_region();  -- Added automatically!

This transformation would happen if user_view were a view that joins multiple tables, and you had row-level security policies that restrict access by region. The rewriter would automatically expand the view definition and apply security policies—all transparently.

The rewriter serves as PostgreSQL’s transformation engine, implementing sophisticated database abstractions while preserving query semantics. In this article, we’ll explore how PostgreSQL’s rule system works, how you can create custom rewrite rules for business logic, how views are implemented as transformation rules, and how row-level security (RLS) policies are applied automatically during rewriting.

Before we dive into the mechanics of rules and security policies, let’s understand where the rewriter fits in PostgreSQL’s overall query processing flow.

The Rewriter’s Pipeline

The rewriter receives the validated Query tree from the analyzer and transforms it before passing it to the planner:

digraph RewriteProcess {
    rankdir=TB;
    node [shape=box, style="rounded,filled", fillcolor=lightblue];

    // Input
    ParserAnalyzer [label="Parser/Analyzer", fillcolor=lightgreen];
    QueryTree [label="Query Tree", shape=cylinder, fillcolor=lightyellow];

    // Rewriter
    Rewriter [label="Rewriter", fillcolor=orange, fontsize=14];

    subgraph cluster_rewriter {
        label="Rewriting Process";
        style=filled;
        fillcolor=lightgray;

        ApplyRules [label="Apply Rules\n(including Views)"];
        ApplyRLS [label="Apply Row Level\nSecurity"];
    }

    RewrittenQueries [label="Rewritten Query Trees", shape=cylinder, fillcolor=lightyellow];

    // Output
    Planner [label="Planner", fillcolor=lightgreen];

    // Main flow
    ParserAnalyzer -> QueryTree;
    QueryTree -> Rewriter;
    Rewriter -> ApplyRules;
    ApplyRules -> ApplyRLS;
    ApplyRLS -> RewrittenQueries;
    RewrittenQueries -> Planner;
}

The diagram shows the complete journey through rewriting. The Parser/Analyzer produces a validated Query tree—this is what we covered in the previous article. This Query tree enters the rewriter, which performs two main transformations:

First, Apply Rules expands views into their underlying tables and applies custom rewrite rules. When you query a view, this step replaces the view reference with the actual query definition. Custom rules can also add additional operations or replace queries entirely.

Second, Apply Row Level Security adds security predicates to enforce access policies. If you have RLS policies defined, this step automatically injects WHERE conditions that restrict which rows users can see or modify.

The result is one or more Rewritten Query Trees that flow to the planner. Notice that the rewriter can produce multiple query trees from a single input—this happens when custom rules add additional queries alongside the original.

All of this happens through PostgreSQL’s rule system, implemented in the QueryRewrite() function (in src/backend/rewrite/rewriteHandler.c). But what exactly are these “rules” we keep mentioning? Let’s explore how they work.

The Rule System: PostgreSQL’s Transformation Engine

PostgreSQL’s rule system provides the foundation for query rewriting. Rules are stored transformations that PostgreSQL applies automatically when certain conditions are met. They’re stored in the pg_rewrite system catalog and contain everything needed to transform queries.

Each rule has several key components:

Rule Events specify when the rule should fire. PostgreSQL supports rules for SELECT, INSERT, UPDATE, and DELETE operations. A rule fires when its target relation is accessed with the matching operation type.

Rule Types fall into two categories:

  • INSTEAD rules replace the original query entirely. When an INSTEAD rule fires, PostgreSQL executes the rule’s action instead of the original operation.
  • ALSO rules add additional queries alongside the original. The original operation still executes, but the rule adds extra actions.

Rule Conditions determine whether a rule should be applied. Rules can be unconditional (always apply) or conditional (only apply when specific criteria are met).

Rule Actions contain the replacement or additional queries that PostgreSQL executes. These actions are stored as Query trees in the catalog and can include complex multi-table operations.

You can explore the rules in your database by querying the pg_rewrite catalog:

SELECT
    rulename,
    ev_type,
    ev_class::regclass as table_name,
    is_instead,
    ev_qual IS NOT NULL as has_condition
FROM pg_rewrite
WHERE ev_class = 'your_table'::regclass;

With this foundation in place, let’s see the rule system in action by creating a custom rule.

Creating Custom Rewrite Rules

PostgreSQL lets you create custom rules manually for sophisticated behavior. Rules can be either INSTEAD (replace the original operation) or ALSO (add additional operations), and they can be conditional (only fire when certain criteria are met) or unconditional (always apply).

Let me show you a practical example using a conditional ALSO rule: an audit logging system that tracks changes to admin users. This ALSO rule will run in addition to the original UPDATE—the update still happens, but we automatically log changes to admin accounts.

-- Create an audit table
CREATE TABLE user_audit (
    audit_id SERIAL PRIMARY KEY,
    user_id INT,
    action TEXT,
    old_values JSONB,
    new_values JSONB,
    audit_time TIMESTAMP DEFAULT NOW()
);

-- Create a conditional rule that only audits updates to admin users
CREATE RULE audit_admin_updates AS
    ON UPDATE TO users
    WHERE (OLD.role = 'admin' OR NEW.role = 'admin')
    DO ALSO
        INSERT INTO user_audit (user_id, action, old_values, new_values)
        VALUES (OLD.id, 'UPDATE',
                row_to_json(OLD),
                row_to_json(NEW));

This rule has several interesting properties:

  • Event: Fires on UPDATE operations to the users table
  • Condition: WHERE (OLD.role = 'admin' OR NEW.role = 'admin') - only fires when admin users are involved
  • Action: INSERT into the audit table
  • Type: ALSO rule - runs in addition to the original UPDATE (not instead of)

Let’s see it in action:

-- This update won't trigger the audit rule
UPDATE users SET email = 'new@email.com' WHERE role = 'customer';
-- Result: email updated, no audit record created

-- This update WILL trigger the audit rule
UPDATE users SET email = 'admin@company.com' WHERE role = 'admin';
-- Result: email updated AND audit record created automatically

When the rule fires, PostgreSQL rewrites your single UPDATE statement into two operations:

-- Original
UPDATE users SET email = 'admin@company.com' WHERE role = 'admin';

-- Rewritten (simplified representation)
BEGIN;
  UPDATE users SET email = 'admin@company.com' WHERE role = 'admin';
  INSERT INTO user_audit (user_id, action, old_values, new_values)
  VALUES (OLD.id, 'UPDATE', row_to_json(OLD), row_to_json(NEW));
COMMIT;

The rewriter evaluates the rule condition during query processing and adds the audit INSERT automatically whenever admin users are affected. This happens completely transparently—your application just issues UPDATE statements, and PostgreSQL ensures admin changes are logged.

Custom rules provide powerful capabilities for implementing business logic at the database level—audit logging, data validation, automatic denormalization, and more. However, they’re less commonly used in modern applications because triggers often provide similar functionality with more flexibility. The key difference is that rules work through query rewriting (transforming SQL before execution) while triggers execute procedural code during execution.

Now that we’ve seen custom rules, let’s look at how PostgreSQL uses the rule system to implement views.

How Views Become Rules

Here’s the key insight: when you create a view in PostgreSQL, you’re not storing data—you’re storing a transformation rule. Behind the scenes, PostgreSQL is not only creating the view object but also storing a rewrite rule that tells it “whenever someone queries this view, replace it with this underlying query.”

When you create this view:

CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE active = true;

PostgreSQL internally creates a rewrite rule equivalent to:

CREATE RULE "_RETURN" AS
    ON SELECT TO active_users
    DO INSTEAD
        SELECT id, name, email FROM users WHERE active = true;

The rule says: “On SELECT from active_users, do INSTEAD this other query.” In this way, views act like syntactic sugar for INSTEAD rules on SELECT operations—a convenient way to create rewrite rules without writing CREATE RULE explicitly.

You can see this rule yourself:

SELECT
    rulename,
    ev_type,
    ev_class::regclass as table_name,
    is_instead
FROM pg_rewrite
WHERE ev_class = 'active_users'::regclass;

This query reveals:

  • rulename: _RETURN (PostgreSQL’s standard name for view rules)
  • ev_type: 1 (SELECT event)
  • table_name: active_users
  • is_instead: true (INSTEAD rule—replaces the original query)

When you query the view, PostgreSQL applies this rule and rewrites your query to use the base table instead.

We’ve seen how the rule system handles custom transformations and view expansion. But the rewriter has another critical responsibility: enforcing data security through row-level access policies.

Automatic Security: Row-Level Security Policies

Here’s where it gets really interesting. PostgreSQL’s row-level security (RLS) system integrates directly into the query rewriter. When you enable RLS on a table, PostgreSQL automatically adds security conditions to every query—you don’t have to remember to check permissions in your application code.

The rewriter applies security policies through the get_row_security_policies() function (in src/backend/rewrite/rowsecurity.c), which examines each table reference and determines which policies apply based on the current user and operation type.

Let me show you a practical example:

CREATE TABLE documents (
    id INT PRIMARY KEY,
    title TEXT,
    content TEXT,
    owner_id INT,
    department TEXT
);

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_documents ON documents
FOR ALL TO employees
USING (owner_id = current_user_id() OR department = current_user_department());

This policy says: “Users can only see documents they own or that belong to their department.”

Now when an employee runs this innocent-looking query:

SELECT title FROM documents WHERE title LIKE '%budget%';

PostgreSQL automatically rewrites it to:

SELECT title FROM documents
WHERE title LIKE '%budget%'
  AND (owner_id = current_user_id() OR department = current_user_department());

The security condition is permanently added—it cannot be bypassed or optimized away. The application doesn’t need to know about the security policy; PostgreSQL enforces it automatically during rewriting.

We’ve explored the main components of query rewriting—custom rules, views, and row-level security. Now let’s see how they all work together in a single query that combines view expansion with automatic security enforcement.

Putting It All Together: A Complete Rewrite Example

Let’s trace through one final, complete example that demonstrates everything we’ve learned. Imagine we have:

-- Base table with RLS enabled
CREATE TABLE orders (
    id INT, customer_id INT, amount DECIMAL, region TEXT
);
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY regional_orders ON orders
    USING (region = current_user_region());

-- Simple view
CREATE VIEW large_orders AS
    SELECT * FROM orders WHERE amount > 1000;

Now we execute this query:

SELECT customer_id, amount FROM large_orders WHERE customer_id = 123;

Here’s how the rewriter transforms it:

Original:

SELECT customer_id, amount
FROM large_orders
WHERE customer_id = 123

After view expansion:

SELECT customer_id, amount
FROM orders
WHERE amount > 1000
  AND customer_id = 123

After security policy application:

SELECT customer_id, amount
FROM orders
WHERE amount > 1000
  AND customer_id = 123
  AND region = current_user_region()

This is the rewritten query that PostgreSQL passes to the planner. The view reference has been replaced with the base table, the view’s WHERE condition (amount > 1000) has been merged with the original query condition, and the security policy has been added as an additional WHERE clause. All of this happens automatically, transparently, during the rewriting phase.

Now that we’ve seen how the rewriter transforms queries behind the scenes, let’s explore why understanding this process matters for database developers and application architects.

Why This Matters

Understanding query rewriting helps explain some important PostgreSQL behaviors:

Performance through views: Views don’t inherently slow down queries. Because PostgreSQL rewrites them into base table operations, the planner can optimize them just as well as if you’d written the query directly against the tables.

Transparent security: Row-level security policies don’t require application changes. Once you define policies, PostgreSQL enforces them automatically for all queries, including those coming from existing application code.

Query debugging: When you use EXPLAIN to understand query performance, you’re seeing the rewritten query, not your original. This explains why the execution plan might reference tables and conditions you didn’t explicitly mention.

These practical implications show how query rewriting affects your day-to-day work with PostgreSQL, from designing schemas to debugging performance issues. Let’s recap what we’ve learned about this critical transformation phase.

Wrapping Up

We’ve traced the journey through PostgreSQL’s query rewriting phase—the transformation step between semantic analysis and planning. The rewriter applies two main types of transformations: rule-based query expansion and automatic security enforcement.

The key insights:

Rules stored in pg_rewrite can be INSTEAD (replace operations) or ALSO (add operations), and fire conditionally or unconditionally. Views are just INSTEAD rules that PostgreSQL creates automatically—this is why views don’t slow down queries, since the planner optimizes the expanded base table operations.

Custom rules let you implement business logic transparently, like automatically auditing admin user changes. While triggers are more common today, rules offer unique capabilities by transforming queries before execution.

Row-level security integrates directly into rewriting by adding WHERE conditions that enforce access policies. The rewriter automatically injects security predicates that cannot be bypassed, providing transparent, application-independent security.

By the time rewriting completes, your query has been transformed—views expanded, security applied, custom rules executed. The rewriter ensures database abstractions work transparently while giving the planner the complete picture of what needs to be executed.

With the rewritten query tree ready, PostgreSQL faces its next challenge: how to execute it most efficiently. Should it use an index or scan the table? Which join algorithm? What order for multiple joins? These optimization decisions belong to the planner, which we’ll explore in the next article.