4 replaced http://stackoverflow.com/ with https://stackoverflow.com/
source | link

If you only have a few variables I would consider keeping separate boolean columns.

  • Indexing is easy. In particular, indexes on expressions are easy.
  • Conditions for queries and partial indexing are easy to write and read and meaningful.
  • A boolean column occupies 1 byte. For only a few variables this occupies the least space.
  • Unlike the other options boolean columns allow NULL values for individual bits if you should need that. You can always define columns NOT NULL if you don't.

Optimizing storage

If you have more than a hand full variables but less than 33, an integer column may serve you best. (Or a bigint for up to 64 variables.)

  • Occupies 4 bytes on disk.
  • Very fast indexing for exact matches (= operator).
  • Handling individual values may be slower / less convenient than with bit string or boolean.

With even more variables, or if you want to manipulate the values a lot, or if you don't have huge tables and disk space / RAM is no issue, or if you are not sure what to pick, I would consider bit(n) or bit varying(n).

Examples

For just 3 bits of information, individual boolean columns get by with 3 bytes, an integer needs 4 bytes and a bit string 6 bytes (5 + 1).

For 32 bits of information, an integer still needs 4 bytes, a bit string occupies 9 bytes for the same (5 + 4) and boolean columns occupy 32 bytes.

Further reading

If you only have a few variables I would consider keeping separate boolean columns.

  • Indexing is easy. In particular, indexes on expressions are easy.
  • Conditions for queries and partial indexing are easy to write and read and meaningful.
  • A boolean column occupies 1 byte. For only a few variables this occupies the least space.
  • Unlike the other options boolean columns allow NULL values for individual bits if you should need that. You can always define columns NOT NULL if you don't.

Optimizing storage

If you have more than a hand full variables but less than 33, an integer column may serve you best. (Or a bigint for up to 64 variables.)

  • Occupies 4 bytes on disk.
  • Very fast indexing for exact matches (= operator).
  • Handling individual values may be slower / less convenient than with bit string or boolean.

With even more variables, or if you want to manipulate the values a lot, or if you don't have huge tables and disk space / RAM is no issue, or if you are not sure what to pick, I would consider bit(n) or bit varying(n).

Examples

For just 3 bits of information, individual boolean columns get by with 3 bytes, an integer needs 4 bytes and a bit string 6 bytes (5 + 1).

For 32 bits of information, an integer still needs 4 bytes, a bit string occupies 9 bytes for the same (5 + 4) and boolean columns occupy 32 bytes.

Further reading

If you only have a few variables I would consider keeping separate boolean columns.

  • Indexing is easy. In particular, indexes on expressions are easy.
  • Conditions for queries and partial indexing are easy to write and read and meaningful.
  • A boolean column occupies 1 byte. For only a few variables this occupies the least space.
  • Unlike the other options boolean columns allow NULL values for individual bits if you should need that. You can always define columns NOT NULL if you don't.

Optimizing storage

If you have more than a hand full variables but less than 33, an integer column may serve you best. (Or a bigint for up to 64 variables.)

  • Occupies 4 bytes on disk.
  • Very fast indexing for exact matches (= operator).
  • Handling individual values may be slower / less convenient than with bit string or boolean.

With even more variables, or if you want to manipulate the values a lot, or if you don't have huge tables and disk space / RAM is no issue, or if you are not sure what to pick, I would consider bit(n) or bit varying(n).

Examples

For just 3 bits of information, individual boolean columns get by with 3 bytes, an integer needs 4 bytes and a bit string 6 bytes (5 + 1).

For 32 bits of information, an integer still needs 4 bytes, a bit string occupies 9 bytes for the same (5 + 4) and boolean columns occupy 32 bytes.

Further reading

3 Update links
source | link

As encouraged by @Jack I copied my answer from SO and adapted it somewhat.

If you only have a few variables I would consider keeping separate boolean columns.

  • Indexing is easy. In particular, indexes on expressions are easy.
  • Conditions for queries and partial indexing are easy to write and read and meaningful.
  • A boolean column occupies 1 byte. For only a few variables this occupies the least space.
  • Unlike the other options boolean columns allow NULL values for individual bits if you should need that. You can always define columns NOT NULL if you don't.

Optimizing storage

If you have more than a hand full variables but less than 33, an integer column may serve you best. (Or a bigint for up to 64 variables.)

  • Occupies 4 bytes on discdisk.
  • Very fast indexing for exact matches (= operator).
  • Handling individual values may be slower / less convenient than with bit string or boolean.

With even more variables, or if you want to manipulate the values a lot, or if you don't have huge tables and discdisk space / RAM is no issue, or if you are not sure what to pick, I would consider bit(n) or bit varying(n).

Examples

For just 3 bits of information, individual boolean columns get by with 3 bytes, an integer needs 4 bytes and a bit string 6 bytes (5 + 1).

For 32 bits of information, an integer still needs 4 bytes, a bit string occupies 9 bytes for the same (5 + 4) and boolean columns occupy 32 bytes.

MoreFurther reading

To optimize disc space further you need to understand the storage mechanisms of PostgreSQL, especially data alignment. More in this related answer.

This answer on how to transform the types boolean, bit(n) and integer may be of help, too.

As encouraged by @Jack I copied my answer from SO and adapted it somewhat.

If you only have a few variables I would consider keeping separate boolean columns.

  • Indexing is easy. In particular, indexes on expressions are easy.
  • Conditions for queries and partial indexing are easy to write and read and meaningful.
  • A boolean column occupies 1 byte. For only a few variables this occupies the least space.
  • Unlike the other options boolean columns allow NULL values for individual bits if you should need that. You can always define columns NOT NULL if you don't.

Optimizing storage

If you have more than a hand full variables but less than 33, an integer column may serve you best. (Or a bigint for up to 64 variables.)

  • Occupies 4 bytes on disc.
  • Very fast indexing for exact matches (= operator).
  • Handling individual values may be slower / less convenient than with bit string or boolean.

With even more variables, or if you want to manipulate the values a lot, or if you don't have huge tables and disc space / RAM is no issue, or if you are not sure what to pick, I would consider bit(n) or bit varying(n).

Examples

For just 3 bits of information, individual boolean columns get by with 3 bytes, an integer needs 4 bytes and a bit string 6 bytes (5 + 1).

For 32 bits of information, an integer still needs 4 bytes, a bit string occupies 9 bytes for the same (5 + 4) and boolean columns occupy 32 bytes.

More

To optimize disc space further you need to understand the storage mechanisms of PostgreSQL, especially data alignment. More in this related answer.

This answer on how to transform the types boolean, bit(n) and integer may be of help, too.

If you only have a few variables I would consider keeping separate boolean columns.

  • Indexing is easy. In particular, indexes on expressions are easy.
  • Conditions for queries and partial indexing are easy to write and read and meaningful.
  • A boolean column occupies 1 byte. For only a few variables this occupies the least space.
  • Unlike the other options boolean columns allow NULL values for individual bits if you should need that. You can always define columns NOT NULL if you don't.

Optimizing storage

If you have more than a hand full variables but less than 33, an integer column may serve you best. (Or a bigint for up to 64 variables.)

  • Occupies 4 bytes on disk.
  • Very fast indexing for exact matches (= operator).
  • Handling individual values may be slower / less convenient than with bit string or boolean.

With even more variables, or if you want to manipulate the values a lot, or if you don't have huge tables and disk space / RAM is no issue, or if you are not sure what to pick, I would consider bit(n) or bit varying(n).

Examples

For just 3 bits of information, individual boolean columns get by with 3 bytes, an integer needs 4 bytes and a bit string 6 bytes (5 + 1).

For 32 bits of information, an integer still needs 4 bytes, a bit string occupies 9 bytes for the same (5 + 4) and boolean columns occupy 32 bytes.

Further reading

2 fix typos, format and clarify
source | link

As encouraged by @Jack I copied my answer from SO and adapted it somewhat.

If you only have a few variables I would consider keeping separate boolean columns.

  • Indexing is easy. In particular, indexes on expressions are easy.
  • Conditions for queries and partial indexing are easy to write and read and meaningful.
  • A boolean column occupies 1 byte. For only a few variables this occupies the least space.
  • Unlike the other options boolean columns allow NULL values for individual bits if you should need that. You can always define columns NOT NULL if you don't.

Optimizing storage

Optimizing storage

If you have more than a hadhand full variables but less than 33, an integer column may serve you best. (Or a bigint for up to 64 variables.)

  • Occupies 4 bytes on disc.
  • Very fast indexing for exact matches (  = operator).
  • Handling individual values may be slower / less convenient than with bit stringsbit string or boolean.

With even more variables, or if you want to manipulate the values a lot, or if you don't have huge tables and disc space / RAM is no issue, or if you are not sure what to pick, I would recommendconsider bit(n) or bit varying(n).

Examples

For just 3 bits of information, individual boolean columns get by with 3 bytes, an integer needs 4 bytes and a bit string 6 bytes (5 + 1).

For 32 bits of information, an integer still needs 4 bytes, a bit string occupies 9 bytes for the same (5 + 4) and boolean columns occupy 32 bytes.

More

To optimize disc space further you need to understand the storage mechanisms orof PostgreSQL, especially data alignment. More in this related answer.

This answer on how to transform the types boolean, bit(n) and integer may be of help, too.

As encouraged by @Jack I copied my answer from SO and adapted it somewhat.

If you only have a few variables I would consider keeping separate boolean columns.

  • Indexing is easy. In particular, indexes on expressions are easy.
  • Conditions for queries and partial indexing are easy to write and read and meaningful.
  • A boolean column occupies 1 byte. For only a few variables this occupies the least space.
  • Unlike the other options boolean columns allow NULL values for individual bits if you should need that. You can always define columns NOT NULL if you don't.

Optimizing storage

If you have more than a had full variables but less than 33, an integer column may serve you best. (Or a bigint for up to 64 variables.)

  • Occupies 4 bytes on disc.
  • Very fast indexing for exact matches (  = operator).
  • Handling individual values may be slower / less convenient than with bit strings.

With even more variables, or if you want to manipulate the values a lot, or if you don't have huge tables and disc space / RAM is no issue, or if you are not sure what to pick, I would recommend bit(n) or bit varying(n).

For just 3 bits of information, individual boolean columns get by with 3 bytes, an integer needs 4 bytes and a bit string 6 bytes (5 + 1).

For 32 bits of information, an integer still needs 4 bytes, a bit string occupies 9 bytes for the same (5 + 4) and boolean columns occupy 32 bytes.

To optimize disc space further you need to understand the storage mechanisms or PostgreSQL, especially data alignment. More in this related answer.

This answer on how to transform the types boolean, bit(n) and integer may be of help, too.

As encouraged by @Jack I copied my answer from SO and adapted it somewhat.

If you only have a few variables I would consider keeping separate boolean columns.

  • Indexing is easy. In particular, indexes on expressions are easy.
  • Conditions for queries and partial indexing are easy to write and read and meaningful.
  • A boolean column occupies 1 byte. For only a few variables this occupies the least space.
  • Unlike the other options boolean columns allow NULL values for individual bits if you should need that. You can always define columns NOT NULL if you don't.

Optimizing storage

If you have more than a hand full variables but less than 33, an integer column may serve you best. (Or a bigint for up to 64 variables.)

  • Occupies 4 bytes on disc.
  • Very fast indexing for exact matches (= operator).
  • Handling individual values may be slower / less convenient than with bit string or boolean.

With even more variables, or if you want to manipulate the values a lot, or if you don't have huge tables and disc space / RAM is no issue, or if you are not sure what to pick, I would consider bit(n) or bit varying(n).

Examples

For just 3 bits of information, individual boolean columns get by with 3 bytes, an integer needs 4 bytes and a bit string 6 bytes (5 + 1).

For 32 bits of information, an integer still needs 4 bytes, a bit string occupies 9 bytes for the same (5 + 4) and boolean columns occupy 32 bytes.

More

To optimize disc space further you need to understand the storage mechanisms of PostgreSQL, especially data alignment. More in this related answer.

This answer on how to transform the types boolean, bit(n) and integer may be of help, too.

1
source | link