Back to Database Engineering

Module 2: PostgreSQL

Master PostgreSQL, the world's most advanced open-source relational database.

Why PostgreSQL?

PostgreSQL (often called "Postgres") is like the Swiss Army knife of databases - it handles everything from simple data storage to complex analytics. It's free, open-source, and trusted by companies like Instagram, Spotify, and Reddit.

🐘 PostgreSQL Advantages:

  • ACID Compliant: Reliable transactions, no data loss
  • Advanced Features: JSON, arrays, full-text search, geospatial data
  • Extensible: Add custom functions, data types, operators
  • Standards Compliant: Follows SQL standards closely
  • Active Community: Great documentation and support

PostgreSQL Setup & Configuration

Installation

macOS (using Homebrew):

brew install postgresql@15
brew services start postgresql@15

Ubuntu/Debian:

sudo apt update
sudo apt install postgresql postgresql-contrib

Windows:

Download installer from postgresql.org

Connecting to PostgreSQL

# Connect to default database

psql -U postgres

# Create a new database

CREATE DATABASE myapp;

# Connect to specific database

psql -U postgres -d myapp

# List all databases

\l

# List all tables

\dt

Advanced Data Types

PostgreSQL goes beyond basic integers and strings. It supports JSON, arrays, ranges, and more - giving you flexibility without sacrificing relational database benefits.

JSON & JSONB

Store and query JSON data directly. JSONB (binary JSON) is faster for queries.

-- Create table with JSON column

CREATE TABLE products (

id SERIAL PRIMARY KEY,

name VARCHAR(100),

metadata JSONB

);

-- Insert JSON data

INSERT INTO products (name, metadata)

VALUES ('Laptop', {'brand': 'Dell', 'ram': 16, 'ssd': true});

-- Query JSON fields

SELECT name, metadata->>'brand' as brand

FROM products

WHERE metadata->>'ram' > 8;

Arrays

Store multiple values in a single column. Great for tags, categories, etc.

-- Create table with array column

CREATE TABLE posts (

id SERIAL PRIMARY KEY,

title VARCHAR(200),

tags TEXT[]

);

-- Insert array data

INSERT INTO posts (title, tags)

VALUES ('PostgreSQL Guide', ARRAY['database', 'sql', 'tutorial']);

-- Query arrays

SELECT * FROM posts

WHERE 'database' = ANY(tags);

UUID

Universally unique identifiers - great for distributed systems.

-- Enable UUID extension

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Create table with UUID

CREATE TABLE users (

id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

email VARCHAR(255) UNIQUE

);

Stored Procedures & Functions

Functions let you write reusable database logic. Like functions in programming, but they run inside the database for better performance.

Creating Functions

-- Simple function to calculate discount

CREATE OR REPLACE FUNCTION calculate_discount(

price DECIMAL,

discount_percent INTEGER

) RETURNS DECIMAL AS $$

BEGIN

RETURN price - (price * discount_percent / 100);

END;

$$ LANGUAGE plpgsql;

-- Use the function

SELECT calculate_discount(100.00, 20); -- Returns 80.00

Triggers

Triggers automatically execute functions when certain events occur (INSERT, UPDATE, DELETE).

-- Function to update timestamp

CREATE OR REPLACE FUNCTION update_modified_column()

RETURNS TRIGGER AS $$

BEGIN

NEW.updated_at = NOW();

RETURN NEW;

END;

$$ LANGUAGE plpgsql;

-- Create trigger

CREATE TRIGGER update_user_modtime

BEFORE UPDATE ON users

FOR EACH ROW

EXECUTE FUNCTION update_modified_column();

Full-Text Search

PostgreSQL has built-in full-text search - no need for external search engines for many use cases. It's like having Google search inside your database!

-- Create table with text search

CREATE TABLE articles (

id SERIAL PRIMARY KEY,

title TEXT,

content TEXT,

search_vector tsvector

);

-- Create index for fast searching

CREATE INDEX articles_search_idx

ON articles USING GIN(search_vector);

-- Update search vector

UPDATE articles

SET search_vector =

to_tsvector('english', title || ' ' || content);

-- Search articles

SELECT title, content

FROM articles

WHERE search_vector @@ to_tsquery('english', 'postgresql & database');

🔍 Search Features:

  • • Stemming: "running" matches "run"
  • • Stop words: Ignores common words like "the", "a"
  • • Ranking: Results sorted by relevance
  • • Multiple languages supported

PostgreSQL Performance Tips

✅ Do

  • • Use indexes on frequently queried columns
  • • Use EXPLAIN ANALYZE to understand queries
  • • Use connection pooling (PgBouncer)
  • • Regular VACUUM and ANALYZE
  • • Use prepared statements
  • • Partition large tables

❌ Don't

  • • Don't use SELECT * in production
  • • Don't create too many indexes
  • • Don't ignore slow query logs
  • • Don't use LIKE '%text%' without full-text search
  • • Don't forget to close connections
  • • Don't skip backups!

🛠️ Hands-On Project: Blog Platform Database

Build a complete blog platform database using PostgreSQL advanced features.

Project Requirements:

  • ✓ Users table with UUID primary keys
  • ✓ Posts table with JSONB metadata (views, likes)
  • ✓ Tags stored as arrays
  • ✓ Full-text search on post content
  • ✓ Function to calculate reading time
  • ✓ Trigger to auto-update modified timestamps
  • ✓ Indexes for performance

📚 Module Summary

You've mastered PostgreSQL fundamentals and advanced features:

  • ✓ PostgreSQL setup and configuration
  • ✓ Advanced data types (JSON, arrays, UUID)
  • ✓ Stored procedures and functions
  • ✓ Triggers for automation
  • ✓ Full-text search capabilities
  • ✓ Performance optimization tips

Next: Learn MongoDB and NoSQL databases!