This page needs a newer browser to run its tools. You can still read everything here.

How PostgreSQL works and how to run it interview questions

Interview questions on how PostgreSQL works and how to run it: warm-ups, then what each harder question tests and how a good answer starts.

12 questions: 4 warm-ups and 8 with model answers · Levels: Fresher to senior

Warm-up questions

Short questions with short answers, each answered in full.

Fresher

What happens inside PostgreSQL when a client connects, and why does it matter for the number of connections?

Answered in the lesson PostgreSQL architecture: processes, memory, files
Fresher

What is the difference between reloading and restarting PostgreSQL, and how do you know which one a change needs?

Answered in the lesson Creating a cluster: initdb, settings, ALTER SYSTEM
Fresher

What is the difference between COPY and \copy, and when do you use each?

Answered in the lesson psql for daily work: meta-commands and scripts
Fresher

Why should an application never use ctid to identify a row?

Answered in the lesson System catalogs, pages and TOAST

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

Mid to senior

A server with 32 GB of RAM runs PostgreSQL with shared_buffers = 8GB, max_connections = 500 and work_mem = 64MB set for everyone. During a burst of reports the kernel's out-of-memory killer ends a postgres process and every session drops. How can that happen when shared memory is only 8 GB, and what do you change?
What it tests

Knowing that work_mem is a limit per sort or hash operation in every backend, estimating the worst case, and fixing it without starving the queries that need memory.

First step

Put numbers on the private memory: 500 backends, each report running a few sorts and hashes at 64MB (hashes up to 128MB with hash_mem_multiplier 2.0), can ask for far more than the 24 GB left after shared_buffers. Then explain why one killed backend ended every session, and set memory per role or per query while capping concurrency.
Follow-ups
- Why did every session drop when only one process was killed?
- How do you find which queries spill or use the most memory?
- What would you set for the reporting role, and how does a pooler help?
From the lesson PostgreSQL architecture: processes, memory, files
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I have a licence key
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A disk report shows that base/16384/2609183.3 and its neighbours take 40 GB, and nobody knows which table that is. How do you find out, safely, and what do the folder name, the number and the .3 tell you?
What it tests

Reading the data directory layout (database OID, relfilenode, segments and forks) and mapping a file back to a relation with the catalogs instead of guessing from OIDs.

First step

Decode the path: 16384 is the database's OID, 2609183 a relfilenode, not necessarily an OID, and .3 the fourth 1 GB segment of the main fork. Find the database with pg_database, connect to it and ask pg_filenode_relation(0, 2609183) for the relation; never touch the files themselves.
Follow-ups
- Why can't you simply look for pg_class.oid = 2609183?
- What do files ending in _fsm, _vm or starting with t3_ mean in the same folder?
- How do you measure the relation's total size once you know its name?
From the lesson PostgreSQL architecture: processes, memory, files
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A team wants one rule: every configuration change is made with ALTER SYSTEM and a reload. This week they need work_mem 256MB for the reporting role, lock_timeout 2s for one migration and statement_timeout 30s in the analytics database. What goes wrong with their rule, and what do you do instead?
What it tests

Choosing the level of a setting by who needs it and for how long, knowing the precedence of the levels, and knowing what ALTER SYSTEM can and cannot do.

First step

For each request, ask who needs the value and for how long. ALTER SYSTEM reaches every session of every database, so all three values would leak into work that must not have them. Put each at the narrowest level that covers the need, and say how you would verify it.
Follow-ups
- Which settings would you still change with ALTER SYSTEM, and how do you keep track of them?
- How do you check that the migration's lock_timeout reached only the migration?
- What breaks if someone edits postgresql.conf by hand for a value that ALTER SYSTEM already set?
From the lesson Creating a cluster: initdb, settings, ALTER SYSTEM
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

After an incident you find shared_buffers = 32GB in postgresql.conf, but SHOW shared_buffers on the running server says 8GB. Explain the possible causes and how you prove which one it is without guessing.
What it tests

Knowing the order in which PostgreSQL reads its configuration, contexts and pending restarts, and using pg_settings and pg_file_settings to trace a value.

First step

List the candidates: a restart has not happened since the edit, a later entry wins (postgresql.auto.conf, an included file, another line), or the command line sets it. Then read source, sourcefile, sourceline and pending_restart in pg_settings and every shared_buffers row of pg_file_settings.
Follow-ups
- What does pending_restart = true tell you, and what does it not?
- How would a value on the command line show up, and where is it recorded?
- Once you know the cause, how do you change the value safely on a production server?
From the lesson Creating a cluster: initdb, settings, ALTER SYSTEM
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Fresher to mid

A nightly psql script of 40 statements failed at statement 7, ran the other 33, exited with status 0 and left the data half migrated. The scheduler reported success. How do you make the script safe, and how does the scheduler learn that it failed?
What it tests

Knowing psql's default of continuing after errors, ON_ERROR_STOP and its exit status, single-transaction runs, and making scripts that behave the same for everyone.

First step

Explain the default: psql reports an error and carries on, and its exit status is 0 if it reached the end. Then run the script with -X, ON_ERROR_STOP on and --single-transaction, so the first error stops it, rolls everything back and ends with status 3, which the scheduler treats as a failure.
Follow-ups
- Which statements cannot run inside --single-transaction, and what do you do with them?
- How would the script check a precondition, such as the schema version, before changing anything?
- Why does -X matter for a scheduled script?
From the lesson psql for daily work: meta-commands and scripts
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A \gexec script grants SELECT on every table of a schema with 'GRANT SELECT ON ' || schemaname || '.' || relname || ' TO reporting'. It broke on a table called Order Items, and a colleague warns it is unsafe as well. Explain both problems and rewrite the query.
What it tests

Understanding that \gexec runs generated text as SQL, quoting identifiers with format's %I, and reading generated statements before they run.

First step

Show what the concatenation produces for Order Items: two words where one name belongs, so the GRANT is invalid, and a name containing SQL could change the statement. Then rewrite it with format('GRANT SELECT ON %I.%I TO reporting', schemaname, relname) and print the statements with a semicolon before running them with \gexec.
Follow-ups
- What do %I and %L each quote, and when would you use quote_ident instead?
- How do you make the script stop at the first failed GRANT?
- Is there a statement that makes this script unnecessary?
From the lesson psql for daily work: meta-commands and scripts
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

pg_relation_size('documents') reports 2 GB, but pg_total_relation_size('documents') reports 61 GB and the disk is filling up. The table has a jsonb column of large documents and a status column that is updated all day. Explain where the other 59 GB can be, how you measure each part, and what you would check next.
What it tests

Knowing what each size function counts, how TOAST stores large values and what UPDATEs leave behind, and using the catalogs to measure the TOAST table and indexes separately.

First step

pg_relation_size counts only the main fork. Split the rest: pg_table_size minus the main fork is mostly the TOAST table, pg_indexes_size the indexes. Find the TOAST table through pg_class.reltoastrelid and measure it, then compare its live data with its size to see whether dead chunks from updates are the cause.
Follow-ups
- Do the status updates themselves grow the TOAST table?
- How would you tell live TOAST data from dead chunks waiting for VACUUM?
- What schema change would keep a busy column away from the large documents?
From the lesson System catalogs, pages and TOAST
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

Deleting one customer takes seconds and locks up a busy orders system. You suspect foreign keys whose referencing columns have no index. Write the catalog query that lists them, and explain why such a key makes deletes slow.
What it tests

Reading pg_constraint (contype f, conkey, conrelid) and pg_index (indkey) together with regclass, and knowing what PostgreSQL does for a referenced row's DELETE.

First step

Explain the cost first: deleting a customer makes PostgreSQL look for referencing rows in orders, and without an index starting with customer_id that is a scan of orders for every deleted customer. Then list foreign keys from pg_constraint where no index of the referencing table has the key's columns as its leading columns.
Follow-ups
- Why must the index's leading columns match, and does column order matter?
- Which other catalog query would you run next for a schema audit?
- How do you add the missing index on a busy table without blocking writes?
From the lesson System catalogs, pages and TOAST
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes

The lessons of this module

PostgreSQL architecture: processes, memory, files Free lesson, Pro answers: 10 minutes
Creating a cluster: initdb, settings, ALTER SYSTEM Free lesson, Pro answers: 10 minutes
psql for daily work: meta-commands and scripts Free lesson, Pro answers: 10 minutes
System catalogs, pages and TOAST Free lesson, Pro answers: 10 minutes

All PostgreSQL in Depth interview questions · The PostgreSQL in Depth track

Warm-up questions

What happens inside PostgreSQL when a client connects, and why does it matter for the number of connections?

What is the difference between reloading and restarting PostgreSQL, and how do you know which one a change needs?

What is the difference between COPY and \copy, and when do you use each?

Why should an application never use ctid to identify a row?

Questions with model answers

A disk report shows that base/16384/2609183.3 and its neighbours take 40 GB, and nobody knows which table that is. How do you find out, safely, and what do the folder name, the number and the .3 tell you?

A team wants one rule: every configuration change is made with ALTER SYSTEM and a reload. This week they need work_mem 256MB for the reporting role, lock_timeout 2s for one migration and statement_timeout 30s in the analytics database. What goes wrong with their rule, and what do you do instead?

After an incident you find shared_buffers = 32GB in postgresql.conf, but SHOW shared_buffers on the running server says 8GB. Explain the possible causes and how you prove which one it is without guessing.

A nightly psql script of 40 statements failed at statement 7, ran the other 33, exited with status 0 and left the data half migrated. The scheduler reported success. How do you make the script safe, and how does the scheduler learn that it failed?

A \gexec script grants SELECT on every table of a schema with 'GRANT SELECT ON ' || schemaname || '.' || relname || ' TO reporting'. It broke on a table called Order Items, and a colleague warns it is unsafe as well. Explain both problems and rewrite the query.

Deleting one customer takes seconds and locks up a busy orders system. You suspect foreign keys whose referencing columns have no index. Write the catalog query that lists them, and explain why such a key makes deletes slow.