Replica Lag in PostgreSQL

Replica Lag in PostgreSQL

It can be calculated in two ways - 

1. Time 

2. Bytes

Time

SELECT

CASE

WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0

ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())::INTEGER

END

AS replication_lag;

replication_lag

-----------------

              28

(1 row)

Bytes

postgres=# SELECT client_addr, pg_wal_lsn_diff(pg_stat_replication.sent_lsn, pg_stat_replication.replay_lsn) AS byte_lag FROM pg_stat_replication;

client_addr | byte_lag

-------------+----------

127.0.0.1   |        0

(1 row)

postgres=#

Free Space Mapping Vs Visibility Map

Free Space Map ( FSM) Vs Visibility Map (VM)

Free Space Map ( FSM)

When inserting a heap or an index tuple, PostgreSQL uses the FSM of the corresponding table or index to select the page which can be inserted it. All tables and indexes have respective FSMs. Each FSM stores the information about the free space capacity of each page within the corresponding table or index file. All FSMs are stored with the suffix 'fsm', and they are loaded into shared memory if necessary. The extension pg_freespacemap provides the freespace of the specified table/index. 

Visibility Map ( VM)

Vacuum processing is costly; thus, the VM was been introduced in version 8.4 to reduce this cost. The basic concept of the VM is simple. Each table has an individual visibility map that holds the visibility of each page in the table file. The visibility of pages determines whether each page has dead tuples. Vacuum processing can skip a page that does not have dead tuples.

Suppose that the table consists of three pages, and the 0th and 2nd pages contain dead tuples and the 1st page does not. The VM of this table holds information about which pages contain dead tuples. In this case, vacuum processing skips the 1st page by referring to the VM's information.

Create Read only User in PostgreSQL ( RDS and On-Prem)

-- Create a group

CREATE ROLE readaccess;

-- Grant access to existing tables

GRANT USAGE ON SCHEMA public TO readaccess;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;

-- Grant access to future tables

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;

-- Create a final user with password

CREATE USER rahul WITH PASSWORD 'password';

GRANT readaccess TO rahul;

CREATE USER rahul_saha WITH PASSWORD 'password';

GRANT readaccess TO rahul_saha;

It is allowed by default for every user to create tables in public schema. If you want to mitigate this, do the following:

REVOKE ALL ON SCHEMA public FROM public

GRANT ALL ON SCHEMA public TO writeuser