Re: PG Schema to be used as log and monitoring store

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@wrkhors.com>:
> 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@wrkhors.com>:
> On Sat, 9 Dec 2017 20:22:02 +0100
> Stefan Keller <sfkeller@gmail.com> 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@wrkhors.com                                    +1 888 359 3508
>