PostgreSQL for Developers: From Basics to Advanced Queries

Why PostgreSQL?

PostgreSQL is the most advanced open-source relational database. It combines SQL compliance with powerful features like JSON support, full-text search, and advanced indexing—without sacrificing reliability.

When to choose PostgreSQL:

• Need ACID guarantees
• Complex queries and joins
• JSON data alongside relational data
• Full-text search
• Geographic data (PostGIS)
• Multi-tenant applications

When to consider alternatives:

• Simple key-value needs → Redis
• Document-heavy workload → MongoDB
• Time-series data → TimescaleDB
• Graph relationships → Neo4j

Installation and Setup

Local Development

# macOS
brew install postgresql@15
brew services start postgresql@15

# Ubuntu/Debian
sudo apt update
sudo apt install postgresql postgresql-contrib

# Windows
# Download from https://www.postgresql.org/download/windows/

# Docker
docker run --name postgres \
  -e POSTGRES_PASSWORD=password \
  -e POSTGRES_DB=myapp \
  -p 5432:5432 \
  -d postgres:15

Connect via psql

# Connect to database
psql -U postgres -d myapp

# Common psql commands
\l                 # list databases
\c dbname          # connect to database
\dt                # list tables
\d tablename       # describe table
\du                # list users
\q                 # quit
\?                 # help

Schema Design Fundamentals

Creating Tables

-- Users table
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  username VARCHAR(50) UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Posts table with foreign key
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title VARCHAR(255) NOT NULL,
  content TEXT,
  published BOOLEAN DEFAULT false,
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Comments table
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Tags (many-to-many)
CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE post_tags (
  post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
  tag_id INTEGER REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

Data Types

-- Common types
INTEGER, BIGINT, SERIAL, BIGSERIAL
NUMERIC(10, 2), DECIMAL, REAL, DOUBLE PRECISION
VARCHAR(n), TEXT, CHAR(n)
BOOLEAN
DATE, TIME, TIMESTAMP, TIMESTAMPTZ
UUID
JSON, JSONB
ARRAY (e.g., INTEGER[])

-- Examples
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price NUMERIC(10, 2) NOT NULL,
  tags TEXT[],
  metadata JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CRUD Operations

Insert

-- Single insert
INSERT INTO users (email, username, password_hash)
VALUES ('user@example.com', 'john', 'hashed_password');

-- Multiple inserts
INSERT INTO posts (user_id, title, content)
VALUES 
  ('uuid-1', 'First Post', 'Content here'),
  ('uuid-2', 'Second Post', 'More content');

-- Insert and return
INSERT INTO users (email, username, password_hash)
VALUES ('jane@example.com', 'jane', 'hash')
RETURNING id, email, created_at;

-- Insert from select
INSERT INTO archive_posts
SELECT * FROM posts WHERE created_at < NOW() - INTERVAL '1 year';

Select

-- Basic select
SELECT * FROM users;
SELECT email, username FROM users WHERE id = '123';

-- With conditions
SELECT * FROM posts 
WHERE published = true 
  AND created_at > NOW() - INTERVAL '7 days'
ORDER BY created_at DESC
LIMIT 10;

-- Joins
SELECT 
  p.title,
  u.username,
  COUNT(c.id) as comment_count
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN comments c ON p.id = c.post_id
WHERE p.published = true
GROUP BY p.id, p.title, u.username
HAVING COUNT(c.id) > 5
ORDER BY comment_count DESC;

Update

-- Basic update
UPDATE users 
SET updated_at = NOW()
WHERE id = '123';

-- Multiple columns
UPDATE posts
SET 
  published = true,
  published_at = NOW()
WHERE id = 1;

-- Update with return
UPDATE users
SET username = 'newname'
WHERE id = '123'
RETURNING *;

-- Conditional update
UPDATE products
SET price = price * 1.1
WHERE category = 'electronics'
  AND price < 1000;

Delete

-- Basic delete
DELETE FROM comments WHERE id = 1;

-- With condition
DELETE FROM posts 
WHERE published = false 
  AND created_at < NOW() - INTERVAL '30 days';

-- Delete and return
DELETE FROM users 
WHERE email = 'user@example.com'
RETURNING id, email;

Indexes for Performance

When to Add Indexes

-- Columns in WHERE clauses
CREATE INDEX idx_posts_published ON posts(published);

-- Foreign keys
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_comments_post_id ON comments(post_id);

-- Composite indexes (order matters!)
CREATE INDEX idx_posts_user_published 
  ON posts(user_id, published);

-- Unique indexes
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Partial indexes
CREATE INDEX idx_posts_published_true 
  ON posts(user_id) 
  WHERE published = true;

-- Expression indexes
CREATE INDEX idx_users_lower_email 
  ON users(LOWER(email));

Index Types

-- B-tree (default, most common)
CREATE INDEX idx_name ON table(column);

-- Hash (equality only)
CREATE INDEX idx_name ON table USING HASH(column);

-- GIN (full-text search, JSONB)
CREATE INDEX idx_posts_content 
  ON posts USING GIN(to_tsvector('english', content));

CREATE INDEX idx_products_metadata 
  ON products USING GIN(metadata);

-- GiST (geometric, full-text)
CREATE INDEX idx_locations ON places USING GIST(location);

Analyzing Index Usage

-- View indexes
\di
SELECT * FROM pg_indexes WHERE tablename = 'posts';

-- Index size
SELECT 
  indexname,
  pg_size_pretty(pg_relation_size(indexname::regclass)) as size
FROM pg_indexes
WHERE tablename = 'posts';

-- Unused indexes
SELECT 
  schemaname, 
  tablename, 
  indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE 'pg_toast%';

JSON and JSONB

Why JSONB?

• Binary format (faster)
• Supports indexing
• Efficient operators
• Supports GIN indexes

Use JSONB instead of JSON unless you need to preserve exact formatting.

Working with JSONB

-- Insert JSONB
INSERT INTO products (name, metadata)
VALUES (
  'Laptop',
  '{"brand": "Dell", "specs": {"ram": 16, "storage": 512}}'
);

-- Query JSONB
SELECT name, metadata->>'brand' as brand
FROM products;

SELECT name, metadata->'specs'->>'ram' as ram
FROM products
WHERE metadata->'specs'->>'ram' = '16';

-- Check existence
SELECT * FROM products
WHERE metadata ? 'brand';  -- key exists

SELECT * FROM products
WHERE metadata @> '{"brand": "Dell"}';  -- contains

-- Update JSONB
UPDATE products
SET metadata = metadata || '{"featured": true}'
WHERE id = 1;

UPDATE products
SET metadata = jsonb_set(
  metadata, 
  '{specs,ram}', 
  '32'
)
WHERE id = 1;

-- Array operations
SELECT 
  name,
  jsonb_array_elements(metadata->'tags') as tag
FROM products;

JSONB Indexes

-- GIN index for containment
CREATE INDEX idx_products_metadata 
  ON products USING GIN(metadata);

-- Specific path
CREATE INDEX idx_products_brand 
  ON products((metadata->>'brand'));

Full-Text Search

-- Add tsvector column
ALTER TABLE posts 
  ADD COLUMN search_vector tsvector;

-- Update search vector
UPDATE posts
SET search_vector = 
  to_tsvector('english', title || ' ' || content);

-- Trigger to keep it updated
CREATE OR REPLACE FUNCTION posts_search_trigger() 
RETURNS trigger AS $$
BEGIN
  NEW.search_vector := 
    to_tsvector('english', NEW.title || ' ' || NEW.content);
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_search_update
BEFORE INSERT OR UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION posts_search_trigger();

-- Search
SELECT title, 
  ts_rank(search_vector, query) as rank
FROM posts, 
  to_tsquery('english', 'postgresql & database') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- GIN index
CREATE INDEX idx_posts_search 
  ON posts USING GIN(search_vector);

Advanced Queries

Window Functions

-- Row numbers
SELECT 
  title,
  ROW_NUMBER() OVER (ORDER BY created_at DESC) as row_num
FROM posts;

-- Rank by user
SELECT 
  user_id,
  title,
  RANK() OVER (PARTITION BY user_id ORDER BY created_at DESC) as rank
FROM posts;

-- Running total
SELECT 
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) as running_total
FROM transactions;

-- Moving average
SELECT 
  date,
  value,
  AVG(value) OVER (
    ORDER BY date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as moving_avg_7days
FROM metrics;

Common Table Expressions (CTEs)

-- Basic CTE
WITH popular_posts AS (
  SELECT p.*, COUNT(c.id) as comment_count
  FROM posts p
  LEFT JOIN comments c ON p.id = c.post_id
  GROUP BY p.id
  HAVING COUNT(c.id) > 10
)
SELECT * FROM popular_posts
WHERE published = true;

-- Recursive CTE (organization tree)
WITH RECURSIVE org_tree AS (
  -- Base case
  SELECT id, name, manager_id, 1 as level
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- Recursive case
  SELECT e.id, e.name, e.manager_id, ot.level + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, name;

Aggregations

-- Group by with rollup
SELECT 
  category,
  COUNT(*) as count,
  AVG(price) as avg_price,
  SUM(price) as total
FROM products
GROUP BY category;

-- Array aggregation
SELECT 
  p.id,
  p.title,
  ARRAY_AGG(t.name) as tags
FROM posts p
LEFT JOIN post_tags pt ON p.id = pt.post_id
LEFT JOIN tags t ON pt.tag_id = t.id
GROUP BY p.id, p.title;

-- JSON aggregation
SELECT 
  u.username,
  JSON_AGG(
    JSON_BUILD_OBJECT(
      'title', p.title,
      'created_at', p.created_at
    )
  ) as posts
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username;

Transactions and Locking

Basic Transactions

BEGIN;

UPDATE accounts 
SET balance = balance - 100 
WHERE id = 1;

UPDATE accounts 
SET balance = balance + 100 
WHERE id = 2;

COMMIT;
-- Or ROLLBACK if error

Isolation Levels

-- Read Committed (default)
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Repeatable Read
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Serializable (strictest)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Row-Level Locking

-- FOR UPDATE (exclusive lock)
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- FOR SHARE (shared lock)
SELECT * FROM posts WHERE id = 1 FOR SHARE;

-- SKIP LOCKED (skip locked rows)
SELECT * FROM queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

Performance Optimization

EXPLAIN ANALYZE

-- View query plan
EXPLAIN SELECT * FROM posts WHERE user_id = '123';

-- With execution stats
EXPLAIN ANALYZE 
SELECT p.*, u.username
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE p.published = true;

-- What to look for:
-- - Seq Scan (bad on large tables) → add index
-- - Index Scan (good)
-- - High cost numbers
-- - Actual time vs planned

Query Optimization Tips

-- ❌ Avoid SELECT *
SELECT * FROM posts;  -- Fetches all columns

-- ✅ Select only needed columns
SELECT id, title, created_at FROM posts;

-- ❌ Functions on indexed columns
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- ✅ Use expression index or avoid function
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- ❌ OR on different columns
SELECT * FROM posts WHERE user_id = '123' OR category = 'tech';

-- ✅ Use UNION
SELECT * FROM posts WHERE user_id = '123'
UNION
SELECT * FROM posts WHERE category = 'tech';

Vacuum and Analyze

-- Update statistics
ANALYZE posts;

-- Reclaim space
VACUUM posts;

-- Both
VACUUM ANALYZE posts;

-- Auto-vacuum (runs automatically)
-- Check status
SELECT * FROM pg_stat_user_tables WHERE relname = 'posts';

Connection Pooling

Node.js with pg

const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'myapp',
  user: 'postgres',
  password: 'password',
  max: 20,  // Max connections
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// Query
const result = await pool.query(
  'SELECT * FROM users WHERE id = $1',
  ['123']
);

// Transaction
const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [100, 1]);
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [100, 2]);
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();
}

Prepared Statements

// Parameterized query (safe from SQL injection)
const result = await pool.query(
  'SELECT * FROM users WHERE email = $1 AND status = $2',
  ['user@example.com', 'active']
);

// Named parameters with pg-promise
const result = await db.query(
  'SELECT * FROM users WHERE email = ${email} AND status = ${status}',
  { email: 'user@example.com', status: 'active' }
);

Migrations

Using node-pg-migrate

npm install node-pg-migrate

# Create migration
npx node-pg-migrate create add-users-table

# Run migrations
npx node-pg-migrate up

# Rollback
npx node-pg-migrate down

Migration File

// migrations/1234567890_add-users-table.js
exports.up = (pgm) => {
  pgm.createTable('users', {
    id: { type: 'uuid', primaryKey: true, default: pgm.func('gen_random_uuid()') },
    email: { type: 'varchar(255)', notNull: true, unique: true },
    username: { type: 'varchar(50)', notNull: true, unique: true },
    created_at: { type: 'timestamptz', notNull: true, default: pgm.func('NOW()') },
  });
  
  pgm.createIndex('users', 'email');
};

exports.down = (pgm) => {
  pgm.dropTable('users');
};

Security Best Practices

1. Never Concatenate SQL

// ❌ SQL Injection vulnerability
const email = req.body.email;
const query = `SELECT * FROM users WHERE email = '${email}'`;

// ✅ Use parameterized queries
const query = 'SELECT * FROM users WHERE email = $1';
const result = await pool.query(query, [email]);

2. Principle of Least Privilege

-- Create read-only user
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE myapp TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

CREATE USER app_reader WITH PASSWORD 'password';
GRANT readonly TO app_reader;

-- Application user with limited access
CREATE USER app_user WITH PASSWORD 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
REVOKE ALL ON users.password_hash FROM app_user;

3. Row-Level Security

-- Enable RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Policy: Users see only their posts
CREATE POLICY posts_user_policy ON posts
  FOR ALL
  USING (user_id = current_user_id());

-- Policy: Everyone sees published posts
CREATE POLICY posts_public_policy ON posts
  FOR SELECT
  USING (published = true);

Backup and Recovery

# Backup database
pg_dump -U postgres myapp > backup.sql

# Backup with compression
pg_dump -U postgres -F c myapp > backup.dump

# Restore
psql -U postgres myapp < backup.sql
pg_restore -U postgres -d myapp backup.dump

# Backup all databases
pg_dumpall -U postgres > all_dbs.sql

Monitoring

-- Active connections
SELECT count(*) FROM pg_stat_activity;

-- Long-running queries
SELECT 
  pid,
  now() - query_start as duration,
  query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

-- Database size
SELECT 
  pg_size_pretty(pg_database_size('myapp')) as size;

-- Table sizes
SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Resources

• PostgreSQL Documentation
• pg (Node.js driver)
• Use The Index, Luke!
• Explain.depesz.com (query plan visualizer)