On 27 February 2018 at 18:03, Sam Saffron <sam.saffron@gmail.com> wrote:
> 1. Where is all my missing space, is this in page alignment stuff and
> per-page overhead?
Yes, tuples are MAXALIGNed when copied onto the page. That appears to
be 8 bytes on your system. You're just 2 bytes over the 8-byte
boundary. You'll notice the table becoming more compact if you somehow
could do away with the dsecs column.
> 2. Is there any other schemes I can look at for storing this data to
> have a more efficient yet easily queryable / updateable table.
Hard to say without learning about the use case. Some sort of
de-normalisation may help to allow fewer tuples, e.g storing one
column in an array, but may be more painful from a design point of
view. It may also cause pain from a dead-tuple point of view if you
have to UPDATE large tuples frequently.
> Keep in mind these tables get huge and in many of our cases will span
> 10-20GB just to store this information.
I guess it's all relative, but that does not seem that large. Is this
causing some sort of performance problems you're trying to solve? or
is this completely down to disk space consumption? If it's down to
performance then you might be able to solve that problem with an
index.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services