Jacob is a Software Engineer at Spacelift with experience working in high scale distributed systems environments: building storage systems, service meshes, observability infrastructure, and developer experience workflows.

All posts by Jacob Martin - Infrastructure as Code Expert

Jacob Martin

Author title

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

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

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

Engineering | Spacelift Blog

Engineering

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="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)

image3-8

What does the query plan say?

Exactly what we wanted! It scans active Runs, and for each of them, checks (using a primary key index lookup &#8211; fast!) whether the Stack it belongs to is relevant.
That’s it! The whole optimized query now looks like this:

Thanks for reading! The moral of this story is that query plans aren’t that hard to interpret with the right tools. Using your domain knowledge, you can rewrite your queries to achieve substantial performance boosts without adding unnecessary indices or denormalizing the data model.

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 for infrastructure management.

Managing Azure Resources using Spacelift

Running Spacelift CI/CD workers in Kubernetes using DinD

Building a Secure CI/CD Integration with Azure

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.

50-tricking_postgres_into_using_an_insane

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

Working on a SaaS product you’ll occasionally have to take administrative actions to assist a user or help them debug an issue. Maybe something is misconfigured and they need a hand, or possibly they just want another week in their trial. If you’re using something like Django, then you get a nice built-in admin UI based on your model definition, and that’s it &#8211; you have a great tool at your disposal by default. Even though you still need to carefully design the access flow, as they otherwise commonly become attack vectors.
If however you’re building a project in a custom way akin to “let’s combine many libraries and build exactly what we need” instead, then all you’ll have is the raw query language interface of your database &#8211; and that only if you’re lucky enough to have chosen a database which supports non-trivial querying.
In the long run though, as most developers know, running SQL queries on prod isn’t really a scalable or secure approach and requires significant coordination, communication and supervision in order not to accidentally corrupt your data. You also have to limit access to a few select individuals and have to manage access to sensitive data.
Moreover, you might have other dependencies than the SQL database. Then administering all the third-party services would require you to jump around various admin UIs, making everything even more complicated and error prone&#8230;

There’s a whole class of tools tailored specifically to solving this use case &#8211; WYSIWYG back office tools. Using one of these tools you can easily create dialogs with SQL queries underneath and connect them directly to your database. Depending on the tool, it might even be usable by non-technical people.
Unfortunately, these tools also bring a whole class of problems with them, mainly access management &#8211; both for people, and for the tool itself. You can use a cloud-based, managed tool, but then you have to give it arbitrary querying access to your internal production database. This usually makes sense, but is a security consideration that you have to take into account. You can also use self-hosted open source tools, but these come with their own set of trade-offs:
<ul>
<li style="font-weight: 400;" aria-level="1">You have to self-host them, costing you precious time.</li>
<li style="font-weight: 400;" aria-level="1">They need to have access to your production database, and people in your organization need to have access to them. So you either make them publicly accessible &#8211; and trust their security &#8211; or you make them internally accessible, and set up limited internal network access for everybody who needs to use them.</li>
<li style="font-weight: 400;" aria-level="1">Access-management in the tool itself.</li>
</ul>
Other than that, one common disadvantage is that they’re yet another tool you’ll have to use in your day-to-day work. They are also usually optimized for working with a SQL database, not additional third-party services.
After comparing various solutions on both sides of the cloud/open-source divide, we’ve decided we didn’t like either set of trade-offs, which put a hold on the whole initiative for a while.
Then we had an idea… What’s the most popular DevOps administration and management tool? Slack of course! I’m slightly joking, but the ChatOps trend&#8217;s significance cannot be denied. In this case, it did indeed look like it could solve all our problems.

Slack is great for a few reasons:
<ul>
<li style="font-weight: 400;" aria-level="1">It’s a textual interface &#8211; almost a terminal really &#8211; so you can transfer a lot of UX knowledge from your terminal experiences. Developers are always happy to use a text interface.</li>
<li style="font-weight: 400;" aria-level="1">You already manage access to Slack channels, so you can piggyback on top of that and only make your tool available in select channels. This way you don’t have to build additional access management for your tooling.</li>
<li style="font-weight: 400;" aria-level="1">If you design the UX well, it’s very easy to use for non-technical users.</li>
<li style="font-weight: 400;" aria-level="1">You automatically have a public audit log of all executed commands &#8211; the channel message history.</li>
<li style="font-weight: 400;" aria-level="1">You already use Slack.</li>
</ul>
It does have its disadvantages too:
<ul>
<li style="font-weight: 400;" aria-level="1">When Slack is dead (which hasn’t been a rare occurrence in recent times), you can’t access your backoffice tool. But that’s ok, as long as you keep your most critical maintenance commands available through alternative access channels.</li>
</ul>
Now for some Slack technicalities. For a project like this you can use slash commands or a slackbot. The gist of it is that slash commands are more structured, but also more limited. Slackbots on the other hand just interact with your channels like a normal user would, so the flexibility is limitless, but it requires more work on your side. In order to provide an experience that’s as user-friendly and magical as possible, we went with the slackbot approach.
And thus the Backoffice Bot was born…

slackbot1

hello Backoffice Bot

Internally, we already had an event handling system in place for application events &#8211; i.e. GitHub push notifications. We actually even have a <a href="https://docs.spacelift.io/integrations/slack" target="_blank" rel="noopener">Slack integration</a>, so there was ample opportunity for copy-paste driven development.
In practice though, it’s pretty simple. There’s an AWS API Gateway endpoint to handle Slack webhooks, it puts events on an SQS queue and then a handler which has access to all relevant production systems takes care of handling each message and potentially responding to it.

slackbot2

building the Slackbot

One more advantage of the slackbot using our existing event handling framework, is that we not only have access to our SQL database, we also have access to any third-party products we use, like various AWS offerings.
<h3>The Router Structure</h3>
All commands are registered with a message pattern, which is then converted into a regular expression with capture groups for the arguments. Here is an example code block specifying a command with its handler:

Initially we planned to make it a sophisticated <a href="https://en.wikipedia.org/wiki/Trie" target="_blank" rel="nofollow noopener">trie</a>-based router, but that’s just immensely more complex than going over a list of regular expressions and trying to match each. With this amount of traffic and number of commands there was simply no point in optimizing further.
This way of adding commands is really simple and there’s a bonus &#8211; an automatically generated <code>@Backoffice Bot help</code> message, which dynamically lists and describes all commands available in the current channel.
<h3>Approval Flow</h3>
With processes like these, oftentime you want certain commands to require approval. We do this by using Slack reactions. 
The Slackbot will first detail what it would like to do (dry-run so to say), and then ask for approval. Approval is required from the caller, as well as a predefined number of other people, which depends on the “destructiveness” of the command at hand.

slackbot3

slackbot approval flow

In order not to double-execute commands, we’re marking handled messages using a Slack reaction &#8211; this way we don’t have to use an external database. You can see this in the above picture with the Spacelift logo reaction on the root message.
<h3>Access Control</h3>
One additional requirement was that different commands are allowed to be executed by different people. In our case there are commands which are built for non-technical people, for example extending account trials; as well as commands built for technical people, for example displaying diagnostic information or cycling a worker pool’s workers.
We achieve this by limiting the available commands based on the channel. We have a #backoffice channel for non-technical people and a #backoffice-developers channel for technical people. They are private, so we can limit access levels by inviting select people to relevant channels.
The help message takes this into account, and only shows the commands available to you in the current channel.

Everybody using our new Backoffice Bot has quickly fallen in love with it. Adding new commands follows the same process as getting standard product changes into production, which is familiar to all developers out of the box. There are no additional tools, workflows, or processes to maintain. We definitely recommend this approach if you ever find yourself having similar challenges to solve.

slackbot4

slackbot

Spacelift is an alternative to using homegrown solutions on top of a generic CI solution. It allows you to automate, audit, secure, and continuously deliver your infrastructure.

How a Specialized Management Platform Can Improve Your IaC

Introducing the Spacelift Azure Integration!

Everybody using our new Backoffice Bot has quickly fallen in love with it. That is why in this article, we show how to create that flexible backoffice tool using Slack and what benefits does it bring for a technical company.

flexible Backoffice Tool

Creating a flexible Backoffice Tool in a Technical Company using Slack

Want to level up your Terraform knowledge? Explore our tutorials and step-by-step guides written by Terraform experts. Learn Terraform best practices and solutions to the most difficult problems.

Terraform Guides and Tutorials | Spacelift Blog

Terraform

Data sources in Terraform are used to get information about resources external to Terraform, and use them to set up your Terraform resources. For example, a list of IP addresses a cloud provider exposes.

We can find excellent examples of data source usage in the <a href="https://registry.terraform.io/providers/hashicorp/aws/latest/docs/data-sources/vpc#example-usage" target="_blank" rel="nofollow noopener">AWS provider docs</a>:

In this scenario, we may have created our aws_vpc manually, because it also contains other manually created resources, and now we want to start adding Terraform managed resources to it.
In order to do this, we use the aws_vpc data source to find the manually created aws_vpc, and then use its properties to configure our aws_subnet.
Another fancy use case would be to use the <a href="https://registry.terraform.io/providers/integrations/github/latest/docs/data-sources/repository_pull_requests" target="_blank" rel="nofollow noopener">GitHub provider Pull Requests data source</a> to list all Pull Requests, and then provision an on-demand preview environment for each Pull Request. It would go along the lines of:

By default, Terraform will refresh all data sources before creating a plan. You can also explicitly refresh all data sources by running <code>terraform refresh</code>.
Occasionally you’ll have data sources that change very often and would like to keep your resources in sync with those changes. The easiest way to achieve this is to just run Terraform every few minutes or so, do a refresh, and apply all resulting changes.
You could also use something akin to <a href="https://docs.spacelift.io/concepts/stack/drift-detection" target="_blank" rel="noopener">Spacelift’s Drift Detection</a> to automate this process and make sure it doesn’t interfere with your manual Terraform executions.

If you are struggling with Terraform automation and management, check out Spacelift. It helps you manage Terraform state, build more complex workflows, and adds several must-have capabilities for end-to-end infrastructure management.

Importing Existing Infrastructure into Terraform - step by step

5 Ways to Manage Terraform at Scale

Data sources in Terraform are used to get information about resources external to Terraform, and use them to set up your Terraform resources. Let's go through the example usage now. 

13-1

Terraform data sources

Terraform Data Sources - How They Are Utilised (Example)

provision an AWS EKS Kubernetes cluster with Terraform

AWS EKS provides managed Kubernetes clusters as a service. You’re on AWS and want to avoid getting into the details of setting up a Kubernetes cluster from scratch? EKS is the way to go!
In this guide, you will learn how to provision an AWS EKS Kubernetes cluster with Terraform. Let&#8217;s start with the basics.

First, locally install Terraform:

as well as the AWS CLI:

and kubectl:

If you’re on a different operating system, please find the respective installation instructions here:
<ul>
<li style="font-weight: 400;" aria-level="1">Terraform: <a href="https://learn.hashicorp.com/tutorials/terraform/install-cli" target="_blank" rel="nofollow noopener">https://learn.hashicorp.com/tutorials/terraform/install-cli</a></li>
<li style="font-weight: 400;" aria-level="1">awscli: <a href="https://docs.aws.amazon.com/cli/latest/userguide/install-cliv2.html" target="_blank" rel="nofollow noopener">https://docs.aws.amazon.com/cli/latest/userguide/install-cliv2.html</a></li>
<li style="font-weight: 400;" aria-level="1">kubectl: <a href="https://kubernetes.io/docs/tasks/tools/" target="_blank" rel="nofollow noopener">https://kubernetes.io/docs/tasks/tools/</a></li>
</ul>

Now, you’ll need to configure your AWS CLI with access credentials to your AWS account. You can do this by running

and providing your Access Key ID and Secret Access Key. You will also need to add the region. For the purposes of this guide, we will use us-east-2. Terraform will later use these credentials to provision your AWS resources.

You can now clone a repository which contains everything you need to set up EKS:

Inside you’ll see a few files, the main one being eks-cluster.tf:

It uses the EKS Terraform module to set up an EKS cluster with 2 worker groups (the actual nodes running your workloads): one with a single medium machine, and one with two small machines.

You can now create all of those resources using Terraform. First, run

to initialize the Terraform workspace and download any modules and providers which are used.
In order to do a dry run of the changes to be made, run

This will show you that 51 resources will be added, as well as their relevant details. You can then run terraform apply with the resulting plan, in order to actually provision the resources:

This may take a few minutes to finish. You might get a “timed out” error, in which case just repeat both the terraform plan and terraform apply steps.
In the end you will get a list of outputs with their respective values printed out. Make note of your cluster_name.

In order to use kubectl, which is the main tool to interact with a Kubernetes cluster, you have to give it credentials to your EKS Kubernetes cluster. You can do this by running

Make sure to replace &lt;output.cluster_name&gt; with the relevant value from your Terraform apply outputs.

You can now view the nodes of your cluster by running

This command is so long because it displays custom columns, thanks to which we can indeed see that there are 2 smaller nodes, and 1 bigger node.
Let’s deploy an Nginx instance to see if the cluster is working correctly.

You can see the status of it by running:

And finally set up a tunnel from your computer to this pod:

If you open <a href="http://localhost:3000" target="_blank" rel="nofollow noopener">http://localhost:3000</a> in your browser, you should see the web server greet you:

welcome to ngix

In order to destroy the resources we’ve created in this session, run

This may again take up to a few minutes. You can read more about it here: <a href="https://spacelift.io/blog/how-to-destroy-terraform-resources" target="_blank" rel="noopener">How to Delete Resources from Terraform Using the Destroy Command.</a>

I hope this guide helped you on your Kubernetes journey on AWS! If you want more help managing your Terraform state file, building more complex workflows based on Terraform, and managing AWS credentials per run, instead of using a static pair on your local machine, check out <a href="https://spacelift.io/" target="_blank" rel="noopener">Spacelift</a>. We’d love to have you!

Spacelift helps manage Terraform state, build more complex workflows, supports policy as code, programmatic configuration, context sharing, drift detection, resource visualization and many more.

Learn how to provision an AWS EKS Kubernetes cluster with Terraform. See a full step-by-step tutorial.

provision-an-aws-eks-kubernetes-cluster-with-terraform

How to Provision an AWS EKS Kubernetes Cluster with Terraform

Occasionally you might want to rename an AWS S3 bucket you are managing with Terraform. However, names of S3 buckets are immutable, which means you can’t change them directly. If you tried, Terraform would destroy the old one and then create a new one, resulting in data loss.
To avoid this, you need to create a new bucket with the desired name, move the data over to it, make the relevant Terraform state replacements, and finally delete the old bucket.
In this post you will learn how to rename an AWS S3 bucket in <a href="https://spacelift.io/blog/what-is-terraform">Terraform</a>. First things first &#8211; let’s say you have a bucket definition in your Terraform code:

and you want to change the name of the bucket to <code>new-name</code>.

Firstly, we’ll need to <a href="https://spacelift.io/blog/terraform-aws-s3-bucket">create a new bucket</a>. You can do this using the AWS CLI or the <a href="https://docs.aws.amazon.com/awsconsolehelpdocs/latest/gsg/console-help-gsg.pdf" target="_blank" rel="nofollow noopener">AWS console</a>. Just make sure to properly replicate the old settings, especially the ACL (so that your data doesn’t accidentally become public).
Now, we can copy all the files from the old to the new bucket:

Now that we have our new bucket, we need to remove the old one from our Terraform state and <a href="https://spacelift.io/blog/importing-exisiting-infrastructure-into-terraform">import</a> the new one in its place:

If you tried to run Terraform now, it would show you that there’s drift—and yes, there would be! We’ve just imported a bucket into a resource which still has <code>old-name</code> in the config.

That’s why we now have to finally change the name of the bucket in our Terraform config:

If you run Terraform now, you’ll see that there are no changes to be made.

If you want to, you can now delete your old bucket using:

Make sure all the data has successfully been copied over to the new bucket.

If you’re using Spacelift then you can use <a href="https://docs.spacelift.io/concepts/run/task" target="_blank" rel="noopener">tasks</a> to run the above arbitrary AWS CLI commands. The default runner image already contains the AWS CLI, so no changes are necessary there.
One additional thing you can do is <a href="https://docs.spacelift.io/concepts/stack/stack-locking" target="_blank" rel="noopener">lock the Stack</a> while you’re running the migration, this way nobody will accidentally run Terraform or make any other changes while you’re moving the data.
And that’s it! If you have any questions about how to rename an AWS S3 bucket, drop me a line in the comments and I’ll get back to you straight away.

Learn how to rename an AWS S3 bucket in Terraform. See renaming process of Amazon s3 bucket in a few simple steps.

Jacob Martin

Tricking Postgres into using an insane – but 200x faster – query plan

Recent posts

Creating a flexible Backoffice Tool in a Technical Company using Slack

Terraform Data Sources – How They Are Utilised

How to Provision an AWS EKS Kubernetes Cluster with Terraform

How to Rename an AWS S3 Bucket in Terraform