An InfluxDB Line Protocol API for PostgreSQL

When working at Timescale, we encountered a lot of users who were using InfluxDB and wanted to migrate to TimescaleDB. They had the full setup: using Telegraf for collecting data, often Kafka or similar solution as buffer, and sending it all to a InfluxDB backend.

Although InfluxDB is a very good database and very efficient for ingesting data, maintaining two different database systems in parallel is expensive in terms of where you have to spend your time. The problems are mainly at two levels:

• InfluxDB has it’s own query language, meaning you need to maintain two different pieces of application code rather than a single one if you need the application to connect to both databases.

• Applications usually need to have a relational database for other information such as users, accounts, sales, etc. This means that you need specialists on two systems.

• With solutions like TimescaleDB offering competitive performance on PostgreSQL, it is also interesting to test the performance and compare it with InfluxDB.

As a result, users where looking at ways to consolidate the system and only use PostgreSQL, but since PostgreSQL does not speak the Influx Line Protocol (ILP), it needed to be transformed to SQL, or use an extension that speaks the Influx Line Protocol.

The pg_influxdb extension takes care of this problem. It is a PostgreSQL extension that makes PostgreSQL speak the InfluxDB wire protocol where data lands in ordinary PostgreSQL tables. You query it with SQL. This is a re-implementation of the pg_influx extension based on my experience from that project.

What it does

The pg_influxdb extension registers background worker processes when PostgreSQL starts. The workers use the same ports as InfluxDB, which is HTTP on port 8086 and UDP on port 8089. When a write arrives, the worker parses it and inserts rows into the appropriate table in the configured schema.

There is no translation layer sitting in front of PostgreSQL. The workers connect to the database directly and use the standard PostgreSQL server programming interface (SPI) to execute queries. The data lives in normal heap tables, visible to any PostgreSQL client immediately after the transaction commits.

Getting started

The quickest way to try it is with the Docker image:

docker run -d \
  -e POSTGRES_PASSWORD=mysecret \
  -e INFLUXDB_DATABASE=metrics \
  -e INFLUXDB_AUTO_CREATE_TABLE=on \
  -p 5432:5432 \
  -p 8086:8086 \
  -p 8089:8089/udp \
  mkindahl/pg_influxdb:latest

There are images for all the versions that PostgreSQL are supporting: Alpine, Bookworm, Bullseye, and Trixie.

The container configures shared_preload_libraries, creates the metrics database, installs the extension, and starts four HTTP workers and four UDP workers. You can connect to PostgreSQL on port 5432 normally.

By setting INFLUXDB_AUTO_CREATE_TABLE=on the extension will automatically create tables as metrics are received. The tables will be in a standard format with _tags and _fields being JSONB columns containing the tags and fields, but you can create your own columns to capture the fields or tags. For example, here is one for Telegraf’s cpu input:

CREATE TABLE metrics.cpu (
    _time       timestamptz,
    host        text,
    cpu         text,
    usage_idle  float8,
    _tags       jsonb,
    _fields     jsonb
);

Named columns (host, cpu, usage_idle) are populated directly from the matching tag and field names in the line protocol. Tags and fields that have no corresponding column fall through to _tags and _fields respectively as JSONB. This lets you define a precise schema for the measurements you care about and still capture everything else without losing data. You can even add indexes as usual to get good performance on your queries.

Note

If you do not create the _tags or _fields columns, the extra unrecognized tags or fields will be dropped, allowing an easy way to ignore some fields or tags.

Now point Telegraf at the extension. The only change from a normal InfluxDB configuration is the URL:

[[outputs.influxdb]]
  urls     = ["http://localhost:8086"]
  database = "metrics"

Telegraf will start writing. After a few seconds:

SELECT _time, host, cpu, usage_idle
  FROM metrics.cpu
 WHERE _time > now() - interval '5 minutes'
 ORDER BY _time DESC
 LIMIT 5;

        _time          |  host  |  cpu  | usage_idle
-----------------------+--------+-------+------------
 2025-04-25 09:43:12   | web-01 | cpu0  |      97.32
 2025-04-25 09:43:12   | web-01 | cpu1  |      94.87
 2025-04-25 09:43:02   | web-01 | cpu0  |      96.90
 2025-04-25 09:43:02   | web-01 | cpu1  |      93.41
 2025-04-25 09:42:52   | web-01 | cpu0  |      98.01

From here you can join against application tables, write views, run window functions, or use any PostgreSQL tools you fancy.

What is currently supported

HTTP /write: accepts POST requests with line protocol in the body. Supports gzip-compressed bodies, the precision query parameter, and the db parameter to select the target schema.

UDP: accepts line protocol datagrams on port 8089. Multiple workers listen on the same port using SO_REUSEPORT so the kernel distributes load across cores without any userspace coordination.

Basic authentication: set influxdb.http_auth = on and credentials are checked against PostgreSQL roles. Telegraf uses Authorization: Basic headers, which is exactly what the extension accepts.

TLS/HTTPS: set influxdb.https = on and the HTTP listener uses the same certificate and key files as PostgreSQL itself (ssl_cert_file and ssl_key_file). No separate certificate management.

Docker images: published for PostgreSQL 17 and 18 on Debian Bookworm, Debian Trixie, and Alpine. A one-line docker run is enough to have a working endpoint.

What is currently not supported

The focus for the extension is to be a Telegraf write endpoint. It is not a full InfluxDB replacement and it does not even try to be a replacement for InfluxDB. It can be used as an intermediate step if you want to migrate from InfluxDB to PostgreSQL.

Limitations on the /query endpoint

Because of this, the /query endpoint only understands very limited set of queries (only CREATE DATABASE, to be precise). All other InfluxQL queries returns an error.

For example, Grafana’s InfluxDB data source sends metadata queries on startup and will not work against this extension.

HTTP limitations

Every HTTP response includes Connection: close and the socket is shut down after a single request. Telegraf reconnects for each batch, which adds a small amount of latency. This is the most significant operational difference from a real InfluxDB instance at high write rates and is something that I might look at soon to not require the connection to be closed after each insert.

No IPv6 support

Both listeners are IPv4 only. IPv6 is not currently supported.

No token authentication

Token authentication (Authorization: Token ...) is not supported. Only HTTP Basic Auth and the u=/p= query parameters are accepted.

When to use it

The pg_influxdb extension is a good fit when:

• You are already running InfluxDB but want to experiment with using PostgreSQL as a ingest endpoint instead.
• You are already running PostgreSQL and want to avoid adding InfluxDB to the stack but want to use Telegraf to ingest data.
• Your metrics volume is within what PostgreSQL handles comfortably (tens of thousands of inserts per second on modest hardware).
• You want to join metrics against application data, or use standard SQL tooling for analysis and alerting.
• You use Telegraf for collection and but want to use a PostgreSQL-compatible tool for querying.

It is not the right fit when:

• You need Grafana’s InfluxDB data source to work out of the box.
• You rely on InfluxDB features, such as retention policies or continuous queries.
• You are writing at the scale where InfluxDB’s TSM storage engine provides a meaningful advantage.
• You are fine with generating SQL queries and insert directly into the database through a SQL connection.

Future work

The immediate priorities are to eliminate the above limitations, as far as is reasonable. Implement support for keep-alive HTTP connections to reduce per-batch overhead, IPv6 support, and maybe add some SELECT support to make Grafana’s InfluxDB data source usable (if there is interest in this). Also token authentication is something that might be added.

Try it and give feedback

The Docker image is on DockerHub at mkindahl/pg_influxdb. The source is on GitHub. Feel free to propose features, suggest improvements to existing features, or file bug reports if you discover something that does not work as expected.