Unlock hundreds more features
Save your Quiz to the Dashboard
View and Export Results
Use AI to Create Quizzes and Analyse Results

OR
Sign inSign in with Facebook
Sign inSign in with Google
Quizzes > Quizzes for Business > Technology

Master the PostgreSQL Knowledge Assessment Quiz

Sharpen Your PostgreSQL Database Skills Now

Difficulty: Moderate
Questions: 20
Learning OutcomesStudy Material
Colorful paper art depicting a PostgreSQL Knowledge Assessment Quiz

This PostgreSQL Knowledge Assessment Quiz is crafted to challenge your database skills and deepen your understanding of core PostgreSQL concepts. Ideal for developers, DBAs, and students, it blends practical SQL query problems with real-world scenarios to elevate your expertise. Explore our Knowledge Assessment Quiz series or dive into the IT Knowledge Assessment Quiz for broader coverage. All questions are freely customizable in the editor, so you can adapt and refine the content to fit your learning objectives. Visit our quizzes library for more assessments and continuous skill improvement.

Which PostgreSQL command displays the estimated execution plan of a query without running it?
DESCRIBE QUERY
EXPLAIN
SHOW PLAN
EXPLAIN ANALYZE
EXPLAIN shows the estimated plan for a query without executing it, which helps in understanding potential performance. EXPLAIN ANALYZE executes the query and provides actual run times.
Which PostgreSQL data type is most suitable for storing variable-length Unicode text of arbitrary size?
VARCHAR2
TEXT
BYTEA
CHAR(255)
TEXT stores variable-length Unicode text without a specified limit and is ideal for large or unpredictable text sizes. CHAR reserves fixed space and VARCHAR2 is Oracle-specific.
Which SQL statement correctly creates a non-unique index on the 'username' column of the 'users' table?
CREATE UNIQUE INDEX idx_users_username ON users username;
ALTER TABLE users ADD INDEX (username);
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_username (username) ON users;
The correct syntax is CREATE INDEX index_name ON table(column). This creates a non-unique index on 'username'. Other options either misuse syntax or define uniqueness incorrectly.
Which command begins an explicit transaction block in PostgreSQL?
BEGIN;
COMMIT;
ROLLBACK;
SAVEPOINT;
BEGIN starts a transaction block in PostgreSQL. COMMIT ends the transaction, ROLLBACK undoes it, and SAVEPOINT sets a restore point within a transaction.
Which tool is recommended for taking a physical backup of an entire PostgreSQL cluster?
pg_basebackup
pg_dumpall
pg_restore
pg_dump
pg_basebackup creates a physical backup of the entire cluster, including data directory files. pg_dump and pg_dumpall are logical backup tools, and pg_restore restores logical dumps.
Which PostgreSQL command provides actual execution times along with the plan?
ANALYZE QUERY
EXPLAIN ANALYZE
EXPLAIN
SHOW ANALYZE
EXPLAIN ANALYZE executes the query and returns the execution plan with actual run times. EXPLAIN alone only shows estimated costs without real timings.
A composite b-tree index is defined on columns (col_a, col_b). Which query can use this index efficiently?
SELECT * FROM t WHERE col_a = 10;
SELECT * FROM t WHERE col_b = 20 AND col_c = 30;
SELECT * FROM t WHERE col_b = 20;
SELECT * FROM t WHERE col_c = 30;
A b-tree index on (col_a, col_b) can be used when filtering on col_a or both col_a and col_b. Queries filtering only on col_b cannot use the index prefix.
What lock does 'SELECT ... FOR UPDATE' acquire on returned rows?
Row-level exclusive lock
Table-level shared lock
Share update exclusive lock
Access exclusive lock
SELECT ... FOR UPDATE obtains a row-level exclusive lock on each returned row to prevent other transactions from modifying them. It does not lock the entire table.
Prior to PostgreSQL 12, what is the default behavior of a CTE (WITH query) regarding optimization?
It acts as an optimization fence and is materialized
It uses temporary tables only when specified
It parallelizes subqueries automatically
It is always inlined into the main query
Before version 12, CTEs are treated as optimization fences, meaning the planner materializes their results before executing the main query, potentially impacting performance.
Which index type is most suitable for efficient JSONB containment queries (e.g., data @> '{"key":"value"}')?
SP-GiST
GIN
BTREE
HASH
GIN indexes are optimized for containment and existence queries on JSONB data. B-tree and hash indexes do not support the required operations efficiently.
Which clause allows handling of duplicate key conflicts during INSERT?
ON CONFLICT
IF NOT EXISTS
ON DUPLICATE KEY UPDATE
UPSERT
PostgreSQL uses the ON CONFLICT clause to specify actions (DO NOTHING or DO UPDATE) when an insert violates a unique constraint. UPSERT is a generic term.
What is the effect of running VACUUM FULL on a table?
Reclaims space by rewriting the table and takes an exclusive lock
Analyzes table statistics without modifying data
Only marks tuples as dead but does not reclaim space
Prevents transaction ID wraparound without locking
VACUUM FULL rewrites the entire table to compact it, reclaiming disk space, and acquires an exclusive lock. Regular VACUUM only marks dead tuples without compacting file size.
Which function converts text to a tsvector for full-text search indexing?
ts_rank
to_tsvector
plainto_tsquery
to_tsquery
to_tsvector parses input text into a tsvector suitable for indexing. to_tsquery and plainto_tsquery convert query strings into tsquery format for searching.
Which backup strategy in PostgreSQL supports point-in-time recovery?
Continuous archiving of WAL files
Logical replication
pg_dump full backup
Streaming SELECT dumps
Point-in-time recovery requires archiving WAL segments continuously so you can replay transactions up to a specific time. Logical backups cannot provide PITR.
Which index configuration speeds up LIKE 'prefix%' pattern searches?
GIN with jsonb_ops
HASH index
BTREE with varchar_pattern_ops
BRIN index
BTREE indexes combined with the varchar_pattern_ops operator class support efficient prefix pattern searches. Other index types are not optimized for this use case.
Which join algorithm is chosen by PostgreSQL when both inputs are already sorted on join keys?
Merge Join
Hash Join
Nested Loop Join
Recursive Join
When both input sets are sorted on the join keys, PostgreSQL prefers a merge join because it can efficiently merge the two sorted streams. Hash joins require hashing structures.
Which operator class allows a B-tree index to support case-insensitive pattern matching for text columns?
text_pattern_ops
default
varchar_pattern_ops
citext_ops
The text_pattern_ops operator class enables efficient case-insensitive prefix searches on text columns with B-tree indexes. citext is a data type, and varchar_pattern_ops is for varchar.
What advantage does logical replication have over physical streaming replication?
Requires identical major versions
Exact byte-level copy of data directory
Lower bandwidth usage
Replicate individual tables and data transformations
Logical replication can replicate a subset of tables and allow row filtering or data transformation. Physical streaming replication replicates the entire cluster byte-for-byte.
Which transaction isolation level in PostgreSQL prevents phantom reads?
SERIALIZABLE
REPEATABLE READ
READ UNCOMMITTED
READ COMMITTED
The SERIALIZABLE isolation level prevents phantom reads by ensuring transactions behave as if they were executed sequentially. REPEATABLE READ in PostgreSQL is aliased to SERIALIZABLE.
Which settings are essential to enable continuous archiving and point-in-time recovery in PostgreSQL?
synchronous_commit = off and log_statement = all
archive_mode = on and wal_level >= replica
max_wal_senders = 0 and hot_standby = off
archive_timeout = 0 and log_rotation_age = 0
To enable continuous archiving and PITR, archive_mode must be on and wal_level must be set to 'replica' or higher. Other settings do not configure WAL archiving correctly.
0
{"name":"Which PostgreSQL command displays the estimated execution plan of a query without running it?", "url":"https://www.quiz-maker.com/QPREVIEW","txt":"Which PostgreSQL command displays the estimated execution plan of a query without running it?, Which PostgreSQL data type is most suitable for storing variable-length Unicode text of arbitrary size?, Which SQL statement correctly creates a non-unique index on the 'username' column of the 'users' table?","img":"https://www.quiz-maker.com/3012/images/ogquiz.png"}

Learning Outcomes

  1. Analyse query execution plans to optimize performance.
  2. Identify appropriate data types and schema designs.
  3. Evaluate indexing strategies for efficient data retrieval.
  4. Apply transaction management and concurrency controls.
  5. Demonstrate proficiency with advanced PostgreSQL functions.
  6. Master backup and recovery techniques for data safety.

Cheat Sheet

  1. Query Optimizer & Plan Selection - Discover how PostgreSQL's query optimizer picks the quickest path through your data, like a detective solving a maze. By understanding cost estimates, statistics, and join strategies, you'll turbocharge your queries in no time. Jovis: A Visualization Tool for PostgreSQL Query Optimizer
  2. Adaptive Indexing - Learn how adaptive indexing tweaks and refines indexes on the fly as queries run, much like a librarian who only organizes shelves when a book is requested. This clever approach balances instant speed boosts with minimal upfront costs. Concurrency Control for Adaptive Indexing
  3. Machine-Learned Optimization - Dive into machine learning for query optimization, where your database becomes smarter over time by learning from previous executions. Think of it as having a personal performance coach cheering your queries to faster results. Neo: A Learned Query Optimizer
  4. Runtime Query Re-optimization - Explore techniques that act like mid-flight course corrections for your SQL plans, fixing estimation errors on the go. QuerySplit demonstrates how to slice your logical plan into subqueries for a smooth performance ride. Efficient Query Re-optimization with Judicious Subquery Selections
  5. Data Types & Schema Design - Master PostgreSQL's rich set of data types and schema principles to ensure your data wears the perfect outfit for its size. Right-sizing your columns keeps data integrity intact, saves storage, and accelerates operations. PostgreSQL Data Types Documentation
  6. Indexing Strategies - Compare B-tree, Hash, GIN, and more as if you're assembling a superhero's utility belt. Picking the correct index is the secret to lightning-fast lookups and smooth table scans. Guide to PostgreSQL Index Types
  7. Transaction Management & ACID - Embrace ACID properties to keep your data consistent, even under heavy concurrent load. Proper transaction techniques are your shield against anomalies and race conditions. PostgreSQL Transaction Isolation Levels
  8. Advanced SQL Functions - Unlock the power of window functions and common table expressions (CTEs) to write more readable, maintainable, and powerful queries. These tools turn complex requests into elegant solutions. Using WITH Queries (CTEs)
  9. Backup & Recovery Techniques - Fortify your data fortress with physical and logical backup strategies, so you can restore your database like a phoenix from the ashes. Regular drills ensure you're always ready for disaster. PostgreSQL Backup and Restore
  10. Concurrency Control (MVCC) - Delve into Multi-Version Concurrency Control to let multiple sessions read and write without stepping on each other's toes. Effective isolation keeps your system running smoothly and conflict-free. Multi-Version Concurrency Control in PostgreSQL
Make a Quiz (FREE) Copy & Edit This Quiz Create a Quiz with AI

Technology Quizzes

Powered by: Quiz Maker