Developer's Catalog

Database

PostgreSQL

Overview

PostgreSQL is the world's most advanced open-source relational database management system with over 30 years of development history. Known for its high extensibility, standards compliance, and robustness, it provides enterprise-level features.

Details

PostgreSQL evolved from a project that began at the University of California, Berkeley in 1986, and is now widely used worldwide. It fully supports ACID properties and offers complete SQL standard compliance, foreign keys, joins, views, triggers, and stored procedures. It also supports unstructured data types like JSON, XML, and arrays, providing NoSQL capabilities.

PostgreSQL features:

• Complete ACID properties (Atomicity, Consistency, Isolation, Durability)
• Multi-Version Concurrency Control (MVCC)
• Rich data types (JSON, XML, arrays, range types, etc.)
• Full-text search capabilities
• Geographic Information System (PostGIS extension)
• Custom functions and stored procedures
• Multiple index types (B-tree, Hash, GiST, SP-GiST, GIN, BRIN)
• Partitioning functionality
• Replication and clustering

Advantages and Disadvantages

Advantages

• High reliability: Robust transaction processing with ACID properties and MVCC
• Standards compliant: One of the most SQL standard-compliant databases
• High extensibility: Ability to create custom data types, functions, and operators
• Rich features: Geographic information, full-text search, JSON processing, and more
• Cost-effective: Open-source with free commercial use
• Active community: Continuous development and support

Disadvantages

• Complexity: High learning curve due to rich feature set
• Memory usage: Can consume large amounts of memory
• Configuration complexity: Requires tuning for optimal performance
• Initial setup: Default configuration may not be optimized

Key Links

• Official Website
• Documentation
• Tutorial
• GitHub

Code Examples

Installation & Setup

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

# Red Hat/CentOS
sudo yum install postgresql-server postgresql-contrib
sudo postgresql-setup initdb

# macOS (Homebrew)
brew install postgresql
brew services start postgresql

# Docker
docker run --name postgres-db -e POSTGRES_PASSWORD=mypassword -p 5432:5432 -d postgres

Basic Operations (CRUD)

-- Database connection
psql -U username -d database_name

-- Create table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert data (Create)
INSERT INTO users (name, email) VALUES 
('John Doe', '[email protected]'),
('Jane Smith', '[email protected]');

-- Read data (Read)
SELECT * FROM users;
SELECT * FROM users WHERE name LIKE 'John%';

-- Update data (Update)
UPDATE users SET email = '[email protected]' 
WHERE name = 'John Doe';

-- Delete data (Delete)
DELETE FROM users WHERE id = 1;

Data Modeling

-- Foreign key constraints
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    amount DECIMAL(10,2) NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_order_date ON orders(order_date);

-- Create view
CREATE VIEW user_orders AS
SELECT u.name, u.email, o.amount, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id;

Indexing & Optimization

-- Composite index
CREATE INDEX idx_user_date ON orders(user_id, order_date);

-- Partial index
CREATE INDEX idx_active_users ON users(id) WHERE active = true;

-- Check execution plan
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

-- Update statistics
ANALYZE users;

-- Run vacuum
VACUUM ANALYZE users;

Practical Examples

-- Using JSON type
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    attributes JSONB
);

INSERT INTO products (name, attributes) VALUES 
('Laptop', '{"brand": "Dell", "cpu": "Intel i7", "ram": "16GB"}');

SELECT * FROM products 
WHERE attributes->>'brand' = 'Dell';

-- Using array type
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    tags TEXT[]
);

INSERT INTO articles (title, tags) VALUES 
('PostgreSQL Tutorial', ARRAY['database', 'postgresql', 'sql']);

SELECT * FROM articles 
WHERE 'postgresql' = ANY(tags);

-- Window functions
SELECT 
    name, 
    amount,
    ROW_NUMBER() OVER (ORDER BY amount DESC) as rank
FROM orders o
JOIN users u ON o.user_id = u.id;

Best Practices

-- Transaction management
BEGIN;
INSERT INTO users (name, email) VALUES ('New User', '[email protected]');
INSERT INTO orders (user_id, amount) VALUES (LASTVAL(), 1000.00);
COMMIT;

-- Stored procedure
CREATE OR REPLACE FUNCTION get_user_order_total(user_id_param INTEGER)
RETURNS DECIMAL AS $$
BEGIN
    RETURN (
        SELECT COALESCE(SUM(amount), 0)
        FROM orders 
        WHERE user_id = user_id_param
    );
END;
$$ LANGUAGE plpgsql;

-- Partitioning
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- Connection pool configuration
-- postgresql.conf
max_connections = 100
shared_buffers = 256MB
effective_cache_size = 1GB