Note: We released a Postgres version of Dolt called DoltgreSQL.

It's been over a year since I published the original Have Postgres. Want Dolt. article. Interest in a Postgres-flavored version of Dolt has not waned. On average, about one person per week shows up and asks for it. This likely means ten other people want it but are too shy to ask. It's a stampede.

It's time for an update.

We call the Postgres-flavored Dolt "Doltgres". We even bought the doltgresql.com domain. We could also call it pgdolt (like pgvector) or PostDolt (like PostGIS). Let us know which one you like.

For those of you who are new here, Dolt is the world's first version controlled SQL database. Dolt is MySQL-compatible for reasons you can read about here.

Where we were in 2022

In the original article, I discussed why Dolt is MySQL-flavored, noting Dolt contains no MySQL code. If you're worried about Oracle support, you only need a MySQL client to use Dolt. Dolt fully owns and controls its own free and open source server code. Dolt is written from the storage engine up to provide fast version control features like diff, branch, and merge.

We provided two migration options for Postgres users, both of which we wrote and support:

1. SQL Sync. A Python/SQLAlchemy tool to sync tables.
2. pg2mysql. A pgdump to mysqldump converter.

We recommended using pg2mysql to import your converted pgdump files into Dolt and just make the switch to the MySQL client and dialect in your application. Most Object Relational Mappers make this easy.

At the end, I teased Doltgres, a true Postgres-flavored Dolt. Aaron had even started looking at the Postgres File-based Foreign Data Wrapper interface. I said the project would ship in late 2022 or early 2023.

A 2023 Update

Well, it's mid-2023 and there is no Doltgres. What happened? The MySQL-flavored version of Dolt continued to attract new users and use cases so we had enough to keep us busy without more Postgres users. That said, we did not completely ignore Postgres users.

Dolt works with mysql_fdw

You can now access Dolt tables from Postgres using the mysql_fdw. This means you can expose Dolt tables in your Postgres instance just like you would MySQL tables. You can access Dolt system tables the same way. Dolt system tables give you access to version history, like an audit log of every cell.

Unfortunately, the mysql_fdw does not expose procedures. We use custom procedures to provide version control write functionality. So with the mysql_fdw, you cannot make Dolt writes, like creating a branch or making a Dolt commit. So, accessing Dolt via the mysql_fdw is more of a read-only experience. Version control writes need to happen out of band via a MySQL client.

One possible set up is to use DoltHub or DoltLab to make writes to your Dolt database, pull those writes to a running Dolt server, and expose those versioned writes to Postgres via the mysql_fdw. This would make sense for important domain mapping tables you want to version like IP to Geo or Country to Country Code. You can use Pull Requests to review updates. This set up would give you enhanced data quality on the important mapping tables.

Dolt 1.0

Dolt went 1.0 in May 2023 so the MySQL-flavored Dolt is ready for your production applications. If you were worried about the stability and performance of a pre-1.0 database, Dolt is past that now. Dolt is still MySQL-flavored but it's production ready. Again, Dolt contains no MySQL code so our bugs and performance are our own. We and our open source community have full power to fix them. You are not at Oracle's mercy.

DoltHub and DoltLab Migrated from Postgres to Dolt

We migrated the metadata databases of DoltHub and DoltLab from Postgres to Dolt. In this blog, Dustin does a good job of explaining the migration steps. If you go on DoltHub right now, the metadata is backed by Dolt, not Postgres. If we can do it, so can you.

pg2mysql is improved

In the process of migrating DoltHub from Postgres to Dolt we used pg2mysql to convert pgdump files to mysqldump files. In that process, we found and fixed issues in pg2mysql. We are happy to support this open source dump conversion tool.

Doltgres

Alright, so what about Doltgres? We decided to fund the project! Daylon will start on it after he finishes up with our first version of fulltext indexes. We're still deciding on exactly what Doltgres is. There are two options both with pros and cons. We're leaning towards the foreign data wrapper around storage approach but we're still undecided.

A Foreign Data Wrapper around Storage

Using a file-based foreign data wrapper to create a new type of Postgres table is often called a Postgres extension.

With this implementation, you would need Postgres and the Doltgres extension installed. Then you could create Dolt versioned tables that exist alongside your normal Postgres tables. For reads against history and version control metadata, we believe our system table approach will work. For version control writes, like commit and branch, we hope our procedure-based approach can work but we're a little less confident the Postgres foreign data wrapper interface provides us all the hooks we need. This is an area of research for us.

The other main issue is that Dolt's storage engine in written in Golang and Postgres is written in C. We would need to expose Dolt's storage engine to C somehow. We've tried making Dolt a C library before. We are somewhat concerned with performance and reliability of this approach.

Pros:

• It's actually Postgres. "We only run Postgres" users can use it.
• Postgres engine performance and stability. You get the Postgres SQL engine, not the Dolt engine.

Cons:

• Dolt storage is written in Golang. Need a Golang to C bridge.
• Complicated to set up and deploy. However, this set up is very common (ie. pgvector and PostGIS).
• Potentially some interface challenges around version control functionality.

A Custom Postgres server and parser

This flavor of Doltgres looks a lot more like our current architecture. For those of you unaware we use a fork of Vitess for MySQL-flavored parsing and serving. We use go-mysql-server as our SQL engine. Both of these were chosen because of their pure Golang implementations. To make Dolt Postgres-flavored but stay in pure Golang and retain full control over our interfaces, we would need Postgres-flavored versions of Vitess and go-mysql-server.

In this case, we would build, open source, and support Postgres-flavored versions of Vitess and and go-mysql-server written in Golang. People use Vitess and go-mysql-server for a number of other use cases. Postgres-flavored versions of these packages would be useful to the Golang and Postgres communities, beyond being the core of our Doltgres offering.

Pros:

• Easy to use. Add a Postgres mode to dolt init and dolt sql-server CLI commands.
• We'll get a couple new generally useful Golang open source packages.
• We have full control.

Cons:

• Not actually Postgres, just Postgres-flavored SQL and client.
• Potentially more work to build.

Conclusion

We haven't forgotten about you Postgres folks. Since 2022, we've added support for the mysql_fdw. Dolt went 1.0 and we migrated our production database from Postgres to Dolt, proving Dolt is a viable alternative to Postgres. Moreover, we're about to start on what some people really want, Doltgres! We have a couple architecture options to choose from. We're leaning towards the foreign data wrapper approach. If you have any opinions on what you want, stop by our Discord and let us know.