dolthub/doltgresql

Doltgres is Dolt for Postgres!

From the creators of Dolt, the world's first version controlled SQL database, comes Doltgres, the Postgres-flavored version of Dolt. It's a SQL database that you can branch and merge, fork and clone, push and pull just like a Git repository. Connect to your Doltgres server just like any Postgres database to read or modify schema and data. Version control functionality is exposed in SQL via system tables, functions, and procedures.

Git versions file, Doltgres versions tables. It's like Git and Postgres had a baby.

Doltgres is Beta

Doltgres is now Beta quality, which means it's ready for your production use case. There will be bugs and missing features, but we can fix most of them in 24 hours if you file an issue.

The wait is over! Now is the time to try out Doltgres and let us know what you think. Import your existing Postgres database into Doltgres with pg_dump and psql, and let us know if anything doesn't work.

If you're excited about this project, you can also help speed it along in a few other ways:

• Star this repo
• Create issues if you find bugs
• Create issues for missing functionality you want
• Contribute code for features you want (see the Contribution Guide)
• Tell your friends and colleagues

Full Documentation

Doltgres has a documentation website with extensive documentation.

Installation

To install Doltgres on Linx or Mac based systems run this command in your terminal:

sudo bash -c 'curl -L https://github.com/dolthub/doltgresql/releases/latest/download/install.sh | bash'

This will download the latest doltgres release and put it in /usr/local/bin/, which is probably on your $PATH.

Windows

Download the latest Microsoft Installer (.msi file) in releases and run it.

Docker

Doltgres publishes an official Docker image on every release:

• dolthub/doltgresql

Run it on your local Docker like this:

$ docker run -e DOLTGRES_PASSWORD=myPassword -p 5432:5432 dolthub/doltgresql:latest

Building From Source

To produce a binary from source code, run ./scripts/build.sh.

Getting Started

1. Run doltgres. This will create a postgres user and a postgres database in the current directory. The default password will be password, just like in Postgres. You can use a config.yaml file or set the DOLTGRES_DATA_DIR environment variable to use a different directory for your databases.

You can change the name and password of the super-user by setting the DOLTGRES_USER and DOLTGRES_PASSWORD environment variables before running doltgres for the first time.

$ doltgres
INFO[0000] Server ready. Accepting connections.

2. Install Postgres to get the psql tool. I used Homebrew to install Postgres on my Mac. This requires I manually add /opt/homebrew/opt/postgresql@15/bin to my path. We only need Postgres in order to use psql, so feel free to skip this step if you already have psql, or if you have another Postgres client you use instead.

export PATH="/opt/homebrew/opt/postgresql@15/bin:$PATH"

3. Open a new terminal. Connect with the following command: PGPASSWORD=password psql -h localhost -U postgres. This will connect to the postgres database with the postgres user.

$ PGPASSWORD=password psql -h localhost
psql (15.4 (Homebrew), server 15.0)
Type "help" for help.

postgres=>

4. Create a getting_started database. Create the getting_started example tables.

postgres=> create database getting_started;
--
(0 rows)

postgres=> \c getting_started;
psql (15.4 (Homebrew), server 15.0)
You are now connected to database "getting_started" as user "postgres".
getting_started=> create table employees (
    id int8,
    last_name text,
    first_name text,
    primary key(id));
--
(0 rows)

getting_started=> create table teams (
    id int8,
    team_name text,
    primary key(id));
--
(0 rows)

getting_started=> create table employees_teams(
    team_id int8,
    employee_id int8,
    primary key(team_id, employee_id),
    foreign key (team_id) references teams(id),
    foreign key (employee_id) references employees(id));
--
(0 rows)

getting_started=> \d
              List of relations
 Schema |      Name       | Type  |  Owner
--------+-----------------+-------+----------
 public | employees       | table | postgres
 public | employees_teams | table | postgres
 public | teams           | table | postgres
(3 rows)

5. Make a Dolt Commit.

getting_started=> select * from dolt.status;
   table_name           | staged |  status
------------------------+--------+-----------
 public.employees       | f      | new table
 public.employees_teams | f      | new table
 public.teams           | f      | new table
(3 rows)

getting_started=> select dolt_add('teams', 'employees', 'employees_teams');
 dolt_add
----------
        0
(1 row)
getting_started=> select * from dolt.status;
   table_name          | staged |  status
-----------------------+--------+-----------
public.employees       | t      | new table
public.employees_teams | t      | new table
public.teams           | t      | new table
(3 rows)

getting_started=> select dolt_commit('-m', 'Created initial schema');
           dolt_commit
----------------------------------
 peqq98e2dl5gscvfvic71e7j6ne34533
(1 row)

6. View the Dolt log.

getting_started=> select * from dolt.log;
           commit_hash            | committer |       email        |        date         |          message
----------------------------------+-----------+--------------------+---------------------+----------------------------
 peqq98e2dl5gscvfvic71e7j6ne34533 | postgres  | [email protected] | 2023-11-01 22:08:04 | Created initial schema
 in7bk735qa6p6rv6i3s797jjem2pg4ru | timsehn   | [email protected]    | 2023-11-01 22:04:03 | Initialize data repository
(2 rows)

7. Continue with Dolt Getting Started to test out more Doltgres versioning functionality.

Limitations and differences from Dolt

• No Git-style CLI for version control like in Dolt, only a SQL interface.
• Can't push to DoltHub or DoltLab, only custom remotes (such as on the file system or to S3).
• Backup and replication are a work in progress.
• No GSSAPI support.
• No extension support yet.
• Some Postgres syntax, types, functions, and features are not yet implemented. If you encounter a missing feature you need for your application, please file an issue to let us know.

Performance

Dolt is 1.1X slower than MySQL as measured by a standard suite of Sysbench tests.

We use these same Sysbench tests to benchmark DoltgreSQL and compare the results to PostgreSQL.

Here are the benchmarks for DoltgreSQL version 0.50.0. All figures are median latency in milliseconds.

Correctness

Dolt is 100% compatible with MySQL based on a standard suite of correctness tests called sqllogictest.

We use these same tests to measure the correctness of DoltgreSQL.

Here are DoltgreSQL's sqllogictest results for version 0.50.0. Tests that did not run could not complete due to a timeout earlier in the run.

Architecture

Doltgres emulates a Postgres server, including parsing Postgres SQL into an Abstract Syntax Tree (AST). This AST is converted to a form that can be interpreted by the Dolt engine. Doltgres uses the same SQL engine and storage format as Dolt.

Dolt has a unique architecture that allows for version control features at OLTP database performance. Doltgres uses the same architecture.