Skip to main content

· 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

· 6 min read

DLE 2.4: DB Migration Checker and Terraform module

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:

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

· 5 min read

DLE 2.2 and Joe 0.9

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.

· 3 min read

Database Lab 2.1 release

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):

· 4 min read

Database Lab 2.0 release

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.

· 2 min read

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.

· One min read

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:

· 6 min read

In addition to Slack integration, Joe Bot can be now integrated with Postgres.ai Platform, providing convenient Web UI for all developers who want to troubleshoot and optimize SQL efficiently. Secure and performant Web UI works in any modern browser (even mobile!) and brings more flexibility, 1:1 communication, and visualization options.

What's new in version 0.7.0#

  • [EE] Support Web UI integration with Postgres.ai Platform (see our updated Joe Bot Tutorial to integrate)
  • Extendable communication types: implement support for your favorite messenger
  • Channel Mapping: plug-in as many databases as you want in one Database Lab instance
  • [EE] Support multiple Database Lab instances in parallel
  • New commands to monitor current activity and terminate long-lasting queries
  • Flexible Bot configuration: various convenient options are available in one place
  • Permalinks: when integrated with Postgres.ai Platform, Joe responses contain links to a detailed analysis of SQL execution plans, with three visualization options (FlameGraphs, PEV2 by Dalibo, and good old "explain.depesz.com", all embedded to the Platform)

The full list of changes can be found in Changelog. Can't wait to try!

· 6 min read

Joe's new command hypo to further boost development processes#

Building indexes for large tables may take a long time. The new release of Joe bot includes the ability to get a sneak peek of the SQL query plan, using hypothetical indexes, before proceeding to actually building large indexes.

A hypothetical index is an index that doesn't exist on disk. Therefore it doesn't cost IO, CPU, or any resource to create. It means that such indexes are created almost instantly.

With the brand new command, hypo, you can create hypothetical indexes with Joe and ensure that PostgreSQL would use them. Once it's done, you can use exec to build the actual indexes (in some cases, you'll need to wait some hours for this) and see the actual plan in action.

Note, since the command works on top of the HypoPG extension, your Database Lab image has to use a Docker image for Postgres that contains HypoPG, because this extension is not a part of the core PostgreSQL distribution. For convenience, we have prepared images with HypoPG (and some other extensions) included, for Postgres versions 9.6, 10, 11, and 12. Of course, you can always use your custom image.

To be able to see the plan without actual execution, we have added one more new command: plan. It is aware of hypothetical indexes, so if one is detected in the plan, it presents two versions of the plan, with and without HypoPG involved.