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

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