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

Hi,

2017-12-10 2:53 GMT+01:00 John R Pierce <pierce(at)hogranch(dot)com>:
> I believe I would use boolean, not bit.

Agreed.

2017-12-10 10:01 GMT+01:00 Thomas Kellerer <spam_eater(at)gmx(dot)net>:
> Did you try to use a (single) hstore or jsonb column instead where the
> attribute name is the key?

Thought about that and I'm using hstore extensively with OpenStreetMap
data in my PostGIS Terminal.

But it "hurts" really with key-values of type text (with numeric
optimization)...

1. to see boolean values stored in text values
2. to see tables m1 and m2 which have a relatively unchanging schema
(since sensors are HW)
3. and knowing that and m1 and m2 have a large common set of sensors
(i.e. common set of attributes).

:Stefan

2017-12-10 10:01 GMT+01:00 Thomas Kellerer <spam_eater(at)gmx(dot)net>:
>> 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].
>
>
> Did you try to use a (single) hstore or jsonb column instead where the
> attribute name is the key?
>
> You'd lose some type safety, but those data types can be compressed, so that
> might be worth the trade off
>
> Thomas
>
>
>