docs/POSTGRESQL.md

<!--
 Licensed to the Apache Software Foundation (ASF) under one
 or more contributor license agreements.  See the NOTICE file
 distributed with this work for additional information
 regarding copyright ownership.  The ASF licenses this file
 to you under the Apache License, Version 2.0 (the
 "License"); you may not use this file except in compliance
 with the License.  You may obtain a copy of the License at

   http://www.apache.org/licenses/LICENSE-2.0

 Unless required by applicable law or agreed to in writing,
 software distributed under the License is distributed on an
 "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 KIND, either express or implied.  See the License for the
 specific language governing permissions and limitations
 under the License.
 -->

# Importing results from PostgreSQL

> [!TIP]
> See [otava.yaml](../examples/postgresql/config/otava.yaml) for the full example configuration.

## Installation

```bash
pip install 'apache-otava[postgres]'
```

## PostgreSQL Connection
The following block contains PostgreSQL connection details:

```yaml
postgres:
  hostname: ...
  port: ...
  username: ...
  password: ...
  database: ...
```

These variables can be specified directly in `otava.yaml` or passed as environment variables:

```yaml
postgres:
  hostname: ${POSTGRES_HOSTNAME}
  port: ${POSTGRES_PORT}
  username: ${POSTGRES_USERNAME}
  password: ${POSTGRES_PASSWORD}
  database: ${POSTGRES_DATABASE}
```

## Tests

Test configuration contains queries selecting experiment data, a time column, and a list of columns to analyze:

```yaml
tests:
  aggregate_mem:
    type: postgres
    time_column: commit_ts
    attributes: [experiment_id, config_id, commit]
    metrics:
      process_cumulative_rate_mean:
        direction: 1
        scale: 1
      process_cumulative_rate_stderr:
        direction: -1
        scale: 1
      process_cumulative_rate_diff:
        direction: -1
        scale: 1
    query: |
      SELECT e.commit,
             e.commit_ts,
             r.process_cumulative_rate_mean,
             r.process_cumulative_rate_stderr,
             r.process_cumulative_rate_diff,
             r.experiment_id,
             r.config_id
      FROM results r
      INNER JOIN configs c ON r.config_id = c.id
      INNER JOIN experiments e ON r.experiment_id = e.id
      WHERE e.exclude_from_analysis = false AND
            e.branch = 'trunk' AND
            e.username = 'ci' AND
            c.store = 'MEM' AND
            c.cache = true AND
            c.benchmark = 'aggregate' AND
            c.instance_type = 'ec2i3.large'
      ORDER BY e.commit_ts ASC;
```

## Example

### Usage

Start docker-compose with PostgreSQL in one tab:

```bash
docker-compose -f examples/postgresql/docker-compose.yaml up --force-recreate --always-recreate-deps --renew-anon-volumes
```

Run Otava in the other tab to show results for a single test `aggregate_mem` and update the database with newly found change points:

```bash
docker-compose -f examples/postgresql/docker-compose.yaml run --rm otava \
  analyze aggregate_mem --since=2025-01-01 --update-postgres
```

Expected output:

```text
time                       experiment_id       commit      config_id    process_cumulative_rate_mean    process_cumulative_rate_stderr    process_cumulative_rate_diff
-------------------------  ------------------  --------  -----------  ------------------------------  --------------------------------  ------------------------------
2025-03-13 10:03:02 +0000  aggregate-36e5ccd2  36e5ccd2            1                           61160                              2052                           13558
2025-03-25 10:03:02 +0000  aggregate-d5460f38  d5460f38            1                           60160                              2142                           13454
2025-04-02 10:03:02 +0000  aggregate-bc9425cb  bc9425cb            1                           60960                              2052                           13053
                                                                      ······························
                                                                                               -5.6%
                                                                      ······························
2025-04-06 10:03:02 +0000  aggregate-14df1b11  14df1b11            1                           57123                              2052                           14052
2025-04-13 10:03:02 +0000  aggregate-ac40c0d8  ac40c0d8            1                           57980                              2052                           13521
2025-04-27 10:03:02 +0000  aggregate-0af4ccbc  0af4ccbc            1                           56950                              2052                           13532
```

### Configuration

See [otava.yaml](../examples/postgresql/config/otava.yaml) for the example configuration:
* Block `postgres` contains connection details to the PostgreSQL database.
* Block `templates` defines shared pieces of configuration with YAML anchors: time column, a list of attributes, and metrics.
* Block `tests` contains configuration for the individual tests, specifically a query that fetches analyzed columns sorted by commit timestamp. Each test merges the shared configuration with `<<: *common`.

[schema.sql](../examples/postgresql/init-db/schema.sql) contains the schema used in this example.

[docker-compose.yaml](../examples/postgresql/docker-compose.yaml) contains example config required to connect to PostgreSQL:
1. `POSTGRES_*` environment variables are used to pass connection details to the container.
2. `OTAVA_CONFIG` is the path to the configuration file described above.
3. `BRANCH` variable is used within `OTAVA_CONFIG` to analyze experiment results only for a specific branch.


### CLI arguments

* `--update-postgres` - updates the database with newly found change points.