> ## Documentation Index
> Fetch the complete documentation index at: https://docs.costgraph.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL

> Allocate a shared PostgreSQL instance's cost to the applications, teams, or customers that use it

A multi-tenant PostgreSQL instance serves many applications, teams, or customers from one set of
compute and storage. Your bill shows it as one line. Connect the instance to CostGraph, and CostGraph
splits that line by how each tenant uses the instance, so every tenant sees its own share.

The instance can run with any provider whose bill you connect to CostGraph, as long as the bill lists
the instance on its own line. CostGraph reads the instance's usage statistics every hour. It reads sizes and
activity counters, not the contents of your tables.

## How each tenant's share is worked out

Each day, CostGraph takes the instance's billed charges and splits them across the databases on it.
Databases that your provider runs for its own maintenance, such as `rdsadmin` on Amazon RDS,
`cloudsqladmin` on Cloud SQL, and `azure_maintenance` and `azure_sys` on Azure, are left out.

| Charge | Split by |
| - | - |
| Storage | Each database's size |
| Compute | Each database's activity, measured as active time on PostgreSQL 14 and later and as transactions before that |

CostGraph's own hourly reads are not counted as activity. On a
day with less than a minute of activity across the instance, CostGraph splits compute by size, the
same as storage. An idle instance is then not charged to the database CostGraph signs in to.

Tenants can also share one database, each signing in with its own role. That database's compute share
is then split across the roles by their query time.

You tell CostGraph which tenant owns each database or role with a label. The shares add up to the
instance's bill, so your totals stay the same.

## Match your layout to a labelling approach

| How tenants use the instance | Label | Each tenant's share follows |
| - | - | - |
| A database per tenant | Each database | The database's size and activity |
| A shared database, with a role per tenant | Each role | The role's query time, for compute |
| A shared database, with one login for everyone | The database, with a shared value such as `app=shared` | Percentages you set in a [shared cost split](https://docs.costgraph.ai/costgraph/allocation/showback.md#split-shared-costs) for that value |

Instances often mix these layouts. Label each database or role by the approach that fits it.

## Before you begin

* **The instance's cost reaches CostGraph on its own line.** Connect the billing data for the provider
  that runs the instance, and check that the bill lists the instance by its resource ID. Some
  providers only report cost per project or service by default. STACKIT, for example, needs its
  [FOCUS report](https://docs.costgraph.ai/costgraph/integrations/stackit.md#if-you-have-stackits-focus-report).
* **You know the instance's resource ID.** Use the ID from the instance's line on the bill, such as
  its ARN for an instance on AWS. CostGraph splits the cost billed to that ID.
* **CostGraph can reach the instance.** CostGraph Cloud reaches instances at public addresses only,
  and connects with the SSL mode `verify-full` or `verify-ca`. A self-hosted installation can reach
  instances on your private network.

## Create a login for CostGraph

Create a login that can read usage statistics, and enable `pg_stat_statements` in the database
CostGraph signs in to:

```sql theme={null}
CREATE ROLE costgraph LOGIN PASSWORD 'choose-a-password';
GRANT pg_monitor TO costgraph;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
```

The `pg_monitor` role lets the login read sizes and activity without reading any table.
`pg_stat_statements` provides each role's query time. It must also be listed in the instance's
`shared_preload_libraries`. On a managed instance, enable it in your provider's settings. Without it,
CostGraph still splits the instance by database but cannot split a shared database by role.

If the extension is installed in a schema other than `public`, let the login use that schema:

```sql theme={null}
GRANT USAGE ON SCHEMA extensions TO costgraph;
```

## Label databases and roles

Tell CostGraph which tenant owns each database or role. You can assign an owner in CostGraph, which
needs no access to the instance, or add a comment in PostgreSQL. Use the key you group spend by, such
as `app`, `team`, or `customer`.

### Assign an owner in CostGraph

1. Open **Cost breakdown** in the sidebar, select the **Showback** tab, and select **Database allocation**.
2. Under **Group by**, choose `postgres:database`. To label the roles of a shared database, choose
   `postgres:role`.
3. Hover over the database or role and select **Assign owner**.
4. Choose the tag key and value, such as `team` and `checkout`, and select **Assign owner**.

CostGraph saves the owner as a [virtual tag](https://docs.costgraph.ai/costgraph/virtual-tags/overview.md) rule and applies it to
the whole split straight away. Edit or remove it later under **Settings > Virtual tags**.

### Label with a comment

Add a `key=value` comment that names the tenant:

```sql theme={null}
COMMENT ON DATABASE orders IS 'app=checkout';
COMMENT ON DATABASE acme_prod IS 'customer=acme, team=enterprise';
```

For a shared database, label the role each tenant signs in with:

```sql theme={null}
COMMENT ON ROLE checkout_service IS 'app=checkout';
COMMENT ON ROLE reporting IS 'app=bi';
```

Each label becomes a tag on that database's or role's share of the cost. Separate several labels with
commas. A labelled database keeps its whole share, even when several roles use it. CostGraph reads
labels every hour, and each read applies your current labels to the last 35 days of the split. To
apply a new comment straight away, select **Resync** on the connection under **Settings >
Integrations**.

If a database has both an owner from CostGraph and a comment label for the same key, the owner from
CostGraph wins.

## Connect the instance

1. Open **Settings > Integrations** and pick **PostgreSQL**.
2. Pick the CostGraph tenant whose bill contains the instance, and enter its **Instance ID**.
3. Enter the host, port, database, and the login you created. Choose the SSL mode your instance
   requires. If its certificate is signed by a private CA, paste the CA certificate.
4. Select **Connect**.

CostGraph signs in before saving. If the check fails, the error names the cause. Common causes are a
wrong login, a missing `pg_monitor` role, a certificate that cannot be verified, and an unreachable
instance. A warning tells you when `pg_stat_statements` is not enabled, not loaded, or in a schema the
login cannot use, or when the bill has no cost for the instance ID yet.

The connection keeps its warnings, and the reason for any failed hourly read, on the PostgreSQL page
under **Settings > Integrations** until you fix them.

Connect each shared instance separately. To change the host, port, or login later, enter the password
again.

## See each tenant's share

Open **Cost breakdown** in the sidebar, select the **Showback** tab, and select **Database allocation**. Under
**Group by**, pick the key from your labels, such as `app` or `customer`, to see each tenant's share
of your shared instances.

To see each database by name, labelled or not, group by `postgres:database`. Group by
`postgres:role` to see the roles of a shared database.

Spend that no label claims appears as **Unallocated**. Label the remaining databases or roles. To
divide a database that tenants share through one login, label it with a shared value and split that
value with a [shared cost split](https://docs.costgraph.ai/costgraph/allocation/showback.md#split-shared-costs).

The **Billed** layer keeps showing each instance as one line, so your totals do not change.

## Fix common problems

* **Nothing is split.** CostGraph splits from the hour it first reads the instance, so earlier days
  stay whole. If the connection warns that the bill has no cost for the instance ID, check that the
  provider's bill is connected and lists the instance under that ID.
* **The connection shows that the instance ID matches several resources.** Enter the instance's full
  resource ID as it appears on the bill. CostGraph does not split a cost it cannot tie to one
  instance.
* **A shared database is not split by role.** Check the warning on the connection. It names whether
  `pg_stat_statements` is missing, not loaded, or in a schema the login cannot use.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.