PG_DATA Layout
A common location for PGDATA is /var/lib/pgsql/data
Layout of Files Associated with Tables and Indexes
A database is a subdirectory under the base subdirectory.
When the file size of tables and indexes exceeds 1GB, PostgreSQL creates a new file named like relfilenode.1 and uses it. If the new file is filled up, PostgreSQL will create another new file named like relfilenode.2 and so on.
The maximum file size of tables and indexes can be changed using the configuration, option “–with-segsize” when building PostgreSQL.
Tablespaces
A tablespace in PostgreSQL is an additional data area outside the base directory.
Use case:
Performance Optimization: For example, an index which is very heavily used can be placed on a very fast, highly available disk, such as an expensive solid state device1. At the same time, a table storing archived data which is rarely used or not performance critical could be stored on a less expensive, slower disk system.
PROCESS AND MEMORY ARCHITECTURE
Autovaccum
Postgres Server Process
A postgres server process is a parent of all processes in a PostgreSQL server
When start postgres server with pg_ctl, it allocates a shared memory area in memory, starts various background processes, starts replication-associated processes and background worker processes if necessary. Whenever it receives a connection request from a client, it starts a Backend Process. (The started backend process then handles all queries issued by the connected client.)
Backend Process
A backend process handles all queries issued by ONE connected client. Since a backend process is only allowed to operate on one database, you must explicitly specify the database you want to use when connecting to a PostgreSQL server.
Background Process
Query Processing
Parser
The parser only checks the syntax of an input when generating a parse tree. Therefore, it only returns an error if there is a syntax error in the query.
The parser does not check the semantics of an input query. For example, even if the query contains a table name that does not exist, the parser does not return an error. Semantic checks are done by the analyzer/analyser.