A history.get API request without time_from can scan all chunks of a history table. This query keeps AccessShareLock on all these chunks until it finishes. Because of this, TimescaleDB compression cannot get AccessExclusiveLock for compress_chunk, so the compression job waits.

Similar bug reports but not exactly applicable:
ZBX-21538 - issue was about drop_chunks and is not related. Not ZBX-26372 neither.

Forum thread with the same problem:
https://www.zabbix.com/forum/zabbix-help/513485-too-many-long-running-queries-in-zabbix-pg-select-h-from-history

Issue:

API call - same as the official documentation example in https://www.zabbix.com/documentation/7.0/en/manual/api/reference/history/get

    {
      "jsonrpc": "2.0",
      "method": "history.get",
      "params": {
        "output": "extend",
        "history": 0,
        "itemids": "26342018,26389356,...",
        "sortfield": "clock",
        "sortorder": "DESC",
        "limit": 23
      },
      "id": 1
    }

Resulting SQL:

    SELECT h.* FROM history_uint h
    WHERE h.itemid IN (<itemids>)
      AND h.clock >= <history retention lower bound>
    ORDER BY h.clock DESC
    LIMIT 23;

When time_from is missing, Zabbix starts from the oldest kept history data. In this case, the retention window still covers all retained chunks, so TimescaleDB cannot skip old chunks. Worst case: the requested itemids have no rows. Then LIMIT 23 cannot stop the query early, because PostgreSQL must check every chunk to confirm there is nothing to return. Some such executions were logged with execution time around almost 3 hours!

PostgreSQL log:

    2026-05-13 07:38:58.516 UTC [pid=724100] LOG:    process 724100 still waiting for AccessExclusiveLock on relation 167758708 of database 164310223 after 1000.061 ms
    2026-05-13 07:38:58.516 UTC [pid=724100] DETAIL: Processes holding the lock: 290837, 445947, 620727. Wait queue: 724100.
    2026-05-13 07:38:58.516 UTC [pid=724100] CONTEXT:  SQL statement "SELECT public.compress_chunk(chunk_rec.oid)"
                                                       PL/pgSQL function _timescaledb_functions.policy_compression_execute(...)

Processes at the same time:

    PID    STIME STAT TIME    CMD
    724100 09:30      00:07:22 postgres: Columnstore Policy [1010] waiting
    290837 07:00 Ds   22:10    postgres: zabbix-fe zabbix SELECT
    445947 08:00 Ds   13:25    postgres: zabbix-fe zabbix SELECT
    620727 09:00 Ds   05:21    postgres: zabbix-fe zabbix SELECT

After the three SELECT queries were stopped, compression continued. Total wait was 169192.194 ms. All three SELECTs used the same 23 itemids - only the start time was slightly different.

EXPLAIN of the same query:

    Limit  (cost=0.63..17610.16 rows=23 width=20)
      -> Custom Scan (ChunkAppend) on history_uint h  (cost=0.63..5628160.62 rows=7351)
           Order: h.clock DESC
           -> Index Scan ... _hyper_2_424_chunk  (cost=0.63..5628160.62  rows=7351)
           -> Index Scan ... _hyper_2_420_chunk  (cost=0.63..14369195.01 rows=19656)
           ... 6 more Index Scan children, each ~14M cost ...
           -> Custom Scan (ColumnarScan) on _hyper_2_242_chunk  (cost=50.83..501.66 rows=9119)
           ... 6 more ColumnarScan children, each ~500 cost ...
           -> Custom Scan (ColumnarScan) on _hyper_2_131_chunk  (cost=50.87..496.70 rows=9018)
                  Filter: (clock >= 1777420857)

15 chunks in plan: 8 uncompressed Index Scans (5.6M-14M cost each) and 7 ColumnarScans on compressed chunks (~500 cost each). All 15 take AccessShareLock at plan-open and hold it until commit; compression's AccessExclusiveLock waits.

Verified workarounds

• Add time_from, for example now() - 300. The query uses only one recent chunk and finishes in less than 1 ms.
• Use item.get with output=lastvalue for last-value checks - served from the value cache, does not read history_uint.
• Set a statement timeout on the role:

    ALTER ROLE zabbix SET statement_timeout = '30s';

Long SELECT queries are cancelled after 30 seconds. Their locks are released and compression can continue.

Requests

1. Update the history.get documentation and example: recommend time_from on TimescaleDB installations and perhaps recommend item.get with lastvalue for last-value use cases.
2. Warn or validate when history.get uses sortfield=clock, sortorder=DESC and limit without a recent time_from.
3. Consider a statement timeout for API database sessions.