EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

PostgreSQL Architecture

Home » Data Science » Data Science Tutorials » PostgreSQL Tutorial » PostgreSQL Architecture

PostgreSQL Architecture

Introduction to PostgreSQL Architecture

As we know that PostgreSQL is the most advanced relational database management system. PostgreSQL architecture uses two models.

  • Client
  • Server

The client sends a request to the PostgreSQL server, and the PostgreSQL server responds to the client request. In typical application client and server is on different host in that scenario, they will communicate through TCP/IP network connection. PostgreSQL server handling multiple concurrent sessions from the client. To achieve this by starting a new process for each connection. From that point, the new server process and the client will communicate without another process intervention. PostgreSQL has its own background processes to manage the PostgreSQL server.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Architecture of PostgreSQL

The physical structure of PostgreSQL is straightforward; it consists of the following components:

  • Shared Memory
  • Background processes
  • Data directory structure / Data files

The below figure shows the PostgreSQL Architecture.

Shared Memory

1. Shared Memory

Shared memory refers to the memory reserved for transactional, and another log catches. Shared memory consist of the following components:

Shared Buffers

  • We need to set some amount of memory to a database server for the uses of shared buffers. The default value of shared buffers in 9.2 and the older version is 32 megabytes (32 MB) from 9.3, and the later default value of shared buffers is 128 megabytes (128 MB).
  • If we have a dedicated server for PostgreSQL, reasonable starting to set shared buffers value is 25% of total memory. The purpose of shared buffers is to minimize server DISK IO.

WAL Buffers

  • WAL buffers temporarily store changes in the database, which changes in the WAL buffers are written to the WAL file at a predetermined time. WAL buffers and WAL files are significant to recover the data at some peak of time at the time of backup and recovery.
  • The minimum value of shared buffers is 32 KB. If we set this parameter as wal_buffers = -1, it will set based on shared_buffers.

Work Memory

  • Set the specific memory to per client connections to be used by internal sort of operations and hash tables to writing data into temporary disk files.
  • The default value of work memory in 9.3 and the older version is 1 megabyte (1 MB) from 9.4, and the later default value of work memory is 4 megabytes (4 MB).

Maintenance Work Memory

  • We need to specify the maximum amount of memory for database maintenance operations such as VACUUM, ANALYZE, ALTER TABLE, CREATE INDEX, ADD FOREIGN KEY, etc.
  • The default value of maintenance work memory in 9.3 and the older version is 16 megabytes (16 MB) from 9.4, and later default value of maintenance work memory is 64 megabytes (64 MB).
  • It is safe to set maintenance work memory is large as compared to work memory. Larger settings will improve maintenance performance (VACUUM, ANALYZE, ALTER TABLE, CREATE INDEX, and ADD FOREIGN KEY, etc.) operations.

2. Background Processes

Below are the background processes of PostgreSQL. Each process has its individual features and PostgreSQL internals. Each process details will be described as follows:

Background Writer process: In PostgreSQL 9.1 version background writer regularly does the checkpoint processing. But in PostgreSQL 9.2 version checkpointer process was separated from the background writer process. It will keep logs and backup information up to date.

Popular Course in this category
PostgreSQL Course (2 Courses, 1 Project)2 Online Courses | 1 Hands-on Project | 7+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (2,948 ratings)
Course Price

View Course

Related Courses

WAL Writer: This process writes and flushes the WAL data on the WAL buffer to persistent storage periodically.

Logging Collector: This process also called a logger. It will write a WAL buffer to the WAL file.

autovacuum Launcher: When autovacuum is enabled, this process has the autovacuum daemon’s responsibility to carry vacuum operations on bloated tables. This process relies on the stats collector process for perfect table analysis.

Archiver: If we enable archive mode, this process has the responsibility to copy the WAL log files to a specified directory.

Stats Collector: In this process, Statistics information like pg_stat_activity and for pg_stat_database is collected. Information is passed from the stats collector via temporary files to requesting processes.

Checkpointer Process: In PostgreSQL 9.2 and later versions checkpoint process is performed. This process’s actual work is when a checkpoint occurs, it will write dirty buffer into a file.

Checkpointer: Checkpointer will write all dirty pages from memory to disk and clean the shared buffer area. If the PostgreSQL database is crashed, we can measure data loss between the last checkpoint time and PostgreSQL stopped time. The checkpoint command forces an immediate checkpoint when the command is executed manually. Only database superuser can call checkpoint.

The checkpoint will occur in the following scenarios:

  • The pages are dirty.
  • Starting and restarting the DB server (pg_ctl STOP | RESTART).
  • Issue of the commit.
  • Starting the database backup (pg_start_backup).
  • Stopping the database backup (pg_stop_backup).
  • Creation of the database.

3. Data Files / Data Directory Structure

  • PostgreSQL consists of multiple databases; this is called a database cluster. When we initialize PostgreSQL database template0, template1 and Postgres databases are created.
  • Template0 and template1 are template databases for new database creation of user it contains the system catalog tables.
  • The user database will be created by cloning the template1 database.

PGDATA directory contains several subdirectories, and control files are as follows.

  • pg_version: It contains database version information.
  • Base: Containing database subdirectories.
  • Global: Containing cluster wise tables such as pg_user.
  • pg_clog: Containing transaction commits status data.
  • pg_multixact: Containing multi transaction status data (used for shared row locks).
  • pg_notify: Containing LISTEN/NOTIFY status data.
  • pg_serial: Containing information about committed serializable transactions.
  • pg_snapshots: Containing exported snapshots.
  • pg_stat_tmp: Containing temporary files for the statistics subsystem.
  • pg_subtrans: Containing sub-transaction status data.
  • pg_tblspc: Containing symbolic links to tablespaces.
  • pg_twophase: Containing state files for prepared transactions.
  • pg_xlog: Containing WAL (Write Ahead Log) files.
  • pid: This file containing the current postmaster process ID (PID).

Conclusion

PostgreSQL architecture mainly divided into two models client and server. The client sends a request to the server; the PostgreSQL server process the data using shared buffers and background processes and send a response back to the client. The data directory contains the physical file of the PostgreSQL database server.

Recommended Articles

This has been a guide to PostgreSQL Architecture. Here we discuss the basic concept and architecture of PostgreSQL, respectively. You may also have a look at the following articles to learn more –

  1. Various Features of PostgreSQL
  2. How to Create Indexes in PostgreSQL?
  3. PostgreSQL Triggers | How to Work?
  4. PostgreSQL Views | How to Create?

PostgreSQL Course (2 Courses, 1 Project)

2 Online Courses

1 Hands-on Project

7+ Hours

Verifiable Certificate of Completion

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
PostgreSQL Tutorial
  • Basic
    • What is PostgreSQL
    • PostgreSQL Features
    • How to Install PostgreSQL
    • PostgreSQL Versions
    • PostgreSQL Architecture
    • PostgreSQL GUI
    • PostgreSQL Variables
    • PostgreSQL Data Types
    • PostgreSQL NOT NULL
    • PostgreSQL Integer
    • PostgreSQL Boolean
    • PostgreSQL NULLIF
    • PostgreSQL Administration
    • PostgreSQL Commands
    • PostgreSQL Operators
    • PostgreSQL IN Operator
  • Control Statement
    • PostgreSQL IF Statement
    • PostgreSQL if else
    • PostgreSQL CASE Statement
    • PostgreSQL LOOP
    • PostgreSQL For Loop
    • PostgreSQL While Loop
  • Joins
    • Joins in PostgreSQL
    • PostgreSQL Inner Join
    • PostgreSQL Outer Join
    • LEFT OUTER JOIN in PostgreSQL
    • PostgreSQL FULL OUTER JOIN
    • PostgreSQL LEFT JOIN
    • PostgreSQL Full Join
    • PostgreSQL Cross Join
    • PostgreSQL NATURAL JOIN
    • PostgreSQL UPDATE JOIN
  • Queries
    • PostgreSQL Queries
    • PostgreSQL WHERE Clause
    • PostgreSQL WITH Clause
    • PostgreSQL ORDER BY
    • PostgreSQL ORDER BY Random
    • PostgreSQL GROUP BY
    • PostgreSQL group_concat
    • PostgreSQL HAVING
    • PostgreSQL Recursive Query
  • Advanced
    • PostgreSQL Schema
    • Postgres List Schemas
    • PostgreSQL VARCHAR
    • Array in PostgreSQL
    • PostgreSQL DDL
    • PostgreSQL List Users
    • Postgres Default User
    • Postgres add user
    • PostgreSQL log_statement
    • PostgreSQL String Functions
    • PostgreSQL Compare Strings
    • PostgreSQL Text Search
    • PostgreSQL TEXT
    • PostgreSQL String Array
    • PostgreSQL Constraints
    • PostgreSQL UNIQUE Constraint
    • PostgreSQL INTERSECT
    • PostgreSQL Like
    • Cursors in PostgreSQL
    • PostgreSQL UNION ALL
    • Indexes in PostgreSQL
    • PostgreSQL Index Types
    • PostgreSQL REINDEX
    • PostgreSQL UNIQUE Index
    • PostgreSQL Clustered Index
    • PostgreSQL DROP INDEX
    • PostgreSQL DISTINCT
    • PostgreSQL FETCH
    • PostgreSQL RAISE EXCEPTION
    • PostgreSQL Auto Increment
    • Sequence in PostgreSQL
    • Wildcards in PostgreSQL
    • PostgreSQL Subquery
    • PostgreSQL Alias
    • PostgreSQL LIMIT
    • PostgreSQL Limit Offset
    • PostgreSQL LAG()
    • PostgreSQL Table
    • Postgres Show Tables
    • PostgreSQL Describe Table
    • PostgreSQL Lock Table
    • PostgreSQL ALTER TABLE
    • Postgres Rename Table
    • Postgres DROP Table
    • PostgreSQL Functions
    • PostgreSQL Math Functions
    • PostgreSQL Window Functions
    • Aggregate Functions in PostgreSQL
    • PostgreSQL Primary Key
    • Foreign Key in PostgreSQL
    • PostgreSQL Procedures
    • PostgreSQL Stored Procedures
    • PostgreSQL Views
    • PostgreSQL Materialized Views
    • Postgres Create View
    • PostgreSQL Triggers
    • PostgreSQL DROP TRIGGER
    • PostgreSQL Date Functions
    • PostgreSQL TO_DATE()
    • PostgreSQL Timestamp
    • PostgreSQL CURRENT_TIMESTAMP()
    • PostgreSQL Notify
    • PostgreSQL RANK()
    • PostgreSQL Select
    • PostgreSQL Average
    • PostgreSQL DATE_PART()
    • PostgreSQL EXECUTE
    • PostgreSQL COALESCE
    • PostgreSQL EXTRACT()
    • PostgreSQL Sort
    • PostgreSQL TO_CHAR
    • PostgreSQL Interval
    • PostgreSQL Number Types
    • PostgreSQL ROW_NUMBER
    • Alter Column in PostgreSQL
    • PostgreSQL Identity Column
    • PostgreSQL SPLIT_PART()
    • PostgreSQL CONCAT()
    • PostgreSQL replace
    • PostgreSQL TRIM()
    • PostgreSQL MAX
    • PostgreSQL DELETE
    • PostgreSQL Float
    • PostgreSQL OID
    • PostgreSQL log
    • PostgreSQL REGEXP_MATCHES()
    • PostgreSQL MD5 
    • PostgreSQL NOW()
    • PostgreSQL RANDOM
    • PostgreSQL round
    • PostgreSQL Trunc()
    • PostgreSQL TIME
    • PostgreSQL IS NULL
    • PostgreSQL CURRENT_TIME
    • PostgreSQL MOD()
    • Postgresql Count
    • PostgreSQL Datetime
    • PostgreSQL MIN()
    • PostgreSQL age()
    • PostgreSQL enum
    • PostgreSQL OR
    • PostgreSQL Wal
    • PostgreSQL NOT IN
    • PostgreSQL SET
    • PostgreSQL Current Date
    • PostgreSQL Compare Date
    • PostgreSQL SERIAL
    • PostgreSQL Database
    • PostgreSQL Clone Database
    • PostgreSQL Copy Database
    • PostgreSQL Restore Database
    • PostgreSQL DROP DATABASE
    • PostgreSQL ALTER DATABASE
    • Postgres DROP Database
    • Postgres Dump Database
    • PostgreSQL OFFSET
    • PostgreSQL GRANT
    • PostgreSQL COMMIT
    • PostgreSQL ROLLUP
    • PostgreSQL JSON
    • EXPLAIN ANALYZE in PostgreSQL
    • PostgreSQL Temporary Table
    • PostgreSQL cluster
    • PostgreSQL Replication
    • PostgreSQL Logical Replication
    • PostgreSQL flush privileges
    • PostgreSQL Tablespaces
    • CAST in PostgreSQL
    • PostgreSQL CTE
    • hstore in PostgreSQL
    • PostgreSQL DECODE()
    • PostgreSQL Vacuum
    • PostgreSQL EXCLUDE
    • Postgres Change Password
    • Postgres Delete Cascade
    • PostgreSQL EXCEPT
    • PostgreSQL Roles
    • PostgreSQL Link
    • PostgreSQL Partition
    • PostgreSQL column does not exist
    • PostgreSQL Log Queries
    • PostgreSQL escape single quote
    • PostgreSQL Query Optimization
    • PostgreSQL Character Varying
    • PostgreSQL Transaction
    • PostgreSQL Extensions
    • PostgreSQL Import CSV
    • PostgreSQL Client
    • PostgreSQL caching
    • PostgreSQL JDBC Driver
    • PostgreSQL Interview Questions
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2020 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you
Book Your One Instructor : One Learner Free Class

Let’s Get Started

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

Special Offer - PostgreSQL Course (2 Courses, 1 Project) Learn More