Re: Optimizing a read-only database

Le 18/05/2015 16:38, William Dunn a écrit :

Thank you William,

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

Yes, good idea.

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

Already set to 5 min with 50 segments and 0.9 completion target (but
used also for the bulk loading). But of course I will set it to 1 hour
when in read only mode.

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

Hum... I was planning to put indexes and data on different disks (SSD) /
controller to maximize bandwith use, am I wrong?

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

Vacuum is done at the end of the import and then set to off.

>   * 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

At least a read write lock should be needed, but you're right: better
take a look at the source code to be sure.

Best regards