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