Pinned queries

Pin a SELECT so its result is always ready in the result cache - computed when the database opens, never pushed out, computed again after a write.

• Category: HTTP API
• Keywords: pin, pinned query, pinned statement, pins, keep a result ready, warm cache, preload, dashboard slow query, GET /api/pins, POST /api/pins, DELETE /api/pins, _pinned_queries, cache_pinned
• See also: Performance tips (result-cache), Run SQL over HTTP (/exec, /exp), Timers, Indexes (warm), Monitor panel (memory, processor, caches)

A pinned query is a SELECT the server keeps ready. Pin the slow question a dashboard asks on every load, and from then on every request for it is answered from the result cache:

• It is computed when the database opens, right after the indexes are built at open, before anyone asks.
• It is never pushed out of the result cache by other results, and it may take up to three quarters of the cache (an ordinary result is kept only up to a quarter).
• After a write to a table it reads, the server computes it again by itself once the database has been quiet for half a second, so the next request finds it fresh. On a database that is written all the time and is never quiet, it does so at the latest two seconds after the first write that made the result old. A request that comes before that computes it, as any query would.
• Pins are saved in the table _pinned_queries of the hidden _system database (like timers), or of the served database when kdb serve runs on one folder, so they survive restarts and are part of every backup.

The rules of the cache still apply: the statement must be one cacheable SELECT of this database (no now(), no random functions, no other database), and the row limit is part of the key: pin it with the max_rows the requests use (/exec sends 10,000 unless limit= says otherwise; the console sends 10,000).

A pin is saved code that later runs by itself, without a rights check: only a server admin can pin or unpin, and every change is written to the audit log (event pin).

Pins are a feature of kdb serve. A server started without them answers /api/pins with 404.

GET /api/pins

Syntax:

GET /api/pins[?db=<name>]

The pins of one database (the default database without db=). Needs read rights on the database.

now_ms at the top is the server's clock. GET /api/stats shows cache_pinned per database: how many pinned results are in the cache now.

POST /api/pins

Syntax:

POST /api/pins[?db=<name>]
Content-Type: application/json

{"sql": "SELECT ...", "max_rows": 10000, "enabled": true}

Pins a statement, or replaces the pin with the same SQL. Returns the pin.

A statement that is not one SELECT, does not parse, or reads another database is a 400 that says so. So is a max_rows out of range.

HTTP: Pin the totals a dashboard shows on every load

Setup:

CREATE TABLE trades (id LONG PRIMARY KEY, symbol SYMBOL, qty LONG, price DOUBLE);
INSERT INTO trades VALUES (1, 'CITY', 100, 510.5), (2, 'CITY', 50, 512), (3, 'VOLT', 200, 301);

POST /api/pins
Content-Type: application/json

{"sql": "SELECT symbol, sum(qty) AS qty, sum(qty * price) AS amount FROM trades GROUP BY symbol ORDER BY symbol"}
###
GET /api/pins

Response:

POST /api/pins

HTTP 200 (application/json)

{
  "database": "demo",
  "sql": "SELECT symbol, sum(qty) AS qty, sum(qty * price) AS amount FROM trades GROUP BY symbol ORDER BY symbol",
  "max_rows": 10000,
  "enabled": true,
  "created_ms": 1792647859769,
  "created_by": "",
  "open": true,
  "cached": false,
  "cached_bytes": 0,
  "last_run_ms": 1792647859769,
  "last_ok": true,
  "last_error": "",
  "error": ""
}

GET /api/pins

HTTP 200 (application/json)

{
  "now_ms": 1792647859811,
  "pins": [
    {
      "database": "demo",
      "sql": "SELECT symbol, sum(qty) AS qty, sum(qty * price) AS amount FROM trades GROUP BY symbol ORDER BY symbol",
      "max_rows": 10000,
      "enabled": true,
      "created_ms": 1792647859769,
      "created_by": "",
      "open": true,
      "cached": false,
      "cached_bytes": 0,
      "last_run_ms": 1792647859769,
      "last_ok": true,
      "last_error": "",
      "error": ""
    }
  ]
}

HTTP: Only one SELECT can be pinned

POST /api/pins
Content-Type: application/json

{"sql": "DELETE FROM trades"}

Response:

HTTP 400 (application/json)

{"error": "a pinned statement is one SELECT"}

DELETE /api/pins

Syntax:

DELETE /api/pins[?db=<name>]
Content-Type: application/json

{"sql": "SELECT ..."}

Removes the pin with that SQL (also ?sql=<url-encoded SQL>). Returns {"deleted": true}. Its cached result stays until the cache needs the room, like any other. An unknown pin is a 404; a request without the SQL is a 400.

HTTP: Unpin it

Setup:

CREATE TABLE trades (id LONG PRIMARY KEY, symbol SYMBOL, qty LONG);

POST /api/pins
Content-Type: application/json

{"sql": "SELECT symbol, sum(qty) FROM trades GROUP BY symbol"}
###
DELETE /api/pins
Content-Type: application/json

{"sql": "SELECT symbol, sum(qty) FROM trades GROUP BY symbol"}

Response:

POST /api/pins

HTTP 200 (application/json)

{
  "database": "demo",
  "sql": "SELECT symbol, sum(qty) FROM trades GROUP BY symbol",
  "max_rows": 10000,
  "enabled": true,
  "created_ms": 1792647859798,
  "created_by": "",
  "open": true,
  "cached": false,
  "cached_bytes": 0,
  "last_run_ms": 1792647859799,
  "last_ok": true,
  "last_error": "",
  "error": ""
}

DELETE /api/pins

HTTP 200 (application/json)

{"deleted": true}