Postgres as Analytics DB for Prometheus data

We are seeing increasing scenarios where Prometheus data is required for Analytical purposes beyond the traditional Observability and alerting.

For example if we need to know which k8s pods don't even use the requested amount of cpu/memory.

There are two challenges that we are trying to overcome here

• Short term storage in Prometheus
• Join data from different prometheus metrics

A SQL database like Postgres PostgreSQL Global Development Group would make perfect sense in this case.

Tembo has created an open source Postgres extension called Prometheus_fdw, which can query prometheus data as a foreign table using SQL.

I have built a solution with a quick hack with good old SQL in 3 steps. We will use docker from Docker, Inc for simplicity

Solution

• Use prometheus_fdw to fetch metrics data from prometheus
• Use pg_cron to sync delta changes from prometheus at regular interval

Steps below

• Standup the postgresDB

git clone https://github.com/vwake7/play-with-promethus_fdw.git
cd play-with-promethus_fdw

# Build the image
docker build -t example-local-image .

# If you have another container running with the same name, delete it
docker rm --force local-tembo

# Run your custom image
docker run -d -it --name local-tembo --rm --network=host example-local-image        

• Standup Prometheus - You can use a prometheus that you have already installed or install microk8 from and enable prometheus and note the prometheus url
• Run the DDL from Script - Create the DB objects and sync prometheus metrics to PostgresDB.

Check the result

select * from metrics_local order by 2,3;        

You should be able to see the metrics ordered by time as the cron job syncs prometheus metrics to the local table in Postgres.