Posted on

Auto_Explain: How to Log Slow Postgres Query Plans Automatically

Do you want to know why a PostgreSQL query is slow? Then EXPLAIN ANALYZE is a great starting point. But query plans can depend on other server activity, can take a while to run, and can change over time, so if you want to see the actual execution plans of your slowest queries, auto_explain is the tool you need. In this post, we’ll look into what it does, how to configure it, and how to use those logs to speed up your queries.

What Is Auto_Explain?

Auto_explain is a PostgreSQL extension that allows you to log the query plans for queries slower than a (configurable) threshold. This is incredibly useful for debugging slow queries, especially those that are only sometimes problematic. It is one of the contribution modules, so it can be installed and configured easily on regular PostgreSQL.

Posted on

Add Databases to Your Spring Cleaning List

Every time you delete or update a row in your database, the old records are secretly still hiding in the background and taking up space on your hard drive.

A VACUUM process is like emptying the recycling bin on your laptop. It clears up space, reduces indexing time, and keeps your database squeaky clean.

Posted on

10 Database Optimization Best Practices for Web Developers

Database optimization has become crucial to web developers for improving the performance of web applications and thus bettering user experience. It might sound a bit unappealing to some, but the benefits are worth the work. If you optimize your database properly, you’ll improve performance, reduce bottlenecks, and save resources.

Luckily, there are some optimization techniques that don’t require DBA-level (database administrator) expertise in SQL-queries.

Posted on

7 Database Optimization Hacks for Web Developers

Optimizing your database comes with great rewards. Higher performance and increased query efficiency are just a few examples of these benefits.

However, the means aren’t always straightforward and may require changing the rules altogether within a developer team. Furthermore, the examples listed here might not work for your database, based on the system you use. In that case, try to follow the core principle and translate the action into the means your system allows.

Posted on

How to Mutate Data in a System Designed for Immutable Data

In a post published on our blog earlier this year, we described some of the decision-making that went into the design and architecture of Snuba, the primary storage and query service for Sentry’s event data. This project started out of necessity; months earlier, we discovered that the time and effort required to continuously scale our existing PostgreSQL-based solution for indexing event data was becoming an unsustainable burden.

Sentry’s growth led to increased write and read load on our databases, and, even after countless rounds of query and index optimizations, we felt that our databases were always a hair’s breadth from the next performance tipping point or query planner meltdown. Increased write load also led to increased storage requirements (if you’re doing more writes, you’re going to need more places to put them), and we were running what felt like an inordinate number of servers with a lot of disks for the data they were responsible for storing. We knew that something had to change.

Posted on

How to Use MySQL Transactions on Alibaba Cloud ECS or ApsaraDB for MySQL

MySQL is one of the most popular Relational Database Management Systems (RDBMS). The open-source database server is widely adopted in a production environment for data storage especially with Content Management Systems like WordPress and Magento.

One of the key features of MySQL is its support for transactions and explicit locks that make it the best when it comes to ACID compliance. We will explain this later in the guide.

Posted on

How to Use Standard MySQL Library Functions on Alibaba Cloud ECS and ApsaraDB

MySQL Relational Database Management System (RDBMS) is widely used by developers because of its advanced features. These include dozens of built-in functions and transactions support. MySQL is also scalable, flexible, and secure to ensure round-the-clock uptime.

The RDBMS standard library functions are a specific set of routines that perform a specific task and return results very quickly. Hence, they eliminate the need to write a lot of code for handling database manipulation.