Re: Optimizing a read-only database

Hello François,

- With read-only work loads you can make shared_buffers very large, like
40% of RAM available to the database. Usually you would keep it lower
because in a write heavy workload large shared_buffers causes checkpoints
to have huge IO, but since you are not making changes in shared_buffers
this will not happen.

- You can also increase checkpoint_timeout to a very large value to
prevent checkpoints, since you don't need them. WAL level can be minimal as
well.

- You can also run a CLUSTER command on one of your indexes to group
data that is frequently accessed together into the same segment of disk so
you can get more of it in a single IO operation.

- You can also run the VACUUM FULL command during off-hours to get your
tables vacuumed and statistics up-to-date. It's usually too much overhead
to be worthwhile but since you are not doing updates you only have to do it
once then don't need to worry about autovacuum being aggressive enough.

- I don't think that removing locks will provide any benefit if your
queries are truly read-only since ordinary read-only transactions do not
require any locks

That's all I can think of right now!
Will

*Will J. Dunn*
*willjdunn.com <http://willjdunn.com>*

On Mon, May 18, 2015 at 10:07 AM, François Battail <
francois(dot)battail(at)sipibox(dot)fr> wrote:

> Dear List,
>
> I would like to know if somebody is aware of tricks for optimizing
> PostgreSQL settings for a read-only database.
> I have a big read-only database (> 1.10^9 records splitted into ~ 10
> tables) using GiST and Btree indexes, no foreign keys on tables at all.
>
> I believe that not doing locks on pages could save some time as there's a
> lot of simultaneaous readers, but so far I've found nothing about this
> specific case in official documentation...
>
> Best regards
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>