DoltHub Blog - Latest Posts

Finding Bugs Using Join Implication Reasoning

Angela Xie — Mon, 05 Oct 2026 00:00:00 GMT

As the world’s first version-controlled SQL database and with 24.6k (and counting) stars on GitHub, Dolt has received a lot of interest from academic researchers over the years. In the past couple of weeks, I have been writing about the research with Dolt done by the TEST Lab at National University of Singapore. Last week, I wrote about Suyang Zhong’s work on SQLancer++. Today, I will be reviewing “Detecting Join Bugs in Database Engines via Join Implication Reasoning” by Zhaokun Xiang. This paper was published earlier this year as part of Proceedings of the ACM on Management of Data and presented at SIGMOD. Some of y’all may know Zhaokun Xiang as theoristcoder on GitHub – last year, I wrote about the changes made to zero time due to a bug found by Zhaokun. Now, let’s dive into the paper.

Join Implication Reasoning#

One of the biggest reasons to choose relational databases over their non-relational counterparts is the support for complex joins. In order to execute a join query as efficiently as possible, SQL query analyzers apply a series of optimization rules, including filter pushdowns, join reordering, memoization, and cost analysis. Many of these optimization rules have been implemented in go-mysql-server, the SQL query engine that powers Dolt. However, fast doesn’t always mean correct if the rules are not applied properly – after all, piping to /dev/null is fast as hell.

Join implication reasoning (JIR) is a testing technique implemented as an extension of SQLancer that focuses on join correctness. Oftentimes, a join query can be rewritten to use different join types and still produce the same result – this is the motivation for join reordering during query optimization. JIR takes these logically equivalent join queries and uses them as test oracles, making sure that a database actually returns the same result for both queries. The paper outlines six join-equivalence rules as well as proofs for each rule. For example, a left outer join can be rewritten as a combination of an inner join and an anti-join with appropriate null-padding. Another example is that a right outer join is the same as a left outer join with the sides of the join swapped and vice versa.

The implementation of JIR involves generating random join queries represented by abstract syntax trees (ASTs) and randomly generated boolean join predicates (also known as join conditions). More queries are subsequently generated by replacing the join types at each node of the ASTs. Equivalent queries are generated as well using the join-equivalence rules. Each pair of equivalent queries is run against the database and the results are compared.

JIR was implemented in both SQLancer and SQLancer++. While SQLancer was already able to generate joins, JIR extended it by generating joins with subqueries and adding randomly generated join conditions.

To compare the efficacy of JIR, databases were run with both JIR and differential query plan (DQP) extensions of SQLancer or SQLancer++, and the number of bugs found by each method were compared to each other. DQP is an earlier project from the TEST Lab which was published in 2024.

So does it use AI?#

Similar to SQLancer++, JIR uses rule-based generation and does not use a large language model (LLM) or generative neural network, so it does not use “generative AI” in the 2026 colloquial sense of the term. Someone 20 years ago might call it AI, but it’s unlikely that someone today would.

Impact on Dolt#

The paper claims to have found 12 unique bugs in Dolt via JIR and 3 unique bugs via DQP; at the time the paper was written, 10 out of 12 of the JIR-found bugs were fixed and all of the DQP-found bugs were fixed. From my perspective, I’m not able to identify via our issues queue whether a bug was found by JIR or DQP and what criterion for uniqueness was used, but Zhaokun did file 19 bugs between November 2025 and February 2026. As of February 10, 2026, every single bug has been fixed.

The majority of these bugs involved anti-joins. This makes sense since anti-joins often involve subqueries, which were previously not supported for join queries in SQLancer. There were also several bugs involving left joins and a few bugs involving how inner joins were optimized into other join types during join planning.

Correctness in Dolt is something we really value, and we are really appreciative of the work Zhaokun has done to ensure the correctness in joins. As mentioned in the paper, join bugs can be tricky to identify due to the number of optimization rules applied and they can get especially complex when multiple joins are involved.

Are you an academic researcher working on a project with Dolt or another DoltHub database? Join our Discord community – we’d love to hear from you.

Your Replica Doesn't Need to Know Everything

Jason Fulghum — Wed, 23 Sep 2026 00:00:00 GMT

Dolt is a MySQL-compatible SQL database with Git-style version control built in. You can branch, merge, diff, clone, push, and pull your data just like you do with source code. You can also connect Dolt to MySQL’s binlog replication protocol, which opens up several useful architectures between MySQL (or MariaDB) and Dolt.

Replicas tend to be enthusiastic little copy machines. Unless told otherwise, they copy every change they receive. That is exactly what you want for a hot standby, but it can be wasteful when a replica serves a narrower purpose and only needs changes from a particular family of tables.

A Dolt user recently opened a feature request asking us to support MySQL’s REPLICATE_WILD_DO_TABLE and REPLICATE_WILD_IGNORE_TABLE filters. It was a useful, well-scoped request, and we were happy to turn around the wildcard filtering support in less than a week. It shipped in Dolt 2.3.5.

Let’s look at why you might want a filtered replica and then investigate a string of suspicious lunch disappearances. This investigation may be more rigorous than the situation warrants, but that has never stopped a database engineer before.

Why Filter a Replica?#

Traditional replication use cases usually need a complete copy of the source database:

A high-availability replica needs all the data so it can take over if the primary fails.
A disaster-recovery replica needs a complete copy in another availability zone or region.
A read-scaling replica often serves the same application queries as the primary.

Filtered replicas solve a different problem. An analytics replica may only need orders_* and sales_* tables. A service-specific read model may only need tables owned by one application domain. A regional replica may only need tables named for that region. You may also want to exclude high-volume scratch or archive tables that provide no value on the replica.

One of my favorite uses of Dolt’s binlog replication support is adding a Dolt replica to an existing MySQL or MariaDB system. The application keeps using its current primary while Dolt receives selected changes and turns them into versioned Dolt commits. This gives you a queryable history, including diffs between revisions, without requiring a full database migration on day one.

Wildcard Table Filters#

Dolt already supported exact table filters through REPLICATE_DO_TABLE and REPLICATE_IGNORE_TABLE. Dolt 2.3.5 adds the wildcard variants:

REPLICATE_WILD_DO_TABLE replicates row changes from tables that match a pattern and excludes unmatched tables.
REPLICATE_WILD_IGNORE_TABLE replicates row changes normally but skips tables that match a pattern.

Patterns contain a database pattern and a table pattern separated by a period. They use MySQL’s wildcard syntax:

Pattern	Meaning
`%`	Match any sequence of bytes
`_`	Match one byte
`\%`	Match a literal percent sign
`\_`	Match a literal underscore

For example, sales.orders% matches tables in the sales database whose names start with orders. Because _ is a wildcard, a literal underscore must be escaped. The pattern breakroom.case\_% matches case_open and case_closed, but not caseworker_notes.

The Great Office Lunch Investigation#

For this demo, we have a MySQL source server and a Dolt replica. The MySQL server is running on port 11229 with GTIDs, binary logging, and row-format binlogs enabled. If you already have a MySQL source configured, you can skip the setup below, but keep in mind that you’ll need to update the replica configuration below with your existing server’s details. If your source contains existing data, our versioned MySQL replica guide also explains how to warm the replica with a snapshot before starting replication.

Start a Local MySQL Source#

First, create an isolated MySQL data directory. --initialize-insecure gives the local root account no password, which is convenient for this disposable demo and a terrible production security policy.

mkdir mysql-source
cd mysql-source

mysqld --no-defaults \
  --initialize-insecure \
  --datadir="$PWD/data"

Now start MySQL with binary logging, row-format events, GTIDs, and a unique server ID:

mysqld --no-defaults \
  --datadir="$PWD/data" \
  --port=11229 \
  --bind-address=127.0.0.1 \
  --socket="$PWD/mysql.sock" \
  --server-id=41 \
  --log-bin="$PWD/mysql-bin" \
  --binlog-format=ROW \
  --gtid-mode=ON \
  --enforce-gtid-consistency=ON \
  --mysqlx=0 \
  --pid-file="$PWD/mysql.pid" \
  --log-error="$PWD/mysql.err"

The four settings that matter to replication are:

server-id=41 uniquely identifies this source in the replication topology.
log-bin enables the binary log that replicas consume.
binlog-format=ROW records the changed rows instead of replaying DML statements.
gtid-mode and enforce-gtid-consistency enable GTID auto-positioning.

Leave that terminal running. In another terminal, connect to the source over TCP:

mysql --protocol TCP -h 127.0.0.1 -P 11229 -uroot

You can verify the important settings from the MySQL shell:

SHOW VARIABLES
WHERE Variable_Name IN
  ('log_bin', 'binlog_format', 'enforce_gtid_consistency', 'gtid_mode', 'server_id');
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| binlog_format            | ROW   |
| enforce_gtid_consistency | ON    |
| gtid_mode                | ON    |
| log_bin                  | ON    |
| server_id                | 41    |
+--------------------------+-------+

From a MySQL shell connected to the source, create a dedicated replication user:

CREATE USER 'replicator'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';

Start the Dolt Replica#

Next, start a fresh Dolt SQL server for the replica. This demo requires Dolt 2.3.5 or later.

mkdir dolt-replica
cd dolt-replica
dolt init --name "Lunch Detective" --email "detective@example.com"
dolt sql-server --port 11230

In another terminal, connect to the Dolt server with the MySQL client:

mysql --protocol TCP -h 127.0.0.1 -P 11230 -uroot

Configure the MySQL source, install our wildcard filter, and start replication:

SET @@GLOBAL.server_id = 42;

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '127.0.0.1',
  SOURCE_PORT = 11229,
  SOURCE_USER = 'replicator',
  SOURCE_PASSWORD = 'password',
  SOURCE_AUTO_POSITION = 1;

CHANGE REPLICATION FILTER
  REPLICATE_WILD_DO_TABLE = ('breakroom.case\\_%');

START REPLICA;

One restart note: MySQL does not persist filters set with CHANGE REPLICATION FILTER, and Dolt matches that behavior. The connection settings from CHANGE REPLICATION SOURCE persist, but you must apply the wildcard filter again after restarting the Dolt server. For a planned restart, run STOP REPLICA before shutting down, then reapply the filter before running START REPLICA again. This avoids briefly auto-starting replication without the filter in place.

There are two backslashes in the SQL string because one escapes the other in the string literal. The resulting filter pattern contains one backslash, which tells the wildcard matcher to treat the underscore literally.

CHANGE REPLICATION FILTER cannot run while the replica SQL thread is active. If you are changing filters on an existing replica, run STOP REPLICA first, update the filters, and then run START REPLICA again.

We can confirm that both replication and the filter are active:

SHOW REPLICA STATUS\G

The complete output contains more replication state, but these are the fields we care about:

            Replica_IO_Running: Yes
           Replica_SQL_Running: Yes
       Replicate_Wild_Do_Table: breakroom.case\_%
   Replicate_Wild_Ignore_Table:

Now reconnect to the MySQL source and create the world’s most over-engineered breakroom incident-response system:

mysql --protocol TCP -h 127.0.0.1 -P 11229 -uroot

CREATE DATABASE breakroom;
USE breakroom;

CREATE TABLE case_open (
  id INT PRIMARY KEY,
  missing_lunch VARCHAR(100),
  prime_suspect VARCHAR(100)
);

CREATE TABLE case_closed (
  id INT PRIMARY KEY,
  missing_lunch VARCHAR(100),
  resolution VARCHAR(100)
);

CREATE TABLE snack_inventory (
  id INT PRIMARY KEY,
  snack VARCHAR(100),
  quantity INT
);

CREATE TABLE employee_alibis (
  id INT PRIMARY KEY,
  employee VARCHAR(100),
  alibi VARCHAR(200)
);

Next, add a little evidence:

INSERT INTO case_open VALUES
  (1, 'Greek yogurt', 'The new intern'),
  (2, 'Leftover pizza', 'Anyone working past 6 PM');

INSERT INTO case_closed VALUES
  (1, 'Turkey sandwich', 'Owner ate it and forgot');

INSERT INTO snack_inventory VALUES
  (1, 'Sea salt chips', 14),
  (2, 'Emergency chocolate', 0);

INSERT INTO employee_alibis VALUES
  (1, 'Zach', 'In a meeting with twelve witnesses'),
  (2, 'Neil', 'Claims he has never seen a sandwich');

Back on the Dolt replica, all four tables exist:

USE breakroom;
SHOW TABLES;
+---------------------+
| Tables_in_breakroom |
+---------------------+
| case_closed         |
| case_open           |
| employee_alibis     |
| snack_inventory     |
+---------------------+

This is an important detail: replication table filters apply to row events. MySQL sends DDL such as CREATE TABLE in query events, so DDL is not filtered. The replica knows that all four tables exist; it has merely been instructed not to gossip about all of their rows.

Let’s count the rows:

SELECT 'case_open' AS table_name, COUNT(*) AS replicated_rows FROM case_open
UNION ALL
SELECT 'case_closed', COUNT(*) FROM case_closed
UNION ALL
SELECT 'snack_inventory', COUNT(*) FROM snack_inventory
UNION ALL
SELECT 'employee_alibis', COUNT(*) FROM employee_alibis;
+-----------------+-----------------+
| table_name      | replicated_rows |
+-----------------+-----------------+
| case_open       |               2 |
| case_closed     |               1 |
| snack_inventory |               0 |
| employee_alibis |               0 |
+-----------------+-----------------+

The wildcard filter admitted row changes for case_open and case_closed and skipped the other two tables. Neil’s alibi has been filtered out, which may be for the best.

Dolt Version Control#

Because the replica is Dolt, the replicated transactions also create versioned history. By default, Dolt creates a new commit as each source transaction is applied, and the commit message records the source GTID. We can see the latest two replicated transactions in dolt_log:

SELECT LEFT(commit_hash, 8) AS commit_hash, message
FROM dolt_log
LIMIT 2;
+-------------+-------------------------------------------------------------------------+
| commit_hash | message                                                                 |
+-------------+-------------------------------------------------------------------------+
| dp5f0jhp    | Dolt binlog replica commit: GTID 614fa9ba-b5e2-11f1-9b3e-274e157c3285:9 |
| 47e7htpp    | Dolt binlog replica commit: GTID 614fa9ba-b5e2-11f1-9b3e-274e157c3285:8 |
+-------------+-------------------------------------------------------------------------+

Your commit hashes and source UUID will be different, but the messages will identify the source transaction behind each commit. That history is queryable data, not a stack of opaque binlog files.

When investigating a change, we can start with dolt_diff_stat() to see which tables changed between two commits and how many rows were added, deleted, or modified. Here we compare the latest commit with its parent:

SELECT table_name, rows_added, rows_deleted, rows_modified
FROM dolt_diff_stat('HEAD~', 'HEAD');
+-------------+------------+--------------+---------------+
| table_name  | rows_added | rows_deleted | rows_modified |
+-------------+------------+--------------+---------------+
| case_closed |          1 |            0 |             0 |
+-------------+------------+--------------+---------------+

The stats point us to case_closed, where one row was added. Now we can use dolt_diff() with the same two commits to see the exact change:

SELECT diff_type, to_id, to_missing_lunch, to_resolution
FROM dolt_diff('HEAD~', 'HEAD', 'case_closed');
+-----------+-------+------------------+-------------------------+
| diff_type | to_id | to_missing_lunch | to_resolution           |
+-----------+-------+------------------+-------------------------+
| added     |     1 | Turkey sandwich  | Owner ate it and forgot |
+-----------+-------+------------------+-------------------------+

The lunch thief remains at large, but now every case update leaves a versioned trail. We may not have a culprit yet, but sooner or later, somebody is going to get caught bread-handed.

Choosing the Right Filter#

Use a wildcard DO filter when the replica should receive only a named family of tables:

CHANGE REPLICATION FILTER
  REPLICATE_WILD_DO_TABLE = ('sales.orders%');

Use a wildcard IGNORE filter when the replica should receive everything except a named family:

CHANGE REPLICATION FILTER
  REPLICATE_WILD_IGNORE_TABLE = ('sales.scratch%');

Be careful when combining filter types. MySQL-compatible table-filter precedence is:

An exact DO match replicates the row.
An exact IGNORE match skips it.
A wildcard DO match replicates it.
A wildcard IGNORE match skips it.
If any DO rules exist, an unmatched row is skipped; otherwise it is replicated.

The early match wins. In particular, a broad wildcard DO pattern cannot be narrowed with an overlapping wildcard IGNORE pattern, because the wildcard DO rule is checked first. If you want everything except scratch%, use the wildcard IGNORE rule by itself.

One other operational detail worth remembering: filters affect new row events; they do not remove data already present on the replica, so an initial snapshot must be scoped appropriately.

Wrapping Up#

Wildcard binlog filters make it easier to build focused Dolt replicas for analytics, audit history, staged migrations, and service-specific read workloads. You can select whole families of tables without maintaining an ever-growing list of exact table names, while the replicated data still gets the benefits of Dolt’s versioned history.

This feature exists because a user told us about a gap that mattered in their deployment. We love getting reports like that. If you hit a missing feature, compatibility gap, or bug, please open an issue on GitHub or come talk to us on Discord. User bug reports and feature requests help us understand what matters to real systems, and sometimes we can get a fix into your hands before your lunch disappears again.

Home and Pricing Page Redesign

Tim Sehn — Tue, 15 Sep 2026 00:00:00 GMT

Dolt now comes in four flavors: Dolt, Doltgres, DoltLite and Dumbo. Our redesigned homepage and pricing page reflect that expanded lineup. This article walks through the new designs and highlights some of the messages we’re trying to emphasize.

Homepage#

DoltHub started as a data sharing company. We still think DoltHub is great for sharing data but our customers mostly want to run our databases in production instead. Our fastest-growing use case is serving as the backing store for systems where agents make writes. Our new homepage reflects that.

A few things to note about this design: the image of agents hanging out on the branches of a tree growing from a database-shaped pot is not new. But it’s one of my favorite images our designers have ever made. It stays. We also wanted to make sure people knew from the start that we are the version-controlled database company and that our databases are perfect for agents. The new above-the-fold language reflects this.

AGENTS NEED VERSION CONTROL
We Build Databases for Agents
The only databases with version control

We also wanted to highlight our three non-alpha databases above the fold: Dolt, Doltgres, and DoltLite. Dumbo will be included once it sheds the alpha tag.

When we only had Dolt, most new folks visiting the homepage would assume Dolt was a MySQL add-on. Now that Dolt comes in four flavors, it’s clearer from the start that our databases are built from the ground up on novel storage to provide Git-style features. The messaging no longer centers on MySQL.

Clicking on a database in the first section takes you to a card explaining what that database is and when to use it. These cards are my favorite part of the homepage. I think they are the best distillation of what makes each Dolt flavor special.

Also, while you’re down there, throw my baby DoltLite a star. It’s getting mogged by Dolt and Doltgres.

Pricing#

We applied lessons from designing the database cards to the new pricing page.

All of our databases are free and open source. We wanted to make that clear from the pricing page.

We make money in two ways:

Enterprise Support
Services

The pricing page explains our services and links to our Enterprise Support page. I have a few ideas about how we could make paid support a clearer option so come back and refresh the pricing page every once in a while if you are curious.

Conclusion#

How do you like the new homepage and pricing page? Did we accomplish our design goals? Come by our Discord and give us your feedback.

Postgres follows the SQL standard for UPDATE statements, unlike MySQL

Zach Musgrave — Fri, 11 Sep 2026 00:00:00 GMT

Doltgres, the world’s first version-controlled Postgres-compatible database, just hit 1.0, meaning that it’s ready for production use. We want Doltgres to be a drop-in replacement for Postgres so that customers can use the entire ecosystem of Postgres-compatible tools and libraries, or port their existing database application to Doltgres without changing any code. This means getting all the nuanced semantics of Postgres’s behavior correct in our emulation. And we think we’ve done pretty well here — our compatibility tests encompass over two dozen tools and languages.

But Doltgres shares the same SQL engine Dolt uses, which was built to emulate MySQL semantics. For most queries this works fine, but MySQL plays famously fast and loose with the SQL standard, while Postgres takes it much more seriously. And because we take client compatibility very, very seriously, that means that we need an engine that reproduces all of MySQL’s wacky non-standard behavior for Dolt and Postgres’s dignified, correct behavior for Doltgres.

Today’s blog is a case study of one area where the engine’s behavior differs to match the emulation target, and a look under the hood for how we manage these differences internally in our interfaces.

UPDATE with column values from the same row#

This issue was brought to our attention by an early adopter customer: Doltgres had the wrong behavior when an UPDATE statement referenced table columns in its update expressions.

CREATE TABLE t_seq (a int, b int);
INSERT INTO t_seq VALUES (1, 0);
UPDATE t_seq SET a = 2, b = CASE WHEN a = 1 THEN 100 ELSE -1 END;
SELECT a, b FROM t_seq;

The SQL standard says that an UPDATE statement that references column values should use the value from the pre-update row, in all cases. So the SELECT query in the above block should return this:

 a |  b
---+-----
 2 | 100    -- per the SQL standard, every assignment reads the pre-update row

But MySQL doesn’t behave this way for an UPDATE. It ignores the SQL standard and uses the new, updated column values in every UPDATE expression as it executes them one by one, left to right, on each row. So in MySQL, and Dolt, the above select returns this:

 a |  b
---+-----
 2 |  -1    -- the CASE saw the NEW value of a (=2)

And until earlier this week, Doltgres behaved this way too. But that’s wrong, and breaks client expectations for Postgres application developers. We needed to change this behavior in the engine, but only when running in Postgres emulation mode.

How do we do that?

Introducing engine overrides#

During development of Doltgres, we experimented with a lot of different mechanisms to vary the engine’s behavior for Doltgres, either to reflect needed differences for Postgres compatibility or to implement features that MySQL doesn’t have. These include new rules during query analysis, new plan nodes that wrap or otherwise alter existing ones, as well as more hacky fixes like swapping function pointers during program init. For something like this divergence in behavior, there wasn’t an existing extension point in the query engine. We designed the engine to make the database backend swappable, as well as some of the query planning logic. But for something as fundamental as applying updates to a row, we had not bothered to make the behavior pluggable.

Our current approach in this kind of situation is to provide the engine with a set of well-defined behavioral extension points at construction. Unlike the interfaces that define tables, databases, functions, etc. that allow integrators to implement a custom database storage backend, these extension points alter the query-time behavior of the engine itself, independent of the storage backend. They’re currently stored in a struct called EngineOverrides. To solve this particular problem, we introduced the new UpdateExpressionApplier interface at the bottom of the struct.

type EngineOverrides struct {
	// Builder contains functions and variables that can replace, supplement, or override functionality within the builder.
	Builder BuilderOverrides
	// SchemaFormatter is the formatter for schema string creation. If nil, this will format in MySQL's style.
	SchemaFormatter SchemaFormatter
	// Hooks contain various hooks that are called within a statement's lifecycle.
	Hooks ExecutionHooks
	// CostedIndexScanExpressionFilter is used to walk expression trees in order to apply index scans based on
	// filter expressions. Some expressions may need to be modified or skipped in order to properly apply indexes
	// for all integrators.
	CostedIndexScanExpressionFilter ExpressionTreeFilter
	// UpdateExpressionApplier evaluates UPDATE assignments. If nil, the engine uses
	// MySQL's sequential assignment evaluation and IGNORE conversion handling.
	UpdateExpressionApplier UpdateExpressionApplier
}

The new interface looks like this:

// UpdateExpressionApplier evaluates the assignments for a row in an UPDATE statement.
// It does not apply to procedural SET or INSERT ON DUPLICATE KEY UPDATE statements.
type UpdateExpressionApplier interface {
	ApplyRowUpdate(ctx *Context, updateExprs *UpdateExprs, tableSchema Schema, oldRow Row, ignore bool) (Row, error)
}

For MySQL behavior, we have a simple interface that applies updates the same way it always has (matching MySQL, not Postgres). For Doltgres, we implemented a new one that we plug in at engine construction time.

func (UpdateExpressionApplier) ApplyRowUpdate(ctx *sql.Context, updateExprs *sql.UpdateExprs, tableSchema sql.Schema, oldRow sql.Row, _ bool) (sql.Row, error) {
	newRow := oldRow.Copy()
	for _, expr := range updateExprs.ExplicitUpdateExprs() {
		assignment, ok := expr.(*gmsexpression.SetField)
		if !ok {
			return nil, fmt.Errorf("UPDATE: expected SetField, found %T", expr)
		}
		// SetField performs assignment conversion and returns a copy of oldRow.
		// Merge only its target, so later assignments cannot undo earlier writes.
		value, err := assignment.Eval(ctx, oldRow)
		if err != nil {
			return nil, err
		}
		...
}

Now Doltgres returns the expected result, the same as Postgres.

a |  b
---+-----
2 | 100    -- per the SQL standard, every assignment reads the pre-update row

Check out these two PRs for the full details.

Conclusion#

Doltgres 1.0 already launched, but Doltgres’s compatibility story is definitely not over. Keep the issues coming and we’ll keep knocking them down in 24 hours.

Have a divergence in Postgres behavior to report? Want to learn more about Doltgres? Visit us on the DoltHub Discord where our engineering team hangs out all day. Hope to see you there.

Implementing Type Contracts via Mutually Referencing Type Parameters

Nick Tobey — Fri, 28 Aug 2026 00:00:00 GMT

I work for Dolt, the world’s first version-controlled database. We made Dolt as a drop-in replacement for MySQL, since MySQL was the most commonly used SQL database in production when we started. But new teams by-and-large are not choosing MySQL; they’re using Postgres. So we also made Doltgres, a alternate version of Dolt that speaks the Postgres dialect.

Unsurprisingly, Dolt and Doltgres share a lot of common code. But there’s also subtle differences between MySQL and Postgres’s feature set. We want to use shared code for things that the projects have in common, and interfaces to implement behavior where they differ.

One of our big features is Git-style branches. A client session operates on a currently checked-out branch, and is completely independent of the other branches in the database. Except when I say “completely independent”, I actually mean “mostly independent.” When we made Dolt, we decided that there was one specific situation where we wanted branches to not be independent: auto incrementing columns.

In MySQL, if a table has a column declared with the AUTO_INCREMENT modifier, inserts aren’t required to specify a value for that column. Instead, a new value will be generated that is guaranteed to not conflict with any values currently in the table. This is true even when multiple sessions are making transactions concurrently, which each transaction getting a different value for the column. While transactions are typically independent from each other, MySQL makes an exception here so that the transactions won’t conflict if both are committed.

We decided that it made sense to apply that same behavior to branches: if sessions on two different branches are inserting into the same table with an auto increment column, Dolt is guaranteed to generate non-conflicting values for that column. If we didn’t do this, then any attempt to merge those branches would result in a merge conflict.

In contrast, Postgres doesn’t have AUTO INCREMENT. Instead, it has SERIAL columns, which behave similarly but have some key differences:

In MySQL, AUTO INCREMENT values are always unsigned, while in Postgres, SERIAL values are always signed.
SERIAL columns are backed by a data type called a Sequence, which has configuration parameters such as the min and max values, whether the generated values are incremented or decremented, whether they wrap around when they reach the end, etc.

Synchronizing the behavior across all branches and all transactions has a lot of tricky corner cases, that we’d already gotten right with AUTO INCREMENT, and duplicating that logic would be a bad idea. So we designed a data model that could handle both:

A Sequence is an item in the database capable of producing a sequence of values, such as a MySQL table with an AUTO INCREMENT column, or a Postgres sequence.
A SequenceValue is a value produced by a sequence.
A SequenceState is a type that represents the current state of a Sequence and can be incremented to produce new SequenceValues.

Using these definitions, we were able to implement all of the behavior common to both MySQL tables and Postgres sequences as generic methods on these types.

This reminded me of a similar situation I encountered two years ago, involving pairs of data structures each consisting of a mutable type and an immutable type that could be converted between each other. The goal was to write something like the below, that could be used with any pair of types that satisfied this contract:

func ApplyMutations(immutable ImmutableValue, mutations []Mutation) ImmutableValue {
  mutableValue := immutable.Mutate()
  mutableValue.ApplyMutations(mutations)
  return mutableValue.Flush()
}

I wrote a blog post talking about my solution to that problem, how I’d used a Golang generic interface to model a situation where a group of types collectively implement some contract. But there’s a lot in that I got flat-out wrong about how go Golang generics worked, and I got rightfully chewed out for it in the responses to the extent that I’m a bit embarrassed to bring it up again.¹

This time, I was determined to make a better solution. This is a real problem, and it’s worth understanding the correct way to tackle it in Go because it’s a useful design pattern for writing type-safe code.

The Problem#

In Go, if you want a function to operate on multiple different types, you can define an interface. An interface is a contract: it specifies a set of constraints that the implementing type must satisfy, usually methods that the type must implement. Any type that implements these methods will satisfy the interface. But this interface only constrains a single type. Sometimes, you have a group of types that need to implement a contract together. How can we use Go’s language features to solve this problem?

What Doesn’t Work: Interfaces#

If we were to express the above-mentioned Sequence data model as interfaces, it might look something like this:

type SequenceValue interface {
    int64 | uint64
}

type SequenceState interface {
    CurrentValue() SequenceValue
    Advance() (nextValue SequenceValue, nextState SequenceState)
}

type Sequence interface {
    CurrentState() SequenceState
    UpdateFromGlobalState(SequenceState) Sequence
}

But making these types regular interfaces is a bad idea for several reasons:

There’s a performance penalty for calling interface methods due to dynamic dispatch.
All values of an interface type are boxed and their underlying values are stored on the heap.
Go doesn’t actually allow interfaces with shape constraints (like SequenceValue above) to be used in method signatures.

But the biggest problem is that this fails to give us the compile-time type safety we want. It doesn’t document or enforce that a particular SequenceState implementation is always expected to have a specific type for SequenceValue. We could need to insert check-casts every time one of these methods is called. If we ever call a method with the wrong implementation, we would panic at runtime.

This isn’t a good use of interfaces, because we’re paying a cost in performance and code complexity but not getting anything out of it.

The Bad Idea: A Single Generic Interface#

This was the concept in my previous attempt that rightly got a lot of pushback.

The basic idea was that if interfaces are how you achieve polymorphism in Go, and an interface is defined by a set of behaviors on a single type, then you can achieve a contract on multiplace types by implementing an interface that accepts each of those types as a generic type parameter.

So in this case, you would have a “contract” type that defines all the necessary behavior, and generic code must call methods on this contract type:

type SequenceContract[SequenceValue any, SequenceState any, Sequence any] interface {
    SequenceState_CurrentValue(SequenceState) SequenceValue
    SequenceState_Advance(SequenceState) (nextValue SequenceValue, nextState SequenceState)
    Sequence_CurrentState(Sequence) SequenceState
    Sequence_UpdateFromGlobalState(Sequence, SequenceState) Sequence
}

The main downside of this is that it’s ugly. Shared code can’t call methods on the component types because they all have an any type constraint. Instead it must call the methods on SequenceContract, which will likely just delegate to the corresponding methods on the component types.

This approach also requires that shared code takes a SequenceContract value as an extra parameter. This also requires the shared code to be itself generic, which means that prior to Go 1.27 it couldn’t be a method and had to be a function.

There are some possible upsides to this approach: implementations of the contract type are allowed to have state, and it’s possible to define multiple contracts on the same collection of types. But these “upsides” are a double-edged sword, because now you’re adding further complexity to your data model. This is rarely the best approach.

Almost A Solution: Generic Interfaces#

We could attempt to capture the relationship between types via generic interfaces. Going back to original example with the corresponding mutable and immutable types, we could attempt to write interfaces like so:

type ImmutableValue[T ...] interface {
  Mutate() T
}

type MutableValue[T ...] interface {
  ApplyMutations([]Mutation)
  Flush() T
}

But what do we put in place of ”…” in the example above?

We can’t have the two interfaces reference each other (that is, we can’t write type ImmutableValue[T MutableValue]) because MutableValue isn’t a complete type. And even if we could somehow do that, prior to Go 1.26 we weren’t allowed to have both ImmutableValue and MutableValue reference each other in their type constraints.

One option is to just use any:

type ImmutableValue[T any] interface {
  Mutate() T
}

type MutableValue[T any] interface {
  ApplyMutations([]Mutation)
  Flush() T
}

But this alone isn’t enough to let us write a function like ApplyMutations above: if the return type of Mutate() is constrained by any, then we can’t call any methods on it.

As we’ll soon see, Go 1.26 added Recursive Type Parameters, which actually gives us something that we can put here. But as we’ll also see, putting a strict type constraint here isn’t actually necessary, and doesn’t actually change what the correct solution looks like.

The Correct Idea: Mutually Referential Type Parameters#

This is a technique originally described in the original Go type parameters proposal.

The idea is to let go of the idea that every part of your data model needs to be described with interfaces. What actually matters is your functions and structs, which describe the data that they accept. An interface is powerful because it allows you to name a set of constraints and reuse them. It’s a useful tool for deduplicating constraint definitions and for composing them, but they’re a means to an end.

So building on the previous example, we can use any type constraints in the interface definitions, and then further constraint them when these interfaces are actually used:

type ImmutableValue[T any] interface {
  Mutate() T
}

type MutableValue[T any] interface {
  ApplyMutations([]Mutation)
  Flush() T
}

func ApplyMutations[
    ImmutableType ImmutableValue[MutableType],
    MutableType MutableValue[ImmutableType],
](
    immutable ImmutableValue,
    mutations []Mutation,
) ImmutableValue {
  mutableValue := immutable.Mutate()
  mutableValue.ApplyMutations(mutations)
  return mutableValue.Flush()
}

And for the case of the Sequence type, which must be able to return itself, it can take a self-referential type constraint. These interfaces are then used in creating the full set of type constraints for a struct, which contains our actual business logic as methods:

type SequenceState[Self any, ValueType any] interface {
    CurrentValue() ValueType
    Advance() (nextValue ValueType, nextState Self)
}

type Sequence[Self any, StateType any, ValueType any] interface {
    CurrentState() StateType
    UpdateFromGlobalState(StateType) Self
}

type SequenceTracker[
    ValueType any,
    StateType SequenceState[StateType, ValueType],
    SequenceType Sequence[SequenceType, StateType, ValueType]
] struct {
    ...
}

Note that even though the interface definitions don’t enforce any requirements for their type parameters, the code is still fully type-safe because the types are fully constrained where they’re actually used.

The one downside is that the function and struct definitions themselves can become quite verbose. And every function that accepts these types must duplicate the type constraint. The interface definitions can reduce the verbosity but don’t eliminate it. Fortunately, all these types can be inferred at the callsite, so the callsite remains clean.

Go 1.26 allows us to further constrain the interface definitions by allowing the type constraints to reference the interface type being defined:

type SequenceState[
    Self SequenceState[Self, ValueType],
    ValueType any
] interface {
    CurrentValue() ValueType
    Advance() (nextValue ValueType, nextState Self)
}

type Sequence[
    Self Sequence[Self, StateType, ValueType],
    StateType SequenceState[Self, ValueType],
    ValueType any,
] interface {
    CurrentState() StateType
    UpdateFromGlobalState(StateType) Self
}

However, this does not reduce any type constraints in the functions and structs that use these interfaces. In my experience the main benefit of this is to make the types more self-documenting, not to provide additional type safety.

Everything example I’ve shown except for the last one has existed since Go 1.18, when type parameters were first added. So why didn’t I identify the proper solution previously?

There were two facets that I think blindsided me: the use of any within type constraints, and the ambiguous documentation around self-referential type constraints.

`any` Type Constraints#

It’s generally discouraged to use any as a parameter type, and recommended to use the most specific type possible in interfaces and APIs. Given that advice, I had attempted to avoid using any as a type constraint outside of situations where it was explicitly expected to support any type. Then, because it wasn’t possible to fully express these constraints within interface definitions prior to 1.26, I concluded that it wasn’t possible to write valid interface definitions for this situation.

But in fact, it’s perfectly fine to use any as a type constraint, and it doesn’t mean that the code you write will have to accept any as a variable type.

Think of a generic type for a data structure:

type MinHeap[T any] interface { ... }

This doesn’t imply that you’re actually going to specialize it with any. It’s just a constraint. Don’t be afraid of any in type constraints.

Ambiguity in documentation#

The original proposal for type parameters describes the above pattern as “mutually referencing type parameters”. And indeed, the only examples provided are cases where two type parameters reference each other. There are no examples of type parameters that reference themselves.

When I initially attempting to write a type constraint for the ImmutableValue interface above, I attempted to write the version with the recursive type constraints, which would not be supported until 1.26. When this was rejected, I incorrectly assumed that it was the self-referential nature of the type constraint that made it not allowed. In fact, it has always been allowed for a type constraint to reference its own type parameter.

Conclusion#

Go isn’t like other languages, and its worth learning its idioms and coding patterns. I still have my gripes: I don’t like how verbose this approach is, how it contains duplicate type constraints at every generic function that needs to operate on the defined types. But adopting the recommended coding styles has also helped to expand how I think about generic code in Go.

As always, if you have thoughts or if you want to tell me how wrong I am, feel free to join our Discord and shoot me a message.

But you can still read it here, if you like. ↩

Introducing the Hosted Dolt REST API

Taylor Bantle — Thu, 20 Aug 2026 00:00:00 GMT

Hosted Dolt is for running online, production Dolt and Doltgres databases. You choose the server and disk you need, and we provision the resources and run the database for you, complete with logging, metrics, backups, and upgrades.

Up until now, the only way to create or manage one of those deployments was from the web UI. Today I’m happy to announce that Hosted Dolt has an official REST API. It’s live at https://hosted.doltdb.com/api/v1/, and it’s documented at dolthub.com/docs/products/hosted/api/v1.

If you’ve used the DoltHub API v2 we released last month, this one will look familiar because it’s purposely built on the same contract.

Motivation#

Hosted Dolt has had two programmatic surfaces for a while. Both are good at what they were designed for, but neither was designed specifically for managing deployments.

The first is your deployment’s SQL endpoint. That’s a live Dolt or Doltgres server, so anything Dolt can do, you can do over a normal MySQL or Postgres connection, including branches, merges, diffs, and the rest of the version control system tables and procedures. This is limited to the data within your database and can not be used to provision or manage the deployment itself.

The second is the GraphQL API behind the Hosted website. A little over a year ago we wrote a blog called “Hosted Dolt’s Hidden GraphQL API”, which walked through pulling the hostedToken cookie out of your browser dev tools and hand-writing GraphQL queries against it. That post opened with a warning that it wasn’t an official API and could change at any time. People used it anyway because it was the only programmatic option for managing deployments.

Creating and managing deployments and instances will always be available from the web UI. But we believe Dolt is the database for agents, and in the age of agents it because increasingly important to provide an API an agent can use. Branching and diffing let an agent work in isolation and have changes audited before they merge, and you’ve been able to connect an agent to a Hosted deployment over MCP since February. Giving it a documented REST API with real status codes and a stable error model means it can provision the database it works in too, so the whole loop, from creating a deployment to querying it to shutting it down when it’s done, is something an agent can run end to end.

Built on the DoltHub API v2#

When we built the DoltHub API v2, the goal was a contract-first API where an OpenAPI 3.1 spec is the source of truth and everything else is generated from it: the published docs, the TypeScript types, the runtime request validation, and the contract tests. Adding an endpoint means editing the spec first, and anything that doesn’t match the spec doesn’t build.

That worked well enough that we reused the whole thing for Hosted. openapi/v1.yaml is the contract, the generated types are checked in and verified against the spec in CI, request bodies are validated at runtime against the spec’s schemas, and a breaking change check runs on every pull request. Both specs are even gated by the same CI workflow, one matrix entry each, so the two APIs can’t drift in how we detect breakage.

More importantly, the parts you actually touch as a caller are the same:

One success envelope. Every 2xx body is { "data": ..., "meta": ... }. data is the resource or the array of resources, and meta is optional.
One error model. Every non-2xx response is an RFC 9457 problem document, with a stable code in SCREAMING_SNAKE_CASE that you can branch on instead of parsing English.
Cursor pagination. Where a list paginates, the response has a meta.next_page_token that you pass back as page_token.
Bearer auth. Authorization: Bearer <token>, 401 if the credential is missing or bad, 403 if it’s valid but not allowed.
snake_case everywhere, and an x-request-id on every response, including successful ones.

There’s one deliberate difference. On DoltHub, endpoints are public unless they opt in to auth, because public database reads are its baseline. On Hosted, every endpoint requires a token and has to opt out. Hosted’s control plane has nothing that’s anonymously readable, so we flipped the default.

What it covers#

There are eleven endpoints today, and they cover your deployments, the instances behind them, their backups and configuration, and the options you can create them with.

Endpoint	What it does
`GET /api/v1/user`	The authenticated user’s profile
`GET /api/v1/deployment-options`	The zones, instance types, and storage a deployment can be created with
`POST /api/v1/deployments`	Create a deployment
`GET /api/v1/deployments/{owner}`	List an owner’s deployments
`GET /api/v1/deployments/{owner}/{deployment}`	Get a deployment
`POST /api/v1/deployments/{owner}/{deployment}/disable`	Disable a deployment
`GET /api/v1/deployments/{owner}/{deployment}/instances`	List the instances behind a deployment
`POST /api/v1/deployments/{owner}/{deployment}/instances`	Add a read replica
`DELETE /api/v1/deployments/{owner}/{deployment}/instances/{id}`	Remove an instance
`GET /api/v1/deployments/{owner}/{deployment}/backups`	List a deployment’s backups
`GET /api/v1/deployments/{owner}/{deployment}/config`	Get a deployment’s database configuration

This API is the deployment control plane only. Querying the data inside a deployment isn’t part of this API and won’t be. Your deployment already exposes a SQL endpoint you connect to directly with your own database credentials, and that’s a much better interface for queries than anything we’d put over HTTP. For the same reason, your database credentials are deliberately not part of the deployment resource, so reading a deployment never hands out a credential.

Getting a token#

First you’ll need a token. Create one from the Tokens section of your user settings. Hosted API tokens are prefixed hsat.v1., they carry the same permissions as the user who created them, and they expire on a date you pick.

export HOSTED_TOKEN=hsat.v1.xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

The quickest way to check it works is GET /api/v1/user. A 200 tells you the token is good and whose access it carries.

curl -s 'https://hosted.doltdb.com/api/v1/user' \
  -H "Authorization: Bearer $HOSTED_TOKEN"

{
  "data": {
    "username": "acme-ops",
    "display_name": "Acme Operations",
    "company": "Acme Corp",
    "email_addresses": [
      { "address": "ops@acme.com", "is_verified": true, "is_primary": true }
    ]
  }
}

Creating a deployment#

1. See what you can create#

GET /api/v1/deployment-options tells you the options you can use to create a deployment or instance. It narrows in steps, since each choice depends on the one before it. Pass cloud on its own to get its zones, add zone to also get that zone’s instance types, and add instance_type_id to also get the storage that works with that instance. Anything you haven’t narrowed enough to determine is left out rather than returned empty.

curl -s -G 'https://hosted.doltdb.com/api/v1/deployment-options' \
  -H "Authorization: Bearer $HOSTED_TOKEN" \
  -d cloud=aws \
  -d zone=us-east-1 \
  -d instance_type_id=aws.t2.medium

{
  "data": {
    "cloud": "aws",
    "zones": ["us-east-1"],
    "instance_types": [
      {
        "id": "aws.t2.medium",
        "name": "t2.medium",
        "cpus": 2,
        "memory_gb": 4,
        "description": "Trial tier, the lowest spec that runs a Dolt SQL server.",
        "hourly_cost_usd": 0.06849315
      }
    ],
    "storage_options": [
      {
        "id": "aws.ebs.gp3_50",
        "name": "Trial 50GB EBS",
        "description": "Trial tier storage capped at 50GB",
        "min_size_gb": 50,
        "max_size_gb": 50,
        "monthly_cost_usd_per_gb": 0
      }
    ]
  }
}

Note that you will need the id, not the name, for the create deployment endpoint.

2. Create it#

curl -s -X POST 'https://hosted.doltdb.com/api/v1/deployments' \
  -H 'Content-Type: application/json' \
  -H "Authorization: Bearer $HOSTED_TOKEN" \
  -d '{
    "owner": "acme",
    "name": "analytics",
    "cloud": "aws",
    "zone": "us-east-1",
    "instance_type_id": "aws.t2.medium",
    "volume_type_id": "aws.ebs.gp3_50",
    "volume_size_gb": 50
  }'

You get back a 202, meaning we’ve accepted the request and provisioning continues after the response. host is empty until the deployment comes up.

{
  "data": {
    "owner": "acme",
    "name": "analytics",
    "state": "starting",
    "cloud": "aws",
    "zone": "us-east-1",
    "cluster_type": "dolt",
    "instance_type_name": "t2.medium",
    "volume_type_name": "Trial 50GB EBS",
    "volume_size_gb": 50,
    "replicas": 0,
    "host": "",
    "port": 3306,
    "caller_role": "admin",
    "created_by": "acme-ops",
    "created_at": "2026-08-11T09:14:00Z"
  }
}

cluster_type defaults to dolt. Pass doltgres if you want a Doltgres deployment, or mysql_with_dolt_replicas for a MySQL primary with Dolt read replicas.

3. Wait for it to start#

The DoltHub API v2 routes its async work through a single Operation resource that you poll. We didn’t need one here, because the deployment is already the thing whose state you care about. So you poll the deployment until state is started.

curl -s 'https://hosted.doltdb.com/api/v1/deployments/acme/analytics' \
  -H "Authorization: Bearer $HOSTED_TOKEN"

{
  "data": {
    "owner": "acme",
    "name": "analytics",
    "state": "started",
    "cloud": "aws",
    "zone": "us-east-1",
    "cluster_type": "dolt",
    "instance_type_name": "t2.medium",
    "volume_type_name": "Trial 50GB EBS",
    "volume_size_gb": 50,
    "replicas": 0,
    "database_version": "1.58.4",
    "host": "analytics.dbs.hosted.doltdb.com",
    "port": 3306,
    "hourly_cost_usd": 0.06849315,
    "webpki_cert": true,
    "expose_remotesapi_endpoint": false,
    "expose_mcp": false,
    "expose_stats": false,
    "disable_automatic_dolt_updates": false,
    "caller_role": "admin",
    "created_by": "acme-ops",
    "created_at": "2026-07-01T18:22:04Z"
  }
}

host and port are your connection details, and from here you’re in normal Dolt territory.

mysql -h analytics.dbs.hosted.doltdb.com -P 3306 -u <user> -p

The same starting to started transition covers restarts and resizes too, so the same poll works for those.

Inspecting a deployment#

GET /api/v1/deployments/{owner} lists deployments for an owner name. It takes an optional state filter and it paginates. The items are a summary rather than the full deployment, so it drops the connection details and adds last backup information.

curl -s -G 'https://hosted.doltdb.com/api/v1/deployments/acme' \
  -H "Authorization: Bearer $HOSTED_TOKEN" \
  -d state=started

{
  "data": [
    {
      "owner": "acme",
      "name": "analytics",
      "state": "started",
      "cloud": "aws",
      "zone": "us-west-2",
      "cluster_type": "dolt",
      "instance_type_name": "m5.large",
      "volume_type_name": "gp3",
      "volume_size_gb": 100,
      "replicas": 0,
      "database_version": "1.58.4",
      "hourly_cost_usd": 0.192,
      "webpki_cert": true,
      "last_backup_time": "2026-08-10T02:00:00Z",
      "last_backup_size_bytes": 1048576
    }
  ],
  "meta": {
    "next_page_token": "eyJvZmZzZXQiOjI1fQ"
  }
}

Pass the next_page_token back as page_token for the next page, and stop when meta isn’t there anymore.

You can list the backups we’re holding for a deployment, newest first:

curl -s 'https://hosted.doltdb.com/api/v1/deployments/acme/analytics/backups' \
  -H "Authorization: Bearer $HOSTED_TOKEN"

{
  "data": [
    {
      "id": "20260812T020000.000",
      "databases": ["analytics", "staging"],
      "instance_index": 0,
      "created_at": "2026-08-12T02:00:00Z"
    },
    {
      "id": "20260811T020000.000",
      "databases": ["analytics", "staging"],
      "size_bytes": 1048576,
      "instance_index": 0,
      "created_at": "2026-08-11T02:00:00Z"
    }
  ]
}

The newest backup there doesn’t have a size_bytes yet. That’s expected, since we measure it asynchronously after the backup is taken, so a recent one has no size for a few minutes.

And you can read a deployment’s database configuration. This returns every setting Hosted supports, at the value the deployment is actually running, which is the same thing the Configuration page in the UI shows you. is_overridden tells you whether you changed it, and default tells you what it would go back to.

curl -s 'https://hosted.doltdb.com/api/v1/deployments/acme/analytics/config' \
  -H "Authorization: Bearer $HOSTED_TOKEN"

{
  "data": {
    "settings": [
      {
        "key": "listener_max_connections",
        "value": "500",
        "default": "100",
        "is_overridden": true
      },
      {
        "key": "behavior_read_only",
        "value": "false",
        "default": "false",
        "is_overridden": false
      }
    ]
  }
}

Values come back as strings exactly as they’re stored, including the numeric and boolean ones.

Disabling a deployment#

POST .../disable tears a deployment’s instances and storage down. You get a 202 with the deployment in stopping, and you poll the deployment until it’s stopped.

curl -s -X POST 'https://hosted.doltdb.com/api/v1/deployments/acme/analytics/disable' \
  -H "Authorization: Bearer $HOSTED_TOKEN"

{
  "data": {
    "owner": "acme",
    "name": "analytics",
    "state": "stopping"
  }
}

Take a backup first if you want the data.

The deployment record itself sticks around. It stays readable with disabled_at and disabled_by set, which is why this is a POST to an action rather than a DELETE on the deployment. To bring it back, add an instance to it, which clears the shutdown and starts it up again. Pass a backup_id from the backups list on that request to restore your data into it, or it’ll come back empty.

Documentation#

The full reference lives at dolthub.com/docs/products/hosted/api/v1. Every endpoint, schema, error code, and security scheme there is rendered straight from the OpenAPI spec, so there’s no drift between what the docs say and what the server does.

If you’d rather generate a typed client in your language of choice, the spec itself is in our docs repo. Grab it and point your generator at it.

Future work#

v1 is additive. We can add endpoints, optional request fields, response fields, and new error codes within v1, and we’d only need a v2 to rename or remove a field or change what an existing one means. So you can build against what’s live today without worrying that the rest of it will move underneath you.

There’s more coming:

Pull requests: creating, viewing, and merging them. Every other version control operation is available over SQL on your deployment, but pull request metadata lives in Hosted’s application database rather than in your Dolt database, so there’s no query that opens or manages one.
Credentials: issuing and rotating a deployment’s database credentials.
Configuration writes: updating deployment settings and Dolt configuration.
Deployment actions: upgrading Dolt/Doltgres, rebooting an instance, and restarting an application.
Access management: adding and removing collaborators and their roles.
CLIs: we’re building a command line tool on top of this API and the DoltHub API v2, along the lines of GitHub’s gh, so you can drive either product from your terminal without writing the HTTP calls yourself.

Conclusion#

Between this and the DoltHub API v2, building against either of our products should feel like working with the same API. If it doesn’t somewhere, that’s a bug and we want to hear about it.

Create a token and try it out. File an issue if there’s an endpoint you want sooner or is not covered above, or come to talk to us on Discord.

What's New With Golang Generics

Nick Tobey — Fri, 14 Aug 2026 00:00:00 GMT

I write Dolt, a version controlled database written 100% in Go. This wasn’t a choice we made so much as a choice that was made for us, since it’s based on noms and built on go-mysql-server, which were both written in Go.

As a result, I use Golang every day, but I’m neither a devoted advocate nor a hater. Go is a tool, and given that we used it to build a drop-in replacement for MySQL that’s faster than MySQL, it does a perfectly decent job.

Go’s design philosophy is that it’s simple by design, and the language is very conservative about adding new features unless those features can be demonstrated to be actually necessary and do more good than harm. It’s a high bar, but a bar than can and has been met in the past. Remember that Go used to not support generics, but support was finally added in 1.18, after it was clear that they were needed.

Since then, Go’s generic support has slowly improved. Features are added when they allow for cleaner and more expressive code, but avoided when it would only lead to messier, harder-to-maintain code. Last year, I ruminated about potential features that Golang might add to generics, debating for each one whether or not it met the bar. I essentially asked for each feature: “Generic code was already hard to read, does adding this allow for even more complicated code? Or do they allow for cleaner ways to express existing ideas?

Cut to two years later, and some of the features I ruminated about have since been more-or-less added to the language. I decided to see how these additions had impacted Dolt, whether we had adopted them and whether they had enabled us to write cleaner code.

Generic Type Aliases (Added in Go 1.24)#

Prior to Go 1.24, the following was invalid:

type Set[T comparable] = map[T]struct{}

While generic type aliases don’t add any expressiveness to the language, they can remove a lot of repetition and clutter. They’re mostly useful when you intend to repeatedly use a specific type for some of a generic’s type parameters, so you partially specialize it once.

I was an advocate for generic type aliases last time, and I’m excited to see that they’re supported now. But they’re also very situational: I searched Dolt’s codebase and found 0 uses. So their use cases are indeed pretty narrow.

That said, now that they’re available, it’s possible they might find a home in Dolt in the future.

Recursive Type Constraints (Added in Go 1.26)#

This was a feature added in Go 1.26, allowing type constraints like the following:

// A Lattice is a type of weakly ordered values with a least value named Bottom, and a unique
// least upper bound for any two value.
type Lattice[T Lattice[T]] interface {
  Less(other T) bool
  LeastUpperBound(other T) T
  Bottom() T
}

func WeakTopologicalOrder[T Lattice[T]](elems []T) seq.Iter { ... }

var _ Lattice[LatticeImpl] = LatticeImpl{}

This lets us use method chaining in functions that operate on this type:

func NAryLeastUpperBound[T Lattice[T]](elems T[]) T {
  var result T
  result = result.Bottom()
  for _, l := range elems {
    result = result.LeastUpperBound(l)
  }
  return result
}

In other languages, using a type parameter in its own type constraint is called a curiously recurring template pattern. It has some additional uses in languages that allow template metaprogramming, but the main value in Golang is to define an interface for types with methods that accept or return the same time being implemented.

This strongly resembles the “self-type constraint” feature from my previous article, with the caveat that it’s technically possible to provide a different type for the type parameter from the type that’s implementing the interface. For instance, You could have LatticeImpl implement Lattice[SomeUnrelatedLatticeImpl], although you probably shouldn’t.

This looks really cool… but it’s not actually accomplishing as much as you might think. Prior to Go 1.26, the above interface definition wouldn’t be allowed, but we could write one that looks like this instead:

type Lattice[T any] interface {
  Less(other T) bool
  LeastUpperBound(other T) T
  Bottom() T
}

And our generic function above would still be just as correct. The main benefit of the recursive type constraint is that it more clearly documents intent. It’s more obvious at a glance that the generic interface is meant to be specialized with the same type that’s implementing it, and it rejects attempts to specialize it with a type that’s completely unrelated.

There’s no code in Dolt that uses this feature, but there’s some code that could be updated to use it, such as the SequencedRelation generic interface. This in an interface type that represents database objects that produce an incrementing sequence of values, such as MySQL’s AUTO_INCREMENT columns. Implementations of this interface have a method that take a state for sequence’s state machine and return a new database object whose state is set to the provided value. Since this is a type that can return itself, it has a self-referential type parameter named Self:

// an abridged version of the SequencedRelation generic interface
type SequencedRelation[Self any, StateType any] interface {
	// GetSequenceState returns the current SequenceState of the object.
	GetSequenceState(ctx context.Context) (StateType, error)
	// HasSequenceState returns whether the relation wraps a sequence.
	// (This may be false, for instance, for tables that do not have an AUTO INCREMENT column)
	HasSequenceState(ctx context.Context) (bool, error)
	// SetSequenceState unconditionally sets the SequenceState for the object.
	SetSequenceState(ctx context.Context, val StateType) (Self, error)
}

Note how the type of this constraint is any. With Go 1.26, we could use a more specific type for this constraint:

// This version is more self-documenting:
// It's more clear what the type parameter is for and harder to misuse.
type SequencedRelation[Self SequencedRelation[Self, StateType], StateType any] interface {
	// GetSequenceState returns the current SequenceState of the object.
	GetSequenceState(ctx context.Context) (StateType, error)
	// HasSequenceState returns whether the relation wraps a sequence.
	// (This may be false, for instance, for tables that do not have an AUTO INCREMENT column)
	HasSequenceState(ctx context.Context) (bool, error)
	// SetSequenceState unconditionally sets the SequenceState for the object.
	SetSequenceState(ctx context.Context, val StateType) (Self, error)
}

Generic Methods (Added in Go 1.27)#

Prior to Go 1.27, while function definitions could take generic type parameters, methods could not. This was because Go is a structurally typed language: the set of methods implemented by a type determines what interfaces it implements. If a type defines generic methods, it becomes difficult for the compiler to reason about which interfaces it implements, and even more for the compiler to determine which of the infinite number of possible specializations of the method will be needed at runtime.

This meant that it wasn’t possible to write generic methods, even if the method wasn’t intended to be part of an interface. For instance, if you have a function that’s tightly coupled with a type, you might want to make that function a method to have it exist in the function’s namespace. But if that function was generic, you couldn’t do that.

type Tree[T any] struct {
  node T
  children []Tree[T]
}

// Prior to Go 1.27, this was not allowed
func (t Tree[T]) Map[U any](f func(T) U) {
  result := Tree[U] { node: f(t.node)}
  for _, child := range children {
    result.children = append(result.children, f(child))
  }
}

Instead, you would have needed to write the almost-identical function below:

func MapTree[T any, U any](input Tree[T]) Tree[U] {
  result := Tree[U] { node: f(t.node)}
  for _, child := range children {
    result.children = append(result.children, f(child))
  }
}

With Go 1.27, types will finally be able to implement generic methods, with the condition that these methods do not participate in satisfying interface constraints. These methods won’t change the set of interfaces that the type implements but can still be used to add functions to the namespace of the type.

We have functions like the above in Dolt. But none of them have been migrated to use generic methods yet, because Go 1.27 isn’t out yet. But when it releases later this month, I’ll expect we’ll find ourselves writing generic methods when it is natural to do so.

Overall Impact#

Currently, Dolt doesn’t use any of the above features, although that likely won’t be true for long. I’ve already identified places where we could incorporate recursive type constraints for a little bit of extra type strictness, and we’ll definitely be writing generic methods once we’re able to. It’s possible that there are places that would benefit from generic type aliases, and we just haven’t identified them or done the necessary refactors yet.

So while none of these features have made an impact on Dolt, although I’m still glad they all exist.

Next time we’ll dive more into how Dolt expresses relationships between generic types in a clean, readable way. In the meantime, I know you all have strong opinions about Golang. Feel free to join our Discord and tell me all the ways I’m wrong about the language.

Doltgres is as Fast as MySQL is Slow

James Cor — Thu, 13 Aug 2026 00:00:00 GMT

It’s official! Doltgres v1.0 is here! Alongside various features and correctness improvements, this release comes with some large performance gains. Over the course of a few months, we’ve managed to reduce Doltgres from 4.4x to 2.6x Postgres on sysbench, which is a 1.8x improvement! Fun fact, MySQL’s average multiplier when compared against Postgres on these same benchmarks is also 2.6x. This means that Doltgres is as fast as MySQL is slow.

Overview#

There were a wide variety of optimizations over various parts of the codebase, including improvements to wire format serialization, collections, query analysis, and index costing. In summary, here are the latency numbers from Doltgres v0.56.3 to v1.0.0 compared against Postgres 15.5:

benchmark	doltgres v0.56.3	doltgres v1.0.0	postgres
covering_index_scan	6.55	2.48	18.28
groupby_scan	155.80	74.46	40.37
index_join	6.67	2.22	1.82
index_join_scan	6.21	1.61	0.69
index_scan	1235.62	484.44	183.21
oltp_point_select	0.55	0.37	0.15
oltp_read_only	11.45	6.55	2.66
select_random_points	0.97	0.73	0.22
select_random_ranges	1.20	1.04	0.42
table_scan	1235.62	484.44	183.21
types_table_scan	2778.39	1235.62	434.83
oltp_delete_insert	7.43	6.79	2.22
oltp_insert	4.10	3.43	1.10
oltp_read_write	20.00	13.46	4.41
oltp_update_index	4.10	3.62	1.14
oltp_update_non_index	3.82	3.30	1.14
oltp_write_only	8.28	6.91	1.82
types_delete_insert	7.98	7.17	2.30

Here’s a graph of the multipliers against Postgres:

The rest of this blog will go over every performance-related change since v0.56.3.

Wire Format Serialization Improvements#

When comparing the flamegraphs against Dolt, the code paths surrounding wire format serialization and sending packets had the largest CPU usage discrepancy.

Buffered Flush#

Perhaps the largest performance improvement we saw was with this 9 line change. The Doltgres server needs to send result rows back the client using the Postgres protocol, which we do through the pgproto3 package. Typically, these results are buffered and flushed out in large batches. However, we were sending a packet for every individual row (including the initial row descriptor), wasting a ton of CPU cycles. After adjusting the send logic to call Flush() every row_batch_size = 128, we saw drastic performance improvements practically across the board.

Benchmarks that returned multiple rows saw throughput improvements of over 200%, with some as high as 247%. You can view the full results here.

Spooling Concurrency#

Another improvement around sending rows back to the client is one we’ve already done before in Dolt. The server handler is in charge of reading results from RowIters, converting them into the wire format, and sending these packets to the client. Similar to the old Dolt code, we put two goroutines in charge of these steps: one to read the rows and another to convert and send the results. The fix here is to break these steps up into three goroutines: (1) read the rows from RowIter, (2) convert each row into wire format through SQL() method, and (3) spool results to client. The resulting optimization gave us over 50% improvement in throughput for index_scan, table_scan, and types_table_scan; these are all benchmarks that return large result sets.

You can view the full results here.

Output Function Cache#

The serialization format functions are fully customizable by users. While this can be pretty useful, it requires additional complexity to be efficient. Namely, we should not be reloading the output function every time we serialize a field, especially since it doesn’t change within a query. So, the solution is to just cache it somewhere.

This change got us ~13% improvement in output heavy benchmarks like index_scan, table_scan, and types_table_scan.

You can view the full results here.

Wire Format Improvements#

These next optimizations all involve improving the wire format serialization itself. Specifically, we made improvements to the TimeOfDay, bpchar, and Date types.

fmt.Sprintf() is handy but slow. Since TimeOfDay.String() produces is a small string with a strict format and known max length, we can just create a []byte and append to it. Additionally, we can safely use the unsafe package, to convert that []byte to a string.

func (t TimeOfDay) String() string {
	dest := make([]byte, 0, 15) // longest possible result is len("12:34:56.123456") = 15
	h, m, s, ms := t.Hour(), t.Minute(), t.Second(), t.Microsecond()
	dest = append(dest,
		'0'+byte(h/10), '0'+byte(h%10), ':',
		'0'+byte(m/10), '0'+byte(m%10), ':',
		'0'+byte(s/10), '0'+byte(s%10))
	if ms > 0 {
		dest = append(dest, '.')
		cmp := 100_000
		for cmp > 0 {
			dest = append(dest, '0'+byte(ms/cmp))
			ms %= cmp
			cmp /= 10
		}
		// trim trailing 0s
		for i := len(dest) - 1; i >= 0; i-- {
			if dest[i] != '0' {
				dest = dest[:i+1]
				break
			}
		}
	}
	return unsafe.String(unsafe.SliceData(dest), len(dest))

Next, we made an improvement to truncateString() for bpchar by again writing our own implementation. The old implementation used the utf8 package to get the rune length and decode each rune.

func truncateString(val string, runeLimit int32) (string, int32) {
	runeLength := int32(utf8.RuneCountInString(val))
	if runeLength > runeLimit {
		startString := val
		for i := int32(0); i < runeLimit; i++ {
			_, size := utf8.DecodeRuneInString(val)
			val = val[size:]
		}
		return startString[:len(startString)-len(val)], runeLength
	}
	return val, runeLength

Here’s a Golang fun fact: len(string) returns the number of bytes, while for pos := range string returns start indexes of each rune. Using this, we can rewrite the same logic like so:

func truncateString(val string, runeLimit int32) (string, int32) {
	var n int32
	for pos := range val {
		if n >= runeLimit {
			return val[:pos], n
		}
		n++
	}
	return val, n

This avoids iterating over the string twice and avoids extra string slicing logic.

You can view the full results here.

Doltgres allows users to change the format of the date output through session variables. So during the wire formatting stage, we must call GetDateStyleOutputFormat(), which acquires a mutex and does a bunch of string operations. The DATE style shouldn’t change within a query, so it makes no sense to do all these steps for each row and each DATE field. Caching the format in the context, netted us 10%-14% improvement on the index_scan, table_scan, and types_table_scan benchmarks.

You can view the full results here.

Lastly, we made the *_scan benchmarks a faster by adjusting this single line.

-return sqltypes.MakeTrusted(sqltypes.Text, types.AppendAndSliceString(dest, value)), nil
+return sqltypes.MakeTrusted(sqltypes.Text, encodings.StringToBytes(value)), nil

This was a relic from copying over code from Dolt, where the SQL() methods are able to share a large byte buffer to reduce memory allocations. This isn’t possible in Doltgres, so appending value to dest here is just wasting a copy operation as we can just use value directly. As a result, index_scan, table_scan and types_table_scan saw a 8%-13% improvement.

You can view the full results here.

Collections#

Doltgres implements Collections which hold both built-in and user-defined functions, views, procedures, types, etc. However, we were being very inefficient by loading these collections every query…sometimes multiple times! On our first pass, we cached Collections per query, reducing the number of loads. This gave us a decent performance bump across all the benchmarks (around 5%-12%).

Later on, we improved this further by simplifying Collections storage. This resulted in another decent bump across the board, so around 5%-15% improvement.

You can view the full results here.

Analyzer Improvements#

The analyzer is an essential component to improving the query performance in Doltgres, and ours could use a lot of improving. Here is some of the work we did to analysis and costing that has led to better latency in Doltgres (and Dolt).

Fix Covering Indexes#

This performance optimization is actually bug fix and the first Doltgres performance improvement I made chronologically. Dolt was missing a case for sql.ExtendedType, which Doltgres explicitly uses, so indexes weren’t getting used properly. Afterwards, we received a nice 21.5% bump in throughput for covering_index_scan and 11.5% for select_random_ranges.

You can view the full results here.

No Index GroupBy#

After some investigation, it appears that sometimes it is better to perform a full table scan rather than lookups through a secondary index. This is because non-covering secondary indexes perform two lookups, making them expensive for filters with low selectivity. We adjusted our coster to include a full table scan option when assigning indexes and added some heuristics.

As a result, median latency on the groupby_scan benchmark saw a 43.80% improvement. You can view the full results here.

We have previously discussed this optimization in greater detailed in this blog.

Conclusion#

For fun, here are all the latency benchmarks we have compared against Postgres:

test	dolt	doltgres	mysql	postgres	dolt vs postgres	doltgres vs postgres	mysql vs postgres
covering_index_scan	2.35	2.48	17.32	18.28	0.13	0.14	0.95
groupby_scan	63.32	74.46	134.90	40.37	1.57	1.84	3.34
index_join	1.93	2.22	3.43	1.82	1.06	1.22	1.88
index_join_scan	1.32	1.61	4.25	0.69	1.91	2.33	6.16
index_scan	196.89	484.44	344.08	183.21	1.07	2.64	1.88
oltp_point_select	0.25	0.37	0.19	0.15	1.67	2.47	1.27
oltp_read_only	5.00	6.55	3.62	2.66	1.88	2.46	1.36
select_random_points	0.52	0.73	0.35	0.22	2.36	3.32	1.59
select_random_ranges	0.64	1.04	0.38	0.42	1.52	2.48	0.90
table_scan	196.89	484.44	344.08	183.21	1.07	2.64	1.88
types_table_scan	442.73	1235.62	746.32	434.83	1.02	2.84	1.72
oltp_delete_insert	6.21	6.79	7.70	2.22	2.80	3.06	3.47
oltp_insert	3.13	3.43	4.03	1.10	2.85	3.12	3.66
oltp_read_write	11.24	13.46	8.90	4.41	2.55	3.05	2.02
oltp_update_index	3.30	3.62	4.33	1.14	2.89	3.18	3.80
oltp_update_non_index	3.02	3.30	4.10	1.14	2.65	2.89	3.60
oltp_write_only	6.21	6.91	5.18	1.82	3.41	3.80	2.85
types_delete_insert	6.79	7.17	8.28	2.30	2.95	3.12	3.60
avg_mult					1.92	2.59	2.55

Using some old tricks and writing some new ones, we’ve trimmed a lot of fat from Doltgres; in fact, we’re about as lean as MySQL (which is apparently not that lean standing next to Postgres). We are still 2.6x slower than Postgres, so we have a long ways to go. Dolt stands at 1.92x, so the theoretical best Doltgres can do (as far as we know) is under 2x. Feel free to chat with us on Discord or file a Github issue.

Doltgres 1.0

Zach Musgrave — Thu, 06 Aug 2026 00:00:00 GMT

At DoltHub, we’ve been in the version-controlled database business for eight years now. We made Dolt, the world’s first version-controlled SQL database, and after a few years in beta it hit 1.0 in 2023. It’s now running on thousands of machines around the world every day, powering as many interesting version-controlled applications.

Since we first announced Dolt, we’ve gotten one question more than any other:

What about a Postgres version?

Dolt speaks the MySQL dialect and wire protocol, so it’s compatible with any tool or library that can connect to MySQL. At the time we started writing the database, MySQL was far and away the most popular free SQL database, but that was already starting to change. Postgres was gaining ground and continues to do so. Today, few companies are building new products on top of MySQL. Nearly everyone chooses Postgres for new work. This means that most of the new generation of database application engineers are familiar with Postgres’s SQL dialect, toolchain, and ecosystem.

Doltgres is our way of meeting those customers where they live. Doltgres combines the performant Git-style version control that makes Dolt unique with full Postgres compatibility. Your favorite Postgres workbench or connection library can connect to Doltgres just like a normal Postgres database, but with extra system tables and functions for full version control of all schema and data.

It’s been almost three years since we announced Doltgres’s alpha release and about 18 months since we announced Doltgres Beta. Today is the 8th anniversary of DoltHub Inc., and we’re excited to announce that Doltgres has finally achieved its 1.0 release.

What does 1.0 mean?#

Doltgres 1.0 means four things.

Forward Storage Compatibility
Production Performance
Postgres Compatibility
Stable Version Control Interface

Forward Storage Compatibility#

All future 1.x versions of Doltgres will be backwards compatible with 1.0.

Dolt’s storage engine has gone through a couple of backwards-incompatible changes since its original beta, including one that required existing customers to migrate their data. Doltgres has likewise explored different ways of storing and versioning its data leading up to the 1.0 release, but managed to do so in a way that didn’t require any customer data migration.

With Doltgres 1.0 we are committing to the current storage format used for all data. There will be no backwards-incompatible storage changes in any 1.x release of Doltgres.

Production Performance#

Doltgres 1.0 has production level query performance.

Dolt is faster than MySQL on the standard sysbench performance suite. Doltgres uses Dolt’s underlying storage and query engine, so that same performance applies to Doltgres as well.

However, MySQL itself is roughly 2-3 times slower than Postgres. This means that Doltgres is about 2.7 times slower than Postgres as of this launch. But there are two pieces of good news here. First, databases are very fast. We’re talking about the difference between 0.2 milliseconds for a query versus 0.5 milliseconds. In practice, any difference in performance for typical OLTP queries in a database-backed application is dominated by network latency, with the database itself being a relatively small part of overall measured latency. Second, we’re continuing to make improvements to Dolt’s performance over time. We started out 5x slower than MySQL and have now passed it in performance.

Doltgres is slower than Postgres but still more than fast enough to handle your production workload. And it will only get faster in future releases.

Postgres Compatibility#

Doltgres 1.0 is 99% Postgres compatible.

Doltgres measures Postgres compatibility in two primary ways:

A test suite of 5.6 million SQL queries that are verified against real Postgres for correctness. These originally come from the sqllogictest project, which was developed for SQLite3. Doltgres currently scores over 99% on this suite.
Comprehensive integration tests of different client libraries in different languages. Currently over 20 have official support, with more being added all the time.

Compatibility has been our highest priority for the last year of development. We have imported many hundreds of real-world Postgres dumps and written tests for dozens of client libraries. This means we’re very confident your Postgres schema and queries will work in Doltgres. If you find that they don’t, we promise to fix the problem in 24 hours.

Stable Version Control Interface#

Doltgres 1.0 version control interfaces will not change.

The system tables and functions that implement the version control features of Doltgres have been battle-hardened by years of production use in Dolt. You can write applications that use them and be assured they will not change in any 1.x release of Doltgres.

We’ll of course continue innovating by adding new version control features and making the existing ones work better and faster. But the code and queries you write today will continue working for every 1.x release.

What’s next?#

Doltgres 1.0 is just the beginning. You can read our roadmap for details, but here are some highlights.

Support for common extensions including PostGIS
Vector indexes
Row-level security
Collation support
Better DDL support

Paying customers get write access to the roadmap.

Conclusion#

If you’ve been interested in a version-controlled SQL database but have been waiting for the Postgres version, this is your signal to try it out now. The current version has been years in the making and represents a lot of hard work and innovation by the team. We’re very excited for you to try it and to tell us what you think.

Questions about using the 1.0 release? Find a bug you want fixed? Come by our Discord to talk to our engineering team and meet other Doltgres users.

No Index GroupBy Optimization

James Cor — Mon, 03 Aug 2026 00:00:00 GMT

You may have heard that we’re launching Doltgres 1.0 on August 6th. As part of the launch, we’ve been improving Doltgres’s performance on Sysbench Latency. This blog focuses on one of the optimizations we made, specifically focusing on groupby_scan.

Discovering the Optimization#

Doltgres reuses large portions of Dolt and GMS, so it’s not unreasonable to assume that Doltgres should perform similarly to Dolt. Comparing latencies across the different platforms helps us highlight where we are underperforming and where we should focus our attention.

benchmark	dolt	doltgres	mysql	postgres
covering_index_scan	2.35	2.48	17.01	17.95
groupby_scan	144.96	147.61	144.97	40.37
index_join	1.93	2.30	3.43	1.82
index_join_scan	1.32	1.70	4.18	0.67
index_scan	219.36	493.24	350.33	179.94
oltp_point_select	0.25	0.39	0.19	0.15
oltp_read_only	5.00	6.79	3.68	2.66
select_random_points	0.52	0.80	0.36	0.22
select_random_ranges	0.65	1.21	0.39	0.42
table_scan	207.82	475.79	350.33	179.94
types_table_scan	458.96	1213.57	759.88	427.07
oltp_delete_insert	6.21	6.91	7.70	2.22
oltp_insert	3.19	3.89	4.10	1.10
oltp_read_write	11.24	14.46	8.90	4.33
oltp_update_index	3.30	3.82	4.41	1.14
oltp_update_non_index	3.02	3.55	4.18	1.12
oltp_write_only	6.32	7.43	5.18	1.79
types_delete_insert	6.79	7.56	8.43	2.30

Focusing on groupby_scan, we see that Postgres is somehow outperforming Dolt, Doltgres, and even MySQL by a significant margin; it’s over 3x faster. To see what they were doing, I ran EXPLAIN on each of the databases.

Dolt:

sbtest/main*> explain plan SELECT year_col, count(year_col), max(big_int_col), avg(small_int_col) FROM sbtest1 WHERE big_int_col > 0 GROUP BY year_col, set_col ORDER BY year_col;
+---------------------------------------------------------------------------------------------------------------------+
| plan                                                                                                                |
+---------------------------------------------------------------------------------------------------------------------+
| Project                                                                                                             |
|  ├─ columns: [sbtest1.year_col, count(sbtest1.year_col), max(sbtest1.big_int_col), avg(sbtest1.small_int_col)]      |
|  └─ Sort(sbtest1.year_col ASC)                                                                                      |
|      └─ GroupBy                                                                                                     |
|          ├─ select: AVG(sbtest1.small_int_col), COUNT(sbtest1.year_col), MAX(sbtest1.big_int_col), sbtest1.year_col |
|          ├─ group: sbtest1.year_col, sbtest1.set_col                                                                |
|          └─ IndexedTableAccess(sbtest1)                                                                             |
|              ├─ index: [sbtest1.big_int_col]                                                                        |
|              ├─ filters: [{(0, ∞)}]                                                                                 |
|              └─ columns: [small_int_col big_int_col set_col year_col]                                               |
+---------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

Doltgres:

postgres=> explain SELECT year_col, count(year_col), max(big_int_col), avg(small_int_col) FROM sbtest1 WHERE big_int_col < 0 GROUP BY year_col, set_col ORDER BY year_col;
                                                                 plan                                                                  
---------------------------------------------------------------------------------------------------------------------------------------
 Project
  ├─ columns: [sbtest1.year_col, count(sbtest1.year_col) as count, max(sbtest1.big_int_col) as max, avg(sbtest1.small_int_col) as avg]
  └─ Sort(sbtest1.year_col ASC)
      └─ GroupBy
          ├─ select: COUNT(sbtest1.year_col), MAX(sbtest1.big_int_col), avg(sbtest1.small_int_col), sbtest1.year_col
          ├─ group: sbtest1.year_col, sbtest1.set_col
          └─ IndexedTableAccess(sbtest1)
              ├─ index: [sbtest1.big_int_col]
              ├─ filters: [{(NULL, 0)}]
              └─ columns: [small_int_col big_int_col set_col year_col]

MySQL:

mysql> explain analyze SELECT year_col, count(year_col), max(big_int_col), avg(small_int_col) FROM sbtest1 WHERE big_int_col > 0 GROUP BY year_col, set_col ORDER BY year_col;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                    |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Sort: sbtest1.year_col, sbtest1.set_col  (actual time=16.4..16.4 rows=765 loops=1)
    -> Table scan on <temporary>  (actual time=15.9..16.1 rows=765 loops=1)
        -> Aggregate using temporary table  (actual time=15.9..15.9 rows=765 loops=1)
            -> Filter: (sbtest1.big_int_col > 0)  (cost=995 rows=4896) (actual time=0.222..7.37 rows=4896 loops=1)
                -> Table scan on sbtest1  (cost=995 rows=9707) (actual time=0.219..6.36 rows=10000 loops=1)
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

Postgres:

postgres=# explain analyze SELECT year_col, count(year_col), max(big_int_col), avg(small_int_col) FROM sbtest1 WHERE big_int_col < 0 GROUP BY year_col, set_col ORDER BY year_col;
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Sort  (cost=427.98..429.89 rows=765 width=55) (actual time=6.003..6.057 rows=765 loops=1)
   Sort Key: year_col
   Sort Method: quicksort  Memory: 84kB
   ->  HashAggregate  (cost=381.77..391.34 rows=765 width=55) (actual time=5.183..5.672 rows=765 loops=1)
         Group Key: year_col, set_col
         Batches: 1  Memory Usage: 297kB
         ->  Seq Scan on sbtest1  (cost=0.00..318.00 rows=5102 width=17) (actual time=0.005..2.315 rows=5104 loops=1)
               Filter: (big_int_col < 0)
               Rows Removed by Filter: 4896
 Planning Time: 0.711 ms
 Execution Time: 6.327 ms
(11 rows)

Interesting, Dolt and Doltgres are using the secondary index defined over big_int_col, while MySQL and Postgres just perform a full table scan. Since the values in big_int_col are uniformly distributed around 0, the filter where big_int_col > 0 excludes roughly half the columns. It appears the MySQL and Postgres analyzer is smart enough to recognize that the additional lookup is suboptimal.

The flame graph supports this conclusion.

Here, we see that a large portion of the CPU is spent in prolly.Map.Get, which is the secondary key lookup.

Optimization#

Currently, our analyzer will always pick an index when applicable because we assume that will always be better. Evidently, we have discovered that isn’t always the case. We need to modify the existing coster to consider full table scans depending on how well the index filters the results. Fortunately, we implemented statistics a while ago, and we can use the histograms there to get a good estimate of how selective the filter is.

We added these new heuristics to the coster:

A primary key is always better than no index
A secondary index should only be used over a full table scan if it selects fewer than 25% of rows
A covering index is always better than no index

The rest of the coster remains the same. In the future, we should take into consideration things like the complexity of the filter, if the table can fit into memory, size of output row, etc., but this is good enough for now. If you’d like to read the implementation in greater detail, you can check out these PRs:

However, it took some extra work to carry these performance benefits over to Doltgres. While Doltgres does use the same costing logic, statistics weren’t even enabled. After enabling statistics, fixing some bugs, and adding some logic to get histograms working, these are the results:

benchmark	dolt	doltgres	mysql	postgres
covering_index_scan	2.35	2.43	17.01	17.95
groupby_scan	62.19	82.96	144.97	40.37
index_join	1.93	2.30	3.43	1.82
index_join_scan	1.32	1.67	4.18	0.67
index_scan	204.11	484.44	350.33	179.94
oltp_point_select	0.25	0.40	0.19	0.15
oltp_read_only	4.91	6.55	3.68	2.66
select_random_points	0.52	0.74	0.36	0.22
select_random_ranges	0.65	1.04	0.39	0.42
table_scan	204.47	475.79	350.33	179.94
types_table_scan	458.96	1213.57	759.88	427.07
oltp_delete_insert	6.21	6.79	7.70	2.22
oltp_insert	3.19	3.89	4.10	1.10
oltp_read_write	11.24	14.21	8.90	4.33
oltp_update_index	3.30	3.82	4.41	1.14
oltp_update_non_index	3.02	3.49	4.18	1.12
oltp_write_only	6.32	7.30	5.18	1.79
types_delete_insert	6.79	7.43	8.43	2.30

On Dolt, this brought down the latency for groupby_scan from 144.96ms to 62.19ms; this is a 57.1% improvement! On Doltgres, groupby_scan latency decreased from 147.61ms to 82.96ms, which is a 43.80% improvement. With these optimizations, Dolt’s latency for groupby_scan is less than half of MySQL’s. Unfortunately, Postgres still pulls way ahead with their latency being less than half that of Doltgres’s.

While we were focused on groupby_scan, there were also some improvements to index_scan. Dolt went from 219.36ms down to 204.11ms, which is a 6.95% improvement. Doltgres went from 493.24ms down to 484.44ms, which is a 1.78% improvement. Interestingly, a byproduct of this optimization is that the index_scan and table_scan benchmarks are essentially the same now. The resulting plans from both these queries avoid the secondary index, making them both full table scans with a filter.

Conclusion#

We continue to bring performance improvements to both Dolt and Doltgres. Making our coster just a little bit smarter has resulted in our groupby_scan benchmarks running two times faster. Stay tuned to hear more about the performance improvements included in the upcoming Doltgres 1.0 release! Feel free to chat with us on Discord or file a Github issue.

DoltHub Blog - Latest Posts

Finding Bugs Using Join Implication Reasoning

Join Implication Reasoning#

So does it use AI?#

Impact on Dolt#

Your Replica Doesn't Need to Know Everything

Why Filter a Replica?#

Wildcard Table Filters#

The Great Office Lunch Investigation#

Start a Local MySQL Source#

Start the Dolt Replica#

Dolt Version Control#

Choosing the Right Filter#

Wrapping Up#

Home and Pricing Page Redesign

Homepage#

Pricing#

Conclusion#

Postgres follows the SQL standard for UPDATE statements, unlike MySQL

UPDATE with column values from the same row#

Introducing engine overrides#

Conclusion#

Implementing Type Contracts via Mutually Referencing Type Parameters

The Problem#

What Doesn’t Work: Interfaces#

The Bad Idea: A Single Generic Interface#

Almost A Solution: Generic Interfaces#

The Correct Idea: Mutually Referential Type Parameters#

The Blind Spot#

any Type Constraints#

Ambiguity in documentation#

Conclusion#

Footnotes#

Introducing the Hosted Dolt REST API

Motivation#

Built on the DoltHub API v2#

What it covers#

Getting a token#

Creating a deployment#

1. See what you can create#

2. Create it#

3. Wait for it to start#

Inspecting a deployment#

Disabling a deployment#

Documentation#

Future work#

Conclusion#

What's New With Golang Generics

Generic Type Aliases (Added in Go 1.24)#

Recursive Type Constraints (Added in Go 1.26)#

Generic Methods (Added in Go 1.27)#

Overall Impact#

Doltgres is as Fast as MySQL is Slow

Overview#

Wire Format Serialization Improvements#

Buffered Flush#

Spooling Concurrency#

Output Function Cache#

Wire Format Improvements#

Collections#

Analyzer Improvements#

Fix Covering Indexes#

No Index GroupBy#

Conclusion#

Doltgres 1.0

What does 1.0 mean?#

Forward Storage Compatibility#

Production Performance#

Postgres Compatibility#

Stable Version Control Interface#

What’s next?#

Conclusion#

No Index GroupBy Optimization

Discovering the Optimization#

Optimization#

Conclusion#

`any` Type Constraints#