PostgreSQL Idle in Transaction: Núdzový Playbook pre Zaseknuté Spojenia

Idle in transaction je pomaly unik, ktory potopi stabilne systemy. “Tabuľka má 50GB ale len 1M riadkov.” Skontrolujem pg_stat_activity: jedno spojenie je “idle in transaction” 6 hodín. Autovacuum nemohol vyčistiť mŕtve tuples.

Idle in transaction je tichý zabijak PostgreSQL výkonu.

Testované na: PostgreSQL 16.1, Spring Boot aplikácie, connection pools

Čo je Idle in Transaction?

Stavy Transakcií

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

-- idle                   -- Spojenie otvorené, žiadna aktívna transakcia
-- active                 -- Aktuálne vykonáva query
-- idle in transaction    -- Transakcia otvorená, čaká na ďalší príkaz ← PROBLÉM
-- idle in transaction (aborted) -- Transakcia zlyhala, čaká na rollback

Prečo Je To Zlé

Idle in Transaction:
1. Drží zámky na tabuľkách/riadkoch
2. Bráni autovacuum vyčistiť mŕtve tuples
3. Drží transaction ID (ovplyvňuje wraparound)
4. Blokuje ostatné transakcie čakajúce na zámky

Timeline:
T+0:    BEGIN; SELECT ... (transakcia začína)
T+1:    Aplikácia spracováva... (transakcia otvorená)
T+10m:  Stále spracováva... (10 min idle in transaction)
T+10m:  Autovacuum skúša tabuľku → BLOKOVANÝ
T+2h:   Table bloat rastie...
T+6h:   Tabuľka 10x normálnej veľkosti

Detekcia

Nájdi Dlhobežiace Transakcie

-- Spojenia idle in transaction > 5 minút
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query,
    now() - xact_start AS transaction_duration,
    now() - state_change AS idle_duration
FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND now() - xact_start > interval '5 minutes'
ORDER BY xact_start;

Skontroluj Lock Blocking

-- Kto koho blokuje
SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    blocking.state AS blocking_state,
    now() - blocking.xact_start AS blocking_duration
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.relation = blocking_locks.relation
    AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted
AND blocking.state = 'idle in transaction';

Skontroluj Dopad na Autovacuum

-- Tabuľky čakajúce na vacuum
SELECT
    schemaname,
    relname,
    n_dead_tup,
    last_autovacuum,
    last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 10;

Núdzová Reakcia

Krok 1: Identifikuj a Zdokumentuj

-- Získaj plné detaily idle transakcie
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    backend_start,
    xact_start,
    query_start,
    state_change,
    state,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

-- Ulož tieto informácie pred zabitím!

Krok 2: Ukonči Spojenie

-- Jemné: Zruš aktuálny query (nefunguje pre idle)
SELECT pg_cancel_backend(12345);

-- Silné: Ukonči spojenie
SELECT pg_terminate_backend(12345);

-- Ukonči všetky idle in transaction > 1 hodina
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND now() - xact_start > interval '1 hour';

Krok 3: Uprac

-- Spusti vacuum na postihnutých tabuľkách
VACUUM ANALYZE affected_table;

-- Skontroluj či bloat klesol
SELECT
    schemaname,
    relname,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'affected_table';

Prevencia

1. idle_in_transaction_session_timeout

-- postgresql.conf (PostgreSQL 9.6+)
idle_in_transaction_session_timeout = '10min'

-- Alebo per-role
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '10min';

-- Po timeoutne: spojenie automaticky ukončené
-- Aplikácia dostane: FATAL: terminating connection due to idle-in-transaction timeout

2. statement_timeout

-- Maximálny čas vykonávania query
statement_timeout = '30s'

-- Per-role
ALTER ROLE app_user SET statement_timeout = '30s';

-- Zabraňuje dlhobežiacim queries, ale nie idle in transaction

3. Opravy na Úrovni Aplikácie

// Spring Boot - Connection timeout
@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties("spring.datasource.hikari")
    public HikariConfig hikariConfig() {
        HikariConfig config = new HikariConfig();

        // Connection max lifetime
        config.setMaxLifetime(1800000);  // 30 min

        // Leak detection
        config.setLeakDetectionThreshold(60000);  // 1 min warning

        return config;
    }
}

// Správne spracovanie transakcií
@Transactional
public void processOrder(Order order) {
    // ZLE: Externé volanie vnútri transakcie
    // paymentService.charge(order);  // HTTP call drží transakciu!

    // DOBRE: Externé volanie mimo transakcie
}

public void processOrderCorrect(Order order) {
    Order saved = saveOrder(order);  // Transakcia končí tu
    paymentService.charge(saved);    // Mimo transakcie
}

4. PgBouncer Konfigurácia

# pgbouncer.ini
[pgbouncer]
# Zabi spojenia idle in transaction
server_idle_timeout = 600  # 10 min

# Použi transaction pooling (uvoľní spojenie po každej transakcii)
pool_mode = transaction

# So statement poolingom (najagresívnejšie, ale rozbije niektoré apps)
# pool_mode = statement

Monitoring

Prometheus Alert Rules

groups:
- name: postgresql
  rules:
  - alert: IdleInTransactionLong
    expr: |
      pg_stat_activity_max_tx_duration{state="idle in transaction"}
      > 300
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "Spojenie idle in transaction > 5 minút"

  - alert: IdleInTransactionCritical
    expr: |
      pg_stat_activity_max_tx_duration{state="idle in transaction"}
      > 3600
    for: 5m
    labels:
      severity: critical
    annotations:
      summary: "Spojenie idle in transaction > 1 hodina - blokuje vacuum"

Custom Metrika

-- Export ako Prometheus metrika
SELECT
    coalesce(max(extract(epoch from (now() - xact_start))), 0) AS max_idle_tx_seconds
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Grafana Dashboard

{
  "panels": [
    {
      "title": "Idle in Transaction Spojenia",
      "type": "timeseries",
      "targets": [
        {
          "expr": "pg_stat_activity_count{state='idle in transaction'}",
          "legendFormat": "count"
        }
      ]
    },
    {
      "title": "Najdlhšie Idle in Transaction",
      "type": "stat",
      "targets": [
        {
          "expr": "pg_stat_activity_max_tx_duration{state='idle in transaction'}"
        }
      ]
    }
  ]
}

Vzory Root Cause

1. Externé API Volania v Transakcii

// ZLE: HTTP volanie drží transakciu otvorenú
@Transactional
public void process(Order order) {
    orderRepository.save(order);
    externalApi.notify(order);  // 30 sekundový timeout = 30s idle in tx
}

// DOBRE: Presuň externé volanie von
@Transactional
public Order save(Order order) {
    return orderRepository.save(order);
}

public void process(Order order) {
    Order saved = save(order);
    externalApi.notify(saved);
}

2. User Input v Transakcii

// ZLE: Čakanie na usera v transakcii
@Transactional
public void wizard(WizardRequest request) {
    stepOne();
    waitForUserInput();  // User ide na obed...
    stepTwo();
}

// DOBRE: Separátne transakcie
public void wizardStepOne() { ... }
public void wizardStepTwo() { ... }

3. Chýbajúci Rollback pri Chybe

// ZLE: Chyba nerobí rollback
try {
    connection.setAutoCommit(false);
    doStuff();
    connection.commit();
} catch (Exception e) {
    // Chýba: connection.rollback();
    throw e;
}  // Spojenie vrátené do poolu s otvorenou transakciou!

// DOBRE: Vždy rollback pri chybe
try {
    connection.setAutoCommit(false);
    doStuff();
    connection.commit();
} catch (Exception e) {
    connection.rollback();
    throw e;
}

Checklist

## Idle in Transaction Reakcia

### Detekcia
- [ ] Query pg_stat_activity pre idle in transaction
- [ ] Skontroluj lock blocking reťazce
- [ ] Identifikuj postihnuté tabuľky (vacuum blokovaný)

### Okamžitá Reakcia
- [ ] Zdokumentuj detaily spojenia (pid, query, trvanie)
- [ ] Ukonči spojenie ak > 1 hodina
- [ ] Spusti VACUUM na postihnutých tabuľkách

### Prevencia
- [ ] Nastav idle_in_transaction_session_timeout
- [ ] Povoľ HikariCP leak detection
- [ ] Prezri kód pre transakcie držiace externé volania

### Monitoring
- [ ] Alert na idle in transaction > 5 min
- [ ] Sleduj max idle transaction trvanie
- [ ] Monitoruj rast table bloat

Záver

Idle in transaction je tichý zabijak databázy:

1. Nastav idle_in_transaction_session_timeout - Auto-ukončenie zaseknutých spojení
2. Nikdy nevolaj externé API v transakciách - Bežný root cause
3. Monitoruj a alertuj na idle in transaction > 5 minút
4. Maj kill playbook - Vedz ako ukončiť a zotaviť sa

Jedna zabudnutá transakcia môže nafúknuť tvoje tabuľky 10x.

Súvisiace články

• PostgreSQL Autovacuum SLO - Vacuum konfigurácia
• Connection Pool Sizing s Little’s Law - Pool management