{"action":"created","issue":{"url":"https://api.github.com/repos/firehol/netdata/issues/2423","repository_url":"https://api.github.com/repos/firehol/netdata","labels_url":"https://api.github.com/repos/firehol/netdata/issues/2423/labels{/name}","comments_url":"https://api.github.com/repos/firehol/netdata/issues/2423/comments","events_url":"https://api.github.com/repos/firehol/netdata/issues/2423/events","html_url":"https://github.com/firehol/netdata/issues/2423","id":240871933,"number":2423,"title":"[Feature]TimescaleDB support","user":{"login":"elfgoh","id":491688,"avatar_url":"https://avatars2.githubusercontent.com/u/491688?v=4","gravatar_id":"","url":"https://api.github.com/users/elfgoh","html_url":"https://github.com/elfgoh","followers_url":"https://api.github.com/users/elfgoh/followers","following_url":"https://api.github.com/users/elfgoh/following{/other_user}","gists_url":"https://api.github.com/users/elfgoh/gists{/gist_id}","starred_url":"https://api.github.com/users/elfgoh/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/elfgoh/subscriptions","organizations_url":"https://api.github.com/users/elfgoh/orgs","repos_url":"https://api.github.com/users/elfgoh/repos","events_url":"https://api.github.com/users/elfgoh/events{/privacy}","received_events_url":"https://api.github.com/users/elfgoh/received_events","type":"User","site_admin":false},"labels":[{"id":43793262,"url":"https://api.github.com/repos/firehol/netdata/labels/enhancement","name":"enhancement","color":"84b6eb","default":true}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":4,"created_at":"2017-07-06T07:48:40Z","updated_at":"2018-02-11T21:45:01Z","closed_at":null,"author_association":"NONE","body":"I would like to request support for [TimescaleDB](http://www.timescale.com/index.html) as netdata backend, which is an extension on top of Postgres. Thanks!"},"comment":{"url":"https://api.github.com/repos/firehol/netdata/issues/comments/364791890","html_url":"https://github.com/firehol/netdata/issues/2423#issuecomment-364791890","issue_url":"https://api.github.com/repos/firehol/netdata/issues/2423","id":364791890,"user":{"login":"mahlonsmith","id":3220479,"avatar_url":"https://avatars3.githubusercontent.com/u/3220479?v=4","gravatar_id":"","url":"https://api.github.com/users/mahlonsmith","html_url":"https://github.com/mahlonsmith","followers_url":"https://api.github.com/users/mahlonsmith/followers","following_url":"https://api.github.com/users/mahlonsmith/following{/other_user}","gists_url":"https://api.github.com/users/mahlonsmith/gists{/gist_id}","starred_url":"https://api.github.com/users/mahlonsmith/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mahlonsmith/subscriptions","organizations_url":"https://api.github.com/users/mahlonsmith/orgs","repos_url":"https://api.github.com/users/mahlonsmith/repos","events_url":"https://api.github.com/users/mahlonsmith/events{/privacy}","received_events_url":"https://api.github.com/users/mahlonsmith/received_events","type":"User","site_admin":false},"created_at":"2018-02-11T21:45:01Z","updated_at":"2018-02-11T21:45:01Z","author_association":"CONTRIBUTOR","body":"After toying a bit (and coming away fairly impressed) with Timescale, here's a proposal for a super simple schema layout, that leaves the heavy lifting to PostgreSQL as nature intended:\r\n\r\n```sql\r\nCREATE TABLE netdata (\r\n    time timestamptz default now() not null,\r\n    host text not null,\r\n    metrics jsonb default '{}'::jsonb not null\r\n);\r\n```\r\nInserts then of course just being:\r\n\r\n```sql\r\nINSERT INTO netdata ( host, metrics ) VALUES ( 'hostname', '<<JSON BLOB>>' );\r\n```\r\n\r\nI think there are some advantages to this worth calling out:\r\n\r\n  - Timescale is not necessarily required for storing metrics here, if someone has a lesser storage need, or is already doing their own declarative (or trigger based) partitioning.  Timescale can be added at any point afterwards via:\r\n```sql\r\nALTER TABLE netdata RENAME TO netdata_old;\r\n\r\nCREATE TABLE netdata (LIKE netdata_old INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);\r\n\r\nSELECT create_hypertable( 'netdata', 'time', \r\n    chunk_time_interval => 86400000000,\r\n    partitioning_column => 'host',\r\n    number_partitions => 10\r\n);   # or whatever\r\n\r\nINSERT INTO netdata ( SELECT * FROM netdata_old );\r\nDROP TABLE netdata_old;\r\n```\r\n - Netdata doesn't need to create tables, indexes, nor concern itself with columns per metric - dump it all into a json blob without worrying about schema.  You still benefit from all of the goodies described [here](http://docs.timescale.com/v0.8/introduction/data-model).\r\n - Indexing specific metrics is left entirely to how the consumer wants to use the data, and can be adjusted anytime afterwards based on SELECT usage.  As an example, indexing 1 minute load might look something like this (depending on data structure, I'm just pretending it looks like `/api/v1/allmetrics?format=json`):\r\n```sql\r\nCREATE INDEX load1 on netdata using btree ( ((metrics->'system.load'->'dimensions'->'load1'->>'value')::real) );\r\n```\r\n  There's some more on this in the Timescale docs [here](http://docs.timescale.com/v0.8/using-timescaledb/schema-management#json).\r\n  - After index tuning, more traditional \"column\" layouts can be easily created with SQL views, making longer term trending / graph building with Grafana significantly easier.\r\n\r\nCoupled with the JSON filtering discussed in #3198, I think this setup allows the most flexibility for longer term trending.  (And yeah, the thought of answering questions like \"what were memory stats across the farm during the 500 errors?\" in a single SQL report is really attractive.)\r\n"}}
{"actor":{"display_login":"mahlonsmith"}}