Posts about Fun with SQL

We just launched our Postgres Playground. Running Postgres in the web browser was not exactly commonplace before, so naturally, people are wondering how it works.

It actually started as a fun weekend experiment. Here's a screenshot I saved, just moments after recovering from the initial "whoa, it's working!" effect.

The next morning, I shared this screenshot in our internal Slack channel for web frontend engineering. Our mental gears began to turn as we imagined what might (and might not) be possible. After a bit of work, we built upon some interesting ideas, and this fun weekend hack evolved into what is now the Postgres Playground

Find me all the things in set "A" that are not in set "B".

This is a pretty common query pattern, and it occurs in both non-spatial and spatial situations. As usual, there are multiple ways to express this query in SQL, but only a couple queries will result in the best possible performance.

Setup

If you're checking archives or working with Postgres replication, data reconciliation can be a necessary task. Row counts can be one of the go to comparison methods but that does not show data mismatches. You could pull table data across the network and then compare each row and each field, but that can be a demand on resources. Today we'll walk through a simple solution for your Postgres toolbox - using Foreign Data Wrappers to connect and compare the two source datasets. With the foreign data wrapper and a little sql magic, we can compare data quickly and easily.

Creating Environments

What is Wordle?

Wordle became very popular on the internet very quickly. It's a 5 letter word game to guess among all possible words in a given language. The French version is like the English one, except words aren't be written with the accents we have in French, like “é, è, ê, ë, à, ô,” etc. Words with the special character “œ” are written with 2 characters like “oe”.

Prepare the database for some fun

While supporting Crunchy Spatial and Crunchy Bridge clients, I’ve been thinking about how I usually clean messy data. I wanted to talk about regular expressions (regex

I recently wrote an example of how you can project monthly recurring revenue (MRR) in Postgres. This is a helpful metric to understand how a subscription-based business is doing and can help inform all sorts of financial and operational decisions at the company.

Since writing that example, my same friend running their SaaS business on Crunchy Bridge

Monthly recurring revenue (MRR) and annual recurring revenue (ARR) are important metrics for a subscription-based business model. It allows for the business to project its available capital to make important decisions around expansion, hiring and more.

In an on-demand subscription model, MRR can fluctuate on any given day. This is especially true in the cloud-services world, where people are constantly spinning up and down services. That's why it makes sense to try and capture what the subscription revenue "run rate" is: this is a projection of how much money you are earning over a period of time based upon current service usage.

This exercise recently came up with a friend who was looking for a little help with analyzing and reporting on their SaaS business which is running on Crunchy Bridge

Supporting PostgreSQL DBAs is an important part of daily life here at Crunchy Data. I’ve recently run across a few use cases where utility queries based on the current state of the database are needed. A simple example could be where you have a table that is the target of logical replication and the id

An interesting question came up on the #postgresql IRC channel about how to use native PostgreSQL features to handle queuing behavior. There are existing solutions for queuing, both in PostgreSQL, with the venerable pgq project, or dedicated message queues like RabbitMQ, Kafka, etc. I wanted to explore what could be done with native Postgres primitives and I thought this warranted an entry in my Devious SQL series