-- Prepared statements basics
-- PostgreSQL syntax
PREPARE get_user (INT) AS
SELECT id, username, email
FROM users
WHERE id = $1;

-- Pattern: Single Table Inheritance (STI)
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  type VARCHAR(50) NOT NULL, -- 'admin', 'customer', 'vendor'
  username VARCHAR(100) UNIQUE NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,

-- Row-level locks with SELECT FOR UPDATE
BEGIN;

SELECT * FROM inventory
WHERE product_id = 123
FOR UPDATE;  -- Locks selected rows

-- Create test database
CREATE DATABASE myapp_test;

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

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