<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Axial Engineering - Medium]]></title>
        <description><![CDATA[All things Engineering at Axial - Medium]]></description>
        <link>https://medium.com/axial-engineering?source=rss----18f36b4fc78a---4</link>
        <image>
            <url>https://cdn-images-1.medium.com/proxy/1*TGH72Nnw24QL3iV9IOm4VA.png</url>
            <title>Axial Engineering - Medium</title>
            <link>https://medium.com/axial-engineering?source=rss----18f36b4fc78a---4</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 20:02:47 GMT</lastBuildDate>
        <atom:link href="https://medium.com/feed/axial-engineering" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Instant Per-Branch Databases with PostgreSQL 18’s clone file copy and Copy-on-Write Filesystems]]></title>
            <link>https://medium.com/axial-engineering/instant-per-branch-databases-with-postgresql-18s-clone-file-copy-and-copy-on-write-filesystems-1b1930bddbaa?source=rss----18f36b4fc78a---4</link>
            <guid isPermaLink="false">https://medium.com/p/1b1930bddbaa</guid>
            <category><![CDATA[software-development]]></category>
            <category><![CDATA[postgresql-database]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[copy-on-write]]></category>
            <dc:creator><![CDATA[Bob Ternosky]]></dc:creator>
            <pubDate>Mon, 12 Jan 2026 16:59:05 GMT</pubDate>
            <atom:updated>2026-01-12T16:59:04.557Z</atom:updated>
            <content:encoded><![CDATA[<p>Most developers use source code control tools (e.g. <strong>Git</strong>) and code branches to provide isolated, parallel development. However, when the application needs to interact with a database most developers do not have databases that are isolated between branches.</p><p>Per-branch databases are desirable for many of the same reasons as code branches:</p><ul><li>Parallel development</li><li>Isolation and safety</li><li>Easy rollbacks</li></ul><p>In our experience, most development shops will ensure that developers have a non-production database for use during code development. Some shops will also extend their infrastructure so that each developer gets their own database to use. But it seems extremely rare to find a setup utilizing per-branch databases.</p><p>The lack of per-branch databases are usually the result of:</p><ul><li>complexity in setting up and maintain the databases</li><li>time needed to spin up a new database</li><li>the amount of disk required to have numerous databases</li></ul><p>In this article, we’ll discuss how you can use <strong>PostgreSQL</strong> 18’s file_copy_method=clone with a <em>Copy-on-Write</em> filesystem to make per-branch database creation easy to manage, insanely fast and consume very little disk.</p><h3>Rationale: Why do developers want/need per-branch databases?</h3><p>We have stated that isolation and safety are a big part of per-branch databases. How? The benefits break down into three general categories:</p><ul><li>Schema isolation</li><li>Data isolation</li><li>Failure isolation</li></ul><h4>Schema isolation</h4><p>Let’s examine a scenario where the lack of schema isolation can cause problems.</p><p>In this scenario, we are going to implement a new feature. We need a branch from our code mainline (<strong>main</strong>) that we’re going to call <strong>ticket-one</strong>.</p><p>As part of implementing this new feature, we make a database schema change that renames a column and we make the necessary code changes for the column rename.</p><p>Once we have completed development of the feature, we create a pull request and ask for a code review and/or testing from others on our team.</p><p>If we pick up any new work before <strong>ticket-one</strong> is merged, we would need to revert the change to the database before beginning the new work. This introduces delay into the workflow. In the case that these two streams of work are concurrent for any sustained period of time, the switching cost is incurred on every context switch.</p><p>This scenario can arise if any of the following types of changes happen in your database:</p><ul><li>Rename of any object type</li><li>Data Type change of any object</li><li>Change to Triggers/Functions</li><li>Removal of any database object (columns, table, triggers, etc.)</li></ul><p>In some cases, the pain to “revert” the change could be very difficult and/or time consuming.</p><p>If we had a per-branch database setup, then the schema changes would have been made in an isolated database and there would be no issues encountered when branch switching.</p><h4>Data isolation</h4><p>Let’s examine a scenario where lack of isolation can cause problems.</p><p>In this scenario, we are going to implement a new feature. We need a branch from our mainline (<strong>main</strong>) that we’re going to call <strong>ticket-one</strong>.</p><p>As part of implementing this new feature we need to remove data from a table (not all data, but all data of a certain type perhaps). We make any code changes needed to deal with the data being removed.</p><p>An example: Remove data from a table where a column has a specific value.</p><p>Once we have completed development of the feature, we generate a pull request and ask for a code review and/or testing from others on our team.</p><p>If we pick up any new work before <strong>ticket-one</strong> is merged, we would need to revert the change to the database before beginning the new work. This introduces delay into the workflow. In the case that these two streams of work are concurrent for any sustained period of time, the switching cost is incurred on every context switch.</p><p>If we had a per-branch database setup, then the data changes would have been made in an isolated database and there would be no issues encountered when branch switching.</p><p>Data isolation means we don’t have to to worry about assumptions in application code about the existence of specific data that may have been removed on another branch.</p><p>With data isolation, we also don’t lose interesting data on any current branch during switching. This interesting data could be things like test data setup.</p><h4>Failure isolation</h4><p>Let’s examine a scenario where lack of isolation can cause problems.</p><p>In this scenario, we are going to implement a new feature. We need a branch from our mainline (<strong>main</strong>) that we’re going to call <strong>ticket-one</strong>.</p><p>As part of implementing this new feature we need to make change to a trigger function in the database. We have applied the changed trigger function to the database successfully. But while testing the updated function we realize our change is not correct (i.e. a bug).</p><p>Before we have a chance to figure out a fix for the bug, we are required to pick up a high priority production bug fix.</p><p>Since we are picking up new work, we would need to revert the change to the database before beginning the new work. This introduces delay into the workflow. In the case that these two streams of work are concurrent for any sustained period of time, the switching cost is incurred on every context switch.</p><p>If we had a per-branch database setup, then the trigger change would have been made in an isolated database and there would be no issues encountered when branch switching.</p><h3>But what about …?</h3><p>We’re sure a number of readers will immediately have thoughts about a few alternate development setups and ask “why not use this instead?”.</p><p>A few of those setups:</p><ul><li>Use a schema-only database and data generation</li><li>Use a lightweight database replacement (e.g. <strong>SQLite</strong>)</li><li>Use a versioned database (e.g. <strong>Dolt</strong>, <strong>Neon</strong>)</li></ul><p>We’ll examine each option and explain why we chose not to use it.</p><h4>Use a schema-only database and data generation</h4><p>If you have the <strong>DDL</strong> to generate your database and tools / scripts to generate data in your database you can create your own per-branch databases pretty easily.</p><p>In an ideal world, every data model would be perfect and every development database would be pragmatically populated based on the needs of the developer. Unfortunately, reality is rarely that clean. One of the benefits of our approach is that it enables significantly faster development without first having to resolve every imperfection.</p><p>Later in the document we will point out where using <strong>DDL</strong> and data generation is an acceptable step for creating a database that can be used as a template for the per-branch databases as part of our solution.</p><h4>Use a lightweight database replacement (e.g. SQLite)</h4><p>We’ve seen many articles about people using drop-in replacements for their database. They might use a super light-weight alternative database (e.g. <strong>SQLite</strong>) or something like <strong>PGlite</strong> where it runs a <strong>PostgreSQL</strong> server, but it runs fully in memory.</p><p>Using something like <strong>SQLite</strong> is problematic unless your code is using a library that supports multiple database protocols. Many shops use native libraries to talk to the database (e.g. <strong>pyscopg</strong> from <strong>Python</strong>). Switching to <strong>SQLite</strong> isn’t feasible in that scenario.</p><p>Using <strong>PGlite</strong> seems like a good option, but it has its own limitations and potential issues for some users:</p><ul><li>It requires the user of <strong>docker</strong>. Not everyone uses <strong>docker</strong> and they may not want to incur the complexity of adding it to their project.</li><li>It doesn’t support all <strong>PostgreSQL</strong> features</li></ul><p>We like to have development environments be as production-like as possible. With either of the alternative solutions you are using a database tool that is NOT the same as your production database. This can result in unexpected issues when shifting from development to production.</p><p>We feel our solution provides a better outcome than these solutions.</p><h4>Use a versioned database</h4><p>There are products available to do versioned databases. Examples:</p><ul><li><a href="https://www.dolthub.com/">Dolt</a></li><li><a href="https://neon.com/">Neon</a></li></ul><p>They provide versioned or branched databases which is the problem we’re trying to solve.</p><p>However, we chose not to use these types of products as:</p><ul><li>These products tend to cost money, where our solution is free</li><li>Any “free” alternatives would require using a 3rd party setup on top of <strong>PostgreSQL</strong>. Our solution uses native <strong>PostgreSQL</strong> as the solution so we avoid 3rd party bloat / package maintenance</li></ul><h3>Some of our specific pain points</h3><p>We have some additional specific environmental issues that also drove us down the road to our solution that you may not have to contend with:</p><ul><li>Our database must have data that is representative of production. We have some legacy data issues that make generative data problematic.</li><li>Our database is large enough to be painful to copy, but using<em> Copy-on-Write</em> makes it feasible. Even with modern solid-state drives, there are limits to how fast data can be copied. This can be exacerbated if there are any layers of virtualization involved (e.g. Docker).</li><li>Maintaining the correspondence of databases to branches while providing ergonomics is non-trivial if you want to cover the most beneficial use-cases (hot fixes/collaborative efforts)</li></ul><h3>Concerns with per-branch databases</h3><p>We’ve discussed some problems that we might solve with per-branch databases. No solution is perfect so let’s review some of the concerns that people might have with per-branch databases:</p><p><strong>Database creation/setup concerns</strong></p><ul><li>How easy is it to create a new per-branch database?</li><li>How long does it take to create a new per-branch database?</li></ul><p><strong>Disk consumption concerns</strong></p><ul><li>If the database we use is large, it becomes untenable pretty quickly to keep around numerous copies of the database.</li></ul><p><strong>Application Configuration</strong></p><ul><li>How is a branch of code configured to connect to a specific per-branch database?</li><li>How does this configuration get changed when branch switching?</li></ul><p><strong>Raw Database Access</strong></p><ul><li>How is a specific per-branch database accessed using a CLI or GUI database tool?</li></ul><p><strong>Database Cleanup</strong></p><ul><li>How are obsolete per-branch databases cleaned up once they are no longer required?</li></ul><p>We’ll address all of these concerns as we explore our per-branch database solution.</p><h3>Our goals</h3><p>Now that we’ve covered the background, we can explore our proposed per-branch database solution. Our goal is to provide developers with fully isolated, per-branch databases in their development environment, where the solution:</p><ul><li>is reasonably cross-platform with off-the-shelf tools</li><li>provides flexible choice of Operating System</li><li>works in Containers or as an installed server</li><li>can run the database locally (laptop/desktop) or remote</li><li>is programming language agnostic</li><li>does not significantly increase storage consumption</li><li>provides fast database cloning</li><li>makes it easy to match a branch with its database</li></ul><p>In the rest of this post we’ll cover:</p><ul><li>Conventions used in this document</li><li>Tools and technology required for our solution</li><li>Software Installation / Configuration</li><li>Setting up a Template Database</li><li>Branching mechanics and per-branch database setup</li><li>Utilizing database clients (CLI and GUI)</li><li>Managing database drift</li><li>Removing obsolete databases</li><li>Optional: Automated configuration</li></ul><h3>Conventions used in this document</h3><p>We will use a few formatting conventions throughout this document. In this section we’ll review the conventions.</p><p><strong>Shell Commands</strong></p><p>We will have examples in the document that show one or more commands to be run from a shell. We show the example system prompt as [bash]$. This does not require you to use the <strong>bash</strong> shell. It is merely a convention to show a command being run.</p><p>Example:</p><pre>[bash]$ cd $HOME</pre><p>NOTE: We have tested our examples on:</p><ul><li>Linux Ubuntu</li><li>OSX</li><li>Windows Subsystem for Linux</li></ul><p><strong>Placeholders</strong></p><p>In our examples we use a variety of placeholder values. You will be expected to replace these values with values specific to your environment. These placeholders take the form of an upper case string enclosed in angle brackets, for example: &lt;VAR_NAME&gt;</p><p>In the following example, you would be required to replace &lt;PG_HOST&gt;with the IP address or hostname of your <strong>PostgreSQL</strong> server:</p><pre>[bash]$  psql -X postgres://postgres@&lt;PG_HOST&gt;</pre><p>Commonly used placeholder values:</p><ul><li>&lt;PG_INSTALL&gt;: The directory that the <strong>PostgreSQL</strong> server is configured to use for storage. The default installation is typically:/var/lib/postgresql/pg_data</li><li>&lt;PG_HOST&gt;: The IP/hostname for your <strong>PostgreSQL</strong> server (e.g. 127.0.0.1, localhost etc.)</li><li>&lt;PG_PORT&gt;:The listening port your <strong>PostgreSQL</strong> server is configured to use</li><li>&lt;DB_USER&gt;: The username to use when connecting to a database</li><li>&lt;DB_PASSWD&gt;: The password to use when connecting to a database</li><li>&lt;DB_NAME&gt;: The database identifier name to use when connecting to a database (e.g. <strong>main</strong>, <strong>branch_one</strong>, etc.)</li></ul><h3>Tools and technology required for the solution</h3><p>Our per-branch database solution requires:</p><ul><li><a href="https://www.postgresql.org/">PostgreSQL</a> Server version 18+</li><li>A <em>Copy-on-Write</em> filesystem for the database file storage</li><li>Source code with each branch in its own directory</li></ul><p>Later, in the section titled <em>Automated Configuration</em> we will also utilize <strong>direnv</strong>. This is not required for the branching / database setup, but is highly recommended.</p><h3>PostgreSQL Server version 18+</h3><p>Our solution takes advantage of features introduced in <strong>PostgreSQL</strong> with version 18. The server deployment is flexible:</p><ul><li>Can be containerized or a bare-metal server</li><li>Can be run locally or remote to your application</li><li>Note: <strong>Amazon RDS</strong> will not work at the current time due to lack of a necessary feature within <strong>RDS</strong></li></ul><p>We are going to make use of a few <strong>PostgreSQL</strong> features to implement our solution:</p><ul><li>Template databases</li><li>The <em>clone</em> file copy method</li></ul><p><strong>PostgreSQL</strong> has a feature called <a href="https://www.postgresql.org/docs/current/manage-ag-templatedbs.html">Template Databases</a>. When creating a new database an existing database can be used as a template to create the new database. More simply, it will copy the template database as the new database.</p><p>This requires two steps:</p><ol><li>A database must exist that will be used as a template. By default, <strong>PostgreSQL</strong> provides a database for this purpose named <em>template1</em>. But ANY database in the server can be used as a template.</li><li>Use of the TEMPLATE keyword when creating a database Example:</li></ol><pre>CREATE DATABASE new_database_name WITH TEMPLATE template_database_name …;</pre><p>In very simplified terms, a database in <strong>PostgreSQL</strong> is manifested as a directory of files on disk and corresponding metadata in the running server. When a new database is created using a <em>Template</em> database, <strong>PostgreSQL</strong> will:</p><ul><li>Copy the directory of files from the <em>Template</em> database to a new folder that represents the new database</li><li>Update the metadata in the running server to utilize the new database folder</li></ul><p><strong>PostgreSQL</strong> has a configuration option called <a href="https://www.postgresql.org/docs/18/runtime-config-resource.html#GUV-FILE-COPY-METHOD">file_copy_method</a>. This setting controls how the data files are copied in the create from template operation. It has two possible values:</p><ul><li><em>COPY</em> — <strong>PostgreSQL</strong> will do a full physical copy of a database</li><li><em>CLONE</em> — <strong>PostgreSQL</strong> will use operating system features (if present) to share common disk blocks between databases. The operating system features that are required by <em>CLONE</em> are provided by <em>Copy-on-Write</em> filesystems</li></ul><h3>A Copy-on-Write filesystem for the database file storage</h3><p>Per <a href="https://en.wikipedia.org/wiki/Copy-on-write">Wikipedia</a>, <em>Copy-on-Write</em> is defined as follows:</p><pre>Copy-on-Write (COW), also called implicit sharing or shadowing, is a <br>resource-management technique used in programming to manage shared data <br>efficiently. Instead of copying data right away when multiple programs use it,<br>the same data is shared between programs until one tries to modify it. If no <br>changes are made, no private copy is created, saving resources. A copy is <br>only made when needed, ensuring each program has its own version when <br>modifications occur. This technique is commonly applied to memory, files, <br>and data structures.</pre><p>When the following items are combined:</p><ul><li>using a <em>Copy-on-Write</em> filesystem for the storage of a <strong>PostgreSQL</strong> database</li><li>using the <em>CLONE</em> value for <strong>PostgreSQL’s</strong> configuration of file_copy_method</li><li>creating a database from a <em>Template</em></li></ul><p>This provides benefits:</p><ul><li>The creation of the database is insanely fast. Since the <em>Copy-on-Write</em> filesystem isn’t copying the full data (it’s sharing data at the filesystem level) the copy executes much faster. The time taken will vary with your database size and your storage media (i.e. spinning disk vs SSD etc.). Generally speaking the creation will be orders of magnitude faster than full copies. On a SSD even multi-hundred GB copies can happen in less than a second.</li><li>Disk consumption for the new database will be much less than the original database. The filesystem only needs to create metadata entries about the database instead of full data copies. The amount of space required will vary with the filesystem and the size of the original database. But overall, it will generally be an order of magnitude less.</li></ul><p>As indicated above, data copies are only made when needed. As either the <em>template</em> database or a cloned database undergo change, the filesystem will need to make file copies so the two databases can diverge. The performance impact of the copies is generally pretty small since:</p><ul><li>The rate of overall data change activities in most development databases tends to be pretty small</li><li>The amount of data changed in an individual database update tends to be pretty small</li><li><strong>PostgreSQL</strong> uses <a href="https://www.postgresql.org/docs/current/wal-intro.html">Write Ahead Logging</a> (AKA WAL) which means updates to the database are very fast from the user’s perspective as writes are made to the WAL and therefore require no copies since it’s net new data. The WAL logs are processed by the database in the background and therefore the user does not experience any delays.</li></ul><p>If an application generates a LOT of writes or very large updates there could be concern about the impact. But even in those cases, by using a <em>Copy-on-Write</em> filesystem:</p><ul><li>The server will consume less disk overall than two discrete copies since only changed data is copied.</li><li>End user performance will be comparable with the use of <strong>Write Ahead Logs</strong>.</li><li>Server performance may be slightly worse than purely independent databases without shared data but the impact should be nominal. Even if it is slightly worse, we feel this is an acceptable trade off as we are running in a development environment.</li></ul><p>Example file systems that support <em>Copy-on-Write</em>:</p><ul><li>Apple File System (<strong>APFS</strong>)</li><li><strong>BTRFS</strong></li><li><strong>ZFS</strong></li></ul><h3>Source code with a directory per-branch</h3><p>A key component of our workflow is ensuring that each code branch has a full checkout of the code (a branch) in its own directory rather than switching branches within the same directory.</p><p>In our solution, we will use <strong>Git Worktrees</strong> for our source code management / branching. We are not going to dive deep into <strong>Git</strong> or <strong>Git Worktrees</strong> works. See the <a href="https://git-scm.com/docs/git-worktree">Git Worktree</a> documentation page for detailed information.</p><p>Our example <strong>Git Worktree</strong> workflow can easily be adapted to any source code management tool (or even having no source code management tool).</p><p>We want this document to be as flexible as possible to cover a variety of use cases:</p><ul><li>Developers should be able to use the OS of their choice</li><li>Developers should be able to run the <strong>PostgreSQL</strong> Server in a container or as system installed service</li><li>Support for any container technology (<strong>podman</strong>, <strong>docker</strong>, etc.) when containerized</li><li>Support for any software management tool (<strong>apt</strong>, <strong>yum</strong>, <strong>snap</strong>, raw source etc.) when system installed</li><li>The <strong>PostgreSQL</strong> server can be run locally on a laptop (or desktop) or on shared infrastructure</li></ul><p>Due to the wide variety we are not going to try to cover all of the installation / setup instructions. The installation of software is up to the developer based on their needs. Instead we’ll identify what our workflow actually requires to function:</p><ul><li>A <strong>PostgreSQL</strong> Server (minimum version 18 to support for the file_copy_method=clone feature)</li><li><strong>PostgreSQL</strong> configured to use file_copy_method = clone (in postgresql.conf)</li><li><strong>PostgreSQL</strong> configured to have it’s data storage on a <em>Copy-on-Write</em> filesystem (data_directory = &#39;/path/to/store/databases’ (in postgresql.conf)</li><li>A <strong>PostgreSQL</strong> Client (CLI or GUI doesn’t matter)</li><li>Per-branch code directories (You can adapt our workflow to avoid this, but its a bit more complicated)</li></ul><p>One extra note for those running <strong>PostgreSQL</strong> in a container. Make sure your container data storage is using a <em>Copy-on-Write</em> filesystem. A naive container configuration will end up using the container filesystem default. Unfortunately, many images use <em>ext4</em> as that filesystem and <em>e</em>xt4 does <em>not</em> support <em>Copy-on-Write</em>.</p><h3>Setting up a template database</h3><p>The first step in our process is to create a <em>Template</em> database that contains a full database for per-branch use. This requires that you have a running <strong>PostgreSQL</strong> server. There are a few ways to create the <em>Template</em> database:</p><ol><li>Create a database using <strong>pg_dump</strong> and<strong> pg_restore</strong></li></ol><p>If you want to use an existing database as the source of your template you can:</p><ul><li>Export an image from a <strong>PostgreSQL</strong> server using <strong>pg_dump. </strong>The dump file contains all database objects and table data</li><li>Create necessary users and their grants so that <strong>pg_restore</strong> will work</li><li>Import the image using <strong>pg_restore</strong></li></ul><p>2. Use one or more <strong>DDL</strong> scripts that:</p><ul><li>Create necessary users and their grants for the database</li><li>Create a database</li><li>Create your database schema(s), tables, views, functions, triggers etc.</li><li>Optional: Run a script to populate data into your tables</li></ul><p>You can use either of these methods or any other method you like to create a database.</p><h4>Creating a database using pg_dump and pg_restore</h4><p>You can use native <strong>PostgreSQL</strong> tooling to create a database <em>Template</em>:</p><ul><li>Export an existing database (e.g. <em>production</em>) using <strong>pg_dump</strong></li><li>Load the export into your local <strong>PostgreSQL</strong> using <strong>pg_restore</strong></li></ul><p>Here is an example of creating the template database using a <strong>pg_restore</strong> from a <strong>pg_dump</strong> file:</p><pre>[bash]$ psql -X postgres://postgres@&lt;PG_HOST&gt;<br>... create users/grants needed by pg_restore ...<br><br>[bash]$ psql -X postgres://postgres@&lt;PG_HOST&gt; <br>    -c &quot;CREATE DATABASE development_template OWNER &lt;DB_USER&gt;&quot;<br><br>[bash]$ pg_restore -v \<br>    -d &quot;postgres://&lt;DB_USER&gt;:&lt;DB_PASSWD&gt;@&lt;PG_HOST&gt;/development_template&quot; \<br>    /path/to/pg_dump_file<br><br>[bash]$ psql -X &quot;postgres://&lt;DB_USER&gt;:&lt;DB_PASSWD&gt;@&lt;PG_HOST&gt;&quot; <br>    -c &quot;VACUUM (ANALYZE, INDEX_CLEANUP ON)&quot;</pre><p>Notes on the commands above:</p><ul><li>We use <strong>psql</strong> to run a few commands. This can be done using any <strong>PostgreSQL</strong> CLI or GUI tool</li><li>The database we created is named: development_template — we’ll use this in later steps</li><li>The 3rd step requires the <strong>pg_dump</strong> output file (/path/to/pg_dump_file)</li><li>We run the VACUUM command after <strong>pg_restore</strong> in line with <strong>PostgreSQL</strong> best practices</li><li>The length of time to create this database depends on the size of you database image and your storage speed (e.g. SATA vs SSD).</li></ul><h4>Creating a database using DDL</h4><p>Here is an example of creating the template database using <strong>DDL</strong> scripts:</p><pre>[bash]$ psql -X postgres://postgres@&lt;PG_HOST&gt;<br>... create users/grants needed by database ...<br><br>[bash]$ psql -X postgres://postgres@&lt;PG_HOST&gt; \<br>    -c &quot;CREATE DATABASE development_template OWNER &lt;DB_USER&gt;&quot;<br><br>[bash]$ psql -X postgres://&lt;DB_USER&gt;:&lt;DB_PASSWD&gt;@&lt;PG_HOST&gt; \<br>    -f /path/to/ddl_file.sql<br><br>[bash]$ psql -X postgres://&lt;DB_USER&gt;:&lt;DB_PASSWD&gt;@&lt;PG_HOST&gt; \<br>    -c &quot;VACUUM (ANALYZE, INDEX_CLEANUP ON)&quot;</pre><p>Notes on the commands above:</p><ul><li>We use <strong>psql</strong> to run a few commands. This can be done through any <strong>PostgreSQL</strong> CLI or GUI tool.</li><li>The database we created is named: development_template — we’ll use this in later steps</li><li>The 3rd step requires the <strong>DDL</strong> file: /path/to/ddl_file.sql</li><li>We run the VACUUM command after the database creation in line with <strong>PostgreSQL</strong> best practices</li><li>The length of time to create this database depends on the size of your <strong>DDL</strong> script and your storage speed (e.g. SATA vs SSD).</li></ul><p>If your <strong>DDL</strong> was schema only and you have scripts/tools to generate data you would insert that step prior to the VACUUM command.</p><h4>Results</h4><p>Whichever option you choose, the end result is a database created in <strong>PostgreSQL</strong> (with an identifier we’ll call development_template) that can be used as a <em>Template</em> for all future per-branch databases. This database should NOT ever be used by your application. It’s entire purpose is to be a <em>Template</em> database for per-branch database creation.</p><p>To see how <em>Copy-on-Write</em> saves on disk storage we’ll check disk usage after all database creation steps.</p><p>It is also worth noting how long it took to create our <em>Template</em> database. We’ll then have a base line to compare against time required to create a per-branch database.</p><p>For our testing we used the <strong>pg_dump</strong> and<strong> pg_restore</strong> method defined above. We encapsulated the four steps into a <em>bash</em> script so we could collect timing information of the full restore and vacuum process:</p><pre>[bash]$ time ./make-template.sh <br>real    17m46.941s<br>user    4m17.327s<br>sys     0m47.121s</pre><p>The<strong> pg_dump</strong> database image file used in the example was 12 GB. This format uses compressed data therefore the resulting database will be much larger.</p><p>To view the amount of disk that <strong>PostgreSQL</strong> is using, we will need to know how big our development_template database is on disk to start.</p><p>First we want the OID of the database:</p><pre>[bash]$ psql postgres://postgres@&lt;PG_HOST<br><br>postgres@postgres.postgres=# <br>SELECT oid, datname FROM pg_database WHERE datname = &#39;development_template&#39;; <br><br>      oid | datname <br>----------+--------------------- <br> 40916095 | development_template</pre><p>Then we can see how big the database directory is on disk. Navigate to the <em>&lt;PG_INSTALL&gt;</em> directory and get the size of the <em>base/40916095</em> directory:</p><p>Example:</p><pre>[bash]$ cd &lt;PG_INSTALL&gt; <br>[bash]$ du -h base/40916095 <br>90G base/40916095</pre><p>In our example, the development_template is 90 GB once uncompressed.</p><p>Unfortunately, <em>C</em>opy-on-Write<em> </em>filesystems lie to the du command. So we’ll need to use df instead to see how much actual storage is being used by the filesystem where the <strong>PostgreSQL</strong> server files are installed. We’ll get the value after each database branch step. Note that we very specifically do NOT use a-h option for df in case the size change is less than a GB.</p><pre>[bash]$ df &lt;PG_INSTALL&gt; <br>Filesystem      1K-blocks     Used Available Use% Mounted on<br>/dev/nvme1n1p1 1000203264 95733548 900743812  10% &lt;PG_INSTALL mount&gt;</pre><p>Our filesystem is currently using 95733548 KB of storage (about 91 GB).</p><p>We’ll revisit these numbers in future steps to see how our storage changes.</p><h3>Branching mechanics and per-branch database setup</h3><p>At this point, we have a running <strong>PostgreSQL</strong> server with a <em>Template</em> database. The next step is to setup a branch / worktree and its matching per-branch database.</p><p>We will use <strong>Git</strong> and <strong>Git Worktrees</strong> in our examples, so we need to start with a <strong>Git</strong> repository:</p><pre>[bash]$ cd $HOME <br>[bash]$ mkdir myproject.git <br>[bash]$ cd myproject.git<br>[bash]$ git init <br>[bash]$ echo &quot;hello&quot; &gt; README.md <br>[bash]$ git add README.md <br>[bash]$ git commit -m&#39;* Initialize our repository&#39;</pre><p>Now we have a <strong>Git</strong> repository with a single branch: <strong>main</strong> and a single file README.md. You can obviously use an existing repository instead of creating a new one.</p><p>We also want to setup a directory for <strong>Git Worktree</strong> to hold our branches:</p><pre>[bash]$ cd $HOME<br>[bash]$ mkdir ~/myproject-worktrees</pre><p>The next step is to create a database for the <strong>main</strong> branch using our development_template database:</p><pre>[bash]$ psql -X postgres://postgres@&lt;PG_HOST&gt; \ <br>    -c CREATE DATABASE &lt;DB_NAME&gt; WITH TEMPLATE development_template STRATEGY=FILE_COPY OWNER &lt;DB_USER&gt;</pre><p>Notes on the command above:</p><ul><li>Change <em>&lt;DB_NAME&gt;</em> to the name you want for your new database. We suggest you use the name of your branch in this case <em>main</em></li><li>Change the <em>&lt;DB_USER&gt;</em> to the database user that should own the new database. This requires the user and appropriate grants already exist</li><li>We are using CREATE DATABASE ... WITH TEMPLATE to tell <strong>PostgreSQL</strong> to use a template</li><li>We tell <strong>PostgreSQL</strong> to use the development_template as the source template</li><li>We tell <strong>PostgreSQL</strong> to use the FILE_COPY strategy to create the database. With our <strong>PostgreSQL</strong> configuration and <em>Copy-on-Write</em> file system this will use shared file storage</li></ul><p>We now have two databases in our server:</p><ul><li><strong>development_template</strong></li><li><strong>main</strong></li></ul><h3>Storage consumption and timing</h3><p>Since we created another database, we’ll look at the filesystem disk usage again.</p><p>First get the OID:</p><pre>[bash]$ psql postgres://postgres@&lt;PG_HOST&gt;<br><br>postgres@postgres.postgres=# <br>SELECT oid, datname FROM pg_database WHERE datname = &#39;main&#39;;<br>      oid | datname <br>----------+----------- <br> 49176495 | main</pre><p>We can’t depend on the du command to see how much storage is actually used. The du command will see the effective size rather than its actual consumption:</p><pre>[bash] cd $PG_INSTALL <br>[bash]$ du -h 49176495<br>90G 59948153</pre><p>It shows the new database is consuming 90 GB, just like the <em>development_template</em>.</p><p>However, when we use the df command o see how much actual storage has been consumed:</p><pre>[bash]$ df &lt;PG_INSTALL&gt; <br>Filesystem      1K-blocks     Used Available Use% Mounted on<br>/dev/nvme1n1p1 1000203264 97412668 899129908  10% &lt;PG_INSTALL mount&gt;<br></pre><p>We are currently using 97412668 K of storage (about 92 GB). The actual storage change was from 95733548 KB to 97412668 KB (1679120 KB), which is only about 1.6 GB.</p><p>Even though we have two 90 GB databases, the second database is only consuming about 1.6 GB of storage.</p><p>The timing of the database creation:</p><pre>[bash]$ time psql -X postgres://postgres@&lt;PG_HOST&gt; \ <br>    -c CREATE DATABASE main WITH TEMPLATE development_template STRATEGY=FILE_COPY OWNER &lt;DB_USER&gt; <br>real 0m2.464s<br>user 0m0.786s<br>sys  0m0.659s</pre><p>Loading the template took over 17 minutes. Creating a new database from that template took just over 2 seconds!</p><h3>Application configuration</h3><p>Next, we need to configure the application in the myproject.git directory (on the <strong>main</strong> branch) to connect to the database named: <em>main</em>. This is VERY simple if you are using environment variables in your application. With an example environment variable in your application of <em>DB_CONFIG_DSN</em> you would run your application as something like:</p><pre>[bash]$ DB_CONFIG_DSN=postgres://&lt;DB_USER&gt;:&lt;DB_PASSWD&gt;@&lt;PG_HOST&gt;:&lt;PG_PORT&gt;/main ./run/my/application</pre><p>Notice the use of <em>main</em> at the end of the DSN. That identifies the specific database. No matter which branch we’re using the DB_USER, DB_PASSWORD, PG_HOST and PG_PORT values are always the same. Only the database identifier at the end changes.</p><p>If you are using another method to configure your application you need to make the equivalent changes to your application configuration.</p><p>After this step you should have an instance of the application running code on the <strong>main</strong> branch and the server is connected to the database named <em>main</em>.</p><p>Next, we’ll see how to setup and configure another branch and its own per-branch database.</p><h3>Setting up another branch and database</h3><p>We’re going to create a new branch from <strong>main</strong> that we’ll call: <strong>branch_one</strong>:</p><pre>[bash]$ cd ~/myproject.git <br>[bash]$ git worktree add ~/myproject-worktrees/branch_one -b branch_one main</pre><p>This command will:</p><ul><li>Create a new branch: <strong>branch_one</strong></li><li>Create a new directory: ~/myproject-worktrees/branch_one</li><li>Checkout the new branch <strong>branch_one</strong> as a <strong>Git Worktree</strong> in the~/myproject-worktrees/branch_one directory</li></ul><p>If you are using another source code tool, you just need to make sure each branch is checked out in separate directories as we do with <strong>Git</strong>.</p><p>Next we create a new database for the new branch:</p><pre>[bash]$ psql -X postgres://postgres@&lt;PG_HOST&gt; \ <br>    -c CREATE DATABASE branch_one WITH TEMPLATE development_template STRATEGY=FILE_COPY OWNER &lt;DB_USER&gt;</pre><p>Notes:</p><ul><li>Change &lt;PG_HOST&gt; to the database host</li><li>Change &lt;DB_USER&gt; to be the database user that needs to own the database. The user and appropriate grants need to already exist</li><li>We used <em>branch_one</em> as the database name (same as our branch)</li></ul><p>We now have three databases in our server:</p><ul><li><strong>development_template</strong></li><li><strong>main</strong></li><li><strong>branch_one</strong></li></ul><p>Since we created another database we’ll look at disk usage again.</p><pre>[bash]$ df &lt;PG_INSTALL&gt; <br>Filesystem      1K-blocks     Used Available Use% Mounted on <br>/dev/nvme1n1p1 1000203264 110911812 885711708  12% &lt;PG_INSTALL mount&gt;</pre><p>We are currently using 110911812 KB of storage (about 105 GB). But there was an actual storage change from 97412668 KB to 110911812 KB (13499144 KB), about 12.8 GB.</p><p>Even though we have what appears to be 270 GB worth of databases (three 90 GB databases), the two new databases only added a combined total of about 12.8 GB of storage.</p><p>The timing of the database creation:</p><pre>[bash]$ time psql -X postgres://postgres@&lt;PG_HOST&gt; \ <br>    -c CREATE DATABASE branch_one WITH TEMPLATE development_template STRATEGY=FILE_COPY OWNER &lt;DB_USER&gt; <br>real    0m4.250s<br>user    0m1.588s<br>sys     0m0.803s</pre><p>Just over four seconds.</p><p>Next we can run the server from the <strong>branch_one</strong> checkout and make it point at the <em>branch_one</em> database.</p><pre>[bash]$ cd ~/myproject-worktrees/branch_one<br>[bash]$ DB_CONFIG_DSN=postgres://&lt;DB_USER&gt;:&lt;DB_PASSWD&gt;@&lt;DB_HOST&gt;:&lt;DB_PORT&gt;/branch_one ./run/my/application</pre><p>We now have an instance of the application running the code from the <strong>branch_one</strong> branch and connecting to the <em>branch_one</em> database.</p><p>There are a couple of things to be aware of with this setup:</p><ol><li><strong>PostgreSQL</strong> has restrictions on database names</li></ol><ul><li>These restrictions are defined in the <a href="https://www.postgresql.org/docs/18/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS">PostreSQL Identifier and Key Words </a>documentation</li><li>It is convenient if you can name your branch and your database the same name. If you can’t do that, you can easily name them different things. Change the branch name, database name or both accordingly</li></ul><p>2. Depending on how your application is run, you may only be able to run one instance of your application at a time (for example — it binds a fixed port).</p><ul><li>If you can only run one instance at a time, you can do something like this to branch switch:</li></ul><pre>[bash]$ cd ~/myproject-worktrees/branch_one/<br><br>[bash]$ DB_CONFIG_DSN=postgres://&lt;DB_USER&gt;:&lt;DB_PASSWD&gt;@&lt;PG_HOST&gt;:&lt;PG_PORT&gt;/branch_one ./run/my/application<br>... run for as long as needed, until time to switch branches<br>Press [CTRL+C] or otherwise stop your application<br><br>[bash]$ cd ../myproject.git/<br><br>[bash]$ DB_CONFIG_DSN=postgres://&lt;DB_USER&gt;:&lt;DB_PASSWD&gt;@&lt;PG_HOST&gt;:&lt;PG_PORT&gt;/main ./run/my/application<br>... run for as long as needed</pre><h3>A note about other Git and Worktree operations</h3><p>Using <strong>Git Worktrees</strong> you will certainly need to use quite a few other <strong>Git</strong> operations on your worktrees:</p><ul><li>Viewing Commit History</li><li>Adding/Committing changes</li><li>Viewing Diffs</li><li>Getting upstream updates</li><li>Merging (to/from) other branches</li><li>etc.</li></ul><p>Since we are using standard <strong>Git/Git Worktrees</strong> all <strong>Git/Git Worktree</strong> operations continue to work as-is, our workflow has zero impact on those tools. See the community <strong>Git</strong> documentation if you need help using <strong>Git/Git Worktree</strong>.</p><h3>Utilizing database clients (CLI and GUI)</h3><p>Connecting to a per-branch database with a CLI or GUI is simply a matter of connecting with the correct per-branch database information.</p><p>If you use a GUI tool you generally only need the main database credentials to connect and you can then view/manage all databases. Good recommendations:</p><ul><li><a href="https://www.pgadmin.org/">pgAdmin</a></li><li><a href="https://dbeaver.io/">DBeaver</a></li></ul><p>If you use a CLI tool you use the same connect information as your application to connect to a specific database. Example:</p><pre>[bash]$ psql &#39;postgres://$DB_USER$:$DB_PASSWD@$PG_HOST:$PG_PORT/$DB_NAME&#39;</pre><h3>Managing database drift</h3><p>We created our <em>Template</em> database at a certain point in time and it is not getting updated. We will use this <em>Template</em> to create all per-branch databases. This can lead to a few issues from database drift. Where <em>database drift</em> is changes to either the database you exported for your template -OR- changes to your per-branch database.</p><p>The issues:</p><ol><li>Your database source could change structure</li></ol><p>If the database source changed structure (and you used <strong>pg_dump </strong>or updated your <strong>DDL</strong> accordingly), these changes are not reflected in any of your branch databases, which can cause huge problems.</p><p>2. Your database source could have meaningful data changes</p><p>You will definitely have temporal drift in your data— no columns using date/time values will be any newer than the date your <em>Template</em> database was created other than rows in your per-branch database created (by you) during your development work</p><p>Additionally, general data changes made to your source system could cause issues in a newer branch (e.g. a value in a column that is no longer used/expected)</p><p>3. Your per-branch database(s) may have schema or data changes</p><p>You may be making changes to your per-branch databases and need to undo these changes. This is particularly useful if you are experimenting with code that modifies the structure of your database</p><p>To address these issues we need to be able to:</p><ul><li>Replace / Update the <em>Template</em> database</li><li>Replace / Update a per-branch database</li></ul><h3>Replace / Update the template database</h3><p>Since every new database is created from the <em>Template</em> you will want to update the <em>Template</em> with some degree of frequency. Some events that might trigger a desire to update the <em>Template</em>:</p><ul><li>The source schema has changed (either your database dump or <strong>DDL</strong> file will have schema changes)</li><li>Significant data change that can lead to code problems In this case your code has assumptions that your database data doesn’t obey since it’s out of date</li><li>Temporal issues (i.e. you need data with newer dates)</li></ul><h4>Replacing a database using pg_dump and pg_restore</h4><p>The process to replace a <em>Template</em> is straight forward and very similar to the original creation of the <em>Template</em>. We only need to add a DROP DATABASE command to the process we used on the initial creation.</p><p>Here is an example of creating the template database using a <strong>pg_restore</strong> from a <strong>pg_dump</strong> file:</p><pre>[bash]$ psql -X postgres://postgres@&lt;PG_HOST&gt; \<br>    -c &quot;DROP DATABASE IF EXISTS development_template OWNER &lt;DB_USER&gt;&quot;<br><br>[bash]$ psql -X postgres://postgres@&lt;PG_HOST&gt; \<br>    -c &quot;CREATE DATABASE development_template OWNER &lt;DB_USER&gt;&quot;<br><br>[bash]$ pg_restore -v \<br>    -d postgres://&lt;DB_USER&gt;:&lt;DB_PASSWD&gt;@&lt;PG_HOST&gt;/development_template \<br>    /path/to/pg_dump_file<br><br>[bash]$ psql -X postgres://&lt;DB_USER&gt;:&lt;DB_PASSWD&gt;@&lt;PG_HOST&gt; \<br>    -c &quot;VACUUM (ANALYZE, INDEX_CLEANUP ON)&quot;</pre><p>Notes on the commands above:</p><ul><li>We use <strong>psql</strong> to run a few commands. This can be done through any <strong>PostgreSQL</strong> CLI or GUI tool</li><li>We added a DROP DATABASE command to drop the old <em>Template</em> database</li><li>We skip user/grant creation as they already exist</li><li>The rest of the process is unchanged from initial creation</li></ul><h4><strong>Replacing a database using </strong><strong>DDL</strong></h4><p>Here is an example of creating the template database using <strong>DDL</strong> scripts:</p><pre>[bash]$ psql -X postgres://postgres@&lt;PG_HOST&gt; \<br>    -c &quot;DROP DATABASE IF EXISTS development_template OWNER &lt;DB_USER&gt;&quot;<br><br>[bash]$ psql -X postgres://postgres@&lt;PG_HOST&gt; \<br>    -c &quot;CREATE DATABASE development_template OWNER &lt;DB_USER&gt;&quot;<br><br>[bash]$ psql -X postgres://&lt;DB_USER&gt;:&lt;DB_PASSWD&gt;@&lt;PG_HOST&gt; \<br>    -f /path/to/ddl_file.sql<br><br>[bash]$ psql -X postgres://&lt;DB_USER&gt;:&lt;DB_PASSWD&gt;@&lt;PG_HOST&gt; \<br>    -c &quot;VACUUM (ANALYZE, INDEX_CLEANUP ON)&quot;</pre><p>Notes on the commands above:</p><ul><li>We use <strong>psql</strong> to run a few commands. This can be done through any <strong>PostgreSQL</strong> CLI or GUI tool</li><li>We add a DROP DATABASE command to drop the old <em>Template</em> database</li><li>We skip user/grant creation as they already exist</li><li>The rest of the process is unchanged from initial creation</li></ul><h4>Increased disk consumption on replace of the Template database</h4><p>When we replace the <em>Template</em> database, any per-branch databases will no longer be able to share blocks with the <em>T</em>emplate. The replacement of the <em>Template</em> will therefore introduce extra storage consumption. We can confirm that:</p><pre>[bash]$ df &lt;PG_INSTALL&gt; <br>Filesystem      1K-blocks      Used Available Use% Mounted on<br>/dev/nvme1n1p1 1000203264 205124044 791913252  21% &lt;PG_INSTALL mount&gt;</pre><p>Our storage has increased to 205124044 KB (about 195 GB).</p><p>Since this process can eat up a lot of disk (at least temporarily), you will want to plan out the times when you trigger this process if you are short on disk. Try to do this when you don’t have a low number of per-branch databases (zero optimally!).</p><p>Later in the doc we will show you how to refresh your per-branch databases to make them use the new <em>Template</em> database. This will reduce the disk consumption back to the original levels.</p><h4>Timing of Template database replacement</h4><p>The process of replacing the <em>Template</em> database is using a new data set and not creating from a template. Therefore, just like the initial <em>Template</em> creation, this process takes significant time (about 17 minutes). Once again, we’ve wrapped the steps into a <em>bash</em> script to collect timing:</p><pre>[bash]$ time ./replace-template.sh <br>real    17m28.671s<br>user    4m15.063s<br>sys     0m49.585s</pre><h3>Replace / Update a per-branch database</h3><p>There are a few reasons why you might want to replace / update your per-branch database:</p><ul><li>You want to revert any changes to your database since you cloned from the development_template</li><li>You have replaced the development_template<em> </em>database and are now consuming increased disk. By replacing you per-branch database you will reduce disk consumption</li></ul><p>The process to replace the per-branch database from the development_template is straightforward:</p><ul><li>Drop the per-branch database</li><li>Recreate from the <em>Template</em></li></ul><pre>[bash]$ psql -X postgres://postgres@&lt;PG_HOST&gt; \ <br>    -c &quot;DROP DATABASE IF EXISTS &lt;DB_NAME&gt;&quot; \ <br>    -c CREATE DATABASE &lt;DB_NAME&gt; WITH TEMPLATE development_template STRATEGY=FILE_COPY OWNER &lt;DB_USER&gt;</pre><p>Notes on command above:</p><ul><li>We use <strong>psql</strong> to run the commands. This can be done through any <strong>PostgreSQL</strong> CLI or GUI tool</li><li>We drop the old version of the database</li><li>Change &lt;PG_HOST&gt; to the host of the database server</li><li>Change <em>&lt;DB_NAME&gt;</em> to the name of the branch database you’re updating</li><li>Change the <em>&lt;DB_USER&gt;</em> to the database user that should own the new database</li><li>We are using CREATE DATABASE ... WITH TEMPLATE to tell <strong>PostgreSQL</strong> to use a template</li><li>We tell <strong>PostgreSQL</strong> to use the development_template as the source template</li><li>We tell <strong>PostgreSQL</strong> to use the FILE_COPY<strong> </strong>strategy to create the database. With our <strong>PostgreSQL</strong> configuration and <em>Copy-on-Write</em> file system this will once again use shared file block storage</li></ul><p>Since we have dropped the per-branch database and re-created it, any changes you had in your per-branch database that were not in the development_template are lost. This is precisely the desired outcome for the “revert” use case.</p><p>If you were in a non-shared, increased disk consumption mode and refresh all of your databases from the <em>Template</em> you will reduce your consumption as the databases are now sharing blocks again.</p><p>NOTE: You decide when to run the process on a per-branch basis. If there is a branch with data that you don’t want to lose (e.g. test data setup), you should defer running this process on the branch until the timing is better.</p><h4><strong>Storage consumption and timing</strong></h4><p>Since this process is the same as the creation of per-branch database with an added <em>DROP</em> command the storage consumption and timing numbers are about the same, a few seconds:</p><pre>[bash]$ time ./refresh-branch-db<br>real 0m2.778s<br>user 0m0.022s<br>sys  0m0.013s</pre><p>After refreshing both per-branch databases the consumed storage is reduced:</p><pre>[bash]$ df &lt;PG_INSTALL&gt; <br>Filesystem      1K-blocks     Used Available Use% Mounted on<br>/dev/nvme1n1p1 1000203264 95598036 901110460  10% &lt;PG_INSTALL mount&gt;</pre><p>Consumption has gone back to 95598036 KB (about 90 GB).</p><h3>Removing obsolete databases</h3><p>When you are done with a branch (e.g. on branch merge) you’ll want to remove the existing database and its Worktree. The steps:</p><pre>[bash]$ psql -X postgres://&lt;PG_HOST&gt;/ \<br>  -c &quot;DROP DATABASE IF EXISTS &lt;DB_NAME&gt;&quot;<br><br>[bash] cd ~/myproject.git<br><br>[bash] git worktree remove &lt;branch_name&gt; [--force]<br><br>[bash] git branch -D &lt;branch_name&gt;</pre><p>Notes on commands above:</p><ul><li>We use <strong>psql</strong> to run a the first command. This can be done through any <strong>PostgreSQL</strong> CLI or GUI tool</li><li>We drop the per-branch database</li><li>We switch to the <em>main</em> repository checkout</li><li>We remove the Worktree (optionally with --force if there are non-committed files)</li><li>We remove the local branch</li></ul><h3>Optional: Automated configuration</h3><p>As shown above, changing your database configuration and running your branch server can be done as easily as:</p><pre>[bash]$ cd ~/myproject-worktrees/branch_one/<br><br>[bash]$ DB_CONFIG_DSN=postgres://&lt;DB_USER&gt;:&lt;DB_PASSWD&gt;@&lt;PG_HOST&gt;:&lt;PG_PORT&gt;/branch_one ./run/my/application<br>... run for as long as needed, until time to switch branches<br>Press [CTRL+C] or otherwise stop your application<br><br>[bash]$ cd ../myproject.git/<br><br>[bash]$ DB_CONFIG_DSN=postgres://&lt;DB_USER&gt;:&lt;DB_PASSWD&gt;@&lt;PG_HOST&gt;:&lt;PG_PORT&gt;/main ./run/my/application<br>... run for as long as needed</pre><p>There is an easier way. We use <a href="https://direnv.net/"><strong>direnv</strong></a> to automate the setting of the DB_CONFIG_DSN environment variable. We add an .envrc file in each Worktree that contains something like:</p><pre>DB_CONFIG_DSN=postgres://&lt;DB_USER&gt;:&lt;DB_PASSWD&gt;@&lt;PG_HOST&gt;:&lt;PG_PORT&gt;/&lt;DB_ID&gt;</pre><p>Where &lt;DB_ID&gt; is the database identifier for that branch/worktree (e.g. <em>main</em>, <em>branch_one</em>, <em>branch_two</em>, etc.)</p><p>With <strong>direnv</strong> configured properly and the .envrc files setup you cdinto the branch directory and <strong>direnv</strong> will automatically set DB_CONFIG_DSN in your environment. Then you simply run your app from the project directory:</p><pre>[bash]$ cd ~/myproject-worktrees/branch_one/<br># on cd, direnv will load the `.envrc` file into your environment<br># automatically setting DB_CONFIG_DSN to connect to the branch_one database<br><br>[bash]$ ./run/my/application<br>  ... run for as long as needed, until time to switch branches<br>Press [CTRL+C] or otherwise stop your application<br><br>[bash]$ cd ../myproject.git/<br># on cd, direnv will load the `.envrc` file into your environment<br># automatically setting DB_CONFIG_DSN to connect to the main database<br><br>[bash]$ ./run/my/application<br>   ... run for as long as needed</pre><h3>Revisiting our concerns with per-branch databases</h3><p>We previously discussed some concerns users might have with per-branch databases, let’s revisit those now.</p><ol><li>Database creation/setup concerns</li></ol><ul><li>How easy is it to create a new per-branch database?</li><li>How long does it take to create a new per-branch database?</li></ul><p>As we have demonstrated, creation is pretty simple: run a few psql commands. You can quickly turn into scripts to make even easier.</p><p>How long does it take? Usually just a second or two. The only slow operation is refreshing the <em>Template</em> database, which is done at whatever frequency you need/want.</p><p>2. Disk consumption concerns</p><ul><li>If the database we use is large it becomes untenable pretty quickly to keep around numerous copies of the database</li></ul><p>With a 90GB database image, having multiple copies around would normally consume disk at a ferocious rate. But as we showed, per-branch databases consume only a fraction of that.</p><p>Granted, when you do a <em>Template</em> replacement your storage increases substantially, but this is a temporary state.</p><p>3. Application Configuration concerns</p><ul><li>How is a branch of code configured to connect to a specific per-branch database?</li><li>How does this configuration get changed when branch switching?</li></ul><p>While we can’t answer this one definitively for you as it depends on your application and how it is configured. Many applications use Environment Variables for configuration. In that setup configuration is done by simply changing the Environment Variable on branch switch.</p><p>If you aren’t using Environment Variables, you need to craft your own solution, but we think it should be fairly easy.</p><p>We also recommend the use of <strong>direnv</strong> to automate this configuration.</p><p>4. Raw Database Access concerns</p><ul><li>How is a specific per-branch database accessed using a CLI or GUI database tool?</li></ul><p>Each branch has identical host, port, user, and password settings. The only unique variant is the database name. We try very hard to use the same name for the branch as the database identifier. This makes database access trivial from either a CLI or GUI.</p><p>5. Database cleanup concerns</p><ul><li>How are obsolete per-branch databases cleaned up once they are no longer required?</li></ul><p>Removing a per-branch database is as simple as executing a DROP TABLE command.</p><h3>Conclusion</h3><p>We hope we’ve provided you a customizable foundation to improve your work environment through the use of per-branch databases. It has saved us a tremendous amount of hours and numerous headaches. Hopefully, you’ll get something out of it.</p><p>Thanks for reading!</p><h3>About Us</h3><p>This document is the product of work of the <a href="http://www.axial.net/">Axial</a> Engineering team:</p><ul><li>David Grimes</li><li>Markus Jonsson</li><li>Sorenson Stallings</li><li>Bob Ternosky</li><li>Zach Werheim</li></ul><p>Founded in 2009, Axial brings together lower middle market M&amp;A professionals and American small business owners on the industry’s most trusted and advanced online M&amp;A platform.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1b1930bddbaa" width="1" height="1" alt=""><hr><p><a href="https://medium.com/axial-engineering/instant-per-branch-databases-with-postgresql-18s-clone-file-copy-and-copy-on-write-filesystems-1b1930bddbaa">Instant Per-Branch Databases with PostgreSQL 18’s clone file copy and Copy-on-Write Filesystems</a> was originally published in <a href="https://medium.com/axial-engineering">Axial Engineering</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>