Hey all,
I am very new to CrateDB. We have previously been using Promscale and TimescaleDB for remote storage of Prometheus metrics, but Promscale is now deprecated and unsupported, meaning we need to look at other options.
CrateDB seems really nice, and well suited for our needs as we maintain our “non time-series” relational data in Postgres anyway, so the support for Postgres protocols is very appealing.
I have managed to get Crate ingesting metrics from Prometheus without issue, and can manually use psycopg2 to connector, get cursor, and query as you would expect. The multi-dimensional labels are stored as a simple key/value object within CrateDB.
A fairly major limitation is that our reporting engine is limited in as much as we cannot do cross-database joins - e.g. our non time-series data to the metric data now stored in CrateDB.
To try and get around this, I have experimented with postgres_fdw and added Crate as a remote server, and actually had success creating a remote table mapping from our standard Postgres to the CrateDB metrics table using jsonb as the field type in Postgres to represent the label object column in Crate. This seems to query just fine in normal Postgres client/connection:
create foreign table doc.recent_metrics (timestamp timestamptz, labels_hash text, labels jsonb, value double precision) server crate;
select * from doc.metrics where timestamp > ‘2023-03-18 21:40’;
…
2023-03-18 21:45:06.031+00 | 8bbaad138daa6a0e | {“app”: “APP1”, “job”: “SNMP_RaritanPX”, “type”: “snmp”, “name”: “scrape_samples_scraped”, “instance”: “snmp_raritan_simulator”, “config_name”: “Raritan SNMP”}
However, things fall down when applying a simple limit clause to a query:
select * from doc.metrics limit 1;
ERROR: line 2:72: mismatched input ‘::’ expecting {, ‘;’}
remote SQL command: SELECT “timestamp”, labels_hash, labels, value FROM doc.metrics LIMIT 1::bigint
It seems as though Postgres is forcibly casting the limit parameter to a type before remotely querying Crate, which clearly CrateDB doesn’t like.
This seems like such a minor issue in the scheme of things - I wondered if anybody else had experienced anything like this and could advise?
Thanks in advance,
Ian