Maria Garcia

-- Basic query debugging with EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- Output shows query plan:
-- Seq Scan on users (cost=0.00..25.00 rows=1 width=100)
--   Filter: (email = 'test@example.com'::text)

-- SCHEMA DESIGN CHECKLIST

-- 1. Use appropriate data types
CREATE TABLE users_optimized (
  id SERIAL PRIMARY KEY,               -- Auto-increment
  uuid UUID DEFAULT gen_random_uuid(), -- UUID for external IDs

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