KDB knowledge

Documentation of KDB (KARELA DataBase) for people and for AI assistants (web site: https://amritshr.github.io/kdb-knowledge/): 120 help topics, 25 tutorial chapters and 2292 examples. Every example ran on kdb 0.2.0-dev when this was built (2026-10-22), and its real result is written under it.

KDB is a SQL database for time-series and relational data, made by Amrit Shrestha. One program serves an HTTP/JSON API and a web console (port 9900) and the PostgreSQL wire protocol (port 8832). It is not kdb+/q.

This repository holds documents and AI skills only. It has no source code of KDB.

Give it to an AI

What is here

Start reading

• About KDB: What KDB is, who made it, what you can use it for, how it keeps your data safe, how fast it is and how it is tested.
• Quick start: Get kdb, make a database, create a table, add rows, query them and open the web console.
• SQL basics: How KDB reads SQL text: statements, names, text, numbers, TRUE/FALSE/NULL, comments, errors and reserved words.
• CREATE TABLE: Make a relational or a time-series table, with partitions, sort order, a dedup key, rounded times and computed columns, or make one from a query with CREATE TABLE ... AS.
• SELECT: Choose the columns and values that a query gives back, and the table they come from.
• SAMPLE BY: Put rows into time buckets (1 minute, 15 minutes, 1 hour, 1 day, 1 week, 1 month) and make candles.
• HTTP API overview: How to talk to kdb serve over HTTP - the address, choosing the database, JSON answers, errors, safety rules and limits.
• PostgreSQL protocol (port 8832): Connect to KDB with PostgreSQL tools and drivers - psql, DBeaver, Python, Node, C#, Go - and what works and what does not.
• Performance tips: Make loading and queries fast - memory, threads, batch commits, bulk INSERTs, good table design, time filters, what the column engine takes, zone maps, saved stats, and how to measure.
• Limits: The sizes and counts KDB allows, what happens at each limit, and the features that are not there yet.

Made from the KDB help, which is also served by every KDB server at /help. Do not edit these files here: they are built again from the help on each release.

Copyright (c) 2026 Amrit Shrestha. See NOTICE.md.

All topics

Getting started

• About KDB: What KDB is, who made it, what you can use it for, how it keeps your data safe, how fast it is and how it is tested.
• What's new: Everything added to KDB since 2026-09-12, grouped by what it does for you, with the measured number and the topic that explains it.
• Install packages and the Claude skill: The Windows and Linux install packages, the Docker image, and the skill file that teaches an AI assistant in another project how to use KDB.
• Quick start: Get kdb, make a database, create a table, add rows, query them and open the web console.
• SQL basics: How KDB reads SQL text: statements, names, text, numbers, TRUE/FALSE/NULL, comments, errors and reserved words.
• SQL command groups: DDL, DML, DCL, TCL, DQL: The five groups of SQL statements, every KDB statement in its group, and the role each group needs.
• Database models: relational, hierarchical, network, NoSQL, object: The five classic kinds of database, and how much of each KDB can be - with a picture and a working example for each.
• Sample data: The small made-up stock market dataset that most help examples use, with a few rows of each.

Tutorial

• 1. Install KDB
• 2. Your first database
• 3. Tables
• 4. Writing data
• 5. Queries
• 6. Web console
• 7. Command line and scripts
• 8. HTTP API
• 9. Backup and safety
• 10. Moving from QuestDB
• 11. SQL reference
• 12. Troubleshooting
• 13. Functions, procedures and triggers
• 14. PostgreSQL protocol
• 15. Transactions and accounting
• 16. Users, passwords and security
• 17. Indexes and search
• 18. Content data: companies, reports, news and a forum
• 19. Analysis functions
• 20. Charts and dashboards
• 21. Protection
• 22. PostgreSQL schema compatibility
• 23. PostgreSQL queries and data changes
• 24. Versions and upgrades
• Known limitations

Databases & folders

• Databases and the data folder: Where KDB keeps databases, how to name them, and CREATE DATABASE, DROP DATABASE, SHOW DATABASES and USE.
• Time travel and branches: SELECT ... AS OF, CREATE DATABASE ... BRANCH OF: Read a table as it was at an earlier time or commit, undo a bad change with one query, and make a full writable copy of a database in a moment, without copying its files.
• Queries across databases: Read a table of another database on the same server as database.table - in SELECT, JOIN, subqueries and INSERT ... SELECT.

Tables

• CREATE TABLE: Make a relational or a time-series table, with partitions, sort order, a dedup key, rounded times and computed columns, or make one from a query with CREATE TABLE ... AS.
• Column types: Every column type, its range, how NULL is kept, and which values it accepts.
• ALTER, RENAME and DROP TABLE: Add, drop, rename and change columns, drop partitions, set a TTL, add and drop rules and keys, turn DEDUP on or off, rename and drop tables.
• SHOW TABLES, COLUMNS, CREATE TABLE: List the tables of a database, the columns of a table, its partitions, and the full CREATE TABLE statement - also with QuestDB's tables() and table_columns().
• Rules: Name a check, the trading hours or the dedup choice of a table once, and KDB applies it to every row written - saying which rule refused a row and why.
• The trash - UNDROP TABLE: DROP TABLE keeps the table hidden for some days; UNDROP TABLE brings it back with its rows and indexes.

Constraints

• Constraints: Rules that keep table data correct: PRIMARY KEY, AUTO, NOT NULL, UNIQUE, DEFAULT, REFERENCES and CHECK.

Writing data

• INSERT: Add rows with INSERT, decide what happens with duplicate keys, get new ids back with RETURNING, and write times correctly.
• UPDATE and DELETE: Change rows with UPDATE, remove rows with DELETE, empty tables with TRUNCATE, get the changed rows back with RETURNING, and fix wrong data safely.
• Loading many rows: Load thousands or millions of rows: multi-row INSERT, SQL files, CSV files (POST /imp, COPY), the line protocol and the C++ InsertRows API, with speed tips.
• Line protocol (POST /write): Load many rows fast with the InfluxDB line format over HTTP - one line per row, answered with 204.

Querying

• SELECT: Choose the columns and values that a query gives back, and the table they come from.
• WHERE: Keep only the rows you want, with comparisons, IN, BETWEEN, LIKE, IS NULL and time ranges.
• Operators: Every operator of KDB SQL, which one is done first, and what NULL does to each of them.
• Joins: Put rows of two or more tables side by side: JOIN, LEFT / RIGHT / FULL JOIN, USING, CROSS JOIN, and ASOF / LT / SPLICE JOIN for time series.
• GROUP BY and HAVING: Make one row per group (per symbol, per broker, per account) with totals like count, sum and max, and filter the groups with HAVING.
• PIVOT: Turn the values of one column into columns, like a pivot table in Excel: turnover per symbol with one column per day.
• ORDER BY, LIMIT, OFFSET: Sort the result, and give back only the first rows or one page of rows.
• UNION and subqueries: Put the rows of two queries together, and use one query inside another.
• EXPLAIN: See how KDB will read the tables for a query, without running it; with ANALYZE, run it and see what really happened.

Time-series

• SAMPLE BY: Put rows into time buckets (1 minute, 15 minutes, 1 hour, 1 day, 1 week, 1 month) and make candles.
• LATEST ON: Keep only the newest row of each symbol (or of each group of columns).
• Time and time zones: How KDB stores times (UTC milliseconds), how to write a local time with its offset (like +05:45), the session's time zone (SET TIME ZONE, --timezone), time zone names, and time ranges with IN '...'.
• Trading calendars: Say which days a market trades - its weekdays, how they changed over the years, and its holidays - and ask it in SQL and in timers.
• Prices adjusted for splits and bonus shares: Read a price table as if every split and bonus share had always been there - a chart without the jump on the ex-date - while the table keeps the traded prices.
• Partitions, SORT BY and DEDUP KEY: How PARTITION BY, SORT BY and DEDUP KEY of a time-series table change where rows go, their order, and what happens to duplicates; DROP PARTITION deletes old data fast.
• Candles cookbook: Ready market-data queries: candles from minute data and from trades, daily change, top gainers, volume leaders, 52-week high and low, moving averages and filled gaps.

Functions — math

• Finance and price functions: Money math like Excel (interest, loan EMI, NPV, IRR, XIRR, CAGR), price ticks and pivot points for traders.
• Math functions: Round and cut numbers, take powers, roots and logarithms, divide safely, put values in buckets, and make random numbers.
• Trigonometry and probability: Sine, cosine, tangent and their inverses, hyperbolic functions, and the normal distribution (chance, bell height and inverse).

Functions — text

• Regular expressions: Find, count, cut out and replace text with patterns like \d+ (one or more digits).
• Text functions: Change case, cut, trim, pad, search and join text. A letter of any language (Nepali, Chinese, ...) counts as one character.

Functions — date and time

• Date and time functions: Take a time apart, find the weekday, quarter and week, cut, add and build times, count days or months between times, fiscal years (also Nepal's), and write a time as text.

Functions — conditional and NULL

• Conditional and NULL functions: Choose a value with CASE, replace NULL with coalesce, and turn a value into NULL with nullif.

Functions — conversion

• Conversion functions: Change a value from one type to another with CAST, ::, to_timestamp and to_date.

Functions — aggregate

• Aggregate functions: Make one value from many rows: count, sum, avg, min, max, first, last, median, percentile, vwap, twap, conditional counts and sums, the row of the maximum, and lists of text.

Functions — window and series

• Window and series functions: Look at a series of rows: previous and next values, running totals, moving windows, time windows and ranks, with OVER (...).

Functions — technical indicators

• Momentum indicators: MACD, stochastic, stochastic RSI, Williams %R, CCI, rate of change, momentum and TRIX on candles, with the same results as TA-Lib.
• Technical indicators: Moving averages (SMA, EMA, WMA, DEMA, TEMA, TRIMA, KAMA, HMA, VWMA), RSI and ATR on candles, per symbol, with the same results as TA-Lib.
• Trend indicators: Is the price going up, down or sideways, and how strong is the move? ADX and DI, Aroon, parabolic SAR, Ichimoku, SuperTrend, linear regression and Heikin-Ashi candles.
• Volatility indicators and bands: How much the price moves: true range, ATR in percent, Bollinger bands, Keltner and Donchian channels.
• Volume indicators: Is money flowing in or out? On-balance volume, the A/D line, Chaikin money flow and oscillator, MFI, force index and ease of movement.

Functions — statistics

• Correlation and regression: Do two series move together? corr, covariance, a straight trend line (regr_slope, regr_intercept, regr_r2), and the beta and alpha of a share against the market.
• Returns and risk: Returns, drawdown, volatility, z-score and the risk numbers of an investment: Sharpe, Sortino, Calmar, information ratio, value at risk and expected shortfall.
• Statistics functions: Describe a set of numbers: spread (stddev, variance, mad, iqr, range), shape (skewness, kurtosis), other averages (geomean, harmonic_mean, mode) and exact percentiles.

Functions — JSON and bytes

• Bytes, hex and base64: Store raw bytes (hashes, keys, small images) and turn them into hex or base64 text and back.
• JSON functions: Check, read, build and change JSON values, like company profiles and report numbers.

Functions — security

• Security functions: Hash and check passwords, make SHA-256 hashes, random tokens and random ids (UUIDs), and encrypt data with PostgreSQL's pgcrypto functions.

Views

• Views: Save a query with a name, and select from it like a table.

Saved functions

• Saved functions: Save a formula with a name, and use it in any query like a built-in function.

Stored procedures

• Stored procedures: Save many SQL steps under one name and run them with CALL, as one transaction.
• The procedure language: Variables, IF, WHILE, FOR, RETURN, RAISE, PRINT and cursors inside a procedure or trigger body.
• Calling web services from SQL (http_get, http_post): http_get and http_post call a web address from SQL, a procedure or a timer. Off until an admin lists the hosts they may call.
• Timers: Let the server run a procedure, a function or any SQL by itself at set times, so a table stays up to date.

Triggers

• Triggers: SQL that runs by itself when rows of a table are inserted, updated or deleted.

Transactions

• Transactions: BEGIN, COMMIT and ROLLBACK: save many changes together, or none of them.

Cursors

• Cursors: Run a query once, keep its rows, and read them a few at a time.

Indexes & search

• Indexes: Make WHERE and ORDER BY ... LIMIT fast with an index, and keep values unique with a UNIQUE index.
• Full-text search: Find rows that contain words, rank the best matches first, and mark the found words in bold.

Users & security

• Users and roles: Database users with passwords, and the roles read, write, ddl and admin for every database or for one.
• Table rights - GRANT ON TABLE: Give a user rights on one table (GRANT SELECT ON TABLE prices), on some of its columns (GRANT SELECT (id, name) ON staff) or on some of its rows (CREATE POLICY), or take some rights of the role away (REVOKE UPDATE, DELETE ON TABLE ledger).
• API tokens: Named, revocable secrets for apps - each acts as one user, and DROP TOKEN stops it at once.
• Two-factor login (TOTP): A database user can log in to the console and POST /api/login with a password and a 6-digit code from an authenticator app.
• Keeping kdb serve safe: Keep the server on 127.0.0.1, use users or a token, put HTTPS in front, and pick strong passwords.
• TLS (https and sslmode=require): Encrypt both ports with one certificate - https:// for the HTTP API and the console, sslmode=require for PostgreSQL clients.
• Encryption at rest: CREATE DATABASE x WITH (encryption = on) - every file, log record and backup of it is encrypted; without the master key a copied disk shows nothing.
• Protection - timeouts, limits, safe updates, read-only: Stop slow statements (timeout and cancel), limit the memory of one query and the statements of one user, refuse UPDATE or DELETE without WHERE, and make a database read-only.
• Audit log: KDB writes security events (logins, users, DDL, backups, refused rights) and, for chosen tables, every changed row with who, when, old and new values.

Backup, check, repair, restore

• Troubleshooting a running server: What to look at, and what to change, when the server is slow, readers wait, memory or disk grows, the start is slow, a timer or a backup does not run, or data must come back.
• Backup, check, repair and restore: How KDB keeps your data safe, how to make and test backups, and how to get the data back after damage or a mistake.
• Backups on a schedule: kdb serve makes a verified backup of every database at set times, checks a second copy on another disk, runs your command to copy each backup off the server, and warns when the last good backup is old.
• Versions and upgrades: A new KDB opens the data of an older KDB. An older KDB refuses the data of a newer KDB with "needs a newer KDB" and changes nothing.
• Standby server: a live read-only copy (--standby-of, kdb promote): A second KDB server that copies every database of a primary from its log, a moment behind, and serves reads.

Command line

• The kdb program: What the kdb program is, how it finds your databases, and what its exit codes mean.
• kdb sql and kdb shell: Run SQL from the command line once (kdb sql) or type it at a prompt (kdb shell).
• kdb serve: Start the KDB server - web console, HTTP API, line protocol and PostgreSQL port - on one database or on the whole data folder.
• kdb info, check, repair, dump-file: Look inside a database, find damage, repair it, and check one file.
• kdb user: Add, list and remove the users of the KDB server, set their passwords and roles, from the command line.

HTTP API

• HTTP API overview: How to talk to kdb serve over HTTP - the address, choosing the database, JSON answers, errors, safety rules and limits.
• Run SQL over HTTP (/exec, /exp): Run SQL with GET or POST /exec and get JSON, or download the rows of a SELECT as CSV with /exp.
• Databases over HTTP (/api/databases): List the databases of a server, create a new one, and drop one (its folder moves to _trash).
• Saved queries as URLs (CREATE ENDPOINT): Save a SELECT with named parameters under a name; programs then call GET /v/<name>?param=value and get JSON, without sending SQL.
• Health, info, check and backup over HTTP: Check that the server is alive, read the tables, disk use and health of a database, look for damage, make and list backups, watch deferred trigger queues, and read the server's memory, processor and settings.
• Key-value store (Redis-like): GET, SET, INCR, EXPIRE, DEL, KEYS, MGET and the other Redis string commands, hashes, sets, sorted sets, lists (with BLPOP), SORT and WATCH / MULTI / EXEC over HTTP (POST /kv, one command or a pipeline) and as SQL functions (kv_get, kv_set, kv_incr); PUBLISH / SUBSCRIBE and keyspace events. Keys are kept in memory and in the table _kv.
• Sessions and cursors over HTTP: Keep a transaction open over several HTTP requests with a session, and read a big result a part at a time with a cursor.
• CSV import over HTTP (POST /imp): Send a CSV file to kdb serve and it loads the rows, making the table from the file when it is new - QuestDB's /imp.
• 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.
• Change revisions: GET /api/rev and kdb_rev(): A cheap number per table and per symbol that changes when committed rows change, so a chart polls it and reads its candles again only when they changed.
• Change stream and live subscriptions: changes('t'), GET /api/changes, GET /api/subscribe: Every committed INSERT, UPDATE and DELETE of a table as an ordered stream you can resume from its LSN, in SQL or over HTTP, and queries that push their changed rows.
• Logins, tokens and roles over HTTP: How a program or the console proves who it is - the access token, HTTP Basic, POST /api/login - and the 401, 403 and 429 answers.

Postgres protocol

• PostgreSQL protocol (port 8832): Connect to KDB with PostgreSQL tools and drivers - psql, DBeaver, Python, Node, C#, Go - and what works and what does not.
• PostgreSQL-compatible functions: Functions a PostgreSQL app expects: arrays, sequence numbers (nextval), who you are and what you may do (current_user, has_table_privilege), comments (COMMENT ON), advisory locks and session settings, so app code runs on KDB without changes.
• pg_stat_activity and pg_stat_statements: See who is connected and what runs now (pg_stat_activity, query_activity(), GET /api/sessions), stop a statement or close a connection (pg_cancel_backend, pg_terminate_backend, CANCEL QUERY, DELETE /api/sessions/<pid>), close idle and vanished clients (idle_session_timeout, tcp_keepalives_*), and see the totals of every statement shape (pg_stat_statements).
• LISTEN and NOTIFY: Send a message to every client that listens on a channel when a transaction commits - over the PostgreSQL port (LISTEN) or HTTP (GET /listen, server-sent events).
• Settings - SET, SHOW ALL, ALTER SYSTEM: One list of the server's settings behind pg_settings, SHOW ALL, SHOW PARAMETERS and GET /api/settings; SET for a session, ALTER SYSTEM and pg_reload_conf() for the server.

C API

• C API (kdb.h): Use KDB inside your own program with no server - open a folder, run SQL, read rows as text or native numbers - through the C header include/kdb.h and the kdb_c library.

Web console

• Web console: The browser page of kdb serve - run SQL, see results as a grid or a chart, switch databases, check health, make backups and open this help.
• Dashboard page (many charts on one page): Put many charts on one page, each from its own query, with pickers such as $symbol at the top and an automatic refresh. Dashboards are saved in the database.
• View data page (browse and edit rows): Look at the rows of a table in the console and change them with forms - add, edit and delete - without writing SQL.
• Keep data page (how long tables keep their rows): See every table of a database with how long it keeps its rows, and set "keep only the last N months or years": the older days, months or years are dropped, the newest stay.
• Monitor panel (memory, processor, caches): What the server is doing right now - memory it holds, processor it uses, what each database keeps in memory, deferred trigger queues, and the statements running this second.
• Settings page (data folder, old log, capacity, program files and users): Where the server keeps its data, how to point it at another folder, and who may log in - all from the console.
• Comments and suggestions: How people who use a KDB server send comments, suggestions and problem reports, and how its admins read them.

Geometry & CAD

• Survey, CAD and 3D data: points, drawings, blocks, meshes: How to keep survey points, 2D drawings (lines, arcs, polylines, blocks, layers) and 3D meshes in KDB, load only the part a canvas can see, and do distance, area and volume maths in SQL - with the rule that the camera stays in the client.

Performance tips

• Performance tips: Make loading and queries fast - memory, threads, batch commits, bulk INSERTs, good table design, time filters, what the column engine takes, zone maps, saved stats, and how to measure.

Limits

• Limits: The sizes and counts KDB allows, what happens at each limit, and the features that are not there yet.

Quality

• Test report: How KDB is tested as of 2026-09-23, what each test proves, the real results, and what is not tested yet.
• Benchmark report: How fast KDB is against QuestDB and PostgreSQL on the same PC, and what each engine change of 2026-09-23 gave, including where KDB is still slower and what is not measured.

Data formats

• How KDB stores data: A short tour of the files in a KDB database folder - data files, blocks and checksums, the log, the manifest and backups.