Unit 5: Introduction to PostgreSQL - Practice Quiz

PostgreSQL is an advanced open-source Object-Relational Database Management System (ORDBMS), which means it includes features of both relational databases and object-oriented databases.

ACID stands for Atomicity, Consistency, Isolation, and Durability. Consistency ensures that any transaction will bring the database from one valid state to another.

PostgreSQL is a powerful, open-source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability and performance.

PostgreSQL originated from the Ingres project at the University of California, Berkeley. It was initially named "Post-Ingres" to signify its development after Ingres.

By default, PostgreSQL server listens for connections on TCP port 5432. 3306 is for MySQL, 1433 for SQL Server, and 27017 for MongoDB.

The installation process typically creates a database superuser named postgres, which has full control over the database cluster.

psql is the official interactive terminal for PostgreSQL, allowing you to execute SQL queries, manage databases, and view results directly from the command line.

apt is the package manager for Debian-based systems. The install command is used to download and set up new packages like PostgreSQL.

The SELECT statement is the fundamental SQL command used for querying and retrieving data from one or more tables.

CREATE TABLE is a Data Definition Language (DDL) command used to create a new table, specifying its name, columns, and the data types for each column.

The WHERE clause is used to extract only those records that fulfill a specified condition. It filters rows before any groupings are made.

A semicolon (;) is the standard way to indicate the end of an SQL statement, allowing you to run multiple statements in a single batch.

The INSERT INTO statement is used to add new rows of data into a table, which represents the 'Create' part of CRUD. The CREATE keyword is used for defining objects like tables, not for adding data.

The SELECT statement is used to query the database and retrieve data, which corresponds to the 'Read' operation in CRUD.

The UPDATE statement is used to modify existing records in a table, fulfilling the 'Update' operation. ALTER is used to change the structure of a table itself.

The DELETE statement is used to remove one or more existing records from a table. The DROP command is used to remove an entire database object, like a table.

Prisma is a next-generation ORM (Object-Relational Mapper) for Node.js and TypeScript that simplifies database access and provides type-safety for your queries.

The schema.prisma file is the heart of a Prisma project. It contains the database connection details, the Prisma client generator, and the data model definitions.

prisma migrate dev compares your schema.prisma file with the database state, creates a new SQL migration file for the changes, and applies it to the database.

The prisma generate command reads your Prisma schema and regenerates the Prisma Client library. This ensures that the client is always in sync with your data models, providing accurate auto-completion and type-safety.

MVCC maintains multiple versions of a data row. When a transaction reads data, it sees a snapshot of the data as it existed at the start of the transaction, allowing other transactions to modify the data concurrently without blocking the read. This significantly improves concurrency in a multi-user environment.

A PostgreSQL schema is a namespace that contains named database objects like tables, views, and functions. Using a schema per tenant is a lightweight and effective way to achieve logical separation of data within a single database, preventing naming conflicts and simplifying data management for each tenant.

The pg_hba.conf (Host-Based Authentication) file is the primary configuration file for client authentication. To allow remote connections, you must add a new line specifying the database, user, connecting IP address (or range), and the authentication method (e.g., md5 or scram-sha-256).

The initdb command initializes a PostgreSQL database cluster. A cluster is a collection of databases managed by a single server instance. This process involves creating the directories where database data will live (the PGDATA directory), generating the shared system catalog tables, and creating the template1 and postgres default databases.

This is a classic 'greatest-n-per-group' problem. A window function like RANK() OVER (PARTITION BY department ORDER BY salary DESC) assigns a rank to each employee within their department based on salary. The outer query can then filter for all employees where the rank is 1.

DELETE removes rows one by one, firing any associated ON DELETE triggers for each row. TRUNCATE is a DDL operation that removes all rows from a table much more quickly by deallocating the table's data pages. Because it doesn't scan the rows, it does not fire row-level triggers.

A Common Table Expression (CTE) allows you to create a temporary, named result set that you can reference within the main query. This is ideal for breaking down complex queries into logical, readable steps. You can define the daily sales aggregation in a CTE, and then the main query can reference that CTE to calculate the moving average.

PostgreSQL provides the ON CONFLICT clause to handle unique constraint violations. ON CONFLICT (column_name) DO NOTHING specifies that if the INSERT would violate the unique constraint on the email column, the command should silently do nothing instead of raising an error.

The RETURNING clause is a PostgreSQL extension that can be appended to INSERT, UPDATE, and DELETE statements. It allows the statement to return values from the rows that were affected by the command, avoiding the need for a second database query.

ON DELETE RESTRICT (and the similar NO ACTION) prevents the deletion of a row in the referenced table if any rows in the referencing table still point to it. This is the most restrictive and often the safest default behavior for maintaining referential integrity.

Standard alphabetical sorting of priority would result in 'High', 'Low', 'Medium'. To enforce a custom sorting logic, a CASE statement in the ORDER BY clause is the most standard and effective SQL approach. It maps the string values to a numerical order, ensuring the correct sequence, before applying the secondary sort on creation_date.

For a one-to-many relationship in Prisma, the 'many' side (Post) holds the foreign key (authorId). The @relation attribute explicitly links this foreign key to the primary key (id) of the User model. The User model contains a field with a list type (Post[]) to represent the 'many' side of the relation for easy querying.

prisma migrate dev is a key command in the development workflow. It introspects the current state of the database, compares it to the schema.prisma file, generates a new, timestamped SQL migration file with the necessary changes, and then executes that file against the development database to keep them in sync.

Prisma's include clause allows for nested filtering and pagination on related models. To get the 10 most recent posts, you can add options like orderBy: { createdAt: 'desc' } and take: 10 to the posts relation within the include object, making for a powerful and expressive single query.

prisma db pull is used for database introspection. It's essential when working with an existing database ('brownfield' project). The command connects to the database, analyzes its tables, columns, and relations, and generates Prisma models in your schema.prisma file that accurately reflect the database's structure.

Prisma Client automatically wraps nested writes in a transaction. You can create a user and their related profile by nesting a create operation for the profile inside the data object of the user.create call. This ensures that both records are created successfully, or neither is, maintaining data integrity.

For jsonb data, PostgreSQL provides a set of powerful operators. The @> (contains) operator checks if the left JSON/JSONB value contains the right JSON/JSONB value. To check if a JSON array contains a specific string element, you use tags @> '"electronics"'. Note the double quotes inside the single-quoted string, which are required to define it as a JSON string value.

In PostgreSQL, every new database is created by cloning an existing template database. By default, the CREATE DATABASE command clones template1. This means any objects (tables, functions, etc.) you create in template1 will automatically appear in any new database created thereafter, making it useful for setting up a standard database environment.

psql is the interactive terminal for PostgreSQL. It allows you to type in queries interactively, issue them to PostgreSQL, and see the query results. It also has a rich set of meta-commands (like \dt to list tables) that make database administration easier.

By default, ORDER BY ... DESC places NULL values first. However, to be explicit and guarantee this behavior across different database versions or configurations, NULLS FIRST should be used. This clause sorts the non-null timestamps in descending (most recent first) order and ensures that all NULL values are grouped together at the top of the result set.

While the SQL standard for REPEATABLE READ allows phantom reads (seeing new rows inserted by other committed transactions), PostgreSQL's implementation is stricter. It uses Multi-Version Concurrency Control (MVCC) to provide the transaction with a consistent snapshot of the database as of the moment the first statement in the transaction began. Therefore, T1 will not see the new row inserted by T2, effectively preventing phantom reads at this isolation level. A serialization failure is more characteristic of the SERIALIZABLE level when a dependency conflict is detected.

While table bloat and stale statistics are serious performance problems caused by a lagging VACUUM, the most critical, catastrophic failure is transaction ID wraparound. PostgreSQL uses a 32-bit transaction ID (XID). After approximately 4 billion transactions, these IDs wrap around. VACUUM's critical job is to 'freeze' the XIDs of very old rows, marking them as visible to all future transactions. If autovacuum cannot keep up and the number of unfrozen XIDs approaches the limit, PostgreSQL will initiate a protective shutdown, refusing all new write transactions to prevent data corruption. This is a hard stop, unlike the gradual performance degradation of other symptoms.

The primary purpose of a checkpoint is to create a known-good point in time on disk. It forces all 'dirty' data buffers (pages in memory that have been modified) to be written to the data files on disk. The location of the checkpoint record in the WAL is then saved. In case of a crash, PostgreSQL recovery knows it only needs to replay WAL entries that occurred after the last completed checkpoint, significantly speeding up the recovery process. It doesn't pause all writes; it's a background process designed to minimize impact.

The main advantage of the multi-process model is robustness. Each connection has its own OS process and isolated memory space. A crash in one backend process (e.g., due to a faulty C extension) will not affect the postmaster or other backends. The trade-off is that forking a process is more resource-intensive (in both memory and CPU time) than starting a thread. This makes establishing new connections relatively expensive, which is why external connection poolers are essential for applications with high connection churn.

work_mem is the most critical parameter here, as it defines the amount of memory available to each individual sort, hash join, or other memory-intensive operation within a query before it spills to disk. Increasing it directly reduces temporary file I/O. shared_buffers is the second most important, as it determines the amount of memory dedicated to caching data pages from disk. A larger shared_buffers can reduce physical I/O for the large table scans common in data warehousing. maintenance_work_mem is for tasks like VACUUM and CREATE INDEX, not user queries. effective_cache_size is just a hint to the planner.

PostgreSQL processes pg_hba.conf by searching for the first record that matches the connection type, client address, requested database, and user name. The connection from 192.168.1.50 as user 'admin' to database 'sales' is checked against the rules sequentially:

1. The first line (host all all 192.168.1.0/24...) is a match because the IP 192.168.1.50 falls within the 192.168.1.0/24 range, and 'all' matches both the database and user.
   Since a match is found, PostgreSQL stops processing the file and uses the method from this first matching line (scram-sha-256). The second, more specific reject rule is never evaluated.

checkpoint_completion_target is a fraction (from 0 to 1.0) of the total time between checkpoints. This interval is determined by whichever trigger comes first: checkpoint_timeout (time since last checkpoint) or when WAL files occupy max_wal_size on disk. A value of 0.9 tells PostgreSQL to pace the I/O operations (writing dirty buffers to disk) so that the checkpoint finishes when 90% of the interval to the next predicted checkpoint has passed. This smooths out the I/O load, reducing performance dips caused by intense, short bursts of write activity.

A local connection in pg_hba.conf refers to a connection made via a Unix domain socket, not a TCP/IP network socket. When the client and server are on the same machine, using a Unix socket is more efficient as it bypasses the overhead of the TCP/IP stack. It is also inherently more secure as it's a file-system-based communication channel and not exposed to the network. An application in a Docker container can connect to a PostgreSQL instance on the host by mounting the host's socket directory (e.g., /var/run/postgresql) into the container. Using host with a TCP/IP address would be less performant.

This problem requires comparing a row with the immediately preceding row within the same group (user). The LAG() window function is designed precisely for this. The query structure would be: 1. Use a CTE or subquery. 2. Inside it, SELECT user_id, view_timestamp - LAG(view_timestamp, 1) OVER (PARTITION BY user_id ORDER BY view_timestamp) AS time_diff. 3. In the outer query, calculate AVG(time_diff) grouped by user_id. This is the most direct and performant approach. A self-join or recursive CTE would be far more complex and inefficient.

This problem is a perfect use case for window functions. The RANK() function can be used with different OVER() clauses within the same SELECT statement. RANK() OVER (PARTITION BY department ORDER BY salary DESC) calculates the rank within each department group. RANK() OVER (ORDER BY salary DESC) omits the PARTITION BY clause, so it calculates the rank over the entire result set (the whole company). This single query is highly efficient as it requires only one scan of the table. The correlated subquery approach (Option B) is extremely inefficient, and the other options are syntactically or logically incorrect.

This question tests the subtle but critical differences between these ranking functions. For a list of values like (100, 90, 90, 80):

• ROW_NUMBER() assigns a unique, consecutive integer to each row regardless of ties: 1, 2, 3, 4.
• RANK() gives ties the same rank, but then skips the next rank(s). The ranks would be: 1, 2, 2, 4 (rank 3 is skipped).
• DENSE_RANK() gives ties the same rank but does not skip subsequent ranks. The ranks would be: 1, 2, 2, 3.
  Understanding this distinction is key to choosing the correct function for a given analytical task.

While all four options can potentially yield the correct product_ids, the array aggregation approach is a uniquely powerful and often highly performant PostgreSQL feature for this type of set-based query within a group. It aggregates all tags for a product into an array and then uses PostgreSQL's rich array operators: @> (contains) to check for the required tags and && (overlaps) to check for the forbidden tag. This approach often results in a more efficient query plan (a single scan and group) compared to multiple joins or subqueries, and it is arguably the most expressive and scalable solution for complex tag-based filtering.

This question tests a subtle aspect of ON CONFLICT. The goal is to always increment by 1. In the DO UPDATE clause, you can reference the existing row's values (using the table name, e.g., counters.value) and the values from the row that was proposed for insertion (using the EXCLUDED pseudo-table). Option D, counters.value + EXCLUDED.value, would add the existing value to the proposed value (1), which is correct. However, Option B, counters.value + 1, is simpler and also correct for this specific case, as the increment is always a constant 1. Option A is incorrect because value in SET value = value + 1 is ambiguous. Option C is not atomic and is vulnerable to race conditions. Between B and D, both work, but B is arguably more direct for a fixed increment. Let's make B the canonical answer for its simplicity. EXCLUDED.value would be the same as the VALUES clause's value, which is 1. So counters.value + EXCLUDED.value is identical to counters.value + 1 in this context. B is a perfectly valid and slightly more concise way of expressing the fixed increment.

This is the most efficient and atomic solution. The WHERE id = 123 AND deleted_at IS NULL clause ensures that the UPDATE statement only finds and locks a row that has not yet been soft-deleted. If another process deletes it first, the WHERE clause for this query will not match any rows, and the update will affect zero rows. The RETURNING id clause will then return an empty result set, which the application can use to understand that it did not perform the deletion. This single statement is fully atomic and avoids the complexity and potential deadlocks of explicit locking with SELECT FOR UPDATE (Option B), as well as the race conditions inherent in separate UPDATE and SELECT statements (Options C and D).

A data-modifying CTE allows you to place a DML statement (INSERT, UPDATE, DELETE) within a WITH clause. By adding a RETURNING clause to this DML statement, you can capture the rows that were affected. This result set can then be used by the main part of the query. For moving rows, you can have a CTE like WITH moved_rows AS (DELETE FROM source_table WHERE ... RETURNING *). The main query can then be INSERT INTO destination_table SELECT * FROM moved_rows;. The entire process is executed as a single, atomic command, which is far safer and often more performant than using separate DELETE and INSERT commands within a transaction block.

The DELETE FROM table_a USING table_b syntax is a PostgreSQL extension for performing joins in a DELETE operation. The table specified immediately after DELETE FROM (here, employees_staging) is the target for deletion. The USING clause introduces other tables to be used in the WHERE clause for filtering. Therefore, this statement joins the staging and production tables on employee_id and deletes only the rows from the staging table that were part of the successful join.

When combining conditions across different relations in Prisma, using the explicit AND operator is the most robust and clear method. The query correctly breaks down the logic: Find posts where there is an AND condition. The first condition is that in the related tags (the join table), some (at least one) of the related tag entities must have a name that starts with 'tech'. The second condition is that the related author must have an email that contains '@example.com'. While the implicit AND (Option B) often works, using an explicit AND: [] array is the canonical way to combine complex relational filters and avoids ambiguity. Option C (every) is incorrect as it would require all tags to start with 'tech'. Option D has incorrect syntax for relational filtering.

This scenario requires conditional logic (calculating cost based on a read value) within a transaction. The sequential transaction API (prisma.$transaction([...])) is unsuitable because it executes a predefined array of operations without allowing for intermediate application logic. The interactive transaction (prisma.$transaction(async (tx) => { ... })) is the perfect solution. It provides a transaction-scoped Prisma client instance (tx) to the callback function. Inside this function, you can await read operations (e.g., tx.user.findUnique), perform JavaScript logic (calculate shipping), and then await write operations (tx.order.create, tx.product.update). Prisma guarantees that all operations within the callback are executed in a single, atomic database transaction. If the callback function throws an error at any point, the entire transaction is automatically rolled back.

The shadow database is a core concept for ensuring the safety and reliability of Prisma Migrate. When you run prisma migrate dev, Prisma creates (or uses an existing) temporary, empty database. It then applies all existing migration files to this shadow database to reconstruct the schema from its history. This serves two critical purposes: 1) It validates that the migration history is sound and can be replayed from scratch. 2) It compares the resulting schema of the shadow database with the current state of your main development database to detect 'drift' (manual changes made to the database that are not recorded in a migration). If drift is detected, Prisma will warn you and prompt for a resolution, preventing the application of new migrations to an inconsistent database state.

Prisma's schema allows for both bi-directional and uni-directional relations. A full bi-directional relation would have author User @relation(...) on the Post model and posts Post[] on the User model. If you omit posts Post[], the foreign key and the relation still exist in the database. You can query a Post and include: { author: true }. However, you lose the ability to start a query from a User and traverse to their posts, for example: prisma.user.findUnique({ where: { id: 1 }, include: { posts: true } }). This query would not be possible without the posts Post[] back-relation field. The field is essential for defining the relation's accessibility in the generated Prisma Client API.