postgresql

-- Create roles
CREATE ROLE readonly;
CREATE ROLE readwrite;
CREATE ROLE admin WITH LOGIN PASSWORD 'secure_password';

-- Grant permissions to roles

local   all             postgres                                peer
hostssl app_production  app_user        10.0.0.0/16             scram-sha-256
hostssl app_production  reporting_user  10.0.1.0/24             scram-sha-256
host    all             all             0.0.0.0/0               reject

-- Create hierarchical table (org chart)
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  title VARCHAR(100),
  manager_id INT REFERENCES employees(id),

-- Basic transaction
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Create test database
CREATE DATABASE myapp_test;

-- Test isolation with transactions
/*
beforeEach(async () => {

-- PostgreSQL Declarative Partitioning (10+)

-- Create partitioned table by date range
CREATE TABLE measurements (
  id BIGSERIAL,
  sensor_id INT NOT NULL,

-- Primary key (unique, not null identifier)
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL
);

-- SCHEMA DESIGN CHECKLIST

-- 1. Use appropriate data types
CREATE TABLE users_optimized (
  id SERIAL PRIMARY KEY,               -- Auto-increment
  uuid UUID DEFAULT gen_random_uuid(), -- UUID for external IDs

Apartment.configure do |config|
  config.excluded_models = %w[Tenant User]
  config.tenant_names = -> { Tenant.pluck(:schema_name) }
  config.use_schemas = true
end

-- Enable pg_stat_statements extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all

class AddIndexesToPosts < ActiveRecord::Migration[6.1]
  def change
    add_index :posts, :author_id
    add_index :posts, :published_at
    add_index :posts, [:author_id, :published_at]
    add_index :posts, :created_at, order: { created_at: :desc }

-- Basic CTE
WITH high_value_customers AS (
  SELECT
    user_id,
    SUM(total) as lifetime_value
  FROM orders