PostgreSQL Interview Questions

PostgreSQL is an open-source, object-relational database management system (ORDBMS) known for its reliability, feature robustness, and performance.

Key Features:
• ACID compliance for data integrity
• Support for advanced data types (JSON, XML, arrays)
• Extensibility with custom functions and data types
• Multi-version concurrency control (MVCC)
• Full-text search capabilities
• Partitioning and inheritance
• Point-in-time recovery
• Streaming replication
• Cross-platform compatibility

Tablespaces allow database administrators to define locations in the file system where database objects can be stored.

Benefits:
• Storage management across multiple disks
• Performance optimization
• Administrative flexibility
• Space allocation control

Example:

CREATE TABLESPACE fast_storage LOCATION '/ssd/postgres';
CREATE TABLE users (...) TABLESPACE fast_storage;

PostgreSQL uses roles for authentication and authorization.

Role Types:
• Login roles (users)
• Group roles (for organizing privileges)
• Superuser roles

Privileges:
• SELECT, INSERT, UPDATE, DELETE
• CREATE, DROP, ALTER
• USAGE, EXECUTE

Example:

CREATE ROLE developer;
GRANT SELECT, INSERT ON users TO developer;
CREATE ROLE john LOGIN PASSWORD 'password';
GRANT developer TO john;

Constraint Types:

PRIMARY KEY:
• Unique identifier
• Cannot be NULL
• Automatically creates index

FOREIGN KEY:
• References another table
• Maintains referential integrity
• Supports CASCADE actions

UNIQUE:
• Ensures uniqueness
• Allows one NULL value

CHECK:
• Custom validation rules
• Boolean expressions

NOT NULL:
• Prevents NULL values

Example:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id) ON DELETE CASCADE,
    amount DECIMAL CHECK (amount > 0),
    email VARCHAR UNIQUE NOT NULL
);

Materialized views store query results physically, unlike regular views that execute queries each time.

Benefits:
• Improved query performance
• Reduced computation overhead
• Snapshot of data at specific time
• Can be indexed for faster access

Example:

CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id, SUM(amount) as total_sales
FROM orders
GROUP BY product_id;

-- Refresh data
REFRESH MATERIALIZED VIEW sales_summary;

LISTEN/NOTIFY provides asynchronous communication between database sessions.

How it works:
• Sessions LISTEN to channels
• Other sessions send NOTIFY messages
• Listeners receive notifications asynchronously

Example:

-- Session 1: Listen for notifications
LISTEN order_updates;

-- Session 2: Send notification
NOTIFY order_updates, 'New order #123';

-- Session 1 receives the notification

Use Cases:
• Real-time updates
• Cache invalidation
• Event-driven architecture

Range types represent ranges of values with support for bounds and operations.

Built-in Range Types:
• int4range, int8range: Integer ranges
• numrange: Numeric ranges
• tsrange: Timestamp ranges
• daterange: Date ranges

Example:

-- Create table with range column
CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INT,
    period tsrange
);

-- Insert reservation
INSERT INTO reservations (room_id, period)
VALUES (101, '[2024-01-01 10:00, 2024-01-01 12:00)');

-- Check for overlaps
SELECT * FROM reservations
WHERE period && '[2024-01-01 11:00, 2024-01-01 13:00)';

MVCC (Multi-Version Concurrency Control) is PostgreSQL's method of handling concurrent access to data.

How it works:
• Each transaction sees a snapshot of data
• Multiple versions of rows can exist simultaneously
• Readers don't block writers and vice versa
• Uses transaction IDs (xmin, xmax) to track row versions
• VACUUM process cleans up old row versions

Benefits:
• High concurrency
• No read locks
• Consistent data views
• Better performance under load

PostgreSQL indexes improve query performance by creating efficient data access paths.

Index Types:

B-tree (Default):
• Best for equality and range queries
• Supports sorting operations

Hash:
• Only for equality comparisons
• Faster than B-tree for simple lookups

GiST (Generalized Search Tree):
• For geometric data types
• Full-text search

GIN (Generalized Inverted Index):
• For composite values (arrays, JSON)
• Full-text search

BRIN (Block Range Index):
• For very large tables
• Stores min/max values per block

SERIALIZABLE provides the highest isolation level, ensuring complete isolation between concurrent transactions.

This is a comprehensive topic that requires detailed understanding of PostgreSQL concepts and practical implementation knowledge.

JSONB stores data in binary format allowing faster operations and indexing, while JSON stores as text. JSONB is generally preferred for better performance.

This is a comprehensive topic that requires detailed understanding of PostgreSQL concepts and practical implementation knowledge.

PostgreSQL Replication Types:

Streaming Replication:
• Real-time data synchronization
• Master-slave setup
• Hot standby for read queries
• Automatic failover support

Logical Replication:
• Table-level replication
• Cross-version compatibility
• Selective data replication
• Multi-master possible

Physical Replication:
• Block-level copying
• Entire database cluster
• Same PostgreSQL version required

Use Cases:
• High availability
• Load balancing
• Disaster recovery
• Data warehousing

Stored Procedures (PostgreSQL 11+):
• Can manage transactions
• Support COMMIT/ROLLBACK
• Called with CALL statement
• Cannot return values directly

Functions:
• Cannot manage transactions
• Return values or tables
• Called in SELECT statements
• More flexible for calculations

Example Procedure:

CREATE PROCEDURE transfer_funds(
    sender_id INT,
    receiver_id INT,
    amount DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = sender_id;
    UPDATE accounts SET balance = balance + amount WHERE id = receiver_id;
    COMMIT;
END;
$$;

Partitioning divides large tables into smaller, manageable pieces while appearing as a single table.

Partition Types:

Range Partitioning:
• Based on value ranges
• Common for dates/timestamps
• Example: Monthly sales data

List Partitioning:
• Based on specific values
• Example: By country/region

Hash Partitioning:
• Even distribution
• Good for load balancing

Benefits:
• Improved query performance
• Easier maintenance
• Parallel processing
• Faster backups/restores
• Constraint exclusion

WAL ensures data integrity by logging changes before applying them to data files.

How WAL Works:
• Changes written to WAL first
• Then applied to data files
• Ensures crash recovery
• Enables point-in-time recovery

WAL Configuration:

-- Key parameters
wal_level = replica
max_wal_size = 1GB
checkpoint_completion_target = 0.9
archive_mode = on
archive_command = 'cp %p /archive/%f'

Benefits:
• Data durability
• Crash recovery
• Replication support
• Backup consistency

Configuration Tuning:
• shared_buffers (25% of RAM)
• effective_cache_size (75% of RAM)
• work_mem (per operation)
• maintenance_work_mem (for VACUUM)

Query Optimization:
• Use EXPLAIN ANALYZE
• Proper indexing strategy
• Avoid SELECT *
• Use prepared statements

Index Optimization:
• Create indexes on WHERE clauses
• Composite indexes for multiple columns
• Partial indexes for filtered data
• Regular REINDEX for maintenance

Maintenance:
• Regular VACUUM and ANALYZE
• Monitor table bloat
• Update statistics
• Archive old WAL files

CREATE INDEX ... WHERE creates a partial index that includes only rows satisfying the WHERE condition, saving space and improving performance.

This is a comprehensive topic that requires detailed understanding of PostgreSQL concepts and practical implementation knowledge.

Parallel Query (introduced in PostgreSQL 9.6+) automatically parallelizes certain operations like sequential scans and joins across multiple CPU cores.

This is a comprehensive topic that requires detailed understanding of PostgreSQL concepts and practical implementation knowledge.

Window functions perform calculations across a set of table rows related to the current row.

Common Functions:
• ROW_NUMBER(): Sequential numbering
• RANK(): Ranking with gaps
• LAG()/LEAD(): Access previous/next rows
• SUM() OVER: Running totals

Example:

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) as rank,
       SUM(salary) OVER (ORDER BY salary) as running_total
FROM employees;

Logical decoding extracts changes from WAL in a user-friendly format.

Applications:
• Change data capture (CDC)
• Real-time analytics
• Data synchronization
• Audit logging

Output Plugins:
• test_decoding (built-in)
• wal2json
• pgoutput (for logical replication)

Setup:

-- Enable logical replication
wal_level = logical
max_replication_slots = 4

-- Create replication slot
SELECT pg_create_logical_replication_slot('my_slot', 'test_decoding');

FDW allows PostgreSQL to access external data sources as if they were local tables.

Popular FDWs:
• postgres_fdw: Other PostgreSQL databases
• file_fdw: CSV files
• mysql_fdw: MySQL databases
• oracle_fdw: Oracle databases

Example:

CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'remote.example.com', dbname 'remote_db');

CREATE FOREIGN TABLE remote_users (
    id INT,
    name TEXT
) SERVER remote_server;

Table inheritance allows tables to inherit columns and constraints from parent tables.

Use Cases:
• Partitioning large tables
• Common column structures
• Hierarchical data modeling

Example:

CREATE TABLE vehicles (
    id SERIAL PRIMARY KEY,
    brand VARCHAR(50)
);

CREATE TABLE cars (
    doors INT
) INHERITS (vehicles);

-- Query all vehicles (including cars)
SELECT * FROM vehicles;

Benefits:
• Code reuse
• Simplified queries
• Automatic partitioning

Advisory locks are application-level locks that don't lock actual database objects.

Types:
• Session-level locks
• Transaction-level locks
• Shared and exclusive modes

Use Cases:
• Preventing duplicate processing
• Coordinating batch jobs
• Application-level synchronization

Example:

-- Acquire exclusive lock
SELECT pg_advisory_lock(12345);

-- Try to acquire lock (non-blocking)
SELECT pg_try_advisory_lock(12345);

-- Release lock
SELECT pg_advisory_unlock(12345);

Custom operators allow defining new symbols for operations on data types.

Example:

-- Create function first
CREATE FUNCTION add_percent(numeric, numeric)
RETURNS numeric AS $$
BEGIN
    RETURN $1 + ($1 * $2 / 100);
END;
$$ LANGUAGE plpgsql;

-- Create operator
CREATE OPERATOR +% (
    LEFTARG = numeric,
    RIGHTARG = numeric,
    FUNCTION = add_percent
);

-- Usage
SELECT 100 +% 15; -- Returns 115

Benefits:
• Domain-specific syntax
• Improved readability
• Custom data type operations

RLS allows tables to have policies that restrict which rows users can see or modify.

Example:

-- Enable RLS on table
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Create policy
CREATE POLICY user_documents ON documents
    FOR ALL TO public
    USING (owner_id = current_user_id());

-- Users only see their own documents

Benefits:
• Fine-grained access control
• Transparent to applications
• Multi-tenant applications
• Data privacy compliance