Maria Garcia

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

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

-- EXPLAIN ANALYZE (actual execution statistics)
EXPLAIN ANALYZE
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'

-- Basic VACUUM (reclaims dead tuple space)
VACUUM users;

-- VACUUM all tables in database
VACUUM;

-- Import CSV with COPY (fastest method)
COPY users (username, email, age, created_at)
FROM '/path/to/users.csv'
WITH (
  FORMAT csv,
  HEADER true,

-- Install PostGIS extension
CREATE EXTENSION IF NOT EXISTS postgis;

-- Create table with geometry column
CREATE TABLE locations (
  id SERIAL PRIMARY KEY,

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