Skip to main content

ยท 7 min read

Database Lab Engine 3.0 by UI, persistent clones, PostgreSQL 14, more


Action required to migrate from a previous version. If you are running DLE 2.5 or older, please read carefully and follow the Migration notes.

What's new in DLE 3.0?

The team is happy to announce the release of version 3.0 of Database Lab Engine (DLE), the most advanced open-source software ever released that empowers development, testing, and troubleshooting environments for fast-growing projects. The use of Database Lab Engine 3.0 provides a competitive advantage to companies via implementing the "Shift-left testing" approach in software development.

Database Lab Engine 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 very own database provisioned in seconds without extra costs.

Among major changes in DLE 3.0:

  • UI included to the core, it allows working with a single DLE instance,
  • persistent clones: clones now survive DLE (or VM) restart,
  • for the "logical" data provisioning mode: the ability to switch reset clone's state using a snapshot from different pool/dataset,
  • better logging and configuration simplicity,
  • improvements for the cases when multiple DLEs are running on a single machine,
  • PostgreSQL 14 support.

Starting with version 3.0.0, DLE collects non-personally identifiable telemetry data. This feature is enabled by default but can be switched off. Read more in the DLE documentation. Keeping telemetry enabled can be considered your contribution to the DLE development because it helps make decisions down the road of the open-source product development.

Further, we discuss the most requested changes that were implemented in DLE 3.0 โ€“ all of them were created based on real-life user experience and invaluable feedback from the growing community of users and contributors.

ยท 8 min read


What is this about?โ€‹

Many DDL statements in PostgreSQL support modifiers IF EXISTS / IF NOT EXISTS. For example:

test=# create table if not exists mytable();
test=# drop table if exists mytable;

I recommend using IF EXISTS / IF NOT EXISTS in DDL only when necessary. Here are three examples that demonstrate how the overuse of these words may lead to negative consequences.

ยท 17 min read

How partial, covering, and multicolumn indexes may slow down UPDATEs in PostgreSQL

Based on a true story.

This article was originally published in 2018. This is a reviewed and extended version of it. The discussed findings can be applied to any actual major version of PostgreSQL.

Primum non nocereโ€‹

"Primum non nocere" โ€“ this is a fundamental principle that is well-known to anyone working in healthcare: "first, do no harm". It is a reminder: when considering any action that is supposed to improve something, we always need to look at the global picture to see if there might be something else that be damaged by the same action.

This is a great principle and it is used not only in healthcare, of course. I strongly believe that it has to be used in database optimization too, and we need better tools to make it happen.

ยท 2 min read

The Database Lab Platform now supports the ability to reset a clone's state using any available data snapshot on the Database Lab Engine (DLE) instance. It means that now you can get fresh data very quickly (a few seconds, as usual) to your clone, keeping all the credentials the same โ€“ including the port. Previously, it was a problem that one would need to create a new clone to get new data. This implies getting database credentials, most likely including a new port value, which was not always convenient.

It might be helpful in the cases when long-living clones need to be refreshed to get the most up-to-date data from the source (usually, production) or when troubleshooting a non-trivial issue requiring the use of a specific database version from the past (specific point in time).

Reset options demo

ยท 6 min read

Example output of the query for 'lock trees' analysis

For OLTP workloads (such as web and mobile applications), it is important to understand object-level and row-level locks in PostgreSQL. There are several good materials that I can recommend reading:

ยท 21 min read

Graceful Postgres database schema migrations

Zero-downtime database schema migrationsโ€‹

This is one of those topics that hit experienced Postgres DBAs badly. So badly that hypothetical Socrates of 21th century could be one of those Postgres DBAs and the words "I know that I know nothing" would sound natural. I've seen dozens of projects with busy Postgres setups serving lots of TPS, in mission-critical systems in zillion-dollar companies, where database engineers were thinking that they are experienced and know what they are doing โ€“ and then suddenly this, quite a basic topic, made them say "ouch" and quickly fix their DDL deploy systems. Well, let's be frank: I was in this position myself, learning this after more than 10 (!) years of Postgres experience.

I'm sure some of you know this very well โ€“ if so, scroll down to see some bits of advanced material on the matter. However, I'm 100% sure that many of my readers will be really surprised right now.

We won't talk about "how to change a column's data type" or "how to add a foreign key" โ€“ those questions are all interesting too, and there are efforts to document and automate each of such steps for heavily-loaded systems with strict uptime requirements (a great example here is GitLab's "Migration Style Guide"). Instead, we will discuss something that affects any Postgres setup where schema needs to be changed from time to time, where downtime is considered as a huge problem, where DDLs are automated in one way or another (I mean DB migration tools such as Flyway, Sqitch, Liquibase, Ruby on Rails AR DB Migrations, and so on), but where DDLs are deployed without certain trick, so downtime can happen suddenly and unpredictably. Even if TPS numbers are not big. Without that trick in place, anyone using Postgres can (and will) hit that wall one day. Therefore, any engineer working with Postgres should know this trick and, perhaps, implement it in all systems to prevent downtime.

It is time to dive into technical details...

ยท 4 min read

DLE 2.5: Better data extraction for logical mode and configuration improvements


Action required to migrate from a previous version. See Migration notes.

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.

ยท 7 min read

What is a slow SQL query? (SRT - Server Response Time)

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?

ยท 26 min read

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.

ยท 2 min read

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 Code AND Data