PostgreSQL/DBMS 101: Comprehensive Cheat Sheet

Table of Contents

1. Database Fundamentals
2. PostgreSQL Basics
3. Data Types
4. Creating Database Objects
5. Basic Queries
6. Joins
7. Indexes
8. Transactions
9. Constraints
10. Views
11. Functions and Stored Procedures
12. Common Table Expressions (CTEs)
13. Performance Optimization
14. Backup and Restore
15. Interview Questions

Database Fundamentals

What is a Database?

A database is an organized collection of structured data, typically stored electronically in a computer system.

Relational Database Concepts

• Table: Collection of related data organized in rows and columns
• Row: A single record in a table (also called a tuple)
• Column: A field in a table that holds a specific type of data
• Primary Key: Unique identifier for each row
• Foreign Key: References a primary key in another table
• Schema: Logical container for database objects (tables, views, functions, etc.)

ACID Properties

• Atomicity: Transactions are all-or-nothing
• Consistency: Database remains in a valid state before and after transactions
• Isolation: Concurrent transactions don't interfere with each other
• Durability: Committed transactions persist even if system fails

PostgreSQL Basics

Connecting to PostgreSQL

# Connect to a database
psql -h hostname -p port -U username -d database_name

# Common connection command
psql -U postgres -d mydatabase

# Connect to default database for your user
psql

Command Line Basics

-- List all databases
\l

-- Connect to a database
\c database_name

-- List all tables
\dt

-- List all schemas
\dn

-- Describe a table
\d table_name

-- List all users
\du

-- Execute SQL from a file
\i filename.sql

-- Quit psql
\q

Data Types

Common Data Types

Creating Database Objects

Create a Database

CREATE DATABASE bookstore;

Create a Schema

CREATE SCHEMA inventory;

Create a Table

CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2),
    publication_date DATE,
    in_stock BOOLEAN DEFAULT TRUE,
    genre VARCHAR(50)
);

Alter a Table

-- Add a column
ALTER TABLE books ADD COLUMN page_count INTEGER;

-- Modify a column
ALTER TABLE books ALTER COLUMN title TYPE VARCHAR(300);

-- Drop a column
ALTER TABLE books DROP COLUMN page_count;

-- Add a constraint
ALTER TABLE books ADD CONSTRAINT price_check CHECK (price > 0);

Drop a Table

DROP TABLE books;

Basic Queries

Insert Data

-- Insert a single row
INSERT INTO books (title, author, price, publication_date, genre)
VALUES ('The Great Gatsby', 'F. Scott Fitzgerald', 12.99, '1925-04-10', 'Fiction');

-- Insert multiple rows
INSERT INTO books (title, author, price, publication_date, genre)
VALUES 
    ('To Kill a Mockingbird', 'Harper Lee', 14.99, '1960-07-11', 'Fiction'),
    ('1984', 'George Orwell', 11.99, '1949-06-08', 'Science Fiction');

Select Data

-- Select all columns
SELECT * FROM books;

-- Select specific columns
SELECT title, author, price FROM books;

-- Select with filter
SELECT * FROM books WHERE price < 15.00;

-- Select with sorting
SELECT * FROM books ORDER BY publication_date DESC;

-- Select with limit
SELECT * FROM books LIMIT 10;

-- Select with offset (pagination)
SELECT * FROM books LIMIT 10 OFFSET 20;

-- Select with pattern matching
SELECT * FROM books WHERE title LIKE 'The%';

-- Select with NULL check
SELECT * FROM books WHERE genre IS NULL;

Update Data

-- Update all rows
UPDATE books SET in_stock = TRUE;

-- Update specific rows
UPDATE books SET price = 13.99 WHERE book_id = 1;

-- Update multiple columns
UPDATE books 
SET price = 15.99, in_stock = FALSE 
WHERE genre = 'Fiction' AND publication_date < '1950-01-01';

Delete Data

-- Delete specific rows
DELETE FROM books WHERE book_id = 3;

-- Delete all rows matching a condition
DELETE FROM books WHERE in_stock = FALSE;

-- Delete all rows
DELETE FROM books;

Joins

Database Setup for Join Examples

-- Create tables for join examples
CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    birth_year INTEGER
);

CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author_id INTEGER REFERENCES authors(author_id),
    price DECIMAL(10, 2),
    genre VARCHAR(50)
);

-- Insert sample data
INSERT INTO authors (name, birth_year) VALUES 
    ('J.K. Rowling', 1965),
    ('George Orwell', 1903),
    ('Jane Austen', 1775),
    ('Ernest Hemingway', 1899);

INSERT INTO books (title, author_id, price, genre) VALUES 
    ('Harry Potter', 1, 24.99, 'Fantasy'),
    ('1984', 2, 11.99, 'Science Fiction'),
    ('Animal Farm', 2, 9.99, 'Satire'),
    ('Pride and Prejudice', 3, 12.99, 'Classic'),
    ('The Old Man and the Sea', 4, 14.99, 'Fiction');

Inner Join

-- Returns only matching rows
SELECT b.title, a.name AS author
FROM books b
INNER JOIN authors a ON b.author_id = a.author_id;

Left Join

-- Returns all rows from the left table and matching rows from the right table
SELECT b.title, a.name AS author
FROM books b
LEFT JOIN authors a ON b.author_id = a.author_id;

Right Join

-- Returns all rows from the right table and matching rows from the left table
SELECT b.title, a.name AS author
FROM books b
RIGHT JOIN authors a ON b.author_id = a.author_id;

Full Outer Join

-- Returns all rows when there's a match in either table
SELECT b.title, a.name AS author
FROM books b
FULL OUTER JOIN authors a ON b.author_id = a.author_id;

Self Join

-- Join a table to itself
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    manager_id INTEGER REFERENCES employees(employee_id)
);

-- Self join to find employee and their manager
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

Indexes

Create Index

-- Create a basic index
CREATE INDEX idx_books_title ON books(title);

-- Create unique index
CREATE UNIQUE INDEX idx_authors_name ON authors(name);

-- Create multi-column index
CREATE INDEX idx_books_author_genre ON books(author_id, genre);

Drop Index

DROP INDEX idx_books_title;

When to Use Indexes

• Frequently queried columns
• Columns used in WHERE clauses
• Foreign key columns
• Columns used in JOIN conditions
• Columns used in ORDER BY or GROUP BY

Transactions

Basic Transaction

-- Start a transaction
BEGIN;

-- Execute operations
INSERT INTO authors (name, birth_year) VALUES ('Stephen King', 1947);
UPDATE books SET price = price * 1.1 WHERE genre = 'Horror';

-- Commit changes
COMMIT;

-- Or roll back changes
ROLLBACK;

Transaction Isolation Levels

-- Set transaction isolation level
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Other levels: READ UNCOMMITTED, REPEATABLE READ, SERIALIZABLE

Constraints

Primary Key

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Foreign Key

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    order_date DATE NOT NULL
);

Unique Constraint

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    username VARCHAR(50) NOT NULL
);

Check Constraint

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0),
    stock INTEGER CHECK (stock >= 0)
);

Not Null Constraint

CREATE TABLE contacts (
    contact_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

Views

Create View

-- Create a simple view
CREATE VIEW book_details AS
SELECT b.book_id, b.title, a.name AS author, b.genre, b.price
FROM books b
JOIN authors a ON b.author_id = a.author_id;

-- Query the view
SELECT * FROM book_details WHERE genre = 'Fantasy';

Materialized View

-- Create a materialized view (stored query results)
CREATE MATERIALIZED VIEW book_stats AS
SELECT genre, COUNT(*) AS book_count, AVG(price) AS avg_price
FROM books
GROUP BY genre;

-- Refresh the materialized view
REFRESH MATERIALIZED VIEW book_stats;

Functions and Stored Procedures

Create Function

-- Simple function to calculate discounted price
CREATE OR REPLACE FUNCTION calculate_discount(
    original_price DECIMAL,
    discount_percentage DECIMAL
)
RETURNS DECIMAL AS $$
BEGIN
    RETURN original_price - (original_price * discount_percentage / 100);
END;
$$ LANGUAGE plpgsql;

-- Use the function
SELECT title, price, calculate_discount(price, 10) AS discounted_price
FROM books;

Create Stored Procedure (PostgreSQL 11+)

-- Procedure to update book prices
CREATE OR REPLACE PROCEDURE update_book_prices(
    genre_name VARCHAR,
    increase_percentage DECIMAL
)
AS $$
BEGIN
    UPDATE books
    SET price = price + (price * increase_percentage / 100)
    WHERE genre = genre_name;
    
    COMMIT;
END;
$$ LANGUAGE plpgsql;

-- Call the procedure
CALL update_book_prices('Fantasy', 5);

Common Table Expressions (CTEs)

Basic CTE

-- Find books more expensive than average
WITH avg_price_cte AS (
    SELECT AVG(price) AS avg_price FROM books
)
SELECT b.title, b.price
FROM books b, avg_price_cte
WHERE b.price > avg_price_cte.avg_price
ORDER BY b.price DESC;

Recursive CTE

-- Create a table for hierarchical data
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INTEGER REFERENCES categories(category_id)
);

-- Insert sample data
INSERT INTO categories (name, parent_id) VALUES
    ('Books', NULL),
    ('Fiction', 1),
    ('Non-Fiction', 1),
    ('Science Fiction', 2),
    ('Fantasy', 2),
    ('Biography', 3),
    ('History', 3),
    ('Space Opera', 4);

-- Recursive CTE to query hierarchical data
WITH RECURSIVE category_tree AS (
    -- Base case: top-level categories
    SELECT category_id, name, parent_id, 1 AS level, name AS path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- Recursive case: child categories
    SELECT c.category_id, c.name, c.parent_id, ct.level + 1, ct.path || ' > ' || c.name
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT path, level FROM category_tree ORDER BY path;

Performance Optimization

EXPLAIN

-- Analyze query execution plan
EXPLAIN SELECT * FROM books WHERE genre = 'Fantasy';

-- EXPLAIN with actual execution statistics
EXPLAIN ANALYZE SELECT * FROM books WHERE genre = 'Fantasy';

Index Optimization

-- Create index for frequent query patterns
CREATE INDEX idx_books_genre ON books(genre);

-- Create index for range queries
CREATE INDEX idx_books_price ON books(price);

-- Create index for pattern matching
CREATE INDEX idx_books_title_pattern ON books USING gin (title gin_trgm_ops);
-- Note: Requires the pg_trgm extension: CREATE EXTENSION pg_trgm;

Query Optimization

-- Use specific columns instead of SELECT *
SELECT title, author_id, price FROM books WHERE genre = 'Fantasy';

-- Use EXISTS instead of IN for subqueries when appropriate
SELECT * FROM authors a
WHERE EXISTS (
    SELECT 1 FROM books b WHERE b.author_id = a.author_id AND b.genre = 'Fantasy'
);

-- Use LIMIT for pagination
SELECT * FROM books ORDER BY publication_date DESC LIMIT 10 OFFSET 20;

Backup and Restore

Backup

# Backup a specific database
pg_dump -U username -d database_name -f backup_file.sql

# Backup with compression
pg_dump -U username -d database_name | gzip > backup_file.sql.gz

# Backup all databases
pg_dumpall -U username -f all_databases_backup.sql

Restore

# Restore a database
psql -U username -d database_name -f backup_file.sql

# Restore from compressed backup
gunzip -c backup_file.sql.gz | psql -U username -d database_name

# Restore all databases
psql -U username -f all_databases_backup.sql

Interview Questions

1. What is PostgreSQL and how does it differ from other RDBMS?

   • PostgreSQL is an open-source object-relational database system.
   • Differences include: advanced data types (arrays, JSON), extensibility, strong standards compliance, multi-version concurrency control (MVCC), and powerful indexing options.

2. Explain the concept of ACID properties in databases.

   • Atomicity: Transactions are all-or-nothing operations.
   • Consistency: Database remains in a valid state before and after transactions.
   • Isolation: Concurrent transactions don't interfere with each other.
   • Durability: Committed transactions persist even if system crashes.

3. What is the difference between a primary key and a unique constraint?

   • Both ensure uniqueness, but primary keys also enforce NOT NULL and only one primary key is allowed per table.
   • Primary keys are used as the main identifier for rows and are often referenced by foreign keys.
   • Tables can have multiple unique constraints.

4. Explain the different types of joins in PostgreSQL.

   • INNER JOIN: Returns rows that have matching values in both tables.
   • LEFT JOIN: Returns all rows from the left table and matching rows from the right.
   • RIGHT JOIN: Returns all rows from the right table and matching rows from the left.
   • FULL OUTER JOIN: Returns all rows when there's a match in either table.
   • CROSS JOIN: Returns the Cartesian product of both tables.

5. What are indexes in PostgreSQL and when should you use them?

   • Indexes are data structures that speed up data retrieval operations.
   • Use indexes on columns frequently used in WHERE clauses, JOIN conditions, and ORDER BY/GROUP BY operations.
   • Common index types: B-tree (default), Hash, GiST, SP-GiST, GIN, and BRIN.

6. What is the difference between DELETE, TRUNCATE, and DROP commands?

   • DELETE: Removes specific rows based on conditions; can be rolled back; fires triggers.
   • TRUNCATE: Removes all rows from a table quickly; cannot be rolled back easily; doesn't fire triggers.
   • DROP: Removes the entire table structure and data; cannot be rolled back.

7. What is normalization and what are the normal forms?

   • Normalization is organizing data to reduce redundancy and improve data integrity.
   • Normal forms:
     • 1NF: Eliminate repeating groups, create separate tables for related data.
     • 2NF: Meet 1NF and remove partial dependencies.
     • 3NF: Meet 2NF and remove transitive dependencies.
     • BCNF: More stringent version of 3NF.
     • 4NF: Deal with multi-valued dependencies.
     • 5NF: Deal with join dependencies.

8. How do you handle concurrency in PostgreSQL?

   • PostgreSQL uses Multi-Version Concurrency Control (MVCC).
   • Each transaction sees a snapshot of the database as it was at the beginning of the transaction.
   • Transaction isolation levels control how transactions interact with each other.

9. What are transaction isolation levels in PostgreSQL?

   • READ UNCOMMITTED: Can read uncommitted changes (PostgreSQL treats this as READ COMMITTED).
   • READ COMMITTED: Only read committed changes (default in PostgreSQL).
   • REPEATABLE READ: All reads in a transaction see same data.
   • SERIALIZABLE: Transactions executed as if they were sequential.

10. Explain the difference between CHAR, VARCHAR, and TEXT data types.

    • CHAR(n): Fixed-length string, padded with spaces if shorter than n.
    • VARCHAR(n): Variable-length string with a maximum length of n.
    • TEXT: Variable-length string with unlimited length.

11. What is the purpose of the EXPLAIN command?

    • EXPLAIN shows the execution plan the PostgreSQL query planner generates.
    • EXPLAIN ANALYZE also executes the query and shows actual run times.
    • Helps identify bottlenecks and optimization opportunities.

12. How do you optimize a slow PostgreSQL query?

    • Analyze with EXPLAIN ANALYZE.
    • Add proper indexes.
    • Rewrite queries to be more efficient.
    • Update statistics with ANALYZE.
    • Adjust configuration parameters.
    • Consider table partitioning for large tables.

13. What are materialized views and when would you use them?

    • Materialized views store query results physically.
    • Use when:
      • Complex queries need to be run frequently.
      • The underlying data doesn't change often.
      • Query results are expensive to compute.
    • Must be refreshed to update data.

14. How would you handle database migrations in a production environment?

    • Plan migrations during low-traffic periods.
    • Use tools like Flyway, Liquibase, or custom migration scripts.
    • Test migrations in a staging environment first.
    • Back up the database before migration.
    • Consider using transaction blocks for safety.
    • Have a rollback plan if something goes wrong.

15. What is a database deadlock and how can it be prevented?

    • A deadlock occurs when two or more transactions are waiting for each other to release locks.
    • Prevention methods:
      • Consistent order of accessing resources.
      • Shorter transactions.
      • Appropriate isolation levels.
      • Use deadlock_timeout and statement_timeout settings.
      • Analyze and redesign queries that frequently cause deadlocks.

16. What is the difference between a function and a stored procedure in PostgreSQL?

    • Functions must return a value; procedures don't need to return anything.
    • Functions can be used in SELECT statements; procedures are called using CALL.
    • Procedures can manage transactions (COMMIT/ROLLBACK); functions cannot.
    • Procedures were introduced in PostgreSQL 11; functions have been available much longer.

17. How would you implement pagination in PostgreSQL?

    -- Simple pagination using LIMIT and OFFSET
    SELECT * FROM books ORDER BY title LIMIT 10 OFFSET 20;
    
    -- More efficient pagination for large datasets
    SELECT * FROM books 
    WHERE (title, book_id) > ('Last title from previous page', last_id)
    ORDER BY title, book_id LIMIT 10;
    

18. Explain the purpose of a database schema and when you would use multiple schemas.

    • A schema is a namespace for database objects (tables, views, functions).
    • Use multiple schemas to:
      • Organize objects logically.
      • Separate objects by application component.
      • Control access permissions at a more granular level.
      • Allow multiple users to use the same database without interference.

19. What is JSONB data type in PostgreSQL and how is it different from JSON?

    • JSONB stores JSON data in a binary, decomposed format.
    • JSONB supports indexing and is more efficient for processing.
    • JSON preserves whitespace and key order; JSONB doesn't.
    • JSONB supports more operators and functions for querying.

20. How do you handle database backups and what's your backup strategy?

    • Regular automated backups (pg_dump for logical backups, pg_basebackup for physical).
    • Point-in-time recovery using WAL (Write-Ahead Log) archiving.
    • Store backups in multiple locations.
    • Test restoration process regularly.
    • Consider both full and incremental backups.
    • Different retention policies for different backup types.