DolphinDB Field Notes #1 | Database & Table Management: Don't Learn These the Hard Way

For developers new to DolphinDB, the scripting syntax can feel oddly familiar — it borrows enough from SQL and Python to give you the confidence of "I've got this". But DolphinDB is an analytical database purpose-built for time-series data at scale, and its approach to database and table management follows its own design philosophy — one that differs from traditional databases in ways that matter.

To help you avoid the hard-learned lessons, we've compiled the most common pain points developers run into, covering database and table management, SQL usage, scripting syntax, cluster operations, and other practical features.

This first installment focuses on Database & Table Management. Whether you're losing your mind trying to rename a database or wrestling with primary key configuration — this one's for you.

Rule #1: The Immutable Laws

In DolphinDB, some decisions are permanent. Save yourself the operational headaches and get these right from the start.

Database names cannot be changed. Once a distributed database is created, its name is locked in. Choose wisely — you don't want to regret it after the data's already flowing in.

Partition schemes cannot be modified. The partition scheme is the skeleton of your database. Once set, it's fixed — and every table under that database inherits it. A few things to keep in mind:

• Partition depth: Minimum 1 level, maximum 3 levels.
• Range partitioning: Can only be extended forward, not auto-scaled. If your data grows, use addRangePartitions to manually extend the range. Watch out: with the default allowMissingPartitions=true , data outside the partition range is silently dropped rather than throwing an error. Set it to false if you want failures to be explicit.
• Value partitioning: Supports automatic expansion — but don't go overboard. Initializing too many empty partitions upfront wastes resources.

Your Ops Toolkit: Getting a Clear Picture of Your Cluster

One of the worst feelings in ops is flying blind — not knowing what databases or tables are even in your cluster. DolphinDB has you covered with a set of ready-to-use functions.

Cluster-Wide Overview

• getClusterDFSDatabases() — List all databases in the cluster.
• getClusterDFSTables() — List all tables in the cluster.
• getTables — List all tables within a specific database.

getClusterDFSDatabases()
getClusterDFSTables()
getTables(database("dfs://stock_lv2_snapshot"))

Reverse-Engineer Your Schema

Want to know how a database or table was originally created? The getDatabaseDDL and getDBTableDDL functions in the ops library will reconstruct the original creation SQL for you.

use ops
getDatabaseDDL("dfs://stock_lv2_snapshot")
getDBTableDDL("dfs://stock_lv2_snapshot", "snapshot")

Inspecting Database & Table Structure

Use the database function to get a handle on a specific database, then call schema to inspect its structure or that of any table within it.

db = database("dfs://stock_lv2_snapshot")
db.schema()                  // View database structure (partition scheme, partition columns, etc.)

tb = loadTable("dfs://stock_lv2_snapshot", "snapshot")
tb.schema()                  // View table structure (storage engine, partition columns, column definitions, etc.)
tb.schema().colDefs          // View column definitions only (name, type, comments, etc.)

Modifying Table Structure: What's Allowed and What's Not

This is where most people trip up. DolphinDB enforces strict rules on schema modifications. Here's what you can and can't do.

Supported Operations

Rename a table — Use renameTable to rename a table within a distributed database.

db = database("dfs://stock_lv2_snapshot")
renameTable(db, "snapshot", "newSnapshot")

Add comments — Use setTableComment and setColumnComment to annotate tables and columns — great for team collaboration.

tb = loadTable("dfs://stock_lv2_snapshot", "snapshot")
addColumn(tb, ["insertTime"], [TIMESTAMP])
// Or using alter syntax
alter table tb add updateTime TIMESTAMP

Drop columns (OLAP engine only) — Use dropColumns! or alter..drop. Partition columns cannot be dropped.

tb = loadTable("dfs://k_minute_level", "k_minute")
dropColumns!(tb, "val")

Rename columns (OLAP engine only) — Use rename! or alter..rename. Partition columns cannot be renamed.

tb = loadTable("dfs://k_minute_level", "k_minute")
rename!(tb, "open", "openPrice")

Change column types (OLAP engine only) — Use replaceColumn!. Partition columns cannot have their type changed.

tb = loadTable("dfs://k_minute_level", "k_minute")
replaceColumn!(tb, "vol", array(LONG, 0, 1))

Hard Limits

A note for those migrating from OLTP databases: In traditional databases, nearly every table has a primary key or auto-increment ID column. This is one of the most significant design differences you'll encounter in DolphinDB.

DolphinDB does not support primary keys in the traditional sense. That said, for CDC (Change Data Capture) scenarios involving primary key tables from OLTP sources, DolphinDB offers a dedicated Primary Key storage engine — but its "primary key" is essentially a deduplication mechanism designed specifically for CDC pipelines, not a general-purpose constraint or a recommended engine for everyday analytical workloads.

For most use cases, assume DolphinDB has no primary key support.

📖 Primary Key storage engine docs: https://docs.dolphindb.com/en/Database/Multi-Model Database/primary_key_storage_eng.html

Edge Case: Working with Small, Static Tables

The problem: You need to store small, rarely-changing datasets — think system config parameters or enum mapping tables. A distributed table is overkill, but an in-memory table won't persist. What do you do?

The answer: Create a dimension table inside your distributed database.

A dimension table is an unpartitioned table within a distributed database. On query, the entire table is loaded into memory — making it a good fit for small, infrequently updated datasets.

db = database("dfs://stock_lv2_snapshot")
colNames = ["param_key", "param_value", "param_flag", "param_desc", "insert_time", "update_time"]
colTypes = [STRING, STRING, STRING, STRING, TIMESTAMP, TIMESTAMP]
tb = table(1:0, colNames, colTypes)

params_cfg = db.createDimensionTable(tb, `params_cfg, sortColumns=`param_key`insert_time)

params_cfg.tableInsert(table(
    ["CONN_TIME_OUT"] as param_key,
    ["60"] as param_value,
    ["1"] as param_flag,
    ["timeout"] as param_desc,
    [now()] as insert_time,
    [now()] as update_time
))

select * from params_cfg

📖 Official docs: https://docs.dolphindb.com/en/Functions/c/createDimensionTable.html

TSDB Engine: The Hidden Trap in sortColumns

When using the TSDB storage engine, sortColumns does more than just sort data — it also drives deduplication: for any rows sharing the same sortKey , only the most recent record is kept.

This leads to a common mistake: don't use primary key or unique index columns from your upstream source as sortColumns.

Why? Those fields are inherently unique — every row produces its own distinct sortKey. Not only does this make deduplication pointless, it causes index bloat and degrades query performance.

📖 For more best practices on database and table creation, see: https://docs.dolphindb.com/en/Tutorials/creating_databases_and_tables.html

Quick Reference