1

We just ran into a limit on indexing due to the 'index size cannot be more than 1/3 of page size (2712)' error.

This indicates the buffer page size is 8192 (8k). This seems awfully small during the terabyte era.

This does not seem to be a parameter we can reset in a config file. I can download, build, and install it if needed.

What are the performance considerations? I would think we might benefit substantially on performance with a larger buffer page size. Can someone experienced with PostgreSQL database tuning offer an opinion?

Thanks,

Andrew Wolfe

Improve this question
4
  • 3
    "This seems awfully small during the terabyte era" there have been numerous discussion about the block size. For regular DBMS this is a pretty good choice. Oracle defaults to the same value (but can be configured for other sizes). SQL Server uses 8k as - and won't let you change that. What kind of index are you trying to create? What is the problem the index is supposed to solve? Very often questions asking to workaround some limits can be solved by taking a different approach.
    – user1822
    Oct 31, 2014 at 17:32
  • 1
    postgresql.1045698.n5.nabble.com/…
    – user1822
    Oct 31, 2014 at 17:36
  • 1
    You can change the page size, but nobody does in practice, and nobody tests with other values. You enter unknown territory if you run with a non-default blocksize in production. Usually you land up wanting to use an expression index on the prefix instead. It'd be nice if PostgreSQL could do this automatically, but it can't (yet, patches welcome).
    – Craig Ringer
    Nov 1, 2014 at 8:44
  • 2
    Let's not intimidate people. It's possible to change page size, it becomes frozen at initdb. I'm nobody. We have been using page sizes of 32kB and 160kB in production since 2011, including at client's sites. That's because our load is particular, we have only 10-20 users at a time, and each writes to few tables, 100s of MB at a time. It's a small number of persons, processing and analyzing very large datasets. It is only a hassle because everybody must be warned that we must compile postgresql ourselves.
    – migle
    May 23, 2016 at 12:36

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.