9

I'm looking for the BLOCKSIZE configuration in postgres. I want to know is there a way to change/set the value?

Rajin
  • 93
  • 1
  • 3
  • Hi Rajin, maybe this will help you: https://dba.stackexchange.com/questions/28556/manually-increase-postgresqls-table-size-limit-of-32-tb – F.Lazarescu May 15 '19 at 11:15
  • This can't easily be changed. Why do you think you need that? –  May 15 '19 at 11:29

3 Answers3

5

you need to compile postgres from source, and during configure step pass additional flags with size in kB:

./configure --with-blocksize=BLOCKSIZE --with-wal-blocksize=BLOCKSIZE

default BLOCKSIZE is 8kB, maximum value: 32kB

after successful installation, you can confirm the current block size by executing:

SELECT current_setting('block_size');
Sebastian
  • 330
  • 4
  • 9
3

For a different data block size, you would have to recompile PostgreSQL after configuring it with

./configure --with-blocksize=<size in kB>

But I wouldn't recommend doing that, since I have never heard of anybody using that, and there are chances that you uncover interesting PostgreSQL bugs that way.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 1
    are you sure it's correct answer? OP asks for BLOCKSIZE - table block size, which is default 8kB; SEGSIZE is table segment size, currently 1GB by default; – Sebastian Mar 11 '21 at 13:01
  • 1
    @Sebastian You are right; I have edited my answer. Funny that nobody noticed it before. – Laurenz Albe Mar 11 '21 at 13:34
2

This is no runtime configuration.

https://www.postgresql.org/docs/current/storage-page-layout.html

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).

Huan
  • 36
  • 1
  • Is "page" synonymous with "database block" in Postgre? Those "pages" have nothing to do with _OS pages_ (a.k.a. _virtual pages_ or _memory pages_), correct? – galeop Apr 14 '23 at 09:58