Skip to main content

DLE 3.0: UI, persistent clones, PostgreSQL 14, more

· 6 min read
Nikolay Samokhvalov
note

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[1].

The Postgres.ai team is happy to announce the release of version 3.0 of Database Lab Engine (DLE)[2], 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"[3] 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[4]. 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.

Links

  1. [1] Migration notes https://gitlab.com/postgres-ai/database-lab/-/releases/v3.0.0#migration-notes
  2. [2] Database Lab Engine (DLE) https://github.com/postgres-ai/database-lab
  3. [3] "Shift-left testing" https://en.wikipedia.org/wiki/Shift-left_testing
  4. [4] the DLE documentation https://postgres.ai/docs/database-lab/telemetry

Three cases against IF NOT EXISTS / IF EXISTS in Postgres DDL

· 6 min read
Nikolay Samokhvalov

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();
CREATE TABLE
test=# drop table if exists mytable;
DROP TABLE

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.

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

· 15 min read
Nikolay Samokhvalov

Based on a true story.

This article was originally published in 2018[1]. 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.

Links

  1. [1] in 2018 https://medium.com/@samokhvalov/how-partial-indexes-affect-update-performance-in-postgres-d05e0052abc

Platform: reset clones to any data snapshot

· 2 min read
Anatoly Stansler

The Database Lab Platform[1] 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

Links

  1. [1] Database Lab Platform https://postgres.ai/

Useful queries to analyze PostgreSQL lock trees (a.k.a. lock queues)

· 4 min read
Nikolay Samokhvalov

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:

Links

  1. [1] "13.3. Explicit Locking" https://www.postgresql.org/docs/current/explicit-locking.html
  2. [2] LOCK TABLE ... https://www.postgresql.org/docs/current/sql-lock.html
  3. [3] SELECT ... FOR UPDATE https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE
  4. [4] pg_advisory_lock(..) https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS
  5. [5] "PostgreSQL rocks, except when it blocks: Understanding locks" https://www.citusdata.com/blog/2018/02/15/when-postgresql-blocks/
  6. [6] Marco Slot https://twitter.com/marcoslot
  7. [7] Egor Rogov https://twitter.com/e_g_Orius
  8. [8] "Locks in PostgreSQL: 1. Relation-level locks" https://postgrespro.com/blog/pgsql/5967999
  9. [9] "Locks in PostgreSQL: 2. Row-level locks" https://postgrespro.com/blog/pgsql/5968005

Zero-downtime Postgres schema migrations need this: lock_timeout and retries

· 15 min read
Nikolay Samokhvalov

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"[1] 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"[2]). 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...

Links

  1. [1] "I know that I know nothing" https://en.wikipedia.org/wiki/I_know_that_I_know_nothing
  2. [2] "Migration Style Guide" https://docs.gitlab.com/ee/development/migration_style_guide.html

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

· 3 min read
Nikolay Samokhvalov
note

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

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[2] 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[3]. 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[4] (currently works with AWS only).

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

Links

  1. [1] Migration notes https://postgres.ai/blog/20210914-dle-2-5#migration-notes
  2. [2] pg_dump https://www.postgresql.org/docs/current/app-pgdump.html
  3. [3] DLE CLI reference https://postgres.ai/docs/reference-guides/dblab-client-cli-reference#subcommand-reset
  4. [4] Terraform module https://gitlab.com/postgres-ai/terraform-postgres-ai-database-lab
  5. [5] full changelog https://gitlab.com/postgres-ai/database-lab/-/releases#2.5.0

What is a slow SQL query?

· 6 min read
Nikolay Samokhvalov

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?

PostgreSQL subtransactions considered harmful

· 21 min read
Nikolay Samokhvalov

An opinionated overview of four cases against using PostgreSQL subtransactions​

💬 Hacker News discussion[1]​

💬 "Why we spent the last month eliminating PostgreSQL subtransactions" (GitLab)[2]​

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.

Links

  1. [1] Hacker News discussion https://news.ycombinator.com/item?id=28374333
  2. [2] "Why we spent the last month eliminating PostgreSQL subtransactions" (GitLab) https://about.gitlab.com/blog/2021/09/29/why-we-spent-the-last-month-eliminating-postgresql-subtransactions/

Comprehensive testing covers code and data

· 2 min read
Dante Cassanego

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