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:

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!

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.

Database Lab 0.3: users can choose which "thin clone manager" to use, ZFS or LVM​

Update: see the discussion on Hacker News!

Version 0.3 of Database Lab Engine (with a minor update to 0.3.1) adds support of LVM as an alternative to ZFS to enable thin cloning of large databases. This was one of the most requested features after the initial launch of the public Database Lab version a month ago.

Database Lab Engine is an open source technology that helps you clone non-production databases in seconds.

LVM can be chosen as a "thin-clone manager" instead of ZFS for those who do not want to use ZFS and prefer staying on more popular file systems (ext4, xfs) in non-production environments. It is worth noting that ZFS remains the default and recommended option. Postgres.ai team is very satisfied with the experience of using it running Database Labs for multi-terabyte, heavily loaded databases.

Compared to ZFS, the LVM module has a certain restriction: it is not possible to support multiple snapshots and allow choosing the snapshot when requesting a new clone. With LVM, the new clones always are based on the latest state of the database.

Meet Joe​

Update: this post reached the HN top, see the discussion of Joe bot at Hacker News!

Joe is a Postgres query optimization assistant. Joe allows to boost the development process:

• eliminating annoying waiting time needed to provision copies of large databases for development and testing purposes,
• helping engineers understand details of SQL query performance.

Joe works on top of Database Lab. Every time when an engineer starts communicating with Joe, a new full-size copy of the database is provisioned.

This process is fully automated and takes only a few seconds, even for multi-terabyte databases. Such database copies are called "thin clones" because multiple clones share the same data blocks, so provisioning is super fast, and disk space consumption is very low. The clones are fully independent, so developers can modify databases. Finally, SQL execution plans are identical to production, which makes it possible to troubleshoot and optimize queries reliably without involving production databases.

Currently, Joe is provided only in the form of Slack chatbot. Slack was chosen to improve the level of collaboration of developers and DBAs. Alternative commucation ways (including beloved psql) are planned for future versions.

More about Joe features you can find in "What Is Joe Bot?".

Demo​

Database Lab Engine updated to 0.2: everything in containers, better API and CLI​

Update: see the discussion on Hacker News!

We have released version 0.2 of Database Lab Engine, an open source technology that helps you clone non-production databases in seconds.

Now all its components run in containers, so installation and use is much easier. Additionally, various improvements were made, including those in API and client CLI.

Work on documentation continues: we reworked Tutorial, and added new texts. One of them is What is Database Lab. This picture should help to compare Database Lab to traditional methods of development and testing involing large databases:

Postgres.ai team is proud to announce the very first public release of Database Lab Engine​

Update: see the discussion on Hacker News!

Database Lab Engine helps you build non-production environments for projects that use multi-terabyte Postgres databases. Initially obtained using standard "thick" copying (such as pg_basebackup, restoration from an archive, or dump/restore), Postgres data directory then gets cloned on request. Such cloning takes just a couple of seconds. Developers, DBAs, and QA engineers can quickly get fully independent copies, perform testing, and idea verification obtaining reliable (close to production) results. As a result, development speed and quality significantly increase.

Database Lab Engine is open source, you can find the code, ongoing work, and the Issue tracker here: https://gitlab.com/postgres-ai/database-lab.

Here is the list of some tasks that Database Lab Engine can help solve:

1. Troubleshoot an SQL query (run EXPLAIN, EXPLAIN (BUFFERS, ANALYZE)): with query planner settings matching production, one can check any query, including UPDATE, DELETE, INSERT, TRUNCATE, not putting production master into any risks. See also: Joe bot.
2. Verify an index idea: it is easy to create an index and check if it helps optimize your queries.
3. Check database migrations (DB schema changes) or massive data modifications and highlight potentially dangerous steps, to avoid performance degradation and downtime on production.

A single Database Lab instance can provide multiple thin Postgres clones (full-size and fully independent) simultaneously. It becomes possible thanks to copy-on-write (CoW) technology. The only option supported in version 0.1 is ZFS; however, there are plans to support other technologies in the future.

Database Lab can be installed either on a physical machine or a VM. Both on-premise or cloud setups are possible. Users communicate with Database Lab using either REST API or client CLI. The first version of Database Lab has certain limitations:

• it works on Ubuntu 18.04 only,
• only Postgres versions 9.6, 10, 11, and 12 are supported,
• in addition to ZFS, the installation of Postgres and Golang is required (it is planned to get rid of this requirement in version 0.2, fully switching to containers).