IaSQL is an open-source software tool that creates a two-way connection between an unmodified PostgreSQL database and an AWS account so you can manage your infrastructure from a database. In this post, we are going to learn how untagged ECR images can rack up your AWS bill unnecessarily and how to get rid of unused repository images with a single query in IaSQL: DELETE FROM repository_images WHERE tag = '<untagged>'; Every time you want to deploy a new version of your code to an ECS container or EKS pod, you build and push a new docker image from your code into an ECR container repository. The latest image in an ECR repository is the only one in use. Stale images remain in the repository and accumulate over long periods by normal usage of ECS, or EKS, as there is typically no workflow to delete untagged images. However, there is a gotcha. AWS charges $0.10 per GB per month for images stored in private or public ECR repositories per the pricing page. This doesn't sound like a lot, but for context, the size of the IaSQL docker image is around 2 GB. We have a CI job that deploys our staging environment every time we land a pull request to the main branch of our repository. This added up to 845 deploys over only a few months. Those 845 images, which have since been deleted, summed up to roughly 1700 GB which was $170 per month in our case. However, companies with lots of microservices on ECS or large docker containers can have many gigabytes of unused storage that can come out to hundreds or thousands of dollars per month of unnecessary AWS spend.
How do you get rid of untagged ECR images though? Deleting hundreds, or thousands, of container images across ECR repositories is quite tedious through the AWS console, which involves multiple clicks per deleted image. There are a few other options. IaC tools require state file manipulation, or it is not possible at all as ECR images are not typically modeled in the infrastructure declaration as they are often generated via CI and are quite numerous. Cloud Query and Steampipe let you query your cloud and inspect your ECR images, but do not let you delete the images or any part of your cloud account for that matter as they are read-only. The most common solution is often a script that calls the AWS SDK or CLI directly. IaSQL lets you delete untagged images to eliminate unnecessary AWS costs using the following query:
SELECT * FROM iasql_install('aws_ecr');
DELETE FROM repository_images WHERE tag = '<untagged>';
It is also possible to delete old images, or images past a certain size This query deletes unused images pushed to the repository before 2023 that are bigger than 10 GB:
DELETE FROM repository_images WHERE tag = '<untagged>' AND pushed_at < '2023-01-01' AND size_in_mb = 10000;
There is a way to delete all unused images if you are using ECR with ECS exclusively:
SELECT * FROM iasql_install('aws_ecs_fargate');
DELETE FROM repository_images;
After installing the aws_ecs_fargate module, which installs the dependant aws_ecr module if it's not already installed, the schema relations between the AWS ECS service will not let you delete ECR images currently in use and will reinstate the ones that are currently active.
Questions or issues with AWS? Join our Discord channel to ask away, or just to let us know if you would like to see more AWS cost optimizations like this one.