-- Install postgres_fdw extension
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- Create foreign server
CREATE SERVER remote_db
  FOREIGN DATA WRAPPER postgres_fdw

-- Publisher: Send notification
NOTIFY new_order, 'Order #12345 created';

-- Subscriber: Listen for notifications
LISTEN new_order;

-- Partition-based archival strategy

-- Create partitioned table
CREATE TABLE logs (
  id BIGSERIAL,
  level VARCHAR(20),

-- Connection statistics
SELECT
  count(*) AS total_connections,
  count(*) FILTER (WHERE state = 'active') AS active,
  count(*) FILTER (WHERE state = 'idle') AS idle,
  count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_tx,

-- Pattern 1: Shared schema with tenant_id column

CREATE TABLE tenants (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(50) UNIQUE NOT NULL,

-- Basic LATERAL join
SELECT
  u.username,
  recent.order_id,
  recent.total,
  recent.created_at

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