At DoltHub, we build Dolt, the world’s first version-controlled SQL database. Dolt supports Git-like version control on top of a MySQL-compatible SQL engine—you can branch, diff and merge your SQL database, as well as perform Git-analogous remote operations, such as push, pull and clone. We also have products that target compatibility with other SQL syntaxes, including Doltgres and DoltLite.

We’re convinced that Dolt’s branch-and-merge-your-database model is a great fit for data collaboration, development, auditing, application version control, and agentic workloads. Agents need branches.

It’s increasingly common for other database offerings to provide a way to cheaply make isolated copies of an existing database whose contents evolve independently going forward. Offerings like Neon and Tiger Data are hosted database solutions that allow you to clone your live environment. PostgreSQL 18, released a little over a year ago, came equipped with its own native feature which made it cheaper to create clones of existing databases in some contexts.

This blog post briefly describes the new PostgreSQL functionality, based on file_copy_method=clone, and compares it with Dolt’s approach to branching. It looks at the use cases that cloning satisfies and where true branching can add value on top of them.

Cloning vs. Branching#

PostgreSQL has the ability to create a new database as a copy of an existing database. Indeed, almost all databases are created as such, because CREATE DATABASE actually works by copying an existing database. In common operation, these databases are relatively clean template databases that only include system-level entities defined by PostgreSQL itself. Sometimes there is site-local specialization such as default extensions installed and enabled as well.

But any PostgreSQL database can be used as the copy source for a new database just by supplying it in the TEMPLATE [database_name_here] parameter of the CREATE DATABASE statement. In PostgreSQL 18, two available options combine to make this, under the right conditions, a true copy-on-write copy. The resulting clone can be done very cheaply, both in wall-clock time and storage overhead, compared to the size of the source database itself.

The two settings are the STRATEGY parameter on the CREATE DATABASE statement and the file_copy_method server configuration setting. Supplying STRATEGY=FILE_COPY will cause Postgres to copy the database files from source to destination, instead of copying the source database block-by-block into the destination database—the latter can be more efficient when the source database is small. The server config file_copy_method=clone makes PostgreSQL use kernel-level file clone functionality, instead of copying files through a series of read(2)/write(2) when performing these database copies. On Linux, copying the data from the source to the destination file corresponds to a call to copy_file_range(2). On certain file systems, that copy_file_range(2) call can in turn create a shallow, copy-on-write copy of the underlying data blocks associated with the contents of the file. On Linux, file systems where this will potentially be cheaper, depending on configuration, include ZFS, XFS and Btrfs.

The end result is that you can get relatively cheap clones of your existing PostgreSQL 18 database when the underlying filesystem and its configuration supports block sharing for copy_file_range. Concretely, the PostgreSQL syntax looks like:

SET file_copy_method = 'clone';
CREATE DATABASE experiment TEMPLATE dev STRATEGY = FILE_COPY;

There are a few operational restrictions regarding the general mechanism in PostgreSQL as well. Two major ones are:

• A PostgreSQL connection can normally only run queries against one database at a time, so no “cross-branch” queries are possible without some extra legwork. Things like postgres_fdw can be used to work around this restriction in some cases.

• PostgreSQL requires there to be no other sessions connected to the source database when it is used as a template database, and it blocks all new connections to it until the database creation is completed. This can be difficult to coordinate across clients for an actual live database, depending on its operational needs. The FILE_COPY strategy additionally causes system checkpoints before and after the CREATE DATABASE work, which can affect performance on a live PostgreSQL cluster.

On Doltgres, the procedure is quite different. Instead of making a new database, you create a new branch within the existing database.

SELECT dolt_branch('experiment', 'main');

This does minimal work, just inserting a new branch, experiment, into the list of branch heads associated with the database, pointing it at the same commit and the same database value as the existing main branch.

The fundamental difference is that the Doltgres branch is in an ongoing relationship with other branches across the database. They have diverging commit histories that share a common ancestor. They can be diff’d against each other and edits that are unique to each branch can be identified and operated on in a principled way.

In contrast, the new database in PostgreSQL is just a copy of the existing database at a point in time. Going forward, as independent edits are made on both the destination and the source, there is no versioned merge base and thus no way to identify which edits came from where or when there are conflicting edits vs. just edits on one side or the other.¹

The Branch Point#

When is the copy a good fit? It gets the job done quite well in a number of common use cases. Some of the major ones that we see often include:

• Developing migrations. A developer can make an independent clone of an existing database to iterate on changes to the schema and the domain tables in the form of migrations. The artifact being produced isn’t changes to the actual database, per se, but a set of changes, the migration, to apply to any number of environment-specific databases.

• Running CI. A CI run starts from a known state and runs the proposed software artifact through a battery of tests, allowing it to make independent changes without the risk of affecting other runs later. At the end of the CI process, the clone is no longer needed and can be thrown away or retained as desired. No changes to the clone itself are meant for future consumption.

• Retaining queryable historical state. Cheap clones allow for retaining a queryable, online snapshot of the state of things at various times in the past. This might be done as part of a financial close, for example. It can also be useful during an operational investigation—when something anomalous happens with the database or the system, cheaply capturing and retaining the state of the database will make forensics and investigation possible in the future.

• Creating derived datasets. Examples include cloning prod and anonymizing it to seed the preprod database, or cloning a dataset multiple times and then filtering or anonymizing it before passing it on to specific clients.

For these cases, inexpensive clones of the database are often enough, at least as a starting point. With no need for the source and destination database to interact going forward, a clone can work well.

The Case for More Than Clones#

But requirements can change and some use cases really do benefit from having an actual branch. Actual branches shine when there is further interaction between the versions of the database going forward, such as a need to review or reconcile changes across versions. It’s surprisingly easy for the use case to evolve in such a way that history and branch relationships provide a lot of value.

Take the derived dataset use case, for example. As soon as a customer wants to be able to maintain their own changes while still being able to integrate updates from upstream, a true branch which can resolve the merge against a common ancestor is a big benefit. It can be a benefit to the publisher as well—the model easily allows for the downstream consumer to both make edits and to propose those edits for publication upstream.

The historical queryable state is similar. Enabling a query of the diff across the historical states enables you to answer questions about what rows changed between one version of the database and the next. When investigating operational anomalies it can help zero in on what’s actually important and unlock powerful differential reporting.

A content management or publishing platform is another common use case. Edits to the data are made on independent branches. Those changes can be reviewered before they are approved and approved changes can be published via merge.

These are all places where having history, a common ancestor, diffs and merges changes the shape of the solution.

Conclusion#

Database offerings increasingly support cheap clone operations to provide independent copies of an existing online database. Creating clones cheaply is a great feature and it’s appropriate for a number of common use cases—anytime you need a disposable test environment, a queryable snapshot of the database or an independently edited copy of it going forward. PostgreSQL itself now comes with support for this, subject to the operational constraints mentioned above. Whenever you need to be able to review changes to the actual database, compare versions between divergent histories, retain local changes while refreshing from the original source, or integrate changes from multiple writers, Dolt’s model is a better fit than just copies. If you’d like to learn more, drop by our Discord server.

Footnotes#

1. Extra tooling can sometimes accomplish reconciliation of edits. For example, schema changes can allow for tracking provenance on rows and can implement soft delete. Or a merge base can be kept around as another copy which doesn’t change and is used as a reference during reconciliation. ↩