In some cases, metadata and observability data, such as metrics and logs, sits in different database because of different nature of data type, data amount and query patterns. However, this makes it difficult to correlate metadata with those live observability data. Imagine a scenario with large-scale compute nodes or IoT devices, you may want to:

1. Filter out those nodes belongs a particular orgnanization from metadata stored in a Postgres.
2. Find out average load during the last week of these nodes collected as Prometheus metrics.

This was difficult because there are two types of databases and query languages with no direct bridge between.

The Solution: Postgres Foreign Data Wrapper (FDW)

Thanks to Postgres' foreign data wrapper extension, it is possible to connect a remote Postgres or Postgres-compatible database into the server, and run queries directly on data from both servers.

Two weeks ago, a user requested the ability to connect GreptimeDB as a foreign table provider to Postgres. GreptimeDB has built-in Postgres wire protocol support on top of pgwire. So we just added a few statements support to make it possible to connect GreptimeDB as a remote server. On the other side, GreptimeDB can be configured as Prometheus and Loki remote write destinations and make their data queryable in SQL.

(Figure 1: Limitations of Static Rate Limiting)

(Figure 1: The Postgres Foreign Data Wrapper of Solution)

Setting Up GreptimeDB as a Postgres FDW

1. To connect GreptimeDB as Postgres FDW, first you will need to enable postgres_fdw extension:

CREATE EXTENSION postgres_fdw;

2. Add GreptimeDB as remote server, and configure user credentials for it.

CREATE SERVER greptimedb
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'greptimedb_host', dbname 'public', port '4003');

CREATE USER MAPPING FOR postgres
SERVER greptimedb
OPTIONS (user 'greptime', password '...');

3. Use \des in psql to see the remote servers.

4. In order to make it possible for postgres to push down filters and sort options, it requires you to define a foreign table that matches the table structure of your GreptimeDB table:

CREATE FOREIGN TABLE ft_grpc_latencies (
  ts TIMESTAMP,
  host VARCHAR,
  method_name VARCHAR,
  latency DOUBLE precision
)
SERVER greptimedb
OPTIONS (table_name 'grpc_latencies');

In this example, we defined this ft_grpc_latencies to map to grpc_latencies in GreptimeDB. Note that you will need to translate GreptimeDB data types to their Postgres equivalent. In GreptimeDB 0.11.2, we build a SQL statement SHOW CREATE TABLE <table_name> FOR postgres_foreign_table to dump the statement automatically.

Querying Data from Postgres and GreptimeDB

Once the foreign table is set up, you can query data from GreptimeDB just like any other Postgres table:

1. Basic query with filters:

SELECT * FROM ft_grpc_latencies WHERE host = 'host1' ORDER BY ts DESC LIMIT 100;

2. Or use some functions that are supported by both GreptimeDB and Postgres for data aggregation:

SELECT
  date_trunc('MONTH', ts) as t,
  host,
  avg(latency) ,
  count(latency)
FROM ft_grpc_latencies GROUP BY host, t;

3. It's also possible to join data from Postgres with the foreign table:

SELECT * FROM ft_grpc_latencies f JOIN local_host_table l ON l.host = f.host WHERE l.host in ('host1', 'host2') ORDER BY f.ts DESC LIMIT 100;

Pros and Cons of Using GreptimeDB as a Foreign Data Wrapper

Pros:

• Join queries can be executed with data stored in Postgres.
• Postgres pushes down filters and sort operations to improve performance.
• Leverage Postgres' user management, permissions, and infrastructure.

Cons:

• Only SQL queries supported by Postgres can be executed; advanced features of GreptimeDB, such as range queries and TQL (Time Query Language), are not supported when querying foreign tables.
• Additional latency due to the extra "hop" when fetching data.

Conclusion

Using GreptimeDB as a Foreign Data Wrapper in Postgres offers a powerful way to integrate time-series and observability data with your existing relational databases. However, it comes with limitations in terms of query capabilities. This setup is ideal for users who want to query Prometheus and log data stored in GreptimeDB from within their Postgres environment, while benefiting from Postgres' robust query optimization and infrastructure. We have created an easy-to-use demo for this integration. Check out our demo repository to try it yourself, and feel free to share any thoughts or ideas regarding GreptimeDB's Postgres integration.

---

About Greptime

Greptime offers industry-leading time series database products and solutions to empower IoT and Observability scenarios, enabling enterprises to uncover valuable insights from their data with less time, complexity, and cost.

GreptimeDB is an open-source, high-performance time-series database offering unified storage and analysis for metrics, logs, and events. Try it out instantly with GreptimeCloud, a fully-managed DBaaS solution—no deployment needed!

The Edge-Cloud Integrated Solution combines multimodal edge databases with cloud-based GreptimeDB to optimize IoT edge scenarios, cutting costs while boosting data performance.

Star us on GitHub or join GreptimeDB Community on Slack to get connected.