Bryn Llewellyn | The Blog Pros

December 16, 2020

Using PostgreSQL Aggregate Functions in YugabyteDB to Analyze COVID-19 Data

An article in the Washington Post, published on 23-Oct-2020, argues the case for wearing a mask while the COVID-19 pandemic continues and refers to data from Carnegie Mellon’s COVIDcast, an academic project tracking real-time coronavirus statistics. Look for this:

There’s a simple statistical measure of correlation intensity called 'R-squared,' which goes from zero (absolutely no relationship between the two variables) to 1 (the variables move perfectly in [linear] tandem). The 'R-squared' of CovidCast’s mask and symptom data is 0.73, meaning that you can predict about 73 percent of the variability in state-level COVID-19 symptom prevalence simply by knowing how often people wear their masks.

December 20, 2019

Implementing PostgreSQL User-Defined Table Functions in YugabyteDB

Welcome part two of a three-part series of posts on PostgreSQL’s table functions. These functions can be easily leveraged in a distributed SQL database like YugabyteDB, which is PostgreSQL compatible.

In part one, I gave a brief introduction to PostgreSQL’s table functions. Part three will cover some realistic use cases. I’ll introduce this second post by quoting that paragraph:

December 19, 2019

An Introduction to PostgreSQL Table Functions in YugabyteDB

Welcome to the first of a three-part series of posts on PostgreSQL’s table functions. These functions can be easily leveraged in a distributed SQL database like YugabyteDB, which is PostgreSQL compatible.

This series follows on from my “Using Stored Procedures in Distributed SQL Databases” post. In this series of posts we’ll cover:

December 16, 2019

Using Stored Procedures in Distributed SQL Databases

Using Stored Procedures in Distributed SQL Databases

These days, most monolithic SQL databases support stored procedures. This support first emerged in commercially available offerings in the late nineteen eighties. However, stored procedure support is not yet standard in distributed SQL databases. In fact, YugabyteDB is just one of two in this category — supporting stored procedures written in PostgreSQL’s PL/pgSQL. (Aurora also supports stored procedures.)

This post recaps the case for stored procedures that motivated their introduction all those years ago. And it describes a performance experiment that measures one of the benefits brought by using stored procedures: the reduction in client-server round trips for multi-statement business transactions.

November 6, 2019

The Benefit of Partial Indexes in Distributed SQL Databases

Partial Indexes

If a partial index is used instead of a regular one, on a nullable column — where only a small fraction of the rows have not null values for this column—then the response time for inserts, updates, and deletes can be shortened significantly. As a bonus, the response times for single row selects shorten a little bit too. This post explains what a partial index is, shows how to create one, describes the canonical use case that calls for a partial index, describes some straightforward performance tests, and shows that the results justify the recommendation to use a partial index when you have the appropriate use case.