# Database overview

_This page explains what a KDB-X database is, the objects it can hold, the forms a persisted table can take, what else a database needs in an enterprise environment, and where to read about each in detail._

A KDB-X database is a **directory of serialized KDB-X objects**. There is no server to install and no separate storage layer: q writes objects to files in its own [native format](../basics/work_with_files/kdb-files.md), and loading the directory makes those objects variables in the q session, queryable exactly like values built in memory.

??? note "If you come from PostgreSQL, MySQL, or SQL Server..."

    In these systems, you connect to a **server** that owns the data and answers on its behalf. In KDB-X, "database" names the **data on disk** — the directory this page describes — and not a running service. The process that loads it and answers queries is an ordinary q process, conventionally called an **HDB** when the data it serves is historical: see [the HDB process](hdb.md). Any number of them can load the same database at once, and none of them owns it.

    The distinction is not unique to KDB-X. Oracle draws the same line between a *database* (the files) and an *instance* (the processes serving them). PostgreSQL keeps three separate words: a *data directory* on disk, a *server* process, and a *database* as a logical container within it. Where a sentence here can be read either way, this documentation says **database directory** or **database root** for the files, and **q process** or **HDB** for the process.

## A database is more than tables

[Tables](../../ref/table.md) are the usual reason to build a database, and the bulk of this section is about them. But any q object can be persisted, and anything in the database root becomes a variable when the database is loaded — vectors, dictionaries, nested lists, keyed tables, and even functions.

This matters in practice, because reference data is often more natural as a dictionary than as a table.

To see it, generate a sample database with the [Datagen module](../../modules/datagen/overview.md):
```q
q)([getInMemoryTables; buildPersistedDB]): use `kx.datagen.capmkts
q)buildPersistedDB["/tmp/testdb"; ([tbls: `trade`quote`daily; start: 2026.04.10; end: 2026.04.11])]
```

Alongside the partitioned `trade` and `quote` tables it writes a flat-file table `master`, the `sym` list, and an `exnames` dictionary mapping each exchange code to its full name. Loading the database brings them all into the root namespace together:
```q
q)\l /tmp/testdb
q)system"v"
`daily`date`exnames`master`quote`sym`trade
```

`exnames` is an ordinary dictionary, not a table:
```q
q)type exnames
99h
q)3#exnames
A| "NYSE American"
B| "NASDAQ OMX BX"
C| "NYSE National"
```

A dictionary (similarly to a list, a q function, etc) can be used directly inside a query, labeling the `ex` column of the partitioned `trade` table:
```q
q)select trades: count i, size: sum size by exch: exnames ex from trade where ex in `N`Q`Z
exch                     | trades size
-------------------------| ------------
"Cboe BZX Exchange"      | 94     5222
"NASDAQ Stock Exchange"  | 224    12313
"New York Stock Exchange"| 77     4414
```

The data is randomly generated, so your own figures differ.

??? note "Why a dictionary rather than a table?"

    Had `exnames` been persisted as a two-column table instead, the lookup could not sit in the `by` clause. The query would need a join against it, and the grouping would move to the joined column — more to write, more to read, and a join that every query wanting exchange names has to repeat and keep correct as the schema changes.

    q treats dictionaries and functions alike, since both are just mappings, so anything you can apply you can apply inside a query. There are further tricks in the same vein. A [step dictionary](../basics/data_structures/dictionaries.md#step-dictionaries) — an ordinary dictionary with the sorted attribute applied — returns the value of the nearest _preceding_ key when a key is absent:
    ```q
    q)buckets: `s#(0D00:00; 0D09:30; 0D12:00; 0D16:00)!`closed`morning`afternoon`after
    q)buckets 0D10:15:00.000
    `morning
    ```

    Being a mapping, it drops into a query exactly as `exnames` did, bucketing trades by trading session — and it does so with no as-of join, which is what matching a "nearest preceding bound" would otherwise require:
    ```q
    q)select trades: count i, size: sum size by bucket: buckets time from trade
    bucket   | trades size
    ---------| ------------
    afternoon| 1454   78188
    morning  | 1088   60518
    ```

Keeping reference data in the database rather than in a script means every process that loads the database sees the same values, versioned with the data they describe.

> **Warning — Non-table objects — and flat-file tables — in the root cost resident memory**
>
>
> A splayed or partitioned table in the root costs nothing at startup, because its columns are [memory-mapped](../basics/work_with_files/kdb-files.md#memory-mapping-and-the-heap) and read only when a query touches them. A flat-file table is not mapped this way, and neither is a dictionary or a symbol vector: each is deserialized onto the heap every time the database is loaded. Keep such objects small, and see [objects in the database root](loading-db.md#objects-in-the-database-root) for what is mapped and what is not.
>
## Forms of a persisted table

Smaller tables can be held in memory, but a table must be persisted once it is large enough, or once it needs to outlive the process. How to serialize it depends on its size and how it is queried.

| **Serialization**  | **Representation**                                                      | **Recommended use case**                                    |
|--------------------|-------------------------------------------------------------------------|-------------------------------------------------------------|
| flat-file table    | single binary file                                                      | small table, and most queries use most columns              |
| splayed table      | directory of column files                                               | up to 100 million rows                                      |
| partitioned table  | table partitioned by e.g. date, with a splayed table for each partition | more than 100 million records; or growing steadily          |
| segmented database | partitioned tables distributed across disks                             | tables larger than disks; or you need to parallelize access |

### Flat-file table

q can [serialize and file](../basics/work_with_files/kdb-files.md) any object as a single binary file, the simplest way to persist a table.

A database with tables `trades` and `quotes`:
```treeview
db/
├── quotes
└── trades
```

> **Tip — You can also export the table in other formats (for example, .csv, .xls) if needed with [save](../../ref/save.md).**
>
>
### Splayed table

A table is [splayed](https://en.wiktionary.org/wiki/splay "Wiktionary") by storing each of its columns as a single file. The table is stored as a directory.
```treeview
db/
├── quotes/
|   ├── .d
|   ├── time
|   ├── sym
|   └── price
└── trades/
    ├── .d
    ├── time
    ├── sym
    ├── price
    └── vol
```

With a [splayed table](splayed-tables.md), each column file is only read into memory when a query requires it.

Consider splaying a table if most queries on a reasonably sized table do not need all the columns.

### Partitioned table

The records of a [partitioned table](partition.md) are distributed across multiple partition directories within its root directory, one per distinct value of a **partition domain** — conventionally a date, though [other domains](#the-partition-domain) work too. Nothing enforces that a row actually belongs in the partition holding it: which directory a row lands in when the table is written is entirely up to whatever wrote it, not something kdb-x checks.
```treeview
db/
├── 2020.10.03/
│   ├── quotes/
│   │   ├── price
│   │   ├── sym
│   │   └── time
│   └── trades/
│       ├── price
│       ├── sym
│       ├── time
│       └── vol
├── 2020.10.05/
│   ├── quotes/
│   │   ├── price
│   │   ├── sym
│   │   └── time
│   └── trades/
│       ├── price
│       ├── sym
│       ├── time
│       └── vol
└── sym
```

The partition directory is named for its partition value and contains a splayed table. By convention its records share that value, but — as noted above — nothing enforces the relationship; see [the partition domain](#the-partition-domain).

Consider partitioning a table if any of the following conditions apply:

- It grows over time.
- It contains more than 100 million records.
- It includes columns that exceed the maximum object size allowed in memory.

### Segmented database

The root directory of a [segmented database](segment.md) contains only two files:

- `par.txt`: a text file listing the paths to the segments
- the sym file for enumerated symbol columns

Segments are stored outside the root, usually on various volumes. Each segment contains partitioned tables.
```tree
DISK 0             DISK 1                     DISK 2
db/                db/                       db/
├── par.txt        ├── 2020.10.03/           ├── 2020.10.04/
└── sym            │   ├── quotes/           │   ├── quotes/
                   │   │   ├── .d            │   │   ├── .d
                   │   │   ├── price         │   │   ├── price
                   │   │   ├── sym           │   │   ├── sym
                   │   │   └── time          │   │   └── time
                   │   └── trades/           │   └── trades/
                   │       ├── .d            │       ├── .d
                   │       ├── price         │       ├── price
                   │       ├── sym           │       ├── sym
                   │       ├── time          │       ├── time
                   │       └── vol           │       └── vol
                   ├── 2020.10.05/           ├── 2020.10.06/
                   │   ├── quotes/           │   ├── quotes/
                   ..                        ..
```

Consider segmenting a table across multiple storage devices if any of the following conditions apply:

- The table exceeds the capacity of a single storage device.
- You need to parallelize access to the table.

Dividing a table across storage devices allows you to:

- Store very large tables that exceed single-device capacity.
- Parallelize I/O across devices — a query against a partitioned table already fans out across partitions regardless of segmenting, but a single device serializes that work, where separate devices don't.
- Isolate updates to specific partitions, avoiding rewrites of the entire table.

## A database mixes all of these

A KDB-X database typically contains many tables of different types. Flat, splayed, and partitioned tables can reside nicely next to each other. Flat and splayed tables are in the root directory together with other KDB-X objects (like lists and dictionaries).

The sample database [generated above](#a-database-is-more-than-tables) is exactly such a mix. Review its file structure:
```treeview
/tmp/testdb
├── 2026.04.10
│   ├── quote
│   │   ├── .d
│   │   ├── asize
│   │   ├── ask
│   │   ├── bid
│   │   ├── bsize
│   │   ├── ex
│   │   ├── mode
│   │   ├── sym
│   │   └── time
│   └── trade
│       ├── .d
│       ├── cond
│       ├── ex
│       ├── price
│       ├── size
│       ├── stop
│       ├── sym
│       └── time
├── daily
│   ├── .d
│   ├── close
│   ├── date
│   ├── high
│   ├── low
│   ├── open
│   ├── price
│   ├── size
│   └── sym
├── exnames
├── master
└── sym
```

Where:

- **`exnames`** stores a dictionary
- **`master`** is a flat-file table
- **`sym`** is the symbol list
- **`daily`** is a splayed table
- **`trade`** and **`quote`** are partitioned tables

Loading the database (for example, with `\l`) makes every one of these a variable in the root namespace. They don't all cost the same, though: `daily`, `trade`, and `quote` are memory-mapped and cost nothing at load time, while `exnames`, `master`, and `sym` are read onto the heap. See [objects in the database root](loading-db.md#objects-in-the-database-root) for the full breakdown.

What makes a directory a splayed table is its [`.d` file](splayed-tables.md), listing the columns. A directory without one is still loaded, as a **dictionary** keyed by the filenames inside it, and a subdirectory becomes a nested dictionary — so a tree of files arrives as a dictionary of dictionaries, indexable at depth. This is a tidy way to group reference data under one name, and worth knowing before leaving anything in a subdirectory of a database that you did not mean to publish, since the whole tree arrives as a variable.
```q
q)refdata          / db/refdata/ holds the file labels and the subdirectory limits/
labels| `low`mid`high
limits| `s#`hard`soft!20 10
q)refdata[`limits;`hard]
20
```

## The partition domain

Dates are the usual way to partition a table, but they are not the only one. q supports four **partition domains** — `date`, `month`, `year`, and `int` — and the choice is recorded nowhere in the database: it is implied entirely by the names of the partition directories, which is why each must start with a digit.

q reads the domain off the **length** of the first partition directory's name:

| Domain  | Example directory | Name length   | Virtual column    |
|---------|-------------------|---------------|-------------------|
| `date`  | `2026.01.01`      | 10            | `date`, type `d`  |
| `month` | `2026.01`         | 7             | `month`, type `m` |
| `year`  | `2026`            | 4             | `year`, type `j`  |
| `int`   | `1`, `20261`      | anything else | `int`, type `j`   |

The domain then appears as a **virtual column**, named after the domain itself and prepended to every partitioned table in the database. It costs no storage — the value comes from the directory name:
```q
q)meta t
c   | t f a
----| -----
date| d
id  | j
px  | f
```

`int` is the general case, and the reason for partitioning by something other than time: any bucketing that can be reduced to an integer works, and q treats a name of any other length as one.

??? note "Avoid int partition names that are 4, 7, or 10 digits long"

    The domain is inferred purely from the name length of the first partition, as sorted by name in alphanumeric order, so an `int` partition name that happens to land on 4, 7, or 10 digits is misread as `year`, `month`, or `date` instead. Partitioning by week number is a common way to trip this — the week number itself is small, but a day count derived from it is not:
    ```q
    q)floor .z.p%7*1D
    1394
    ```

    Pick a name length that cannot collide with the other domains — for example, zero-pad short integers to a length outside 4, 7, and 10. If the first partition (alphanumerically) is known to always have a colliding length, for example a single digit, zero-padding the rest is unnecessary.

Three utilities report what q decided: [`.Q.pf`](../../ref/dotq.md#pf-partition-field) gives the domain, [`.Q.PV`](../../ref/dotq.md#pv-partition-values) the partition values found on disk, and [`.Q.pt`](../../ref/dotq.md#pt-partitioned-tables) the tables that are partitioned.
```q
q).Q.pf
`date
q).Q.PV
2026.01.01 2026.01.02
q).Q.pt
`s#,`t
```

> **Warning — Every partition must fit the domain inferred from the first**
>
>
> Because the inference looks only at one directory name, a database whose partitions are not all the same shape is read against the wrong domain and fails to load. Here database `mixed` has one partition named `2026` (4 digits) and another named `2026.01.01` (10 digits):
>
> ```treeview
> mixed/
> ├── 2026/
> │   └── t/
> └── 2026.01.01/
>     └── t/
> ```
>
> The first directory is taken as a year, and `2026.01.01` cannot be read as one:
>
> ```q
> q)\l mixed
> 'part
> ```
>
> A directory whose name does not begin with a digit is not a partition at all. It is loaded as an ordinary root entry, and — if nothing else in the root looks like a partition — the database is not partitioned, leaving `.Q.pf` and `.Q.PV` undefined rather than signaling an error.
>
## Staging files and directories are ignored

Everything described so far is loaded. One class of entry is deliberately skipped: a name ending in a **dollar sign** marks a q staging entry. The new version of something is written alongside the original under a `$` name and renamed over it once it is complete, which is how a sym file, for example, can be updated without a reader ever seeing a half-written one.

This applies to **files and directories alike**, and a `$`-suffixed directory is skipped whatever it contains — a whole splayed table sitting beside the live one is passed over rather than loaded:
```treeview
db/
├── trade/          (loaded)
│   ├── .d
│   ├── id
│   └── px
├── sym$            (skipped — a file)
└── trade.v2$/      (skipped — a directory, with its own .d)
    ├── .d
    ├── id
    └── px
```
```q
q).Q.lo[`:db;0;0]
q)\v
,`trade
```

No `sym`, and no `trade.v2$`. The suffix is the only thing that hides an entry: a directory without a `.d` is loaded as a [dictionary](#a-database-is-more-than-tables), and a leading dot does not exempt one either. That makes `$` the right marker for anything you are staging or keeping for rollback, since the loader neither names it nor maps what is inside it.

> **Warning — Only in the database root**
>
>
> Inside a partition directory every entry is expected to be a table, and neither a `$` suffix nor a leading dot exempts it. A staging directory left in a partition breaks the load:
>
> ```q
> q)\l pdb
> 't.v2$
> ```
>
> So versioned or in-progress directories for a partitioned table belong in the database root, not in the partitions. See [atomicity, integrity, and durability](atomicity.md) for the pattern.
>
## The sym file

Splaying a table requires its symbol columns to be **enumerated**: each distinct symbol is stored once in a `sym` file at the database root, and the column holds indexes into it. Because symbols repeat heavily in a historical database, this saves both space and comparison time.

[`.Q.en`](../../ref/dotq.md#en-enumerate-varchar-cols) does the enumeration, and [`dsave`](../../ref/dsave.md) and [`.Q.dpft`](../../ref/dotq.md#dpft-save-table) call it for you. See [manage sym files](symfiles.md) for how to populate, verify, migrate, and compact one.

## Running a database in an enterprise environment

Choosing the form of each table makes a database work. In an enterprise environment, a database also has to serve many users, keep costs down, protect the data, and stay consistent as it changes. Each of the following pages in this section covers one of these requirements:

- **[Relationships between tables](relationships/overview.md).** Foreign keys and link columns declare a relationship between two tables once, in the database, so that queries do not have to repeat the same join.
- **[Loading a database](loading-db.md).** A database is served by a q process, called an HDB (historical database) process, that loads the directory and answers queries. How loading maps the data into memory determines startup time, memory use, and how fast the first queries run.
- **[Compression](file-compression.md).** Historical data grows every day, and compression reduces the disk space it needs. The tradeoff is query time: decompression costs CPU, and whether a query gets slower or faster depends on how fast your storage is.
- **[Storage tiering](tiering.md).** Recent data is queried far more often than old data. Tiering keeps recent partitions on fast, expensive storage and moves older ones to slower, cheaper storage, without changing how you query them.
- **[Encryption](dare.md).** Security policies often require that data on disk is unreadable to anyone without the key. Data-at-rest encryption protects the database files without any change to queries.
- **[Query audit](query-audit.md).** Regulators and security teams need a record of which queries the database served, and when. The same log shows which queries are slow or use the most memory, the queries that put the stability of a shared process at risk.
- **[Handling concurrent queries](concurrency.md).** A q process serves one query at a time, so one expensive query delays every other user. Serving many users means running several HDB processes against the same database and spreading queries across them.
- **[Atomicity and integrity](atomicity.md).** q writes a database one file at a time, with no transactions, so an interrupted write can leave readers with errors or wrong data. Staging a write and publishing it in one step keeps readers from ever seeing a partial one.
- **[Maintenance](maintenance.md).** Schemas change after data is written. Maintenance covers adding, renaming, and removing columns and tables across every partition, and filling gaps where a table is missing from a partition.
- **[Working with sym files](symfiles.md).** Every symbol column in the database depends on the sym file, so losing or corrupting it breaks every table. This page covers how to inspect, migrate, and compact the sym file, and how to keep it from growing out of hand.

## Next steps

- Persist a single object, including a flat-file table, with [serializing as an object](../basics/work_with_files/kdb-files.md).
- Store one file per column with [splayed tables](splayed-tables.md).
- Spread a table across directories with [partitioned tables](partition.md) and across disks with [segmented databases](segment.md).
- Understand the format itself — the file header, mapped lists, and what loading actually maps — in [the KDB-X file format](../basics/work_with_files/kdb-files.md).
