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