Sequences, Views & Materialized Views

10 min read

Sequences, Views & Materialized Views

Sequences

A sequence is a database object that generates unique numeric values. SERIAL and GENERATED ALWAYS AS IDENTITY both use sequences under the hood. You can also create and use sequences manually.

sql

-- Create a sequence
CREATE SEQUENCE invoice_number_seq
  START WITH 1000
  INCREMENT BY 1
  NO CYCLE;

-- Get next value
SELECT nextval('invoice_number_seq');

-- Peek at current value (does not advance the sequence)
SELECT currval('invoice_number_seq');

-- Use in a table
CREATE TABLE invoices (
  number  bigint DEFAULT nextval('invoice_number_seq') PRIMARY KEY,
  amount  numeric(12,2) NOT NULL
);

-- Inspect sequences in the database
SELECT * FROM information_schema.sequences;

Sequences are not transactional — if a transaction is rolled back, the sequence value is still consumed. This is intentional to avoid contention; expect gaps in your IDs.

Views

A view is a saved SQL query that behaves like a table. It is computed fresh on every access. Views are great for encapsulating complex joins and presenting a simplified interface to application code.

sql

CREATE VIEW published_posts AS
  SELECT
    p.id,
    p.title,
    p.slug,
    p.published_at,
    u.full_name AS author_name
  FROM posts p
  JOIN users u ON u.id = p.author_id
  WHERE p.published = true;

-- Query the view like a table
SELECT * FROM published_posts ORDER BY published_at DESC LIMIT 10;

Materialized Views

A materialized view caches the query result on disk. Unlike a regular view it does not re-execute on every query — you must explicitly refresh it. This makes materialized views ideal for expensive aggregations that do not need to be real-time.

sql

CREATE MATERIALIZED VIEW monthly_revenue AS
  SELECT
    date_trunc('month', created_at) AS month,
    SUM(amount)                      AS total
  FROM orders
  WHERE status = 'paid'
  GROUP BY 1
  ORDER BY 1;

-- Refresh (blocks reads by default)
REFRESH MATERIALIZED VIEW monthly_revenue;

-- Refresh concurrently (does not block, requires a unique index)
CREATE UNIQUE INDEX ON monthly_revenue (month);
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

Ready to test yourself?

Practice PostgreSQL— quiz & coding exercises