Cheatsheet PostgreSQL

Mental model PostgreSQL sebagai relational, transactional, query, storage, indexing, dan operational system untuk Java/JAX-RS backend.

PostgreSQL Architecture Mental Model

Process, memory, WAL, heap, tuple, TOAST, catalog, snapshot, dan storage internals yang memengaruhi behaviour PostgreSQL.

SQL Foundation

SELECT, WHERE, JOIN, aggregation, CTE, window function, LATERAL, UPSERT, RETURNING, dan SQL readability untuk code review.

Enterprise Data Modelling

23 min

Schema, table, key, constraint, normalization, denormalization, audit, soft delete, temporal validity, dan multi-tenant modelling.

CPQ/Order Data Modelling

25 min

Data modelling patterns untuk customer, account, catalog, price, quote, order, approval, fulfillment, snapshot, lifecycle, dan outbox dalam konteks PostgreSQL enterprise.

Transactions and MVCC

ACID, transaction lifecycle, MVCC, snapshot, tuple visibility, isolation level, serialization failure, retry strategy, dan transaction boundary pada Java/JAX-RS service.

Locking and Concurrency Control

Table lock, row lock, predicate lock, advisory lock, SELECT FOR UPDATE, SKIP LOCKED, NOWAIT, optimistic/pessimistic locking, unique constraint race, timeout, deadlock diagnosis, dan review checklist untuk Java/JAX-RS enterprise services.

Indexing Fundamentals

B-tree, Hash, GIN, GiST, SP-GiST, BRIN, unique, composite, covering, partial, expression index, index-only scan, selectivity, cardinality, leftmost prefix, write overhead, bloat, dan index review checklist untuk Java/JAX-RS enterprise systems.

Query Planner and EXPLAIN

Query planner, statistics, cost model, scan/join strategy, EXPLAIN, EXPLAIN ANALYZE, buffers, timing, estimate vs actual rows, plan regression, prepared statement plan behavior, dan query tuning workflow untuk Java/JAX-RS enterprise systems.

Query Performance Patterns

Pagination, offset vs keyset, cursor, N+1 query, batch fetching, large IN clause, EXISTS vs IN, JOIN vs subquery, CTE materialization, aggregation, sorting, temp file, work memory, hot row, hot index, timeout, dan performance PR checklist untuk Java/JAX-RS enterprise systems.

PostgreSQL JSON and JSONB

JSON vs JSONB, hybrid relational/document model, JSON operators, JSON path, GIN index, expression index, schema governance, partial update, performance pitfalls, dan review checklist untuk Java/JAX-RS + MyBatis enterprise systems.

PostgreSQL Full-Text Search

Full-text search mental model, tsvector, tsquery, ranking, weighting, highlighting, dictionaries, language configuration, GIN/GiST index, search relevance, filters, pagination, dan trade-off vs external search engine.

Key-Value and Flexible Attribute Patterns

24 min

Key-value modelling, EAV anti-pattern, JSONB attribute bag, relational attribute table, configuration table, sparse attributes, dynamic forms, indexing, validation, migration, reporting impact, dan technical debt boundary untuk enterprise Java/JAX-RS + MyBatis systems.

Time-Series and Temporal Data

Timestamp correctness, UTC policy, effective date, valid-from/valid-to, temporal validity, event time vs processing time, audit timestamp, time-series table pattern, partitioning by time, BRIN index, retention, archival, rollup, materialized view, timezone bugs, dan temporal PR review checklist untuk PostgreSQL-backed Java/JAX-RS systems.

Analytical and OLAP-Oriented Patterns

OLTP vs OLAP, reporting query, read model, denormalized table, materialized view, refresh strategy, incremental aggregation, window function for analytics, star schema-lite, large history table, partition pruning, dan production impact analytical query pada PostgreSQL-backed Java/JAX-RS systems.

Partitioning and Large Table Management

Declarative partitioning, range/list/hash partition, partition key, partition pruning, local index, global uniqueness limitation awareness, default partition, maintenance, attach/detach, retention, archival, large table migration, vacuum, planner behaviour, anti-pattern, dan review checklist untuk PostgreSQL production tables.

PostgreSQL Functions

27 min

SQL function, PL/pgSQL function, volatility, SECURITY DEFINER, parameter, scalar/table/set-returning return, function in index, performance, testing, versioning, dan integrasi Java/MyBatis untuk enterprise backend.

PostgreSQL Procedures

23 min

Procedure vs function, CALL, transaction control, parameters, operational/batch procedure, migration helper, Java/MyBatis integration, error handling, logging, observability, testing, anti-pattern, dan internal policy checklist.

Triggers and Database-Side Automation

Trigger mental model, BEFORE/AFTER/INSTEAD OF triggers, row/statement-level behavior, audit, validation, outbox, derived data, hidden side effects, debugging, testing, and production review checklist for Java/JAX-RS systems.

PL/pgSQL Deep Dive

PL/pgSQL block structure, variables, control flow, loops, cursors, exception handling, dynamic SQL, diagnostics, temporary tables, transaction nuance, testing, debugging, coding standards, and anti-patterns for production PostgreSQL systems.

Views and Materialized Views

View, updatable view, security barrier view, materialized view, refresh strategy, dependency, contract, indexing, stale data, migration risk, and production usage in Java/JAX-RS systems.

JDBC Lifecycle

PostgreSQL JDBC lifecycle from driver, DataSource, Connection, PreparedStatement, CallableStatement, ResultSet, transaction boundary, autocommit, batch, fetch size, generated keys, SQLState, SQLException, type mapping, streaming, and resource closing in Java/JAX-RS systems.

Connection Pooling

23 min

Connection pool mental model for PostgreSQL-backed Java/JAX-RS services: HikariCP, pool sizing, timeout, leak detection, max lifetime, pool exhaustion, connection storm, PgBouncer, Kubernetes replicas, and PostgreSQL max_connections.

MyBatis Fundamentals

16 min

MyBatis mental model for PostgreSQL-backed Java/JAX-RS systems: mapper interface, mapper XML, SqlSession, SqlSessionFactory, ResultMap, parameter mapping, TypeHandler, dynamic SQL, MyBatis vs JPA/Hibernate, mapper organization, SQL readability, and mapper testing.

MyBatis Mapping and Dynamic SQL

Advanced MyBatis mapping and dynamic SQL for PostgreSQL-backed Java/JAX-RS services: ResultMap, association, collection, constructor mapping, discriminator, TypeHandler, JSONB, enum, dynamic WHERE, ORDER BY, pagination, bulk operation, and SQL injection safety.

MyBatis Transactions, Isolation, Locking, and Error Handling

Transaction integration, isolation, propagation, PostgreSQL locking queries, serialization/deadlock retry, SQLState mapping, domain vs technical errors, batch executor behavior, partial failure, and rollback discipline in MyBatis-backed Java/JAX-RS services.

MyBatis with PostgreSQL Functions, Procedures, Triggers, and PL/pgSQL

Calling PostgreSQL functions and procedures from MyBatis, mapping return values, using CallableStatement, understanding trigger side effects, database-side logic versioning, transaction boundaries, migration ordering, testing, and observability gaps.

Database Migration Fundamentals

Schema and data migration fundamentals for PostgreSQL-backed Java/JAX-RS systems: versioned and repeatable migrations, baseline, checksum, ordering, idempotency, rollback, roll-forward, expand-contract, zero-downtime deployment, CI/CD, GitOps, and review discipline.

Liquibase

Liquibase mental model, changelog, changeset, preconditions, rollback, context, label, checksum, generated SQL review, lock table, object migration, large migration safety, CI/CD, GitOps, PostgreSQL, and enterprise Java/JAX-RS integration.

Flyway

Flyway mental model, versioned migration, repeatable migration, baseline, validate, repair, checksum, naming convention, SQL migration, Java migration, PostgreSQL object migration, large migration safety, CI/CD, GitOps, and production anti-patterns.

Data Migration and Backfill

Production-safe online/offline data migration, large table backfill, chunking, batching, lock avoidance, throttling, resumability, idempotency, checkpointing, validation, reconciliation, rollback strategy, and runbook discipline for PostgreSQL-backed Java/JAX-RS systems.

Outbox, Inbox, CDC, and Event Consistency

Transactional outbox, inbox pattern, polling and CDC-based publishing, PostgreSQL logical decoding, replication slots, WAL impact, Debezium, Kafka integration, at-least-once delivery, idempotent consumers, duplicate/out-of-order events, replay, reconciliation, and event schema governance.

Replication, Logical Decoding, and High Availability

Physical replication, streaming replication, logical replication, publications, subscriptions, replication slots, WAL retention, lag, read replicas, failover, promotion, timelines, split-brain risk, synchronous/asynchronous replication, cloud-managed HA, on-prem HA, and replication monitoring for enterprise Java/JAX-RS systems.

Backup, Restore, PITR, and Disaster Recovery

Backup strategy, logical and physical backup, WAL archive, point-in-time recovery, restore drills, RPO, RTO, snapshots, cloud snapshots, cross-region backup, encryption, retention, validation, disaster recovery planning, restore runbooks, and data-loss scenarios for PostgreSQL-backed enterprise Java/JAX-RS systems.

Vacuum, Autovacuum, Bloat, and Maintenance

16 min

Production-oriented guide to VACUUM, autovacuum, ANALYZE, freeze, transaction ID wraparound, dead tuples, table/index bloat, visibility map, long transaction impact, reindexing, maintenance windows, and bloat diagnosis for enterprise Java/JAX-RS systems.

PostgreSQL Security

15 min

Production-oriented guide to PostgreSQL roles, users, privileges, schema permissions, default privileges, row-level security, column-level security, SECURITY DEFINER, search_path risk, TLS, secrets, service accounts, migration accounts, and least-privilege review for Java/JAX-RS systems.

Data Privacy, Audit, and Compliance

Production-oriented guide to PII handling, sensitive data classification, minimization, encryption, tokenization, masking, audit tables/triggers, change history, retention, deletion, log redaction, access traceability, and compliance evidence for PostgreSQL-backed Java/JAX-RS systems.

PostgreSQL Observability

Production-oriented guide to PostgreSQL observability using slow query logs, pg_stat_activity, pg_stat_statements, pg_locks, pg_stat_user_tables, pg_stat_user_indexes, wait events, connection metrics, temp files, WAL, replication lag, autovacuum, disk usage, dashboards, and alerting strategy.

PostgreSQL Performance and Capacity Planning

25 min

Production-oriented guide to PostgreSQL performance and capacity planning: CPU, memory, IO, connection, lock, WAL, checkpoint, cache pressure, PostgreSQL memory parameters, load testing, read replicas, scale-up, and sharding considerations for enterprise Java/JAX-RS systems.

PostgreSQL in Java/JAX-RS Architecture

Architecture guide for integrating PostgreSQL with Java/JAX-RS services: service-layer transaction boundaries, MyBatis mapper boundary, DAO/repository design, DTO vs persistence model, idempotency keys, optimistic locking, pagination, streaming, database-driven state machines, and HTTP error mapping.

PostgreSQL in Microservices

Production-oriented guide for using PostgreSQL in microservice architectures: database-per-service, schema ownership, shared database anti-patterns, cross-service querying, read models, eventual consistency, saga, compensation, reference data, migration coordination, and operational review checklists.

PostgreSQL in Kubernetes

Production-oriented guide for PostgreSQL as a Kubernetes stateful workload: StatefulSet, PV/PVC, StorageClass, operators, backup, scheduling, probes, secrets, network policies, resource limits, failure modes, and managed database trade-offs.

PostgreSQL on AWS

Production-oriented guide for running PostgreSQL-backed Java/JAX-RS systems on AWS using Amazon RDS for PostgreSQL, Aurora PostgreSQL-compatible, RDS Proxy, VPC networking, backups, replicas, observability, upgrades, and incident-aware operations.

PostgreSQL on Azure

Production-oriented guide for Azure Database for PostgreSQL Flexible Server in Java/JAX-RS systems: server parameters, networking, private endpoints, firewall rules, identity, Key Vault, Azure Monitor, Query Store, backups, HA, read replicas, storage, upgrades, and failure modes.

PostgreSQL On-Prem and Hybrid Deployment

Production-oriented guide for running PostgreSQL in self-managed, on-prem, air-gapped, and hybrid environments, with focus on operational responsibility, OS/storage/networking, HA, backup, monitoring, upgrades, Java/JAX-RS impact, and runbook discipline.

PostgreSQL Extensions and Ecosystem

Production-oriented guide to PostgreSQL extensions and ecosystem dependencies, including pg_stat_statements, uuid-ossp, pgcrypto, citext, btree_gin, btree_gist, pg_trgm, unaccent, PostGIS, TimescaleDB, pgvector, extension governance, cloud/on-prem portability, migration, and security review.

PostgreSQL Troubleshooting Playbook

31 min

Production-oriented PostgreSQL troubleshooting playbook for slow query, blocking query, deadlock, serialization failure, connection exhaustion, pool exhaustion, high CPU, high memory, high IO, disk full, WAL growth, replication lag, autovacuum pressure, bloat, long transaction, failed migration, bad query plan, missing index, and corrupted data from application bugs.

Production Runbook and Incident Response

Production runbook for PostgreSQL incident response in enterprise Java/JAX-RS systems, covering alert triage, severity, customer impact, RPO/RTO, emergency query kill, emergency index creation, emergency rollback, failover, bad migration response, data repair, RCA, corrective/preventive action, post-incident backlog, and communication with DBA/SRE/product/customer-facing teams.

PR Review and Architecture Decision Checklist