Larger settings will improve maintenance performance (VACUUM, ANALYZE, ALTER TABLE, CREATE INDEX, and ADD FOREIGN KEY, etc.) operations.īelow are the background processes of PostgreSQL. It is safe to set maintenance work memory is large as compared to work memory.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).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 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).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.If we set this parameter as wal_buffers = -1, it will set based on shared_buffers. The minimum value of shared buffers is 32 KB.WAL buffers and WAL files are significant to recover the data at some peak of time at the time of backup and recovery. WAL buffers temporarily store changes in the database, which changes in the WAL buffers are written to the WAL file at a predetermined time.The purpose of shared buffers is to minimize server DISK IO. If we have a dedicated server for PostgreSQL, reasonable starting to set shared buffers value is 25% of total memory.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). We need to set some amount of memory to a database server for the uses of shared buffers.Shared memory consist of the following components: Shared memory refers to the memory reserved for transactional, and another log catches. The below figure shows the PostgreSQL Architecture. The physical structure of PostgreSQL is straightforward it consists of the following components: Hadoop, Data Science, Statistics & others Architecture of PostgreSQL