21–26 of 26 posts
For another alternative: I added some monitoring, by using file_fdw. This is a standard pg extension that presents files or program stdout as tables. So I file_fdw'd some files in /proc and some system utilities.
could achieve something similar a bit different way - by using Linux nftables ( new iptables ) netfilter interface. I have setup like this for measuring used traffic by certain daemons running under specific user: table inet raw { ... counter postgre_tcp_traffic_out { packets 0 bytes 0 } ... chain output { ... meta l4proto tcp skuid postgres counter name "postgre_tcp_traffic_out" notrack ... } } and then view it like…
Systemd seems to report.network traffic stats for managed units. It spawns cgroups for units which among other things track network traffic.
Nearly all usecases for this seem to be recreating a time series database... Postgres isn't a great TSDB - the indexes don't understand columns that will only have updates at one end, it doesn't do column compression, range queries are expensive, etc. Perhaps it's time to just set up a time series database... Like influxDB.
Nearly all usecases for this seem to be recreating a time series database... Postgres isn't a great TSDB - the indexes don't understand columns that will only have updates at one end, it doesn't do column compression, range queries are expensive, etc. Perhaps it's time to just set up a time series database... Like influxDB.
or clickhouse!
Nearly all usecases for this seem to be recreating a time series database... Postgres isn't a great TSDB - the indexes don't understand columns that will only have updates at one end, it doesn't do column compression, range queries are expensive, etc. Perhaps it's time to just set up a time series database... Like influxDB.
Or just use TimescaleDB which is competitive on most axes but already PG.
If anyone's curious about TimescaleDB, it's packaged as an extension to Postgres, optimizing for performance, storage, and analysis of time series data. Implementing columnar compression algorithms is a big part of the secret sauce that makes TimescaleDB a popular choice with Postgres and SQL developers. You can read more about that on the Timescale blog (I'm Timescale's community manager btw). https://www.timescale.com/blog/search/?query=compression
If anyone's curious, the youtube channel may be a good place to start, especially this playlist https://www.youtube.com/playlist?list=PLsceB9ac9MHTtM1XWONMR...
For another alternative: I added some monitoring, by using file_fdw. This is a standard pg extension that presents files or program stdout as tables. So I file_fdw'd some files in /proc and some system utilities.
I would like to see your magic please.
Setup like this:
CREATE EXTENSION file_fdw;
CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE IF NOT EXISTS proc_loadavg(
load1 decimal,
load5 decimal,
load15 decimal,
threads_runnable_total text,
most_recent_pid integerFor harder things, read the file as lines instead of fields, then create a view with some regexes to split it in fields:
CREATE FOREIGN TABLE IF NOT EXISTS proc_meminfo(
line text
) SERVER pglog OPTIONS ( filename '/proc/meminfo', header 'false',delimiter '$' );
CREATE OR REPLACE VIEW proc_meminfo_interpreted AS
WITH arr AS (SELECT regexp_split_to_array(line,':| +') a FROM proc_meminfo)
SELECT a[1] as name,a[3] as value FROM arr;