Posts

PostgreSQL Vacuum and Vacuum full are not two different processes

-
  PostgreSQL’s   VACUUM   and   VACUUM FULL   are not separate processes but rather different operational modes of the same maintenance command. Here’s why: Core Implementation Both commands share the same underlying codebase and are executed through the  vacuum_rel()  function in PostgreSQL’s source code ( src/backend/commands/vacuum.c ). The key distinction lies in the  FULL  option, which triggers additional steps: Standard  VACUUM : Removes dead tuples (obsolete rows) and marks space reusable  within PostgreSQL Updates the visibility map to optimize future queries Runs concurrently with read/write operations VACUUM FULL : Rewrites the entire table into a new disk file, compressing it and reclaiming space for the  operating system Rebuilds all indexes and requires an  ACCESS EXCLUSIVE  lock, blocking other operations Key Differences in Behavior Aspect Standard VACUUM VACUUM FULL Space Reclamation Internal reuse onl...
Post a Comment
Read more

Predefined roles introduced in PostgreSQL 14 reduced my responsibilities

-
Image
  User management is a crucial role in the database, and the DBA should assign suitable permissions to users. For example, some users require read-only access to all tables, while others require read/write access (application user). Now, if I want to allow the read or read-write access to the user, I need to perform specific instructions on the database. Let's take example for read and read-write Read 1.  Grant select on all existing tables in schema to user 2.  Alter default privileges for feature tables 3.  Grant usage on schema   Read-Write   1.  Grant select,insert,delete,update on all existing tables in schema to user 2.  Alter default privileges for feature tables 3.  Grant usage on schema   If there are comparable requirements for additional users, it is preferable to construct separate roles with different permissions, such as read and write roles, and then grant the roles to users depending on the requirements.   I used to ...
Post a Comment
Read more

An Overview of PostgreSQL Backend Architecture

-
Image
  1. Introduction PostgreSQL backend is a collection of processes forked from the main process called  Postmaster . Each forked process has different roles and responsibilities in the backend. This article describes the responsibility of core backend processes that power the PostgreSQL system as we know it today. The overall PostgreSQL backend architecture can be illustrated by the image below: Postmaster is the first process to be started who has control of all the backend processes and is responsible for accepting and closing the database connections. At start up, the postmaster forks several backend processes that are intended to process different aspects of backend tasks, which we will be covering in this blog. When a user initiates a connection to the PostgreSQL database, the client process will send an authentication message to the Postmaster main process. The Postmaster main process authenticates the user according to the authentication methods configured and will fork ...
Post a Comment
Read more