Cheatsheet Observability Part 032 — Database Instrumentation

Fokus part ini: memahami bagaimana database access dari aplikasi Java/JAX-RS harus diinstrumentasi agar query latency, transaction duration, connection pool wait, lock contention, deadlock, slow query, rows affected, ORM/MyBatis behavior, dan PostgreSQL health dapat dikaitkan dengan request, trace, log, metric, deployment, dan business operation. Tujuannya adalah membuat database tidak menjadi black box saat incident production.

1. Core Mental Model

Database instrumentation adalah observability di boundary antara aplikasi dan database.

Dalam service Java/JAX-RS, path umum:

HTTP request
  ↓
JAX-RS resource method
  ↓
Service layer
  ↓
Repository / DAO
  ↓
MyBatis / JPA / JDBC
  ↓
DataSource / connection pool
  ↓
PostgreSQL

Database failure jarang muncul sebagai satu bentuk saja. Symptom bisa terlihat sebagai:

• API latency naik;
• request timeout;
• thread pool penuh;
• connection pool exhausted;
• transaction menggantung;
• lock wait tinggi;
• deadlock;
• slow query;
• CPU database tinggi;
• I/O database tinggi;
• replication lag;
• stale read;
• wrong result due to transaction/isolation issue.

Instrumentation yang baik harus menjawab:

• query apa yang lambat;
• endpoint apa yang memicu query;
• transaction mana yang panjang;
• apakah latency terjadi saat menunggu connection atau saat eksekusi query;
• apakah query blocked oleh lock;
• apakah ORM menghasilkan query tidak terduga;
• apakah parameter atau SQL logging aman;
• apakah query latency berkorelasi dengan deployment, migration, traffic spike, atau data growth.

2. Three Layers of Database Observability

Database observability harus dilihat dari tiga layer.

Incident sering salah diagnosis karena layer ini dicampur.

Contoh:

API latency naik karena DB lambat.

Kemungkinan berbeda:

Aplikasi menunggu connection dari pool, query belum sampai DB.

atau:

Query sampai DB tetapi blocked oleh row lock dari transaction lain.

atau:

Query lambat karena plan berubah setelah data distribution berubah.

Masing-masing butuh mitigation berbeda.

3. JDBC and DataSource Instrumentation

JDBC instrumentation menangkap database calls dari aplikasi.

Yang harus terlihat:

• connection acquisition;
• prepared statement execution;
• query/update/batch call;
• transaction begin/commit/rollback;
• exception SQL state;
• query duration;
• rows affected if available;
• database system;
• database name;
• sanitized statement.

Common instrumentation points:

• OpenTelemetry Java agent JDBC instrumentation;
• DataSource proxy;
• connection pool metrics;
• MyBatis plugin/interceptor;
• Hibernate statistics/instrumentation;
• custom DAO/repository wrapper.

Rule:

Instrument at the lowest common layer when possible, enrich at the domain/repository layer when necessary.

JDBC-level instrumentation is broad. Repository-level instrumentation adds business meaning.

4. Connection Pool Instrumentation

Connection pool is often the real bottleneck.

Important metrics:

connection_pool_active
connection_pool_idle
connection_pool_max
connection_pool_min
connection_pool_pending
connection_pool_acquire_seconds
connection_pool_timeout_total
connection_pool_usage_seconds
connection_pool_creation_total
connection_pool_eviction_total

For HikariCP-like pools, observe:

• active connections;
• idle connections;
• pending threads;
• max pool size;
• connection acquisition time;
• connection timeout count;
• connection lifetime/eviction;
• leak detection if enabled.

Failure mode:

p95 endpoint latency rises
DB CPU normal
PostgreSQL query count normal
connection_pool_pending rises

Likely cause:

• pool too small;
• slow queries holding connections;
• long transactions;
• leaked connections;
• thread concurrency exceeds DB capacity;
• backpressure missing.

Connection pool telemetry must be on the service dashboard, not hidden only in database dashboard.

5. Query Span Design

A database span should represent a database operation, not leak raw sensitive SQL.

Good span name examples:

SELECT quote
UPDATE order
INSERT audit_event
CALL pricing_reconciliation

Avoid span names containing full dynamic SQL with parameters.

Useful attributes:

db.system=postgresql
db.namespace=quote_order
db.operation.name=SELECT
db.collection.name=quote
db.query.summary=SELECT quote by id
db.response.returned_rows=1
error.type=deadlock_detected

Be careful with:

db.statement
sql.parameters
customer_name
email
phone
address
quote_id as metric label
order_id as metric label

Full statement capture can be useful in lower environments or tightly controlled production sampling, but it must follow privacy/security policy.

6. SQL Statement Sanitization

SQL visibility is valuable but dangerous.

Unsafe:

SELECT * FROM customer WHERE email = 'alice@example.com' AND phone = '...'

Safer:

SELECT * FROM customer WHERE email = ? AND phone = ?

Even sanitized SQL can reveal schema and business model, so access control still matters.

Recommended approach:

• capture normalized SQL or query summary;
• avoid parameter values;
• avoid raw user input;
• limit full SQL capture in production;
• use sampling for expensive SQL details;
• keep sensitive tables under stricter policy;
• prefer query name/operation in application metrics.

For MyBatis, beware dynamic SQL. The final SQL can differ greatly from mapper intent.

For JPA/Hibernate, beware generated SQL. A simple repository call may trigger multiple queries.

7. SQL Parameter Privacy

SQL parameters can contain:

• PII;
• credentials;
• account numbers;
• commercial pricing data;
• contract terms;
• quote/order identifiers;
• internal notes;
• authorization-related data.

Default stance:

Do not log SQL parameter values in production.

Possible exceptions must be explicitly approved and controlled:

• temporary debugging window;
• lower environment;
• redacted/masked values;
• restricted access;
• short retention;
• incident-specific approval.

A senior engineer should reject PRs that add raw SQL parameter logging without policy and guardrails.

8. Transaction Span and Transaction Duration

A slow query is not the only database problem. Long transactions can be worse.

Observe:

• transaction start;
• transaction duration;
• commit latency;
• rollback count;
• transaction isolation level if relevant;
• number of queries per transaction;
• lock wait inside transaction;
• external calls inside transaction;
• transaction timeout;
• retry after serialization/deadlock failure.

Anti-pattern:

Begin DB transaction
  ↓
Update quote
  ↓
Call downstream HTTP service
  ↓
Publish message
  ↓
Commit transaction

This can hold locks while waiting for network calls.

Instrumentation should expose:

• transaction duration;
• child query spans;
• external call inside transaction;
• lock wait or timeout;
• rollback reason.

9. MyBatis Instrumentation

MyBatis gives explicit mapper boundaries, which can be useful for observability.

Useful signals:

• mapper namespace;
• statement ID;
• operation type;
• query duration;
• rows returned/affected;
• batch size;
• exception SQL state;
• dynamic SQL complexity;
• N+1-like repeated mapper calls;
• slow mapper method.

Example stable operation name:

QuoteMapper.selectQuoteById
OrderMapper.updateOrderStatus
AuditEventMapper.insertAuditEvent

Avoid metric labels with raw SQL or parameters.

Good labels:

mapper=QuoteMapper
statement=selectQuoteById
operation=SELECT

Potential concerns:

• statement ID cardinality is usually bounded, but verify;
• mapper method may reveal domain model, acceptable internally but access-controlled;
• dynamic SQL can hide expensive query variants;
• batch operations need batch size metrics.

10. JPA/Hibernate Instrumentation

JPA/Hibernate can hide database behavior behind object navigation.

Observe:

• query count per request;
• lazy loading spikes;
• N+1 query pattern;
• flush duration;
• dirty checking cost;
• transaction duration;
• second-level cache hit/miss if used;
• entity load count;
• collection fetch count;
• batch fetch behavior;
• optimistic lock failure.

Common failure:

One endpoint change adds a new JSON field.
That field triggers lazy loading for every item in a list.
Query count jumps from 3 to 503.

Metrics/traces should make this obvious.

Review dashboard/traces for:

• many identical SELECT spans;
• database span count per request;
• request latency dominated by repeated queries;
• Hibernate statistics if enabled.

11. PostgreSQL Span Attributes and Error Classification

PostgreSQL-specific errors should not be collapsed into generic database error.

Useful classifications:

• unique constraint violation;
• foreign key violation;
• not null violation;
• check constraint violation;
• serialization failure;
• deadlock detected;
• lock timeout;
• statement timeout;
• connection failure;
• too many connections;
• read-only transaction violation;
• cancelled query.

In logs/metrics, prefer stable error type rather than raw exception message.

Example:

{
  "event": "database_operation_failed",
  "db_system": "postgresql",
  "operation": "UPDATE",
  "repository": "OrderRepository",
  "error_type": "deadlock_detected",
  "sql_state": "40P01",
  "transaction_id": "...",
  "trace_id": "...",
  "correlation_id": "..."
}

Do not use raw exception message as a metric label.

12. Slow Query Correlation

Slow query debugging requires joining signals.

From application side:

• endpoint route;
• trace ID;
• query span;
• query summary;
• repository/mapper method;
• transaction duration;
• connection acquisition time;
• deployment version.

From PostgreSQL side:

• slow query log;
• pg_stat_statements if available;
• wait events;
• locks;
• deadlocks;
• CPU/I/O;
• rows scanned;
• index usage;
• query plan;
• table/index bloat if relevant;
• autovacuum activity.

Debug flow:

1. Find slow endpoint.
2. Open representative trace.
3. Identify slow DB span.
4. Determine whether time is pool acquisition, query execution, or transaction wait.
5. Map query summary to PostgreSQL slow query/statistics.
6. Check locks/wait events.
7. Check recent deployment/migration/data growth.
8. Compare before/after query plan if available.
9. Mitigate with index, query rewrite, pool/concurrency adjustment, transaction boundary change, or rollback.

13. Lock Wait and Deadlock Observability

Locks are a correctness and latency concern.

Signals:

• lock wait count;
• lock wait duration;
• deadlock count;
• transaction duration;
• blocked query;
• blocking query/session;
• table/index involved;
• endpoint or job causing transaction;
• retry after deadlock/serialization failure.

Application symptoms:

• intermittent latency spikes;
• update endpoint timeout;
• order/quote state transition stuck;
• deadlock exception;
• retry success after delay;
• queue consumer processing slows.

Important:

Deadlock retry may hide correctness issues if not observable.

If deadlock retries are enabled, emit metrics:

database_deadlock_total
database_serialization_retry_total
database_transaction_retry_total

14. Rows Affected and Correctness Signals

Rows affected is a correctness signal, not only a performance detail.

Examples:

UPDATE order SET status='SUBMITTED' WHERE id=? AND status='DRAFT'

Rows affected meanings:

For state transitions, log/audit carefully:

{
  "event": "state_transition_update_result",
  "entity": "order",
  "from_state": "DRAFT",
  "to_state": "SUBMITTED",
  "rows_affected": 0,
  "reason": "stale_state_or_duplicate_request",
  "trace_id": "..."
}

Do not turn every zero-row update into ERROR. Classify based on domain semantics.

15. Metrics Design for Database Access

Recommended application-side metrics:

db_client_operation_duration_seconds
db_client_operations_total
db_client_errors_total
db_client_connection_acquire_seconds
db_client_transaction_duration_seconds
db_client_transaction_rollbacks_total
db_client_deadlocks_total
db_client_lock_timeouts_total
db_client_statement_timeouts_total
db_client_rows_affected

Recommended labels:

service
environment
db_system
db_name_or_namespace
operation
repository_or_mapper
statement_name
error_type

Avoid labels:

raw_sql
sql_parameter
exception_message
user_id
request_id
quote_id
order_id
free_text_filter

Use histograms for latency. Averages hide tail latency.

16. Logging Strategy for Database Operations

Do log:

• slow query above threshold;
• connection acquisition timeout;
• transaction timeout;
• deadlock detected;
• lock timeout;
• rollback due to unexpected error;
• repeated serialization retry exhausted;
• suspicious rows affected;
• migration/runtime schema mismatch;
• database unavailable.

Do not log:

• every successful query in production high-volume path;
• raw SQL parameters;
• full result sets;
• PII values;
• large stack traces repeatedly for same expected constraint violation;
• exception message as unreviewed structured field.

Good slow query log:

{
  "event": "database_slow_operation",
  "db_system": "postgresql",
  "repository": "QuoteRepository",
  "operation": "selectQuoteForSubmission",
  "sql_operation": "SELECT",
  "latency_ms": 1840,
  "threshold_ms": 500,
  "connection_acquire_ms": 8,
  "transaction_active_ms": 2100,
  "rows_returned": 1,
  "trace_id": "...",
  "correlation_id": "..."
}

17. Dashboard Design for Database Instrumentation

Service-level database panel should show:

• DB operation rate;
• DB latency p50/p95/p99;
• DB error rate by type;
• connection pool active/idle/pending;
• connection acquisition latency;
• transaction duration;
• rollback count;
• slow operation count;
• deadlock/lock timeout count;
• top repository/mapper operations by latency;
• DB spans per request;
• deployment marker.

PostgreSQL-level dashboard should show:

• active connections;
• CPU;
• memory;
• disk I/O;
• locks;
• wait events;
• deadlocks;
• slow queries;
• transaction age;
• replication lag if relevant;
• cache hit ratio;
• autovacuum health;
• table/index growth.

Both dashboards are needed. One does not replace the other.

18. Alerting for Database Issues

Possible alerts:

• connection pool pending high;
• connection acquisition timeout spike;
• DB operation latency SLO burn;
• database error rate spike;
• deadlock spike;
• lock timeout spike;
• transaction duration too high;
• rollback rate spike;
• PostgreSQL CPU/I/O saturation;
• too many connections;
• replication lag high;
• disk space low;
• slow query count spike.

Alert quality rule:

Alert should say whether the symptom is application-side pool pressure, query latency, DB server saturation, or correctness failure.

Bad alert:

Database problem detected.

Better alert:

quote-order-service connection pool pending > 20 for 10 minutes, p95 DB acquire latency > 500ms, API p95 affected for POST /orders/{id}/submit.

19. Production Debugging Playbook

When database is suspected:

1. Check endpoint latency and error rate.
2. Open slow/error traces.
3. Identify DB spans and their duration.
4. Separate connection acquisition latency from query execution latency.
5. Check connection pool metrics.
6. Check transaction duration.
7. Check DB error type and SQL state.
8. Check PostgreSQL dashboard: CPU, I/O, locks, wait events.
9. Check slow query logs or query statistics.
10. Check recent deployment or migration.
11. Check traffic spike or batch job overlap.
12. Check queue consumer concurrency if async load increased.
13. Determine if issue is query, pool, transaction, lock, capacity, migration, or data growth.
14. Mitigate safely.

Mitigation examples:

• rollback bad query/deployment;
• disable expensive feature flag;
• reduce consumer concurrency;
• increase pool cautiously;
• kill blocking session only with approval/runbook;
• add index through controlled migration;
• tune statement timeout;
• move external call outside transaction;
• pause batch/reconciliation job.

20. Java/JAX-RS Integration Pattern

Database instrumentation should connect to inbound request context.

JAX-RS request span
  ↓
Service method span/event
  ↓
Repository/mapper operation
  ↓
JDBC query span
  ↓
PostgreSQL execution

Logs and spans should share:

• trace ID;
• correlation ID;
• request ID;
• tenant ID if allowed;
• business key if allowed;
• route template;
• service version.

Repository instrumentation can add domain meaning:

OrderRepository.transitionOrderState
QuoteRepository.loadQuoteForPricing
AuditRepository.insertAuditEvent

JDBC instrumentation can add technical detail:

SELECT quote
UPDATE order
INSERT audit_event

Both are useful when not duplicated excessively.

21. Failure Mode Table

22. PR Review Checklist

Ask these questions:

• Does this change add or modify database access?
• Is the query observable by repository/mapper operation?
• Is SQL parameter logging avoided?
• Is the operation name low-cardinality?
• Could this create N+1 queries?
• Could this increase transaction duration?
• Is an external call made inside a DB transaction?
• Are rows affected checked where correctness depends on it?
• Are constraint/deadlock/serialization errors classified?
• Is retry safe and observable?
• Does this affect connection pool pressure?
• Does dashboard need a new panel?
• Does alerting need update?
• Does migration need release observability marker?
• Does audit logging need before/after state?

23. Internal Verification Checklist

Verify in internal CSG/team context:

• JDBC driver and version;
• DataSource implementation;
• connection pool library and metrics;
• MyBatis/JPA/Hibernate usage;
• OpenTelemetry JDBC instrumentation coverage;
• SQL sanitization policy;
• SQL parameter logging policy;
• mapper/repository naming convention;
• PostgreSQL dashboard;
• slow query log access;
• pg_stat_statements or equivalent availability;
• lock/deadlock visibility;
• transaction timeout policy;
• statement timeout policy;
• migration tooling and markers;
• database runbooks;
• incident history involving DB latency/locks/pool exhaustion.

24. Senior Engineer Heuristics

Use these heuristics:

• If DB CPU is normal, do not assume database is healthy; check connection pool and locks.
• If API is slow but DB does not see queries, check pool acquisition and network.
• If query count per request jumps, suspect ORM/lazy loading or looped mapper calls.
• If retries hide deadlocks, still track deadlock count.
• If a transaction spans external calls, expect lock and latency risk.
• If metric labels include raw SQL, expect cost and cardinality risk.
• If logs include SQL parameters, expect privacy/security risk.
• If dashboard has DB server metrics but no application-side DB metrics, debugging will be incomplete.

25. Summary

Database instrumentation turns database access from a black box into explainable production evidence.

A mature Java/JAX-RS service should observe:

• JDBC/DataSource calls;
• connection pool behavior;
• query latency;
• transaction duration;
• MyBatis/JPA/Hibernate behavior;
• PostgreSQL errors and wait symptoms;
• slow query correlation;
• rows affected correctness;
• lock/deadlock signals;
• privacy-safe SQL visibility;
• database dashboards and alerts.

The core production question is:

Is the problem in our query, our pool, our transaction boundary, our ORM behavior, PostgreSQL capacity, lock contention, migration, data growth, or caller traffic pattern?

Good instrumentation makes that answer evidence-driven instead of guess-driven.