DEV Community: Mohamed Abdelwahed

When Not to Use DB Indexes

Mohamed Abdelwahed — Thu, 01 May 2025 06:44:07 +0000

Avoid Indexing DB Columns with Highly Repeated Values

Indexing is a powerful tool in databases that can dramatically improve query performance when used appropriately. However, not all indices are created equal regarding impact and efficiency. A common mistake developers make is adding indexes to columns that contain highly repeated (low-cardinality) values. In most cases, this wastes resources and can degrade performance. Here's why.

Understanding Indexes in PostgreSQL

An index in PostgreSQL is essentially a data structure (commonly a B-tree) that allows the database engine to quickly locate rows matching a condition. Indexes shine when they help narrow down the number of rows PostgreSQL needs to scan, especially in large tables.

The Problem with Low-Cardinality Columns

Low-cardinality columns are those where the number of distinct values is small compared to the total number of rows. For example, a status column with values like active, inactive, or archived, in a table of millions of rows, is low in cardinality.

Why indexing them is usually a bad idea:

Low Selectivity

Indexes are effective when they help filter out large portions of the data. If 90% of your table has status = 'active', an index won't help much — PostgreSQL will likely fall back to a sequential scan because it's cheaper than jumping back and forth between the index and table rows (known as "random I/O").

Extra Overhead on Writes

Every time you INSERT, UPDATE, or DELETE a row, PostgreSQL must update any indexes that involve that row. If your low-cardinality column has an index, you're paying the overhead for maintaining it — with little or no performance gain in reads.

Bloated Indexes

Indexes on repetitive values often result in large, inefficient index structures that waste disk space and can lead to slower maintenance operations like VACUUM or ANALYZE.

Query Planner Might Ignore It

PostgreSQL is smart. It analyzes data distribution and will avoid using indexes if they don't offer performance benefits. So even if you create an index on a column like is_active, PostgreSQL might not use it in queries unless the distribution changes significantly.

When It Might Be Okay

There are exceptions. Indexing a low-cardinality column can make sense if:

The table is relatively small, and you're optimizing for a very frequent query
The column is combined with other, more selective columns in a composite index
You're using partial indexes — e.g., CREATE INDEX ON users (id) WHERE status = 'archived'
You're optimizing for index-only scans and the column is in a covering index

Better Alternatives

Instead of blindly indexing low-cardinality columns, consider:

Partial indexes: Useful when queries always filter on specific values
Multicolumn indexes: More selective when used with additional filtering criteria
Materialized views: For precomputed subsets of data
Denormalization or restructuring: Sometimes, changing schema design helps more than indexing

Conclusion

Indexes are a powerful but limited resource. Indexing a column with highly repeated values in PostgreSQL often results in little to no performance gain and unnecessary overhead. Before creating an index, always consider the cardinality of the column and analyze your query patterns. Use tools like EXPLAIN ANALYZE to measure actual query performance and validate whether an index is truly beneficial.

Efficient Python Dependency Management with uv in Docker for FastAPI

Mohamed Abdelwahed — Wed, 05 Mar 2025 08:28:10 +0000

In modern Python development, managing project dependencies efficiently is crucial for optimizing both development and production environments. In this post, we'll focus on using the uv package manager to streamline Python dependency management in a Dockerized FastAPI application.

The Role of `uv` in Dependency Management

uv is a high-performance package manager designed to make Python dependency management faster, more reproducible, and less resource-intensive. Unlike traditional tools like pip and virtualenv, uv focuses on reducing complexity and enhancing speed by syncing your dependencies based on a lock file. By using uv, you can ensure that only the necessary system dependencies are installed, avoiding unnecessary overhead and improving the overall performance of your containerized application.

Dockerfile for FastAPI Application with `uv`

FROM python:3.12-alpine

# Install system dependencies required by uv
RUN apk add --no-cache curl git

# Install uv safely
RUN curl -LsSf -o /tmp/install_uv.sh https://astral.sh/uv/install.sh && \
    sh /tmp/install_uv.sh && \
    rm /tmp/install_uv.sh

# Set PATH to include uv
ENV PATH="/root/.local/bin:$PATH"

# Create app directory and set permissions
WORKDIR /app

# Copy dependency files first for caching
COPY pyproject.toml uv.lock ./

# Install dependencies using uv
RUN uv sync --frozen --no-install-project --no-dev --python-preference=only-system

# Copy the rest of the project files
COPY . .

# Run the FastAPI application
CMD ["uv", "run", "uvicorn", "app.main:app", "--host", "0.0.0.0", "--port", "80"]

Breakdown of the Dockerfile and `uv` Integration

1. Base Image and System Dependencies

FROM python:3.12-alpine
RUN apk add --no-cache curl git

We start by using the official python:3.12-alpine image, which is optimized for small size. Then, we install system dependencies curl and git, which are required for installing uv and potentially fetching git-based dependencies.

2. Installing `uv`

RUN curl -LsSf -o /tmp/install_uv.sh https://astral.sh/uv/install.sh && \
    sh /tmp/install_uv.sh && \
    rm /tmp/install_uv.sh

The core of this Dockerfile is the installation of the uv package manager. uv is installed by downloading and running the installer script. This step ensures that your project uses a fast, consistent, and lean dependency management tool. The installer script installs uv to the /root/.local/bin directory.

3. Setting Up `uv` in the Environment

ENV PATH="/root/.local/bin:$PATH"

Here, we add the directory where uv is installed to the PATH environment variable, ensuring it is accessible in subsequent Docker commands.

4. Copying Dependency Files and Installing Dependencies

COPY pyproject.toml uv.lock ./
RUN uv sync --frozen --no-install-project --no-dev --python-preference=only-system

We copy the pyproject.toml and uv.lock files, which contain all the necessary dependency information for the project.
We then use uv sync to install dependencies. The --frozen flag ensures that the exact versions from uv.lock are used, and --no-install-project avoids installing the project itself, focusing solely on dependencies. The --no-dev option skips installing development dependencies, while --python-preference=only-system ensures system dependencies are prioritized, minimizing the size of the image.

5. Copying Application Code

COPY . .

This command copies the remaining application files into the container. Since dependencies were already installed earlier, Docker will skip unnecessary installations if the code changes.

6. Running the FastAPI Application

CMD ["uv", "run", "uvicorn", "app.main:app", "--host", "0.0.0.0", "--port", "80"]

Finally, we use uv to launch the FastAPI application with uvicorn as the ASGI server. The app is configured to listen on port 80, and 0.0.0.0 ensures it's accessible externally.

Why Choose `uv`?

Speed: uv is optimized for performance and installs dependencies faster than traditional tools, especially in containerized environments where build times matter.
Reproducibility: By relying on uv.lock and using the --frozen flag, you ensure that every environment running the Docker container uses the same dependency versions, making builds more predictable and stable.
Lean Builds: uv avoids unnecessary installations of development dependencies or redundant Python packages, reducing the overall size of your Docker image.

Conclusion

Using uv in your Dockerfile offers significant improvements in dependency management, speed, and image size. The approach outlined here ensures that your FastAPI application is built on a reproducible, efficient foundation, making it easy to deploy and scale. By integrating uv, you not only streamline the installation process but also improve the overall stability and performance of your application in production environments.

Makefiles Make It Easy

Mohamed Abdelwahed — Fri, 15 May 2020 21:35:06 +0000

Sometimes when we developing applications we have a lot of commands for a run, test, format code, etc. and those commands may be long and we want to alias it, for many reasons we can't memorize these commands and we want to share it with other team members to make development easy for this situation we will use make utility to help us.

Lets Start

To know more about Make program we can write this command in the terminal.

man make

you should see something like this :

make - GNU make utility to maintain groups of programs

if you faced any errors make sure that make utility installed on your machine.

as you see in man make description that make developed to make compile C programs easy and can be used in other tasks.

Install Make

for most Linux distributions you will find make installed on your machine.

Ubuntu run sudo apt-get install build-essential for other distributions check your distro package manager.
Mac OS run xcode-select --install.
Windows

Get your hands dirty

Let us apply make utility on a PHP lumen app for example if we want to run lumen app with PHP internal server we need to run this command php -S localhost:8000 -t public in the terminal what if we make this command shorter to convert it only to make serve.

Create a file in the application root folder named makefile to tell make what to do.
Open makefile and paste the next code snippet and make sure to use tabs not spaces :

serve:
        php -S localhost:8000 -t public

3.Run make serve in the terminal instead of
php -S localhost:8000 -t public this is a basic usage for makerfile.

Lets Explain

Makefile consists of rules as the next structure of the rule :

target … : prerequisites …
        recipe
        …
        …

makefiles have a lot of detail but we will explain what we need in our scope so let us describe make rule as :

Before : this the name we will execute the rule with.
After : this is a list of prerequisites rule we declared before and want to run before The rule.
Next line starts with tab contain a command we want to run or script consists of several lines.

Example

#Variables
PHP = php

#Rules
serve:
        $(PHP) -S localhost:8000 -t public

test:
        ./vendor/bin/phpunit

fresh-db:
        $(PHP) artisan migrate:fresh

fresh-serve: fresh-db serve

fresh-test: fresh-db test
        echo Done;

Expalination

You can run any rule just by type make rule-name terminal opened in your app root folder with makefile in it.
As in the first block of the file, we can declare variables to reuse them with $(VariableName) notation.
serve, test and fresh-db rules only execute a command you can write any bash command.
fresh-serve rule runs the fresh-db and serve rules as prerequisites.
fresh-test rule runs the fresh-db and test rules as prerequisites and bash echo command.

Conclusion

I can't cover all make features in one article I just want to explain how I use makefiles to the development process easy you use it with any technology stack you want.

References

GNU make

DEV Community: Mohamed Abdelwahed

When Not to Use DB Indexes

Avoid Indexing DB Columns with Highly Repeated Values

Understanding Indexes in PostgreSQL

The Problem with Low-Cardinality Columns

Why indexing them is usually a bad idea:

Low Selectivity

Extra Overhead on Writes

Bloated Indexes

Query Planner Might Ignore It

When It Might Be Okay

Better Alternatives

Conclusion

Efficient Python Dependency Management with uv in Docker for FastAPI

The Role of uv in Dependency Management

Dockerfile for FastAPI Application with uv

Breakdown of the Dockerfile and uv Integration

1. Base Image and System Dependencies

2. Installing uv

3. Setting Up uv in the Environment

4. Copying Dependency Files and Installing Dependencies

5. Copying Application Code

6. Running the FastAPI Application

Why Choose uv?

Conclusion

Makefiles Make It Easy

Lets Start

Install Make

Get your hands dirty

Lets Explain

Example

Expalination

Conclusion

References

The Role of `uv` in Dependency Management

Dockerfile for FastAPI Application with `uv`

Breakdown of the Dockerfile and `uv` Integration

2. Installing `uv`

3. Setting Up `uv` in the Environment

Why Choose `uv`?