Database & Data

-- PostgreSQL
CREATE TABLE orders (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    public_id  UUID NOT NULL DEFAULT gen_random_uuid(),
    -- ...
    CONSTRAINT uq_orders_public_id UNIQUE (public_id)
);

-- SQL Server
CREATE TABLE orders (
    id        BIGINT IDENTITY(1,1) PRIMARY KEY,
    public_id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    -- ...
    CONSTRAINT uq_orders_public_id UNIQUE (public_id)
);

-- Example of random identifiers
CREATE TABLE customers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid()
    -- Measure insertion patterns, page splits and index size
);

-- Alternative: sequential internal key and public UUID
CREATE TABLE customers (
    id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    public_id UUID NOT NULL DEFAULT gen_random_uuid(),
    name      VARCHAR(200) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_customers_public_id UNIQUE (public_id)
);

-- Example schema
CREATE TABLE products (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    public_id    UUID NOT NULL DEFAULT gen_random_uuid(),
    name         VARCHAR(300) NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at   TIMESTAMPTZ NULL, -- NULL = active, NOT NULL = deleted
    CONSTRAINT uq_products_public_id UNIQUE (public_id)
);

-- Partial index for active records (PostgreSQL)
-- Excludes deleted records from this index
CREATE INDEX ix_products_name_active
    ON products (name)
    WHERE deleted_at IS NULL;

-- Standard query: always include the filter
SELECT * FROM products WHERE deleted_at IS NULL AND name ILIKE '%notebook%';

-- A simple index may require lookups for additional columns
CREATE INDEX ix_orders_customer_id ON orders (customer_id);

-- Covering index: includes the columns this query needs
CREATE INDEX ix_orders_customer_id_cover
    ON orders (customer_id)
    INCLUDE (status, total_amount, created_at);

-- Check whether the execution plan uses the covering index:
SELECT status, total_amount, created_at
FROM orders
WHERE customer_id = 42 AND deleted_at IS NULL;

-- Composite index: column order matters
-- Choose column order from predicates and the execution plan
CREATE INDEX ix_orders_status_created
    ON orders (status, created_at DESC)
    WHERE deleted_at IS NULL;

-- Manual maintenance examples: evaluate before running
-- Schedule according to workload and maintenance needs
VACUUM (VERBOSE, ANALYZE) orders;

-- Inspect dead tuples by table
SELECT
    schemaname,
    relname AS table_name,
    n_dead_tup AS dead_tuples,
    n_live_tup AS live_tuples,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_pct DESC;

-- Inspect index usage before considering removal
SELECT
    indexrelname AS index_name,
    relname AS table_name,
    idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%pkey%'
ORDER BY relname;

-- Inspect fragmentation by index
SELECT
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name AS index_name,
    ips.avg_fragmentation_in_percent AS fragmentation_pct,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 5
  AND ips.page_count > 100
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- Reorganize: evaluate workload, page density and benefits first
ALTER INDEX ix_orders_customer_id ON orders REORGANIZE;

-- Rebuild: evaluate costs and locking for this edition
ALTER INDEX ix_orders_customer_id ON orders REBUILD WITH (ONLINE = ON);

-- Update statistics for a table
UPDATE STATISTICS orders WITH FULLSCAN;

-- Inspect expensive queries (top 10 by CPU)
SELECT TOP 10
    qs.total_elapsed_time / qs.execution_count / 1000 AS avg_ms,
    qs.execution_count,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_ms DESC;