Using airflow's postgres database for application data

Question

I'm starting a new project which requires a data warehouse, for which we'll be using postgres. (Shameless plug: swarm64 makes postgres a great DW option for datasets up to terabytes large)

I'm using apache airflow to orchestrate the workloads, but as I'm new to airflow, I'm not sure what the best practice is for the application's DB needs.

For some more context, I'm using airflow's docker-compose.yml, and I'm also an airflow newbie.

Noticing that the docker-compose already defines a postgres db:

...
  postgres:
    image: postgres:13
    environment:
      POSTGRES_USER: airflow
      POSTGRES_PASSWORD: airflow
      POSTGRES_DB: airflow
    volumes:
      - postgres-db-volume:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD", "pg_isready", "-U", "airflow"]
      interval: 5s
      retries: 5
    restart: always
...

I'm immediately wondering if it would be a better idea to add another postgres service or if to configure the existing one to have 2 users, 2 databases...

Eventually, I'll move this project to a cloud, and will probably use a AWS postgres RDS or similar.

My question then is:

What is the best practice here?
If there isn't one, what are the tradeoffs between the different approaches?

Elad Kalif · Accepted Answer · 2021-05-19 05:14:46Z

2

Airflow doesn't care what is your DWH you will be able to interact with it using Hooks and Operators. Many of them are available as Providers to Airflow and you can always write custom ones if needed. You need to separate between the Airflow backend metadata db (which can be PostgreSQL, MySQL) and you analytical storage where you store your processed data which can be anything you want (PostgreSQL, MySQL, S3, BigQuery, and many others).

Do NOT make Airflow backend database also your analytical database even if they both are PostgreSQL!

As for your questions the answer is:

Use plain regular PostgreSQL/MySQL for your Airflow installation.

answered May 19, 2021 at 5:14

Elad Kalif

16.4k3 gold badges29 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joey Baruch Over a year ago

Thanks for your answer, @Elad. Clearly, there's added complexity either way, and clearly Airflow is built with separation of concerns about the application DWH/DB. Could you provide a little more reasoning for your answer?

Elad Kalif Over a year ago

There is no logic in binding two diffrent services. Consider that when you use Airflow you will probably evntually upgrade version by running migration scripts and commands like upgrade db etc.

Collectives™ on Stack Overflow

Using airflow's postgres database for application data

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related