# Catatan Seekor Postgresql

## Overview

PostgreSQL adalah sistem manajemen basis data relasional (RDBMS) open-source yang powerful dan extensible. Dikembangkan oleh Michael Stonebraker dan Eugene Wong di UC Berkeley, PostgreSQL dikenal karena fitur-fiturnya yang advanced, compliance dengan standar SQL, dan kemampuan untuk menangani beban kerja yang kompleks.

## Sejarah dan Perkembangan

* **1986**: Project INGRES dimulai di UC Berkeley
* **1994**: PostgreSQL 4.0 dirilis sebagai open source
* **1996**: PostgreSQL Global Development Group dibentuk
* **2005**: PostgreSQL 8.0 dengan fitur Windows native
* **2010**: PostgreSQL 9.0 dengan streaming replication
* **2016**: PostgreSQL 9.6 dengan parallel query execution
* **2020**: PostgreSQL 13 dengan performance improvements
* **2023**: PostgreSQL 16 dengan logical replication enhancements

## Karakteristik Utama

* **ACID Compliance** - Atomicity, Consistency, Isolation, Durability
* **Extensible** - Custom data types, operators, functions
* **Advanced Features** - JSON support, full-text search, geospatial data
* **High Performance** - Advanced indexing, query optimization
* **Scalability** - Partitioning, parallel processing, clustering
* **Open Source** - Free to use, modify, and distribute

## Fitur Utama

### Data Types

```sql
-- Basic types
CREATE TABLE example_types (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    salary DECIMAL(10,2),
    is_active BOOLEAN,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data JSONB,
    location POINT
);

-- Custom types
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended');
CREATE TYPE address AS (
    street VARCHAR(100),
    city VARCHAR(50),
    country VARCHAR(50)
);
```

### Advanced Indexing

```sql
-- B-tree index (default)
CREATE INDEX idx_users_email ON users(email);

-- Hash index for equality comparisons
CREATE INDEX idx_users_id_hash ON users USING hash(id);

-- GiST index for geometric data
CREATE INDEX idx_locations_geom ON locations USING gist(geom);

-- GIN index for full-text search
CREATE INDEX idx_documents_content ON documents USING gin(to_tsvector('english', content));

-- BRIN index for large tables
CREATE INDEX idx_logs_timestamp ON logs USING brin(timestamp);

-- Partial index
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

-- Composite index
CREATE INDEX idx_users_name_email ON users(last_name, first_name, email);
```

### JSON Support

```sql
-- JSONB operations
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    attributes JSONB
);

-- Insert JSON data
INSERT INTO products (name, attributes) VALUES (
    'Laptop',
    '{"brand": "Dell", "ram": "16GB", "storage": "512GB SSD", "price": 999.99}'
);

-- Query JSON data
SELECT name, attributes->>'brand' as brand, 
       (attributes->>'price')::numeric as price
FROM products 
WHERE attributes @> '{"brand": "Dell"}';

-- Update JSON data
UPDATE products 
SET attributes = jsonb_set(attributes, '{price}', '899.99')
WHERE id = 1;
```

### Full-Text Search

```sql
-- Create full-text search index
CREATE INDEX idx_documents_fts ON documents 
USING gin(to_tsvector('english', title || ' ' || content));

-- Search with ranking
SELECT title, 
       ts_rank(to_tsvector('english', title || ' ' || content), query) as rank
FROM documents, to_tsquery('english', 'postgresql & database') query
WHERE to_tsvector('english', title || ' ' || content) @@ query
ORDER BY rank DESC;
```

### Geospatial Data (PostGIS)

```sql
-- Enable PostGIS extension
CREATE EXTENSION postgis;

-- Create table with geometry
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(POINT, 4326)
);

-- Insert spatial data
INSERT INTO locations (name, geom) VALUES 
('Office', ST_GeomFromText('POINT(-122.4194 37.7749)', 4326));

-- Spatial queries
SELECT name, ST_Distance(geom, ST_GeomFromText('POINT(-122.4194 37.7749)', 4326)) as distance
FROM locations
WHERE ST_DWithin(geom, ST_GeomFromText('POINT(-122.4194 37.7749)', 4326), 1000);
```

## Performance Optimization

### Query Optimization

```sql
-- Analyze table statistics
ANALYZE users;

-- Explain query execution plan
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM users WHERE email = 'user@example.com';

-- Use prepared statements
PREPARE user_query(text) AS 
SELECT * FROM users WHERE email = $1;

EXECUTE user_query('user@example.com');

-- Optimize slow queries
-- 1. Add appropriate indexes
-- 2. Use LIMIT for large result sets
-- 3. Avoid SELECT * when possible
-- 4. Use appropriate data types
```

### Partitioning

```sql
-- Create partitioned table
CREATE TABLE logs (
    id SERIAL,
    timestamp TIMESTAMP,
    message TEXT
) PARTITION BY RANGE (timestamp);

-- Create partitions
CREATE TABLE logs_2024_01 PARTITION OF logs
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE logs_2024_02 PARTITION OF logs
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Automatic partitioning with pg_partman
CREATE EXTENSION pg_partman;
SELECT create_parent('logs', 'timestamp', 'time', 'monthly');
```

### Connection Pooling

```sql
-- Configure connection pooling with pgBouncer
[databases]
* = host=localhost port=5432

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
```

## Replication dan High Availability

### Streaming Replication

```sql
-- Primary server configuration (postgresql.conf)
wal_level = replica
max_wal_senders = 3
max_replication_slots = 3

-- Standby server configuration (recovery.conf)
standby_mode = 'on'
primary_conninfo = 'host=primary_host port=5432 user=repl password=repl_password'
trigger_file = '/tmp/postgresql.trigger'
```

### Logical Replication

```sql
-- Create publication on primary
CREATE PUBLICATION pub_users FOR TABLE users, profiles;

-- Create subscription on replica
CREATE SUBSCRIPTION sub_users 
CONNECTION 'host=primary_host port=5432 dbname=mydb user=repl password=repl_password'
PUBLICATION pub_users;
```

## Monitoring dan Maintenance

### Performance Monitoring

```sql
-- Check active connections
SELECT count(*) as active_connections 
FROM pg_stat_activity 
WHERE state = 'active';

-- Check table sizes
SELECT schemaname, tablename, 
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables 
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes 
ORDER BY idx_scan DESC;

-- Check slow queries
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements 
ORDER BY mean_time DESC 
LIMIT 10;
```

### Maintenance Tasks

```sql
-- Vacuum tables
VACUUM ANALYZE users;

-- Reindex tables
REINDEX TABLE users;

-- Update statistics
ANALYZE;

-- Clean up old WAL files
-- Configure in postgresql.conf
wal_keep_segments = 32
archive_cleanup_command = 'pg_archivecleanup /var/lib/postgresql/archive %r'
```

## Security

### Authentication dan Authorization

```sql
-- Create user
CREATE USER app_user WITH PASSWORD 'secure_password';

-- Grant permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO app_user;
GRANT USAGE ON SEQUENCE users_id_seq TO app_user;

-- Row Level Security
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_policy ON users
    FOR ALL
    TO app_user
    USING (id = current_setting('app.user_id')::integer);

-- Encrypt sensitive data
CREATE EXTENSION pgcrypto;

UPDATE users 
SET password_hash = crypt('new_password', gen_salt('bf'))
WHERE id = 1;
```

### SSL Configuration

```sql
-- Enable SSL in postgresql.conf
ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'
ssl_ca_file = '/etc/ssl/certs/ca.crt'

-- Require SSL for connections
-- In pg_hba.conf
hostssl all all 0.0.0.0/0 md5
```

## Backup dan Recovery

### Logical Backup

```bash
# Create logical backup
pg_dump -h localhost -U postgres -d mydb > backup.sql

# Restore logical backup
psql -h localhost -U postgres -d mydb < backup.sql

# Backup specific tables
pg_dump -h localhost -U postgres -d mydb -t users -t profiles > users_backup.sql
```

### Physical Backup

```bash
# Create physical backup with pg_basebackup
pg_basebackup -h localhost -U repl -D /backup/postgresql -Ft -z -P

# Point-in-time recovery
# 1. Restore base backup
# 2. Configure recovery.conf
# 3. Start recovery process
```

## Docker dan Deployment

### Docker Compose

```yaml
version: '3.8'
services:
  postgres:
    image: postgres:15
    environment:
      POSTGRES_DB: mydb
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: password
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    command: postgres -c shared_preload_libraries=pg_stat_statements

  pgadmin:
    image: dpage/pgadmin4
    environment:
      PGADMIN_DEFAULT_EMAIL: admin@example.com
      PGADMIN_DEFAULT_PASSWORD: admin
    ports:
      - "5050:80"
    depends_on:
      - postgres

volumes:
  postgres_data:
```

### Kubernetes Deployment

```yaml
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: postgres
spec:
  serviceName: postgres
  replicas: 3
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
      - name: postgres
        image: postgres:15
        env:
        - name: POSTGRES_DB
          value: mydb
        - name: POSTGRES_PASSWORD
          valueFrom:
            secretKeyRef:
              name: postgres-secret
              key: password
        ports:
        - containerPort: 5432
        volumeMounts:
        - name: postgres-storage
          mountPath: /var/lib/postgresql/data
  volumeClaimTemplates:
  - metadata:
      name: postgres-storage
    spec:
      accessModes: ["ReadWriteOnce"]
      resources:
        requests:
          storage: 10Gi
```

## Best Practices

### 1. Database Design

```sql
-- Use appropriate data types
-- Use constraints for data integrity
-- Normalize data appropriately
-- Use meaningful names for tables and columns

-- Example of good design
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Add check constraints
ALTER TABLE users ADD CONSTRAINT check_email_format 
CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
```

### 2. Performance

```sql
-- Use indexes strategically
-- Monitor query performance
-- Use connection pooling
-- Implement caching strategies
-- Regular maintenance tasks
```

### 3. Security

```sql
-- Use least privilege principle
-- Encrypt sensitive data
-- Regular security updates
-- Monitor access logs
-- Use SSL/TLS connections
```

## Referensi

### Dokumentasi Resmi

* [PostgreSQL Official Documentation](https://www.postgresql.org/docs/) - Dokumentasi lengkap
* [PostgreSQL Tutorial](https://www.postgresqltutorial.com/) - Tutorial interaktif
* [PostgreSQL Wiki](https://wiki.postgresql.org/) - Wiki komunitas
* [PostgreSQL Downloads](https://www.postgresql.org/download/) - Download berbagai platform

### Tutorial dan Artikel

* [Automatically cleaning up archived WAL files on a PostgreSQL server](https://www.claudiokuenzler.com/blog/740/automatically-cleaning-up-archived-wal-files-on-postgresql)
* [Change Data Capture (Postgres) \[Materialize\]](https://materialize.com/docs/guides/cdc-postgres/)
* [Change Data Capture(CDC) in PostgreSQL \[Medium\]](https://medium.com/@ramesh.esl/change-data-capture-cdc-in-postgresql-7dee2d467d1b)
* [Faster JSON Generation with PostgreSQL](https://hashrocket.com/blog/posts/faster-json-generation-with-postgresql)
* [PostgreSQL SQL Tricks](https://postgres.cz/wiki/PostgreSQL_SQL_Tricks)
* [PostgreSQL with Docker-Compose](https://zhao-li.medium.com/getting-started-with-postgresql-using-docker-compose-34d6b808c47c)
* [Searching in a Radius using Postgres](https://johanndutoit.net/searching-in-a-radius-using-postgres/)

### Komunitas dan Forum

* [PostgreSQL Community](https://www.postgresql.org/community/) - Komunitas resmi
* [Stack Overflow PostgreSQL](https://stackoverflow.com/questions/tagged/postgresql) - Q\&A Stack Overflow
* [Reddit r/PostgreSQL](https://www.reddit.com/r/PostgreSQL/) - Subreddit PostgreSQL
* [PostgreSQL Slack](https://postgres-slack.herokuapp.com/) - Slack komunitas

### Tools dan Framework

* [pgAdmin](https://www.pgadmin.org/) - GUI administration tool
* [DBeaver](https://dbeaver.io/) - Universal database tool
* [PostGIS](https://postgis.net/) - Spatial database extension
* [pgBouncer](https://www.pgbouncer.org/) - Connection pooler
* [pg\_partman](https://github.com/pgpartman/pg_partman) - Partitioning extension

### Buku dan Kursus

* **"PostgreSQL: Up and Running"** by Regina Obe and Leo Hsu
* **"PostgreSQL 9.6 High Performance"** by Ibrar Ahmed
* **"Mastering PostgreSQL 13"** by Hans-Jürgen Schönig
* **"PostgreSQL Administration Cookbook"** by Simon Riggs and Gianni Ciolli

## Tips dan Trik

### 1. Performance Tuning

* Monitor slow queries dengan `pg_stat_statements`
* Gunakan `EXPLAIN ANALYZE` untuk query optimization
* Implementasikan connection pooling
* Regular maintenance dengan VACUUM dan ANALYZE

### 2. Monitoring

* Setup monitoring dengan Prometheus dan Grafana
* Monitor connection count, query performance, dan disk usage
* Set up alerting untuk critical metrics
* Regular backup testing dan recovery drills

### 3. Scaling

* Implementasikan read replicas untuk read-heavy workloads
* Gunakan partitioning untuk large tables
* Consider sharding untuk very large datasets
* Use appropriate indexes dan query optimization

PostgreSQL adalah database yang powerful dan feature-rich yang cocok untuk berbagai jenis aplikasi, dari aplikasi sederhana hingga enterprise-level systems. Dengan memahami fitur-fiturnya dan best practices, Anda dapat membangun sistem database yang robust dan performant.