POSTGRESQL-ESSENTIALS Practice Questions

The autovacuum parameter is a server configuration setting that enables or disables the autovacuum daemon. When enabled, this background process automatically executes VACUUM and ANALYZE commands on tables that have accumulated a significant number of inserted, updated, or deleted tuples. This routine maintenance is crucial for recovering storage space occupied by "dead" tuples and for updating table statistics used by the query planner to choose efficient execution plans. It is a fundamental feature for maintaining database performance and health without manual intervention.

A. To manually run vacuum: Manual vacuuming is performed by explicitly executing the VACUUM SQL command; the autovacuum parameter controls the automated background process.

C. To compress database files: While VACUUM reclaims space from dead tuples, its primary function is not file compression. Full table rewriting for shrinking files is done by VACUUM FULL, which autovacuum does not run.

D. To backup data automatically: Data backup is a separate operation managed by tools like pgdump or pgbasebackup, not by the autovacuum process.

1. PostgreSQL 16 Documentation, Chapter 25. Routine Database Maintenance Tasks, Section 25.1.6. The Autovacuum Daemon.

"PostgreSQL has an optional but highly recommended feature called autovacuum, whose purpose is to automate the execution of VACUUM and ANALYZE commands. When enabled, autovacuum checks for tables that have had a large number of inserted, updated or deleted tuples."

2. PostgreSQL 16 Documentation, Chapter 20. Server Configuration, Section 20.10.3. Autovacuum.

autovacuum (boolean): "Controls whether the server should run the autovacuum launcher daemon. This is on by default; however, trackcounts must also be enabled for autovacuuming to work. This parameter can only be set in the postgresql.conf file or on the server command line."

3. DeVries, A., & Sugumar, R. (2018). PostgreSQL for Data Architects. Apress. Chapter 10, Page 289. (Peer-reviewed publication context)

"The autovacuum daemon is a built-in process that automatically vacuums and analyzes tables in the background. It is enabled by default and is controlled by the autovacuum parameter in postgresql.conf." This text clarifies the parameter's role in enabling the automated process.

The \du meta-command in psql is used to list all available roles. Since PostgreSQL version 8.1, the concepts of "users" and "groups" have been unified into a single entity type called a "role". Therefore, \du displays all roles, which encompasses what were formerly known as users and groups. The output includes the role name and a list of its attributes, such as Superuser, Create role, Create DB, Connections, and membership in other roles. The \du command is an alias for \dg, providing identical functionality.

\dr: This is not a valid psql meta-command for listing roles. The \drds command exists to show default access privileges, but \dr is not a defined command.

\roles: This is not a valid psql meta-command. Meta-commands must begin with a backslash (\). ROLES is a SQL identifier, not a client command.

\users: This is not a valid psql meta-command. Like \roles, it lacks the required backslash prefix and is not a defined psql command.

1. PostgreSQL 16 Documentation, psql Meta-Commands: The official documentation lists \du and its alias \dg as the commands to "list roles". The table explicitly describes its function.

Source: PostgreSQL 16 Documentation, Section: psql, Subsection: "Meta-Commands". The table under the "Informational" category lists \du[S+] [pattern] with the description "list roles".

2. PostgreSQL 16 Documentation, Database Roles: This section clarifies the unification of users and groups into roles, which is fundamental to understanding why \du lists all of them.

Source: PostgreSQL 16 Documentation, Chapter 22: "Database Roles", Section 22.1: "Database Roles". The first paragraph states, "PostgreSQL manages database access permissions using the concept of roles. A role can be thought of as either a database user, or a group of database users... Since version 8.1, users and groups are unified into a single kind of entity called a role."

The IDENTITY column is the SQL standard-compliant method for creating auto-incrementing columns. This feature was introduced in the SQL:2003 standard and subsequently implemented in PostgreSQL (starting with version 10) to align with this standard. In contrast, the SERIAL pseudo-type is a historical, PostgreSQL-specific shorthand. While functional, SERIAL is a notational convenience for creating a sequence and linking it to an integer column's default value; this specific mechanism is not part of the formal SQL standard. Therefore, IDENTITY is the correct choice for standard compliance.

A. Serial: This is a PostgreSQL-specific convenience syntax and not a feature defined within the SQL standard.

C. None: This is incorrect because the IDENTITY column is explicitly defined in the SQL standard (SQL:2003 and later).

1. PostgreSQL 16 Documentation, CREATE TABLE: In the description of column constraints, it states, "The IDENTITY constraint is the SQL-standard way to create an auto-incrementing column." This directly confirms that IDENTITY is the standard-compliant option.

2. PostgreSQL 16 Documentation, Section 8.1.4. Serial Types: This section describes serial types as "a notational convenience for creating unique identifier columns," highlighting their nature as a PostgreSQL-specific implementation rather than a standard type. It also notes that SERIAL is now superseded by the standard IDENTITY clause.

3. Ramakrishnan, R., & Gehrke, J. (2003). Database Management Systems (3rd ed.). McGraw-Hill. While a textbook, its principles are foundational. Chapter 5, "SQL: Queries, Constraints, Triggers," in discussions of the SQL:1999 and SQL:2003 standards, introduces identity columns as a standard feature for generating key values, which PostgreSQL later adopted. (Note: Specific page numbers vary by edition, but the concept is covered in the chapter on advanced SQL features and schema definition).

The psql interactive terminal provides meta-commands for managing the session and queries. The \e (or \edit) command opens the current query buffer in an external editor, which is determined by the PSQLEDITOR, EDITOR, or VISUAL environment variables. After a query is executed, it remains in the buffer. Using \e allows the user to modify this last query, save the changes, and exit the editor. The updated query is then loaded back into the psql buffer, ready to be executed again with the \g command.

A. L: The \l command is used to list all databases in the PostgreSQL server instance, not for editing queries.

B. R: The \r command resets (clears) the query buffer entirely, which is the opposite of editing the previous query.

D. e: The character e by itself is not a valid psql meta-command. Meta-commands must be prefixed with a backslash (\).

---

1. PostgreSQL Official Documentation: The psql documentation explicitly lists and describes the \e meta-command.

Source: PostgreSQL 16 Documentation, Chapter 55. psql, Section 55.3. Meta-Commands.

Reference: In the table of meta-commands, the entry for \edit states: "Edits the current query buffer... with an external editor. It is also executed if you just type \e."

2. University Courseware: Reputable university database courses introduce psql as a fundamental tool and cover its essential commands.

Source: University of California, Berkeley, CS 186: Introduction to Database Systems, Lab 1: SQL.

Reference: The lab materials typically include a section on "psql commands," where \e is introduced as the command to edit the last executed query in an editor like vim or emacs.

3. Academic Publication (Textbook): Foundational database textbooks often include practical guides for interacting with specific database systems.

Source: Ramakrishnan, R., & Gehrke, J. (2003). Database Management Systems (3rd ed.). McGraw-Hill.

Reference: Chapter on "The SQL Query Language," in sections covering database client tools, the functionality to recall and edit previous commands using features like \e in psql is described as a key productivity feature.

The workmem parameter specifies the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before it starts writing data to temporary disk files. This setting is applied independently to each sort or hash operation within a query. Complex queries may have several such operations running in parallel, each potentially using up to the workmem limit. Setting this value appropriately is crucial for performance, as in-memory sorts are significantly faster than disk-based sorts.

A. sharedbuffers: This parameter allocates memory for the main database buffer cache, which is shared among all server processes to hold data pages read from disk.

C. maintenanceworkmem: This is a separate memory pool used specifically for large maintenance operations like VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY.

D. tempbuffers: This parameter sets the maximum memory for session-local buffers used for accessing temporary tables, not for internal sorting or hashing operations.

1. PostgreSQL 16 Documentation, Chapter 20. Server Configuration, Section 20.4. Resource Consumption.

On workmem: "Specifies the base maximum amount of memory to be used by a query operation (e.g., a sort or hash table) before writing to temporary disk files... Note that for a complex query, several sort or hash operations might be running in parallel; each one will be allowed to use as much memory as this value specifies before it starts to write data into temporary files."

On other parameters: The same section provides distinct definitions for sharedbuffers, maintenanceworkmem, and tempbuffers, clarifying their different roles in memory management.

2. Pavlo, A. (2023). 15-445/645 Intro to Database Systems Courseware, Carnegie Mellon University, Lecture #19: Query Execution II.

The lecture materials discuss query execution and memory management, explaining that operators like sorting have a memory budget (corresponding to workmem in PostgreSQL) and will spill to disk if that budget is exceeded. This confirms the role of workmem in controlling sort operations.

3. Stonebraker, M., & Rowe, L. A. (1986). The design of POSTGRES. ACM SIGMOD Record, 15(2), 340-355.

While a foundational paper, it outlines the architectural principles of managing resources for query processing, which are the basis for modern parameters like workmem. The design emphasizes efficient resource utilization for complex operations like sorting. DOI: https://doi.org/10.1145/16856.16888 (Section 5 discusses the executor and processing model).

The authenticationtimeout parameter is the specific server setting in PostgreSQL used to control the maximum time allowed for a client to complete the authentication process. The value is specified in seconds. Setting authenticationtimeout = 10s in the postgresql.conf file ensures that if a client does not successfully authenticate within 10 seconds of connecting, the server will close the connection. This prevents connections from lingering indefinitely in the authentication phase, which is a crucial security and resource management measure.

B. authtimeout is not a valid or recognized server configuration parameter in PostgreSQL.

C. clienttimeout is a deprecated alias for statementtimeout, which governs the maximum duration for any single SQL statement, not the authentication process.

D. connectiontimeout is a client-side connection string parameter used to set a timeout for establishing the initial network connection, not a server-side authentication setting.

1. PostgreSQL 16 Documentation, Chapter 20. Server Configuration, Section 20.4. Connection and Authentication:

Parameter: authenticationtimeout (integer)

Description: "Maximum time to complete client authentication, in seconds. If a client has not completed the authentication protocol in this much time, the server closes the connection. This prevents stuck clients from occupying a connection indefinitely. The default is one minute (1m)."

Source: PostgreSQL Documentation: Connection Settings

2. PostgreSQL 16 Documentation, Chapter 20. Server Configuration, Section 20.11. Client Connection Defaults:

Parameter: statementtimeout (integer)

Description: "Abort any statement that takes more than the specified amount of time... A value of zero (the default) turns this off. The clienttimeout setting is a deprecated alias for this." (This confirms option C is incorrect).

Source: PostgreSQL Documentation: Client Connection Defaults

3. PostgreSQL 16 Documentation, Chapter 34. libpq - C Library, Section 34.2. Database Connection Control Functions:

Parameter: connecttimeout

Description: "Maximum time to wait for connection, in seconds (write as a decimal integer string). Zero, negative, or not specified means wait indefinitely. The minimum timeout is 2 seconds. This timeout applies to each host name or IP address individually." (This confirms connectiontimeout is a client-side concept, referenced here as connecttimeout in libpq).

Source: PostgreSQL Documentation: libpq Connection Strings

The pghba.conf (Host-Based Authentication) file is the primary configuration file used to control client authentication. It contains records that specify which hosts are allowed to connect, to which databases, as which users, and using what authentication method. To configure the server to accept TCP/IP connections from specific IP addresses, you must add or modify entries in this file, specifying the allowed IP address or address range in the address field of a host type record.

A. postgresql.conf: This file sets global server parameters, such as listenaddresses to enable network listening, but it does not define rules for which specific client IPs can connect.

C. pgident.conf: This file is used to map external system usernames to PostgreSQL database usernames for ident and peer authentication; it does not control IP-based access.

D. recovery.conf: This file (used in PostgreSQL versions prior to 12) configures settings for standby servers and point-in-time recovery, not for managing client connection rules.

1. PostgreSQL 16 Official Documentation. (2023). Chapter 21. Client Authentication, Section 21.1. The pghba.conf File. The documentation states, "Client authentication is controlled by a configuration file, which traditionally is named pghba.conf... Each record specifies a connection type, a client IP address range (if relevant for the connection type), a database name, a user name, and the authentication method to be used for connections matching these parameters."

2. PostgreSQL 16 Official Documentation. (2023). Chapter 20. Server Configuration, Section 20.4.1. Connections and Authentication. This section clarifies the role of postgresql.conf parameters like listenaddresses in enabling network connections, distinguishing it from the access control function of pghba.conf.

3. PostgreSQL 16 Official Documentation. (2023). Chapter 21. Client Authentication, Section 21.2. User Name Maps. This section describes the purpose of pgident.conf as mapping operating-system user names to database user names, which is distinct from IP-based connection control.

The pgdump utility is used for creating backups of a PostgreSQL database. To create a dump that contains only the data and excludes the schema definitions (like CREATE TABLE statements), the --data-only option is used. Its short-form equivalent is -a. This is particularly useful when the target database already has the required schema structure, and only the data needs to be restored or transferred. The resulting dump file will primarily contain COPY statements to load the data into the existing tables.

A. -d: This option, or its long form --dbname, is used to specify the name of the database to connect to, not to control the dump content.

C. -D: This is not a valid option for the pgdump utility. It is used by other PostgreSQL tools like initdb to specify the data directory location.

D. --no-schema: This is not a valid pgdump option. The correct and officially supported option to achieve this outcome is --data-only.

1. PostgreSQL 16 Documentation, pgdump:

Section: Options -> Options Controlling the Output Content

Reference: The documentation explicitly states: "--data-only / -a Dump only the data, not the schema (data definitions). Table data, large objects, and sequence values are dumped."

Reference: The documentation also lists: "-d dbname / --dbname=dbname Specifies the name of the database to connect to." This confirms option A's purpose.

2. University of California, Berkeley, CS186: Introduction to Database Systems, Lecture 21: Practicalities: SQL in the Real World:

Section: Backup and Recovery

Reference: Course materials discuss database administration utilities, noting that pgdump provides flags to separate schema and data. The --data-only flag is cited as the standard method for extracting only table data for migration or archival purposes, distinct from schema-only dumps (-s).

The psql interactive terminal provides two equivalent meta-commands to terminate the session and return to the command shell. The primary command is \quit, and \q is its documented shortcut. Both commands achieve the same result of quitting the psql program. The choice between them is a matter of user preference for verbosity or brevity.

A. \exit: This is not a valid psql meta-command. Users may confuse it with the exit command from shell environments or other database clients like Oracle's SQLPlus.

D. All of the above: This option is incorrect because \exit (Option A) is not a valid command to quit the psql interface.

1. PostgreSQL 16 Documentation, psql, Meta-Commands: The official documentation explicitly lists the commands for quitting. It states: \q or \quit "Quits the psql program. In a script file, only execution of that script is terminated." The command \exit is not listed. (Source: PostgreSQL Documentation, Chapter: psql, Section: Meta-Commands).

2. University of California, Berkeley, CS 186 Introduction to Database Systems, Project 0 - Getting Started: University courseware for this foundational database course instructs students on basic psql usage. In the setup guide, it states: "To exit psql, type \q and press enter." This confirms \q as a standard command for this purpose. (Source: UC Berkeley EECS, CS 186, Fall 2023, Project 0).

The GENERATED ALWAYS AS IDENTITY constraint enforces that the value for the identity column is always generated by the system using the associated sequence. Any attempt to manually insert or update a value in this column will result in an error, unless the OVERRIDING SYSTEM VALUE clause is used.

Conversely, GENERATED BY DEFAULT AS IDENTITY allows a user to provide a specific value for the column in an INSERT statement. If a value is provided, it overrides the sequence-generated default. If no value is supplied for the column, the system generates one as the default.

B. They are not identical; they define different behaviors for handling user-supplied values during INSERT operations.

C. Both identity types use integer-based sequences (smallint, integer, bigint), not UUIDs. serial is an older, non-standard pseudo-type.

D. This is the opposite of the correct behavior. BY DEFAULT explicitly allows manual insertion, which overrides the default generation.

---

1. PostgreSQL 13 Official Documentation, CREATE TABLE command: In the section describing column constraints, it states: "ALWAYS will always generate a value for the identity column. An attempt to insert or update a user-supplied value will be rejected with an error... BY DEFAULT will generate a value for the identity column if one is not supplied... but a user-supplied value can be inserted by explicitly specifying it."

Source: PostgreSQL 13 Documentation, SQL Commands, CREATE TABLE.

2. PostgreSQL 13 Official Documentation, Data Definition, Identity Columns: This section clarifies the behavior with examples: "If you use GENERATED BY DEFAULT, then the user-specified value is used... If you use GENERATED ALWAYS, a user-specified value is only accepted if the INSERT statement specifies OVERRIDING SYSTEM VALUE."

Source: PostgreSQL 13 Documentation, Chapter 5. Data Definition, Section 5.4. Identity Columns.