# Practical example

- **Dockerfile**: A Postgres database with prometheus_fdw installed
- **setup-prometheus-fdw.sql**: A script to show how to configure
- **setup-cache.sql**: Setup tables for local storage of metrics data
- **setup-metrics-sync.sql**: Automatically sync data from Prometheus to Postgres
- **run-demo.sh**: A script showing the whole process of running the demo

## Data model

This data model was inspired by the [Crunchy Data postgresql-prometheus-adapter](https://github.com/CrunchyData/postgresql-prometheus-adapter).

**metric_labels**: Stores the metric name labels.

```
 id |               name                 |      labels
----+------------------------------------+-------------------------
  1 | container_cpu_usage_seconds_total  | {"pod": "my-pod-1", ...}
  2 | container_cpu_usage_seconds_total  | {"pod": "my-pod-2", ...}
  3 | container_memory_working_set_bytes | {"pod": "my-pod-1", ...}
  4 | container_memory_working_set_bytes | {"pod": "my-pod-2", ...}
```

**metric_values**: A partitioned table that stores metric values, when they happened, and the corresponding labels.

```
 label_id |    time    |  value
----------+------------+----------
     4320 | 1702678142 | 12214272
     4320 | 1702678742 | 11923456
     4320 | 1702679342 | 12230656
     4320 | 1702679942 | 11804672
     4320 | 1702677542 | 11870208
     4331 | 1702679942 | 53743616
     4331 | 1702678142 | 54022144
     4331 | 1702678742 | 53903360
     4331 | 1702679342 | 53288960
     4331 | 1702677542 | 53514240
```

## Example query

The query in **sample-query.sql** is an example of showing the current memory utilization of each container in kube-system.