# Prefect Workflow Orchestration

> Prefect for workflow orchestration with server, agent, and PostgreSQL.

- Category: Database Stacks
- Difficulty: intermediate
- Components: prefect-server, prefect-agent, postgresql
- Tags: prefect, orchestration, workflow, etl, python
- Source: https://docker.recipes/databases/prefect-orchestration

## Overview

Prefect is a modern Python-native workflow orchestration platform that emerged as a next-generation alternative to Apache Airflow, designed to eliminate the pain points of traditional workflow management. Unlike rigid DAG-based systems, Prefect allows developers to build dynamic, data-dependent workflows using pure Python code with built-in observability, automatic retries, and intelligent caching. The platform consists of a server component that provides the API, UI, and orchestration engine, while agents act as lightweight workers that poll for and execute flow runs.

This stack combines the Prefect server with PostgreSQL as the backend database and a Prefect agent for flow execution, creating a complete orchestration environment. PostgreSQL serves as the metadata store, tracking flow runs, task states, logs, and scheduling information, while the agent enables distributed execution of workflows across different environments. The server exposes a modern web UI for monitoring pipeline performance, debugging failed runs, and managing deployments.

Data engineers, ML engineers, and Python developers building complex ETL pipelines, machine learning workflows, or data processing jobs will find this stack particularly valuable. Unlike Airflow's template-based approach, Prefect allows for truly dynamic workflows where tasks can be generated at runtime based on data or external conditions. This makes it ideal for organizations that need flexible, maintainable data pipelines with modern observability features and minimal operational overhead.

## Features

- Pythonic workflow definition with native Python functions as tasks
- Dynamic task generation and conditional workflow logic at runtime
- Real-time flow monitoring with detailed execution graphs and logs
- Automatic retry mechanisms with exponential backoff and custom retry policies
- Result caching and data persistence between task runs
- Work pools and agents for distributed execution across environments
- PostgreSQL metadata store with full ACID compliance and concurrent access
- Built-in notifications and alerting for flow state changes

## Use cases

- ETL pipelines processing daily batch data with dynamic source discovery
- Machine learning model training workflows with hyperparameter optimization
- Data validation and quality monitoring pipelines with conditional alerting
- Multi-tenant data processing with isolated execution environments
- Financial data processing requiring strict audit trails and compliance
- IoT data ingestion pipelines with real-time processing and aggregation
- Development and staging environments for testing complex data workflows

## Prerequisites

- Docker Engine 20.10+ and Docker Compose V2 for container orchestration
- Minimum 2GB RAM (1GB for PostgreSQL, 1GB for Prefect components)
- Port 4200 available for Prefect UI access
- Python knowledge for creating and deploying Prefect flows
- Basic understanding of workflow orchestration concepts and task dependencies
- PostgreSQL familiarity for database maintenance and troubleshooting

## docker-compose.yml

```yaml
services:
  postgres:
    image: postgres:16-alpine
    container_name: prefect-postgres
    restart: unless-stopped
    environment:
      POSTGRES_USER: ${POSTGRES_USER:-prefect}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-prefect}
      POSTGRES_DB: ${POSTGRES_DB:-prefect}
    volumes:
      - postgres_data:/var/lib/postgresql/data
    networks:
      - prefect-network

  prefect-server:
    image: prefecthq/prefect:2-python3.11
    container_name: prefect-server
    restart: unless-stopped
    command: prefect server start
    ports:
      - "${PREFECT_PORT:-4200}:4200"
    environment:
      PREFECT_API_DATABASE_CONNECTION_URL: postgresql+asyncpg://${POSTGRES_USER:-prefect}:${POSTGRES_PASSWORD:-prefect}@postgres:5432/${POSTGRES_DB:-prefect}
      PREFECT_SERVER_API_HOST: 0.0.0.0
    depends_on:
      - postgres
    networks:
      - prefect-network

  prefect-agent:
    image: prefecthq/prefect:2-python3.11
    container_name: prefect-agent
    restart: unless-stopped
    command: prefect agent start -q default
    environment:
      PREFECT_API_URL: http://prefect-server:4200/api
    depends_on:
      - prefect-server
    networks:
      - prefect-network

volumes:
  postgres_data:

networks:
  prefect-network:
    driver: bridge
```

## .env

```bash
# Prefect
PREFECT_PORT=4200
POSTGRES_USER=prefect
POSTGRES_PASSWORD=prefect
POSTGRES_DB=prefect
```

## Usage notes

- Prefect UI at http://localhost:4200
- Define flows in Python
- Agent executes flow runs
- Modern alternative to Airflow

## Troubleshooting

- prefect-server container fails with database connection errors: Verify PostgreSQL container is healthy and POSTGRES_PASSWORD matches PREFECT_API_DATABASE_CONNECTION_URL
- Agent shows 'No work queues found' in logs: Create a work queue in the UI or use 'prefect work-queue create default' command
- Flow runs stuck in 'Pending' state: Check agent logs for connection issues and ensure PREFECT_API_URL points to correct server address
- PostgreSQL container crashes with 'data directory not empty': Remove postgres_data volume or check file permissions on mounted directories
- Prefect UI loads but shows API connection errors: Verify prefect-server container is running and accessible on port 4200
- High memory usage in PostgreSQL: Tune shared_buffers and work_mem settings, or implement log retention policies for large deployments

