Posted on

PostgreSQL EXPLAIN – What Are the Query Costs?

Understanding the Postgres EXPLAIN Cost

EXPLAIN is very useful for understanding the performance of a Postgres query. It returns the execution plan generated by the PostgreSQL query planner for a given statement. The EXPLAIN command specifies whether the tables referenced in a statement will be searched using an index scan or a sequential scan. When reviewing the output of  EXPLAIN the command, you'll notice the cost statistics, so it’s natural to wonder what they mean, how they’re calculated, and how they’re used. In short, the PostgreSQL query planner estimates how much time the query will take (in an arbitrary unit), with both a startup cost and a total cost for each operation. More on that later. When it has multiple options for executing a query, it uses these costs to choose the cheapest, and therefore hopefully fastest, option.

What Unit Are the Costs In?

The costs are in an arbitrary unit. A common misunderstanding is that they are in milliseconds or some other unit of time, but that’s not the case. The cost units are anchored (by default) to a single sequential page read costing 1.0 units (seq_page_cost). Each row processed adds 0.01 (cpu_tuple_cost), and each non-sequential page read adds 4.0 (random_page_cost). There are many more constants like this, all of which are configurable. That last one is a particularly common candidate, at least on modern hardware. We’ll look into that more in a bit.

Posted on

Application Database Monitoring: The Middle Way

Sometimes you just need to know what a database client is doing in the database. You might be:

  • Debugging an application and trying to figure out if its database interactions are a problem.
  • Analyzing the performance of a serverless function to determine if some queries take too long.
  • Testing an application and making sure its database interactions are as expected.
  • Auditing a system to make sure it does not do anything funny in the database.
  • Reverse-engineering an application.
  • You get the picture, the list is endless.

What's the Database Doing Now?

Posted on

Integrating SCTL Into DistSQL’s RAL

In the previous article “An Introduction to DistSQL” written by Haoran Meng, the Apache ShardingSphere Committer shared the motivating reasons behind the design of DistSQL, explained its syntax system, and impressively showcased how you can use one SQL to create a sharding table.

Recently, the ShardingSphere community has redesigned the SCTL grammar and the execution engine, integrating SCTL into the DistSQL syntax system. Now RAL contains the old SCTL function, making ShardingSphere’s command language even more convenient for database management. Today, our community author would like to introduce the changes and elaborate on how you can use the new RAL command lines. We always pursue a better user experience, and the upgrade we developed this time is just another typical example.

Posted on

Continuous Integration and Continuous Delivery for Database Changes

Introduction

Over the last two decades, many application development teams have adopted Agile development practices and have immensely benefited from it. Delivering working software frequently, receiving early feedback from customers and having self-organized cross-functional teams led to faster delivery to market and satisfied customers. Another software engineering culture, called DevOps, started evolving towards the middle of this decade and is now popular among many organizations. DevOps aims at unifying software development (Dev) and IT operations (Ops). Both these software engineering practices advocate automation, and two main concepts coming out of them are Continuous Integration (CI) and Continuous Delivery (CD). The purpose of this article is to highlight how Database Change Management, which is an important aspect of the software delivery process, is often the bottleneck in implementing a Continuous Delivery process. The article also recommends some processes that help in overcoming this bottleneck and allow streamlining application code delivery and database changes into a single delivery pipeline.

Continuous Integration

One of the core principles of an Agile development process is Continuous Integration. Continuous Integration emphasizes on making sure that code developed by multiple members of the team are always integrated. It avoids “integration hell” that used to be so common during the days when developers worked in their silos and waited until everyone was done with their pieces of work, before attempting to integrate them. Continuous Integration involves independent build machine, automated builds, and automated tests. It promotes test-driven development and the practice of making frequent atomic commits to the baseline or master branch or trunk of the version control system.

Posted on

Redis vs. Memcached: 2021 Comparison

Redis stands for REmote DIctionary Server, created in 2009 by Salvatore Sanfilippo. Memcached, on the other hand, was created in 2003 by Brad Fitzpatrick. Both Redis and Memcached are:

  • NoSQL in-memory data structures
  • Written in C
  • Open source
  • Used to speed up applications
  • Support sub-millisecond latency

In 2014, Salvatore wrote an excellent StackOverflow post on when it makes more sense to use Memcached than Redis. In this post, we provide a current and detailed comparison between Redis and Memcached so that you can make an informed choice about their use in your application.

Posted on

6 Best Practices to Improve Your Data Center Operations

In his article, ROI Valuation, The IT Productivity GAP, Erik Brynjolfsson states, “The critical question facing IT managers today is not, ‘Does IT pay off?’ but rather, ‘How can we best use computers?’” This is not a simple question for CTOs to answer because each data center and IT operation is unique, with a multitude of variables affecting the overall operation. Two different companies with almost identical IT ecosystems yet one might have a fraction of their competitor's productivity, argues Brynolfsson. However, there are several best practices that CTOs can follow to ensure their IT operation is efficient, running within capacity, and executing as productively as possible.  

1. Clean Up and Declutter 

“Cleanliness is godliness” as the old saying goes, and it could also be stress-relieving when it comes to IT. Servers and networking equipment all have set lifespans and old equipment should be decommissioned on a schedule defined by the manufacturers. Old equipment should be properly destroyed, recycled, or returned to the manufacturer, with all data wiped clean to ensure proper security. 

Posted on

Database Migration tools: Flyway vs. Liquibase

I believe that there is no need for describing why the usage of database migration tools is vital for modern days apps that utilize relational database engines. I will only say that they can make our life much easier and help us to automatize a complex and repetitive process.

Through the course of this article, I will provide some more insights into similarities and differences between two of the most common open-source migration tools, namely Flyway and Liquibase.

Posted on

Follower Clusters — 3 Major Use Cases for Syncing SQL and NoSQL Deployments

Follower clusters are a ScaleGrid feature that allows you to keep two independent database systems (of the same type) in sync. Unlike cloning or replication, this allows you to maintain an active, point-in-time copy of your production data. 

This extra cluster, known as a follower cluster, can be leveraged for multiple use cases, including for analyzing, optimizing and testing your application performance for MongoDB, MySQL, and PostgreSQL. In this article, we will cover the top three scenarios to leverage follower clusters for your application.

Posted on

Tech Talks With Tom Smith: Blockchain Use Cases Proliferate

I had the opportunity to meet with Frank Xiong, Group VP, Oracle Blockchain during Oracle Open World.

By building the Oracle Blockchain Platform in eight geographically dispersed data centers around the world, Oracle is enabling customers to build blockchain applications more easily since the platform provides all the necessary plumbing and obfuscates a lot of complexity. This allows developers to focus on the business problem they are trying to solve. Companies are able to get up and running with a blockchain-driven application in 15 minutes.

Posted on

Which Is the Best PostgreSQL GUI? 2019 Comparison

PostgreSQL GUI Comparison

PostgreSQL graphical user interface (GUI) tools help these open source database users to manage, manipulate, and visualize their data. In this post, we discuss the top 5 GUI tools for administering your PostgreSQL deployments. PostgreSQL is the fourth most popular database management system in the world and is heavily used in all sizes of applications. The traditional method to work with databases is using the command-line interface (CLI) tool, however, this interface presents a number of issues:

  • It requires a big learning curve to get the best out of the DBMS
  • Console display may not be something of your like, and it only gives very little information at a time
  • It is difficult to browse databases and tables, check indexes, and monitor databases through the console

Many still prefer CLIs over GUIs, but this set is ever so shrinking. I believe anyone who comes to programming after 2010 will tell you GUI tools increase their productivity over a CLI solution.

Posted on

The Best Way to Host MySQL on Azure Cloud

Are you looking to get started with the world’s most popular open-source database and wondering how you should set up your MySQL hosting? So many default to Amazon RDS when MySQL performs exceptionally well on Azure Cloud. While Microsoft Azure does offer a managed solution, Azure Database, the solution has some major limitations you should know about before migrating your MySQL deployments. In this post, we outline the best way to host MySQL on Azure, including managed solutions, instance types, high availability replication, backup, and disk types to use to optimize your cloud database performance.

You may also like: MySQL Tutorial: A Beginners Guide to Learn MySQL

MySQL DBaaS vs. Self-Managed MySQL

The first thing to consider when weighing between self-management and a MySQL Database-as-a-Service (DBaaS) solution is what internal resources you have available. If you’re reading this, you likely already know the magnitude of operational tasks associated with maintaining a production deployment, but for a quick recap, there’s provisioning, de-provisioning, master-slave configurations, backups, scaling, upgrades, log rotations, OS patching, and monitoring to name a few.

Posted on

Will Automation Wipe Out the Role of the DBA?

Disruption has come to the role of the database administrator (DBA). Automation is eating up tasks typically completed by a DBA — and the technology is evolving to become ever-smarter, ever more capable.

This has sparked concern that humans will inevitably become redundant in running databases. Already, cloud services have threatened to render the DBA role obsolete. Now, with automation infiltrating more and more areas of database administration, many have predicted that DBAs will not survive the storm of change.

Posted on

9 of the Most Common Mistakes in Database Design

As a database designer, when you are tasked with a database project, you can expect to run into a couple of challenges during the design process and after the database is deployed to production.

Some of these problems are unavoidable and outside your control. However, a number of them can be traced back to the quality of the database design itself. The decisions you make at this preliminary phase can have a profound impact on how well the database eventually works. The following are some of the most common mistakes of database design.

Posted on

DevOps and Automation Will Eliminate the DBA

I’ve been reading about the death of the DBA ever since I first made the jump from full-time developer to full-time data professional. The first time I heard it was when SQL Server 7.0 was released. Did you know that SQL Server 7.0 was self-tuning? In fact, it was so self-tuning that the DBA is a relic of the past and no one will be paid for that kind of work anymore.

Right.

Posted on

The 10 Habits for Highly Successful Compliant Database DevOps

Database DevOps has come of age. Now seen as a key technical practice which can contribute to the successful implementation of DevOps, it stops the database being a bottleneck and makes releases faster and easier.

Conversely, perhaps, the automation and audit trails it introduces can help to protect personal data within databases and make compliance part of the same process rather than an additional time-consuming step outside it.