Check out the Spacelift Engineering blog to discover internal technical architecture posts written by Spacelift's very own engineers. 

Engineering | Spacelift Blog

Author title

Register for the Product Momentum Webinar Aug 6 →

Learn about Spacelift's latest features and capabilities 🚀🚀

Title category

Get all the latest content on Infrastructure as Code, DevOps, Spacelift features & news directly to your inbox!

Level up your IaC knowledge. Stay up to date with educational devops guides & news in infrastructure as code, cloud & more!

Managing Infrastructure at Scale | Spacelift Blog

Transform your IaC management to scale
securely, efficiently, and productively
into the future.

Blog

What Are Terraform Modules and How to Use Them: Tutorial

Terraform Functions, Expressions, Loops (Examples)

Managing Terraform State - Best Practices & Examples

Terraform For Loop - Expression Overview with Examples

What is Terraform? What is it Used For, How it Works

44 Ansible Best Practices to Follow [Tips & Tricks]

How to Stop and Remove Docker Containers

How to Create a CI/CD Pipeline with Docker [Tutorial]

CI/CD Pipeline : Everything You Need To Know

Kubernetes CI/CD Pipelines - 7 Best Practices and Tools

How to Deploy Infrastructure in CI/CD Using Terraform (Pipeline)

Terraform with Azure DevOps CI/CD Pipelines - Tutorial

Terragrunt Tutorial - Getting Started & Examples

Pulumi Pricing – Editions Overview 2024

73 Most Useful DevOps Tools to Use in 2024

Top 10 Terraform Enterprise Alternatives for 2024

Terraform Cloud/Enterprise Pricing - Tiers Overview 2024

16 Most Useful Infrastructure as Code (IaC) Tools for 2024

Terraform Cheat Sheet - 23 Terraform CLI Commands & Examples

ebook-global-banner

ebook global banner

Product overview

How it works

Integrations

Product

Solutions

cicd

CICD

cicd-2

Infrastructure governance

This is the short description, two lines is maximum

team

Team

team-2

Collaboration

speed-2

Speed

speed-3

Developer Velocity

productivity

Productivity

productivity-2

Scaling IaC

Use cases

Financial

Technology

Industries

Request a meeting

Enjoy an action-packed day at AWS Summit London 2024, where the latest in cloud innovation comes to life.

Join us at AWS Summit  in London!

Docs

Pricing

Resources

blog

blog-2

partners

Partners

partners-2

events

Events

events-2

cheatsheets

Cheatsheets

cheatsheets-3

Ebooks & Cheatsheets

users

Users

users-2

Case studies

chat

Chat

chat-2

User reviews

388-terraform-yamlencode

terraform yamlencode

Using Terraform YAML Functions: Yamldecode & Yamlencode

About Us

Contact Us

About

cheatsheets-2

Test

See more

Test webinar name

applepodcasts

spotify

missionctrl

Welcome to missionCTRL, a show dedicated to exploring the frontiers of modern DevOps. Listen (or watch) as we sit down with the practitioners who are pushing the boundaries of development

Engineering

Adam Connelly

Way back in December 2021, I wrote a post explaining how to <a href="https://spacelift.io/blog/ci-cd-workers-in-kubernetes-dind" target="_blank" rel="noopener">run Spacelift workers in Kubernetes using Docker-in-Docker.</a> We took that initial approach because it was a relatively simple way to get something working in Kubernetes, and it was a very close match to how we run workers in non-containerized environments like EC2. You could almost think of it like performing a lift and shift.
Although the Docker-in-Docker approach worked, it had some problems:
<ul>
<li style="font-weight: 400;" aria-level="1">The Docker-in-Docker daemon needs to run as a <a href="https://kubernetes.io/docs/concepts/security/pod-security-standards/#privileged" target="_blank" rel="nofollow noopener">privileged container</a>, which is a potential security issue.</li>
<li style="font-weight: 400;" aria-level="1">Each worker in the pool has an always running Pod, which uses up resources in the cluster.</li>
<li style="font-weight: 400;" aria-level="1">Scaling down is not safe because we can’t ensure that workers are idle before Kubernetes removes their Pods.</li>
<li style="font-weight: 400;" aria-level="1">It’s not easy to make full use of Kubernetes functionality; for example, using the cluster credentials to pull a private runner image.</li>
</ul>
We were aware at the time that the Docker-in-Docker approach was only ever meant to be a temporary measure, and that eventually we would need to create a more Kubernetes-native approach that solved these problems. We also realized that by integrating more fully with Kubernetes, we could do some really exciting things like provide the ability to view status information about Spacelift workers and runs from within the Kubernetes cluster itself (more on that later!).

Before going any further, it’s worth giving a quick overview of the architecture of Spacelift workers. The following diagram shows the basic architecture of a Spacelift launcher:

basic-architecture-of-a-spacelift-launcher

basic architecture of a Spacelift launcher

As you can see, there’s a “Worker Instance” that runs a “Launcher” process. This process is responsible for communicating with Spacelift (via MQTT and HTTPS, in case you’re interested), and for creating Docker containers when scheduling messages are received from the Spacelift Mothership.
The Docker-in-Docker approach basically mimics this by running Docker as a side-car container (the DinD containers in the following diagram):

dind-containers-diagram

DinD containers diagram

As I mentioned at the start of this post, in the Docker-in-Docker approach used above, a separate Pod is created for each Spacelift Worker in the pool, meaning that if you have a pool size of ten, you’ll have ten Pods sitting in your cluster using up resources, even when all of the Workers are idle.

The approach we’ve taken to solving these problems is to create a <a href="https://kubernetes.io/docs/concepts/extend-kubernetes/operator/" target="_blank" rel="nofollow noopener">Kubernetes operator</a>. The operator pattern allows you to extend Kubernetes by introducing new types of resources into the Kubernetes API. You then build a controller that knows how to reconcile those custom resources, providing additional functionality.
In our case, we’ve defined two new types of resource:
<ul>
<li style="font-weight: 400;" aria-level="1">A WorkerPool resource that represents a set of Spacelift workers.</li>
<li style="font-weight: 400;" aria-level="1">A Worker resource, representing an individual worker.</li>
</ul>
When using the operator, you only define WorkerPool resources. The operator handles creating the Workers based on your WorkerPool definition and also handles all of the communication with the Spacelift Mothership; for example, registering workers and handling scheduling messages.
The following diagram provides a high-level overview of the WorkerPool operator:

high-level-overview-of-the-workerpool-operator

high-level overview of the WorkerPool operator

It’s worth noting here that the WorkerPool and Worker resources are only entries in the Kubernetes API. They don’t consume any compute resources themselves, and no Pods exist for workers, except when a run is in progress. The Run Pods in the diagram above are created as a result of Spacelift runs being assigned to a worker in the pool. They only exist for the lifetime of the run, and then they are removed.
The “Spacelift Controller” shown in the diagram is the part responsible for managing the workers and communicating with Spacelift. It runs as one or more Pods in your cluster and reacts to Kubernetes API events as well as messages from Spacelift. When a new run is scheduled onto a worker that the controller manages, it uses the Kubernetes API to create a Pod for that run, based on the configuration information contained in the WorkerPool resource for the worker.
This design means that we no longer need Docker-in-Docker (normal Kubernetes Pods are created for each Spacelift run), we avoid Pods running in the cluster for workers when no runs are in progress, and we can handle scaling gracefully, making sure we don’t remove a worker while a run is in progress. Also, by making virtually all of the Pod configuration options available via the WorkerPool resource, we allow customers to take advantage of standard Kubernetes features like service accounts, node affinity, custom volumes, etc.

<ul>
<li><a href="https://spacelift.io/blog/how-to-maintain-operations-around-kubernetes-cluster" target="_blank" rel="noopener">How to Maintain Operations Around Kubernetes Cluster</a></li>
<li><a href="https://spacelift.io/blog/why-devops-engineers-recommend-spacelift" target="_blank" rel="noopener">Why DevOps Engineers Love and Recommend Spacelift</a></li>
<li><a href="https://spacelift.io/blog/infrastructure-as-code-with-generic-ci-cd">Why Generic CI/CD Tools Will Not Deliver Successful IaC</a></li>
</ul>

<a href="https://docs.spacelift.io/concepts/worker-pools#installation" target="_blank" rel="noopener">Getting started</a> with the operator is simple. We provide Kubernetes manifests that can be used with <code>kubectl apply</code>, as well as a <a href="https://github.com/spacelift-io/spacelift-helm-charts/tree/main/spacelift-workerpool-controller" target="_blank" rel="nofollow noopener">Helm chart</a> that can be used to install the Custom Resource Definitions (CRDs) and controller Deployment. For example, here’s how to install the operator using kubectl:

Once the operator is installed, you need to create a <a href="https://spacelift.io/blog/kubernetes-secrets" target="_blank" rel="noopener">Kubernetes Secret</a> to store your worker pool credentials. These credentials can be created during the <a href="https://docs.spacelift.io/concepts/worker-pools#setting-up">standard worker pool setup process</a> on Spacelift.

And finally create your pool:

For the full details of how to install the operator and configure your WorkerPools, please see the documentation here: <a href="https://docs.spacelift.io/concepts/worker-pools#installation" target="_blank" rel="noopener">https://docs.spacelift.io/concepts/worker-pools#installation</a>.

<h3>WorkerPools and workers</h3>
If we take a look at the cluster (you can use <code>kubectl get workerpools</code>), we can see our new pool:

kubectl-get-workerpools

kubectl get workerpools

As well as the name of the pool, you can see a desired pool size, and an actual pool size. The desired pool size corresponds to the <code>poolSize</code> field in your <code>WorkerPool</code> definition, and defines how many workers you want to have in your pool. The actual pool size indicates the number of <code>Worker</code> resources that actually exist, connected to the pool. The operator is responsible for creating these workers and adding or removing workers as the <code>poolSize</code> is updated.
If we take a look at the workers in the pool (<code>kubectl get workers</code>), we can see that there are indeed two workers:

workers-in-the-pool

workers in the pool

The name of each worker is made up of two items: the name of the <code>WorkerPool</code> resource and a unique ID for that worker. Registered indicates whether that worker has been registered with the Spacelift backend, and the status can be either “IDLE” or “BUSY”, indicating whether the worker is ready to process a run, or whether a run is already in progress on that worker.
Describing the worker shows a number of labels and annotations containing information about that worker and the pool it’s connected to:

describing-the-worker

Describing the worker

In the screenshot above, the workers.spacelift.io/ulid label contains the unique ID for that worker, and the workers.spacelift.io/workerpool label contains the unique ID for the pool it belongs to. If we take a look in Spacelift, we can see both workers connected to our pool:

local-kind-pool

local kind pool

You can also see that we automatically include some information about each worker in the “Metadata tags” column. In this case, we can see a <code>k8s-native</code> tag indicating that this worker is from a native Kubernetes pool, a <code>k8s-namespace</code> tag telling us the Kubernetes namespace that the worker is in, as well as the <code>k8s-name</code> tag that contains the name of the Worker resource in Kubernetes.
<h3>Run scheduling</h3>
When a run is assigned to a worker in Spacelift, the Spacelift backend sends a message to that worker via MQTT with all the information required to process that run. In the case of the Kubernetes operator, the controller component receives that scheduling message, creates a new <code>Pod</code> via the Kubernetes API to process the run, and then marks the associated <code>Worker</code> resource in Kubernetes as busy.
When this happens, if you query for the workers in Kubernetes, you will see the updated status, as well as the ID of the run assigned to the worker:

query-for-the-workers-in-kubernetes

query for the workers in Kubernetes

If you take a look at your Pods, you will also see a Pod that’s been created for that run:

look-at-your-pods

look at your Pods

Once the run completes successfully, the Pod is deleted, and the worker status goes back to IDLE again:

worker-idle-status

worker idle status

<h3>Run Pods</h3>
The definition of the <code>Pod</code> created for each run is based on information in the <code>WorkerPool</code> resource. If you create the most basic <code>WorkerPool</code> definition possible, the Pods created will look something like this:

The <code>Pod</code> spec above has been simplified a bit to reduce some of the noise, but at its core are three separate containers used to process the Spacelift run:
<ul>
<li style="font-weight: 400;" aria-level="1">An init container called “init” that performs one-off tasks like downloading the source code for the run, performing role assumption, etc.</li>
<li style="font-weight: 400;" aria-level="1">The “worker” container that actually executes the run.</li>
<li style="font-weight: 400;" aria-level="1">A container called “launcher-grpc” that provides certain services to the worker container that would normally be provided by the launcher process.</li>
</ul>
The <code>init</code> and <code>launcher-grpc</code> containers both use the <code>public.ecr.aws/spacelift/launcher</code> container image. The version of the image to use is sent from the Spacelift backend as part of the run scheduling message, ensuring that the image used is compatible with the current version of the Spacelift backend.
The <code>worker</code> container uses the <a href="https://docs.spacelift.io/concepts/stack/stack-settings.html#runner-image" target="_blank" rel="noopener">runner image</a> defined in your Spacelift stack, falling back to a default image if none is specified (for example, <code>public.ecr.aws/spacelift/runner-terraform:latest</code> for Terraform stacks). The image to use is sent through from the Spacelift backend based on your stack settings.
<h3>Run Pod customization</h3>
When designing the operator, we wanted to keep things as flexible as possible and allow users to make use of as much standard Kubernetes functionality as possible. Because of that, we’ve taken the approach of exposing any Pod configuration options as long as there isn’t a good technical reason to avoid doing so.
For example, say you wanted to use a custom service account, you could define a WorkerPool like this:

The full set of configuration options can be found in our documentation: <a href="https://docs.spacelift.io/concepts/worker-pools#configuration" target="_blank" rel="noopener">https://docs.spacelift.io/concepts/worker-pools#configuration</a>.
<h3>Scaling WorkerPools</h3>
The size of your <code>WorkerPool</code> defines how many <code>Worker</code> resources are created. These <code>workers</code> are effectively virtual, and tell the Spacelift backend that there are resources available to schedule runs. This means the number of workers in your pool defines the maximum concurrent Spacelift runs you can execute at any given time (since each worker can only be assigned a single run at any point in time).
If you need to scale your pool out to allow you to process more concurrent runs, or if you have more workers than you currently need and want to scale down, you have two options: You can edit the <code>poolSize</code> in your <code>WorkerPool</code> resource, or you can use the <code>kubectl scale</code> command. There’s no difference between the two approaches, so feel free to use whichever option makes sense for your use case, but here’s an example of the <code>kubectl scale</code> command:

One of the major advantages of the operator over the previous Docker-in-Docker approach is when scaling down. Because the Docker-in-Docker approach used <code>Deployments</code> or <code>StatefulSets</code>, there wasn’t a great way to hook into the scale-down process to ensure that a <code>Pod</code> processing a run was not deleted while a run was in progress.
The operator solves this problem. When you attempt to scale down a <code>WorkerPool</code>, the operator only scales down idle workers. If there are currently no idle workers available to scale down, the operator waits until the run for a worker completes, and only then removes that worker from the pool.

Hopefully, this post has been interesting and has provided some insight into the reasons we created the Spacelift worker Kubernetes operator, as well as shedding some light on some of the internals of how the operator works.
I’m really proud of what we’ve managed to achieve so far, and I&#8217;m excited to see customers using the operator to run their pools. If you decide to use the operator, please feel free to reach out to tell me and the rest of the team about your experience and to give us any feedback we can use to improve. 
I want to give a huge thanks to everyone at Spacelift who’s contributed to this project. It’s been a multi-team effort, getting tons of help and input from colleagues from our customer success team, solutions engineering, data, and product teams, amongst others. I also want to thank our amazing customers who gave us feedback and participated in early testing of the operator.
And in particular, I want to give a huge shout out to my partner in crime on this project, Elie. It’s been amazing working with such a talented engineer who has such a great mix of pragmatism and attention to detail. There’s no way this project would have been as successful without him!

Spacelift is an alternative to using homegrown solutions on top of a generic CI. It helps overcome common state management issues and adds several must-have capabilities s for infrastructure management.

How to Use Amazon CodeWhisperer (AI Code Generator)

Terraform Cloud Agents vs. Spacelift Workers

What is a Kubernetes Operator? Definition & Examples

See the reasons we created the Spacelift worker Kubernetes operator, and some of the internals of how the operator works.

333-spacelift-workerpool-operator

spacelift workerpool operator

Spacelift WorkerPool Operator: Technical Overview

Jacob Martin

As many others did before and regretted later, we’ve decided to move a big part of our workload from long-running processes to Lambda. That said, we haven’t regretted it a bit. It’s been a breeze so far, even if there are a few shortcomings of Lambda, which are just annoying.
In this blog post, I’ll describe the positive sides, the negatives, and the approach we’ve taken that allowed us to radically simplify our setup, as well as clarify common misconceptions you might have about AWS Lambda development.
But first of all, let’s take a look at…

Our backend is based on two main constructs:
<ul>
<li style="font-weight: 400;" aria-level="1">The server, which exposes GraphQL and REST APIs to the outside world.</li>
<li style="font-weight: 400;" aria-level="1">The drain, which consumes a bunch of SQS queues to handle asynchronous jobs. Each SQS queue transports several event types, based on a logical separation (webhooks, async jobs, cronjobs, etc.)</li>
</ul>
We have a very asynchronous architecture. The server often emits async jobs instead of doing something by itself, and the drain often emits new events when handling existing ones.
Because we’re a <a href="https://spacelift.io/blog/ci-cd-pipeline" target="_blank" rel="noopener">CI/CD system</a>, our traffic patterns are very unevenly distributed. Sometimes there are few jobs, at other times, somebody creates an avalanche of them.
Until recently, the drain was a long-running process on ECS, reading from multiple SQS queues. Autoscaling in such a situation is fairly hard. The simplest option is to have a separate process per SQS queue, with each process scaled independently. Even doing this would have resulted in paying too much for periods of low traffic, along with handling spikes in traffic too slowly. The diagram below describes that architecture visually:

image2-26

Our Architecture

Also, everything on the backend is written in Go, no exceptions.

Many people think that moving to Lambda requires huge codebase changes and has to result in having a lot of binaries, one for each Lambda Function. That’s actually not necessary. Yes, Lambda does only call the binary without any arguments (and there’s no way to change that to my knowledge &#8211; I tried), but you can provide arbitrary environment variables.
In our case, we have a single <code>spacelift</code> binary that will run the server process if called as <code>spacelift backend server</code>, but if called as plain <code>spacelift</code>, then it assumes it’s being run as a Lambda handler and will take a look into the <code>HANDLER</code> environment variable to deduce which handler to actually run. This way, we can keep using a single binary for everything, and operational simplicity ensues. The following diagram shows the new setup:

image1-27

Changing Our Codebase for Lambda

Lambda can also be quite annoying for local development. The way we solve this is by having all of our handlers &#8211; as you’ve seen above &#8211; fronted by SQS queues. This way we can run the handlers locally, and have a shim that polls the SQS queue (a copy the given developer has), translates it into the Lambda-native SQS message format, and passes that to the handler. Very similar to the way the original setup worked, but just for local development.
In practice, this means we have a <code>spacelift backend local-drain</code> command that just runs all handlers with their respective SQS queue pollers. That gets recompiled on each code change, so the feedback loop from code change to locally deployed new binary is around 5 seconds and you can easily attach a debugger to it.

<ul>
<li><a href="https://spacelift.io/blog/devops-best-practices" target="_blank" rel="noopener">16 DevOps Best Practices to Follow</a></li>
<li><a href="https://spacelift.io/blog/ci-cd-tools" target="_blank" rel="noopener">Top Most Useful CI/CD Tools for DevOps</a></li>
<li><a href="https://spacelift.io/blog/how-specialized-solution-can-improve-your-iac" target="_blank" rel="noopener"><span data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;10 Ways to Improve Your Infrastructure as Code&quot;}" data-sheets-userformat="{&quot;2&quot;:513,&quot;3&quot;:{&quot;1&quot;:0},&quot;12&quot;:0}">10 Ways to Improve Your Infrastructure as Code</a></li>
</ul>

On ECS, the system we moved from, <a href="https://docs.aws.amazon.com/AmazonECS/latest/developerguide/specifying-sensitive-data-secrets.html" target="_blank" rel="nofollow noopener">you can just specify a list of environment variables and mark their sources as “to be taken from SecretsManager”</a>. With Lambda, that’s not possible. However, we wanted to keep dependency injection uniform across the codebase, so we wanted to have the same flow in Lambda as before.
Thus, we just pass a plain environment variable with the list of objects to get from SecretsManager. The Lambda binary will get those objects from SecretsManager, iterate over all key values in each, and set them as environment variables on the current process. This happens first-thing after starting, so the rest of our initialization process is oblivious to it. This might sound like a hack, but it’s simple, and it does the job.

Another misconception about Lambda is that Container Image support means it can take arbitrary Docker images and run them as-is. Unfortunately, that’s not true.
Container Images in Lambda basically work as a glorified file archive, nothing more, nothing less. Your binary still has to adhere to the Lambda communication protocol, Although you can still use Docker to build your image. The main difference is that thanks to the improved caching story, they let you use 10 GB images instead of just 50 MB for ZIP archives.
Another difference is that with Container Images you can’t configure Lambda layers to be used through the AWS console, as you can with ZIP archives. That said, you can easily copy layers into your Container Image:

The way your Lambda Function works is that it gets spun up, launches your binary, which can do any setup it wants and then starts polling for jobs from the Lambda runtime. Then, when the Lambda is not needed anymore (scaling down) it gets terminated.
So, you can use goroutines to do some async tasks in your Lambdas while they’re running, right? Well, no.
The Lambda Runtime will actually freeze your process between invocations. This means that if you have an async process doing work between invocations, it might be frozen right after it tries to do an IO operation and be woken up 30 seconds later, which makes the IO operation time out immediately.
In other words, you can’t safely use async processes in your Lambda binaries &#8211; at least not outside a single invocation’s timespan. Also, make sure that your database doesn’t terminate connections if it didn’t get a keepalive in 30 seconds, in case you’re connecting to it directly (which btw. was not an issue for us).

It’s not ideal.
With Lambdas, there is a concept of throttling. It happens when you try to invoke a lot of Lambdas at once, but they can’t scale fast enough. Lambda will then return a 429 error.
So, if you suddenly have lots of SQS messages, the SQS connector will scale up and send lots of invocations which might fail with the 429 error. So, the SQS connector will slow down and retry them in a second, right?
Well, no. Instead of retrying with those most recent messages a moment later, it will actually fail the message handling and put it back to SQS to be handled again after the visibility timeout passes (which might take several minutes). That is not great and can introduce additional delays in times of high traffic.
That said, we haven’t encountered this issue again since setting up high <a href="https://docs.aws.amazon.com/lambda/latest/dg/configuration-concurrency.html" target="_blank" rel="nofollow noopener">reserved concurrent execution numbers</a> on the Lambda functions. Based on the last few months of usage, this has been a non-issue to us.

With most systems, often “turn it off and back on again” is a quite good incident recovery mechanism. Your code gets into an unexpected state, and this way you reset it.
With Lambda, however, there’s no obvious way to invalidate all the current warm Lambda runtimes. So, if you need this, the way to go is actually to add a useless environment variable, so that you have a change, and then deploy it. Deploying will invalidate old Lambda runtime versions, and effectively  “turn it off and back on again”.

As described in the beginning, we often encounter load spikes, which required us to have many machines on standby before moving to Lambda. With Lambda, this works beautifully out of the box. Whenever we get a huge amount of events at a moment’s notice, Lambda is able to scale up to 10x the capacity, quickly handle all the events, and then scale down.
Moreover, we achieve all of this while having drastically reduced the cost of the whole setup. Lambdas for asynchronous event processing are very cost-effective. I’m not sure I’d recommend them for synchronous HTTP services, but here they definitely do shine.

Lambdas deploy instantaneously. They have perfect isolation &#8211; we don’t have to worry about one Function’s execution influencing any other Function, by starving resources or something similar. Cold starts are very short with Go, warm starts are basically instant. So as far as these considerations are concerned, we’re very happy. 
After migrating to Lambdas, they required very little upkeep, and haven’t been a source of incidents.

Overall, we’re very satisfied with our new setup. I hope the above lessons learned can be useful to somebody else considering the same switch, or planning their greenfield project on Lambda.

Spacelift is an alternative to using homegrown solutions on top of a generic CI. It helps overcome common state management issues and adds several must-have capabilities s for infrastructure management.

How to Manage AWS Lambda Functions with Terraform: Tutorial

Tricking PostgresSQL into Using an Insane - but 200x Faster - Query Plan

Creating a Flexible Backoffice Tool in a Technical Company Using Slack

See the approach we’ve taken that allowed us to simplify our setup and clarify common misconceptions you might have about AWS Lambda development.

89-aws-lambda-land

AWS Lambda Land

Adventures in AWS Lambda Land - a Migration Gone Well

tricking Postgres into using an insane - but 200x faster - query plan

At Spacelift, we use PostgreSQL (specifically Aurora Serverless) for most of our primary database needs. Our application query patterns are mostly small transactions that touch a few rows &#8211; a typical scenario that PostgreSQL handles without breaking a sweat.
We also use Datadog as a monitoring solution. Their agent has a handy feature to create metrics based on periodically executed SQL queries, allowing us to build smart and highly contextual monitors and alerts. Occasionally, such queries might be slow and dominate the processing time of the database. After hooking up Datadog Database monitoring, we identified a lot of slow queries and moved most of them to a separate Redshift cluster. There they execute once per day on a periodic snapshot of the database.
However, a number of these queries need to happen at a more frequent interval as they can detect alertable conditions. We couldn’t move these queries to Redshift because of the staleness of the data there. Aurora Serverless also doesn’t support read replicas to offload analytical queries to them, so no luck there either. Thus, we had to optimize, add indices, optimize, and again optimize until we cut the database usage of these metric-creating queries by 1-2 orders of magnitude.
This article will show you one such optimization story, which is interesting because both the before and after query plans will look somewhat ridiculous (in terms of cost). Moreover, it shows how PostgreSQL row count estimation can go very wrong. In this case, domain knowledge will help us trick PostgreSQL into a different query plan which will be waaay faster, without adding any additional indices.

Spacelift is a CI/CD platform specializing in Infrastructure as Code. Users create Runs (executions of, i.e., Terraform) which will be executed on Worker Pools. There is a public shared Worker Pool managed by Spacelift and private Worker Pools that users can host for themselves. 
Each Run belongs to a Stack, which can be thought of as a single environment, managing a bunch of infrastructure resources. If you know Terraform or CloudFormation, a single Stack maps to a single Terraform state file or a CloudFormation root stack, respectively.
A Run can have one of many types: Tracked, Proposed, and others. That type determines what the overall workflow will look like.

The query is supposed to notify us when the public Worker Pool cannot handle demand &#8211; user Runs are waiting too long to be handled by a Worker.

That’s a big query! What’s happening here?
We’re interested in the number of Runs (as well as the age of the oldest one) which are of the Proposed or Testing type (can be scheduled right away &#8211; other Run types require exclusiveness), are in the Queued state (which means they’re not yet being processed) and don’t have a Worker attached to them (are still waiting for one).
We only want Runs on Accounts that are far from their parallelism limit to reduce the number of false positives caused by users being correctly limited. So for each Run, we count the number of active Runs on its account and check if it&#8217;s under 50% of the parallelism limit, and only then do we count this Run as properly pending.
Unfortunately, that query lasts 20 seconds and consumes 50% of our database&#8217;s processing time. That’s pretty bad.

Let’s take a look at the query plan. I’ll be using <a href="https://explain.dalibo.com" target="_blank" rel="nofollow noopener">https://explain.dalibo.com</a> to visualize query plans. To get the query plan, I’m executing it prefixed with EXPLAIN (FORMAT JSON, ANALYZE) … so we can also see information about the query’s actual execution (ANALYZE).

image5-8

visualization of query plans

What can we see here?
On the left side, we’re joining Worker Pools with Stacks. We also calculate the number of active Runs for each Account using the SubPlan. We do a three-way hash join on the (Worker Pool, Stack) tuples, the Accounts, and the SubPlan results based on the Account ID. Then we loop over all those triples and get the pending Runs for each, finally counting all the pending Runs found.
The SubPlan goes over all Stacks in an Account, and for each Stack gets the active Runs for it (the ones where worker_id is not NULL).
Let’s look at the time distribution of the plan nodes.

image4-8

time distribution of the plan nodes

We can see that the most expensive part of this query is the Bitmap Index Scan which looks for Runs whose worker_id is not NULL, so let’s take a closer look at that part of the query plan.
For each Stack in the SubPlan, we look for active Runs on it. How do we do that?

image1-8

Bitmap

We scan the runs_worker_id index for storage pages containing Runs with a non-empty worker_id and the run_stack_id index for the pages containing Runs for the current Stack. Then we AND these together &#8211; we only want pages present in both of these scans. Finally, we scan the actual Runs from the pages and only take the ones that satisfy the predicates (pages found by the index may contain both relevant and irrelevant Runs).

image2-8

bitmap index scan

In a different tab of the left Index Scan, we can see the root of the problem. We made 531358 loops over this, and we expected to scan 120 mil Runs over the whole processing of the query here, but we scanned 800 mils. That’s much more.
But later in the Heap Scan of the ANDed pages, we see a gross overestimation.

image6-4

bitmap heap scan

We expected to scan 2 mil rows but scanned 42 overall, which means no Runs were executing on most Stacks at this instant.
Overall, it’s mostly this subquery being very expensive:

Now comes the domain knowledge. Is there something we know the optimizer seems not to…? Yes!
Only a minuscule part of the Runs in the database are active at any given time. Most Stacks stay dormant most of the time. Whenever a user makes a commit, only the affected Stacks will execute Runs. There will obviously be Stacks that are constantly in use, but most won’t. Moreover, intensive users will usually use private Worker Pools. The public shared Worker Pool is predominantly utilized by smaller users, so that’s another reason for not seeing many Runs here.
Right now, we’re iterating over all existing Stacks attached to the public worker pool and getting relevant Runs for each. How about we flipped that? We know there are at most as many active Runs as there are public Workers &#8211; orders of magnitude fewer than there are Stacks. Overall active Runs (not just the ones on the public Worker Pool) are also way less numerous, at most as many as there are Workers overall (and since the number of private Workers is the main dimension we currently base our Enterprise tier pricing on, query processing time being linearly correlated with the number of Workers is OK). Thus, we could get all active Runs and filter to just those whose Stack uses the public Worker Pool.
The only problem is, we’re working with a fairly opaque query optimizer, and PostgreSQL doesn’t have any way to give hints to it &#8211; like forcing a join strategy. So we’ll have to do that differently. We’ll create a query that scans the active Runs and then uses another subquery to filter them to just the ones related to a relevant Stack. Then we’ll have to hope the query is opaque enough for Postgres not to get too clever with it.

This query does exactly what we described. Scan active Runs, filter to just those with more than 0 Stacks in the current account and connected to the public Worker Pool. There would be either 1 or 0 such Stacks for each Run.
Executing this query, we get down to 100ms instead of the original 20s. That’s a vast improvement. What does the query plan say? (apologies for the long vertical picture)

At Spacelift, we use PostgreSQL (specifically Aurora Serverless) for most of our primary database needs. This article will show you our optimization story and how PostgreSQL row count estimation can go very wrong.

Spacelift WorkerPool Operator: Technical Overview

Get the newsletter

Recent posts

Adventures in AWS Lambda Land – a Migration Gone Well

Tricking PostgresSQL into Using an Insane – but 200x Faster – Query Plan

Running Spacelift CI/CD workers in Kubernetes using DinD

Creating a Flexible Backoffice Tool in a Technical Company Using Slack

Building a Secure CI/CD Integration with Azure

Share your data and download the guide