The Database Lab Engine (DLE) is an open-source technology that enables thin cloning for PostgreSQL. Thin clones are exceptionally useful when you need to scale the development process. DLE can manage dozens of independent clones of your database on a single machine, so each engineer or automation process works with their own database provisioned in seconds without extra costs.

DLE 2.5 significantly expands the capabilities of automatic preparation of snapshots directly from managed database services, as well as from logical dumps, namely:

• restoring of multiple databases
• various pg_dump output formats and file compression formats

Since version 2.5, it becomes possible to reset the clone's database state to a specific snapshot if multiple snapshots are available. See DLE CLI reference. There is also a new option for the reset command, --latest, that allows resetting to the latest available state not knowing the snapshot name. This can be very useful in situations when a clone lives long, occupying a specific port, and some applications (e.g., analytical tools) are configured to work with it – users can periodically switch to the freshest database state without a need to reconfigure their applications.

All new restore features are also already available in the Terraform module (currently works with AWS only).

Additionally, this release has a lot of improvements and fixes. Read the full changelog.

User interface performance#

For web and mobile applications (here, let's talk only about them, not touching analytical workloads, background processing, and so on), engineers usually aim to have as few SQL queries per each HTTP(S) request as possible, and keep the execution time of those queries very low.

How much low?

An opinionated overview of four cases against using PostgreSQL subtransactions#

💬 Hacker News discussion#

💬 "Why we spent the last month eliminating PostgreSQL subtransactions" (GitLab)#

This article discusses what subtransactions are, how to create them, and how widely they are used nowadays. Next, we try to understand why they have a terrible reputation among PostgreSQL experts who operate heavily loaded systems. We overview four problems that may arise in a Postgres database in which subtransactions are used, and load grows. In the end, we discuss the short-term and long-term options for PostgreSQL users willing to solve the ongoing or prevent future problems related to subtransactions.

Over its relatively short history, the discipline of Software Engineering has made rapid advances in the sophistication of its development processes and tools. In the past 15 years alone, the popularization of CI/CD tools has drastically improved software quality and reliability.

However, a large gap remains on the landscape of software tooling. For many engineers, it's a gap they are so accustomed to, they can no longer even see it.

The Elements of Application Behavior

Database Lab Engine 2.4 is out#

The Database Lab Engine (DLE) is an open-source technology to enable thin cloning for PostgreSQL. Thin clones are exceptionally useful when you need to scale the development process. DLE can manage dozens of an independent clones of your database on a single machine, so each engineer or automation process works with their own database provisioned in seconds without extra costs.

DLE 2.4 brings two major capabilities to those who are interested in working with PostgreSQL thin clones:

• a new component, DB Migration Checker, that automates DB migration testing in CI/CD pipelines
• a Terraform module to deploy DLE in AWS using the "logical" provisioning mode

Additionally, this release has a lot of improvements and fixes.

About Database Lab Engine#

The Database Lab Engine (DLE) is an open-source experimentation platform for PostgreSQL databases. The DLE instantly creates full-size thin clones of your production database which you can use to:

1. Test database migrations
2. Optimize SQL queries
3. Deploy full-size staging applications

The Database Lab Engine can generate thin clones for any size database, eliminating the hours (or days!) required to create “thick” database copies using conventional methods. Thin clones are independent, fully writable, and will behave identically to production: they will have the same data and will generate the same query plans.

Learn more about the Database Lab Engine and sign up for an account at https://postgres.ai/.

Database Lab Engine 2.2.0#

Database Lab Engine (DLE) 2.2.0 further improves support for both types of PostgreSQL data directory initialization and synchronization: “physical” and “logical”. Particularly, for the “logical” type (which is useful for managed cloud PostgreSQL such as Amazon RDS users), it is now possible to setup multiple disks or disk arrays and automate data retrieval on a schedule. This gracefully cleans up the oldest versions of data, without downtime or interruptions in the lifecycle of clones.

Database Lab Engine 2.1 for PostgreSQL released#

We are happy to announce version 2.1.0 of Database Lab Engine (DLE), an open-source tool for building powerful development and testing environments based on thin cloning of PostgreSQL databases. Using Database Lab API or CLI (and if you are using Database Lab SaaS, GUI), on a single machine with, say, a 1 TiB disk, you can easily create and destroy dozens of database copies of size 1 TiB each. All these copies are independently modifiable and created/destroyed in just a few seconds. This can become a game-changer in your development and testing workflow, improving time-to-market, and reducing costs of your non-production infrastructure.

In 2.1, the main new features are:

• Better data protection and security:
  • robust configuration defining how data is patched when snapshots are automatically created (both shell and SQL scripts are now supported),
  • an option specifying whether or not passwords for the existing DB users need to be preserved.
• [experimental] DLE API and the CLI tool are extended to have a new feature: "CI Observer" helping control DB schema changes (DB migrations) — here is the reference on how to use it https://postgres.ai/docs/reference-guides/dblab-client-cli-reference#subcommand-start-observation. This is a small step towards the big goal: have 100% coverage for testing DB migrations in CI using full-sized thin clones. Watch the demo (turn captions on):

Database Lab Engine 2.0 for PostgreSQL released#

The Postgres.ai team is proud to announce version 2.0 of Database Lab Engine (DLE) for PostgreSQL, a modern database tool for building powerful development and testing environments based on thin cloning. Using Database Lab API or CLI (and if you are using Database Lab SaaS, GUI), on a single machine with, say, a 1 TiB disk, you can easily create and destroy dozens of database copies of size 1 TiB each. All these copies are independently modifiable and created/destroyed in just a few seconds. This can become a game-changer in your development and testing workflow, improving time-to-market, and reducing costs of your non-production infrastructure.

This release continues our strategy to automate all routine tasks such as initialization of the PostgreSQL data directory, data transformation, and snapshot management. In DLE 2.0, all these tasks can be flexibly configured in a single configuration file. As a result, building dev&test environments for projects with many databases (such as those that adopted microservice architecture) becomes much easier.

The previous versions of the Database Lab introduced the core technology: thin clone provisioning, based on either ZFS (default) or LVM. It was already possible to provision full-sized multi-terabyte database clones in just a few seconds and use them for a broad spectrum of tasks such as database schema changes verification, SQL query analysis, or general application testing.

Version 2.0 speeds up and empowers the initialization of DLE itself. Instead of using custom scripts for initial and continuous data retrieval, it is now possible to configure everything in a declarative manner to get the data and be up and running.

Database Lab Engine 2.0 beta: one config to rule them all; support for Amazon RDS#

During this Summer, we were super-busy achieving two goals that defined version 2.0 of Database Lab Engine:

1. Make all the things in Database Lab configurable in a unified manner (single configuration file): first of all, data initialization and snapshot management.
2. Support both physical and logical types of initialization. Particularly, allow working with an RDS database as a source.

Both targets happened to be quite challenging, but it is finally done, and now we are happy to see that all the pieces of Database Lab Engine work in containers, the whole workflow is described in a single YAML configuration file, and, last but not least, it works with RDS Postgres databases. Yay!

Check out Database Lab Engine release notes, Tutorial for RDS users, and Database Lab Engine configuration reference.

If you love and use psql (like I do), you're equipped with a lot of power. However, when you want to visualize execution plans — using such services as good old explain.depesz.com or modern explain.dalibo.com — you need to deal with inconvenient copy-pasting.

To solve this problem, my colleague Artyom Kartasov has developed a small utility called plan-exporter. It allows sending EXPLAIN data with minimal efforts: