1.3Database Structure

Earlier sections covered how to use the commands. This section goes underneath that, into how PostgreSQL actually works internally. Why does a large JSON column get stored outside the table row? Why does each connection spawn its own process? Why can you JOIN a table from another database as if it were local? Knowing the structure makes it far easier to reach the root cause of a performance problem or an incident.

1.3.1Physical layout and process architecture

• PostgreSQL uses a multi-process architecture (not multi-threaded). postmaster is the parent process launched first at cluster startup; it accepts connection requests and forks child processes. It never processes queries itself.
• A backend process (a postgres process) is forked by postmaster once per client connection and is what actually executes queries. Because each connection maps to exactly one process, high connection counts require attention to the max_connections ceiling and memory usage.
• Background processes are the resident helper processes: checkpointer (runs checkpoints), background writer (smooths out dirty-page writes), walwriter (flushes the WAL buffer to disk), autovacuum launcher (spawns autovacuum workers), and the stats collector / logical replication launcher.

1.3.2TOAST and FILLFACTOR

• TOAST (The Oversized-Attribute Storage Technique) splits values too large to fit in a single page (default 8KB)—long text, JSONB, etc.—into a separate TOAST table (in the pg_toast schema), optionally compressing them. The main row keeps only a reference pointer, so the row itself stays small.
• FILLFACTOR is the percentage of a page intentionally left empty at table-creation time (default 100 = pack it fully). Set it with CREATE TABLE t (...) WITH (fillfactor = 70);. On update-heavy tables, leaving room enables HOT (Heap-Only Tuple) updates, where the new row version fits on the same page—reducing the need to update indexes.
• autovacuum internals: the autovacuum launcher periodically checks each table's bloat level and forks an autovacuum worker to run VACUUM/ANALYZE on tables past the threshold. The worker count is capped by autovacuum_max_workers, and per-worker load is throttled via autovacuum_vacuum_cost_limit (cost-based delay).

1.3.3Foreign tables (FDW)

• FDW (Foreign Data Wrapper) lets SQL treat an external data source as if it were a local table. Key extensions: postgres_fdw (connects to another PostgreSQL server) and file_fdw (reads files like CSV).
• Setup happens in three stages: CREATE SERVER (defines the target host, port, dbname) → USER MAPPING (maps a local role to the remote credentials: CREATE USER MAPPING FOR local_user SERVER remote_srv OPTIONS (user 'x', password 'y')) → FOREIGN TABLE (the actual queryable foreign table definition via CREATE FOREIGN TABLE).
• postgres_fdw supports writes (INSERT/UPDATE/DELETE) too, and foreign tables can be joined with local ones. Because it involves remote-server communication, the planner's cost estimates and whether predicate pushdown (letting the remote side evaluate conditions) kicks in both affect performance.

Trace a common "why is this column slow" investigation back to the underlying structure. Suppose a table has a JSONB column holding large payloads (tens of KB). Since these do not fit in an 8KB page, the value automatically becomes a TOAST candidate and gets stored (optionally compressed) in a separate table, pg_toast.pg_toast_<oid>. The main row keeps only a pointer, so scanning the base table stays cheap as long as that column is not selected—but a query that actually fetches it incurs an extra TOAST-table access and added cost. Next, on a frequently updated table where index update cost is a concern, lowering FILLFACTOR (e.g., to 70) leaves free space on each page so the post-update new version is more likely to land on the same page. When that happens you get a HOT update, which skips updating index entries as long as the changed column is not indexed, improving write performance. From the process angle, if an application opens a large number of concurrent connections without a connection pool, one backend process is forked per connection, and you can hit the max_connections ceiling or exceed expected memory use (work_mem × number of concurrent queries). Investigate by counting backend processes with ps aux | grep postgres or cross-referencing pg_stat_activity. Finally, consider a scenario needing reporting across multiple PostgreSQL sites. After CREATE EXTENSION postgres_fdw;, define the target with CREATE SERVER remote_srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '10.0.0.5', dbname 'sales');, map the local role to the remote one with CREATE USER MAPPING FOR report_user SERVER remote_srv OPTIONS (user 'ro_user', password '*');, and finally define CREATE FOREIGN TABLE remote_orders (...) SERVER remote_srv OPTIONS (table_name 'orders');—after which a local SELECT can JOIN remote_orders as if it were a local table.

1.3.4Section summary

• postmaster (parent, forks only) → backend (per connection, executes queries) + background processes (checkpointer/background writer/walwriter/autovacuum launcher) — the multi-process architecture
• TOAST = out-of-line storage for large values / FILLFACTOR = intentional free space (lower values aid HOT updates)
• FDW setup order: CREATE SERVER → USER MAPPING → FOREIGN TABLE. postgres_fdw = another PostgreSQL server / file_fdw = external files