1. What is PostgreSQL?

Answer: PostgreSQL is an advanced open-source relational

database management system (RDBMS) that supports SQL
compliance, ACID properties, and various advanced features
like JSON support, full-text search, and extensibility. It is
known for its robustness, scalability, and support for complex
queries.
2. What are the key features of PostgreSQL?
Answer:
 ACID compliance ensures reliability in transaction
processing.
 Support for JSON/JSONB to handle unstructured data.
 Extensibility with custom functions and data types.
 Replication for high availability.
 Full-text search capabilities.
 MVCC (Multi-Version Concurrency Control) for
concurrent data access.
 Support for stored procedures and triggers.
3. Explain the concept of MVCC in PostgreSQL.
Answer: MVCC (Multi-Version Concurrency Control) is a
method PostgreSQL uses to manage concurrent access to the
database. It ensures that transactions do not block each
other by maintaining multiple versions of data. Each
transaction sees a snapshot of the database at a specific
point, allowing for consistent reads and writes.
4. How does PostgreSQL handle indexing? What types
of indexes are available?
Answer: PostgreSQL uses indexes to improve query
performance by reducing the amount of data scanned. The
types of indexes include:
 B-tree: Default index type for equality and range queries.
 Hash: For equality queries.
 GIN (Generalized Inverted Index): For full-text search
and array data.
 GiST (Generalized Search Tree): For complex data
types like geometries.
 BRIN (Block Range Index): For large datasets with
sequential data.
 SP-GiST (Space-Partitioned GiST): For non-overlapping
data ranges.
 Partial Indexes: Indexes on a subset of data.
5. What is the difference between JSON and JSONB in
PostgreSQL?
Answer:
 JSON: Stores data in plain text format, retaining the input
structure.
 JSONB: Stores data in a binary format for faster
processing and efficient indexing.
 Use JSONB when you need to query, index, or manipulate
JSON data frequently.
6. How can you optimize query performance in
PostgreSQL?
Answer:
 Use EXPLAIN and EXPLAIN ANALYZE to understand
query execution plans.
 Create appropriate indexes, including multi-column or
partial indexes.
 Normalize or denormalize data based on access patterns.
 Use query caching where applicable.
 Optimize joins with proper indexing and limiting result
sets.
 Regularly vacuum and analyze the database to update
statistics.
7. What is the purpose of the VACUUM command?
Answer: The VACUUM command removes dead tuples from
tables and indexes to reclaim storage and maintain
performance. There are two types:
 VACUUM: Removes dead tuples but does not lock the
table.
 VACUUM FULL: Performs a full cleanup, locks the table,
and compacts it to free space.
8. How do you back up and restore a PostgreSQL
database?
Answer:
 Backup: Use pg_dump for logical backups or
pg_basebackup for physical backups.
 Restore: Use psql to restore logical backups or
pg_restore for specific formats (e.g., custom or directory
formats).
Commands:
pg_dump -U [username] -d [dbname] > [Link]
psql -U [username] -d [dbname] < [Link]
9. What are tablespaces in PostgreSQL?
Answer: Tablespaces in PostgreSQL allow administrators to
define locations on the filesystem where database objects
can be stored. This provides better control over disk I/O and
helps distribute data across different storage devices.
10. How does PostgreSQL handle replication?
Answer: PostgreSQL supports several replication methods:
 Streaming Replication: Sends changes from the
primary to replica in real-time.
 Logical Replication: Replicates specific tables or subsets
of data.
 Hot Standby: Allows read-only queries on replicas during
streaming replication.
11. What are CTEs, and why are they used?
Answer: CTEs (Common Table Expressions) are temporary
result sets defined within the execution of a query. They
improve query readability and can be referenced multiple
times in a single query.
Example:
WITH recent_sales AS (
SELECT * FROM sales WHERE sale_date > NOW() - INTERVAL '30
days'
)
SELECT * FROM recent_sales WHERE amount > 1000;
12. Explain the difference between DELETE and
TRUNCATE.
Answer:
 DELETE: Removes specific rows from a table and allows
filtering with conditions. Triggers are invoked, and it is
slower due to logging.
 TRUNCATE: Removes all rows from a table, bypasses
triggers, and is faster as it does not log individual row
deletions.
13. How can you enforce unique constraints in
PostgreSQL?
Answer: Use the UNIQUE constraint or create a unique
index:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE
);
14. How does PostgreSQL handle concurrency?
Answer: PostgreSQL uses MVCC to handle concurrency. It
allows multiple transactions to occur simultaneously without
locking the database. It uses snapshots to provide consistent
reads and avoids conflicts through row-versioning.
15. What is the role of pg_hba.conf?
Answer: The pg_hba.conf file defines client authentication
settings for PostgreSQL. It specifies how clients can connect,
the authentication methods, and which IP addresses or hosts
are allowed access.
16. What are the different data types available in
PostgreSQL?
Answer: PostgreSQL supports a variety of data types:
 Numeric: Integer, Decimal, Float.
 Character: CHAR, VARCHAR, TEXT.
 Date/Time: DATE, TIME, TIMESTAMP, INTERVAL.
 Boolean: TRUE, FALSE.
 Geometric: Point, Line, Polygon.
 Network: CIDR, INET, MACADDR.
 JSON/JSONB.
 Array: Multi-dimensional arrays.
17. How do you create a stored procedure in
PostgreSQL?
Answer: Use the CREATE PROCEDURE statement. Example:
CREATE PROCEDURE update_salary(emp_id INT, increment NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE employees SET salary = salary + increment WHERE id =
emp_id;
END;
$$;
Call it using CALL:
CALL update_salary(1, 500);

18. What is the use of pg_stat_activity?

Answer: The pg_stat_activity view provides information
about the active processes in the PostgreSQL database. It is
useful for monitoring queries, client connections, and
troubleshooting performance issues.
19. How can you perform a case-insensitive search in
PostgreSQL?
Answer: Use the ILIKE operator instead of LIKE:
SELECT * FROM users WHERE name ILIKE '%john%';
20. What are extensions in PostgreSQL?
Answer: Extensions are packages that add additional
functionality to PostgreSQL. Common extensions include:
 pgcrypto: For cryptographic functions.
 hstore: For key-value storage.
 PostGIS: For spatial and geographic data.
Install an extension:
CREATE EXTENSION IF NOT EXISTS hstore;
21. What is the difference between NOW() and
CURRENT_TIMESTAMP?
Answer: Both return the current date and time. However:
 NOW() is a PostgreSQL-specific function.
 CURRENT_TIMESTAMP is ANSI SQL compliant and portable.
22. How can you list all databases in PostgreSQL?
Answer: Use the \l command in psql or query the
pg_database table:
SELECT datname FROM pg_database;
 23. Explain the difference between INNER JOIN and
OUTER JOIN.
Answer:
 INNER JOIN: Returns rows that match in both tables.
 OUTER JOIN: Includes matching rows and unmatched
rows from one or both tables (LEFT, RIGHT, or FULL).
Example:
SELECT * FROM orders INNER JOIN customers ON orders.customer_id
= [Link];

24. How can you update multiple rows in PostgreSQL?

Answer: Use the UPDATE statement with a WHERE clause:
UPDATE employees SET salary = salary + 1000 WHERE department =
'IT';
25. What is a sequence in PostgreSQL?
Answer: A sequence is a database object used to generate
unique numeric identifiers, often for primary keys.
CREATE SEQUENCE emp_id_seq START WITH 1 INCREMENT BY 1;
SELECT nextval('emp_id_seq');

1. SQL and relational thinking

 Set-based querying in ANSI SQL with PostgreSQL extensions such as CTEs and
window functions. Mastering these is a core part of the essential PostgreSQL developer
skills that interviewers screen for.
 Command over joins, grouping, filtering, and predicate logic across realistic datasets.
 Reduces over-fetching, N+1 patterns, and application-side loops that waste resources.
 Enables consistent, predictable query plans and maintainable business logic in SQL
layers.
 Applied through correct join strategies, selective predicates, and minimal data
movement.
 Executed via psql tests, EXPLAIN ANALYZE reviews, and refactors to set-oriented
solutions.
2. ACID, MVCC, and consistency
 Transaction semantics, durability, and isolation behavior in PostgreSQL's MVCC
engine.
 Prevents lost updates, phantom reads, and corruption under concurrent workloads.
 Achieved via transaction scoping, SAVEPOINT usage, and sensible isolation choices.
 Guards against blocking storms with balanced read consistency vs. writer progress.
 Implemented with SERIALIZABLE or REPEATABLE READ where needed and careful
retry logic.
 Validated by simulating concurrent writers, conflict resolution, and latency impacts.
3. Data types, constraints, and domains
 Native types, arrays, JSONB, enums, generated columns, check constraints, and
domains.
 Improves data fidelity, index efficiency, and query simplicity for sql developer questions.
 Enforced using NOT NULL, UNIQUE, CHECK, and FK rules close to the data.
 Minimizes application complexity and guards integrity across services.
 Selected to match semantics, storage, and operator availability for query plans.
 Verified with sample inserts, constraint violation tests, and operator-based indexing.
Need PostgreSQL developers with rock-solid fundamentals? Digiqt's pre-vetted
engineers are ready to deliver.
How Are Query Optimization Skills Tested in PostgreSQL
Interviews?
You should expect questions on EXPLAIN ANALYZE plan reading, identifying
bottleneck nodes, and proposing measurable query speedups with concrete baselines.

 Prepare to work through a slow query with realistic data volume and a time/buffers
baseline. Our dedicated PostgreSQL query optimization guide covers the techniques
interviewers expect you to know.
 Be ready to form hypotheses, read plans, and suggest minimal-change improvements.
 Practice quantifying improvements with numeric deltas: runtime, buffers, rows, and plan
node shifts.
1. EXPLAIN and EXPLAIN ANALYZE literacy
 Reading node order, cardinality estimates, join types, and filter selectivity.
 Translates plans into concrete tuning moves with low-risk changes first.
 Performed by comparing estimated vs. actual rows and cost vs. time asymmetry.
 Targets nodes with highest time or I/O, then adjusts predicates or indexes.
 Executed repeatedly with tracked metrics and notes on each iteration.
 Documented outcomes tie to query plans, buffer hits, and wall-clock timing.
2. Join strategy and rewrite skill
 Mastery of hash, merge, and nested loops along with CTE inlining changes.
 Impacts CPU, memory, and I/O depending on data sizes and distribution.
 Achieved by reordering joins, pushing filters, and replacing subqueries.
 Eliminates unnecessary sorts and materializations to reduce overhead.
 Implemented with statistics refreshes and selective indexes on join keys.
 Measured by plan shape simplification and reduced loops or rechecks.
3. Work_mem and batching tactics
 Awareness of memory settings for sorts, hashes, and batch sizing in drivers.
 Prevents disk spills and reduces round trips that slow I/O-bound paths.
 Tuned by sizing work_mem to data slices and enabling server-side cursors.
 Balanced to avoid runaway memory across concurrent sessions.
 Applied with load-specific profiles and connection pool guardrails.
 Verified via EXPLAIN ANALYZE, pg_stat_statements, and spill counters.
Run structured PostgreSQL assessments with Digiqt's pre-vetted database engineers
Need PostgreSQL experts who pass these exact assessments? Digiqt provides pre-
vetted database engineers.
What Indexing Questions Should You Expect in a
PostgreSQL Interview?
PostgreSQL interviews commonly test your ability to choose the right index type, justify
partial or expression indexes, and demonstrate measurable plan improvements.

 Prepare to give precise index justifications using real predicates and sort orders. A
structured approach to evaluating a PostgreSQL developer can help you gauge
indexing depth during interviews.
 Expect questions on partial, expression, and multicolumn index trade-offs.
 Practice validating results with buffer hits, index-only scans, and reduced heap visits.
1. Index type selection depth
 Alignment of B-tree, GIN, GiST, BRIN, and hash with data shapes and queries.
 Avoids mismatches that inflate storage or force sequential scans.
 Selected by examining operators, range needs, and column correlation.
 Nuances include trigrams for LIKE and GiST for geometric or range data.
 Executed by matching access methods to predicates and ORDER BY clauses.
 Confirmed by plan shifts to index scans and lower heap fetches.
2. Partial and expression indexes
 Predicate-limited or computed-key structures targeting hot query subsets.
 Shrinks storage, boosts cache locality, and trims write overhead.
 Defined with WHERE clauses or expressions that mirror query filters.
 Enables index-only scans when INCLUDED columns serve projections.
 Applied where data skew or soft deletes cause needless bloat.
 Measured by runtime drops and fewer rows removed by filter.
3. Index lifecycle and bloat control
 Awareness of autovacuum limits, HOT updates, and REINDEX thresholds.
 Sustains predictable latency and throughput as tables evolve.
 Managed with fillfactor, routine rechecks, and maintenance windows.
 Integrates pgstattuple, pageinspect, and visibility maps where needed.
 Executed with change calendars and replication-safe operations.
 Tracked via bloat ratios, index size trends, and plan stability.
Hire PostgreSQL developers who ace these exact assessments. Talk to Digiqt.
PostgreSQL Index Types Comparison
Index Type Best For Example Use Case

B-tree Equality and range queriesWHERE age > 25

GIN Full-text, JSONB, arrays JSONB containment

GiST Geometric, proximity PostGIS spatial

BRIN Naturally ordered data Timestamp append-only

What Transaction and Concurrency Questions Come Up

in PostgreSQL Interviews?
You should expect questions on isolation level selection, deadlock diagnosis, lock
management, and retry-safe patterns under concurrent workloads.

 Prepare to explain deadlock handling, lock graphs, and retry-safe patterns.

Understanding PostgreSQL security best practices also strengthens your answers on
access control under concurrency.
 Review savepoint usage and minimal critical sections.
 Practice demonstrating concurrency skills with simulated hotspots and log-based
evidence.
1. Isolation level mastery
 Selection among READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.
 Balances anomaly prevention with throughput under mixed workloads.
 Chosen per workflow: financial ledgers vs. analytics readers.
 Avoids unnecessary elevation that increases conflicts.
 Implemented with targeted overrides and retry logic for serialization.
 Measured by conflict counts, latency spreads, and success rates.
2. Locking insight and deadlock triage
 Understanding relation, row, and advisory locks plus lock modes.
 Prevents head-of-line blocking and cascading stalls in services.
 Diagnosed with pg_locks, blocked PIDs, and lock wait samples.
 Mitigated by index coverage, access order discipline, and timeouts.
 Executed through shorter transactions and decoupled side effects.
 Verified via deadlock logs, alert reductions, and p95 latency.
3. Long-running transaction control
 Visibility horizon awareness and impact on vacuum progress.
 Guards against table bloat and autovacuum starvation.
 Monitored with age of snapshots and idle-in-transaction sessions.
 Contained via timeouts, checkpoints, and chunked workloads.
 Enforced in ORMs with statement time caps and retryable flows.
 Audited with sessions dashboards and stale snapshot alerts.
Struggling to find PostgreSQL engineers who understand concurrency at depth? Digiqt
can help.
What Advanced PostgreSQL Features Are Asked About in
Senior Interviews?
Senior-level PostgreSQL interviews focus on partitioning, logical replication, extensions,
and materialized views with clear performance and operability justifications. Review the
full breakdown in our senior PostgreSQL developer skills guide to align your preparation
with what hiring managers expect.
 Be ready to discuss principled use of native partitioning for large tables.
 Expect questions on extension usage tied to concrete access patterns.
 Prepare to explain change data capture and cache invalidation strategies.
1. Declarative partitioning
 Range, list, hash partitioning with aligned PKs and indexes.
 Enables pruning, faster maintenance, and targeted archiving.
 Designed to match time-series, multitenant, or sharded access.
 Reduces vacuum scope and accelerates constraint checks.
 Implemented with attachments, default partitions, and triggers where needed.
 Benchmarked via pruned scans and smaller working sets.
2. Materialized views and refresh strategy
 Precomputed result sets with REFRESH management patterns.
 Cuts latency for heavy aggregations and joins.
 Scheduled incremental or concurrent refreshes around SLAs.
 Coordinates with cache layers and invalidation hooks.
 Executed via dependency mapping and refresh windows.
 Tracked by staleness, refresh duration, and hit ratios.
3. Extensions and operator classes
 Practical use of PostGIS, pg_trgm, hll, and advanced GIN/GiST ops.
 Expands capability for search, geo, and cardinality summaries.
 Mapped to concrete queries and storage budgets.
 Avoids unnecessary footprint in core schemas.
 Packaged with version pinning and compatibility checks.
 Verified by feature tests and explainable benefits.
Looking for senior PostgreSQL talent with advanced feature expertise? Digiqt delivers
production-ready engineers.
What Backup, Recovery, and HA Questions Appear in
PostgreSQL Interviews?
PostgreSQL interviews commonly test PITR setup, WAL archiving, streaming replication
configuration, and your ability to meet recovery time objectives.

 Prepare to walk through PITR demos and WAL archive validation.

 Expect questions on streaming replication with failover tooling.
 Be ready to discuss recovery point and time objectives with test evidence.
1. Backup strategy depth
 Logical vs. physical backups, pg_dump vs. pg_basebackup selections.
 Aligns restore granularity, speed, and data volume realities.
 Combined with WAL archiving for consistent snapshots.
 Segregates retention by tier with immutability controls.
 Executed via scheduled jobs, checksums, and restore tests.
 Proven by timed restores and data consistency checks.
2. PITR and disaster readiness
 WAL-based recovery to precise targets and timelines.
 Limits data loss to agreed recovery points in incidents.
 Driven by tested restore procedures and timelines.
 Avoids incorrect base backup chains or archive gaps.
 Practiced with drill books and automation scripts.
 Reported via RTO/RPO metrics and audit trails.
3. Replication and failover operations
 Streaming replication, slots, and synchronous vs. asynchronous modes.
 Delivers resilience and read scaling under load.
 Planned quorum rules and client-side failover routing.
 Minimizes split-brain through fencing and consensus tools.
 Orchestrated by Patroni, repmgr, or cloud-native services.
 Validated with switchover drills and replication lag SLOs.
Streaming vs. Logical Replication Comparison
Feature Streaming ReplicationLogical Replication

Level Physical (WAL bytes) Logical (table-level)

Scope Entire cluster Selected tables

Cross-versionNo Yes

Use Case HA failover Data integration

Need PostgreSQL engineers who can architect backup, recovery, and HA? Talk to
Digiqt.
Building a high-availability PostgreSQL stack? Our engineers specialize in production-
grade database architecture.
What Performance Tuning Questions Should You Prepare
For?
You should prepare for questions on pg_stat_statements analysis, configuration
baselines, vacuum hygiene, and diagnosing latency issues under load.

 Know how to use pg_stat_statements and interpret server logs. For a deeper dive into
tuning methodology, see our PostgreSQL performance optimization resource.
 Expect questions on config rationale tied to workload profiles.
 Practice explaining vacuum strategy and bloat control outcomes.
1. Observability with pg_stat_* and logs
 Familiarity with pg_stat_statements, pg_stat_activity, and log settings.
 Surfaces hotspots, blockers, and plan churn quickly.
 Built on normalized query fingerprints and sampling windows.
 Reduces noise via log_line_prefix and statement controls.
 Implemented dashboards tracking time, I/O, and temp spills.
 Judged by MTTR improvements and stable top query sets.
2. Sensible configuration baselines
 Workload-tuned shared_buffers, work_mem, autovacuum settings, and checkpoints.
 Prevents stalls, excessive I/O, and memory contention.
 Derived from data size, concurrency, and query mix.
 Avoids cargo-cult tweaks that regress reliability.
 Applied via staged rollouts and canary nodes.
 Audited by p95 latency, spill counts, and checkpoint I/O.
3. Vacuum and bloat management
 Autovacuum behavior, thresholds, and aggressive tuning for hot tables.
 Preserves visibility maps and keeps tables lean.
 Sized workers and cost limits to match write rates.
 Schedules manual VACUUM or REINDEX for edge cases.
 Executed with per-table overrides and monitoring hooks.
 Measured via dead tuple ratios and freeze age trends.
Need PostgreSQL engineers with proven performance tuning skills? Digiqt delivers.
How Should You Structure Your PostgreSQL Interview
Preparation?
Effective PostgreSQL interview preparation combines scenario-based practice, hands-
on SQL tasks, and system design discussions mapped to your target role level.

 Align your preparation to junior, mid, or senior expectations for the role. Following a
clear PostgreSQL hiring roadmap helps both candidates and hiring teams stay focused
on the right competencies.
 Practice with rubric-style scoring tied to measurable outcomes.
 Balance timed SQL labs with collaborative design review exercises.
 1. Scenario-based prompts
 Real incidents: slow queries, lock storms, or schema drift.
 Surfaces judgment, trade-offs, and communication under constraints.
 Posed with limited data, logs, and a ticking SLA.
 Separates guesswork from evidence-driven steps.
 Executed as 15–20 minute guided triage with plan snapshots.
 Graded on hypotheses, metrics, and final risk profile.
2. Hands-on SQL tasks
 Focused exercises on joins, windows, and indexing fixes.
 Produces tangible, comparable outputs across candidates.
 Seeded with realistic row counts and skewed distributions.
 Prevents toy-problem illusions and inflated signals.
 Implemented in psql or a sandbox with EXPLAIN ANALYZE.
 Scored by speedups, plan changes, and clarity of notes.
3. System design for data workloads
 End-to-end data flow: ingestion, storage, queries, and HA. If you are scaling your team
around these workloads, our guide on how to build a PostgreSQL database
team covers the hiring architecture side.
 Evaluates architecture thinking beyond single queries.
 Framed around SLAs, cost, and growth projections.
 Ensures future changes fit without risky rewrites.
 Captured via diagrams, trade-offs, and migration steps.
 Assessed by consistency, observability, and failover paths.
Skip the screening hassle. Digiqt provides PostgreSQL developers who are already
vetted for these competencies.
What Data Modeling and Schema Design Questions Are
Common?
PostgreSQL interviews commonly test normalization trade-offs, constraint-first integrity
enforcement, and schema evolution practices that protect correctness at scale.

 Prepare to discuss normalization depth and targeted denormalization decisions.

Benchmarking candidates on these topics is easier with a PostgreSQL developer salary
guide that maps skill expectations to market compensation.
 Expect questions on constraint-first integrity handling.
 Review migration planning and rollout safety strategies.
 1. Normalization with pragmatic denormalization
 Balanced 3NF foundations with selective pre-joins or aggregates.
 Maintains integrity while serving read-heavy endpoints.
 Chosen based on access paths and update frequencies.
 Avoids write amplification that negates benefits.
 Implemented with materialized views or redundant fields.
 Verified by index coverage and SLA-aligned latencies.
2. Referential integrity and constraints
 Foreign keys, cascades, checks, and unique enforcement at the DB layer.
 Stops silent drift and cross-service inconsistencies.
 Designed to reflect domain rules and data lifecycles.
 Prevents orphan records and race conditions.
 Executed with deferred constraints and batch-safe patterns.
 Monitored via violation counts and incident retros.
3. Schema evolution and migrations
 Versioned changesets, backward-compatible releases, and rollbacks.
 Reduces risk during blue-green or rolling deploys.
 Sequenced additive steps before destructive updates.
 Keeps services online during transformations.
 Executed with feature flags and dual-write strategies.
 Audited by deploy success rates and recovery drills.
Hire PostgreSQL developers skilled in data modeling and schema design. Digiqt has
your next engineer.
What Security and Compliance Questions Are Tested in
PostgreSQL Interviews?
You should expect questions on role design, least-privilege access, Row Level Security,
encryption usage, and audit logging aligned to compliance requirements.

 Prepare to walk through privilege audits and role hierarchy design.

 Expect questions on RLS policies for multi-tenant isolation.
 Review encryption, logging, and retention practices for common compliance
frameworks.
1. Roles, privileges, and least privilege
 Role inheritance, default privileges, and secure search_path discipline.
 Limits blast radius and curbs accidental data exposure.
 Structured as functional roles mapped to service accounts.
 Avoids superuser sprawl and ad-hoc grants.
 Enforced with scripts that diff desired vs. actual grants.
 Measured by privilege reviews and access request SLAs.
2. Row Level Security and tenant isolation
 Policy-driven row filters and per-tenant access control.
 Protects data boundaries in shared clusters.
 Implemented with session settings and predicate policies.
 Prevents cross-tenant leaks during complex joins.
 Combined with app-layer claims and schema guards.
 Tested via policy fuzzing and targeted query probes.
3. Encryption, auditing, and retention
 TLS in transit, disk-level or column-level at rest, and audit trails.
 Supports compliance mandates and forensic traceability.
 Configured via SSL modes, KMS-backed keys, and pgAudit.
 Preserves evidence without undue performance cost.
 Implemented with rotation schedules and tamper-resistance.
 Verified by audit completeness and incident playbacks.
Need PostgreSQL engineers who understand security and compliance inside out?
Digiqt connects you with the right talent.
Looking to hire PostgreSQL developers who can handle these challenges? Digiqt
connects you with top-tier database talent. Get started today.
How Does Digiqt Deliver Results?
Digiqt follows a proven delivery methodology to ensure measurable outcomes for every
engagement.

1. Discovery and Requirements

Digiqt starts with a detailed assessment of your current operations, technology stack,
and business objectives. This phase identifies the highest-impact opportunities and
establishes baseline KPIs for measuring success.

2. Solution Design
Based on the discovery findings, Digiqt architects a solution tailored to your specific
workflows and integration requirements. Every design decision is documented and
reviewed with your team before development begins.
 3. Iterative Build and Testing
Digiqt builds in focused sprints, delivering working functionality every two weeks. Each
sprint includes rigorous testing, stakeholder review, and refinement based on real
feedback from your team.

4. Deployment and Ongoing Optimization

After thorough QA and UAT, Digiqt deploys the solution with monitoring dashboards and
performance tracking. The team continues optimizing based on production data and
evolving business requirements.

Ready to discuss your requirements?

Schedule a Discovery Call with Digiqt

Why Do Companies Choose Digiqt for PostgreSQL

Hiring?
Companies choose Digiqt because we evaluate PostgreSQL developers against the
exact competencies covered in this guide, not generic coding challenges. Our technical
screening process tests MVCC understanding, EXPLAIN ANALYZE fluency, indexing
strategy, replication architecture, and production-grade performance tuning.

What Digiqt delivers:

 Pre-vetted PostgreSQL engineers who have passed hands-on assessments covering all
30 question areas in this guide
 Developers with production experience across RDS, Aurora, self-managed clusters, and
hybrid environments
 Flexible engagement models: dedicated engineers, team augmentation, or project-
based delivery
 Average time to fill: 2-4 weeks versus the industry average of 3-6 months
 Zero-risk trial period so you validate fit before committing
Stop losing months on PostgreSQL hiring. Talk to Digiqt today.
Conclusion
The demand for skilled PostgreSQL engineers is outpacing supply in 2026, with
database modernization initiatives accelerating across fintech, SaaS, and enterprise
platforms. Companies that secure top PostgreSQL talent now gain a 6-12 month
infrastructure advantage over competitors still struggling with recruiting pipelines.

These 30 PostgreSQL interview questions cover the full spectrum of competencies that
separate production-ready engineers from candidates with surface-level knowledge.
From MVCC internals and query optimization to replication architecture and security
compliance, each question maps to a real skill that matters in production environments.
 Use this guide to structure your technical assessments, benchmark candidates against
measurable standards, and build a PostgreSQL team that delivers from day one.

PostgreSQL talent is in high demand in 2026, with database engineer salaries rising 15-
20% year over year. Every month you spend on a vacant PostgreSQL role costs your
team in delayed features, unoptimized queries, and production risk. The companies that
move fastest on hiring lock in the best talent before competitors do.

Ready to hire PostgreSQL developers who meet these standards? Talk to Digiqt.
Faqs
1. What are the most commonly asked PostgreSQL interview
questions in 2026?
 PostgreSQL interviews focus on MVCC, indexing types, EXPLAIN ANALYZE, query
optimization, partitioning, replication, and vacuum tuning.
2. How should I prepare for a PostgreSQL interview with 2-3 years of
experience?
 Practice EXPLAIN ANALYZE output reading, MVCC concepts, CTE and window
functions, indexing strategies, and transaction isolation with real examples.
3. What is the difference between B-tree, GIN, GiST, and BRIN
indexes?
 B-tree handles equality and range queries, GIN suits full-text and JSONB, GiST
supports geometric queries, and BRIN works for naturally ordered data.
4. How does MVCC work in PostgreSQL?
 MVCC allows concurrent reads and writes without locking by giving each transaction a
snapshot based on xmin/xmax transaction IDs.
5. What performance tuning questions appear in senior PostgreSQL
interviews?
 Expect pg_stat_statements analysis, shared_buffers sizing, autovacuum tuning,
checkpoint configuration, and PgBouncer connection pooling questions.
6. What is the difference between logical and streaming replication?
 Streaming replication copies WAL at the physical level for full HA, while logical
replication decodes changes at the table level for selective sync.
7. How should I answer query optimization questions in a PostgreSQL
interview?
 Read EXPLAIN ANALYZE from innermost nodes, identify highest-cost operations,
check row estimate mismatches, and propose targeted index or rewrite fixes.
 8. What PostgreSQL security topics are tested in interviews?
 Expect GRANT/REVOKE, Row Level Security, pg_hba.conf, SSL/TLS, column
encryption, and pgAudit questions.

1. What is PostgreSQL?

PostgreSQL is a lightweight, free, and open-source relational database management

system. PostgreSQL is used widely across regions and companies and can be used in
most popular operating systems.

2. What are the benefits of PostgreSQL?

PostgreSQL excels among other SQL databases for several reasons, including:
[Link] that makes it suitable for all kinds of applications
[Link] and open-source
[Link] and reliability
[Link] variety of data types
5.A big community of users worldwide.
3. How connection is established in postgres?

The PostgreSQL server (formerly known as Postmaster) acts as the main supervisor
process, listening on a designated TCP/IP port for incoming client connections. Upon
receiving a connection request, it spawns a separate backend process to handle that
session. These backend processes communicate with each other and other instance
components using shared memory and semaphores, ensuring data consistency
during concurrent access. Once connected, the client sends queries to its assigned
backend process, which then parses, plans, and executes the query. The results are
transmitted back to the client over the established connection.
4. Explain the postgres architecture ?

PostgreSQL follows a multi-process architecture, where each client connection is

handled by a separate backend process. The PostgreSQL server
process (previously known as Postmaster) listens on a specified TCP/IP port for
incoming client requests. When a connection request arrives, it spawns a dedicated
backend process to handle that session. These backend processes interact with shared
memory and use inter-process communication (IPC) mechanisms like semaphores and
spinlocks to ensure data consistency. The architecture consists of key components
such as shared memory (used for caching and transaction management),
background processes (like WAL Writer, Checkpointer, and Autovacuum
Daemon), and storage structures (such as tables, indexes, and transaction logs).
PostgreSQL employs MVCC (Multi-Version Concurrency Control) to allow multiple
 transactions to run simultaneously without blocking each other. The WAL (Write-Ahead
Logging) mechanism ensures data durability by logging changes before applying them
to data files. This modular and process-based architecture enhances
PostgreSQL’s scalability, reliability, and performance, making it suitable for both
transactional and analytical workloads.
5. What are some important background processes in postgres?

PostgreSQL has several important background processes that ensure smooth

database operation:
1. Checkpointer – Flushes dirty pages from shared buffers to disk at regular intervals to
reduce write overhead.
2. WAL Writer – Writes transaction logs (WAL – Write-Ahead Logging) to ensure data
durability and crash recovery.
3. Autovacuum Daemon – Cleans up dead tuples to prevent table bloat and optimize
performance.
4. Background Writer – Helps reduce I/O spikes by pre-writing dirty pages to disk before
checkpoints occur.
5. Archiver – Handles WAL file archiving when continuous archiving is enabled.
6. Stats Collector – Gathers database statistics for query optimization and performance
monitoring.
7. Logical Replication Launcher – Manages logical replication workers for data
synchronization between databases.
8. WAL Receiver/Sender – Handles streaming replication between primary and standby
servers.
These background processes enhance performance, data integrity, and high
availability in PostgreSQL.
6. What are the memory components in postgres?
PostgreSQL has several important memory components that optimize performance
and manage database operations efficiently:
1. Shared Buffers – Caches frequently accessed data pages to reduce disk I/O.
2. Work Memory – Allocated per query for sorting and hashing operations.
3. Maintenance Work Memory – Used for maintenance tasks like vacuuming and
indexing.
4. WAL Buffers – Temporary storage for Write-Ahead Logging (WAL) before writing to
disk.
5. Effective Cache Size – An estimate of how much OS cache PostgreSQL can use for
query planning.
6. Temp Buffers – Session-local buffers used for temporary tables.
7. Kernel Page Cache – OS-level caching that stores frequently accessed data to
improve performance.
These memory components help PostgreSQL handle queries efficiently, minimize
disk access, and optimize overall performance.
7. what is the maximum file size of table or index in postgres? Can we
increase that ?

Max size is 1GB. If a table size is big, then it can spread across multiple files.
8. When wal writer write data to wal segement?

The WAL Writer process writes data to WAL (Write-Ahead Logging)

segments periodically to ensure durability and crash recovery. It writes data when:
1. WAL Buffers are Full – If the WAL buffer fills up, WAL Writer flushes data to WAL
segments.
2. Timeout Occurs – It writes at regular intervals (wal_writer_delay, default 200ms) to
prevent data loss.
3. Transaction Commit – WAL entries are flushed when a transaction commits to ensure
durability.
4. Checkpoints Trigger – During a checkpoint, WAL data is written and synced to disk for
consistency.
9. When bgwriter writes data to disk?
The Background Writer (bgwriter) writes dirty pages from shared buffers to
disk proactively to reduce I/O spikes and improve performance. It
operates independently of checkpoints and writes data at regular intervals based
on bgwriter_lru_maxpages and bgwriter_lru_multiplier settings. The primary triggers
for writing include buffer eviction (when new pages are needed), time-based
intervals (bgwriter_delay), and system workload. By gradually writing pages, it
prevents sudden bursts of disk I/O during checkpoints, enhancing PostgreSQL’s overall
efficiency and responsiveness.
10. What is restore_command?
restore_command in PostgreSQL is a configuration setting that specifies the command
used to retrieve archived WAL (Write-Ahead Log) files during recovery or replication.
11. What is the significance of pg_ident.conf file?

Just like we have os authenticated db users in oracle. Here in postgres also we have
similarconcept. We can provide mapping of os user and postgres db user inside
pg_ident.conf file.
12. What is recovery_target_time?

recovery_target_time is a configuration parameter in PostgreSQL that specifies the

exact point in time to which the database should be recovered during point-in-time
recovery.
13. What is pg_dumpall?

pg_dumpall is a PostgreSQL utility that backs up the entire database cluster—including

all databases, roles, and global objects—into a single dump file.
14. What are some wal related parameter in [Link] file?
max_wal_size → It defines the soft limit for the total WAL segment size. If this limit is
reached, a checkpoint is triggered to free up space.
wal_keep_segments → Specifies the number of old WAL segments to retain for
standby servers. (Deprecated in newer versions, replaced by wal_keep_size.)
wal_keep_size → Defines the size (in MB or GB) of WAL files to retain for replication
before they are recycled.
 max_wal_senders = 10 → Sets the maximum number of WAL sender
processes that handle streaming replication to standby servers.
15. What is a tablespace?
In PostgreSQL, a tablespace is a storage location on disk used to store database
objects like tables and indexes. It helps in managing disk usage and optimizing
performance by placing data on specific storage devices.
Example of Creating a Tablespace:
CREATE TABLESPACE my_tablespace LOCATION '/data/my_tablespace';
16. What is checkpoint? When checkpoint happens in postgres?

A checkpoint in PostgreSQL is a process that flushes all modified data (dirty pages)
from shared buffers to disk and synchronizes WAL (Write-Ahead Logging) files to
ensure database consistency. It helps in crash recovery by marking a safe point where
all previous changes are permanently stored. Checkpoints occur automatically at
regular intervals (checkpoint_timeout), when WAL size
exceeds max_wal_size, manually via the CHECKPOINT command, during server
shutdown, or when required for replication.
17. Which parameters controls the behaviour of BGWriter?

The Background Writer (bgwriter) behavior in PostgreSQL is controlled by the

following parameters:
1. bgwriter_delay → Defines the interval (in ms) between background writer runs
(default 200ms).
2. bgwriter_lru_maxpages → Sets the maximum number of dirty pages written to disk
per bgwriter cycle.
3. bgwriter_lru_multiplier → Determines how aggressively bgwriter writes pages based
on buffer usage (default 2.0).
18. Does postgres support direct i/o.?
No, PostgreSQL does not support Direct I/O natively. It relies on the operating
system’s page cache for disk I/O operations, which improves performance by reducing
direct disk access.
19. What is the difference between TRUNCATE and DELETE?

TRUNCATE is faster, removes all rows at once

DELETE removes rows one by one, can use WHERE clause
20. What is continuous archiving?

Method of archiving WAL files continuously

Essential for PITR capability
21. What is autovacuum in PostgreSQL?

Automated process that removes dead tuples and updates statistics

Essential for maintaining database performance
22. What is promote_trigger_file?
 promote_trigger_file in PostgreSQL is a file that, when created, triggers the standby
server to promote itself to a primary server in a replication setup.
23. How do you create and drop a database in PostgreSQL?

To create a database in PostgreSQL, use the following command:

CREATE DATABASE dbname;
To drop a database, use:
DROP DATABASE dbname;
Ensure no active connections to the database before dropping it. You can check for
active connections using pg_stat_activity.
24. How do you take a hot backup?

To take a hot backup in PostgreSQL using pg_basebackup while the database is

running:
1. Enable ARCHIVE_MODE:
Set archive_mode = on and configure archive_command in [Link],
then restart PostgreSQL.
2. Run pg_basebackup:
Use pg_basebackup to take a backup:
pg_basebackup -h localhost -D /path/to/backup -U replicator -P --wal-
method=stream
25. How do you create a user and assign privileges in PostgreSQL?

To create a user in PostgreSQL, use the CREATE USER command:

CREATE USER username WITH PASSWORD 'password';
To assign privileges, you can grant specific permissions
like SELECT, INSERT, UPDATE, etc., using the GRANT command. For example:
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;
This grants the user all privileges on the specified database. You can also grant table-
specific privileges:

GRANT SELECT, INSERT ON table_name TO username;

26. How do you take a full database backup using **pg_dump**?

To take a full database backup using pg_dump, use the following command:
pg_dump -U username -F c -b -v -f /path/to/[Link] dbname
27. How do you manage users and roles in PostgreSQL?

In PostgreSQL, manage users and roles using SQL commands: create users
with CREATE ROLE username WITH LOGIN PASSWORD 'password';, assign roles
with GRANT role TO user;, and set permissions with GRANT SELECT ON table TO
user;. Modify roles with ALTER ROLE, remove with DROP ROLE, and manage privileges
using REVOKE. Use pg_roles view to monitor roles and permissions.
28. How authentication happens in postgres?
 In PostgreSQL, authentication happens through various methods defined in
the pg_hba.conf file. When a client tries to connect, PostgreSQL checks this file to
determine the authentication method.
1. Password-Based Authentication – Uses MD5, SCRAM-SHA-256, or plain
passwords for user verification.
2. Trust – Allows connections without authentication (used for local, trusted
environments).
3. Peer Authentication – Uses OS user credentials to match PostgreSQL roles
(Unix/Linux only).
4. Ident Authentication – Relies on an external Ident server to verify the user.
5. Kerberos, LDAP, PAM, and SSPI – Supports enterprise authentication
mechanisms for secure login.
6. Certificate-Based Authentication – Uses SSL/TLS certificates for secure
connections.
29. What is the significance of pg_ident.conf file?
The pg_ident.conf file in PostgreSQL is used for user mapping when using Ident or
external authentication methods like LDAP, Kerberos, or PAM. It allows mapping OS-
level or external usernames to PostgreSQL roles, enabling flexible authentication.
30. What is pg_stat_statements?
pg_stat_statements is a PostgreSQL extension that records execution statistics of
SQL queries for performance monitoring and tuning.
31. What are connection limits?

Connection limits in PostgreSQL define the maximum number of concurrent

connections allowed per database or user, preventing resource exhaustion.
32. What is primary_conninfo?

primary_conninfo in PostgreSQL is a configuration parameter

in [Link] or [Link] that specifies the connection details for a
standby server to stream WAL data from the primary server.
33. What is pg_stat_activity?
pg_stat_activity is a PostgreSQL system view that shows information about currently
active database connections.
34. What is [Link]?

In PostgreSQL <12, the recovery configuration is managed using

the [Link] file.
In PostgreSQL ≥12, recovery is managed using [Link] for recovery-related
settings and [Link] to enable replication.
35. What are GRANT/REVOKE?

GRANT/REVOKE in PostgreSQL are commands used to assign or remove privileges

on databases, tables, or other objects to users or roles for access control.
36. What is application_name in replication?
 application_name in PostgreSQL replication identifies a replica or client connection in
the primary server’s pg_stat_replication view for monitoring and management.
37. What are wait events?

Wait events in PostgreSQL indicate the resource or condition a process is waiting for,
helping diagnose performance bottlenecks.

38. What is pg_audit?

pg_audit is a PostgreSQL extension that provides detailed logging of database

activities for security and compliance auditing.
39. What is pg_dumpall?
pg_dumpall is a PostgreSQL utility that backs up the entire database cluster—including
all databases, roles, and global objects—into a single dump file.
40. What is archive_command?

archive_command in PostgreSQL is a configuration setting that specifies the command

used to archive WAL (Write-Ahead Log) files to a safe location for point-in-time recovery
(PITR).
41. How do you handle bloated tables and indexes?

To handle bloated tables and indexes in PostgreSQL, use VACUUM to reclaim space from
deleted rows. For severe bloat, run VACUUM FULL to compact the table and reclaim disk
space. For bloated indexes, use REINDEX to rebuild them. Regular autovacuum helps
manage bloat over time. Monitoring tools
like pg_stat_user_tables and pg_stat_all_indexes can help identify and manage bloat
efficiently.
42. What are the different types of backups in PostgreSQL?

1. Logical Backup – Uses tools like pg_dump and pg_dumpall to export data in SQL or
other formats for selective restoration.
2. Physical Backup – Copies database files directly using pg_basebackup or file system-
level backups for full database recovery.
3. Point-In-Time Recovery (PITR) – Combines WAL archiving with a base backup to
restore the database to a specific point in time.
43. What is this wal_level parameter , different values of wal_level?
The wal_level parameter controls the amount of information written to the Write-
Ahead Log (WAL), impacting replication, backups, and point-in-time recovery
(PITR).
Different Values of wal_level
1. minimal → Generates only enough WAL for crash recovery (no replication or PITR).
2. replica (default) → Supports streaming replication and PITR, recording changes at
the row level.
3. logical → Includes additional details for logical replication and decoding (used for
logical replication tools).

44. What is visibility map in postgres?

The Visibility Map (VM) in PostgreSQL is a lightweight data structure that tracks
which pages in a table contain only frozen tuples (i.e., tuples that do not need further
vacuuming). It helps autovacuum and index-only scans work efficiently by reducing
the need to scan unnecessary pages.
When a page is marked all-visible in the Visibility Map, PostgreSQL can skip scanning
it during vacuum operations, improving performance. Additionally, index-only
scans use the VM to determine if they can return results without accessing heap
pages, making queries faster. The VM is automatically maintained by vacuum and
autovacuum processes.
45. What is log_min_duration_statement?

log_min_duration_statement is a PostgreSQL setting that logs SQL statements taking

longer than a specified time, helping in performance monitoring.
46. What is SSL certificate authentication?

SSL certificate authentication in PostgreSQL verifies client identity using SSL/TLS

certificates instead of passwords, ensuring secure and encrypted connections.
47. What is pg_stat_replication?

pg_stat_replication is a PostgreSQL system view that displays real-time information

about active replication connections on the primary server, including lag and status.
48. What are statistics in PostgreSQL?

**Statistics** in PostgreSQL are collected metadata about database objects (such as

tables, indexes, and columns) that help the query planner optimize query execution by
estimating the cost of different query plans. for a couple of seconds.

49. What is pg_stat_bgwriter?

pg_stat_bgwriter is a PostgreSQL view that provides statistics on the background

writer process, helping monitor checkpoint activity and buffer management.
50. What is password encryption?

Password encryption in PostgreSQL secures stored user passwords using hashing

algorithms like MD5, SCRAM-SHA-256, or plaintext (unencrypted) for authentication.
51. What are default privileges?

Default privileges in PostgreSQL define the automatic permissions granted to users or

roles for newly created objects like tables, sequences, or functions.
52. What is pg_stat_database?
 pg_stat_bgwriter is a PostgreSQL view that provides statistics on the background
writer process, helping monitor checkpoint activity and buffer management.
53. What is SSL in PostgreSQL?

SSL (Secure Sockets Layer) in PostgreSQL encrypts client-server communication,

ensuring secure data transmission and protection against eavesdropping and man-in-
the-middle attacks.
54. How do you list all databases and tables?

To list all databases in PostgreSQL, use the following command:

\l -- or \list
To list all tables in the current database, use:
\dt
55. What is visibility map in postgres?

Every heap relation (i.e table/index) have a visibility map associated with [Link]
visibility map has 2 bits per [Link] first bit, if set, indicates that the page is all-visible
(means those pages need not to bevacuumed)The second bit, if set means, all tuples
on this page has been frozen. (No need to vacuum) Note – > Visibility map bits are set
by VACUUM operation. And if data is modified, bits will [Link] condition helps
in index only scan.
56. What is the difference between **VACUUM**, **ANALYZE**, and
**VACUUM FULL**?

In PostgreSQL, VACUUM reclaims storage by cleaning up dead tuples without locking

tables, improving performance over time. ANALYZE collects statistics on table contents
to help the query planner optimize queries. VACUUM FULL, unlike regular VACUUM,
reclaims space more aggressively by compacting tables and indexes, but it requires an
exclusive lock on the table, making it more disruptive.
Use VACUUM regularly, ANALYZE for query optimization, and VACUUM FULL for
severe bloat or space reclamation.
57. What is free space mapping (FSM) in postgres?

Each table/index has a Free space mapping file. It keeps information about which pages
are [Link] VACUUM process also updates the Free Space Map and using VACUUM
FULL we canrecover those free spaces.
58. What is track_io_timing?

track_io_timing is a PostgreSQL setting that enables tracking of I/O operation times

for query performance analysis.
59. What is client authentication?

Client authentication in PostgreSQL is the process of verifying a user’s identity using

methods like password, SSL, LDAP, Kerberos, or trust before granting database
access.
60. How do you perform a failover in PostgreSQL?

To perform a failover in PostgreSQL, promote the standby to primary using pg_ctl

promote or a trigger file. Update application connections to the new primary.
Reconfigure the old primary as a standby if needed. Tools like Patroni, Repmgr, or
Pgpool-II can automate failover for high availability.
61. What is free space mapping(FSM) in postgres?
The Free Space Map (FSM) in PostgreSQL is a data structure that tracks available
free space in table pages. It helps PostgreSQL efficiently find pages with enough room
to insert new tuples, reducing the need for table bloat and minimizing random I/O.
FSM is automatically updated during vacuum and insert operations. When a new row
is inserted, PostgreSQL checks FSM to locate a page with sufficient free space instead
of always appending data to the end of the table. This improves storage efficiency and
helps maintain better performance over time.
62. What is pgcrypto?

pgcrypto is a PostgreSQL extension that provides cryptographic functions for

encryption, hashing, and data security.
63. How do you change a user’s password?

To change a user’s password in PostgreSQL, use the following command:

ALTER USER username WITH PASSWORD 'new_password';

64. Explain Logical Replication vs Physical Replication

Logical Replication in PostgreSQL replicates specific tables or databases by

streaming changes at the SQL level, allowing selective data replication, cross-version
replication, and flexible replication setups. In contrast, Physical Replication copies the
entire database at the disk level using WAL files, providing exact copies of the database
for high availability, but without table-level granularity. Logical replication is more
flexible, while physical replication is faster and simpler for full database replication.
65. How does partitioning work in PostgreSQL?

Partitioning in PostgreSQL divides a large table into smaller, manageable pieces

(partitions) to improve query performance and maintenance. PostgreSQL
supports range, list, and hash partitioning, where data is stored in separate child
tables based on predefined rules. The planner optimizes queries by scanning only
relevant partitions (partition pruning), reducing I/O and execution time.
66. Describe the purpose of the pg_isready command in
[Link] is initial fork?

The pg_isready command in PostgreSQL is used to check the availability and

readiness of a PostgreSQL database server. It helps in monitoring database
 health and is commonly used in scripts and automated systems to detect if the
server is accepting connections.
pg_isready -h localhost -p 5432 -U postgres
67. How do you enable SSL in PostgreSQL?

To enable SSL in PostgreSQL, modify [Link] by setting ssl = on and

specifying certificate and key files (ssl_cert_file and ssl_key_file).
Update pg_hba.conf to allow SSL connections using hostssl. Restart the PostgreSQL
service for changes to take effect. Ensure the server has valid SSL certificates to
establish secure connections.
68. Explain the role of **WAL (Write-Ahead Logging)** in PostgreSQL.

Write-Ahead Logging (WAL) in PostgreSQL ensures data durability and crash

recovery by recording changes to a log before applying them to the data files. WAL
allows PostgreSQL to recover from crashes by replaying logs and supports Point-In-
Time Recovery (PITR) and replication. By minimizing disk writes and
enabling efficient backups, WAL enhances database reliability, consistency, and
performance
69. What is TOAST in PostgreSQL?
TOAST (The Oversized-Attribute Storage Technique) is a mechanism in PostgreSQL
that stores large values efficiently by compressing and storing them out-of-line in a
separate table. It is used when a row exceeds the page size limit (typically 8KB).
When a column, like TEXT, BYTEA, or JSONB, contains a large value,
PostgreSQL automatically compresses and moves it to a TOAST table, keeping only
a reference in the main table. This helps reduce bloat, optimize queries, and improve
performance by keeping frequently accessed data in memory while storing large
values separately.
70. What is log_checkpoints?

log_checkpoints is a PostgreSQL setting that logs checkpoint activity, helping monitor

database write performance and recovery behavior.
71. Explain Row-Level Security (RLS) in PostgreSQL.

Row-Level Security (RLS) in PostgreSQL controls access to individual rows in a table

based on user roles. It is enabled with ALTER TABLE table_name ENABLE ROW LEVEL
SECURITY; and policies are created using CREATE POLICY to define conditions for row
access. RLS ensures that users see only the rows they are authorized to access,
enhancing data security and isolation.
72. Explain the difference between TOAST tables and regular tables in
PostgreSQL.

In PostgreSQL, TOAST (The Oversized-Attribute Storage Technique) tables store

large column values separately from the main table, while regular tables store all data
within the main table heap.
73. What is pg_stat_user_tables?
 pg_stat_user_tables is a PostgreSQL system view that provides statistics on user-
defined tables, including tuples read, inserted, updated, and deleted.
74. What are index types in PostgreSQL?

PostgreSQL supports various index types, including B-Tree, Hash, GIN (Generalized
Inverted Index), GiST (Generalized Search Tree), SP-GiST (Space-Partitioned
GiST), and BRIN (Block Range INdex), each optimized for different query types.
75. What is pg_locks?

pg_locks is a PostgreSQL system view that displays information about locks held by
active transactions, helping in diagnosing concurrency issues.
76. What is the default block size in postgres? Can we set different
block size?
The default block size in PostgreSQL is 8 KB. It is defined at compile time using
the BLCKSZ [Link] cannot change the block size for an existing database, but
you can set a different block size (between 1 KB and 32 KB) by recompiling
PostgreSQL from source with a custom BLCKSZ value.
77. What is pgvector?

pgvector is a PostgreSQL extension for storing and querying high-dimensional vector

embeddings, enabling efficient similarity search.
78. How does PostgreSQL handle **MVCC (Multi-Version Concurrency
Control)?

PostgreSQL uses Multi-Version Concurrency Control (MVCC) to manage concurrent

transactions without blocking reads and writes. Instead of locking rows, MVCC creates
multiple versions of a record, allowing transactions to access the appropriate
snapshot based on their start time. Dead tuples (old versions) are later cleaned up
by autovacuum.
79. What is pg_cron?

pg_cron is a PostgreSQL extension that enables scheduled execution of SQL

commands using the database’s internal cron-based job scheduler.
80. How do you detect and fix database corruption?

To detect database corruption in PostgreSQL, check logs for errors and use tools
like pg_check or pg_dump to identify issues. The pg_catalog views can also help spot
inconsistencies. To fix corruption, restore from a recent backup, use pg_resetwal if
needed, or rebuild corrupted indexes and tables. Regular backups and CHECKSUMS help
prevent and recover from corruption efficiently.
81. What happens in the background during vacuuming process?
Background Process During VACUUM in PostgreSQL
When VACUUM runs in PostgreSQL, it performs multiple background tasks to clean up
dead tuples and optimize storage without blocking normal operations. It scans table
 pages to identify dead tuples left behind by UPDATE and DELETE operations. These
tuples are then marked as reusable in the Free Space Map (FSM), allowing future
inserts to reuse space efficiently.
If AUTOVACUUM is enabled, it runs automatically, adjusting workload based on system
activity. VACUUM FULL, however, rewrites the entire table, reclaiming space but
requiring more resources. Additionally, ANALYZE can be triggered to update statistics,
improving query planner decisions for better performance.
82. What is pg_stat_monitor?

pg_stat_monitor is a PostgreSQL extension that provides enhanced query

performance metrics and statistics for advanced database monitoring and analysis.
83. How do you monitor and manage autovacuum in PostgreSQL?

Use the pg_stat_progress_vacuum view and autovacuum configuration parameters to

monitor and control the automatic vacuum process.

84. What are the different **index types** in PostgreSQL?

PostgreSQL supports several index types to optimize query

performance:
1. B-Tree Index – Default and most commonly used, ideal for range and equality queries.
2. Hash Index – Optimized for exact matches, but less commonly used due to WAL
limitations (before PostgreSQL 11).
3. GIN (Generalized Inverted Index) – Best for full-text search and JSONB queries.
4. GiST (Generalized Search Tree) – Used for geospatial, full-text, and complex data
types.
5. BRIN (Block Range INdex) – Efficient for large tables with sequentially correlated
data.
6. SP-GiST (Space-Partitioned GiST) – Supports hierarchical and non-balanced tree
structures.

85. What is the significance of search_path in postgres?

The search_path in PostgreSQL defines the schema lookup order when executing
SQL queries. It allows users to refer to objects (tables, views, functions) without
specifying their schema explicitly. When a query is run, PostgreSQL searches for the
object in the schemas listed in search_path, from left to right, until it finds a match.
By default, it includes the public schema, but it can be customized to prioritize specific
schemas. This helps organize database objects, avoid naming conflicts, and
enhance security by restricting access to certain schemas.
86. What is logical replication?

Logical replication in PostgreSQL allows selective data replication at the table level
using a publish-subscribe model for real-time data synchronization.
87. What is pg_partman?

pg_partman is a PostgreSQL extension that automates the creation and management of

table partitioning for improved performance and data organization.
88. What is table partitioning?

Table partitioning in PostgreSQL is a technique that divides a large table into smaller,
more manageable pieces (partitions) to improve query performance and maintenance
efficiency.

89. What is parallel query?

1. Multiple workers executing parts of query
2. Controlled by max_parallel_workers parameter
90. Difference between pg_log, pg_clog, pg_xlog?
1. pg_log → Stores database server logs, including errors, queries, and system
messages. This helps in troubleshooting and monitoring database activity.
2. pg_clog (now pg_xact in newer versions) → Stores transaction commit
status information, tracking whether a transaction is committed or rolled back.
Essential for transaction recovery and MVCC consistency.
3. pg_xlog (now pg_wal) → Contains Write-Ahead Logs (WAL), which ensure data
durability and help in crash recovery. It records all changes before they are written to
disk.

91. What is ctid?

ctid is a system column in PostgreSQL that uniquely identifies the physical
location of a row within a table. It stores the block number and tuple index (offset)
within the block, helping PostgreSQL quickly locate rows.
Since ctid changes when a row is updated or moved (e.g., by VACUUM FULL), it is not
a reliable primary key but is useful for efficient row lookups, indexing, and low-
level operations like DELETE optimizations.
92. What is pg_bouncer?

PgBouncer is a lightweight connection pooler for PostgreSQL that reduces overhead by

managing database connections efficiently.
93. How can you monitor and manage replication lag in PostgreSQL
streaming replication?

Calculate replication lag by comparing the current WAL location on the primary with the
corresponding location on the standby

94. What is oid?

Every row in postgres will have a object identifier called oid.
95. What is streaming replication?

Streaming replication in PostgreSQL is a process where a standby server continuously

receives WAL (Write-Ahead Logging) records from the primary server to maintain real-
time data synchronization.

96. difference between oid and relfilenode?

1. oid (Object Identifier) → A unique identifier assigned to database objects like tables,
indexes, and functions. It is stored in system catalogs (pg_class, pg_database, etc.)
and remains constant unless the object is dropped and recreated.
2. relfilenode → Represents the physical file name on disk for a table or index.
PostgreSQL uses relfilenode to map a relation (pg_class.relfilenode) to its actual
storage file in the base/ directory. If VACUUM FULL or REINDEX is performed,
the relfilenode can change, but the oid remains the same.
97. Difference between [Link] and [Link] file?
[Link] is the configuration file of the postgres cluster. But when we do any config
changes using alter system command, then those parameters are added in
[Link] file.
When postgres starts , it will first read [Link] and then it will read
[Link] file.
98. What is the use of **materialized views**?

A materialized view in PostgreSQL stores the result of a query physically on disk,

allowing faster retrieval of complex queries. Unlike regular views, it does not update
automatically and requires REFRESH MATERIALIZED VIEW to update data.
99. What is effective_cache_size?

**effective_cache_size** is a PostgreSQL configuration parameter that estimates the

amount of memory available for disk caching by the operating system and database,
helping the query planner optimize queries.

100. What is work_mem?

**work_mem** is a PostgreSQL configuration parameter that sets the amount of

memory allocated for operations like sorting and hashing before writing data to disk. for
a couple of seconds

101. What is synchronous replication?

Synchronous replication in PostgreSQL ensures that transactions are committed on

both the primary and standby servers simultaneously, providing zero data loss but with
increased latency.
102. What is Foreign Data Wrapper?

Foreign Data Wrapper (FDW) in PostgreSQL allows access to external databases or

data sources as if they were local tables.

103. What is shared_buffers?

shared_buffers is a PostgreSQL configuration parameter that determines the amount
of memory allocated for caching data blocks to reduce disk I/O.
104. What is pgAdmin in PostgreSQL?

pgAdmin is a handy utility that comes with the PostgreSQL installation, and it lets you
do regular database-related tasks through a nice graphical interface.

105. What is pg_upgrade?

pg_upgrade is a PostgreSQL tool that upgrades databases to a new major version

quickly without dumping and restoring data.
106. What is timeline in postgres?
Timeline in postgres is used to distinguish between original cluster and recovered one.
When we initialize the cluster, the timelineid will be set to 1. But if database recovery
happens then it will increase to 2.

107. What is the default port in postgres?

Default is 5432.

108. What is event trigger?

An event trigger in PostgreSQL is a special trigger that fires in response to DDL events
like CREATE, ALTER, or DROP.
109. What is hot_standby?
hot_standby in PostgreSQL allows read-only queries on a standby server while it is
applying WAL changes from the primary server, enabling high availability and load
balancing.
110. What is difference between pg_cancel_backend vs
pg_terminate_backend?
1. pg_cancel_backend(pid) → Gracefully cancels a running query without
terminating the session. It sends a SIGINT signal to the backend process, allowing it to
clean up and stop execution safely. However, the session remains connected.
2. pg_terminate_backend(pid) → Forcibly kills the backend process, terminating the
entire session. It sends a SIGTERM signal, closing all active transactions abruptly,
which may lead to rollback.
Key Difference
3. Use pg_cancel_backend when you want to stop a long-running query without
disconnecting the session.
4. Use pg_terminate_backend when you need to forcefully disconnect a session, such
as in case of blocking transactions or resource-heavy queries.
111. What is the use of pgbench utility?
pgbench is a benchmarking tool used to test PostgreSQL performance by simulating
workloads and measuring transaction throughput. It helps in stress testing,
performance tuning, and identifying bottlenecks. Users can run default or custom
SQL workloads to analyze system efficiency under different loads
112. How can you configure and manage replication slots in
PostgreSQL?

Use the pg_create_logical_replication_slot and pg_drop_replication_slot functions to

manage replication slots.

113. What is table sampling?

Table sampling in PostgreSQL allows querying a random subset of rows from a table
using methods like TABLESAMPLE SYSTEM or TABLESAMPLE BERNOULLI.
114. What is replication slot?
A replication slot in PostgreSQL ensures WAL logs are retained until a replica or
consumer processes them, preventing data loss in streaming replication.
115. How can we encrypt specific columns in postgres?
PostgreSQL does not provide built-in column-level encryption, but you can encrypt
specific columns using PGCrypto or client-side encryption.
116. How to handle connection issues?

Handle connection issues in PostgreSQL by checking network settings, authentication

configurations (pg_hba.conf), server logs, and resource limits.
117. Which utility is used to upgrade postgres cluster?
pgbouncer is used for connection pooling

118. What is Patroni?

Patroni is an open-source cluster management tool for PostgreSQL that automates
high availability, failover, and replication using etcd, Consul, or Zookeeper.
119. How to identify slow queries?

Identify slow queries in PostgreSQL using EXPLAIN ANALYZE, pg_stat_statements,

and log_min_duration_statement.
120. What are the popular tools for managing backup and recovery in
postgres?
edb bart , barman etc
121. How to handle disk full?

VACUUM FULL
Remove unused indexes
Archive old data

122. What are the different types of replication in PostgreSQL?

PostgreSQL supports Streaming Replication (WAL-based real-time standby), Logical

Replication (table-level replication via PUBLICATION/SUBSCRIPTION), Physical
Replication (disk-level data copy), Synchronous Replication (zero data loss),
and Asynchronous Replication (better performance with minimal data loss risk). Tools
like pglogical, Slony-I, and Bucardo offer additional replication options.
123. Describe the purpose of the pg_rewind command in PostgreSQL.

The pg_rewind command in PostgreSQL is used to resynchronize a standby or failed

primary server with a new primary after a failover. Instead of performing a full base
backup, pg_rewind efficiently rewinds the diverged instance by copying only the
changed data, making it faster and reducing downtime.
pg_rewind --target-pgdata=/var/lib/postgresql/data --source-
server="host=new_primary user=postgres"
124. What is an extension in postgres? Which extensions you have
used ?
pg_stat_statements

pg_track_settings

pg_profile

125. How do you set up Streaming Replication?

To set up Streaming Replication in PostgreSQL, enable WAL logging on the primary

server (wal_level = replica, max_wal_senders = 3), create a replication user, and
allow standby connections. On the standby server, use pg_basebackup to copy data,
create [Link] (PostgreSQL 12+), and
configure primary_conninfo in [Link]. Restart the standby server to start
replication.
126. How to kill a session in postgres?
In PostgreSQL, you can terminate or cancel a session using the following functions:
Cancel a Query (without killing the session):
SELECT pg_cancel_backend(pid);
This stops the running query but keeps the session active.
 Terminate a Session (forcefully kill the session):
SELECT pg_terminate_backend(pid);
127. How you monitor long running queries in postgres?
we can use pg_stat_activities to track .

128. How to fix high CPU usage?

Fix high CPU usage in PostgreSQL by optimizing queries, indexing properly, tuning
configuration settings, and monitoring pg_stat_statements for expensive queries.
129. What are **CTEs (Common Table Expressions)**, and how do they
work?

Common Table Expressions (CTEs) in PostgreSQL are temporary result sets that
simplify complex queries and improve readability. Defined using the WITH clause, CTEs
can be referenced multiple times within a query. They are especially useful
for recursive queries and breaking down complex logic.
130. What is the role of pg_stat_replication?

pg_stat_replication is a PostgreSQL system view that monitors replication on

the primary server, showing active standby connections, replication lag, and status.
Key columns include client_addr (standby IP), state (streaming, catchup),
and sent_lsn, write_lsn, replay_lsn (WAL positions). Use it to check replication health:
SELECT pid, client_addr, state, sent_lsn, replay_lsn, sync_state FROM
pg_stat_replication;
131. What is the purpose of the pg_stat_bgwriter view in PostgreSQL?

The pg_stat_bgwriter view in PostgreSQL provides statistics about the background

writer process, which helps manage buffer writes to disk. It tracks metrics
like checkpoints, buffers written, and WAL sync operations, aiding in performance
tuning and monitoring database I/O behavior.
To check bgwriter stats:

SELECT * FROM pg_stat_bgwriter;

132. What is table partitioning in postgres? What are the advantages?
Table Partitioning in PostgreSQL
Table partitioning divides a large table into smaller partitions based on criteria
like range, list, or hash. The parent table is a logical structure, while actual data is
stored in partitions.
Advantages:
1. Faster Queries – Scans only relevant partitions.
2. Efficient Data Management – Easy to archive or drop old partitions.
3. Better Performance – Improves bulk inserts and deletes.
4. Optimized Indexing – Smaller and more efficient indexes.
5. Reduced Contention – Enhances concurrency by distributing load.
133. Between postgres and nosql database like mongodb , which one is
better?
The choice between PostgreSQL and MongoDB depends on your use case:
PostgreSQL (Relational DB) – Best for structured data, strong ACID compliance,
complex queries, and transactions. Suitable for financial systems, analytics, and
applications requiring strict data [Link] (NoSQL, Document DB) –
Ideal for unstructured or semi-structured data, high scalability, and flexible
schemas. Best for big data, real-time apps, and fast-growing applications with
schema changes.
Which is Better?
Choose PostgreSQL for reliable transactions, reporting, and complex queries.
Choose MongoDB for scalability, flexibility, and high-speed document-based
storage.
134. Explain the difference between **JSON and JSONB** in PostgreSQL.
In PostgreSQL, JSON stores data as a plain text representation, preserving formatting
but requiring re-parsing for each query. JSONB, on the other hand, stores data in
a binary format, allowing faster searches, indexing, and efficient storage.
While JSON is better for simple storage and retrieval, JSONB is preferred for complex
queries and indexing.
135. CREATE TABLE data (info JSON, info_b JSONB);
136. What are some key difference between oracle and postgres?
Oracle is a proprietary, enterprise-grade relational database, while PostgreSQL is
an open-source, community-driven database. Oracle offers advanced scalability,
partitioning, and multi-tenant architecture, making it ideal for large enterprises and
mission-critical applications. It supports PL/SQL for procedural programming,
advanced replication options like Active Data Guard and GoldenGate, and enterprise-
grade backup and recovery tools such as RMAN.
PostgreSQL, on the other hand, is highly extensible, allowing users to define custom
data types, functions, and extensions. It supports PL/pgSQL, along with other
languages like Python and Java. PostgreSQL is preferred for modern
applications due to its native JSON support, flexible partitioning, and cost-
effectiveness. While Oracle provides robust enterprise solutions, PostgreSQL excels
in openness, flexibility, and cost efficiency, making it a strong alternative for
organizations looking to avoid high licensing costs.
137. How do you create a new database in PostgreSQL?

To create a new database, use the SQL command:

sqlCopy code
CREATE DATABASE dbname;
138. How to handle replication lag?

Handle replication lag in PostgreSQL by tuning wal_sender_timeout, optimizing

network performance, increasing max_wal_size, and monitoring pg_stat_replication.
139. How does partitioning work in PostgreSQL?
 Partitioning in PostgreSQL divides a large table into smaller, manageable pieces
called partitions based on a specified column. It improves query performance and
maintenance. PostgreSQL supports range, list, and hash partitioning.
140. While dropping a postgres database i am getting error? What
might be the issue?
If you want to drop a database , then you need to fire the command, after connecting to
a different database in the same postgres cluster with superuser privilege.

141. How to fix database bloat?

Fix database bloat in PostgreSQL by running VACUUM (FULL), ANALYZE,

using autovacuum, and implementing table partitioning where necessary.
142. What is the pg_dump method used for?

The pg_dump method allows you to create a text file with a set of SQL commands that,
when run in a PostgreSQL server, will recreate the database in the same state as it was
at the moment of the dump.

143. Explain the concept of roles and privileges in PostgreSQL.

PostgreSQL uses roles to manage user access and permissions. A role can be
a user (with LOGIN) or a group (without LOGIN). Privileges define what actions a role
can perform, such as SELECT, INSERT, UPDATE, DELETE, CREATE, CONNECT,
and EXECUTE. Roles are granted privileges using the GRANT command and revoked
using REVOKE. This role-based access control (RBAC) system ensures secure and
efficient permission management.
144. What is the latest version of postgres in market?
Postgres 15.

145. What is the purpose of the pg_hba.conf file in PostgreSQL?

The pg_hba.conf file specifies client authentication rules, controlling which hosts are
allowed to connect to the PostgreSQL server and how they authenticate.

146. How to handle connection pooling?

Handle connection pooling in PostgreSQL using tools like PgBouncer or Pgpool-II to

manage and optimize database connections efficiently.
147. How can i check the version of postgres?
cat PG_VERSION file.

or select pg_version();
148. How do you perform a backup and restore in PostgreSQL?

Use the pg_dump command for backups and the pg_restore command for restores.
Example:
bashCopy code
pg_dump -h localhost -U username dbname > [Link] pg_restore -h localhost -U
username -d dbname [Link].
149. How to fix lock contention?

Fix lock contention in PostgreSQL by identifying blocking queries with pg_locks,

optimizing transactions, using proper indexing, and minimizing long-held locks.
150. How pg_repack works internally?

pg_repack reorganizes tables and indexes online without locking writes. It creates a
new table, copies data efficiently while tracking changes via triggers, applies
modifications, and swaps the old table with the new one. This helps reclaim space and
optimize performance without downtime.

151. What is the purpose of the pg_stat_user_tables view in

PostgreSQL?

The pg_stat_user_tables view provides statistics about table-level activity and

performance

152. Is there a way in which we can rebuild/reorg a table online to

release free space?
As we know vacuum full is used to rebuild table and it releases free space to operating
system. However this method, puts an exclusive lock on the table.

153. Describe the purpose of the pg_stat_progress_cluster view in

PostgreSQL.

The pg_stat_progress_cluster view provides information about the progress of cluster

reorganization operations.

154. what is the maximum file size of table or index in postgres? Can
we increase that ?
Max size is 1GB. If a table size is big, then it can spread across multiple files. lets says
the file_name is 19870 . and once it reaches 1gb, a new file will be created as 19870.1 .
155. How can you implement high availability and failover in
PostgreSQL?

Use tools like Patroni or repmgr to set up and manage streaming replication and
automatic failover.

156. How to handle index corruption?

Handle index corruption in PostgreSQL by rebuilding the index using REINDEX, or

recreating it if necessary to restore data integrity.
157. What are the different datatypes in postgres?
Boolean
char,vchar,
int,float(n)
uuid
date
158. Explain the concept of parallel queries in PostgreSQL.

Parallel queries use multiple worker processes to execute parts of a query concurrently,
improving query performance.

159. How to improve checkpoint performance?

Improve checkpoint performance in PostgreSQL by

tuning checkpoint_timeout, max_wal_size, checkpoint_completion_target, and
monitoring pg_stat_bgwriter.
160. Difference between explain and explain analyze in postgres?
Explain – > Generates query plan by calculating the cost
Explain analyze -> It will execute the query and provides query statistics of the executed
query. This gives more accurate plan details. Please be careful while running
insert,update,delete like DML commands with explain analyze, as it will run the query
and cause data changes.
161. What is the meaning of PgAdmin?
PgAdmin is a free open-source graphical front-end PostgreSQL database administration
tool. This web-based GUI tool is prominently used to manage PostgreSQL databases. It
assists in monitoring and managing numerous complex PostgreSQL and EDB database
systems. PgAdmin is used to accomplish tasks like accessing, developing, and carrying
out quality testing procedures.
162. What is the full form of MVCC?
The full form of MVCC is Multi-version Concurrency Control.

163. Describe the purpose of the pg_xact directory in PostgreSQL.

 The pg_xact directory contains transaction status files that track the state of active
transactions.

164. What is the full form of GEQO?

The full form of GEQO is Genetic Query Optimization. It enables non-exhaustive search
to efficiently manage large join queries in PostgreSQL.

165. What is random page cost?

random_page_cost is a PostgreSQL parameter that defines the cost of fetching a
random disk page during query execution. It influences the query planner’s decision
when choosing between index scans and sequential scans. A higher
value (default: 4.0) makes sequential scans more favorable, while a lower
value encourages index scans. Adjusting this setting based on storage type (HDD vs.
SSD) can improve query performance.
166. what different types of streaming replications are present in
postgres? And which parameters control that.
PostgreSQL supports asynchronous, synchronous, and logical
replication. Asynchronous improves performance but risks data
loss, synchronous ensures zero data loss by waiting for standby confirmation,
and logical replication allows selective table replication.
Key parameters:
wal_level (replica/logical)
max_wal_senders (limits WAL sender processes)
synchronous_commit (controls commit behavior)
primary_conninfo (connects standby to primary
hot_standby (enables read queries on standby)
167. How can you monitor and manage autocommit behavior in
PostgreSQL?

Use the autocommit configuration parameter and the BEGIN and COMMIT statements
to control autocommit behavior.

168. what is the use of share_preload_libraries in [Link] file?

Usually when we add extensions like pg_stat_statement, then we need to add the
library path in the parameter shared_preload_libraries.
Because these extensions use shared memory, we need to restart the postgres cluster.
the reason we are preloading these libraries is to avoid the library startup time, when
the library is first used.
169. Explain the concept of logical replication in PostgreSQL.

Logical replication replicates data changes using a publish-subscribe mechanism at the

row level.
170. What are foreign data wrappers? What is its use?
Foreign Data Wrappers (FDW) allow PostgreSQL to access external databases as if
they were local tables. They use the SQL/MED (SQL Management of External Data)
standard to fetch data from sources like other PostgreSQL instances, MySQL,
MongoDB, or even CSV files.

171. Describe the purpose of the pg_resetxlog utility in PostgreSQL.

The pg_resetxlog utility is used to reset the write-ahead log (WAL) and recover from
severe corruption.

172. Difference between role and user in postgres? Can we convert a

role to user?
Role and user are almost same in postgres, only difference is , a role cannot login , But
a user can. We can say like a user is role with login privilege.
And yes we can convert the role to a user.
alter role <role_name> nologin;
173. What are the default databases created after setting up postgres
cluster?
postgres=# select datname from pg_database;
datname
———–
postgres
template1
template0
174. What is the use of temporary tablespace?
A temporary tablespace in PostgreSQL is used to store temporary objects like temp
tables, sorting operations, and hash joins. It helps improve performance by
preventing temp data from consuming the main tablespace. You can assign a
temporary tablespace using:
SET temp_tablespaces = 'my_temp_tablespace';
175. How can you implement data encryption in PostgreSQL?

PostgreSQL supports encryption via PGCrypto for column-level encryption, SSL for
secure connections, and disk-level encryption using LUKS or filesystem tools.
Transparent Data Encryption (TDE) requires third-party solutions.
176. Explain the concept of full-text search in PostgreSQL.

Full-text search allows you to search for words or phrases within text documents stored
in the database.

177. What is vacuum?

 In PostgreSQL, VACUUM is a maintenance operation that removes dead tuples, frees
up space, and updates visibility maps to optimize database performance.
178. What tools do you use for monitoring PostgreSQL performance?

To monitor PostgreSQL performance, use pg_stat_activity to track active queries and

long-running transactions, and pg_stat_statements to analyze query execution
statistics. Tools like EXPLAIN ANALYZE help optimize queries,
while pgAdmin provides a GUI for real-time monitoring. For advanced monitoring, tools
like Prometheus with PostgreSQL Exporter, Grafana, and PGBouncer can be used
to track performance metrics, connections, and query execution times.
179. What are the tablespaces created by default after installing
postgres cluster?
After installing a PostgreSQL cluster, two default tablespaces are created:

1. pg_default – Stores user-defined tables, indexes, and other database objects by

default.
2. pg_global – Stores shared system catalogs that are accessible across all databases in
the cluster.
These tablespaces manage database storage unless additional tablespaces are
created.

180. What is analyze?

In PostgreSQL, ANALYZE collects statistics on table data distribution to help the query
planner generate efficient execution plans.
181. How do you check the current connections and terminate a
session?

To check current connections in PostgreSQL, query pg_stat_activity:

SELECT pid, usename, datname, client_addr, application_name, state FROM
pg_stat_activity;
To terminate a specific session, use:

SELECT pg_terminate_backend(pid);
For immediate termination, use:

SELECT pg_cancel_backend(pid);
182. What are common causes of deadlocks, and how do you resolve
them?

Deadlocks in PostgreSQL occur when two or more transactions hold locks that the other
transactions need, causing a cyclic wait. Common causes include transactions
updating rows in different orders, long-running transactions holding locks for too
 long, and foreign key constraints with cascading updates/deletes. To resolve
deadlocks, ensure transactions access resources in a consistent order, keep
transactions short, and use appropriate indexing to reduce lock contention.
Monitoring pg_stat_activity and using deadlock_timeout settings can help detect
and troubleshoot deadlocks efficiently.
183. How do you handle bloated tables and indexes?

To handle bloated tables and indexes in PostgreSQL, use

the VACUUM and ANALYZE commands to reclaim storage and update statistics. For severe
bloat, run VACUUM FULL, which locks the table but compacts it effectively.
Use REINDEX to rebuild bloated indexes.
The pg_stat_user_tables and pg_stat_all_indexes views help identify bloat. For
automated maintenance, enable autovacuum or use tools like pg_repack to reclaim
space without locking tables.
184. Describe the purpose of the pg_wal directory in PostgreSQL.

The pg_wal directory in PostgreSQL stores Write-Ahead Logging (WAL) files, which
ensure data durability and crash recovery. WAL records all changes before they are
written to disk, allowing PostgreSQL to recover uncommitted transactions after a crash.
It also plays a key role in replication and point-in-time recovery (PITR).
185. How do you check for long-running queries?

Use the following query to check long-running queries in PostgreSQL:

SELECT pid, age(clock_timestamp(), query_start) AS duration, state, query

FROM pg_stat_activity

WHERE state = 'active'

ORDER BY duration DESC;

186. What is the use of EXPLAIN ANALYZE?

EXPLAIN ANALYZE in PostgreSQL is used to analyze and optimize query performance. It

provides a detailed execution plan by showing how the database processes the query,
including cost estimates, row estimates, index usage, join methods, and execution
time. This helps identify inefficiencies such as sequential scans instead of index
scans, slow joins, or misestimated row counts. By using EXPLAIN ANALYZE,
developers can fine-tune queries by adding indexes, rewriting queries, or adjusting
PostgreSQL configuration settings to improve performance.
187. How do you perform Point-In-Time Recovery (PITR)?

To perform Point-In-Time Recovery (PITR) in PostgreSQL, first take a physical base

backup using pg_basebackup or by copying the data directory. Enable WAL
 archiving by setting archive_mode = on and archive_command. To restore, stop the
database, replace data files with the base backup, and configure recovery
settings in [Link] (or [Link] in newer versions)
with restore_command and recovery_target_time. Restart PostgreSQL, and it
will replay WAL logs up to the specified point, ensuring precise data recovery.
188. What is the purpose of **pg_hba.conf**?

The pg_hba.conf (PostgreSQL Host-Based Authentication) file controls client

authentication in PostgreSQL. It defines which users can connect, from which hosts,
and what authentication methods are required. Each entry specifies a connection type
(local, host, hostssl, hostnossl), database, user, client IP address, and authentication
method (e.g., md5, trust, peer, scram-sha-256). PostgreSQL checks this file when a
client attempts to connect, ensuring secure access based on predefined rules.
189. How do you optimize query performance in PostgreSQL?

To optimize query performance in PostgreSQL, use EXPLAIN ANALYZE to analyze

execution plans and identify bottlenecks. Create appropriate indexes (B-tree, GIN,
BRIN) based on query patterns. Use VACUUM and ANALYZE to update statistics and
prevent bloat. Optimize queries with proper JOINs, WHERE filters, and indexing on
frequently searched columns. Avoid **SELECT ***; instead, fetch only required
columns. Use partitioning for large tables and connection pooling for handling
multiple queries efficiently. Tune PostgreSQL settings like work_mem, shared_buffers,
and parallel_execution for better performance.
190. How do you restore a corrupted database?

To restore a corrupted Oracle database, first identify the issue

using V$DATABASE_BLOCK_CORRUPTION and alert logs. If datafiles are missing or corrupt,
restore them using RMAN: RESTORE DATABASE; RECOVER DATABASE;. For control file
corruption, restore from backup or recreate it. If redo logs are corrupted, clear or drop
them. Block corruption can be fixed with RMAN BLOCKRECOVER or DBMS_REPAIR. If the
entire database is affected, perform a full RMAN restore and recovery. Regular
backups, ARCHIVELOG mode, and proactive monitoring help prevent future corruption.
191. What is the purpose of the pg_basebackup command?

The pg_basebackup command is used to take a physical backup of a PostgreSQL

database, including data files and WAL segments. It is commonly used for full
database backups, replication setup, and Point-In-Time Recovery (PITR). This tool
ensures fast, consistent backups without requiring database downtime.