postgresql

name	postgresql
description	PostgreSQL database design, queries, optimization, and best practices
category	databases
triggers	["postgresql","postgres","sql","database","query","schema"]

PostgreSQL

Enterprise-grade PostgreSQL database design and optimization following industry best practices. This skill covers schema design, query optimization, indexing strategies, and production-ready patterns.

Purpose

Build performant, scalable database systems:

• Design efficient schemas
• Write optimized queries
• Implement proper indexing
• Handle transactions correctly
• Optimize for performance
• Ensure data integrity

Features

1. Schema Design

-- Users table with proper constraints
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(100) NOT NULL,
  role VARCHAR(20) DEFAULT 'user' CHECK (role IN ('user', 'admin', 'moderator')),
  is_active BOOLEAN DEFAULT TRUE,
  email_verified_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Products table with foreign key
CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
  stock_quantity INTEGER DEFAULT 0 CHECK (stock_quantity >= 0),
  category_id UUID REFERENCES categories(id) ON DELETE SET NULL,
  seller_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  is_published BOOLEAN DEFAULT FALSE,
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Orders table with status tracking
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),
  status VARCHAR(20) DEFAULT 'pending' CHECK (status IN (
    'pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled'
  )),
  total_amount DECIMAL(12, 2) NOT NULL,
  shipping_address JSONB NOT NULL,
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Order items junction table
CREATE TABLE order_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id UUID NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price DECIMAL(10, 2) NOT NULL,
  UNIQUE(order_id, product_id)
);

-- Updated at trigger function
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply trigger to tables
CREATE TRIGGER users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER products_updated_at
  BEFORE UPDATE ON products
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER orders_updated_at
  BEFORE UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

2. Indexing Strategies

-- Primary indexes (created automatically)
-- B-tree indexes for equality and range queries
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_seller ON products(seller_id);
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

-- Composite indexes for common query patterns
CREATE INDEX idx_products_category_price ON products(category_id, price);
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
CREATE INDEX idx_orders_created_status ON orders(created_at DESC, status);

-- Partial indexes for filtered queries
CREATE INDEX idx_products_published ON products(category_id, created_at)
  WHERE is_published = TRUE;

CREATE INDEX idx_orders_pending ON orders(user_id, created_at)
  WHERE status = 'pending';

-- Full-text search indexes
CREATE INDEX idx_products_search ON products
  USING GIN(to_tsvector('english', name || ' ' || COALESCE(description, '')));

-- JSONB indexes
CREATE INDEX idx_orders_shipping_city ON orders
  USING GIN((shipping_address-));


 INDEX idx_users_email_lower  users((email));


 INDEX idx_products_list  products(category_id, is_published)
  INCLUDE (name, price);

3. Query Patterns

-- Pagination with cursor (more efficient than OFFSET)
SELECT id, name, price, created_at
FROM products
WHERE created_at < '2024-01-01' -- cursor value
  AND is_published = TRUE
ORDER BY created_at DESC
LIMIT 20;

-- Efficient offset pagination when needed
SELECT id, name, price
FROM products
WHERE is_published = TRUE
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

-- Search with full-text search
SELECT id, name, description,
  ts_rank(to_tsvector('english', name || ' ' || COALESCE(description, '')),
          plainto_tsquery('english', 'laptop gaming')) as rank
FROM products
WHERE to_tsvector('english', name || ' ' || COALESCE(description, ''))
      @@ plainto_tsquery('english', 'laptop gaming')
ORDER BY rank DESC
LIMIT 20;



  DATE_TRUNC(, created_at)  ,
  ()  order_count,
  (total_amount)  revenue,
  (total_amount)  avg_order_value
 orders
 status  
   created_at  NOW()   
  DATE_TRUNC(, created_at)
   ;



  id, name, price, category_id,
  ()  (  category_id   price )  price_rank,
  (price)  (  category_id   price)  prev_price,
  (price)  (  category_id)  category_avg
 products
 is_published  ;


 monthly_sales  (
  
    DATE_TRUNC(, o.created_at)  ,
    (oi.quantity  oi.unit_price)  revenue
   orders o
   order_items oi  o.id  oi.order_id
   o.status  
    DATE_TRUNC(, o.created_at)
),
sales_with_growth  (
  
    ,
    revenue,
    (revenue)  (  )  prev_revenue,
    revenue  (revenue)  (  )  growth
   monthly_sales
)
   sales_with_growth
   ;


  category_tree  (
  
   id, name, parent_id,   depth, [id]  path
   categories
   parent_id  

   

  
   c.id, c.name, c.parent_id, ct.depth  , ct.path  c.id
   categories c
   category_tree ct  c.parent_id  ct.id
)
   category_tree
  path;

4. Transactions and Locking

-- Basic transaction
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 'sender_id';
UPDATE accounts SET balance = balance + 100 WHERE id = 'receiver_id';

INSERT INTO transactions (from_id, to_id, amount)
VALUES ('sender_id', 'receiver_id', 100);

COMMIT;

-- Transaction with savepoint
BEGIN;

UPDATE products SET stock_quantity = stock_quantity - 1 WHERE id = 'product_id';
SAVEPOINT after_stock_update;

INSERT INTO orders (user_id, total_amount)
VALUES ('user_id', 99.99)
RETURNING id INTO order_id;

-- If something goes wrong
ROLLBACK TO SAVEPOINT after_stock_update;

COMMIT;

-- Pessimistic locking (FOR UPDATE)
BEGIN;

SELECT * FROM products WHERE id =   ;


 products  stock_quantity  stock_quantity    id  ;

;


   jobs
 status  
  created_at
LIMIT 
   LOCKED;


 pg_advisory_lock(hashtext());

 pg_advisory_unlock(hashtext());

5. Performance Optimization

-- Analyze query performance
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT p.*, c.name as category_name
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.is_published = TRUE
  AND p.price BETWEEN 10 AND 100
ORDER BY p.created_at DESC
LIMIT 20;

-- Table statistics
ANALYZE products;

-- Vacuum to reclaim space
VACUUM (VERBOSE, ANALYZE) products;

-- Check index usage
SELECT
  schemaname, tablename, indexname,
  idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'products'
ORDER BY idx_scan DESC;

-- Find unused indexes
SELECT
  schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public';

-- Table bloat check
SELECT
  schemaname, tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as total_size,
  n_dead_tup as dead_tuples,
  n_live_tup  live_tuples
 pg_stat_user_tables
 n_dead_tup  
  n_dead_tup ;



  datname, usename, application_name,
  client_addr, state, query_start,
  NOW()  query_start  query_duration
 pg_stat_activity
 state  
  query_start;

6. JSONB Operations

-- JSONB column
CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  type VARCHAR(50) NOT NULL,
  payload JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert JSONB data
INSERT INTO events (type, payload)
VALUES ('user.created', '{"user_id": "123", "email": "[e-mail]", "metadata": {"source": "web"}}');

-- Query JSONB
SELECT * FROM events
WHERE payload->>'user_id' = '123';

SELECT * FROM events
WHERE payload @> '{"metadata": {"source": "web"}}';

-- JSONB operators
SELECT
  payload->'user_id' as user_id,  -- Get as JSONB
  payload->>'email' as email,     -- Get as text
  payload#>'{metadata,source}' as source,
  payload ? 'email' as has_email,
  payload ?& ARRAY['user_id', 'email']  has_all
 events;


 events
 payload  payload  
 id  ;

 events
 payload  jsonb_set(payload, , )
 id  ;


 events
 payload  payload  
 id  ;

7. Stored Procedures

-- Function to get user stats
CREATE OR REPLACE FUNCTION get_user_stats(p_user_id UUID)
RETURNS TABLE (
  total_orders BIGINT,
  total_spent DECIMAL,
  avg_order_value DECIMAL,
  last_order_date TIMESTAMPTZ
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    COUNT(*)::BIGINT,
    COALESCE(SUM(total_amount), 0),
    COALESCE(AVG(total_amount), 0),
    MAX(created_at)
  FROM orders
  WHERE user_id = p_user_id
    AND status = 'delivered';
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT * FROM get_user_stats('user-uuid-here');

-- Procedure for order creation
CREATE OR REPLACE PROCEDURE create_order(
  p_user_id UUID,
  p_items JSONB,
  OUT p_order_id UUID
)
LANGUAGE plpgsql AS $$
DECLARE
  v_total DECIMAL := 0;
  v_item JSONB;
BEGIN
  -- Calculate total
  FOR v_item     jsonb_array_elements(p_items)
  LOOP
    v_total : v_total  (v_item)::  (v_item)::;
   LOOP;

  
   orders (user_id, total_amount, status)
   (p_user_id, v_total, )
  RETURNING id  p_order_id;

  
   order_items (order_id, product_id, quantity, unit_price)
  
    p_order_id,
    ()::UUID,
    ()::,
    ()::
   jsonb_array_elements(p_items);

  
   products p
   stock_quantity  stock_quantity  (i.value)::
   jsonb_array_elements(p_items) i
   p.id  (i.value)::UUID;
;
$$;

Use Cases

E-commerce Analytics

SELECT
  p.category_id,
  c.name as category_name,
  COUNT(DISTINCT o.id) as order_count,
  SUM(oi.quantity) as units_sold,
  SUM(oi.quantity * oi.unit_price) as revenue
FROM products p
JOIN categories c ON p.category_id = c.id
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'delivered'
  AND o.created_at >= NOW() - INTERVAL '30 days'
GROUP BY p.category_id, c.name
ORDER BY revenue DESC;

User Activity Report

WITH user_activity AS (
  SELECT
    u.id,
    u.email,
    COUNT(o.id) as order_count,
    COALESCE(SUM(o.total_amount), 0) as total_spent,
    MAX(o.created_at) as last_order_date
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'delivered'
  GROUP BY u.id, u.email
)
SELECT
  *,
  CASE
    WHEN total_spent >= 1000 THEN 'VIP'
    WHEN total_spent >= 500 THEN 'Regular'
    ELSE 'New'
  END as customer_tier
FROM user_activity
ORDER BY total_spent DESC;

Best Practices

Do's

• Use UUIDs for primary keys
• Add indexes for common queries
• Use EXPLAIN ANALYZE
• Use transactions for data integrity
• Use connection pooling
• Regular VACUUM and ANALYZE

Don'ts

• Don't use SELECT *
• Don't ignore query plans
• Don't forget foreign keys
• Don't skip migrations
• Don't use raw SQL without parameterization

References

• PostgreSQL Documentation
• Use The Index, Luke
• PostgreSQL Wiki