Recent posts

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

Nikolay Samokhvalov

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.

As usual, please send us any feedback you have; it is hard to overestimate its meaning for such a young project:

Database Lab Engine is open-source software distributed under OSI-approved AGPLv3 license. Database Lab Engine allows to clone PostgreSQL databases of any size in just a few seconds. This can save you a lot of money for development and testing infrastructure, and at the same time, drastically improve development quality and time-to-market.

The open-source Database Lab Engine is equipped with convenient API and CLI. Additionally, we continue developing the Enterprise version that offers GUI, authentication flexibility, and user management for Database Lab Engine API and CLI, more. The Enterprise version is in the "private beta" mode; we encourage you to sign up and request a demo.

plan-exporter: visualize PostgreSQL EXPLAIN data right from psql

Nikolay Samokhvalov

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:

To enable plan-exporter you need to use \o with a pipe:

\o | plan-exporter

After this, psql will start mirroring the output to plan-exporter. When plan-exporter sees the EXPLAIN data, it suggests you sending it to a visualization service.

Both services mentioned above are supported and can be chosen using --target option. The default is explain.depesz.com.

To reset, just use \o command without parameters – and plan-exporter will stop receiving the data. And if you want to always have it enabled when you start psql, consider adjusting your .psqlrc file:

echo '\o | plan-exporter --target=dalibo' >> ~/.psqlrc

Joe 0.7.0 released! New in this release: Web UI, Channel Mapping, and new commands

Artyom Kartasov

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!

Web UI communication type#

Originally, only the Slack version of Joe Bot was publicly available. Today, we are excited to announce that there are two available types of communication with Joe:

The good news is that you can use both of them in parallel.

Thanks to recent refactoring of Joe codebase, and the fact that this codebase is open-source, you can develop and add support for any messenger. Feel free to open issues to discuss the implementation and merge requests to include the code into the main Joe Bot repository. See also: communication channels issues, and discussions in our Community Slack.

Check Platform Overview to discover all advantages of using Web UI working on Postgres.ai Platform.

TODO Fix image ?Postgres.ai Console

Joe Bot Tutorial was adjusted and now explains setting up both Slack and Web UI versions: https://postgres.ai/docs/tutorials/joe-setup.

🚀 Note that currently, Postgres.ai Platform is working in "Closed Beta" mode. During this period, we activate accounts on Postgres.ai only after a 30-minute video call with a demo session and screen sharing. Feel free to join https://postgres.ai using our Google/GitLab/GitHub/LinkedIn account but allow some time while we process your registration and reach you to organize a demo session.

Channel Mapping#

Often the infrastructure doesn't limit by a single database. In addition, we want to work with different kinds of communication types. Someone is comfortable with Slack, whereas someone prefers Web UI.

Does it mean that we have to run multiple Joe instances? Starting with version 0.7.0 the answer is no.

Thanks to Channel Mapping you can easily use multiple databases and communication types. Moreover, you can configure multiple Database Lab instances in Enterprise Edition.

Check all configuration options in the docs to realize how the channel mapping can be implemented.

New commands: activity and terminate#

Imagine, we have a PostgreSQL database \d+:

List of relations
Schema | Name | Type | Owner | Size | Description
--------+------------------+-------+----------+---------+-------------
public | pgbench_accounts | table | postgres | 171 GB |
public | pgbench_branches | table | postgres | 624 kB |
public | pgbench_history | table | postgres | 512 kB |
public | pgbench_tellers | table | postgres | 6616 kB |
(4 rows)

We are running a query and realizing that it will take for a long time:

explain select from pgbench_accounts where bid = 100;
Plan without execution:
Gather (cost=1000.00..29605361.74 rows=118321 width=0)
Workers Planned: 2
-> Parallel Seq Scan on pgbench_accounts (cost=0.00..29592529.64 rows=49300 width=0)
Filter: (bid = 100)
JIT:
Functions: 3
Options: Inlining true, Optimization true, Expressions true, Deforming true

What can we do if we don't want to waste our time? Version 0.7.0 adds new commands to control running queries.

The activity command shows currently running sessions in Postgres for following states: active, idle in transaction, disabled. We can easily discover the current clone activity using this command:

Activity response:
PID | QUERY | STATE | BACKEND TYPE | WAIT EVENT | WAIT EVENT TYPE | QUERY DURATION | STATE CHANGED AGO
------+--------------------------------+--------+-----------------+--------------+-----------------+-----------------+--------------------
20 | EXPLAIN (ANALYZE, COSTS, | active | client backend | DataFileRead | IO | 00:10:06.738739 | 00:10:06.738783
| VERBOSE, BUFFERS, FORMAT JSON) | | | | | |
| select from pgbench_accounts | | | | | |
| where bid = 100... | | | | | |
29 | EXPLAIN (ANALYZE, COSTS, | active | parallel worker | DataFileRead | IO | 00:10:06.738798 | 00:10:06.698513
| VERBOSE, BUFFERS, FORMAT JSON) | | | | | |
| select from pgbench_accounts | | | | | |
| where bid = 100... | | | | | |
28 | EXPLAIN (ANALYZE, COSTS, | active | parallel worker | DataFileRead | IO | 00:10:06.738807 | 00:10:06.705874
| VERBOSE, BUFFERS, FORMAT JSON) | | | | | |
| select from pgbench_accounts | | | | | |
| where bid = 100... | | |

It shows we have a long-lasting query. So, in case if we don't want to wait when it finishes, a new terminate command will help us with this. The command terminates Postgres backend that has the specified PID. As we can see above, the client backend process has PID 20. Therefore, let's run terminate 20:

Terminate response:
PG TERMINATE BACKEND
------------------------
true

Success. Then repeat the command activity:

Activity response:
No results.

Also, we can notice that the previously running command has been stopped:

explain select from pgbench_accounts where bid = 100;
ERROR: FATAL: terminating connection due to administrator command (SQLSTATE 57P01)

That's a very powerful tool!

See the full list of Joe's commands in the docs.

Links:#