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

-- Create regular table
CREATE TABLE sensor_data (
  time TIMESTAMPTZ 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
);

-- 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

-- Create materialized view
CREATE MATERIALIZED VIEW user_statistics AS
SELECT
  users.id,
  users.username,
  COUNT(DISTINCT orders.id) AS order_count,

-- 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 (

; PgBouncer configuration file

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

-- 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

-- Logical backup with pg_dump
-- Single database
-- pg_dump -h localhost -U postgres -d mydb -F c -f mydb_backup.dump

-- All databases
-- pg_dumpall -h localhost -U postgres -f all_databases.sql

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

-- Grant permissions to roles

-- Create table with tsvector column
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200),
  content TEXT,
  author VARCHAR(100),

-- PostgreSQL Declarative Partitioning (10+)

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

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