Getting Started with PostgreSQL

PostgreSQL, often called Postgres, is the most advanced open-source relational database. It supports everything from basic CRUD operations to JSON document storage, full-text search, and geographic queries through PostGIS. Installing PostgreSQL varies by platform. On macOS, brew install postgresql. On Ubuntu, apt-get install postgresql. Windows has an installer from the official website.

After installation, switch to the postgres system user and create your first database. The psql command-line tool is your gateway to the database. It supports tab completion, command history, and backslash commands for common operations. Learn the backslash commands early because they save a lot of time. \dt lists tables, \d table_name describes a table, and \? shows all available commands.

-- First steps in psql
CREATE DATABASE myapp;
\c myapp

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    preferences JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Note: TIMESTAMPTZ stores timezone-aware timestamps

Advanced Data Types and JSONB

PostgreSQL distinguishes itself with advanced data types that go far beyond typical SQL databases. JSONB stores JSON data in a binary format that supports indexing. You can store a document with varying fields in a JSONB column and still query it efficiently. This is useful when you have flexible user profiles, product attributes that vary by category, or configuration data that does not justify a separate table.

Arrays are another first-class data type. You can store an array of integers, texts, or even composite types in a single column. PostgreSQL supports array operations like contains, overlaps, and element access. The ANY and ALL keywords let you query arrays in standard SQL syntax.

Range types store a range of values like date ranges, numeric ranges, or time ranges. They prevent overlapping reservations, enforce exclusion constraints, and simplify queries that check if a value falls within a range. A hotel booking system, for example, can use a daterange column for reservations and add an exclusion constraint to prevent double-booking.

-- JSONB queries
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    attributes JSONB
);

INSERT INTO products (attributes)
VALUES ('{"color": "red", "size": "L", "in_stock": true}');

-- Query JSONB fields
SELECT * FROM products
WHERE attributes ->> 'color' = 'red'
  AND (attributes -> 'in_stock')::boolean = true;

-- Create a GIN index for JSONB queries
CREATE INDEX idx_products_attrs ON products USING GIN(attributes);

Window Functions and Analytics

Window functions are one of PostgreSQL's most powerful features. They let you perform calculations across rows related to the current row without collapsing the result set. A regular GROUP BY reduces the number of rows returned, but a window function keeps all rows while computing aggregate values alongside them.

Common use cases include running totals, moving averages, ranking within groups, and finding the first or last value in a partition. The syntax uses OVER() and PARTITION BY. You can also use ORDER BY within the window to control the order of rows in the frame.

I use window functions for detecting trends, computing percentiles, and finding outliers. For example, finding the top three products per category by revenue is straightforward with ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY revenue DESC). No self-joins or subqueries needed.

-- Window function examples
SELECT 
    department,
    employee_name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank,
    AVG(salary) OVER (PARTITION BY department) as dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) as diff_from_avg
FROM employees;

Full-Text Search Capabilities

PostgreSQL has built-in full-text search that competes with dedicated search engines for many use cases. It supports stemming, ranking, highlighting, and multiple languages. The tsvector and tsquery types handle text preprocessing and search queries. You can create indexes on tsvector columns for fast search performance.

To set up full-text search, create a tsvector column that combines all the text fields you want to search. Use a trigger to keep it updated when data changes. The to_tsvector() function parses text into tokens, reduces them to lexemes, and stores them with position information. The to_tsquery() function parses search queries and supports boolean operators.

Ranking results with ts_rank() orders results by relevance. You can also use setweight() to give more importance to certain fields. For example, matches in the title field can be weighted higher than matches in the body field. This gives you Google-like search results without adding Elasticsearch to your stack.

-- Full-text search setup
ALTER TABLE articles ADD COLUMN search_vector tsvector;

CREATE FUNCTION update_search_vector() RETURNS trigger AS $$
BEGIN
    NEW.search_vector := 
        setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(NEW.body, '')), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- Query
SELECT title, ts_rank(search_vector, query) as rank
FROM articles, plainto_tsquery('english', 'database performance') as query
WHERE search_vector @@ query
ORDER BY rank DESC LIMIT 10;

Performance Tuning and Configuration

PostgreSQL comes with conservative default settings that work on a Raspberry Pi but will not perform well on a production server. The first thing to tune is shared_buffers, which controls how much memory PostgreSQL uses for caching data. Set it to about 25% of your server's RAM on a dedicated database server. effective_cache_size should be set to about 50% of RAM to help the query planner estimate whether indexes will fit in cache.

Work_mem controls memory used for sort operations and hash tables. If you have queries that sort large result sets, increasing work_mem can dramatically improve performance. The catch is that work_mem applies per operation, not per query, so a query with multiple sort operations can use multiple times work_mem. Start with 4MB to 8MB and monitor for disk-based sorts in the query logs.

Vacuuming is not optional in PostgreSQL. The autovacuum daemon runs automatically, but you should monitor its activity. Long-running transactions prevent vacuum from cleaning up dead rows, which leads to table bloat. Set idle_in_transaction_session_timeout to prevent these sessions from staying open indefinitely.

-- Configuration tuning (postgresql.conf)
shared_buffers = '2GB'          # 25% of RAM
work_mem = '64MB'               # per operation
maintenance_work_mem = '512MB'   # for VACUUM, CREATE INDEX
effective_cache_size = '8GB'    # 50% of RAM

-- Check for long-running queries
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

Backup Strategies and Point-in-Time Recovery

PostgreSQL offers several backup approaches. pg_dump creates logical backups that are portable across versions and architectures. For databases under 100GB, pg_dump is simple and reliable. For larger databases, physical backups with pg_basebackup are faster because they copy the data files directly.

Point-in-time recovery requires Write-Ahead Log (WAL) archiving. Configure archive_mode and archive_command in postgresql.conf to ship WAL segments to a safe location. With continuous WAL archiving, you can restore your database to any point in time by replaying WAL files from a base backup. This is essential for disaster recovery.

Test your restore procedure regularly. I have seen teams with flawless backup scripts that failed during a real restore because they never tested the procedure. Automate a weekly restore to a staging environment and run data integrity checks. Document the restore steps so anyone on the team can execute them during an incident.

# Logical backup
pg_dump --dbname=mydb --format=custom --file=mydb_backup.dump

# Physical backup for PITR
pg_basebackup --pgdata=/backup/base --wal-method=stream

# Restore from custom format
pg_restore --dbname=mydb --jobs=4 mydb_backup.dump