Postgresql Memory structure

                          MEMORY STRUCTURE IN POSTGRESQL 9.6.6



Memory architecture
Memory architecture in PostgreSQL can be classified into two broad categories:

Local memory area – allocated by each backend process for its own use.
Shared memory area – used by all processes of a PostgreSQL server.

Local memory area
Each backend process allocates a local memory area for query processing;
Each area is divided into several sub-areas – whose sizes are either fixed or variable.

work_mem Executor uses this area for sorting tuples by ORDER BY and DISTINCT operations, joining tables like merger_joins,hash_joins.
maintenance_work_mem Some kinds of maintenance operations (e.g., VACUUM, REINDEX) use this area.
temp_buffers Executor uses this area for storing temporary tables.

Shared memory area
A shared memory area is allocated by a PostgreSQL server when it starts up.
This area is also divided into several fix sized sub-areas

shared buffer PostgreSQL loads image copies pages within tables and indexes to here, and operates them directly.
WAL buffer To ensure that no data has been lost by server failures,wal refred to x_log

commit log keeps the states of all transactions (e.g., in_progress,committed,aborted)for Concurrency Control (CC) mechanism.

Shared_buffers (integer):-Paramter is Shared_buffers=2048

1.The database changes made here(update,delete,insert etc.).it change made only image copies.
2.The default is typically 128 megabytes (128MB),
3.The useful range for shared_buffers on Windows systems is generally from 64MB to 512MB.
4.Higher than the minimum are usually needed for good performance.
(Minimum 2GB is helpful and dedicated server means 20% to 40% best)
5.Sets the amount of memory the database server uses for shared memory buffers.

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

Wal-Buffer (integer).(Parameter is wal_buffer)

1.The default is 64 kilobytes (64kB).
2.It is mainly used for Recovery Purpose.
3.Whatever change's made to the database that information spool into wal buffer.
4.It is a ring buffer,write ahead log protocol mechanism will work here.
5.The data is written out to disk at every transaction commit.
6.This parameter can only be set at server start.
7.Dont set high of memory of this buffer area,if any typical transaction ne we can set high.

wal_level
 i) wal_level default is minimal which writes only the information needed to recover from a crash.
 ii)logical adds information necessary to support logical decoding.
 iii)replica adds logging required for WAL archiving as well as information required to run read-only queries on a standby server.
 In minimal level, WAL-logging of some bulk operations can be safely skipped(CTAS,CREATE INDEX,CLUSTER,COPY AS)
synchronous_commit (boolean)
Specifies whether transaction commit will wait for WAL records to be written to disk before the command returns a "success" indication to the client.
The default, and safe, setting is on.
When off, there can be a delay between when success is reported to the client and when the transaction is really guaranteed to be safe against a server crash.
The maximum delay is three times wal_writer_delay.)

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

Work Memory(integer)
1.Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files.
2.The value defaults to four megabytes (4MB).
3.Sort operations are used for ORDER BY, DISTINCT, and merge joins.
4.If work memory fill in memory level its go to temp sorting in disk level.we can see in explain plan.
5.Each operation will be allowed to use as much memory as this value specifies before it starts
6.The total memory used could be many times the value of work_mem;
7.Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN subqueries.
8.Note that for a complex query, several sort or hash operations might be running in parallel;
--------------------------------------------------------------------------------------------------------------------------

Maintenance_work_mem (integer)
1.Specifies the maximum amount of memory to be used by maintenance operations,
   EXAMPLE'S.such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY.
2.It defaults to 64 megabytes (64MB).
3.Since only one of these operations can be executed at a time by a database session,
4.It's safe to set this value significantly larger than work_mem.
5.Larger settings might improve performance for vacuuming and for restoring database dumps.

Note: that when autovacuum runs, up to autovacuum_max_workers times this memory may be allocated,
so be careful not to set the default value too high.
It may be useful to control for this by separately setting autovacuum_work_mem.
--------------------------------------------------------------------------------------------------------------------------

Effective_cache_size (integer). The default is 4 gigabytes (4GB).

1.Its store a index cache information it set na make better performance.
2.The effective size of the disk cache that is available to a single query and estimates of the cost of using an index;
3.A higher value makes it more likely index scans will be used,
4.A lower value makes it more likely sequential scans will be used.
5.This parameter has no effect on the size of shared memory allocated by PostgreSQL,
6.The system also does not assume data remains in the disk cache between queries.

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

Temp_buffers (integer)Parameters set temp_buffer to '10MB';
1.Sets the maximum number of temporary buffers used by each database session.
2.These are session-local buffers used only for access to temporary tables.
3.The default is eight megabytes (8MB). The setting can be changed within individual sessions,no affect on another sessions.
4.if need high amount of temp buffers set this parameters to high temp_buffers.
5.However if a buffer is actually used an additional 8192 bytes will be consumed for it (or in general, BLCKSZ bytes).

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

Autovacuum_work_mem (integer)
1.Specifies the maximum amount of memory to be used by each autovacuum worker process.
2.It defaults to -1, indicating that the value of maintenance_work_mem should be used instead.
3.The setting has no effect on the behavior of VACUUM when run in other contexts.

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

CLOG BUFFERS. (it is undo)
1.Commit Log(CLOG) keeps the states of all transactions(insert,update,delete)
 (e.g., in_progress,committed,aborted) for Concurrency Control (CC) mechanism.
2.The clog comprises one or more 8 KB pages in shared memory. The clog logically forms an array.
3.The indices of the array correspond to the respective transaction ids,
4.PostgreSQL holds the statuses of transactions in the Commit Log.
5.The Commit Log, often called the clog, is allocated to the shared memory, and is used throughout transaction processing.

Transaction status
PostgreSQL defines four transaction states, i.e. IN_PROGRESS, COMMITTED, ABORTED, and SUB_COMMITTED.
  EXAMPLE:-when a transaction is in progress, its status is IN_PROGRESS, etc.
           SUB_COMMITTED is for sub-transactions, and its description is omitted in this document.