Variable length array element encoding… - Mailing list pgsql-hackers

[ Not subscribed, please keep me in the CC list ]

Is there a standard idiom for encoding small variable length data in an array? I wrote the varint extension[1] that
encodesdata using a variable width encoding scheme[2] for signed and unsigned integers[3]. Right now the extension is
mostlyof use in skinny tables that have at least 4-5 columns, all of which are of INT or INT8. If you have only 5
columnsof INT8, you can save ~50% of your table space. 

But, to get larger savings, it's required to bypass the tuple overhead and aggregating data in to an array (i.e.
aggregateall time series data for a 5min window of time in to a single varuint[]). 

The problem with that being, each varint takes 8 bytes in an array because of padding and alignment. Is there a way to
preventthat, or, more realistically, are there standard ways of encoding this data in to a BYTEA and then manually
scanningand unpacking the data? Random access in to the array isn't a concern. I was thinking about adding a BYTEA to
varint[]cast, but am fishing for a better idea. 

Any hints or thoughts? Thanks in advance. -sc


[1] https://github.com/sean-/postgresql-varint

[2] SELECT varint64,  pg_column_size(varint64) FROM varint64_table ORDER BY varint64 ASC;
       varint64       |  pg_column_size
----------------------+-----------------
 -4611686018427387905 |              11
 -4611686018427387904 |              10
 -36028797018963969   |              10
 -36028797018963968   |               9
 -281474976710657     |               9
 -281474976710656     |               8
 -2199023255553       |               8
 -2199023255552       |               7
 -17179869185         |               7
 -17179869184         |               6
 -134217729           |               6
 -134217728           |               5
 -1048577             |               5
 -1048576             |               4
 -8193                |               4
 -8192                |               3
 -65                  |               3
 -64                  |               2
 -1                   |               2
 0                    |               2
 1                    |               2
 63                   |               2
 64                   |               3
 8191                 |               3
 8192                 |               4
 1048575              |               4
 1048576              |               5
 134217727            |               5
 134217728            |               6
 17179869183          |               6
 17179869184          |               7
 2199023255551        |               7
 2199023255552        |               8
 281474976710655      |               8
 281474976710656      |               9
 36028797018963967    |               9
 36028797018963968    |              10
 4611686018427387903  |              10
 4611686018427387904  |              11
(39 rows)

SELECT varuint64,  pg_column_size(varint64) FROM varuint64_table ORDER BY varint64 ASC;
      varuint64      |  pg_column_size
---------------------+-----------------
 0                   |               2
 127                 |               2
 128                 |               3
 16383               |               3
 16384               |               4
 2097151             |               4
 2097152             |               5
 268435455           |               5
 268435456           |               6
 34359738367         |               6
 34359738368         |               7
 4398046511103       |               7
 4398046511104       |               8
 562949953421311     |               8
 562949953421312     |               9
 72057594037927935   |               9
 72057594037927936   |              10
 9223372036854775807 |              10

[3] I know the unsigned int only goes up to 2^^63 atm, it will go to 2^^64 once I get around to setting up a test
methodology.Using INT8 internally was too convenient at the time. 

--
Sean Chittenden
sean@chittenden.org