Database.Middle.ClusterIndex.Examples

🐘 PostgreSQL

📌 1. Create a Table

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    email TEXT,
    created_at TIMESTAMP
);

✅ Clustered Index in PostgreSQL

PostgreSQL does not have a true clustered index like SQL Server or MySQL’s InnoDB. But you can physically reorder the table using the CLUSTER command on an index:

-- Create a regular (non-clustered) index
CREATE INDEX idx_users_created_at ON users(created_at);

-- Cluster the table based on that index (reorders the data on disk)
CLUSTER users USING idx_users_created_at;

-- Optional: mark table as clustered (for documentation)
-- Note: PostgreSQL doesn't automatically keep it clustered after new inserts

⚠️ In PostgreSQL, clustering is a one-time operation and not maintained automatically after inserts.

✅ Non-Clustered Index in PostgreSQL

PostgreSQL’s default indexes (e.g. CREATE INDEX) are non-clustered:

-- Index for faster email lookups (non-clustered)
CREATE INDEX idx_users_email ON users(email);

🐬 MySQL (InnoDB Engine)

📌 1. Create a Table

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    created_at DATETIME
) ENGINE=InnoDB;

✅ Clustered Index in MySQL

In InnoDB, the primary key is always the clustered index:

-- id is PRIMARY KEY, so it's also the CLUSTERED index
-- Data is physically ordered by 'id'

You can also create a different clustered index by defining a primary key or a unique key first (InnoDB clusters by the first unique NOT NULL index):

-- This would make 'email' the clustered index (if no PK is defined first)
CREATE TABLE users (
    email VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(100),
    created_at DATETIME
) ENGINE=InnoDB;

⚠️ Only one clustered index per table in MySQL (InnoDB).

✅ Non-Clustered Index in MySQL

Any other secondary index is non-clustered:

-- Secondary index (non-clustered)
CREATE INDEX idx_users_created_at ON users(created_at);

🧠 Summary

Feature	PostgreSQL	MySQL (InnoDB)
Clustered Index	Emulated via CLUSTER	Always on PRIMARY KEY
Maintains clustering?	❌ Not automatically	✅ Always maintained
Non-Clustered Index	Default for all indexes	Any secondary (non-PK) index