postgres_basics_memory.sql

--- MEMORY RELATED
--- wal_buffer
SHOW wal_buffers;
SELECT name,setting,unit,current_setting(name) FROM pg_settings WHERE name='wal_buffers';
--- unix: `getconf PAGE_SIZE``getconf _PHYS_PAGES` ... Linux: `ipcs -l` `sysctl kernel.sem`
--- initial parameters of PostgreSQL: https://pgtune.leopard.in.ua/#/
create extension pg_buffercache;
\dx pg_buffercache
--- pg_buffercache has several columns
--- bufferid, the block ID in the server buffer cache
--- relfilenode, which is the folder name where data is located for relation
--- reltablespace, Oid of the tablespace relation uses
--- reldatabase, Oid of database where location is located
--- relforknumber, fork number within the relation
--- relblocknumber, age number within the relation
--- isdirty, true if the page is dirty
--- usagecount, page LRU (least-recently used) count

--- number of buffers used by each relation of the current database
SELECT c.relname, count(*) AS buffers
FROM pg_buffercache b INNER JOIN pg_class c
ON b.relfilenode = pg_relation_filenode(c.oid) AND
b.reldatabase IN (0, (SELECT oid FROM pg_database
WHERE datname = current_database()))
GROUP BY c.relname
ORDER BY 2 DESC
LIMIT 10;

show data_directory;
select name,setting from pg_settings where category='File Locations';

--- segment is filled, and a new WAL segment is allocated
show max_wal_size;
--- time has passed since the last checkpoint
show checkpoint_timeout;
--- If WAL archiving is being used, use this instead of above
show archive_timeout;
--- Force a checkpoint
checkpoint;
--- Spread checkpoints, it is a parameter that makes PostgreSQL try to write the data slower – to finish in checkpoint_completion_target * checkpoint_timeout time.
show checkpoint_completion_target;