postgresql

-- Create basic index
CREATE INDEX idx_users_email ON users(email);

-- Unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Create table with JSONB column
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255),
  metadata JSONB DEFAULT '{}'::jsonb

-- ROLLUP for hierarchical subtotals
SELECT
  COALESCE(category, 'ALL CATEGORIES') AS category,
  COALESCE(subcategory, 'ALL SUBCATEGORIES') AS subcategory,
  SUM(revenue) AS total_revenue,
  COUNT(*) AS order_count

; PgBouncer configuration file

[databases]
; Database connection strings
mydb = host=localhost port=5432 dbname=mydb
analytics = host=replica.example.com port=5432 dbname=mydb

-- Install TimescaleDB extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Create regular table
CREATE TABLE sensor_data (
  time TIMESTAMPTZ NOT NULL,

-- Primary server configuration (postgresql.conf)
-- wal_level = replica
-- max_wal_senders = 10
-- wal_keep_size = 64MB
-- hot_standby = on

-- Create table with JSONB column
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50),
  profile JSONB,
  preferences JSONB,

-- Migration naming convention: V{version}__{description}.sql
-- Example: V001__create_users_table.sql

-- Migration 1: Create initial schema
-- V001__create_users_table.sql
CREATE TABLE users (

class AddMetadataToUsers < ActiveRecord::Migration[6.1]
  def change
    add_column :users, :metadata, :jsonb, default: {}, null: false
    add_index :users, :metadata, using: :gin
  end
end