It is basically a custom Postgres.

Important: This post uses Terraform to create and destroy resources on an AWS account. Make sure that you destroy the resources as soon and as long as you don’t need them any more. Otherwise, you will be charged more than you might want.

How do you set up a TimescaleDB on AWS?

Unfortunately, you can’t use, at the time of this writing (Jul 2025), the managed AWS RDS Service. Neither the AWS RDS Custom Service.

Subscribe to Hosted TimescaleDB Services

If you want to use TimescaleDB you have some options by companies that offer it as a hosting service.

• TigerData Cloud

• Aiven.io

• ScaleGrid

• and more

Set it Up Manually on your AWS Account

This requires that you are more competent in terms of devops, but it allows you more flexibility.

This is the option I am going to go with in this blog post.

Here is an architecture diagram for the demo project I will develop to demonstrate how I can set up TimescaleDB on AWS.

• I will need access to the EC2 machine in order to install TimescaleDB Server. That’s why the SSH access on port 22. That’s the easiest way to install the software on the EC2 machine. In subsequent blog posts, I will present other ways to install third-party software like TimescaleDB without requiring ssh access.

• I will need access to the database server via psql from a remote machine. That’s why I will need to open the port 5432. However, again, if it were a real production environment, it would make it more secure if direct access to the database was only possible from within the VPC and not from the public internet. This is another topic for another blog post.

TimescaleDB on my AWS Account

Project Setup

Source code here:

https://github.com/pmatsinopoulos/setting_up_timescaledb_on_aws

Third-party Tools

These are the tools and their version at the time of writing: (BTW, I am using asdf as my third-party tools version manager)

• Terraform, version 1.12.2

• direnv, version 2.35.0

Git - Don’t Go Without

I don’t go without git. I also create a .gitignore file. Initially, I have the following folders and files ignored:

.env
.env.*
.envrc
*.pem
*.pem.pub

terraform/.terraform
*.tfstate
*.tfstate.backup

Folder For Terraform Files

I like to create the folder terraform and put everything inside

So, all the Terraform related files will be inside this terraform sub-folder.

Also, when I will be telling you about shell commands that I execute, like terraform validate, they will be executed from within this sub-folder, which I will indicate with the terraform > prefix at the beginning.

Main Terraform Setup

The main.tf file declares the version of Terraform and AWS Provider I will work with.

https://github.com/pmatsinopoulos/setting_up_timescaledb_on_aws/blob/main/terraform/main.tf

terraform {
  required_providers {
    aws = {
      source  = "hashicorp/aws"
      version = "6.0.0"
    }
  }

  required_version = "~>1.12.0"
}

Backend To Save Terraform State

I will leave the default backend (local). However, you might want to use other backends in your own projects.

I define the backend inside the file backend.tf

terraform {
  backend "local" {
    path = "terraform.tfstate"
  }
}

Initialize

With the main.tf and backend.tf in place, I fire the terraform init.

terraform > terraform init

AWS Region

I will use Europe/Frankfurt, which is eu-central-1. I will set it as default value for the variable region inside terraform/variables.tf file. But since this is declared as input variable, you can pass whichever value you want.

AWS Provider

I set up the AWS provider configuration as follows (inside the file providers.tf):

https://github.com/pmatsinopoulos/setting_up_timescaledb_on_aws/blob/main/terraform/providers.tf

# Without +alias+ this is the default +aws+ provider
#
provider "aws" {
  region = var.region
  default_tags {
    tags = {
      project     = var.project
      terraform   = "1"
      environment = var.environment
      tf_repo     = var.repository_url
      tf_folder   = "terraform/${var.environment}"
    }
  }
}

This introduces the following three input variables:

• project

• environment

• repository_url

which I have to declare inside the variables.tf file. I will also set some default values. Set your own values to match your settings:

variable "project" {
  description = "The name of the project."
  type        = string
  default     = "setting_up_timescaledb_on_aws"
}

variable "environment" {
  description = "The environment for the deployment (e.g., development, staging, production)."
  type        = string
  default     = "development"
}

variable "repository_url" {
  description = "The GitHub repository URL for the project."
  type        = string
  default     = "https://github.com/pmatsinopoulos/setting_up_timescaledb_on_aws"
}

VPC

I will use the default VPC in the region selected. There is always a default VPC. It has plenty of IP addresses (172.31.0.0/16 - 65,536 addresses).

Since the VPC already exists and I will not create it, I just need to create a data source reference. I will put that inside the data.tf file:

https://github.com/pmatsinopoulos/setting_up_timescaledb_on_aws/blob/main/terraform/data.tf

data "aws_vpc" "timescaledb_vpc" {
  id = var.vpc_id
}

I am using an input variable to reference the VPC by its id. Which means that I need the input variable definition inside the variables.tf file:

variable "vpc_id" {
  description = "The ID of the VPC where the EC2 instance will be launched in."
  type        = string
}

VPC - Subnet

The default VPC in each region is coming with 3 default subnets each one created in one of the Availability Zones:

• eu-central-1a

• eu-central-1b

• eu-central-1c

I will use the subnet deployed into eu-central-1a. I will pass its id via a variable

variable "subnet_id" {
  description = "The ID of the subnet where the EC2 instance will be launched in."
  type        = string
}

I declare the data source for the subnet inside the data.tf file, in which I declare resources that I reference rather than create.

data "aws_subnet" "timescaledb_subnet" {
  id = var.subnet_id
}

SSH Access Security Group

I will need two security groups, one to allow incoming SSH traffic on port 22. And another one for postgres traffic on port 5432.

Here is how I set up the SSH one. I write its configuration inside the file security_groups.tf.

https://github.com/pmatsinopoulos/setting_up_timescaledb_on_aws/blob/main/terraform/security_groups.tf

resource "aws_security_group" "ssh" {
  name        = "${var.project}-${var.environment}-security-group-ssh"
  description = "Allow SSH traffic from anywhere to anywhere"
  vpc_id      = data.aws_vpc.timescaledb_vpc.id
  ingress {
    description = "SSH from anywhere"
    from_port   = 22
    to_port     = 22
    protocol    = "tcp"
    cidr_blocks = ["0.0.0.0/0"]
  }
  tags = {
    "Name" = "${var.project}-${var.environment}-security-group-ssh"
  }
}

This is opening the incoming traffic from any IP address over the port 22.

Postgres Access Security Group

In the same file, I create another security group that will allow me postgres traffic over the port 5432.

resource "aws_security_group" "postgres" {
  name        = "${var.project}-${var.environment}-security-group-postgres"
  description = "Allow postgres traffic from anywhere"
  vpc_id      = data.aws_vpc.timescaledb_vpc.id
  ingress {
    description = "postgres from anywhere"
    from_port   = 5432
    to_port     = 5432
    protocol    = "tcp"
    cidr_blocks = ["0.0.0.0/0"]
  }
  tags = {
    "Name" = "${var.project}-${var.environment}-security-group-postgres"
  }
}

SSH Key Pair

I will need to create an ssh key pair (private-public) and upload the public part of it on AWS key pairs.

I use ssh-keygen to create the key pair. But you can use any method described here. Please, note that the EC2 instance that I will create to install the TimescaleDB server is going to be a Linux/Ubuntu server. Hence, I follow the instructions to create an ssh key pair that is compatible with Linux/Ubuntu.

This is how I generate the key on my Linux client:

ssh-keygen -m PEM -f timescaledb.pem

Note that when I am being asked for a passphrase, I leave it empty.

This generates two files locally:

• timescaledb.pem

• timescaledb.pem.pub

Important: The .gitignore file should include the entry *.pem to make sure the private part of the key is not checked in.

Important: I use the following command to change the access mode of the private key file. This will make sure that the file is only readable by me.

terraform > chmod 400 "timescaledb.pem"

Now I need to declare the resource so that the public key is uploaded to AWS key pairs. I do that inside the file ec2_key_pair.tf

https://github.com/pmatsinopoulos/setting_up_timescaledb_on_aws/blob/main/terraform/ec2_key_pair.tf

# This imports the public part of an OpenSSH key.
# The key has been generated using `ssh-keygen` in PEM format.
# Example:
#
# ssh-keygen -m PEM
#
# The key has been generated without a passphrase.
#
resource "aws_key_pair" "timescaledb" {
  key_name   = "${var.project}-${var.environment}-timescaledb"
  public_key = file("${path.module}/timescaledb.pem.pub")

  tags = {
    "Name" = "${var.project}-${var.environment}-timescaledb"
  }
}

EC2 Instance

I am now ready to code for the EC2 instance resource that I will use to run my TimescaleDB server.

I write this inside the file ec2_instance.tf.

https://github.com/pmatsinopoulos/setting_up_timescaledb_on_aws/blob/main/terraform/ec2_instance.tf

data "aws_ami" "timescaledb" {
  most_recent = true

  filter {
    name   = "block-device-mapping.delete-on-termination"
    values = ["true"]
  }

  filter {
    name   = "is-public"
    values = ["true"]
  }


  filter {
    name   = "name"
    values = ["ubuntu/images/hvm-ssd-gp3/ubuntu-noble-24.04-amd64-server*"]
  }

  filter {
    name   = "root-device-type"
    values = ["ebs"]
  }

  owners = ["099720109477"] # Canonical
}

resource "aws_instance" "timescaledb" {
  ami               = data.aws_ami.timescaledb.id
  instance_type     = var.timescaledb_server_instance_type
  availability_zone = "${var.region}${var.timescaledb_server_availability_zone}"
  subnet_id         = var.subnet_id
  vpc_security_group_ids = [
    data.aws_security_group.timescaledb_vpc_default_security_group.id,
    aws_security_group.ssh.id,
    aws_security_group.postgres.id,
  ]
  key_name                    = aws_key_pair.timescaledb.key_name
  associate_public_ip_address = true

  tags = {
    "Name" = "${var.project}-${var.environment}-timescaledb"
  }
}

output "ec2_timescaledb_public_ip" {
  value       = aws_instance.timescaledb.public_ip
  description = "The public IP address of the EC2 Timescale DB Machine"
}

output "ssh_connect" {
  value       = "ssh -i ${path.module}/timescaledb.pem -o IdentitiesOnly=yes ubuntu@${aws_instance.timescaledb.public_ip}"
  description = "SSH command to connect to the Timescale DB EC2 instance"
}

The data “aws_ami” “timescaledb” { … } block is used to select the AMI that will be used to launch the EC2 instance described in the following resource “aws_instance” “timescaledb” { … }

You can see how the resource block is referencing the data block:

...
ami               = data.aws_ami.timescaledb.id
...

Inside the resource block, I am setting the very minimum properties so that can I launch the EC2 instance as I want it.

Outputs:

I also make sure that I export two outputs:

• ec2_timescaledb_public_ip: the public IP of the EC2 machine.

• ssh_connect: the command to use locally if I want to SSH to the EC2 machine.

Values to terraform Input Variables

Most of the terraform commands that I will need in this project will require me to have a method to pass values to the input variables that they don’t have a default value.

There are plenty of ways you can pass values to Terraform input variables. For this particular demo, I prefer the method of environment variables.

So, I make sure that the terminal shell that I use to execute the terraform commands is set with correct values for the environment variables that terraform needs.

The tool that is helping me do that is direnv. I have created the terraform/.envrc file with the following contents:

See terraform/.envrc.sample and create your own terraform/.envrc.

export AWS_PROFILE=...<put here the name of the AWS profile you have configured>...
export TF_VAR_subnet_id=...<put here the sunet id from your VPC>...
export TF_VAR_vpc_id=...<put here the id of your VPC>...

Note that terraform/.envrc is being git ignored and it is not checked-in. This is to avoid checking in sensitive secret information. I have the file terraform/.envrc.sample which is checked-in and is a template to tell the reader how the actual terraform/.envrc file should be structured.

AWS_PROFILE

The aws provider needs a way to connect to my AWS account. One way that I like very much to use is AWS CLI Profiles. And the AWS_PROFILE is holding the name of the profile that I have configured on my local machine and that I want to use for this project.

If you want to use this method, follow the instructions here and here.

Checkpoint 1

I will now double-check that everything works OK up until this point.

I run

terraform > terraform validate

It needs to print

Success! The configuration is valid.

And then I will actually create the resources.

Important! This is the point where costs will be incurred in your AWS Account.

terraform > terraform apply

I am being presented with the plan and prompt to reply with yes, if I want to apply the plan:

Terraform will perform the following actions:

  # aws_instance.timescaledb will be created
  + resource "aws_instance" "timescaledb" {
      ...
    }

  # aws_key_pair.timescaledb will be created
  + resource "aws_key_pair" "timescaledb" {
      ...
    }

  # aws_security_group.postgres will be created
  + resource "aws_security_group" "postgres" {
      ...
    }

  # aws_security_group.ssh will be created
  + resource "aws_security_group" "ssh" {
      ...
    }

Plan: 4 to add, 0 to change, 0 to destroy.

Changes to Outputs:
  + ec2_timescaledb_public_ip = (known after apply)
  + ssh_connect               = (known after apply)

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value: 

I reply with yes and it is taking about 2 minutes for everything to be ready.

Then I use the AWS Management Console to check upon the state of the new EC2 Instance. I am waiting for it to become Running:

Then I use the ssh_connect output to connect to the instance using ssh command. From the terraform folder again, I execute the command: (The shell first executes the command terraform output ssh_connect and then takes its output and executes again).

terraform > $(terraform output -raw ssh_connect)

After I reply with yes to save the remote key to known hosts, I am inside the EC2 machine with an ssh shell:

Welcome to Ubuntu 24.04.2 LTS (GNU/Linux 6.8.0-1030-aws x86_64)

 ...

To run a command as administrator (user "root"), use "sudo <command>".
See "man sudo_root" for details.

ubuntu@ip-172-31-25-169:~$

EBS Volume - Data Storage

So far, so good.

But the EC2 instance doesn’t have TimescaleDB installed.

Also, it doesn’t have a specific instance-independent storage to save the actual database data.

I want:

1. Every time the EC2 instance is created (or recreated) to have the TimescaleDB installed AND

2. Make sure that, if the EC2 instance is created (or recreated), the TimescaleDB data created from previous instance runs, it survives and continues to exist; i.e. I want make sure that I don’t lose my data every time the EC2 instance, for any reason, it is destroyed and then created from the beginning.

Outline Of Steps

In order to satisfy these requirements:

• I will create the AWS EBS volume (using the resource ”aws_ebs_volume”), then

• I will attach it to the EC2 instance (using the resource ”aws_volume_attachment“, then

• I will automatically connect to the EC2 instance and

  • create a file system device to represent the attached EBS volume

  • create the /data directory and mount it to the device created

  • install Postgres and configure TimescaleDB extension

  • configure Postgres to use the /data folder for storing data.

AWS EBS Volume Resource

This is the resource configuration for the AWS EBS volume:

file: terraform/ec2_volume.tf

resource "aws_ebs_volume" "timescaledb_volume" {
  availability_zone = "${var.region}${var.timescaledb_server_availability_zone}"
  size              = 64
  type              = "gp3"
  encrypted         = false
  final_snapshot    = false

  tags = {
    Name = "${var.project}-${var.environment}-timescaledb-volume"
  }
}

Note: the final_snapshot has been set to false. You may want to turn this to true to allow for AWS to create a snapshot when you destroy this volume, just in case you want to recover old data. But setting to true will incur costs.

This is quite minimum and many values are hard-coded. For example, the size is set to 64 GiB. And the type is set to gp3. Apparently, go ahead and set your own values.

If I terraform apply, Terraform is going to create an AWS EBS Volume which will be standing alone, not attached to any EC2 instance.

Attach New Volume to EC2 Instance

This is how I am attaching the new volume to the EC2 instance:

file: terraform/ec2_volume.tf

resource "aws_volume_attachment" "timescaledb_volume_attachment" {
  device_name = "/dev/sdd"
  volume_id   = aws_ebs_volume.timescaledb_volume.id
  instance_id = aws_instance.timescaledb.id
}

It’s quite simple, isn’t it.

But then, one might ask, how did I come up with the value ”/dev/sdd” for the device_name?

Explain /dev/sdd

If I look at the details of the AMI that I use, I get this picture with regards to block devices that come with it:

There are 3 devices that AMI offers to EC2 instances launched with this AMI. Only one of the 3 devices, the one with name /dev/sda1 , is used as the 8GiB root device. This is where the EC2 instance is boot from. The devices /dev/sdb and /dev/sdc are ephemeral virtual devices which are not yet used by the EC2 Instance.

This is another picture (taken from AWS documentation) of what’s going on with storage in this EC2 instance:

And if I look at the “Storage” tab on the instance details, I will see this:

So, the EC2 instance uses the /dev/sda1 device.

But how do I finally choose the name /dev/sdd for the new volume that I am attaching? The AWS documentation on device naming has the details.

According to the documentation and the AMI virtualization type I am here:

Since /dev/sdb and /dev/sdc are already taken , I am going with /dev/sdd.

Note that I am not interested in mounting the /dev/sdb and /dev/sdc ephemeral devices. So, I just leave them unmounted.

Hence, after I terraform apply the new resource timescaledb_volume_attachment, the “Storage” tab on the instance picture will be:

I can see the new volume attached using the device name /dev/sdd.

Prepare To Store Data

But even if the EBS volume is attached to the EC2 machine, this doesn’t mean that it is ready to write data on it.

If I ssh to the new EC2 instance and do the following command:

$ lsblk

I will see something like this:

NAME         MAJ:MIN RM  SIZE RO TYPE MOUNTPOINTS
loop0          7:0    0 27.2M  1 loop /snap/amazon-ssm-agent/11320
loop1          7:1    0 73.9M  1 loop /snap/core22/2010
loop2          7:2    0 50.9M  1 loop /snap/snapd/24718
nvme0n1      259:0    0    8G  0 disk 
├─nvme0n1p1  259:1    0    7G  0 part /
├─nvme0n1p14 259:2    0    4M  0 part 
├─nvme0n1p15 259:3    0  106M  0 part /boot/efi
└─nvme0n1p16 259:4    0  913M  0 part /boot
nvme1n1      259:5    0   64G  0 disk 

The last entry nvme1n1 of size 64G corresponds to the newly attached AWS EBS volume. But, it doesn’t have any partition (like the nvme0n1 does for example).

I need to format and create the logical partitions. Then we will have to mount a directory on it.

I have several ways to do that with Terraform. I chose to go with terraform_data resource.

I create the file prepare_ebs_volume.tf with the following content:

file: terraform/prepare_ebs_volume.tf

resource "terraform_data" "prepare_ebs_volume_for_writing" {
  depends_on = [aws_volume_attachment.timescaledb_volume_attachment]

  triggers_replace = {
    volume_attachment = aws_volume_attachment.timescaledb_volume_attachment.id
  }

  connection {
    type        = "ssh"
    user        = "ubuntu"
    host        = aws_instance.timescaledb.public_ip
    private_key = file("${path.module}/timescaledb.pem")
  }

  provisioner "remote-exec" {
    inline = concat(
      [
        "sudo file -s /dev/nvme1n1 | grep -q '/dev/nvme1n1: data$' && sudo mkfs -t xfs /dev/nvme1n1",
        "sudo mkdir /data",
        "sudo mount /dev/nvme1n1 /data",
        "sudo cp /etc/fstab /etc/fstab.bak",
        "echo \"UUID=$(sudo blkid -s UUID -o value /dev/nvme1n1) /data xfs defaults,nofail 0 2\" | sudo tee -a /etc/fstab"
      ]
    )
  }
}

1. the depends_on is critical in order to make sure that resources are created in correct order, but

2. the triggers_replace is critical to trigger reconstructions/recreations when the resource already exists. And for this particular case, if the EBS volume is reattached (see for example later on the case in which EC2 instance is destroyed and recreated again), we want this provisioner to fire again.

3. mkfs creates a files system of type xfs on the new volume. Note, that I call mkfs only if there is not file system already on the volume, in order to avoid overwriting any existing data.

4. mkdir creates the /data directory. This is where the Postgres/TimescaleDB will be writing data. See later on.

5. mount mounts the volume to the directory /data.

6. the echo…. updates the /etc/fstab file so that when the instance is ever rebooted to have the volume mounted to /data automatically.

Note: If you want to read more about this process read the official AWS documentation here.

I do terraform apply and then I ssh to the EC2 instance.

The command lsblk now prints:

NAME         MAJ:MIN RM  SIZE RO TYPE MOUNTPOINTS
loop0          7:0    0 27.2M  1 loop /snap/amazon-ssm-agent/11320
loop1          7:1    0 73.9M  1 loop /snap/core22/2010
loop2          7:2    0 50.9M  1 loop /snap/snapd/24718
nvme0n1      259:0    0    8G  0 disk 
├─nvme0n1p1  259:1    0    7G  0 part /
├─nvme0n1p14 259:2    0    4M  0 part 
├─nvme0n1p15 259:3    0  106M  0 part /boot/efi
└─nvme0n1p16 259:4    0  913M  0 part /boot
nvme1n1      259:5    0   64G  0 disk /data

Look how the nvme1n1. Instead of just disk it shows /data.

Bingo! If you go back and read the Outline of Steps above, you will see that I have taken these two steps already:

• create a file system device to represent the attached EBS volume

• create the /data directory and mount it to the device created

Next two steps:

Install Postgres and TimescaleDB extension

I will now add two more terraform_data blocks that will take care of installing Postgres and TimescaleDB extension and configuring it to store data into the /data folder (which now resides in the new EBS volume).

I name the file install_and_setup_timescaledb.tf.

file: terraform/install_and_setup_timescaledb.tf.

locals {
  db_name                   = "events_server_${var.environment}"
  path_to_postgres_data_dir = "/data/postgresql/${var.postgresql_version}/main"
}

resource "terraform_data" "install_and_setup_timescaledb" {
  depends_on = [terraform_data.prepare_ebs_volume_for_writing]

  triggers_replace = {
    volume_attachment  = aws_volume_attachment.timescaledb_volume_attachment.id
    postgresql_version = var.postgresql_version
  }

  connection {
    type        = "ssh"
    user        = "ubuntu"
    host        = aws_instance.timescaledb.public_ip
    private_key = file("${path.module}/timescaledb.pem")
  }

  provisioner "file" {
    source      = "${path.module}/install_postgres.sh"
    destination = "/home/ubuntu/install_postgres.sh"
  }

  provisioner "file" {
    source      = "${path.module}/install_timescaledb.sh"
    destination = "/home/ubuntu/install_timescaledb.sh"
  }

  provisioner "remote-exec" {
    inline = concat(
      [
        "sudo chmod u+x /home/ubuntu/install_postgres.sh",
        "sudo ./install_postgres.sh ${var.postgresql_version} ${var.timescaledb_server_port}  ${local.db_name} ${var.timescaledb_version}",

        "sudo chmod u+x /home/ubuntu/install_timescaledb.sh",
        "sudo ./install_timescaledb.sh ${var.postgresql_version} ${var.timescaledb_version} ${local.db_name}"
      ]
    )
  }
}

resource "terraform_data" "postgres_password" {
  depends_on = [terraform_data.install_and_setup_timescaledb]

  triggers_replace = {
    volume_attachment  = aws_volume_attachment.timescaledb_volume_attachment.id
    postgresql_version = var.postgresql_version
  }

  connection {
    type        = "ssh"
    user        = "ubuntu"
    host        = aws_instance.timescaledb.public_ip
    private_key = file("${path.module}/timescaledb.pem")
  }

  provisioner "remote-exec" {
    inline = concat(
      [
        "echo '**************** remote-exec: Setting the postgres user password...'",
        "sudo -u postgres psql -c \"ALTER USER postgres WITH PASSWORD '${var.timescaledb_server_postgres_password}';\""
      ]
    )
  }
}

output "psql_connect" {
  value       = "psql -h ${aws_instance.timescaledb.public_ip} -U postgres -d ${local.db_name} -p ${var.timescaledb_server_port}"
  description = "Command to connect to the PostgreSQL database using psql."
}

Dependency: I put a dependency of the first resource to the previous one, i.e. of the install_and_setup_timescaledb to the terraform_data.prepare_ebs_volume_for_writing. Hence, the remote-exec calls are going to be executed after that block’s commands do.

Triggers Replace: However, besides the order of creation which is specified with the dependends_on block, I also use the triggers_replace block to specify that I want this block to be recreated under these two conditions:

• Every time the attachment of the EBS volume is recreated.

• Every time the version of the Postgres server changes.

Two bash Scripts

I use two bash scripts:

• Bash script to install Postgres: install_postgres.sh.

• Bash script to install TimescaleDB: install_timescaledb.sh.

Note that both scripts are also called when upgrading Postgres version.

Installing Postgres

The file install_postgres.sh has the following contents:

file terraform/install_postgres.sh.

#!/bin/bash

set -e # e: exit if any command has a non-zero exit status
set -x # x: all executed commands are printed to the terminal
set -u # u: all references to variables that have not been previously defined cause an error

NEW_VERSION=$1
PORT=$2
DB_NAME=$3
TIMESCALEDB_VERSION="${4}*"

# Create the PostgreSQL data directory
sudo mkdir -p /data/postgresql

# Update the package list
sudo DEBIAN_FRONTEND=noninteractive apt update -y

# Install postgres common tools
sudo DEBIAN_FRONTEND=noninteractive apt install -y postgresql-common apt-transport-https net-tools

# Enable the PostgreSQL APT repository
sudo DEBIAN_FRONTEND=noninteractive /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y

# Let's now install on top of any existing
CURRENT_CLUSTER=$(sudo pg_lsclusters -h 2>/dev/null | grep "${PORT}" | awk '{print $1"-"$2}' || true)

INSTALL="yes"
OLD_VERSION=""
OLD_NAME="main"
if [ -n "$CURRENT_CLUSTER" ]; then
  echo "Found existing PostgreSQL cluster:"
  echo "$CURRENT_CLUSTER"
  echo

  OLD_VERSION=$(echo $CURRENT_CLUSTER | cut -d'-' -f1)
  OLD_NAME=$(echo $CURRENT_CLUSTER | cut -d'-' -f2)

  # Skip if this is the same version and name we're about to install
  if [ "$OLD_VERSION" = "$NEW_VERSION" ] && [ "$OLD_NAME" = "main" ]; then
      echo "Skipping cluster $OLD_VERSION/$OLD_NAME as it matches the target version and name"
      INSTALL="no"
      continue
  fi

  echo "************************ stopping and disabling $OLD_VERSION/$OLD_NAME ************************"
  sudo systemctl stop postgresql@$OLD_VERSION-$OLD_NAME || true
  sudo systemctl status postgresql@$OLD_VERSION-$OLD_NAME --no-pager || true
  sudo systemctl disable postgresql@$OLD_VERSION-$OLD_NAME || true
  sudo pg_ctlcluster stop $OLD_VERSION $OLD_NAME || true
  sudo pg_lsclusters -h
else
  echo "No existing PostgreSQL clusters found on port ${PORT}. Proceeding with installation."
  echo

  INSTALL="yes"
fi

echo "****************** INSTALL: $INSTALL"
if [ "$INSTALL" = "yes" ]; then
  sudo DEBIAN_FRONTEND=noninteractive apt install -y postgresql-$NEW_VERSION postgresql-client-$NEW_VERSION postgresql-contrib-$NEW_VERSION
  sudo DEBIAN_FRONTEND=noninteractive apt install -y postgresql-server-dev-$NEW_VERSION
  # When I install postgresql for the first time, the cluster is already created.
  # But when I install a new version while another already exists, the cluster is not created.
  if sudo pg_lsclusters -h 2>/dev/null | grep -q "^${NEW_VERSION}[[:space:]]\+main[[:space:]]"; then
    echo "Cluster $NEW_VERSION/main already exists"
  else
    echo "Creating cluster $NEW_VERSION/main"
    sudo pg_createcluster $NEW_VERSION main
    sudo pg_ctlcluster start $NEW_VERSION main
    sudo pg_lsclusters -h
  fi
  sudo systemctl start postgresql@${NEW_VERSION}-main
  sudo systemctl enable postgresql@$NEW_VERSION-main
else
  echo "Skipping installation of PostgreSQL ${NEW_VERSION} as it is already installed."
fi

# Show final status

echo "Current PostgreSQL clusters:"
sudo pg_lsclusters -h

sudo systemctl status postgresql@${NEW_VERSION}-main --no-pager

if [ -n "$OLD_VERSION" ]; then
  echo "Stopping and disabling old PostgreSQL cluster $OLD_VERSION/$OLD_NAME"
  sudo pg_ctlcluster stop $OLD_VERSION $OLD_NAME || true
else
  echo "No old PostgreSQL cluster to stop."
fi

# Stop postgres from running
sudo systemctl stop postgresql@${NEW_VERSION}-main

# Change where PostgreSQL stores its data
ORIGINAL_DATA_DIR="/var/lib/postgresql/${NEW_VERSION}/main"

if [ -d "${ORIGINAL_DATA_DIR}" ];then
  if [ -d "/var/lib/postgresql/${NEW_VERSION}/main.bak" ]; then
    rm -f -R /var/lib/postgresql/${NEW_VERSION}/main.bak
  fi
  sudo mv ${ORIGINAL_DATA_DIR} /var/lib/postgresql/${NEW_VERSION}/main.bak
fi

NEW_PATH_TO_POSTGRES_DATA_DIR="/data/postgresql/${NEW_VERSION}/main"

sudo mkdir -p ${NEW_PATH_TO_POSTGRES_DATA_DIR}
sudo chown -R postgres:postgres /data/postgresql
if [ -n "$(sudo ls -A ${NEW_PATH_TO_POSTGRES_DATA_DIR} 2>/dev/null)" ]; then
  echo "The new PostgreSQL data directory is not empty. We will not initialize it."
else
  echo "Initializing new PostgreSQL data directory at ${NEW_PATH_TO_POSTGRES_DATA_DIR}"
  sudo -u postgres /usr/lib/postgresql/${NEW_VERSION}/bin/initdb -D ${NEW_PATH_TO_POSTGRES_DATA_DIR}
fi
sudo sed -i "s|data_directory = '${ORIGINAL_DATA_DIR}'|data_directory = '${NEW_PATH_TO_POSTGRES_DATA_DIR}'|g" /etc/postgresql/${NEW_VERSION}/main/postgresql.conf

# Set the port to whatever we specify as port in the terraform variables
sudo sed -i "s|port = .*|port = ${PORT}|g" /etc/postgresql/${NEW_VERSION}/main/postgresql.conf

# Allow remote connections
sudo sed -i "s|#listen_addresses = 'localhost'|listen_addresses = '*'|g" /etc/postgresql/${NEW_VERSION}/main/postgresql.conf
sudo sed -i "s|host    all             all             127.0.0.1/32            scram-sha-256|host    all             all             0.0.0.0/0            scram-sha-256|g" /etc/postgresql/${NEW_VERSION}/main/pg_hba.conf

# upgrade from previous version if needed
LAST_CLUSTER=$(echo "$CURRENT_CLUSTER" | tail -n 1)
if [ -n "$LAST_CLUSTER" ]; then
    OLD_VERSION=$(echo $LAST_CLUSTER | cut -d'-' -f1)
    OLD_NAME=$(echo $LAST_CLUSTER | cut -d'-' -f2)
    if [ "$OLD_VERSION" = "$NEW_VERSION" ] && [ "$OLD_NAME" = "main" ]; then
      echo "...no need to upgrade data, we are on the same cluster version and name"
    else
      echo "We need to upgrade the data of the last cluster ${OLD_VERSION}-${OLD_NAME}"
      # We will need to install timescale db for the new version, otherwise the pg_upgrade will fail
      # ---------------------------------------------------------------------------------------------
      sudo apt install timescaledb-2-postgresql-${NEW_VERSION}="${TIMESCALEDB_VERSION}" timescaledb-2-loader-postgresql-${NEW_VERSION}="${TIMESCALEDB_VERSION}" timescaledb-toolkit-postgresql-${NEW_VERSION} -y

      sudo sed -i "s|#shared_preload_libraries = ''|shared_preload_libraries = 'timescaledb'|g" /data/postgresql/${NEW_VERSION}/main/postgresql.conf

      # Tune TimescaleDB
      sudo timescaledb-tune --yes
      # --------------- end of installing timescale db for the new version -----------------------------

      (cd /tmp && sudo -u postgres /usr/lib/postgresql/${NEW_VERSION}/bin/pg_upgrade \
        --old-datadir=/data/postgresql/$OLD_VERSION/$OLD_NAME \
        --new-datadir=/data/postgresql/$NEW_VERSION/main \
        --old-bindir=/usr/lib/postgresql/$OLD_VERSION/bin \
        --new-bindir=/usr/lib/postgresql/$NEW_VERSION/bin)
    fi
fi

# Restart PostgreSQL to apply changes
sudo systemctl restart postgresql@${NEW_VERSION}-main

# Wait for PostgreSQL to start
sleep 10

# Create the database
sudo -u postgres psql -c "create database ${DB_NAME};" || echo "Database ${DB_NAME} already exists, skipping creation."

I believe that the comments in-between the lines of bash script statements are enough to understand what is going on.

Note that the script makes sure that:

• it can be used to upgrade from one Postgres version to another. Downgrades don’t work though.

• It makes sure that it upgrades the data too.

Installing TimescaleDB

The file install_timescaledb.sh has the following contents:

file: terraform/install_timescaledb.sh.

#!/bin/bash

set -e # e: exit if any command has a non-zero exit status
set -x # x: all executed commands are printed to the terminal
set -u # u: all references to variables that have not been previously defined cause an error

POSTGRESQL_VERSION=$1
TIMESCALEDB_VERSION="${2}*"
DB_NAME=$3
# Set TimescaleDB repository at system repositories so that we can install TimescaleDB
echo "deb https://packagecloud.io/timescale/timescaledb/ubuntu/ $(lsb_release -c -s) main" | sudo tee /etc/apt/sources.list.d/timescaledb.list
wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo gpg --dearmor --yes -o /etc/apt/trusted.gpg.d/timescaledb.gpg
sudo DEBIAN_FRONTEND=noninteractive apt update -y

sudo systemctl stop postgresql@${POSTGRESQL_VERSION}-main || true

# Install TimescaleDB and TimescaleDB Toolkit all TimescaleDB postgres related packages
sudo apt install timescaledb-2-postgresql-${POSTGRESQL_VERSION}="${TIMESCALEDB_VERSION}" timescaledb-2-loader-postgresql-${POSTGRESQL_VERSION}="${TIMESCALEDB_VERSION}" timescaledb-toolkit-postgresql-${POSTGRESQL_VERSION} -y

sudo sed -i "s|#shared_preload_libraries = ''|shared_preload_libraries = 'timescaledb'|g" /data/postgresql/${POSTGRESQL_VERSION}/main/postgresql.conf

# Tune TimescaleDB
sudo timescaledb-tune --yes

sudo systemctl restart postgresql@${POSTGRESQL_VERSION}-main

# Create the extensions for TimescaleDB and TimescaleDB Toolkit
sudo -u postgres psql -d ${DB_NAME} -c "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;"
sudo -u postgres psql -d ${DB_NAME} -c "CREATE EXTENSION IF NOT EXISTS timescaledb_toolkit CASCADE;"

Again, this script is idempotent, and allows for upgrades of the timescale db.

It installs the TimescaleDB extension and the TimescaleDB Toolkit.

Also, it sets some common default values for TimescaleDB configuration.

Setting the Password

The second terraform_data resource is about setting the password for postgres user. I have it in a separate block, because it is using sensitive data and if I put it on the first block, Terraform would hide all output from that block (Postgres installation and setup commands) which I wouldn’t want.

Important! This scripts introduces more variables. My variables.tf needs to have these too:

variable "postgresql_version" {
  description = "The PostgreSQL version to install on the TimescaleDB server."
  type        = string
  default     = "16"
}

variable "timescaledb_version" {
  description = "The TimescaleDB version to install on the TimescaleDB server."
  type        = string
  default     = "2.19.3"
}

variable "timescaledb_server_port" {
  description = "Port for the TimescaleDB server."
  type        = number
  default     = 5432
}

variable "timescaledb_server_postgres_password" {
  description = "The password for the PostgreSQL user on the TimescaleDB server."
  type        = string
  sensitive   = true
}

Note: At the time of this writing, the latest Postgres version was 17, but, in the file above I choose to install version 16, to start with. Later in the post below, you will see how I choose to upgrade to 17, in order to demonstrate the Postgres upgrade process.

Important! Look also at the .envrc file (or other file that you keep your environment variables that set values to the Terraform input variables), you need to add the TF_VAR_timescaledb_server_postgres_password=…

...
export TF_VAR_timescaledb_server_postgres_password='...<put your postgres password here>...'

If I do terraform apply, at the end, I have my TimescaleDB ready.

I use this to connect (from terraform folder):

$ (terraform output -raw psql_connect)

And then I key in the postgres user password and I am on a psql terminal.

Look at the output of some commands.

Check that Extensions are Installed

I connect to the events_server_development database and I issue the command to get the extensions installed:

postgres=# \c events_server_development 
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "events_server_development" as user "postgres".
events_server_development=# select * from pg_extension;
  oid  |       extname       | extowner | extnamespace | extrelocatable | extversion |                                                                                 extconfig                                                                                 |                                                              extcondition                                                               
-------+---------------------+----------+--------------+----------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------
 13610 | plpgsql             |       10 |           11 | f              | 1.0        |                                                                                                                                                                           | 
 17140 | timescaledb_toolkit |       10 |         2200 | f              | 1.21.0     |                                                                                                                                                                           | 
 16385 | timescaledb         |       10 |         2200 | f              | 2.19.3     | {16406,16407,16429,16443,16442,16462,16461,16477,16476,16502,16518,16519,16536,16535,16555,16556,16611,16624,16651,16664,16674,16684,16688,16704,16715,16731,16740,16739} | {"","WHERE id >= 1","","","","","","","","","","","","","","WHERE id >= 1000"," WHERE key <> 'uuid' ","","","","","","","","","","",""}
(3 rows)

Creating a Table and A Row

I am creating a table and inserting a row.

postgres=# \c events_server_development 
psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1), server 17.5 (Ubuntu 17.5-1.pgdg24.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "events_server_development" as user "postgres".
events_server_development=# create table events (id bigint not null, type varchar not null, created_at timestamp default curren
t_timestamp not null, primary key(id));
CREATE TABLE
Time: 37,602 ms
events_server_development=# insert into events (id, type) values (1, 'product_sold');
INSERT 0 1
Time: 31,945 ms
events_server_development=# select * from e  vents;
 id |     type     |         created_at         
----+--------------+----------------------------
  1 | product_sold | 2025-07-06 12:37:03.418051
(1 row)

Time: 31,053 ms

So, I have created the table events and I have added 1 row into it.

Let’s now see some scenarios under which we would like to keep our data intact.

What if EC2 Instance is Upgraded?

The current EC2 instance type is t3.xlarge. What If I want to scale up to t3.2xlarge for example.

I update the default value of the variable timescaledb_server_instance_type to t3.2xlarge and I do terraform apply.

The plan presented is very simple:

  # aws_instance.timescaledb will be updated in-place
  ~ resource "aws_instance" "timescaledb" {
        id                                   = "i-069be2f31daf52118"
      ~ instance_type                        = "t3.xlarge" -> "t3.2xlarge"
      ~ public_dns                           = "ec2-63-178-242-62.eu-central-1.compute.amazonaws.com" -> (known after apply)
      ~ public_ip                            = "63.178.242.62" -> (known after apply)
        tags                                 = {
            "Name" = "setting_up_timescaledb_on_aws-development-timescaledb"
        }
        # (35 unchanged attributes hidden)

        # (9 unchanged blocks hidden)
    }

Plan: 0 to add, 1 to change, 0 to destroy.

Only a change of the instance type.

I apply and then I connect to TimescaleDB with psql. It works. My data are still there.

What if EC2 Instance is Replaced?

I do terraform taint aws_instance.timescaledb to mark the EC2 instance as something that needs to be destroyed and created again by Terraform.

Then I do terraform apply. I see that I am being presented with a plan to

• destroy and create the EC2 instance

• destroy and recreate the EBS volume attachment.

• destroy and recreate the terraform_data prepare_ebs_volume_for_writing.

• destroy and recreate the terraform_data install_and_setup_timescaledb.

It seems that this is exactly what I want. New EC2 instance and the EBS volume to be detached from the old and attached to the new. Plus, installation of postgres on the new machine.

I proceed.

Then I test that I can connect with psql and that I can find my data in place.

Boom! Data there! All good!

What if the EBS Volume is Scaled Up?

I go to the ec2_volume.tf file and I increase the storage size from 64 to 128.

Then I do terraform apply.

I am being presented with a simple plan that the EBS volume will be updated in-place.

I then connect to the database and I can still see my data intact.

What If Postgres Version is Upgraded?

I change the default value of the postgresql_version variable from 16 to 17. Thanks to the triggers_replace of the resources install_and_setup_timescaledb and postgres_password, these resources will be recreated/replaced.

After I do terraform apply I then connect to the Postgres server. I can still see my events table and events record.

What If TimescaleDB Version is Upgraded?

This is not supported by the current script. Upgrading the TimescaleDB minor and/or major version is a quite involved process which is described here: Upgrade TimescaleDB.

What Could Be Improved?

This setup is good to start, but it has plenty of room for improvement in terms of production readiness

• One can implement a read-replica of the main database. The read-replica could be used to scale the server horizontally, by serving the read requests.

• Automated backups and quick restore from backups should be set up.

• Cloudwatch alarms should be implemented on

  • CPU

  • Memory

  • Free Storage

• Allow direct access only from within VPC.

• Implement a Multi-AZ deployment and automatic switch over from master to stand-by server on another zone.

• Use of AWS Systems Manager to install Postgres and TimescaleDB and manage upgrades.

• Have a Terraform provider that would allow setting TimescaleDB specific parameters.

• Attach the same Elastic IP, in case one wants to expose the same public IP.

Closing Thoughts

Running your own, self-hosted TimescaleDB on AWS is quite possible. The above work demonstrates that. However, it needs more work if this was to become a real production environment.

gm! I am Panos Matsinopoulos, humble software reader, writer, classical music and V8 engine lover. I work for Talent Protocol, where we make builders get the recognition they deserve.

The Table

I have a table named daily_stats, which keeps track of some daily metrics. Here it is:

The structure of the table:

-- CREATE TABLE "daily_stats" ----------------------------------
CREATE TABLE "public"."daily_stats" ( 
  "id" BigInt DEFAULT nextval('daily_stats_id_seq'::regclass) NOT NULL,
  "metric" Character Varying NOT NULL,
  "date" Date NOT NULL,
  "count" BigInt DEFAULT 0 NOT NULL,
  "lock_version" BigInt DEFAULT 0 NOT NULL,
  "created_at" Timestamp( 6 ) Without Time Zone NOT NULL,
  "updated_at" Timestamp( 6 ) Without Time Zone NOT NULL,
  PRIMARY KEY ( "id" ) );
 ;
-- -------------------------------------------------------------

-- CREATE INDEX "daily_stats_metric_date_uidx" -----------------
CREATE UNIQUE INDEX "daily_stats_metric_date_uidx" ON "public"."daily_stats" USING btree( "metric" Asc NULLS Last, "date" Asc NULLS Last );
-- -------------------------------------------------------------

I have 3 columns, mainly:

• metric which identifies the metric I keep counts for

• date which identifies the date I keep counts for

• count which is the actual count for the given metric and date

And, as you can see from the CREATE INDEX… above, the (metric,date) pair has a unique index, which means that I can’t have the same metric for the same date more than once.

I will not tell you how I populate this table. It is out of scope of this blog post. But I will tell you how I built some useful queries on top of it.

Querying by a Date Range

I want to get a result set for a given date range, date_from .. date_to.

I can just simply do:

(assuming that date_from = ‘2025-04-29’ and date_to = ‘2025-05-03’)

select * from "daily_stats" 
where date between '2025-04-29' and '2025-05-03'
order by date desc

That is good and easy!

But what if a metric doesn’t have a count entry for a given date within the range?

Is this a problem? Generally not, but if you want to send the results to the front-end to draw a line chart with the date series from the first date to the last, there will be gaps.

Querying by a Date Range - Filling in the Missing Dates With Count 0

Look at the result set which the previous, simple query, might return:

How can I return 0 for that date for that metric?

Step 1 - Build a Result Set with All Dates Within Date Range

First, I need to build a set will all the dates within date range.

I want something like this:

This is quite easy in Postgres. The select I need is this:

 select generate_series(
    date '2025-04-25',
    date '2025-05-03',
    interval '1 day'
)::date as date

One could say: Let’s just do left join with the daily_stats on the date column. So, for each date that does not exist in daily_stats it will just give NULL which can be coalesce to 0. But this is not correct. Because we want a 0 entry for each metric that does not have counter for the particular date.

Step 2 - All Metrics

Lets first get all the metrics.

select distinct metric from daily_stats;

This returns something like this:

Step 3 - Dates and Metrics Combinations

Now, I want all the dates with all the metrics combinations.

The key here is cross join:

I will use the following select which also incorporates with i.e. the CTE in Postgres:

Want to read more about CTEs? Read this.

with all_dates as (
  select generate_series(
     date '2025-04-25',
     date '2025-05-03',
     interval '1 day'
   )::date as date
),

all_metrics as (
  select distinct metric from daily_stats
)
 
select * from all_dates
cross join all_metrics
order by all_dates.date desc, all_metrics.metric asc
;

When I run this, it gives me a result set like this:

Step 4 - Final: Left Join With Daily Stats

Now that I have all the combinations of dates and metrics, I can left join with the daily_stats and for the cases which I don’t have a count for, I will just display a 0.

with all_dates as (
  select generate_series(
     date '2025-04-25',
     date '2025-05-03',
     interval '1 day'
   )::date as date
),

all_metrics as (
  select distinct metric from daily_stats
),

all_dates_and_metrics as (
  select * from all_dates
  cross join all_metrics
)

select
  adm.date,
  adm.metric,
  coalesce(ds.count, 0) as count
from all_dates_and_metrics adm
left join daily_stats ds on ds.date = adm.date and ds.metric = adm.metric
order by adm.date desc, adm.metric asc
;

This returns the results with the missing dates filled with 0s:

Which allows me to draw nice line charts for all the metrics:

Getting Cumulative Counts

daily_stats#count gives me the count for a given metric for a given date.

What if I want to get the cumulative count. I.e. the accumulated count up until a date, still with a date range?

Step 1 - Cumulative Counts on Daily Stats

Getting the cumulative counts from the daily stats is easy:

select
  date,
  metric,
  sum(count) over (
    partition by metric
    order by date asc
    rows between unbounded preceding and current row
) as cumulative_count
from daily_stats
order by date desc, metric asc

We use the window function sum(count) over (…) The partition by metric would have been enough if I wanted sums by metric only. But here we want to partition our data by metric, but for each partition, to order them by date and sum all the counts up to the current row. This is what the window function does.

The above will return data like this:

Although accumulation works, the dates for which a metric does not have a count are missing.

Important: This result set, on the first date of the date range, i.e. on 2025-04-25, it has the correct accumulated values up until that date:

Can we just generate the date series and left join as we did with the previous case?

Not exactly, because we don’t want to fill in the missing rows with 0. For example, the base_basename case for 2025-05-02 should be 660,101 which is the value the metric had on the 2025-04-30, the latest date before the 2025-05-02 that it had its value accumulated.

Step 2 - Date Series + Dates Metrics Combinations + Cumulative + Left Join+ lateral

The solution is to use left join and lateral sub-query.

But, before we see the actual solution, let’s see what is NOT the solution:

Don’t do this. This is not the solution:

One might say: let’s use the window function on the result set of the previous case:

with all_dates as (
  select generate_series(
     date '2025-04-25',
     date '2025-05-03',
     interval '1 day'
   )::date as date
),

all_metrics as (
  select distinct metric from daily_stats
),

all_dates_and_metrics as (
  select * from all_dates
  cross join all_metrics
),

with_all_counts as (
  select
    adm.date,
    adm.metric,
    coalesce(ds.count, 0) as count
  from all_dates_and_metrics adm
  left join daily_stats ds on ds.date = adm.date and ds.metric = adm.metric
)

select
  date,
  metric,
  sum(count) over (
    partition by metric
    order by date asc
    rows between unbounded preceding and current row
  ) as cumulative_count
from with_all_counts
order by date desc, metric asc
;

This is not correct, because it accumulates from the first date of the range specified, i.e. the 2025-04-25 and not from the first date of the whole set in the daily_stats whichever this might be. In other words, the count of a metric for the 2025-04-25 is NOT accumulated value.

If you run this, you will see that the value for base_basename on 2025-04-25 is 460 which is not the accumulated value for this metric up to that date. This, of course, makes the whole result set being wrong, as it accumulates values on top of the value counted on 2025-04-25.

Hence, the accumulated values need to be calculated on the whole daily_stats and not limited to the date range I want the graph/chart for.

In other words, I first need to accumulate and then limit by date range.

The correct SQL is the following:

with all_dates as (
  select generate_series(
     date '2025-04-25',
     date '2025-05-03',
     interval '1 day'
   )::date as date
),

all_metrics as (
  select distinct metric from daily_stats
),

all_dates_and_metrics as (
  select * from all_dates
  cross join all_metrics
),

cumulative_counts as (
  select
    date,
    metric,
    sum(count) over (
      partition by metric
      order by date asc
      rows between unbounded preceding and current row
    ) as cumulative_count
  from daily_stats
)

select
  adm.date,
  adm.metric,
  coalesce(cc_top.cumulative_count::int, 0) as count
  from
    all_dates_and_metrics adm
  left join lateral (
    select
      cumulative_count
    from cumulative_counts cc
    where cc.metric = adm.metric and cc.date <= adm.date
    order by
      cc.date desc limit 1
  ) cc_top on true
  
order by date desc, metric asc
;

As you can see above, the accumulation takes place on the whole daily_stats set, and then we join.

But we don’t just do left join because this would have given us 0 instead of accumulated values. We use left join and lateral subquery. Read about the lateral here.

The sub-query:

select
  cumulative_count
from cumulative_counts cc
where cc.metric = adm.metric and cc.date <= adm.date
order by cc.date desc limit 1

is using a reference to the date and metric columns of the preceding from item, i.e. of the from all_dates_and_metrics adm. This is possible thanks to lateral.

So, we left join the all_dates_and_metrics with the result of the sub-query. The result of the sub-query is always a single row (thanks to limit 1) and it is the latest row we have accumulation value for the metric and date at hand. Note that the join is unconditional thanks to the on true.

Here is the result set (first and last parts shown only) returned by this query:

It looks that it works! 🎉

gm! I am Panos Matsinopoulos, humble software reader, writer, classical music and V8 engine lover. I work for Talent Protocol, where we make builders get the recognition they deserve.

Introduction

In a Ruby on Rails project, creating a job to run in the background is as simple as

1. Defining the job class / execution code

2. Sending a job instance into a queue

3. Have a worker execute that instance

But all things that can go wrong when multiple users are trying to access the same shared state, a.k.a. concurrency issues, the same wrong things can happen when multiple processes / threads (let’s call them workers) are trying to execute a background task.

Also, there might be reasons when you want a background task to be executed only once. How do you prevent it from being enqueued many times?

Let’s see various scenarios of concurrent access to shared state and possible solutions.

This blog post is long, but still not exhaustive of the subject.

Tools Suggested As Solutions

Before I delve into details, here is the list of tools suggested as possible solutions to various problems:

• Synchronize access to parts of the code using a Mutex.

• Use ActiveRecord#increment! to update an integer value atomically.

• Optimistic Locking.

• Pessimistic Locking - ActiveRecord API.

• Postgres Advisory Locks.

• Enqueued Jobs table.

• Sidekiq::Shutdown Exception.

• Other Solutions like Sidekiq Enterprise, Sidekiq Unique Jobs, Sidekiq Iteration.

Context:

Ruby on Rails application

A Ruby on Rails application. Repository here.

Model

A simple data model, User , stored in users table, and UserBalance stored in table user_balances.

The User:

https://gist.github.com/pmatsinopoulos/7a0df745fde6fe8b2b367faaadbd26a5

The UserBalance:

https://gist.github.com/pmatsinopoulos/20dbde10b987886c0d5edfb43b8ace4a

I keep track of a user balance.

Background Task

I have a Sidekiq job in my Ruby on Rails project.

You can imagine any background task in your project.

https://gist.github.com/pmatsinopoulos/e10f9bc615ef733ee3a0400a97e4a696

Problem:

This job is not thread-and-process safe.

We can prove it by running this code in a rails console. Assuming that we have created a User who has balance 0, running the following code should increase it to 5.

https://gist.github.com/pmatsinopoulos/5d2f69cdf14f19f9931dc0fd070ea0e6

This is an instance of running this script:

be rails runner script/threads_increase_balance.rb 
12780 -> Calculating balance for user: panagiotis@matsinopoulos.gr
12860 -> Calculating balance for user: panagiotis@matsinopoulos.gr
12840 -> Calculating balance for user: panagiotis@matsinopoulos.gr
12800 -> Calculating balance for user: panagiotis@matsinopoulos.gr
12820 -> Calculating balance for user: panagiotis@matsinopoulos.gr
user_balance: 1

This is because all threads are accessing the balance concurrently. They are not serialized one behind the other.

Solutions?

There are, but not all of them are good.

Solution 1 - Mutex (I don’t like it)

I can make the job use a Mutex to access the shared balance resource.

The #perform() needs to take an extra argument, the mutex. And the script to fire the 5 jobs should be something like this:

https://gist.github.com/pmatsinopoulos/a19c20b63c996f7a66652e947d4d181a

Bad!

This is bad. I don’t like it. I would have to do the same for all my background jobs. Also, the most important thing is that it is not process-safe. I can’t share the same mutex across different processes. And the #perform taking as input the mutex is not an option. It’s not something that can be serialized to be stored into a queue, like Redis.

Solution 2 - Use #increment! (I don’t like it)

I can use the ActiveRecord#increment! method, which is atomic. That’s not bad initially.

https://gist.github.com/pmatsinopoulos/0eafae7b6c6eefbbbc48235002fdf81e

It works as expected with the script that spawns threads to update the balance in parallel.

And it works well if we put these jobs in the background. I use this script to do it: (I make sure that I have sidekiq running)

https://gist.github.com/pmatsinopoulos/e40e2265cbfa65f4b20c577310c604a6

However, I still don’t like that.

First reason? #increment!() does not call the ActiveRecord callbacks.

Second and most important reason? Doesn’t work with job business logic which is a little bit more elaborate than just an increment.

I generalize my job to do some complex logic to calculate the balance. Imagine that it reads other models, possibly updates others, and finally comes up with a new balance to set the balance with a new value, and it is not just an increment. It is a value which does not depend on the previous / existing balance.

So, I am on a new problem now.

More Complex Problem

This is another, more complex problem, job:

https://gist.github.com/pmatsinopoulos/4b42cba1efbbbf7e3b9b57471396cbbf

Here, the job sets the balance to a business-dependent value.

And because there are 5 jobs running in parallel, we don’t know which one has set the correct value, whatever correct means in the business that we run.

BTW, the script prints a log of the jobs and what balance each job calculated.

The business logic inside the CalculateUserBalanceJob might be long and complex, as I said, and I can’t set two (or more) instances of this job run at the same time.

I should NOT allow two (or more) instances of this job to run at the same time.

How can I solve this problem now?

Solutions

Solution - Optimistic Locking (Is not Enough - I don’t like It as The Single Solution)

I am going to assume that this job is going to run as a singleton, i.e at most one instance of it at any point in time. I am optimism. But, if for any reason, this is violated, then I need a way to know and make sure that I don’t suffer any serious implication.

The tool I use is called optimistic locking.

I will add the column :lock_version to the UserBalance model. This will make sure that only the first process that loads a UserBalance instance will succeed in updating that instance. All others will fail with ActiveRecord::StaleObjectError exception.

Without doing any modification to my Job code itself, if I try the script that fires 5 background jobs, 4 of them will fail.

And Sidekiq will keep on retrying them, until they succeed. And finally they will succeed since on every retry, one will be succeeding and the rest will be failing until all succeed.

So, this looks good. But

• I don’t like the fact that my jobs fail. Sidekiq will retry them unto to 25 times, but with exponential wait time in between retries.

• Lot’s of exceptions and noise.

Is there anything better I can do?

Solution - Optimistic Locking - Retry in the Job (I don’t like it either)

I can rescue the ActiveRecord::StaleObjectError and reload the object and retry again, maybe after waiting for some time.

So, I could do the job that Sidekiq does for me with Retries.

Nah! Very dirty code and raises questions like:

• How many retries?

• How long to wait between retries?

There has to be something better.

Solution - Pessimistic Locking (Lock on User), Keep Optimistic Locking (Good When Updating The Record At Hand)

I will keep the optimistic locking with the lock_version. I really like it in general. It keeps me from doing updates on objects that might be accessed by more than one process. In a multi-user Web application for example, the majority of objects would benefit from optimistic locking.

Hence, I am leaving optimistic locking in, but I also introduce pessimistic locking. This will lock all processes out except one. The other processes will wait behind a database enforced lock mechanism.

Ruby on rails allows me to do record-level locking and will work well for my case.

https://gist.github.com/pmatsinopoulos/e2fee3d4ce74be9614f152196668ec9e

That’s almost perfect. Isn’t it?

When we run the script, in the Sidekiq console, the ”****** <job-id> Calculating balance for user:…” does not appear for all 5 jobs at once. Only after a job finishes, then next message appears. And the jobs do not fail with the ActiveRecord::StaleObjectError. So, the jobs have been serialized behind the User.lock.find(user_id) statement, which does a select * from users where user_id = ? for update.

Good! That is a very good solution for locking processes out when updating a record instance, like a User with a specific id.

Not Good For: But what happens if I don’t want to update a record, but I want to create a record?

Problem: Create Record

Let’s see another problem. I have a job that creates a User. But I want to make sure that it runs, again, as singleton, unique job at a given point in time.

This is an example:

https://gist.github.com/pmatsinopoulos/a11a2ab5887629a1a600be54c1e75c36

If I just span 5 such jobs in Sidekiq, simulating a bad, but possible to happen, situation, 4 of them will fail, because the email is unique. They will retry and will retry until they find that another process has created the user.

Note: One might say, “Hey you! Why don’t you use User.find_or_create_by!(email: user_email)”. My answer to this comment is that I would do it if it were that simple. If I didn’t have any complex business logic in creating a User. The point is to find a solution to a more complex problem. A job/task that does a lot of things before committing/creating one or more things into the database. So, the example of creating a User by email might not be representative, but this is why I am not using find_or_create_by(). To simulate a more complex scenario.

How do I lock a process out when another one is being executed in this creation case? There is no way I can do select for update, because there are not any records to lock. I am not doing any update.

Solution - Pessimistic Locking Using Advisory Locks (I like this very much)

I am going to use Postgres Advisory Locks.

Side note to those who use MySQL or MS SQL Server: I asked an LLM to tell me whether MySQL and MS SQL Server have equivalent to Advisory Locks tools. Here is what it replied to me:

• MySQL: MySQL doesn't have a direct equivalent to Advisory Locks, but you can achieve similar functionality using GET_LOCK() and RELEASE_LOCK() functions. These allow you to create named locks that can be used across sessions, much like Advisory Locks in PostgreSQL.

• SQL Server: SQL Server doesn't have an exact match either, but you can use sp_getapplock and sp_releaseapplock to create application-level locks. These functions allow you to define locks that aren't tied to specific database objects, making them somewhat comparable to Advisory Locks.

Here is how I can use it in the CreateUserJob:

https://gist.github.com/pmatsinopoulos/6edb671bdabdc6e4daac8c5c75246b16

The behavior of this process is exactly the same like when I used the Rails pessimistic locking with User.lock.find(). Each process is queued behind the other.

I repeat here the lines that do the locking:

# LOCKING >
lock_namespace = ADVISORY_LOCK_NAMESPACES[:create_user_job]

lock_key = user_email

lock_comment = "CreateUserJob: #{provider_job_id} - #{user_email}"

lock_query = <<~SQL.squish
  SELECT pg_advisory_xact_lock(:lock_namespace, hashtext(:key)) /* #{lock_comment} */
SQL

lock_query_args = {
  lock_namespace: lock_namespace,
  key: lock_key
}

sql = ActiveRecord::Base.sanitize_sql_array([ lock_query, lock_query_args ])

ActiveRecord::Base.connection.execute(sql)
# < LOCKING

The juice of the story here is the SELECT pg_advisory_xact_lock(). I am not going to explain how it works. You can read the Postgres documentation.

What I am going to emphasize is what I did on top of it:

1. I use the namespace to tell that I am locking out CreateUserJob(s) for the key user_email. Note that the pg_advisory_xact_lock() takes as arguments integers. That’s why I have to convert the namespace :create_user_job and the email to an integer. The first I do it with an application level hash and the second using the hashtext() function in Postgres.

2. I also annotate the SQL statement with an SQL level lock.

Important Note: hashtext() is an internal function in Postgres. It is not publicly documented. This means that it might break in the future. Also, as a hashing function, it might fall into collisions. Now, I am looking at alternatives to generate a unique integer from a string. But, if you can provide an integer for the key, rather than a string which is converted with hashtext() to an integer, that would be ideal.

Suggestion: Factor out these lines of code into a reusable Ruby method. Like this:

module AdvisoryLock
  class << self
    def lock(lock_namespace:, lock_key:, lock_comment:)
      lock_query = <<~SQL.squish
        SELECT pg_advisory_xact_lock(:lock_namespace, hashtext(:key)) /* #{lock_comment} */
      SQL
      lock_query_args = {
        lock_namespace: lock_namespace,
        key: lock_key
      }
      sql = ActiveRecord::Base.sanitize_sql_array([lock_query, lock_query_args])
      ActiveRecord::Base.connection.execute(sql)
    end
  end
end

Then it will only be a matter of calling this method: AdvisoryLock.lock(…) at the beginning of your transaction.

Disadvantages?: It doesn’t have any. As long as your server is configured to accept many advisory locks, then you will not hit any problem. Except this solution is not very good if the job is very-long-running with numerous updates in db. See next problem.

Good! I even use it some times instead of the Ruby on Rails pessimistic locking. This is because Ruby on Rails pessimistic locking requires you to lock some rows in the database and prevents others from updating them, which might not be necessary.

Important Important Important! Exactly as it is mentioned on the Best Practices section in Sidekiq documentation,

Make your jobs idempotent and transactional

This is exactly what I did with the CreateUserJob.

It is idempotent, because I am checking whether a user already exists or not. If it exists, I don’t do anything. And it runs inside a transaction.

Hey! This solution is not good for the next problem. What if my job is a long-running job? Assuming that it needs to run for 20 mins or for 40 mins or for 2 hours? What if it is a batch job?

Problem: Long Running Batch Jobs

Important! I don’t like long running batch jobs. If I have a long-running batch job, I try to break into smaller chunks, chunks that can finish in about 1 or 2 minutes maximum.

But, let’s suppose that we have a job that is long-running and cannot be broken into smaller jobs.

Keeping the advisory lock and the transaction open for 1 hour or 2 hours is not good for many reasons.

Solution - Use Table To Log A Job Is Enqueued

Before I enqueue a job with something like LongRunningBatchJob.perform_later(batch_id), I check whether the job has already been enqueued.

So, I want something like this:

unless job_enqueued?(LongRunningBatchJob.name, batch_id)
  LongRunningBatchJob.perform_later(batch_id)
end

But this ☝️, needs to be atomic. I.e. in between job_enqueued?() check and the …perform_later() the job should not be enqueued.

Hence, we need to find a way to lock the access to the queue when checking and then enqueuing.

Can I use Sidekiq API to do that. No, I can’t. Sidekiq uses Redis to store background tasks. And I don’t see Sidekiq API having any such facility as LongRunningJob.perform_later_if_not_already_enqueued().

And it’s not only that we want to check whether it is already enqueued. It might be enqueued and have already started. In that case it is not literally enqueued in Sidekiq terms.

So, generally, I believe that Sidekiq is not very helpful here.

Enqueued Jobs In a Table

Hence, I am going to use a Postgres table to implement this feature.

enqueued_jobs table:

EnqueuedJob model:

https://gist.github.com/pmatsinopoulos/4d566b971a60a4cd67a1a432f3dc6afe

This is a model that has a polymorphic association to units of work, other models that are called queuables. For example to a User , or to a Batch.

Each enqueued job is for

• a queueable, such as a User, a Batch, an Order. What we usually give as argument to the job, #perform.

• a job_class_name, such as CreateUserJob, LongRunningBatchJob, e.t.c.

Enqueuing a Job - Don’t Do Like This

This should not be done with just LongRunningBatchJob.perform_later(batch_id). We should not call the #perform_later()on the Job class. If we do, we don’t take advantage of this enqueued_jobs table.

We should call the enqueue() method on the model at hand, on the queueable.

For example: batch.enqueue(…) or user.enqueue(…).

In order to support this interface, we introduce the Queuable module concern.

Enqueuing a Job - Do Like This - Queuable API.

I have developed the module Queueable:

https://gist.github.com/pmatsinopoulos/b84115e5966d3b64c7be28f0a666e024

The main method is the enqueue. If I include this module into the User or Batch or other model, then I can call it like this:

user = User.find_by(email: “panagiotis@matsinopoulos.gr”)
user.enqueue(job_class_name: LongRunningBatchJob.name)

This will use the Postgres advisory lock to create an entry in the enqueued_jobs only if there is none. If it creates the entry it also do a perform_later(). Otherwise, it doesn’t.

The Queueable API supports enqueuing jobs in the future with the scheduling_options too.

I will not go into the details of the implementation, but they should be fairly clear if you have read the whole blog post from the beginning.

What About The Job Itself?

But how do I implement a job to work together with the enqueued_jobs?

Here it is how:

https://gist.github.com/pmatsinopoulos/9908d5d43ba95994d72d8cb918765929

First, I include a concern called EnqueueableJob

Then, I wrap the actual work inside a call such as:

process_enqueued_job(queueable: batch) do
  ...
end

As simple as that. The interaction wit the enqueued_jobs happens inside the process_enqueued_job which is implemented in the EnqueueableJob concern as follows:

https://gist.github.com/pmatsinopoulos/3edd981a26b85a58fdd3e4f406cf933e

This is very simple.

I run the job using yield and then I delete it from the enqueued_jobs.

In summary, for the long running jobs:

1. I synchronize the access to the enqueued_jobs and the subsequent call to #perform_later().

2. The job itself, runs only if it still exists in the enqueued_jobs and removes itself after finishing.

Problem: Jobs Need To Restart From Where They Were Stopped

This is a very interesting problem. When I have a long running job that usually takes 1 or 2 hours, for example, to run, Sidekiq might kill it as part of a restart. For example, every time I do a deployment.

My job needs to be able to continue from the point it was killed. Not do the whole job from the beginning. Otherwise, in the case of frequent deployments, more frequent than the duration of a single-run of the job, this job will never finish.

Solution: Rescue Sidekiq::Shutdown, Save State, Raise

Inside the job, one can rescue the Sidekiq::Shutdown exception which signals the killing of a job by Sidekiq .

Then, inside the rescue block, job will save its state so that the next time it starts it will continue from the point it was stopped.

Then it will raise the exception because this is the proper thing to do as a Sidekiq job.

Solutions: Other

There are some gems that are trying to solve the above problems, but I have to be honest, I have not tried them out. Maybe in another post I will do:

• Unique Jobs by Sidekiq Enterprise

• Sidekiq Unique Jobs

• Sidekiq Iteration

• 
  

gm! I am Panos Matsinopoulos, humble software reader, writer, classical music and V8 engine lover. I work for Talent Protocol, where we make builders get the recognition they deserve.

• Ruby on Rails

• Postgres

• ActiveRecord

Problem:

When I inspect the SQL queries stats in my Postgres database, I want to know where each one is coming from, in terms of application code. As precisely as possible.

The pg_stat_activity , for example, has the column query which shows the SQL statement:

- START_REPLICATION SLOT "..." 539/C0000000 TIMELINE 1
- COMMIT 
- SELECT "github_accounts".* FROM "github_accounts" WHERE "github_accounts"."name" = '...' ORDER BY "github_accounts"."account_created_at" ASC LIMIT 1 
- SELECT value FROM rds_heartbeat2
- COMMIT
- COMMIT 
- COMMIT 
- COMMIT

Or AWS RDS Performance Insights:

But I can’t tell which point in the code is generating these queries.

Knowing the exact line of code that generates these queries is very useful when I want to troubleshoot problematic queries such as slow ones.

Solution:

I enable the ActiveRecord query_log_tags. In my config/application.rb file I have the following configuration:

module TalentProtocol
  class Application < Rails::Application
    ...
    config.active_record.query_log_tags_enabled = true
    config.active_record.query_log_tags |= [:namespaced_controller, :source_location]
    config.active_record.verbose_query_logs = true
  end
end

Bingo!

Then pg_stat_activity includes SQL comments that have the precise file and line of code (from my Ruby on Rails application) that generates this particular SQL statement.

- COMMIT /*action:index,application:TalentProtocol,namespaced_controller:api/v2/passport_credentials,source_location:app/jobs/api/log_request_job.rb:21:in `perform'*/
- SELECT "github_accounts".* FROM "github_accounts" WHERE "github_accounts"."name" = '...' ORDER BY "github_accounts"."account_created_at" ASC LIMIT 1 /*application:TalentProtocol,job:BuilderGrants::CalculateUserScoreJob,source_location:app/services/builder_grants/calculation_methods/base_org_v1.
rb:202:in `calculate_github_score'*/
- COMMIT /*action:show,application:TalentProtocol,namespaced_controller:api/v2/passports,source_location:app/jobs/api/log_request_job.rb:21:in `perform'*/
- COMMIT /*action:index,application:TalentProtocol,namespaced_controller:api/v2/passport_credentials,source_location:app/jobs/api/log_request_job.rb:21:in `perform'*/
- COMMIT /*action:refresh_token,application:TalentProtocol,namespaced_controller:api/v2/auth,source_location:app/jobs/api/log_request_job.rb:21:in `perform'*/

And in AWS RDS Performance Insights, I can see these comments too:

Extremely Useful!

gm!

I am Panos M., humble, back-end software reader and writer at Talent Protocol.

Image by G Poulsen from Pixabay

Introduction

At Talent Protocol we use Ruby on Rails for our back-end server.

Problem Definition

How do we know which HTTP requests take too long to be processed? With too long we mean more than 2 seconds.

Solution

AWS CloudWatch Logs

We collect all Rails server logs into AWS CloudWatch. Hence, when we inspect the logs we can see lines like the following:

Completed OK - More than 2 Seconds

So, it is quite easy to select the lines individually. For example, the following Logs Insights QL statement gives me the successful HTTP requests that took more than 2 seconds:

filter @message like /Completed 200 OK in \d+ms/
| parse @message /Completed 200 OK\sin\s(?<duration>\d+)ms/
| filter duration >= 2000
| display duration, @message
| sort duration desc
| limit 10000

Note: this also sorts these lines in descending order of the duration.

An example result set is this:

The problem is that I don’t know which requests, in terms of path and query parameters are these. This is because this information is coming on another line, the one that contains the pattern Started…, i.e. the 1st line that is logged for a particular request.

Working with OpenSearch SQL

I understand that I need a way to combine, join, two lines by a common piece of information.

I know that this sounds like SQL joins and the common piece of information is the request id.

This is where I switched to the CloudWatch Logs Insights OpenSearch SQL tool.

Let me work with this tool, step-by-step.

Get the Completed lines with duration >= 2 seconds

I will write this statement first:

SELECT 
  c.`@message` as message,
  REGEXP_EXTRACT(c.`@message`, 'INFO -- : \\[([0-9a-f-]+)\\] .+ Completed 200 OK', 1) as request_id,
  CAST(REGEXP_EXTRACT(c.`@message`, 'in ([0-9]+)ms', 1) as INT) as duration_in_ms
FROM `play-production-play-server` as c
WHERE c.`@message` like '%Completed 200 OK%' AND CAST(REGEXP_EXTRACT(c.`@message`, 'in ([0-9]+)ms', 1) as INT) >= 2000

As you can see, this is an SQL-like language that I am using. SELECT … FROM…WHERE….

Look how I use the REGEXP_EXTRACT to get the request_id from the @message. I use the same function to get the duration and cast it to integer so that I can compare it to 2000 (which is 2 seconds in milliseconds).

The CloudWatch group that I am querying is the play-production-play-server. This is the name of the CloudWatch group.

If I run this, I am getting results like this:

Get the Started lines

I will use another statement to get the lines that have the pattern Started. Here it is:

SELECT 
    s.`@message` as message,
    REGEXP_EXTRACT(s.`@message`, 'Started GET .+\\?(.+)" for', 1) as query,
    REGEXP_EXTRACT(s.`@message`, 'Started GET "([a-zA-Z0-9-/]+).+ for', 1) as path,
    REGEXP_EXTRACT(s.`@message`, 'INFO -- : \\[([0-9a-f-]+)\\] .+ Started GET', 1) as request_id,
    REGEXP_EXTRACT(s.`@message`, 'Started GET .+ for ([0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}) ', 1) as ip
FROM `play-production-play-server` as s
WHERE s.`@message` LIKE '%Started GET%'

Again, my tool is the REGEXP_EXTRACT. I can use it to extract the query, the path, the request_id and the ip of the request. Pretty useful information.

If I run this statement, I get results like this:

Join Two Statements

Now that I have the two statements, I can join them on the request_id. This will give me the duration of each request, along side other useful information like its path, its query and its ip.

Here is the statement:

SELECT 
    ss.path as path,
    URL_DECODE(ss.query) as query,
    ss.ip as client_ip,
    ss.request_id as request_id,
    cc.duration_in_ms as duration_in_ms
FROM (
    SELECT 
      s.`@message` as message,
      REGEXP_EXTRACT(s.`@message`, 'Started GET .+\\?(.+)" for', 1) as query,
      REGEXP_EXTRACT(s.`@message`, 'Started GET "([a-zA-Z0-9-/]+).+ for', 1) as path,
      REGEXP_EXTRACT(s.`@message`, 'INFO -- : \\[([0-9a-f-]+)\\] .+ Started GET', 1) as request_id,
      REGEXP_EXTRACT(s.`@message`, 'Started GET .+ for ([0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}) ', 1) as ip
    FROM `play-production-play-server` as s
    WHERE s.`@message` like '%Started GET%'
) as ss
    inner join (
        SELECT c.`@message` as message,
           REGEXP_EXTRACT(c.`@message`, 'INFO -- : \\[([0-9a-f-]+)\\] .+ Completed 200 OK', 1) as request_id,
           CAST(REGEXP_EXTRACT(c.`@message`, 'in ([0-9]+)ms', 1) as INT) as duration_in_ms
        FROM `play-production-play-server` as c
        WHERE c.`@message` like '%Completed 200 OK%' and CAST(REGEXP_EXTRACT(c.`@message`, 'in ([0-9]+)ms', 1) as INT) >= 2000
    ) as cc on cc.request_id = ss.request_id
ORDER BY duration_in_ms desc
LIMIT 100

Two SQL sub-queries, inner-joined on the request_id.

Look how I also use the function URL_DECODE() to decode the query in order to make it easier to read.

I now get results like these:

Bingo! I now know which path, query and client_ip had these long running HTTP requests.

gm!

I am Panos M., humble, back-end software reader and writer at Talent Protocol.

Yes, I liked it very much!

Yes, I recommend it to all founders of startups, especially those who target developers.

It will help you build numerous TODO lists. They constitute a good action plan for your startup to succeed.

So, it is very practical.

But I found it useful to other types of startups too.

Moreover, when I read such books I pick up lessons to apply to both my personal and professional life, because I like to read and abstract. And I like to generalize and find applications of what I read to other aspects of life too.

And this book had a lot of lessons for me to learn.

Also, it had a different perspective on things, perspective that I didn’t expect one could have.

No Spoiler: I will not reveal the secrets of the book. It’s not fair to the book author.

Affiliate link: This is where you can buy the book from.

Some Thinks You Will Find In the Book

About what Entrepreneurship and Startup is.

How developers are different. This means they have to be approached and treated differently, in a special way. The author is explaining in low-level details and with an action plan on how to do it. It tells you which is the wrong and which is the correct approach.

Be the trusted authority, the subject-matter expert. And show that you are.

What is a TAB, Technical Advisory Board and how it can help you focus the right problem searching for the right solution. The book will teach you how to set up a TAB. From recruiting to management.

Very nice chapters on how to build a story and how a story is boring without a villain. Book comes with a lot of example on good and bad stories.

The importance of evidence. Book is suggesting what kind of evidence you should build upon. What to avoid and what to pursue.

What should be your focus when you approach devs. Is it the problem, the solution, their pain?

Mistake, mistakes and mistakes. Books comes with lots of acts and actions you need to avoid. They are mistakes. But they are common.

And when the story is ready, then you have to build your presence. A lot of advises on how you should do it effectively and in way that will build trust. And it’s not only the Web site presence.

What about content creation? Should you write blog posts? Should you write books?

Take advantage of your users and customers. In a good way of course. Book will tell you how. What about the old-school social proof?

Book gives useful advises on how you should package your product. How to price it? When to price it?

Build feedback loop.

The importance of a well-designed (in terms of information and easy of read) Web Site. It deserves its own chapter. Book has it.

Developers need to adopt your product. But this deserves a pipeline.

Do you need a Marketing Team? Book will tell you how to set this up and when. Who to hire and in what order.

Special advises come from the book for those startups what are looking for funding.

gm!

I am Panos M., humble, backend software reader and writer at Talent Protocol.

Introduction

Recently, I followed the Advanced Solidity Bootcamp organized by Encode Club and delivered by Extropy.

Among the things we learnt, it was about gas optimization. The two many areas we touched in gas optimization were:

• Storage optimization

• Converting Solidity to Yul/Assembly.

Then I decided to apply this knowledge to one Talent Protocol production smart contract. The TalentCommunitySale.sol published in this repository here.

https://github.com/talentprotocol/contracts/blob/master/contracts/talent/TalentCommunitySale.sol

Note: If you want to jump into the code, click here.

Business Logic

A quick note on the business logic of this contract.

Talent Protocol published this contract to allow for interested builders to pre-purchase $TALENT tokens by depositing an amount of $USDC.

There were 4 different tiers:

• Tier 1: 100 USDC

• Tier 2: 250 USDC

• Tier 3: 500 USDC

• Tier 4: 1000 USDC

The picture below tries to depict the main business flow:

Assuming that Bonnie wants to deposit 100USDC, she would call TalentCommunitySale#buyTier1()

There are requirements for this transaction to succeed:

• The Sale should be active. Note that the Sale ran for a specific period of time in July 2024 (you can see the public contract here)

• Bonnie should have enough $USDC, i.e. at least 100 for tier 1.

• Bonnie should have approved TalentCommunitySale contract to spend as a spender 100USDC on her behalf.

• Also, for each tier, Talent Protocol, had specified a maximum number of sales. This was registered in the contract itself. After this number, tier was considered sold out.

• Finally, the same buyer was not allowed to purchase more than once, even if it were on different tiers.

All these business rules were coded as part of each buyTierX() function in the original contract code. Here is the related snippet for buyTier1():

require(saleActive, "TalentCommunitySale: Sale is not active");
require(
    paymentToken.allowance(msg.sender, address(this)) >= 100 * 10**tokenDecimals,
    "TalentCommunitySale: Insufficient allowance"
);
require(tier1Bought < TIER1_MAX_BUYS, "TalentCommunitySale: Tier 1 sold out");
require(!listOfBuyers[msg.sender], "TalentCommunitySale: Address already bought");
require(paymentToken.transferFrom(msg.sender, receivingWallet, 100 * 10**tokenDecimals), "Transfer failed");

Test Coverage

Before trying to optimize a Smart Contract code, and as a good practice in refactoring any code in software engineering, one should have very good test coverage.

The original smart contract had some coverage with tests written in Hardhat and Ethers.

I decided to go with foundry, mainly because that was the tool we were taught in the bootcamp.

Test Source Code

The test source code can be found here.

Test Main Takeaways

Dependency on an ERC20 Token

When your contract depends on an ERC20 Token, you can use a Mock smart contract while testing. This is the purpose of the ERC20Mock.sol (and the ERC20MockBad.sol which is specially used for testing ReentrancyGuard)

Test 3rd-Party Inherited Contracts

When a contract derives from 3rd-Party inhered contracts, like OpenZeppelin Ownable and ReentrancyGuard, you have to write tests for all the public features/functions these contracts make your contract expose. This is because inheriting from these 3rd-party contracts is an implementation detail, which you might decide to change in the future, but still want to keep the functionality.

This is why you will see tests that test the Ownership features inherited from Ownable and a couple of tests testing ReentrancyGuard.

Testing ReentrancyGuard

This deserves its own paragraph in this post, because I had to use a special ERC20MockBad.sol contract to simulate the behavior of a bad contract whose function is trying to call back to the calling function on the calling contract.

I implemented the ERC20MockBad.sol contract to call back to the buyTier1() function that is calling the bad’s contract transferFrom() function:

function transferFrom(address, address, uint256) public override returns (bool) {
    msg.sender.functionCall(abi.encodeWithSignature("buyTier1()"));

    return true;
}

Testing the re-entrancy had one glitch. The following code should have worked but it didn’t:

vm.prank(caller);
vm.expectRevert(abi.encodeWithSelector(TalentCommunitySale.ReentrancyGuardReentrantCall.selector));
talentCommunitySaleBad.buyTier1();

The error that it threw was:

In order to sort this out, I had to write:

vm.prank(caller);
try talentCommunitySaleBad.buyTier1() {}
catch (bytes memory err) {
    assertEq(bytes4(err), TalentCommunitySale.ReentrancyGuardReentrantCall.selector);
}

Here, I catch the error and I check that the first 4 bytes of the error caught are equal to the selector for the custom error TalentCommunitySale.ReentrancyGuardReentrantCall.

Test Coverage

Foundry has a very good test coverage report:

One can run:

$ forge coverage

and Foundry will run the tests and print the report. A report like this:

Printing Messages to Console

Another very useful utility that helps debugging inside your tests is the console utility, which you can import from forge-std/Test.sol.

USDC Dependency

The USDCTMock is an ERC-20 compatible contract that I used in my tests, instead of the real USDC contract.

Main Foundry Cheat Codes

The main Foundry cheat codes that I used:

• vm.prank() to set the msg.sender of the following transaction.

• vm.expectRevert() to set revert expectations.

• vm.expectEmit() to set emit event expectations.

Storage Layout

Initially, I did the exercise of reducing the number of slots the contract occupied.

I can get the storage layout and information about how state variables use the storage with the following Foundry command:

$ forge inspect --pretty TalentCommunitySale storageLayout

Initially, before I do any change, the storage layout was:

As you can see, it occupied 8 slots.

At the end of the exercise, the storage layout became this:

As you can see, the optimized storage, uses 3 less slots. Instead of 8, it uses 5.

Code optimizations

With regards to code optimization, as I said at the beginning, I converted a lot of Solidity code to Yul/Assembly code.

The source code can be found here. But the key takeaways I would like to mention in this blog post are:

Don’t use Magic Numbers - Use Constants

Don’t just throw magic numbers like 100 in the code. Use constants instead. Also, don’t just use state variables without making them constant, because you are wasting storage space.

This is a good example:

uint32 public constant TIER1_MAX_BUYS = 100;

Just keep in mind though, that, unfortunately, constants you declare at the Solidity level, can’t be used at the assembly level. This means that you might need to assign the constant to a local variable before being able to access it inside an assembly block.

Immutable

If something takes a value inside the constructor and never changes again, declare it as immutable. It saves storage space.

Example:

uint256 public immutable TIER1_AMOUNT;

Slot Numbers, Offsets and Sizes as Private Constants

When I wrote Yul/Assembly I had to reference slot numbers, offsets and sizes of state variables. If you don’t use private constants to refer to them it will be difficult to update your code should you decide to further optimize the storage. Also, it makes it easier to read when you use constants. It is another case to avoid magic numbers.

Example:

uint8 private constant STORAGE_TIER1_BOUGHT_SLOT = 3;
uint8 private constant STORAGE_TIER1_BOUGHT_OFFSET = 20;

Removed Dependencies to OpenZeppelin Ownable and ReentrancyGuard

Implementation of ownership and reentrancy guard has been moved inside the contract code. This allowed us to write the functions inherited in our own assembly implementation alongside with other optimizations.

Removed Dependency to Math

This library was not necessary. I removed it.

Removed require Calls

I removed all the require calls. I replaced them with revert calls and custom errors.

Non-Slot Aligned Variable Update Is Hard

Reducing the slots in the storage has a disadvantage when you write assembly. It requires a lot of careful bit operations to change the value of a variable which does not have its own dedicated slot.

Take for example the saleActive bool which is in the middle of slot 5. It occupies 1 byte at offset 20.

If I want to set it to true, I have to write this assembly code:

assembly {
    let slotSaleActiveValue := sload(STORAGE_SALE_ACTIVE_SLOT)
    let offsetBits := mul(STORAGE_SALE_ACTIVE_OFFSET, 8)
    let zeroMask := not(shl(offsetBits, 0xFF))
    let setMask := shl(offsetBits, 0x01)
    sstore(STORAGE_SALE_ACTIVE_SLOT, or(and(slotSaleActiveValue, zeroMask), setMask))
}

if saleActive occupied an entire slot, then it would have only be a case of calling just the sstore() statement. But with the saleActive being in between other variable values in slot 5, I need to make sure that I only update the specific byte at offset 20 and leave the rest as it was.

Revert With Custom Code in Assembly

When reverting with a custom code in assembly here is how to do it:

1. save the free memory pointer to use it as the memory point to store revert information.

let freeMemoryPointer := mload(0x40)
let initialFreeMemoryPointer := freeMemoryPointer

2. Assuming that I want to revert with the custom error OwnableUnauthorizedAccount(address) , I have to store the error identifier:

mstore(freeMemoryPointer, shl(mul(28, 8), 0x118cdaa7))
freeMemoryPointer := add(freeMemoryPointer, 4)

Note: How do I find the identifier? Various different methods. You can use the cast utility that is coming with foundry:

$ cast keccak 'OwnableUnauthorizedAccount(address)'
0x118cdaa7a341953d1887a2245fd6665d741c67c8c50581daa59e1d03373fa188

… and take the first 4 bytes (0x118cdaa7).

The identifier, 0x118cdaa7 needs to be left aligned before being stored in the memory at the free memory pointer. That’s why I do shl(mul(28, 8), 0x118cdaa7)). I shift left by 28 bits (which is the result of 32, the total size of bits of the memory slot, minus 4, which is the side of the identifier).

Which makes the memory position looking something like this:

Note: After I set a value to the memory, I then update the free memory pointer value to point to the next position available for writing to memory. That’s the second command you see in the snippet above: freeMemoryPointer := add(freeMemoryPointer, 4). I move the free memory pointer by 4 which is the number of bytes occupied by the identifier.

3. Then I write the run-time argument values to the custom error. In our case, I only have one argument, the msg.sender, an argument of type address.

In assembly the msg.sender is accessed with the Yul function caller().

The address type in Ethereum is 20 bytes long, but, in Yul/assembly it is always right aligned in 32 bytes with leading 0s. This is because the only available type in Yul/assembly is u256 i.e. a 32 bytes long unsigned integer.

Hence, I am not going to write 20 bytes into the memory. I am going to write 32 bytes.

mstore(freeMemoryPointer, caller())
freeMemoryPointer := add(freeMemoryPointer, 32)

This means that both identifier and caller address will be stored in memory like this:

Then, as part of a good practice, I save the new free memory pointer value:

mstore(0x40, freeMemoryPointer)

4. Reverting

Finally, reverting is a matter of calling the revert function with the memory pointer holding the revert data, i.e. the identifier and the caller address:

revert(initialFreeMemoryPointer, 36)

Reverting with custom error that takes more than one run-time argument values follows the same technique and rules.

Emitting Events

Emitting events in assembly, is a matter of using the correct logX() function.

I will show you an example for the event OwnershipTransferred(address,address).

This event is actually declared as:

event OwnershipTransferred(
    address indexed previousOwner,
    address indexed newOwner
);

This is a 3 topics event, because the signature of the event is always the first topic

• OwnershipTransferred(address,address)

• previousOwner

• newOwner

with no extra data.

Hence, I am using the log3 function.

log3(
  0x00,
  0x00,
  0x8be0079c531659141344cd1fd0a4f28419497f9722a3daafe3b4186f6b6457e0, 
  oldOwner,
  newOwner
)

How do I get the signature of the event? Again I can use different methods. Like cast or forge. Let’s use forge here:

$ forge inspect --pretty TalentCommunitySale events
{
  "OwnershipTransferred(address,address)": "0x8be0079c531659141344cd1fd0a4f28419497f9722a3daafe3b4186f6b6457e0",
  ...
}

Accessing Dynamic Variables - mapping

mapping type variables do not store their payload at the position in storage at which they are declared. They occupy 1 slot in storage and they set the value of it to be 0x0.

The actual payload is stored in storage at positions which are dynamically determined.

I will explain this with the example of the variable:

mapping(address => bool) public listOfBuyers;  

The storage layout shows that it is stored on slot number 2:

But if I deploy the contract and check with cast storage <contract-address> 2 I will see the value of this slot being 0x0:

But, if I have stored the true for the address 0x324e9E13dd19528D0F390201923d17c4B7E94462, then this is stored at the position:

keccak256(0x324e9E13dd19528D0F390201923d17c4B7E94462,2)

The keccak256() assembly function returns a 32 byte number which is the slot number in the storage for the given address (0x324…) and slot number 2. To be precise, the arguments to the Yul/assembly function are pointing to the memory. Hence, I first have to store the address and slot number into the memory.

Here is the assembly code that accesses a mapping using this method. I am checking whether the caller() is in the listOfBuyers by accessing the listOfBuyers[caller()].

let buyerAddress := caller()
let freeMemoryPointer := mload(0x40)
let initialFreeMemoryPointer := freeMemoryPointer

mstore(freeMemoryPointer, buyerAddress) // 32 bytes
freeMemoryPointer := add(freeMemoryPointer, 32)

mstore(freeMemoryPointer, STORAGE_LIST_OF_BUYERS_SLOT)
freeMemoryPointer := add(freeMemoryPointer, 32)
mstore(0x40, freeMemoryPointer)

let listOfBuyersSlotForBuyer := keccak256(initialFreeMemoryPointer, 64)

sload(listOfBuyersSlotForBuyer)

1. I save the address to memory mstore(freeMemoryPointer, buyerAddress)

2. I save the slot number mstore(freeMemoryPointer, STORAGE_LIST_OF_BUYERS_SLOT)

3. I calculate the keccak256() 32 number by sending the 64 bytes of memory to the keccak256() function.

Conclusion

The whole exercise was really useful to me to learn Yul/Assembly and how EVM works. I understand that I have a lot more to learn. I will keep on studying and working on this.

What I Do?

I am a happy software engineer, working for Talent Protocol, but also for personal projects like a Web-2-SMS platform. I also like to play tennis and the piano. I read a lot of books. I have a lovely family of humans and animals.

This is the idea behind Arigatai, a project that dreams to save the gratitude statements in history.

When I First Had The Idea for a #thankyou Slack Channel

Back when I worked at Lavanda, I introduced the #thankyou Slack channel.

Why?

I wanted to express my gratitude to another colleague, in public.

Why?

I always sent my gratitude with a thank you message to the person having offered his time, knowledge and help, to make my life at work better...but I didn't want this to stay only between us.

• That person made me happy, and I wanted to show to the rest of the company that I was happy.

• I also wanted to show to the rest of the company my happiness of having colleagues who could make others happy.

• I wanted to show to the rest of the company who is the person that can make others happy.

• I believe that my colleagues who can make others happy, they deserve to be, at least, recognized in public.

And, I wanted to make public, the reason I am thanking my colleague. Not many details. Brief short phrase to let anyone actually be in my shoes for the reason I am happy for. To give a little more context and value to this open gratitude.

Then, everyone went mad about it. They loved it.

And they even created a thank you counter. The tacos.

And they put some limits. Every day you can thank up to 5 times. You can give your 5 tacos to the same person, if you want, or give each one of the 5 to different. Doesn't matter, but you can only spend up to 5 tacos each day.

And they even have a leader board:

So, employees, earned tacos on a daily basis. And they gave tacos to other colleagues too.

Other Companies

Is this a common practice in companies. I didn't know. Until I saw this:

https://handbook.gitlab.com/handbook/values/#say-thanks

Ohhh! They have a #thanks channel. Similar to the one I introduced at Lavanda! I was so happy I saw that.

And then I also found this:

https://heytaco.com/

A company that offers a peer-to-peer recognition platform. Great!

So, it make sense!

The Problem

All tacos were deleted 😭.

I left Lavanda, my tacos have gone away. I couldn't take my tacos with me 😢.

I guess you immediately get the point.

The tacos, they presented a value to me, which

• I lose when I leave the company,

• I lose when the centralized entity that keeps track of them stops to exist.

So what one might say!

I am saying that gratitude that I receive throughout my professional life should belong to me.

It is a value that I want to keep on having, forever, registered in history.

And I want to accumulate such value even, going from one company to the next.

It is part of my reputation as a professional.

The Solution

Join Arigatai.

ERC-20 Token

Arigatai, with symbol ARIG, stores the gratitude value on Ethereum, as an ERC-20 token.

ARIG, is my professional reputation points on the gratitude dimension.

Minting

When Arigatai Smart Contract is deployed it doesn't have any initial circulation. I.e. the circulating supply is 0.

Minting starts when clients call the external functions arigataiDeliver() and arigatoAccept()

This is the signature of the function:

function arigatoDeliver(
  address _recipient, // The EOA that will receive the `ARIG`
  uint256 _amount, // The amount of `ARIG` the _recipient will get
  bytes[640] memory _reason, // The reason sender is sending the _recipient this amount of ARIG
) external;

There are some rules for this call to succeed.

1. The caller should be an EOA, not a contract.

2. Recipient should be an EOA, not a contract.

3. The caller should not exceed the maximum daily delivery amount which is 5.

This function does not actually create ARIG. It is the arigatoAccept() that does.

function arigatoAccept(
  address _sender, // The EOA that sent the arigatoRequest()
  uint256 _amount, // The amount of $ARIG that will be accepted
) external;

1. The caller needs to have a pending ARIG delivery from the _sender.

2. The pending delivery should be for the _amount.

3. The delivery should have been created within the last 24 hours from acceptance.

So, it is the arigatoAccept() that does the actual minting of the ARIG token.

When arigatoAccept() succeeds, the caller gets the amount of ARIG and there is a record created (Arigato record) in the state of the contract to record the details of the reward.

The arigatoAccept() will internally call a _mint() function like:

_mint(msg.sender, _amount)

and will also take care to create the Arigato record.

Properties of the ERC-20 Token

• name: ARIGATAI

• symbol:ARIG

• decimals: 0. The value ARIG token is not divisible.

How can an EOA Find The List of its Arigato Records?

There is a function that allows pagination access to the Arigato records of an address:

function getArigatos(address receiver, uint256 start, uint256 count) public view returns (Arigato[] memory);

Also, there is a function that returns the total number of Arigato records of an address:

function getArigatosCount(address receiver) public view returns uint256;

How can we have a Leader Board?

Contract keeps track of the recipients and their Arigato records. Building a Leader Board, is outside of the scope of the Smart Contract, but we have other plans for it.