1

I need to add new field to a table. The field will only store a number that ranges from 0 to maybe 30, so (in theory) a single byte field should be fine.

The smallest integer data-type listed in the documentation is SMALLINT which requires 2 bytes.

The table I want to add the field to is partitioned and currently holds approximately 3 billion rows. So adding a 2 byte field to each row should require 6 billion bytes, or 6 GB.

For reasons I still haven't fathomed, (see this question) I'm running out of space when adding a SMALLINT field to a large table.

QUESTION - is there a single-byte datatype I can use to store a number with only a very small range of values?

2
  • Looking at your linked question, it’s unlikely you’ll benefit from reducing the size of the column - the simple act of adding a column means a full rebuild of the table (indexes and all) so that’s going to be the majority of your space requirement. Upgrading your version should give you a better code path here so you won’t require so much spare space Jan 14, 2023 at 18:45
  • Note that Postgres 10 is no longer supported you should plan an upgrade as soon as possible. (And as mentioned in the other question: with a supported version it's highly likely you wouldn't have run in this issue)
    – user1822
    Jan 15, 2023 at 12:11

1 Answer 1

1

Well, you could use the special "char" type (note the double quotes and no length), then cast your values on input and output.

Tell us though, what percentage of the total table size are the 3 GB you wish to save, and why is it important enough to warrant the extra effort?

2
  • please see linked question. Nearly 900GB freespace is NOT enough to add a 4 byte SMALLINT field to the table. Jan 14, 2023 at 15:35
  • Well, there's now an answer to that question that tells you why you're running out of space (and it's not because of 6 GB).
    – mustaccio
    Jan 14, 2023 at 18:49

Not the answer you're looking for? Browse other questions tagged or ask your own question.