Hi Thanks James and Steven! I hoped somebody will advise me not to do this. I was just bothered with NoSQL databases. Even TimescaleDB made me wonder because it says it scales Postgres [1] for IoT which implies that Postsgres does not scale... 2017-12-09 23:01 GMT+01:00 Steven Lembark <lembark@xxxxxxxxxxx>: > In general this is a bad idea *unless* you have benchmarked the > database and found that the amount of space saved really does make > some difference. I actually made some tests on my own (using generate_series) and did not find any disk space or performance issues yet. I've also found this paper from 2012 about "Sensor Data Storage Performance: SQL or NoSQL, Physical or Virtual" [2] which confirms my observations. Now, you have to know that there are about 100 attributes for the machines/tables - not only 40 - so I initially thought, it's easier to setup the schema using bit(50) and float8[50]. Below I re-modeled it to a relational schema as you suggested and also tried to utilize the INHERITS feature. Does that look better? :Stefan [1] https://blog.timescale.com/choose-postgresql-for-iot-19688efc60ca [2] https://www.ceid.upatras.gr/webpages/faculty/vasilis/Courses/SpatialTemporalDM/Papers/SQLorNoSQL2012.pdf /* Pure relational logging and monitoring schema */ create table m_meta ( id int primary key, name text ); drop table if exists m cascade; create table m ( id bigint primary key, gid int references m_meta not null, created timestamp, b1 bit, b2 bit, b3 bit, -- b2 .. b20 f1 float8, f2 float8, f3 float8 --f4 ... f20 ); create table m1 ( b21 bit, -- b22 .. b50 bit, f21 float8, --f4 ... f20 float8, primary key (id), foreign key (gid) references m_meta ) inherits (m); --create table m1 ( ... ) inherits (m); /* end */ 2017-12-09 23:01 GMT+01:00 Steven Lembark <lembark@xxxxxxxxxxx>: > On Sat, 9 Dec 2017 20:22:02 +0100 > Stefan Keller <sfkeller@xxxxxxxxx> wrote: > >> create table m1 ( >> id bigint, >> created timestamp, >> b20 bit(20) default b'00000000000000000000', >> farr20 float8[20] >> ); > > In general this is a bad idea *unless* you have benchmarked the > database and found that the amount of space saved really does make > some difference. Using the packed format makes most SQL a lot harder > to write and makes indexing impossible (or at least messy and rather > error prone). This also makes adding add'l fields harder. > > If you were really intent on doing this I'd add a few million recods > with both formats on a database tuned to handle the load and see if > the packed bits really do make a difference. My guess is that you > won't see all that much difference in storage and the query speed > with effective indexing is going to be decent. > > Using this database might be a lot simpler with a few that > breaks the sub-fields out, or which has indexes on the sub > -fields within the packed data. > > > -- > Steven Lembark 1505 National Ave > Workhorse Computing Rockford, IL 61103 > lembark@xxxxxxxxxxx +1 888 359 3508 >
