Mfcttrf

How can I download a file from a host I can only SSH to through another host?

Why did my folder names end up like this, and how can I fix this using a script?

How long do you think advanced cybernetic implants would plausibly last?

Thought experiment and possible contradiction between electromagnetism and special relativity

Tex Quotes(UVa 272)

Changing JPEG to RAW to use on Lightroom?

Was the Boeing 2707 design flawed?

Retroactively modifying humans for Earth?

Subsetting single raster from raster list using ArcPy?

How would a low-tech device be able to alert its user?

Can you board the plane when your passport is valid less than 3 months?

Anyone else seeing white rings in the Undead parish?

"There were either twelve sexes or none."

Given current technology, could TV display screens double as video camera sensors?

Number of academics in various EU countries

LINQ for generating all possible permutations

How many birds in the bush?

Cooking Scrambled Eggs

Joining lists with same elements

Gambler coin problem: fair coin and two-headed coin

When, exactly, does the Rogue Scout get to use their Skirmisher ability?

Semantic difference between regular and irregular 'backen'

How were medieval castles built in swamps or marshes without draining them?

Why should a self-financing strategy be previsible?

Disk usage of integer column vs boolean column in Postgres

Bit vs. Boolean columnsPostgresql table with one integer column, sorted index, with duplicate primary keyboolean purpose column char(0) or tinyint(1)is it “bad” to bulk update a boolean column semi-regularly on potentially a large set of records in Postgres?Running out of disk space running MySQL queryPostgres command failed: No space left on deviceDoes it make sense to store a couple of Boolean values as array?Simplified table using more disk space than its original (bigger) versionExtracting 'hot columns' into a separate table

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

1

I saw this question Bit vs. Boolean columns.

I'm asking myself the same for Postgres: does a single digit integer column occupy the same disk space of a boolean one? In big tables (~50 columns x ~50 million rows) which one perform best?
How can I find this out?

postgresql database-design optimization postgresql-performance disk-space

share|improve this question

edited 7 hours ago

Colin 't Hart

6,91388 gold badges2727 silver badges3434 bronze badges

asked 8 hours ago

SotisSotis

622 bronze badges

New contributor

Sotis is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  PostgerSQL User Manual shows disk storage size for each datatype explicitly. Look...
  
  – Akina
  8 hours ago
  

  
  
  
  

add a comment | 

1

I saw this question Bit vs. Boolean columns.

I'm asking myself the same for Postgres: does a single digit integer column occupy the same disk space of a boolean one? In big tables (~50 columns x ~50 million rows) which one perform best?
How can I find this out?

postgresql database-design optimization postgresql-performance disk-space

share|improve this question

edited 7 hours ago

Colin 't Hart

6,91388 gold badges2727 silver badges3434 bronze badges

asked 8 hours ago

SotisSotis

622 bronze badges

New contributor

Sotis is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  PostgerSQL User Manual shows disk storage size for each datatype explicitly. Look...
  
  – Akina
  8 hours ago
  

  
  
  
  

add a comment | 

I saw this question Bit vs. Boolean columns.

I'm asking myself the same for Postgres: does a single digit integer column occupy the same disk space of a boolean one? In big tables (~50 columns x ~50 million rows) which one perform best?
How can I find this out?

postgresql database-design optimization postgresql-performance disk-space

share|improve this question

edited 7 hours ago

Colin 't Hart

6,91388 gold badges2727 silver badges3434 bronze badges

asked 8 hours ago

SotisSotis

622 bronze badges

New contributor

Sotis is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited 7 hours ago

Colin 't Hart

6,91388 gold badges2727 silver badges3434 bronze badges

asked 8 hours ago

SotisSotis

622 bronze badges

New contributor

Sotis is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited 7 hours ago

Colin 't Hart

6,91388 gold badges2727 silver badges3434 bronze badges

asked 8 hours ago

SotisSotis

622 bronze badges

New contributor

Sotis is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

edited 7 hours ago

Colin 't Hart

6,91388 gold badges2727 silver badges3434 bronze badges

edited 7 hours ago

Colin 't Hart

6,91388 gold badges2727 silver badges3434 bronze badges

asked 8 hours ago

SotisSotis

622 bronze badges

New contributor

Sotis is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 8 hours ago

SotisSotis

622 bronze badges

2 Answers
2

active

oldest

votes

3

You can find out the storage size with

SELECT typname, typlen FROM pg_type WHERE typname IN ('bool', 'int4');

 typname | typlen 
---------+--------
 bool | 1
 int4 | 4
(2 rows)

However you need to take alignment into account:

SELECT typname, typlen, typalign FROM pg_type WHERE typname IN ('bool', 'int4');

 typname | typlen | typalign 
---------+--------+----------
 bool | 1 | c
 int4 | 4 | i
(2 rows)

c is “character” (1 byte), while i is “integer”.

If you define a table like this:

CREATE TABLE a (
 b boolean,
 i integer
);

you will get three unused “padding” bytes between the columns, so that the integer can be stored at an address that is divisible by 4.

So the boolean would take up 4 rather than 1 bytes of storage.

If you specify the columns the other way around, the space taken up by each row would only be 5 bytes (but since the next row starts at an integer boundary again, you'd lose 3 bytes between the rows).

So if you want to optimize your table to use as little storage as possible, you also need to take the order od the columns in the table into account.

share|improve this answer

edited 7 hours ago

answered 8 hours ago

Laurenz AlbeLaurenz Albe

2,84133 silver badges1818 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  So if you want to optimize your table to use as little storage as possible, you also need to take the order od the columns in the table into account. So in a case of CREATE TABLE a (i integer, b boolean); we will see 5 bytes (excluding common overhead, of course) per record whereas in shown structure - 8 bytes per record, is it? Tell about this explicitly, please...
  
  – Akina
  8 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, that is precisely true.
  
  – Laurenz Albe
  7 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Won't the row be aligned on 4 byte boundary anyway, so with just 2 columns it doesn't matter what the order is. But if you were to throw in a third column (a SMALLINT of 2 bytes), you could get either 8 bytes (int, smallint, boolean) or 12 bytes (boolean, smallint, int) for the row size?
  
  – Kayaman
  7 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes that is again true. The next row would start at an integer boundary again. I tried to make the answer more precise.
  
  – Laurenz Albe
  7 hours ago
  
  

  
  
  
  

add a comment | 

1

The storage size of each data type is documented in the manual

Both types are fixed-length data types and the their sizes are documented in the respective chapters of the manual

For integer

integer 4 bytes

For boolean

boolean 1 byte

share|improve this answer

answered 8 hours ago

a_horse_with_no_namea_horse_with_no_name

45.9k99 gold badges9090 silver badges122122 bronze badges

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

Sotis is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f246243%2fdisk-usage-of-integer-column-vs-boolean-column-in-postgres%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

3

You can find out the storage size with

SELECT typname, typlen FROM pg_type WHERE typname IN ('bool', 'int4');

 typname | typlen 
---------+--------
 bool | 1
 int4 | 4
(2 rows)

However you need to take alignment into account:

SELECT typname, typlen, typalign FROM pg_type WHERE typname IN ('bool', 'int4');

 typname | typlen | typalign 
---------+--------+----------
 bool | 1 | c
 int4 | 4 | i
(2 rows)

c is “character” (1 byte), while i is “integer”.

If you define a table like this:

CREATE TABLE a (
 b boolean,
 i integer
);

you will get three unused “padding” bytes between the columns, so that the integer can be stored at an address that is divisible by 4.

So the boolean would take up 4 rather than 1 bytes of storage.

If you specify the columns the other way around, the space taken up by each row would only be 5 bytes (but since the next row starts at an integer boundary again, you'd lose 3 bytes between the rows).

So if you want to optimize your table to use as little storage as possible, you also need to take the order od the columns in the table into account.

share|improve this answer

edited 7 hours ago

answered 8 hours ago

Laurenz AlbeLaurenz Albe

2,84133 silver badges1818 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  So if you want to optimize your table to use as little storage as possible, you also need to take the order od the columns in the table into account. So in a case of CREATE TABLE a (i integer, b boolean); we will see 5 bytes (excluding common overhead, of course) per record whereas in shown structure - 8 bytes per record, is it? Tell about this explicitly, please...
  
  – Akina
  8 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, that is precisely true.
  
  – Laurenz Albe
  7 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Won't the row be aligned on 4 byte boundary anyway, so with just 2 columns it doesn't matter what the order is. But if you were to throw in a third column (a SMALLINT of 2 bytes), you could get either 8 bytes (int, smallint, boolean) or 12 bytes (boolean, smallint, int) for the row size?
  
  – Kayaman
  7 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes that is again true. The next row would start at an integer boundary again. I tried to make the answer more precise.
  
  – Laurenz Albe
  7 hours ago
  
  

  
  
  
  

add a comment | 

3

You can find out the storage size with

SELECT typname, typlen FROM pg_type WHERE typname IN ('bool', 'int4');

 typname | typlen 
---------+--------
 bool | 1
 int4 | 4
(2 rows)

However you need to take alignment into account:

SELECT typname, typlen, typalign FROM pg_type WHERE typname IN ('bool', 'int4');

 typname | typlen | typalign 
---------+--------+----------
 bool | 1 | c
 int4 | 4 | i
(2 rows)

c is “character” (1 byte), while i is “integer”.

If you define a table like this:

CREATE TABLE a (
 b boolean,
 i integer
);

you will get three unused “padding” bytes between the columns, so that the integer can be stored at an address that is divisible by 4.

So the boolean would take up 4 rather than 1 bytes of storage.

If you specify the columns the other way around, the space taken up by each row would only be 5 bytes (but since the next row starts at an integer boundary again, you'd lose 3 bytes between the rows).

So if you want to optimize your table to use as little storage as possible, you also need to take the order od the columns in the table into account.

share|improve this answer

edited 7 hours ago

answered 8 hours ago

Laurenz AlbeLaurenz Albe

2,84133 silver badges1818 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  So if you want to optimize your table to use as little storage as possible, you also need to take the order od the columns in the table into account. So in a case of CREATE TABLE a (i integer, b boolean); we will see 5 bytes (excluding common overhead, of course) per record whereas in shown structure - 8 bytes per record, is it? Tell about this explicitly, please...
  
  – Akina
  8 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, that is precisely true.
  
  – Laurenz Albe
  7 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Won't the row be aligned on 4 byte boundary anyway, so with just 2 columns it doesn't matter what the order is. But if you were to throw in a third column (a SMALLINT of 2 bytes), you could get either 8 bytes (int, smallint, boolean) or 12 bytes (boolean, smallint, int) for the row size?
  
  – Kayaman
  7 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes that is again true. The next row would start at an integer boundary again. I tried to make the answer more precise.
  
  – Laurenz Albe
  7 hours ago
  
  

  
  
  
  

add a comment | 

You can find out the storage size with

SELECT typname, typlen FROM pg_type WHERE typname IN ('bool', 'int4');

 typname | typlen 
---------+--------
 bool | 1
 int4 | 4
(2 rows)

However you need to take alignment into account:

SELECT typname, typlen, typalign FROM pg_type WHERE typname IN ('bool', 'int4');

 typname | typlen | typalign 
---------+--------+----------
 bool | 1 | c
 int4 | 4 | i
(2 rows)

c is “character” (1 byte), while i is “integer”.

If you define a table like this:

CREATE TABLE a (
 b boolean,
 i integer
);

you will get three unused “padding” bytes between the columns, so that the integer can be stored at an address that is divisible by 4.

So the boolean would take up 4 rather than 1 bytes of storage.

If you specify the columns the other way around, the space taken up by each row would only be 5 bytes (but since the next row starts at an integer boundary again, you'd lose 3 bytes between the rows).

So if you want to optimize your table to use as little storage as possible, you also need to take the order od the columns in the table into account.

share|improve this answer

edited 7 hours ago

answered 8 hours ago

Laurenz AlbeLaurenz Albe

2,84133 silver badges1818 bronze badges

SELECT typname, typlen FROM pg_type WHERE typname IN ('bool', 'int4');

 typname | typlen 
---------+--------
 bool | 1
 int4 | 4
(2 rows)

SELECT typname, typlen, typalign FROM pg_type WHERE typname IN ('bool', 'int4');

 typname | typlen | typalign 
---------+--------+----------
 bool | 1 | c
 int4 | 4 | i
(2 rows)

CREATE TABLE a (
 b boolean,
 i integer
);

share|improve this answer

edited 7 hours ago

answered 8 hours ago

Laurenz AlbeLaurenz Albe

2,84133 silver badges1818 bronze badges

answered 8 hours ago

Laurenz AlbeLaurenz Albe

2,84133 silver badges1818 bronze badges

answered 8 hours ago

Laurenz AlbeLaurenz Albe

2,84133 silver badges1818 bronze badges

1

The storage size of each data type is documented in the manual

Both types are fixed-length data types and the their sizes are documented in the respective chapters of the manual

For integer

integer 4 bytes

For boolean

boolean 1 byte

share|improve this answer

answered 8 hours ago

a_horse_with_no_namea_horse_with_no_name

45.9k99 gold badges9090 silver badges122122 bronze badges

add a comment | 

1

The storage size of each data type is documented in the manual

Both types are fixed-length data types and the their sizes are documented in the respective chapters of the manual

For integer

integer 4 bytes

For boolean

boolean 1 byte

share|improve this answer

answered 8 hours ago

a_horse_with_no_namea_horse_with_no_name

45.9k99 gold badges9090 silver badges122122 bronze badges

add a comment | 

The storage size of each data type is documented in the manual

Both types are fixed-length data types and the their sizes are documented in the respective chapters of the manual

For integer

integer 4 bytes

For boolean

boolean 1 byte

share|improve this answer

answered 8 hours ago

a_horse_with_no_namea_horse_with_no_name

45.9k99 gold badges9090 silver badges122122 bronze badges

share|improve this answer

answered 8 hours ago

a_horse_with_no_namea_horse_with_no_name

45.9k99 gold badges9090 silver badges122122 bronze badges

answered 8 hours ago

a_horse_with_no_namea_horse_with_no_name

45.9k99 gold badges9090 silver badges122122 bronze badges

answered 8 hours ago

a_horse_with_no_namea_horse_with_no_name

45.9k99 gold badges9090 silver badges122122 bronze badges