2

If I have a bigint column and in one row store 1 and in another store 999999999999, will these take up different amounts of space on disk?

Will Postgres have an easier time doing queries and calculations with the smaller data?

The motivation for my question is that I have a lot of data in this form: 1000000306429071. I'm wondering if it will be valuable to strip the 1 and leading 0s.

0
4

If I have a bigint column and in one row store 1 and in another store 999999999999, will these take up different amounts of space on disk?

Bigint is always 8 bytes regardless of what the decimal representation is

1            | 000000000000000000000000000000000000000000000000000000000000‌​0001
999999999999 | 000000000000000000000000111010001101010010100101000011111111‌​1111

Will Postgres have an easier time doing queries and calculations with the smaller data?

Yes. int4 takes half the amount of space as int8. That means at most you'll save 4 bytes per tuple in the row, and the index will be half the size.

The motivation for my question is that I have a lot of data in this form: 1000000306429071. I'm wondering if it will be valuable to strip the 1 and leading 0s.

That sounds like a wise idea to me. The max value for an int4, is 2147483647, putting them next to each other.

2147483647
306429071

You can see that you're not even close to exhausting that range. You're only 14% there. Do you ever expect to need more than 2147483647? If so, you definitely want to stay with int8.

You can return the value with..

1e15::bigint + 306429071
7
  • by "the smaller data" i meant the 1 vs 999999999999 in the same bigint column Jun 19 '17 at 21:17
  • And what I mean by always 8 bytes is regardless of whether it's storing 1 as 0000000000000000000000000000000000000000000000000000000000000001, or 999999999999 as 0000000000000000000000001110100011010100101001010000111111111111 Jun 19 '17 at 21:26
  • actually I knew that party -- I'm referring to when you said "int4 takes half the amount of space as int8" -- but i'm not comparing those two data types, i'm comparing postgres' in-memory space and time complexity when dealing with small and large int8 data. Jun 19 '17 at 22:26
  • 1
    @JohnBachir That's also what I'm referring to when I say "space": int8 takes half the amount of space as int4. I'm not sure where we're talking past each other. Jun 19 '17 at 22:36
  • 1
    @amacvar Neither one runs faster. One or both integers gets copied into a CPU register and then you call a machine instruction to tell the cpu to copy it. That instruction doesn't care about how long the representation is. Determining that would certainly take more time than copying it. Jun 20 '17 at 21:28
2

As Paulo Scardine already wrote in the comment, bigint has 8 bytes (64 bits) storage size.

The PostgreSQL manual offers an overview about the types.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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