by timescale

timescale / pg_prometheus

PostgreSQL extension for Prometheus data

205 Stars 49 Forks Last release: about 1 year ago (0.2.2) Apache License 2.0 65 Commits 4 Releases

Available items

No Items, yet!

The developer of this repository has not created any items for sale yet. Need a bug fixed? Help with integration? A different license? Create a request here:


We'll be sunsetting this project in the coming months as we focus on a new implementation with additional functionality and better support for new TimescaleDB features (such as compression). You can find the new project at

More details can be found in our design document for the new project.

This project will continue only in maintenance mode.

Prometheus metrics for PostgreSQL

is an extension for PostgreSQL that defines a Prometheus metric samples data type and provides several storage formats for storing Prometheus data.

Related packages to install: - Prometheus remote storage adaptor (required) - TimescaleDB (optional for better performance and scalability)

Running from Docker

A PostgreSQL docker image with both pg_prometheus and TimescaleDB installed is available in Docker Hub at timescale/pg_prometheus.

Example usage:

docker run --name pg_prometheus -d -p 5432:5432 timescale/pg_prometheus:latest-pg11 postgres \

Note that this image inherits from the official postgres image and so all options documented there are applicable to this image as well. Especially important for users that wish to persist data outside of docker volumes is the

environmental variable and accompanying volume mount.



  • Install PostgreSQL libraries and headers for C language backend development (
  • Make sure you have PostgreSQL bin in your
    and the postgresql-devel package for your version of PostgreSQL before compiling from source

To install from source, do:

make install # Might require super user permissions


to include the
shared_preload_libraries = 'pg_prometheus'

Start PostgreSQL and install the extension as a superuser using the

CREATE EXTENSION pg_prometheus;

Optionally grant permissions to the database user (

) that will own the Prometheus data:
-- Create the role

-- Grant access to the schema GRANT ALL ON SCHEMA prometheus TO prometheus;

This also requires superuser privileges.

Integrating with Prometheus

For quickly connecting Prometheus to pgprometheus simply connect the Prometheus PostgreSQL adapter to a database that has pgprometheus installed.

For more technical details, or to use pg_prometheus without Prometheus, read below.

Creating the Prometheus tables.

To create the appropriate Prometheus tables use:

SELECT create_prometheus_table('metrics');

This will create a

table for inserting data in the Prometheus exposition format using the Prometheus data type. It will also create a
to easily query data.

Other supporting tables may also be created depending on the storage format (see below).

Inserting data

With either storage format, data can be inserted in Prometheus format into the main table (e.g.

in our running example). Data should be formatted according to the Prometheus exposition format.
INSERT INTO metrics VALUES ('cpu_usage{service="nginx",host="machine1"} 34.6 1494595898000');


is a view, and PostgreSQL does not allow
to views, we create a specialized table to be the target of copy commands for normalized tables (raw tables could write directly to the underlying
table). By default, copy tables have a

One interesting usage is to scrape a Prometheus endpoint (e.g.

) directly (without using Prometheus):
curl http://localhost:8080/metrics | grep -v "^#" | psql -h localhost -U postgres -p 5432 -c "COPY metrics_copy FROM STDIN"

Querying data


view has the following schema:
  Column |           Type           | Modifiers
  time   | timestamp with time zone |
  name   | text                     |
  value  | double precision         |
  labels | jsonb                    |

An example query would be

SELECT time, value
FROM metrics
WHERE time > NOW() - interval '10 min' AND
      name = 'cpu_usage' AND
      labels @> '{ "service": "nginx"}';

Storage formats

Pgprometheus allows two main ways of storing Prometheus metrics: raw and normalized (the default). With raw, a table simply stores all the Prometheus samples in a single column of type `promsample`. The normalized storage format separates out the labels into a separate table. The advantage of the normalized format is disk space savings when labels are long and repetitive.

Note that the

view can be used to query and insert data regardless of the storage format and serves to hide the underlying storage from the user.

Raw format

In raw format, data is stored in a table with one column of type

. To define a raw table use pass
. This will also create appropriate indexes on the raw table. The schema is:
  Column   |           Type           | Modifiers
 sample    | prom_sampe               |

Normalized format

In the normalized format, data is stored in two tables. The

table holds the data values with a foreign key to the labels. It has the following schema:
  Column   |           Type           | Modifiers
 time      | timestamp with time zone |
 value     | double precision         |
 labels_id | integer                  |

Labels are stored in a companion table called

(note that
is in its own column since it is always present):
   Column    |  Type   |                          Modifiers
 id          | integer | not null default nextval('metrics_labels_id_seq'::regclass)
 metric_name | text    | not null
 labels      | jsonb   |

Use with TimescaleDB

TimescaleDB scales PostgreSQL for time-series data workloads (of which metrics is one example). If TimescaleDB is installed, pgprometheus will use it by default. To install TimescaleDB, follow the instruction here. You can explicitly control whether or not to use TimescaleDB with the `usetimescaledb

parameter to

For example, the following will force pgprometheus to use Timescale (and will error out if it isn't installed): ```SQL SELECT createprometheustable('metrics',usetimescaledb=>true); ```


We welcome contributions to this extension, which like TimescaleDB is released under the Apache2 Open Source License. The same Contributors Agreement applies; please sign the Contributor License Agreement (CLA) if you're a new contributor.

We use cookies. If you continue to browse the site, you agree to the use of cookies. For more information on our use of cookies please see our Privacy Policy.