Journey of a PostgreSQL Write

BG Writer (Background Writer)

The BGWriter helps to diminish the I/O operations needed to be done by Checkpoints. This stabilizes the system’s I/O. BGWriter writes Dirty Buffers to disk when the system load is low to minimize the impact on active transactions. It constantly monitors the DB Buffer Pol and makes decisions on when to write dirty buffers to disk and which buffers to free up for reuse.

Frequency of Checkpoints

Use the below query to find the frequency of the checkpoints:

WITH sub as (
  SELECT 
    EXTRACT(
      EPOCH 
      FROM 
        (now() - stats_reset)
    ) AS seconds_since_start, 
    (
      checkpoints_timed + checkpoints_req
    ) AS total_checkpoints 
  FROM 
    pg_stat_bgwriter
) 
SELECT 
  total_checkpoints, 
  seconds_since_start / total_checkpoints / 60 AS minutes_between_checkpoints 
FROM 
  sub;

There are two important columns checkpoints_req, checkpoints_timed.

If the checkpoints_req is more than the checkpoints_timed, PostgreSQL is doing checkpoints due to the high WAL generation.

If the checkpoints are happening frequently it will cause more IO load on the machine so increase the max_wal_size parameter.

checkpoints_req > checkpoints_timed = (bad). This means that PostgreSQL is doing checkpoints due to the high WAL generation

The basic rule is simple – checkpoints_timed value should be much higher than checkpoints_req. It’s best when the last one (checkpoints_req) is near zero. It may be obtained by increasing max_wal_size (or checkpoint_segments) and checkpoint_timeout. A good initial point is to set max_wal_size to 10GB and checkpoint_timeout = 30min. Also, checkpoint_completion_target should be configured in a way that will enable spread of execution of checkpoints to a time that is the closest to timeout or to the size of collected WAL. A good starting point for this is 0.9 (default in PostgreSQL 14 and above). Thus, with these settings checkpoint will occur when Postgres collects 10GB of WAL, or after 30 minutes from the last checkpoint. The already running checkpoint’s execution will spread over 27 minutes or until Postgres again collects 9GB of WAL.

Cheers!

Suggested Solutions:

1. Set hot_standby_feedback = on on the standby. This will pass a message to the Primary server that some rows are still required in a table. It will allow queries on the Secondary replica to be completed, but it will cause bloat in the Primary.
2. Increase max_standby_archive_delay / max_standby_streaming_delay. This will allow the queries to finish as well by allowing the standby server to purposely augment replication lag.

Otherwise, since you can also try to resubmit the queries, you may be okay with those queries being canceled sometimes.

Cheers!

The data can be replicated from the primary/master node to the replica/slave node after the transaction has been committed on the primary node. The replica is never ahead of the primary. It is usually a little behind. This delay is called lag.

Monitoring Primary Server

The system view to monitor Replication on the Primary node is pg_stat_replication.

\d pg_stat_replication
                    View "pg_catalog.pg_stat_replication"
      Column      |           Type           | Collation | Nullable | Default 
------------------+--------------------------+-----------+----------+---------
 pid              | integer                  |           |          | 
 usesysid         | oid                      |           |          | 
 usename          | name                     |           |          | 
 application_name | text                     |           |          | 
 client_addr      | inet                     |           |          | 
 client_hostname  | text                     |           |          | 
 client_port      | integer                  |           |          | 
 backend_start    | timestamp with time zone |           |          | 
 backend_xmin     | xid                      |           |          | 
 state            | text                     |           |          | 
 sent_lsn         | pg_lsn                   |           |          | 
 write_lsn        | pg_lsn                   |           |          | 
 flush_lsn        | pg_lsn                   |           |          | 
 replay_lsn       | pg_lsn                   |           |          | 
 write_lag        | interval                 |           |          | 
 flush_lag        | interval                 |           |          | 
 replay_lag       | interval                 |           |          | 
 sync_priority    | integer                  |           |          | 
 sync_state       | text                     |           |          | 
 reply_time       | timestamp with time zone |           |          |

Important Fields in pg_stat_replication

Let’s talk about the fields in detail.

• pid: This represents the process ID of the wal_receiver process in charge of this streaming connection. If you check the process table in your operating system, you should find a PostgreSQL process with exactly this number
• usesysid: OID of user which is used for Streaming replication.
• usename: This (not username; mind the missing “r”) stores the name of the user related to usesysid. It is what the client has put into the connection string
• application_name: Application name connected to master
• client_addr: This will tell you where the streaming connection comes from. It holds the IP address of the client.
• client_hostname: Hostname of standby.
• client_port: This is the TCP port number the client is using for communication with the particular WAL sender. -1 will be shown if local UNIX sockets are used.
• backend_start: Start time when SR connected to Master.
• backend_xmin: The transaction ID reported by hot_standby_feedback (which is the oldest transaction ID on a slave). It can make sense to monitor the difference between the current transaction ID on the master and the oldest one reported by the slave as well, to check whether there are unusually high differences
• state: Current WAL sender state i.e streaming
• sent_lsn: This represents the last transaction log position sent to the connection.
• write_lsn: Last transaction written on disk at standby.
• flush_lsn: This is the last location that was flushed to the standby system. Mind the difference between writing and flushing here. Writing does not imply flushing.
• replay_lsn: Last transaction flush on disk at standby.
• write_lag: Elapsed time during committed WALs from primary to the standby (but not yet committed in the standby)
• flush_lag: Elapsed time during committed WALs from primary to the standby (WAL’s has already been flushed but not yet applied)
• replay_lag: Elapsed time during committed WALs from primary to the standby (fully committed in standby node)
• sync_priority: Priority of standby server being chosen as synchronous standby
• sync_state: Sync State of standby (is it async or synchronous).

Below is  a sample query output with a single replica:

When you see an entry in the system view, that means that there is one ACTIVE stream. Otherwise, there is none. You won’t see any entry.

If the values of all *_lsn columns are the same, that means that the Replica has caught up 100%.

sent_lsn: Last write-ahead log location sent on this connection (How much WAL has been sent over the network already?).

write_lsn: Last write-ahead log location written to the operating system (without flushing).

flush_lsn: Last write-ahead log location flushed to disk by this standby server.

replay_lsn: Last write-ahead log location replayed into the database on this standby server (Data that is already visible to end-users).

The following is an illustration of the data flow.

Monitoring Replica Servers

Replica server has incoming replication and remains in recovery mode so that it can replay the WALs/records as they come in. There is a view called pg_stat_wal_receiver.

\d pg_stat_wal_receiver
                      View "pg_catalog.pg_stat_wal_receiver"
        Column         |           Type           | Collation | Nullable | Default 
-----------------------+--------------------------+-----------+----------+---------
 pid                   | integer                  |           |          | 
 status                | text                     |           |          | 
 receive_start_lsn     | pg_lsn                   |           |          | 
 receive_start_tli     | integer                  |           |          | 
 written_lsn           | pg_lsn                   |           |          | 
 flushed_lsn           | pg_lsn                   |           |          | 
 received_tli          | integer                  |           |          | 
 last_msg_send_time    | timestamp with time zone |           |          | 
 last_msg_receipt_time | timestamp with time zone |           |          | 
 latest_end_lsn        | pg_lsn                   |           |          | 
 latest_end_time       | timestamp with time zone |           |          | 
 slot_name             | text                     |           |          | 
 sender_host           | text                     |           |          | 
 sender_port           | integer                  |           |          | 
 conninfo              | text                     |           |          |

Monitoring Replication Slots

The view pg_replication_slots gives one row for each replication slot in the primary. A replication slot guarantees that the  WAL will not vanish if the replica is lagging behind. A primary will recycle its WAL as soon as it doesn’t need it on its own anymore if there are no replication slots.

\d pg_replication_slots
             View "pg_catalog.pg_replication_slots"
       Column        |  Type   | Collation | Nullable | Default 
---------------------+---------+-----------+----------+---------
 slot_name           | name    |           |          | 
 plugin              | name    |           |          | 
 slot_type           | text    |           |          | 
 datoid              | oid     |           |          | 
 database            | name    |           |          | 
 temporary           | boolean |           |          | 
 active              | boolean |           |          | 
 active_pid          | integer |           |          | 
 xmin                | xid     |           |          | 
 catalog_xmin        | xid     |           |          | 
 restart_lsn         | pg_lsn  |           |          | 
 confirmed_flush_lsn | pg_lsn  |           |          | 
 wal_status          | text    |           |          | 
 safe_wal_size       | bigint  |           |          | 
 two_phase           | boolean |           |          |

Cheers!

SELECT current_timestamp - pg_postmaster_start_time();

SELECT
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit)  as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM
  pg_statio_user_tables;

SELECT 
  datname, 
  (
    blks_hit * 100 /(blks_hit + blks_read)
  ):: numeric as hit_ratio 
from 
  pg_stat_database WHERE 
  datname not in (
    'postgres', 'template0', 'template1'
  );

If the hit ratio is less than 90%, there may be a problem with low allocation of shared buffers or queries doing large table scans. The hit ratio should be close to 100%. Boost shared buffers or tweak queries that do more IO.

Check unvacummed dead tupes – Get table bloat information

Bloat can slow down other write and creates other issues.

1st script:

WITH constants AS (
  SELECT current_setting('block_size')::numeric AS bs, 23 AS hdr, 4 AS ma
), bloat_info AS (
  SELECT
    ma,bs,schemaname,tablename,
    (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
    (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
  FROM (
    SELECT
      schemaname, tablename, hdr, ma, bs,
      SUM((1-null_frac)*avg_width) AS datawidth,
      MAX(null_frac) AS maxfracsum,
      hdr+(
        SELECT 1+count(*)/8
        FROM pg_stats s2
        WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
      ) AS nullhdr
    FROM pg_stats s, constants
    GROUP BY 1,2,3,4,5
  ) AS foo
), table_bloat AS (
  SELECT
    schemaname, tablename, cc.relpages, bs,
    CEIL((cc.reltuples*((datahdr+ma-
      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta
  FROM bloat_info
  JOIN pg_class cc ON cc.relname = bloat_info.tablename
  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema'
), index_bloat AS (
  SELECT
    schemaname, tablename, bs,
    COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
  FROM bloat_info
  JOIN pg_class cc ON cc.relname = bloat_info.tablename
  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema'
  JOIN pg_index i ON indrelid = cc.oid
  JOIN pg_class c2 ON c2.oid = i.indexrelid
)
SELECT
  type, schemaname, object_name, bloat, pg_size_pretty(raw_waste) as waste
FROM
(SELECT
  'table' as type,
  schemaname,
  tablename as object_name,
  ROUND(CASE WHEN otta=0 THEN 0.0 ELSE table_bloat.relpages/otta::numeric END,1) AS bloat,
  CASE WHEN relpages < otta THEN '0' ELSE (bs*(table_bloat.relpages-otta)::bigint)::bigint END AS raw_waste
FROM
  table_bloat
    UNION
SELECT
  'index' as type,
  schemaname,
  tablename || '::' || iname as object_name,
  ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS bloat,
  CASE WHEN ipages < iotta THEN '0' ELSE (bs*(ipages-iotta))::bigint END AS raw_waste
FROM
  index_bloat) bloat_summary
ORDER BY raw_waste DESC, bloat DESC;

2nd Script:

WITH constants AS 
(
       -- define some constants for sizes of things
       -- for reference down the query and easy maintenance
       
   SELECT
      current_setting('block_size')::numeric AS bs,
      23 AS hdr,
      8 AS ma 
)
,
no_stats AS 
(
       -- screen out table who have attributes
       -- which dont have stats, such as JSON
       
   SELECT
      table_schema,
      table_name,
              n_live_tup::numeric as est_rows,
              pg_table_size(relid)::numeric as table_size     
   FROM
      information_schema.columns         
      JOIN
         pg_stat_user_tables as psut            
         ON table_schema = psut.schemaname            
         AND table_name = psut.relname         
      LEFT OUTER JOIN
         pg_stats         
         ON table_schema = pg_stats.schemaname             
         AND table_name = pg_stats.tablename             
         AND column_name = attname     
   WHERE
      attname IS NULL         
      AND table_schema NOT IN 
      (
         'pg_catalog',
         'information_schema'
      )
          
   GROUP BY
      table_schema,
      table_name,
      relid,
      n_live_tup 
)
,
null_headers AS 
(
       -- calculate null header sizes
       -- omitting tables which dont have complete stats
       -- and attributes which aren't visible
       
   SELECT
              hdr + 1 + (sum(
      case
         when
            null_frac <> 0 
         THEN
            1 
         else
            0 
      END
) / 8) as nullhdr,         SUM((1 - null_frac)*avg_width) as datawidth,         MAX(null_frac) as maxfracsum,         schemaname,         tablename,         hdr, ma, bs     
   FROM
      pg_stats 
      CROSS JOIN
         constants         
      LEFT OUTER JOIN
         no_stats             
         ON schemaname = no_stats.table_schema             
         AND tablename = no_stats.table_name     
   WHERE
      schemaname NOT IN 
      (
         'pg_catalog', 'information_schema'
      )
              
      AND no_stats.table_name IS NULL         
      AND EXISTS 
      (
         SELECT
            1             
         FROM
            information_schema.columns                 
         WHERE
            schemaname = columns.table_schema                     
            AND tablename = columns.table_name 
      )
          
   GROUP BY
      schemaname,
      tablename,
      hdr,
      ma,
      bs 
)
,
data_headers AS 
(
       -- estimate header and row size
       
   SELECT
              ma,
      bs,
      hdr,
      schemaname,
      tablename,
              (datawidth + (hdr + ma - (
      case
         when
            hdr % ma = 0 
         THEN
            ma 
         ELSE
            hdr % ma 
      END
)))::numeric AS datahdr,         (maxfracsum*(nullhdr + ma - (
      case
         when
            nullhdr % ma = 0 
         THEN
            ma 
         ELSE
            nullhdr % ma 
      END
))) AS nullhdr2     
   FROM
      null_headers 
)
, table_estimates AS 
(
       -- make estimates of how large the table should be
       -- based on row and page size
       
   SELECT
      schemaname,
      tablename,
      bs,
              reltuples::numeric as est_rows,
      relpages * bs as table_bytes,
          CEIL((reltuples*             (datahdr + nullhdr2 + 4 + ma -                 (
      CASE
         WHEN
            datahdr % ma = 0                     
         THEN
            ma 
         ELSE
            datahdr % ma 
      END
)                 ) / (bs - 20))) * bs AS expected_bytes,         reltoastrelid     
   FROM
      data_headers         
      JOIN
         pg_class 
         ON tablename = relname         
      JOIN
         pg_namespace 
         ON relnamespace = pg_namespace.oid             
         AND schemaname = nspname     
   WHERE
      pg_class.relkind = 'r' 
)
, estimates_with_toast AS 
(
       -- add in estimated TOAST table sizes
       -- estimate based on 4 toast tuples per page because we dont have
       -- anything better.  also append the no_data tables
       
   SELECT
      schemaname,
      tablename,
              TRUE as can_estimate,
              est_rows,
              table_bytes + ( coalesce(toast.relpages, 0) * bs ) as table_bytes,
              expected_bytes + ( ceil( coalesce(toast.reltuples, 0) / 4 ) * bs ) as expected_bytes     
   FROM
      table_estimates 
      LEFT OUTER JOIN
         pg_class as toast         
         ON table_estimates.reltoastrelid = toast.oid             
         AND toast.relkind = 't' 
)
,
table_estimates_plus AS 
(
   -- add some extra metadata to the table data
   -- and calculations to be reused
   -- including whether we cant estimate it
   -- or whether we think it might be compressed
       
   SELECT
      current_database() as databasename,
                  schemaname,
      tablename,
      can_estimate,
                  est_rows,
                  
      CASE
         WHEN
            table_bytes > 0                 
         THEN
            table_bytes::NUMERIC                 
         ELSE
            NULL::NUMERIC 
      END
                      AS table_bytes,             
      CASE
         WHEN
            expected_bytes > 0                 
         THEN
            expected_bytes::NUMERIC                 
         ELSE
            NULL::NUMERIC 
      END
                          AS expected_bytes,             
      CASE
         WHEN
            expected_bytes > 0 
            AND table_bytes > 0                 
            AND expected_bytes <= table_bytes                 
         THEN
(table_bytes - expected_bytes)::NUMERIC                 
         ELSE
            0::NUMERIC 
      END
      AS bloat_bytes     
   FROM
      estimates_with_toast     
   UNION ALL
       
   SELECT
      current_database() as databasename,
              table_schema,
      table_name,
      FALSE,
              est_rows,
      table_size,
              NULL::NUMERIC,
      NULL::NUMERIC     FROM no_stats 
)
,
bloat_data AS 
(
       -- do final math calculations and formatting
       
   select
      current_database() as databasename,
              schemaname,
      tablename,
      can_estimate,
              table_bytes,
      round(table_bytes / (1024 ^ 2)::NUMERIC, 3) as table_mb,
              expected_bytes,
      round(expected_bytes / (1024 ^ 2)::NUMERIC, 3) as expected_mb,
              round(bloat_bytes*100 / table_bytes) as pct_bloat,
              round(bloat_bytes / (1024::NUMERIC ^ 2), 2) as mb_bloat,
              table_bytes,
      expected_bytes,
      est_rows     
   FROM
      table_estimates_plus 
)
-- filter output for bloated tables
SELECT
   databasename,
   schemaname,
   tablename,
       can_estimate,
       est_rows,
       pct_bloat,
   mb_bloat,
       table_mb 
FROM
   bloat_data -- this where clause defines which tables actually appear
   -- in the bloat chart
   -- example below filters for tables which are either 50%
   -- bloated and more than 20mb in size, or more than 25%
   -- bloated and more than 1GB in size
WHERE
   (
      pct_bloat >= 50 
      AND mb_bloat >= 20 
   )
       
   OR 
   (
      pct_bloat >= 25 
      AND mb_bloat >= 1000 
   )
ORDER BY
   pct_bloat DESC;

Finding Unused Indexes

The following query will return any unused indexes which are not part of any constraint.

SELECT s.schemaname,
  s.relname AS tablename,
  s.indexrelname AS indexname,
  pg_relation_size(s.indexrelid) AS index_size
FROM pg_catalog.pg_stat_user_indexes s
  JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0  
  AND 0 <>ALL (i.indkey)  
  AND NOT i.indisunique  
  AND NOT EXISTS  
  (SELECT 1 FROM pg_catalog.pg_constraint c
  WHERE c.conindid = s.indexrelid)
  AND NOT EXISTS  
  (SELECT 1 FROM pg_catalog.pg_inherits AS inh
  WHERE inh.inhrelid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;

Check query performance

SELECT query,
       calls,
       total_time,
       total_time / calls as time_per,
       stddev_time,
       rows,
       rows / calls as rows_per,
       100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
WHERE query not similar to '%pg_%'
and calls > 500
--ORDER BY calls
--ORDER BY total_time
order by time_per
--ORDER BY rows_per
DESC LIMIT 20;

Check commit ratio of database

We must engage with the application team to determine why there are so many transaction rollbacks if the commit percentage is less than 95%. Consider a scenario where many of your transactions contain DML which can result in fragmentation.

SELECT 
  datname, 
  round(
    (
      xact_commit :: float * 100 /(xact_commit + xact_rollback)
    ):: numeric, 
    2
  ) as successful_xact_ratio 
FROM 
  pg_stat_database 
WHERE 
  datname not in (
    'postgres', 'template0', 'template1'
  );

Get the temp file usage of database

Use the log_temp_files parameter to log queries utilizing the temp files and modify the queries if you see that the temp files and bytes are high.

select 
  datname, 
  temp_files, 
  round(temp_bytes / 1024 / 1024, 2) as temp_filesize_MB 
from 
  pg_stat_database 
WHERE 
  datname not in (
    'postgres', 'template0', 'template1'
  ) 
  and temp_files > 0;

Frequency of Checkpoints

There are two important columns checkpoints_req, checkpoints_timed.

If the checkpoints_req is more than the checkpoints_timed, PostgreSQL is doing checkpoints due to the high WAL generation.

If the checkpoints are happening frequently it will cause more IO load on the machine so increase the max_wal_size parameter.

checkpoints_req > checkpoints_timed = (bad)PostgreSQL is doing checkpoints due to the high WAL generation.

Use the below query to find the frequency of the checkpoints.

Use below query to find the frequency of the checkpoints
WITH sub as (
  SELECT 
    EXTRACT(
      EPOCH 
      FROM 
        (now() - stats_reset)
    ) AS seconds_since_start, 
    (
      checkpoints_timed + checkpoints_req
    ) AS total_checkpoints 
  FROM 
    pg_stat_bgwriter
) 
SELECT 
  total_checkpoints, 
  seconds_since_start / total_checkpoints / 60 AS minutes_between_checkpoints 
FROM 
  sub;

Get top five tables with highest sequential scans

SELECT 
  schemaname, 
  relname, 
  seq_scan, 
  seq_tup_read, 
  seq_tup_read / seq_scan as avg_seq_tup_read 
FROM 
  pg_stat_all_tables 
WHERE 
  seq_scan > 0 
  and pg_total_relation_size(schemaname || '.' || relname) > 104857600 
  and schemaname IN ('"gameserver"')
ORDER BY 
  5 DESC 
LIMIT 
  20;

Cheers!

The measurement is based on transactions per second.  In other words – you’ll define how many transactions to execute per client and this will result in a rate per second.

Using pgbench for Benchmark Testing

1. Create a sample database

CREATE DATABASE example;

We now have an empty database named example. Let’s return to our bash shell to execute pgbench commands. We can do this by issuing the \q (quit) command.

2. Initialize the pgbench DB. This will create the sample tables in the DB

pgbench -h 10.190.30.209 -p 5432 -U jvalencia -i -s 50 example

In the command above, we ran pgbench with the -i option and the -s option followed by the database name (example).

The -i (initialize) option will tell pgbench to initialize the database specified. This will create the following tables within the example database.

The -s is the scaling option.  It is used to multiply the number of rows entered into each table. It will take the default rows and multiply it by 50 or whatever scaling number you entered.

3. Establish a baseline.

pgbench -h 10.190.30.209 -p 5432 -U jvalencia -c 10 -j 2 -t 10000 example

-c number of clients

-j number of threads

-t amount of transactions

These values are 10000 transactions per client (10 x 10000 = 100,000 transactions).

3. Get results

Below is a sample result set.

The part of the output that we are most interested in is the following:

tps = 2077.939220 (without initial connection time)

This indicates that our baseline is 2077 database transactions per second.

Cheers!

SELECT 
  round(
    (
      100 * total_exec_time / sum(total_exec_time) OVER ()
    ):: numeric, 
    2
  ) percent, 
  round(total_exec_time :: numeric, 2) AS total, 
  calls, 
  round(mean_exec_time :: numeric, 2) AS mean, 
  substring(query, 1, 200) 
FROM 
  pg_stat_statements 
ORDER BY 
  total_exec_time DESC 
LIMIT 
  10;

select queryid, left(query,40), calls, temp_blks_read,
temp_blks_written from pg_stat_statements order by
temp_blks_written desc;

Find the queries that are reading from temp the most

select queryid, left(query,40), calls, temp_blks_read,
temp_blks_written from pg_stat_statements order by temp_blks_read
desc;

Find the queries that are reading from disk the most

select queryid, left(query,40), calls, local_blks_read,
local_blks_read/calls as avg_read_per_call from pg_stat_statements
order by local_blks_read desc;

Find the queries with the highest execution times

select queryid, left(query,40), mean_exec_time, max_exec_time from
pg_stat_statements where calls > 10 order by mean_exec_time desc,
max_exec_time desc;

Cheers!

If you take a backup in plain format (.sql file), we can use psql to restore.

If you take a backup in a custom format (tar/dir/binary), the only option to restore is pg_restore.

pg_restore is 10x faster than psql.

When you do a backup using pg_dump, it will not apply any locks; hence, it will not cause any waits to the database traffic.

There are a few things to note before using pg_dump and planning your backup strategy:

• Backing up multiple databases is not possible using pg_dump. It can only backup one database at a time.
• pg_dump is not suitable for point-in-time recovery. To do a point-in-time recovery, WAL segments need to be replayed, but it will not be possible to replay transaction logs (WALs) after restoring backups taken using pg_dump.
• To ensure that all the data from the disk is readable and have no corrupted pages, it is important to have a periodical logical backup testing.

Backing up a single or several tables from a database is possible using pg_dump. However, backing up a table from one database and another table from another database simultaneously is not possible. pg_dump can only work on one database at a time. Furthermore, taking a backup of an entire database excluding one or more tables is also possible.

We shall use the flag -t to backup and restore a table.

Syntax for backing up a table:

pg_dump -h source_server -p source_port -U  database_name -Fformat -t tablename -f /location/dumpfile_name.bin

Syntax to restore a custom format dump:

pg_restore -h target_server -p target_port -U username -d database_name -t tablename /backup_location/dumpfile_name.bin

Syntax to restore a plain format dump:

psql -h target_server -p target_port -U username -d mypgdb -f /backup_dir/tables.sql

Example: Backing up one or more tables

1. In the example below, we are going to backup employees and salary tables.

Custom format:

pg_dump -t employees -t salary -Fc -f /postgres/backups/employees_salary.bin -v

Plain Format:

pg_dump -t employees -t salary -f /postgres/backups/employees_salary.sql -v

2. If you want to restore these tables to another database in a target server, then create the database first. You may ignore this step if the database already exists.

psql -c "CREATE DATABASE perf_review"

3. Now, we are going to restore the two tables from backup to another database called perf_review:

Custom format:

pg_restore -p5432 -U postgres -t employees -t salary -d perf_review /postgres/backups/employees_salary.bin -v

Plain Format:

psql -p5432 -U postgres -t employees -t salary -d perf_review -f /postgres/backups/employees_salary.sql

Cheers!