Re: PG Schema to be used as log and monitoring store - Mailing list pgsql-general

From Steven Lembark
Subject Re: PG Schema to be used as log and monitoring store
Date
Msg-id 20171209160147.36322c55@wrkhors.com
Whole thread Raw
In response to PG Schema to be used as log and monitoring store  (Stefan Keller <sfkeller@gmail.com>)
Responses Re: PG Schema to be used as log and monitoring store
List pgsql-general
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


pgsql-general by date:

Previous
From: John R Pierce
Date:
Subject: Re: Windows XP to Win 10 migration issue
Next
From: Stefan Keller
Date:
Subject: Re: PG Schema to be used as log and monitoring store