Clustered Index Physical Sequence

-- In PostgreSQL: tables are stored as HEAP (no inherent order)
-- Clustered index concept via CLUSTER command

-- Make table data physically ordered by an index
CLUSTER users USING idx_users_created_at;
-- Rows are now physically ordered by created_at

-- SQL Server / MySQL: Primary key IS the clustered index
CREATE TABLE orders (
  id     INT         NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- clustered in MySQL
  ...
);

-- InnoDB (MySQL): table organized by primary key (clustered)
-- Every secondary index stores PK values, not row pointers

-- PostgreSQL equivalent: using PRIMARY KEY creates a B-tree index
-- Use CLUSTER to physically reorder data

-- Checking cluster info (PostgreSQL)
SELECT tablename, indexname FROM pg_stat_user_tables
JOIN pg_indexes USING (tablename) WHERE indexname IS NOT NULL;

-- Time-series table: cluster by timestamp for range scan efficiency
CREATE TABLE sensor_readings (
  id          BIGSERIAL   PRIMARY KEY,
  sensor_id   INT         NOT NULL,
  value       DECIMAL(10,4),
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Create index on the most common range query column
CREATE INDEX idx_readings_time ON sensor_readings(recorded_at DESC);

-- Cluster the table by time (physical ordering)
CLUSTER sensor_readings USING idx_readings_time;

-- After CLUSTER: range queries read sequential pages = fast!
EXPLAIN ANALYZE
SELECT * FROM sensor_readings
WHERE recorded_at BETWEEN NOW() - INTERVAL '1 hour' AND NOW()
ORDER BY recorded_at DESC;
-- Heap Blocks: hit=100, read=2 → mostly in memory due to sequential layout

-- Regular re-clustering (CLUSTER loses effect as new rows are inserted)
-- Schedule: CLUSTER sensor_readings during low-traffic hours