Views

A view is a virtual table defined by a SQL SELECT statement. Views do not store data themselves; instead, their defining query is executed as a sub-query whenever the view is referenced.

What are views for?

Views provide several benefits:

• Abstraction: Hide complex queries behind simple table-like interfaces
• Reusability: Define queries once, use them everywhere
• Security: Control data access without exposing underlying tables
• Maintainability: Single source of truth for business logic

-- Create a view
CREATE VIEW hourly_summary AS (
  SELECT ts, symbol, sum(quantity) as volume
  FROM trades
  SAMPLE BY 1h
);

-- Query the view like a table
SELECT * FROM hourly_summary WHERE symbol = 'AAPL';

Creating views

Use CREATE VIEW to define a new view:

CREATE VIEW daily_prices AS (
  SELECT ts, symbol, last(price) as closing_price
  FROM trades
  SAMPLE BY 1d
)

CREATE IF NOT EXISTS

To avoid errors when the view already exists:

CREATE VIEW IF NOT EXISTS price_view AS (
  SELECT symbol, last(price) as price FROM trades SAMPLE BY 1h
)

CREATE OR REPLACE

To update an existing view or create it if it doesn't exist:

CREATE OR REPLACE VIEW price_view AS (
  SELECT symbol, last(price) as price, ts FROM trades SAMPLE BY 1h
)

For full syntax details, see CREATE VIEW.

Querying views

Views are queried exactly like tables:

SELECT * FROM my_view

SELECT ts, price FROM my_view WHERE symbol = 'AAPL'

SELECT v1.ts, v2.value
FROM view1 v1
JOIN view2 v2 ON v1.id = v2.id

Optimizer transparency

Views in QuestDB are fully transparent to the query optimizer. When you query a view, the optimizer treats it exactly as if you had written the view's query inline as a sub-query. This means views benefit from the complete suite of query optimizations:

• Filter push-down: WHERE conditions are pushed to base tables
• Projection push-down: Only required columns are read from storage
• Join optimization: Join order and strategies are optimized across view boundaries
• ORDER BY optimization: Sorting can leverage table indexes
• Timestamp optimizations: Time-based operations use partition pruning

-- View definition
CREATE VIEW trades_view AS (
  SELECT ts, symbol, price, quantity FROM trades WHERE price > 0
)

-- This query is optimized as if written inline
SELECT ts, price FROM trades_view WHERE symbol = 'AAPL' ORDER BY ts
-- Optimizer sees: SELECT ts, price FROM trades WHERE price > 0 AND symbol = 'AAPL' ORDER BY ts
-- Only ts and price columns are read, filters applied at scan, ordering uses index

Use EXPLAIN to see how the optimizer processes view queries:

EXPLAIN SELECT * FROM trades_view WHERE symbol = 'AAPL'

There is no performance penalty for using views compared to writing equivalent sub-queries directly.

Parameterized views

Views support the DECLARE statement to define parameters with default values. Use OVERRIDABLE to allow users to change parameter values at query time.

Creating a parameterized view

CREATE VIEW filtered_trades AS (
  DECLARE OVERRIDABLE @min_price := 100
  SELECT ts, symbol, price FROM trades WHERE price >= @min_price
)

Querying with default parameters

SELECT * FROM filtered_trades
-- Uses default @min_price = 100

Overriding parameters

DECLARE @min_price := 500 SELECT * FROM filtered_trades
-- Overrides @min_price to 500

Multiple parameters

By default, parameters are non-overridable. Use OVERRIDABLE to allow override at query time:

CREATE VIEW price_range AS (
  DECLARE OVERRIDABLE @lo := 100, OVERRIDABLE @hi := 1000
  SELECT ts, symbol, price FROM trades WHERE price >= @lo AND price <= @hi
)

-- Query with custom range
DECLARE @lo := 50, @hi := 200 SELECT * FROM price_range

Non-overridable parameters

Parameters without OVERRIDABLE cannot be changed at query time, providing security for sensitive filters:

CREATE VIEW secure_view AS (
  DECLARE @min_value := 0
  SELECT * FROM trades WHERE value >= @min_value
)

-- This will fail with "variable is not overridable: @min_value"
DECLARE @min_value := -100 SELECT * FROM secure_view

Mixed parameters

Combine overridable and non-overridable parameters:

CREATE VIEW mixed_params AS (
  DECLARE @fixed_filter := 'active', OVERRIDABLE @limit := 100
  SELECT * FROM data WHERE status = @fixed_filter LIMIT @limit
)

-- @limit can be overridden, @fixed_filter cannot
DECLARE @limit := 50 SELECT * FROM mixed_params

View hierarchies

Views can reference other views, tables, and materialized views:

-- Level 1: Raw data filtering
CREATE VIEW valid_trades AS (
  SELECT * FROM trades WHERE price > 0 AND quantity > 0
)

-- Level 2: Aggregation
CREATE VIEW hourly_stats AS (
  SELECT ts, symbol, sum(quantity) as volume
  FROM valid_trades
  SAMPLE BY 1h
)

-- Level 3: Derived metrics
CREATE VIEW hourly_vwap AS (
  SELECT ts, symbol, volume, turnover / volume as vwap
  FROM hourly_stats
  WHERE volume > 0
)

:::tip

Keep view hierarchies shallow (3-4 levels maximum) for better query planning and maintainability.

:::

View management

Listing views

SELECT * FROM views()

Returns:

Show view definition

SHOW CREATE VIEW my_view

Returns the CREATE VIEW statement that would recreate the view.

Show view columns

SHOW COLUMNS FROM my_view

Altering views

To modify an existing view's definition:

ALTER VIEW my_view AS (SELECT col1, col2 FROM my_table WHERE col1 > 0)

For full syntax, see ALTER VIEW.

Dropping views

DROP VIEW my_view

-- Or safely:
DROP VIEW IF EXISTS my_view

For full syntax, see DROP VIEW.

View invalidation

Views are automatically invalidated when their dependencies change:

Automatic recovery

Views are automatically revalidated when the invalidating condition is reversed:

• If a table is dropped and later recreated, dependent views become valid again
• If a column is renamed back to its original name, dependent views become valid again

Checking view status

SELECT view_name, view_status, invalidation_reason
FROM views()
WHERE view_status = 'invalid'

Views in tables() output

Views appear in the tables() function with table_type = 'V':

SELECT table_name, table_type FROM tables()

Views vs materialized views

Understanding when to use each type is important for performance:

When to use views

• Simple transformations that execute quickly
• Data that must always be current
• Ad-hoc analysis where requirements change frequently
• Parameterized queries with DECLARE
• Low-frequency queries

When to use materialized views

• Heavy aggregations over large datasets
• Frequently accessed summary data
• Dashboard queries that run repeatedly
• Historical summaries that don't need real-time accuracy

For detailed comparisons and examples, see Materialized Views.

Security with views

Views provide a security boundary between users and underlying data.

Definer security model (Enterprise)

Views use a definer security model. When a view is created, the creator's permissions are captured. Users querying the view only need SELECT permission on the view itself - they do not need permissions on the underlying tables.

-- Admin creates a view on sensitive table
CREATE VIEW public_summary AS (
  SELECT date, region, sum(sales) as total FROM sensitive_sales GROUP BY date, region
);

-- Grant SELECT on the view to analysts
GRANT SELECT ON public_summary TO analyst_role;

-- Analysts can query the view without access to sensitive_sales
SELECT * FROM public_summary;  -- Works!
SELECT * FROM sensitive_sales; -- Access denied!

The view's definer permissions are preserved even if the creator's account is later disabled or deleted.

No column-level permissions on views

Unlike tables, views do not support column-level permissions. You can only grant or revoke permissions on the entire view:

-- This works: grant SELECT on entire view
GRANT SELECT ON my_view TO user1;

-- Column-level permissions are NOT supported for views
-- Use separate views to expose different column subsets

To provide different column access to different users, create multiple views with different column selections.

Security patterns

Views enable several security patterns:

• Data subsetting: Expose only specific rows or columns
• Column masking: Hide sensitive columns from certain users
• Row-level security: Filter rows based on business rules
• Aggregation-only access: Provide summaries without raw data access

Column-level security example

-- Base table with sensitive data
CREATE TABLE employees (
  id LONG,
  name VARCHAR,
  salary DOUBLE,        -- Sensitive
  department VARCHAR,
  hire_date TIMESTAMP
);

-- View exposing only non-sensitive columns
CREATE VIEW employees_public AS (
  SELECT id, name, department, hire_date
  FROM employees
);

-- Grant access to public view only
GRANT SELECT ON employees_public TO analyst_role;

Row-level security example

-- View for specific trading desk
CREATE VIEW desk_a_trades AS (
  SELECT * FROM trades WHERE trader_id IN (101, 102, 103)
);

GRANT SELECT ON desk_a_trades TO desk_a_users;

For more details on permissions, see Role-Based Access Control (RBAC).

Performance considerations

Views don't cache results

Every query against a view executes the underlying query. For expensive aggregations accessed frequently, consider materialized views.

Optimize with indexes

Create indexes on base table columns used in view filters:

ALTER TABLE trades ALTER COLUMN symbol ADD INDEX

Check query plans

Always examine query plans when optimizing:

EXPLAIN SELECT * FROM my_view WHERE symbol = 'AAPL'

Best practices

• Use indexed columns in filters for best performance
• Use parameterized views for common filter patterns
• Avoid deeply nested view hierarchies (>3-4 levels) for maintainability
• Consider materialized views for expensive aggregations that run frequently

Limitations

1. No data storage: Views don't store data - the query runs each time
2. No indexes: Views cannot have indexes; filtering relies on underlying table indexes
3. Circular references: Views cannot reference themselves or create circular dependencies
4. Read-only: You cannot INSERT, UPDATE, or DELETE on views
5. No DDL operations: You cannot run DDL operations (like RENAME TABLE) on views
6. No column-level permissions: Unlike tables, you cannot grant permissions on individual view columns (Enterprise)

Related documentation

• SQL Commands

  • CREATE VIEW: Create a new view
  • ALTER VIEW: Modify a view definition
  • DROP VIEW: Remove a view

• Related Concepts

  • Materialized Views: Pre-computed query results
  • DECLARE: Parameter declaration for views