Six months ago we announced the release of Doltgres Beta, signalling that the Postgres-compatible version of Dolt was ready for integration by application developers. Beta quality means we knew there would be bugs, compatibility problems, and missing features, but we felt confident enough in the core stability of the product that people could start using it for real work. We’ve been hard at work over the last six months filling in the gaps we need to get to 1.0. What’s new since Beta launch, and where are we going next? Keep reading to find out.

Triggers

Triggers have been supported in Dolt for years, but Postgres’s trigger support is quite different from MySQL’s, which made implementing them in Doltgres a little tricky. They were the first major feature we shipped after Beta.

The syntax is a bit different if you’re used to MySQL’s, but the concept is very similar. Here’s an example.

postgres=> CREATE FUNCTION update_on_main_func() RETURNS TRIGGER AS $$
BEGIN
	DELETE FROM secondary WHERE pk = OLD.pk+100;
	INSERT INTO secondary VALUES (NEW.pk+100, NEW.v1||'_updated');
	RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=> CREATE TRIGGER update_on_main BEFORE UPDATE ON main FOR EACH ROW EXECUTE FUNCTION update_on_main_func();
CREATE TRIGGER
postgres=> UPDATE main SET pk = pk+10 WHERE pk = 1;
UPDATE 1

Triggers are still missing support for STATEMENT level triggers, so if you need those please file an issue and let us know.

Extensions

When we announced the Beta, our plan for extensions was similar to other Postgres compatible databases like CockroachDB. We were going to manually implement support for common extensions like PostGIS and not support arbitrary extensions customers might bring. But after some thought and research, we came up with what we think is a much better plan: native extension support. This is possible due to Go’s ability to integrate with C code, which makes it possible to load and execute native Postgres extensions that should work exactly as they do in native Postgres installations. This provides support not only for common extensions like PostGIS and uuid-ossp, but for any extension you might need in your production environment. Read the blog announcement for more details. If an extension you need doesn’t work, please let us know and we should be able to fix it fast.

Record types and set-returning functions

Several important Postgres functions work with or return composite values, sometimes called row() support after common syntax to create these values. It looks like this in common use:

postgres=# SELECT ROW(1,2.5,'this is a test');
           row
--------------------------
 (1,2.5,"this is a test")
(1 row)

We added support for these in late May, so you can begin using them in your database schemas or queries.

We also added support for several built-in functions that return sets, which are important for compatibilty with several popular libraries. Here’s an example of one.

SELECT a AS array, s AS subscript, a[s] AS value
FROM (SELECT generate_subscripts(a, 1) AS s, a FROM arrays) foo;
     array     | subscript | value
---------------+-----------+-------
 {-1,-2}       |         1 |    -1
 {-1,-2}       |         2 |    -2
 {100,200,300} |         1 |   100
 {100,200,300} |         2 |   200
 {100,200,300} |         3 |   300
(5 rows)

You may not run one of these types of queries yourself, but if you use a Postgres library like SQLAlchemy it’s doing it for you under the hood. In fact, support for these two features allowed us to get Doltgres working with our SQLAlchemy demo application.

Better support for pg_catalog tables

The pg_catalog system schema contains metadata for all the types and other database entities in a Postgres installation. Customers don’t typically interact with it directly, but supporting it is important for compatibility with various tools and libraries that query it to determine the available types, tables, functions, etc. Doltgres launched with decent support for the pg_catalog tables, but since then we’ve found and closed a number of gaps revealed by integrating with differnt clients and tools. If you tried a tool that didn’t work with Doltgres, there’s a good chance this was the reason. There’s more work to be done on this front, but we’re making rapid progress on compatibility and hope to iron out the remaining wrinkles over the next few months.

What’s next on the roadmap

Compatibility is the biggest remaining issue for Doltgres to achieve 1.0. We have hundreds of .pgdump files from real-world Postgres databases, and are crunching through missing syntax and features as fast as we can. Our goal is that any Postgres dump can be imported into Doltgres without a hitch, and while this is true for many databases there is a long tail of features we need to support to get there. 1.0 will mean that we are confident any Postgres dump you bring to Doltgres will just work, and that’s our main focus for the next several months. If try to import a Postgres dump and something doesn’t work, please let us know.

As for large language features, we’re almost done. We’re currently in the final phases of implementating of the last ones: stored procedures. As with triggers, Dolt has supported them for years, but Postgres’s implementation and syntax for this features is different enough to make this integration pretty tricky. But they’re almost done, and we’ll be announcing support soon. Watch this blog for updates.

For the full set of features on our roadmap and estimated delivery dates, please refer to our documentation.

Looking forward to 1.0

Dolt hit 1.0 a little more than two years ago after several years of development and beta releases. We expect that Doltgres will follow a similar path, and anticipate hitting a 1.0 release approximately six months from now, one year from Beta launch.

Here’s what we said 1.0 meant for Dolt:

1. Forward Storage Compatibility
2. Production Performance
3. MySQL Compatibility
4. Stable Version Control Interface

We’ll hold ourselves to the same standard for Doltgres. Luckily for us, the performance and version control stability (items 2 and 4) are mostly solved for Doltgres because it’s built on Dolt (although some aspects of Doltgres’s performance will need to be addressed separately). That leaves two items:

• Forward Storage Compatibility: Doltgres uses new serialization techniques for the features and types found in Postgres but not in MySQL. We won’t declare 1.0 until we are confident these formats are stable, meaning no data migrations for other 1.x releases.
• Postgres Compatibility: Postgres has a much, much larger surface area of features than MySQL does, and we have not implemented all of it. We won’t declare 1.0 until we are confident we have covered 99% of what typical customers require.

For people shopping for a version-controlled database and not choosy about the dialect or SQL feature set, our recommendation continues to be to choose Dolt. It’s already feature complete and production quality, backing thousands of customer applications.

But if you feel strongly about the Postgres dialect and ecosystem, then please do try Doltgres instead. It’s ready to build on today, and should be stable enough and performant enough to write a version-controlled application against. You may encounter bugs and missing features, but we’ll fix them for you in 24 hours or less.

And if you’re not quite ready to change your production database from Postgres, consider starting up a Doltgres replica. You get a diff-able change log of your Postgres primary basically for free.

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 beta release? Find a bug you want fixed? Come by our Discord to talk to our engineering team and meet other Doltgres users.