How can I get the page size of a Postgres database?Wordcount in a field (all and unique) - is there a more elegant/optimal way?PostgreSQL Initial Database SizeHow to Change location of postgres cluster and database within the same machine?Try to define appropiate memory size for this postgres databaseImporting data from MS Access database to an existing Postgres databaseUsing the correct postgres serverHow can filesystem storage (for blobs, images, etc.) be integrated with (traditional) database storage (e.g. MySQL/Postgres)?How Can I Access the postgres database in a Script Executing pg_dumpall?Is there a query to check the current WAL size in PostgreSQL?Postgres (permissions): Can connect to DB, can't see tablesWhat determine how much time the query will cost?
My mom's return ticket is 3 days after I-94 expires
How do I properly use a function under a class?
How can I find out about the game world without meta-influencing it?
Approach sick days in feedback meeting
Can a 40amp breaker be used safely and without issue with a 40amp device on 6AWG wire?
Idiom for 'person who gets violent when drunk"
What does BREAD stand for while drafting?
Placement of positioning lights on A320 winglets
Is it good practice to create tables dynamically?
Is time complexity more important than space complexity?
Am I being scammed by a sugar daddy?
Nth term of Van Eck Sequence
Is it true that "only photographers care about noise"?
Are athlete's college degrees discounted by employers and graduate school admissions?
How to deal with an excess of white-space in a CRM UI?
What is Gilligan's full name?
Can an open source licence be revoked if it violates employer's IP?
Can you open the door or die? v2
Why did Robert pick unworthy men for the White Cloaks?
The best in flight meal option for those suffering from reflux
Is tuition reimbursement a good idea if you have to stay with the job
Which are the methodologies for interpreting Vedas?
Can I use 220 V outlets on a 15 ampere breaker and wire it up as 110 V?
Purpose of cylindrical attachments on Power Transmission towers
How can I get the page size of a Postgres database?
Wordcount in a field (all and unique) - is there a more elegant/optimal way?PostgreSQL Initial Database SizeHow to Change location of postgres cluster and database within the same machine?Try to define appropiate memory size for this postgres databaseImporting data from MS Access database to an existing Postgres databaseUsing the correct postgres serverHow can filesystem storage (for blobs, images, etc.) be integrated with (traditional) database storage (e.g. MySQL/Postgres)?How Can I Access the postgres database in a Script Executing pg_dumpall?Is there a query to check the current WAL size in PostgreSQL?Postgres (permissions): Can connect to DB, can't see tablesWhat determine how much time the query will cost?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
Is there some way to select the page size of a Postgres database?
I know that the page size is usually 8K. I also read this email thread from 2003. I don't know that pg_controldata will work as I don't have access to the filesystem that the database is hosted on.
postgresql
asked Jun 5 at 23:03
JohnJohn
1183
New contributor
John is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Is there some way to select the page size of a Postgres database?
I know that the page size is usually 8K. I also read this email thread from 2003. I don't know that pg_controldata will work as I don't have access to the filesystem that the database is hosted on.
postgresql
asked Jun 5 at 23:03
JohnJohn
1183
New contributor
John is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Is there some way to select the page size of a Postgres database?
I know that the page size is usually 8K. I also read this email thread from 2003. I don't know that pg_controldata will work as I don't have access to the filesystem that the database is hosted on.
postgresql
asked Jun 5 at 23:03
JohnJohn
1183
New contributor
John is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Is there some way to select the page size of a Postgres database?
I know that the page size is usually 8K. I also read this email thread from 2003. I don't know that pg_controldata will work as I don't have access to the filesystem that the database is hosted on.
postgresql
postgresql
asked Jun 5 at 23:03
JohnJohn
1183
New contributor
John is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Jun 5 at 23:03
JohnJohn
1183
New contributor
John is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Jun 5 at 23:03
JohnJohn
1183
New contributor
John is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Jun 5 at 23:03
JohnJohn
1183
asked Jun 5 at 23:03
JohnJohn
1183
1183
New contributor
John is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
John is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
If you don't have access to pg_controldata ...
There is a simple way:
test=# SELECT current_setting('block_size');
current_setting
-----------------
8192
The manual:
The following “parameters” are read-only, and are determined when
PostgreSQL is compiled or when it is installed. [...]
block_size(integer)
Reports the size of a disk block. It is determined by the value of
BLCKSZwhen building the server. The default value is 8192 bytes. The
meaning of some configuration variables (such as shared_buffers) is
influenced byblock_size. See Section 19.4 for information.
To verify
Create a dummy table with only 1 small row: one data page is allocated. Then check the size of the "main" relation fork with pg_relation_size()
test=# CREATE TEMP TABLE foo AS SELECT 1 AS id;
SELECT 1
test=# SELECT pg_size_pretty(pg_relation_size('pg_temp.foo'));
pg_size_pretty
----------------
8192 bytes
(1 row)
So the page size is 8 kB, which is hardly surprising like you mentioned. The manual:
Every table and index is stored as an array of pages of a fixed size (usually 8 kB, although a different page size can be selected when compiling the server).
answered Jun 6 at 0:02
Erwin BrandstetterErwin Brandstetter
98.1k9199320
+1 - but just as a matter of interest, why would one compile the server with a different page size?
– Vérace
Jun 6 at 5:47
Never done this myself, but I imagine if you have large rows (that cannot be "toasted") it might save a bit of overhead. Not sure it would be worth the trouble.
– Erwin Brandstetter
Jun 6 at 11:12
@Vérace A btree index entry is limited to about 1/3 of the block size. That could be one motivation to increase it. You might also hope to get better performance, but I've never seen that pan out myself.
– jjanes
Jun 6 at 15:00
@ErwinBrandstetter - Thanks for that Erwin. You might be interested in a bonus that I'm offering Postgresql related - it should be right up your alley!
– Vérace
Jun 6 at 15:03
@Vérace: I had a nagging sensation I'd missed something. And indeed, there is a simpler way, yet. Consider the addition.
– Erwin Brandstetter
Jun 7 at 23:59
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
);
);
John is a new contributor. Be nice, and check out our Code of Conduct.
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f239917%2fhow-can-i-get-the-page-size-of-a-postgres-database%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
If you don't have access to pg_controldata ...
There is a simple way:
test=# SELECT current_setting('block_size');
current_setting
-----------------
8192
The manual:
The following “parameters” are read-only, and are determined when
PostgreSQL is compiled or when it is installed. [...]
block_size(integer)
Reports the size of a disk block. It is determined by the value of
BLCKSZwhen building the server. The default value is 8192 bytes. The
meaning of some configuration variables (such as shared_buffers) is
influenced byblock_size. See Section 19.4 for information.
To verify
Create a dummy table with only 1 small row: one data page is allocated. Then check the size of the "main" relation fork with pg_relation_size()
test=# CREATE TEMP TABLE foo AS SELECT 1 AS id;
SELECT 1
test=# SELECT pg_size_pretty(pg_relation_size('pg_temp.foo'));
pg_size_pretty
----------------
8192 bytes
(1 row)
So the page size is 8 kB, which is hardly surprising like you mentioned. The manual:
Every table and index is stored as an array of pages of a fixed size (usually 8 kB, although a different page size can be selected when compiling the server).
answered Jun 6 at 0:02
Erwin BrandstetterErwin Brandstetter
98.1k9199320
+1 - but just as a matter of interest, why would one compile the server with a different page size?
– Vérace
Jun 6 at 5:47
Never done this myself, but I imagine if you have large rows (that cannot be "toasted") it might save a bit of overhead. Not sure it would be worth the trouble.
– Erwin Brandstetter
Jun 6 at 11:12
@Vérace A btree index entry is limited to about 1/3 of the block size. That could be one motivation to increase it. You might also hope to get better performance, but I've never seen that pan out myself.
– jjanes
Jun 6 at 15:00
@ErwinBrandstetter - Thanks for that Erwin. You might be interested in a bonus that I'm offering Postgresql related - it should be right up your alley!
– Vérace
Jun 6 at 15:03
@Vérace: I had a nagging sensation I'd missed something. And indeed, there is a simpler way, yet. Consider the addition.
– Erwin Brandstetter
Jun 7 at 23:59
add a comment |
If you don't have access to pg_controldata ...
There is a simple way:
test=# SELECT current_setting('block_size');
current_setting
-----------------
8192
The manual:
The following “parameters” are read-only, and are determined when
PostgreSQL is compiled or when it is installed. [...]
block_size(integer)
Reports the size of a disk block. It is determined by the value of
BLCKSZwhen building the server. The default value is 8192 bytes. The
meaning of some configuration variables (such as shared_buffers) is
influenced byblock_size. See Section 19.4 for information.
To verify
Create a dummy table with only 1 small row: one data page is allocated. Then check the size of the "main" relation fork with pg_relation_size()
test=# CREATE TEMP TABLE foo AS SELECT 1 AS id;
SELECT 1
test=# SELECT pg_size_pretty(pg_relation_size('pg_temp.foo'));
pg_size_pretty
----------------
8192 bytes
(1 row)
So the page size is 8 kB, which is hardly surprising like you mentioned. The manual:
Every table and index is stored as an array of pages of a fixed size (usually 8 kB, although a different page size can be selected when compiling the server).
answered Jun 6 at 0:02
Erwin BrandstetterErwin Brandstetter
98.1k9199320
+1 - but just as a matter of interest, why would one compile the server with a different page size?
– Vérace
Jun 6 at 5:47
Never done this myself, but I imagine if you have large rows (that cannot be "toasted") it might save a bit of overhead. Not sure it would be worth the trouble.
– Erwin Brandstetter
Jun 6 at 11:12
@Vérace A btree index entry is limited to about 1/3 of the block size. That could be one motivation to increase it. You might also hope to get better performance, but I've never seen that pan out myself.
– jjanes
Jun 6 at 15:00
@ErwinBrandstetter - Thanks for that Erwin. You might be interested in a bonus that I'm offering Postgresql related - it should be right up your alley!
– Vérace
Jun 6 at 15:03
@Vérace: I had a nagging sensation I'd missed something. And indeed, there is a simpler way, yet. Consider the addition.
– Erwin Brandstetter
Jun 7 at 23:59
add a comment |
If you don't have access to pg_controldata ...
There is a simple way:
test=# SELECT current_setting('block_size');
current_setting
-----------------
8192
The manual:
The following “parameters” are read-only, and are determined when
PostgreSQL is compiled or when it is installed. [...]
block_size(integer)
Reports the size of a disk block. It is determined by the value of
BLCKSZwhen building the server. The default value is 8192 bytes. The
meaning of some configuration variables (such as shared_buffers) is
influenced byblock_size. See Section 19.4 for information.
To verify
Create a dummy table with only 1 small row: one data page is allocated. Then check the size of the "main" relation fork with pg_relation_size()
test=# CREATE TEMP TABLE foo AS SELECT 1 AS id;
SELECT 1
test=# SELECT pg_size_pretty(pg_relation_size('pg_temp.foo'));
pg_size_pretty
----------------
8192 bytes
(1 row)
So the page size is 8 kB, which is hardly surprising like you mentioned. The manual:
Every table and index is stored as an array of pages of a fixed size (usually 8 kB, although a different page size can be selected when compiling the server).
answered Jun 6 at 0:02
Erwin BrandstetterErwin Brandstetter
98.1k9199320
If you don't have access to pg_controldata ...
There is a simple way:
test=# SELECT current_setting('block_size');
current_setting
-----------------
8192
The manual:
The following “parameters” are read-only, and are determined when
PostgreSQL is compiled or when it is installed. [...]
block_size(integer)
Reports the size of a disk block. It is determined by the value of
BLCKSZwhen building the server. The default value is 8192 bytes. The
meaning of some configuration variables (such as shared_buffers) is
influenced byblock_size. See Section 19.4 for information.
To verify
Create a dummy table with only 1 small row: one data page is allocated. Then check the size of the "main" relation fork with pg_relation_size()
test=# CREATE TEMP TABLE foo AS SELECT 1 AS id;
SELECT 1
test=# SELECT pg_size_pretty(pg_relation_size('pg_temp.foo'));
pg_size_pretty
----------------
8192 bytes
(1 row)
So the page size is 8 kB, which is hardly surprising like you mentioned. The manual:
Every table and index is stored as an array of pages of a fixed size (usually 8 kB, although a different page size can be selected when compiling the server).
answered Jun 6 at 0:02
Erwin BrandstetterErwin Brandstetter
98.1k9199320
edited Jun 7 at 23:57
answered Jun 6 at 0:02
Erwin BrandstetterErwin Brandstetter
98.1k9199320
answered Jun 6 at 0:02
Erwin BrandstetterErwin Brandstetter
98.1k9199320
answered Jun 6 at 0:02
Erwin BrandstetterErwin Brandstetter
98.1k9199320
98.1k9199320
+1 - but just as a matter of interest, why would one compile the server with a different page size?
– Vérace
Jun 6 at 5:47
Never done this myself, but I imagine if you have large rows (that cannot be "toasted") it might save a bit of overhead. Not sure it would be worth the trouble.
– Erwin Brandstetter
Jun 6 at 11:12
@Vérace A btree index entry is limited to about 1/3 of the block size. That could be one motivation to increase it. You might also hope to get better performance, but I've never seen that pan out myself.
– jjanes
Jun 6 at 15:00
@ErwinBrandstetter - Thanks for that Erwin. You might be interested in a bonus that I'm offering Postgresql related - it should be right up your alley!
– Vérace
Jun 6 at 15:03
@Vérace: I had a nagging sensation I'd missed something. And indeed, there is a simpler way, yet. Consider the addition.
– Erwin Brandstetter
Jun 7 at 23:59
add a comment |
+1 - but just as a matter of interest, why would one compile the server with a different page size?
– Vérace
Jun 6 at 5:47
Never done this myself, but I imagine if you have large rows (that cannot be "toasted") it might save a bit of overhead. Not sure it would be worth the trouble.
– Erwin Brandstetter
Jun 6 at 11:12
@Vérace A btree index entry is limited to about 1/3 of the block size. That could be one motivation to increase it. You might also hope to get better performance, but I've never seen that pan out myself.
– jjanes
Jun 6 at 15:00
@ErwinBrandstetter - Thanks for that Erwin. You might be interested in a bonus that I'm offering Postgresql related - it should be right up your alley!
– Vérace
Jun 6 at 15:03
@Vérace: I had a nagging sensation I'd missed something. And indeed, there is a simpler way, yet. Consider the addition.
– Erwin Brandstetter
Jun 7 at 23:59
+1 - but just as a matter of interest, why would one compile the server with a different page size?
– Vérace
Jun 6 at 5:47
+1 - but just as a matter of interest, why would one compile the server with a different page size?
– Vérace
Jun 6 at 5:47
Never done this myself, but I imagine if you have large rows (that cannot be "toasted") it might save a bit of overhead. Not sure it would be worth the trouble.
– Erwin Brandstetter
Jun 6 at 11:12
Never done this myself, but I imagine if you have large rows (that cannot be "toasted") it might save a bit of overhead. Not sure it would be worth the trouble.
– Erwin Brandstetter
Jun 6 at 11:12
@Vérace A btree index entry is limited to about 1/3 of the block size. That could be one motivation to increase it. You might also hope to get better performance, but I've never seen that pan out myself.
– jjanes
Jun 6 at 15:00
@Vérace A btree index entry is limited to about 1/3 of the block size. That could be one motivation to increase it. You might also hope to get better performance, but I've never seen that pan out myself.
– jjanes
Jun 6 at 15:00
@ErwinBrandstetter - Thanks for that Erwin. You might be interested in a bonus that I'm offering Postgresql related - it should be right up your alley!
– Vérace
Jun 6 at 15:03
@ErwinBrandstetter - Thanks for that Erwin. You might be interested in a bonus that I'm offering Postgresql related - it should be right up your alley!
– Vérace
Jun 6 at 15:03
@Vérace: I had a nagging sensation I'd missed something. And indeed, there is a simpler way, yet. Consider the addition.
– Erwin Brandstetter
Jun 7 at 23:59
@Vérace: I had a nagging sensation I'd missed something. And indeed, there is a simpler way, yet. Consider the addition.
– Erwin Brandstetter
Jun 7 at 23:59
add a comment |
John is a new contributor. Be nice, and check out our Code of Conduct.
John is a new contributor. Be nice, and check out our Code of Conduct.
John is a new contributor. Be nice, and check out our Code of Conduct.
John is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f239917%2fhow-can-i-get-the-page-size-of-a-postgres-database%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
