Server Tuning
Server-wide memory settings
We will look at two parameters related to buffers, the first parameter is an
allocation and the second parameter is a pointer to the optimizer. Both have
a significant impact on performance.
shared_buffers
This parameter decides how much memory will be dedicated to PostgreSQL
to cache data. When multiple sessions request the same data from the same
table, shared_buffers ensure that there is no need to have many copies of
the datasets in memory. This approach reduces the necessary physical I/O.
The buffers are allocated at database startup.
This parameter has a significant impact on the performance because this
setting directly affects the amount of physical I/O on the server.
In versions earlier than 9.3, kernel settings adjustment (shmmax) would be necessary to set a
value higher than 32 MB for shared_buffers.
About 25 percent of RAM is a reasonable starting point, assuming that the server is not one
with hundreds of GBs of RAM. Large values of shared_buffers can result in a drop in
performance in older versions of PostgreSQL. Large buffers in a write-heavy system result in a
lot of dirty data waiting to be flushed. Checkpoints take care of this flushing. During
checkpoints, there will a big spike in the system I/O if there is a large volume of data to be
flushed. Later versions of PostgreSQL added parameters to spread out the checkpoint process
and thus reduce the spikes.
In cases where the server has lots of RAM, starting with something like 8 GB of
shared_buffers and testing to see how much cache hit we are getting might be the right
approach.
On the other end, an upper limit of 40 percent of RAM is usually recommended.
effective_cache_size:
This value tells PostgreSQL approximately how much memory is available for all cache purposes
(shared_buffers plus filesystem cache). Unlike shared_buffers, this memory is not
allocated. The value is used for estimation purposes by the query planner. Concurrent queries
will share the available space. The default setting of 128 MB can be too low in most cases, as is
the case with shared_buffers.
When we actually execute the query, there might not be any difference for
the two cases shown.
Let's see why this parameter has an impact. When we increase the
effective_ cache_size, the planner assumes that more pages will fit
in memory. This makes using indexes better than sequential scans. If the
setting is too low, PostgreSQL might decide that sequential scans will be
efficient. In short, a high setting increases the likelihood of the use of
indexes, whereas a low setting increases the chances of sequential scans.
Managing writes, connections, and maintenance
When we increase shared_buffers, it might be necessary to increase
checkpoint_segments value too.
In a busy system with a lot of changes being made to the data, a low value will result in
frequent checkpoints. We can increase this number and spread out the process of writing a
relatively larger volume of data at each checkpoint (resulting from fewer checkpoints) using
checkpoint_completion_target.The default value of 3 is usually too low for any
system with frequent writes.
A conservative figure for work_mem can be arrived at by a formula, as shown in the
following code:
work_mem = (available_ram * 0.25) / max_connections
As work_mem can be set at session level, we can always allocate more memory for individual
connections when there is an obvious need to, such as in the case of batch processes, which
will sort the data in a big table.
End-of-day processes in transactional systems and Extract, Transform, and Load (ETL)
processes for data warehouses might benefit from higher settings of work_mem.
Disk and Disk access cost :
It's not a good idea to set random_page_cost to a value less than seq_page_cost. We
should also remember that it's not the individual values of random and sequential page costs,
but their ratio that will influence the query planner.
The default settings of 4 for random_page_cost and 1 for seq_page_cost imply
that random seeks are four times as expensive as sequential access. Changing the default
ratio of 4:1 to a lower ratio 2:1, increases the chances that the planner will decide to use
indexes.
CPU costs:
The default CPU-related cost settings are pretty small .
cpu_operator_cost (0.0025): This value represents the CPU cost to perform
an operation (such as hash or aggregation) .
cpu_tuple_cost (0.01): This number represents the cost to process each row.
cpu_index_tuple_cost (0.005): This number represents the cost of processing
each index entry.
The formula used to calculate the total cost can be simplified as follows:
TC = n1*c1+n2*c2+n3*c3+….
Here, TC represents the total cost: n1, n2, n3, and so on represent the number of pages or
tuples as the case may be: c1, c2, c3, and so on represent the respective cost
constants/parameter settings.
postgres=# EXPLAIN ANALYZE SELECT * FROM myt;
QUERY PLAN
------------------------------------------------------------------
Seq Scan on myt (cost=0.00..28850.00 rows=2000000 width=4) (actual
time=0.000..339.130 rows=2000003 loops=1)
Total runtime: 626.217 ms
(2 rows)
postgres=# SELECT
relpages * current_setting('seq_page_cost')::decimal +
reltuples * current_setting('cpu_tuple_cost')::decimal
as total_cost FROM pg_class WHERE relname='myt';
total_cost
------------
28850
(1 row)
The number 28850 appears in the cost we computed as well as in
PostgreSQL's cost estimates.
Materialized views: Materialized views are similar to views because they also depend on
other tables for their data.
Although there are a couple of differences. SELECTs against views will always fetch the latest
data, whereas SELECTs against materialized views might fetch stale data.
The other key difference is that materialized views actually contain data and take up storage
space (proportionate to the volume of data), whereas views do not occupy significant space on
disk.
Materialized views are used mostly to capture summaries or snapshots of data from base
tables. Some latency/staleness is acceptable. Consider the case of a report for average branch-
wise balances for a bank at the end of business day. Typically, the report will be sent after close
of business for the day, implying that the averages, once calculated, are not likely to change for
that particular day. In addition, no one is likely to request the report before close of business.
There are other use cases for materialized views. Let's see one more. We can
use PostgreSQL's foreign data wrappers to access data from various data
sources including:
Relational databases (such as Oracle and MySQL)
NoSQL databases (such as MongoDB, Redis, and CouchDB)
Various types of files
When we use materialized views to store precalculated aggregates, there are
two advantages. First, we avoid the overhead of doing the same calculation
multiple times (there may be multiple requests for the reports, right?).
Materialized views that provide summaries tend to be small compared to the
base tables. So, we will save on the cost of scanning big tables. This is the
second advantage.
When we use materialized views to store data from foreign tables, we make
the query performance more predictable. We also eliminate the data transfer
that occurs when we access foreign tables multiple times.
Here is how we can create and refresh a materialized view:
The data is stale now. So, let's refresh the materialized view and update its
contents:
accounts=# REFRESH MATERIALIZED VIEW mv_myt ;
REFRESH MATERIALIZED VIEW
accounts=# SELECT * FROM mv_myt;
We can look up all our materialized views as follows:
accounts=# SELECT matviewname ,definition
FROM pg_matviews;
matviewname | definition
------------- + -----------------------------
mv_myt | SELECT avg([Link]) AS avg
FROM myt