Backend interview prep

Start

PostgreSQL Basics Interview Questions

Master PostgreSQL fundamentals with interview-focused questions covering PostgreSQL architecture, features, ACID, MVCC overview, data types, JSONB, UUID, schemas, extensions, psql, pgAdmin, and enterprise production best practices.

Introduction

PostgreSQL is one of the world's most advanced Open Source Relational Database Management Systems (RDBMS).

It is widely used in

  • Banking
  • Healthcare
  • Insurance
  • E-Commerce
  • SaaS Platforms
  • Cloud-native Applications
  • Microservices
  • Analytics

PostgreSQL is famous for

  • Reliability
  • ACID Compliance
  • MVCC
  • High Performance
  • Extensibility
  • JSON Support
  • Advanced Indexing

Today, PostgreSQL is one of the most commonly used databases in enterprise Java, Spring Boot, Kubernetes, and cloud-native applications.

PostgreSQL Architecture Overview

flowchart LR

Application --> PostgresqlServer["PostgreSQL Server"]

PostgresqlServer["PostgreSQL Server"] --> SharedBuffers["Shared Buffers"]

PostgresqlServer["PostgreSQL Server"] --> BackgroundProcesses["Background Processes"]

PostgresqlServer["PostgreSQL Server"] --> DataFiles["Data Files"]

BackgroundProcesses["Background Processes"] --> WAL

DataFiles["Data Files"] --> Disk

1. What is PostgreSQL?

Answer

PostgreSQL is an advanced Open Source Object-Relational Database Management System (ORDBMS).

It supports

  • SQL
  • ACID Transactions
  • MVCC
  • JSON
  • JSONB
  • Stored Procedures
  • Partitioning
  • Replication
  • Extensions

It is known for enterprise-grade reliability and standards compliance.

2. Why is PostgreSQL popular?

Reasons include

  • Open Source
  • ACID Compliance
  • Excellent Performance
  • JSONB Support
  • Advanced Indexing
  • Rich Extension Ecosystem
  • Strong Community
  • Cloud Ready

3. What are the main features of PostgreSQL?

  • ACID Transactions
  • MVCC
  • JSON & JSONB
  • Window Functions
  • Common Table Expressions (CTEs)
  • Full Text Search
  • Partitioning
  • Replication
  • Extensions
  • Foreign Data Wrappers

PostgreSQL Feature Stack

flowchart TD

PostgreSQL --> SQL

PostgreSQL --> MVCC

PostgreSQL --> JSONB

PostgreSQL --> Replication

PostgreSQL --> Partitioning

PostgreSQL --> Extensions

4. What is the difference between PostgreSQL and MySQL?

PostgreSQL MySQL
Open Source Open Source
Object-Relational Relational
Better Standards Compliance Simpler
Native JSONB JSON
Advanced Indexes Limited Index Types
Better Complex Queries Better Simple Workloads

5. PostgreSQL vs Oracle?

PostgreSQL Oracle
Open Source Commercial
Community Driven Oracle Corporation
JSONB Oracle JSON
Lower Cost Expensive Licensing
Extensions Enterprise Features

6. What is ACID?

PostgreSQL fully supports

  • Atomicity
  • Consistency
  • Isolation
  • Durability

This guarantees reliable transaction processing.

ACID Properties

flowchart LR

Atomicity --> Consistency --> Isolation --> Durability

7. What is MVCC?

MVCC stands for

Multi-Version Concurrency Control

Instead of locking rows for readers,

PostgreSQL creates multiple row versions.

Benefits

  • High Concurrency
  • Non-blocking Reads
  • Better Performance

(MVCC is covered in detail later.)

8. What data types are available?

Numeric

  • SMALLINT
  • INTEGER
  • BIGINT
  • NUMERIC
  • DECIMAL

Character

  • CHAR
  • VARCHAR
  • TEXT

Date

  • DATE
  • TIME
  • TIMESTAMP

Others

  • BOOLEAN
  • UUID
  • JSON
  • JSONB
  • BYTEA
  • ARRAY

9. What is TEXT datatype?

TEXT stores variable-length strings.

Unlike VARCHAR,

TEXT has no practical length limit for most applications.

Example

description TEXT

10. What is UUID?

UUID

Universally Unique Identifier

Example

550e8400-e29b-41d4-a716-446655440000

Useful for

  • Distributed Systems
  • Microservices
  • Public APIs

11. What is JSON?

JSON stores documents

as plain text.

Advantages

  • Flexible Structure

Disadvantages

  • Slower Processing
  • Limited Indexing

12. What is JSONB?

JSONB stores JSON

in binary format.

Benefits

  • Faster Queries
  • Efficient Storage
  • GIN Index Support

JSONB is generally preferred over JSON.

JSON vs JSONB

JSON JSONB
Text Binary
Slower Faster
Preserves Formatting Optimized Storage
Limited Indexing Supports GIN Index

13. What are Arrays?

PostgreSQL supports array columns.

Example

skills TEXT[];

Values

Java

Spring

Docker

can be stored in one column.

14. What are Extensions?

Extensions add extra functionality.

Examples

  • PostGIS
  • pg_stat_statements
  • pgcrypto
  • uuid-ossp
  • hstore

Extension Example

CREATE EXTENSION pgcrypto;

15. What is psql?

psql is PostgreSQL's command-line client.

Used for

  • Running SQL
  • Managing Databases
  • Administration
  • Automation

16. What is pgAdmin?

pgAdmin is PostgreSQL's graphical administration tool.

Features

  • SQL Editor
  • Database Browser
  • Query Tool
  • Backup & Restore
  • Performance Dashboard

PostgreSQL Client Tools

flowchart LR

psql --> PostgreSQL

pgAdmin --> PostgreSQL

JDBC --> PostgreSQL

17. What is a Schema?

A Schema is a logical container inside a database.

Contains

  • Tables
  • Views
  • Functions
  • Indexes
  • Sequences

Example

public

sales

hr

Database Structure

Database

├── Schema
│     ├── Tables
│     ├── Views
│     ├── Functions
│     └── Indexes

18. What is the default schema?

The default schema is

public

19. What are System Catalogs?

System catalogs store metadata about database objects.

Examples

  • Tables
  • Columns
  • Indexes
  • Constraints
  • Users

20. Common System Catalogs

  • pg_class
  • pg_database
  • pg_namespace
  • pg_attribute
  • pg_index
  • pg_roles

Example

SELECT *

FROM pg_tables;

21. What is PostgreSQL Object-Relational?

PostgreSQL supports

  • Tables
  • User-defined Types
  • Arrays
  • JSON
  • Composite Types
  • Inheritance

This extends traditional relational databases.

22. Does PostgreSQL support Stored Procedures?

Yes.

Supports

  • Functions
  • Procedures

using

  • PL/pgSQL
  • SQL
  • Python
  • Perl
  • JavaScript (via extensions)

23. What languages are supported?

Examples

  • SQL
  • PL/pgSQL
  • PL/Python
  • PL/Perl

24. Banking Example

Tables

  • Customer
  • Account
  • Transaction

Uses

  • ACID
  • MVCC
  • Replication

25. E-Commerce Example

Tables

  • Product
  • Customer
  • Order
  • Payment

Uses

  • JSONB
  • GIN Indexes
  • Partitioning

26. SaaS Example

Store tenant configuration

inside JSONB

while relational data remains in tables.

27. Advantages of PostgreSQL

  • Open Source
  • Enterprise Ready
  • MVCC
  • JSONB
  • Rich SQL Support
  • Advanced Indexes
  • Extensions
  • Replication
  • Partitioning
  • High Reliability

28. Limitations

  • Slightly steeper learning curve
  • Fewer GUI tools than some commercial databases
  • Autovacuum requires tuning for large workloads
  • Some enterprise Oracle features require alternative implementations

29. Common PostgreSQL Beginner Mistakes

  • Choosing JSON instead of JSONB
  • Ignoring indexes
  • Using SELECT *
  • Not understanding MVCC
  • Ignoring VACUUM
  • Poor schema design

Enterprise Best Practices

  • Prefer JSONB over JSON for most applications.
  • Use UUID for distributed systems.
  • Normalize relational data appropriately.
  • Use schemas for logical separation.
  • Create indexes based on query patterns.
  • Monitor autovacuum activity.
  • Keep PostgreSQL updated.
  • Use extensions only when necessary.
  • Regularly analyze execution plans.
  • Enable monitoring in production.

Quick Revision

Topic Key Point
PostgreSQL Open Source ORDBMS
ACID Reliable Transactions
MVCC Multi-Version Concurrency
JSON Text Storage
JSONB Binary JSON
UUID Unique Identifier
TEXT Unlimited String
Schema Logical Container
psql Command Line Client
pgAdmin GUI Tool
Extensions Extra Features
System Catalog Metadata

Interview Tips

Interviewers frequently ask

  • What is PostgreSQL?
  • PostgreSQL vs MySQL.
  • PostgreSQL vs Oracle.
  • What is MVCC?
  • JSON vs JSONB.
  • Why is JSONB faster?
  • What is UUID?
  • What are PostgreSQL extensions?
  • What is psql?
  • What is a schema?

A strong interview explanation is:

"PostgreSQL is an enterprise-grade open-source object-relational database that combines traditional relational database capabilities with advanced features such as MVCC, JSONB, extensibility, advanced indexing, partitioning, replication, and strong SQL standards compliance. It is widely used in cloud-native applications, Spring Boot microservices, and high-performance enterprise systems."

Summary

PostgreSQL is a powerful open-source object-relational database designed for enterprise applications. Its support for ACID transactions, MVCC, JSONB, advanced indexing, extensions, partitioning, and replication makes it one of the most versatile databases available today.

Understanding PostgreSQL fundamentals—including architecture, data types, schemas, JSONB, UUIDs, extensions, and client tools—provides the foundation for mastering PostgreSQL architecture, indexing, partitioning, MVCC, replication, and performance tuning in enterprise environments.