PostgreSQL includes a large collection of internal system tables and diagnostic views that expose how the database engine operates behind the scenes. While most developers interact only with familiar views like pg_stat_activity or pg_stat_database, PostgreSQL offers many lesser-known system tables that reveal deep insights into memory usage, authentication rules, encryption details, transaction internals, and write-ahead logging behavior.
In this post, we explore five powerful but rarely discussed PostgreSQL system tables:
- pg_shmem_allocations
- pg_hba_file_rules
- pg_stat_ssl
- pg_stat_slru
- pg_stat_wal
For each view, we run real queries, show sample output, explain every column in simple terms, and describe why the information matters. These examples come directly from a live PostgreSQL 18 environment, so you can paste the same commands into your own system to understand how PostgreSQL behaves internally.
1. pg_shmem_allocations
Purpose
pg_shmem_allocations exposes named allocations inside PostgreSQL’s main shared memory segment. This view is a wrapper over the internal function pg_get_shmem_allocations() and helps you see which subsystems have reserved memory inside the PostgreSQL cluster shared memory region.
How to read it
Run:
SELECT * FROM pg_shmem_allocations LIMIT 5;
Sample output (your server):
name | off | size | allocated_size
------------------------+------------+---------+----------------
subtransaction | 42363264 | 8553856 | 8553856
notify | 8849310080 | 133760 | 133760
Shared Memory Stats | 8849443840 | 315552 | 315648
pg_stat_functions hash | 9151289216 | 2896 | 2944
serializable | 8814577152 | 267424 | 267520
………………………………………………………………………………………….
You can also inspect the function definition and attributes:
\d+ pg_shmem_allocations
\df+ pg_get_shmem_allocations
What the columns mean
- name: logical name of the memory allocation (subsystem or structure).
- off: offset in the shared memory segment.
- size: configured or requested size for that allocation.
- allocated_size: actual size allocated (may align up to a boundary).
Relation to shared_buffers and effective_cache_size
- shared_buffers is a buffer pool configured in PostgreSQL that is allocated in shared memory. The buffer pool and its management structures are allocated in the cluster shared memory, so pg_shmem_allocations will show allocations for subsystems that implement and manage the buffer pool, but the exact allocation names may be implementation-specific (for example, BufferDescriptors, buffermgr structures, and related objects).
- pg_shmem_allocations is not a pledge that one row equals shared_buffers bytes exactly; several entries together represent memory used by the buffer manager and supporting structures.
- effective_cache_size is a planner configuration setting (a hint to the optimizer about how much OS cache + shared_buffers you expect to be available). It is not an actual memory allocation and therefore will not appear in pg_shmem_allocations.
- In short: pg_shmem_allocations shows real shared memory allocations (which typically include things related to shared_buffers), while effective_cache_size is a planner parameter only.
Practical checks and examples
1. Show all shared-memory allocations and total their allocated sizes:
SELECT sum(allocated_size) AS total_bytes
FROM pg_shmem_allocations;
2. Compare shared_buffers setting to observed buffer-related allocations (look for names like BufferDescriptors, shared_buffers or similar — the exact name may vary between versions/builds):
SHOW shared_buffers;
SELECT * FROM pg_shmem_allocations WHERE name ILIKE '%buffer%' OR name ILIKE '%Buffer%';
3. Use pg_get_shmem_allocations() directly (already wrapped by the view) to inspect internal allocations in detail when troubleshooting memory pressure.
Why this matters
- Detect unexpected large consumers of shared memory.
- Validate that memory sizing and OS limits (e.g. kernel SHM max) are sufficient.
- Troubleshoot crashes or failures related to insufficient shared memory.
2. pg_hba_file_rules
Purpose
pg_hba_file_rules() parses pg_hba.conf and returns the rules as rows. It’s a convenient, structured way to inspect authentication rules the server is using without opening the file yourself.
Basic query
SELECT * FROM pg_hba_file_rules();
Sample output (your server):
rule_number | file_name | line_number | type | database | user_name | address | netmask | auth_method | options | error
-------------+-------------------------------------+-------------+-------+---------------+------------+-----------+-----------------------------------------+---------------+---------+-------
1 | /etc/postgresql/18/main/pg_hba.conf | 118 | local | {all} | {postgres} | | | peer | |
2 | /etc/postgresql/18/main/pg_hba.conf | 123 | local | {all} | {all} | | | peer | |
3 | /etc/postgresql/18/main/pg_hba.conf | 125 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | scram-sha-256 | |
4 | /etc/postgresql/18/main/pg_hba.conf | 127 | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | scram-sha-256 | |
5 | /etc/postgresql/18/main/pg_hba.conf | 130 | local | {replication} | {all} | | | peer | |
6 | /etc/postgresql/18/main/pg_hba.conf | 131 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | scram-sha-256 | |
7 | /etc/postgresql/18/main/pg_hba.conf | 132 | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | scram-sha-256 | |
(7 rows)
You can view the view definition:
\d+ pg_hba_file_rules
\df+ pg_hba_file_rules
Columns explained
- rule_number: order in the file (line order).
- file_name: path to the pg_hba.conf used.
- line_number: approximate line number in the file.
- type: local/host/hostssl/hostnossl etc.
- database: array of databases the rule applies to.
- user_name: array of user names or roles matched.
- address: host address (for host records).
- netmask: netmask (for host records).
- auth_method: the authentication method used (peer, scram-sha-256, md5, trust, etc.)
- options: optional method-specific options.
- error: parse error if present.
Practical uses
- Validate that the effective authentication configuration matches what you expect.
- Quickly find rules that permit replication or local superuser access.
- Check whether IPv6 rules or localhost rules are present and in correct order.
- Detect conflicting or overlapping rules (order matters in pg_hba.conf).
Examples
- Find rules that allow trust (dangerous):
SELECT * FROM pg_hba_file_rules() WHERE auth_method ILIKE 'trust';
- See which line grants replication access:
SELECT rule_number, file_name, line_number, auth_method
FROM pg_hba_file_rules()
WHERE database @> ARRAY['replication'];
3. pg_stat_ssl
Purpose
pg_stat_ssl reports SSL/TLS information for each connected client that has a network connection (it derives data from pg_stat_get_activity). Use it to audit which backends use encryption, cipher details, and certificate info if client certificates are used.
Query
SELECT * FROM pg_stat_ssl;
Sample output (your server):
pid | ssl | version | cipher | bits | client_dn | client_serial | issuer_dn
---------+-----+---------+------------------------+------+-----------+---------------+-----------
1862484 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | | |
1875649 | f | | | | | |
1952626 | f | | | | | |
3441786 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | | |
2199982 | f | | | | | |
Columns explained
- pid: backend process id.
- ssl: boolean; true if the connection is encrypted.
- version: TLS version negotiated (e.g. TLSv1.3).
- cipher: negotiated cipher suite.
- bits: key bits or effective strength.
- client_dn: client certificate distinguished name (if client cert auth used).
- client_serial: client certificate serial (if present).
- issuer_dn: issuer distinguished name for the client certificate.
Practical uses
- Audit which sessions are encrypted and see the ciphers used.
- Detect clients connecting in plaintext (ssl = false) when you expect TLS everywhere.
- Check if weak TLS versions or ciphers are in use. If you detect TLSv1.0 or weak ciphers, update your server TLS config (postgreSQL build/OpenSSL settings) and enforce hostssl entries in pg_hba.conf.
Examples
- Show only non-SSL sessions with a remote client port (i.e. network connections not encrypted):
SELECT pid
FROM pg_stat_ssl s
JOIN pg_stat_activity a USING (pid)
WHERE s.ssl = false
AND a.client_addr IS NOT NULL;
- Count SSL vs non-SSL sessions:
SELECT ssl, count(*) FROM pg_stat_ssl GROUP BY ssl;
4. pg_stat_slru
Purpose
SLRU (Simple LRU) caches are used for small on-disk control structures such as commit status (clog), multi-transaction bookkeeping, and commit timestamps. pg_stat_slru reports hits/reads/writes and other activity on those SLRU caches.
Query
SELECT * FROM pg_stat_slru;
Sample output (your server):
name | blks_zeroed | blks_hit | blks_read | blks_written | blks_exists | flushes | truncates | stats_reset
------------------+-------------+----------+-----------+--------------+-------------+---------+-----------+----------------------------------
commit_timestamp | 0 | 0 | 0 | 0 | 0 | 6209 | 0 | 2025-11-16 08:41:01.725457+05:30
multixact_member | 1 | 5334 | 29 | 90 | 0 | 6247 | 0 | 2025-11-16 08:41:01.725457+05:30
multixact_offset | 1 | 5350 | 57 | 90 | 38 | 6247 | 0 | 2025-11-16 08:41:01.725457+05:30
notify | 3 | 115 | 0 | 0 | 0 | 0 | 0 | 2025-11-16 08:41:01.725457+05:30
serializable | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2025-11-16 08:41:01.725457+05:30
subtransaction | 77 | 17916 | 0 | 333 | 0 | 6209 | 6208 | 2025-11-16 08:41:01.725457+05:30
transaction | 3 | 45609796 | 41 | 482 | 0 | 6209 | 0 | 2025-11-16 08:41:01.725457+05:30
other | 0 | 0
Columns explained
- name: logical SLRU area (e.g., multixact_member, transaction, commit_timestamp, subtransaction, notify, and possibly other custom SLRUs).
- blks_zeroed: number of blocks zeroed (initialization).
- blks_hit: number of block cache hits.
- blks_read: blocks read from disk into the SLRU.
- blks_written: blocks written back to disk.
- blks_exists: number of blocks that already existed on disk.
- flushes: flush operations.
- truncates: truncation counts.
- stats_reset: timestamp when stats were last reset.
Why it matters
- Transaction ID wraparound and multi-transaction bookkeeping are critical to database correctness. SLRU pressure can lead to delays, IO, or autovacuum behavior. Monitoring pg_stat_slru helps identify pressure points and informs tuning (e.g. max_prepared_transactions, vacuum behavior, or investigating long-running transactions).
Example checks
- Identify SLRU areas with most reads:
SELECT name, blks_read, blks_written, blks_hit
FROM pg_stat_slru
ORDER BY blks_read DESC;
- If multixact_member shows lots of reads/writes, inspect long transactions and prepared transactions.
5. pg_stat_wal
Purpose
pg_stat_wal is available in modern PostgreSQL versions (15+). It provides statistics about WAL generation: how many WAL records are produced, how many full page images (FPI) are written, WAL bytes generated, and whether WAL buffers are becoming full. This is essential when examining write-heavy workloads and replication bandwidth needs.
Basic query
SELECT * FROM pg_stat_wal;
Typical columns (may vary by version) include:
- wal_records: number of WAL records generated.
- wal_fpi: full-page-image writes (caused by checks or certain WAL settings).
- wal_bytes: total size of WAL written in bytes.
- wal_buffers_full: count of times WAL buffers were full.
- stats_reset: when counters were reset.
How to use it
- To show current WAL activity:
SELECT wal_records, wal_fpi, wal_bytes, wal_buffers_full, stats_reset
FROM pg_stat_wal;
- To demonstrate WAL growth, create a large table:
CREATE TABLE big_table AS
SELECT i, md5(random()::text)
FROM generate_series(1, 500000) AS i;
After such an operation, wal_bytes and wal_records should increase noticeably. Repeat the query on pg_stat_wal to measure the delta.
Conclusion
These five views give you visibility into authentication, encryption, shared memory usage, transaction bookkeeping internals, and WAL generation. They are particularly valuable when you are troubleshooting production issues: authentication surprises, unexpected memory usage at startup, replication lag, or transaction/IO-related performance problems.
Add these views to your routine monitoring and incident playbooks. Each view is low-cost to query and yields high-leverage information that many teams overlook.