Views, CTEs, JSON and other powers

The whole chapter in one table

Everything this chapter covers, with what each capability is for and the support you can count on in each engine. The four that come up most often get a short section below; for the rest, this row plus the documentation at the end is enough.

Views and materialized views

A view is a SELECT statement stored under a name: query it and the engine runs the saved query, so it behaves like a table you never fill. It hides a join from reporting users, it can leave out columns so you grant SELECT on the view instead of the table (see Users, roles and security), and when a table is renamed or split you rewrite the view once and nothing that reads it notices.

A view built on an aggregate, DISTINCT, GROUP BY, UNION or a window function is read only in both engines. PostgreSQL also has materialized views, which store the result on disk and stay stale until you REFRESH them; MySQL has none, so you emulate one with a summary table that a stored procedure fills on a timer from the Event Scheduler (chapter 12), which is the answer to give a developer who asks for one.

CREATE VIEW customer_orders AS
SELECT c.name, o.id, o.order_date, o.status, o.total
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id;

-- PostgreSQL only: the result kept on disk and refreshed on demand
CREATE MATERIALIZED VIEW sales_by_city AS
SELECT c.city, SUM(o.total) AS revenue
FROM customers AS c JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.city;
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_by_city;   -- needs a unique index

CTEs and recursive CTEs

A common table expression, written with WITH, names a query result for the duration of one statement. Nothing is stored, and the gain is readability: nested subqueries become named steps you read top to bottom. A recursive CTE refers to itself, so an anchor query picks the roots and a second query joins the previous round to reach the next level, which is how you walk a category tree, an org chart or a bill of materials. Both engines have both forms, MySQL since 8.0, where a runaway walk stops at cte_max_recursion_depth, while PostgreSQL adds SEARCH and CYCLE clauses to order the walk and catch loops.

-- categories(id, name, parent_id): Electronics > Peripherals > Keyboards
WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 1 AS depth
  FROM categories WHERE parent_id IS NULL                    -- anchor: the roots
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1
  FROM categories AS c JOIN tree AS t ON c.parent_id = t.id   -- one level down
)
SELECT id, name, depth FROM tree ORDER BY depth, id;

JSON inside a relational database

Both engines let a column hold a JSON document and reach inside it with SQL, which suits sparse attributes that differ by product type and payloads you keep exactly as they arrived. -> extracts a value at a path or a key and ->> also strips the quotes; MySQL indexes a document through a generated column or a multi-valued index over an array, while PostgreSQL's jsonb takes one GIN index that answers containment and key-existence tests without naming keys in advance; JSON_TABLE turns an array into rows you can join and group, and PostgreSQL 17 added it under that same standard name.

Keep out of JSON anything a report filters on, joins on or must enforce, because a customer_id inside a document gets no foreign key and a price gets no DECIMAL type. The normalization rules from Designing good tables still apply to everything else.

-- MySQL: a path, plus the multi-valued index that makes the array search fast
SELECT name, attrs->>'$.color' AS color FROM products
WHERE 'usb' MEMBER OF(attrs->'$.tags');
ALTER TABLE products ADD INDEX idx_tags ((CAST(attrs->'$.tags' AS CHAR(30) ARRAY)));

-- PostgreSQL: a key name, and one GIN index for the whole jsonb column
CREATE INDEX idx_products_attrs ON products USING gin (attrs);
SELECT name, attrs->>'color' AS color FROM products
WHERE attrs @> '{"tags": ["usb"]}';

Partitioning: one big table in pieces

Partitioning splits one logical table into physical pieces by a rule on one column, usually RANGE on a date, sometimes LIST on a country or HASH on an id, while applications keep using the one table name; the engine then reads only the partitions your WHERE clause can match. DBAs want it for maintenance: deleting last year's fifty million rows takes hours and lags every replica (see Backup, restore and replication), while dropping the partition that holds them finishes in a moment.

In MySQL every unique key, including the primary key, must contain the partition column, and a partitioned InnoDB table cannot have foreign keys. PostgreSQL declares a parent with PARTITION BY and each piece as its own table, keeps foreign keys, and can ATTACH and DETACH PARTITION CONCURRENTLY without blocking readers.

-- MySQL: the partition column has to sit in every unique key
CREATE TABLE orders_archive (
  id INT NOT NULL, order_date DATE NOT NULL, total DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p2026 VALUES LESS THAN (2027),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);
ALTER TABLE orders_archive DROP PARTITION p2025;   -- no DELETE, no undo log

-- PostgreSQL: a parent table, then one real table per piece
CREATE TABLE orders_log (
  id INT NOT NULL, order_date DATE NOT NULL, total NUMERIC(10,2) NOT NULL,
  PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (order_date);
CREATE TABLE orders_2026 PARTITION OF orders_log
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

Go deeper

• MySQL: The JSON Data Type

  Storage, path syntax and the index workarounds.

• PostgreSQL: JSON Types

  json versus jsonb, containment, GIN indexing and jsonpath.

• MySQL: Restrictions and Limitations on Partitioning

  Read it before you promise anyone a partitioned table.

• PostgreSQL: Table Partitioning

  Declarative partitioning, pruning, attach and detach.

• MySQL: LOAD DATA Statement

  Every clause, plus the secure_file_priv and LOCAL rules.

• pgvector

  Types, operators, HNSW and IVFFlat indexes, install notes.