All questions
Showing of 36Explain the ACID properties in PostgreSQL.
Answer it yourself first - out loud, or typed below.
How should your speech become text?
Listening… your words appear above as you speak - tap Stop when you're done.
Recording · cr - tap Stop & transcribe when you're done.
Transcribing with AI…
Voice:
Last attempt -
ACID properties ensure database reliability:
- Atomicity: Transactions are all-or-nothing. If any part fails, the entire transaction rolls back
- Consistency: Database remains in a valid state before and after transactions
- Isolation: Concurrent transactions don't interfere with each other
- Durability: Committed changes persist even after system failures
Example:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- Both operations succeed or both fail
This answer doesn't lend itself to a diagram - it reads best . No credits were charged.
Why there's no diagram: “”
The interactive diagram is below the answer - jump to diagram ↓ · Below it, the related concept . Jump to it ↓
What are the main PostgreSQL data types?
Answer it yourself first - out loud, or typed below.
How should your speech become text?
Listening… your words appear above as you speak - tap Stop when you're done.
Recording · cr - tap Stop & transcribe when you're done.
Transcribing with AI…
Voice:
Last attempt -
PostgreSQL supports various data types:
- Numeric: INTEGER, BIGINT, DECIMAL, NUMERIC, REAL, DOUBLE PRECISION
- Character: CHAR, VARCHAR, TEXT
- Date/Time: DATE, TIME, TIMESTAMP, INTERVAL
- Boolean: BOOLEAN
- Binary: BYTEA
- JSON: JSON, JSONB
- Arrays: Any data type can be an array
- UUID: Universally Unique Identifiers
- Network: INET, CIDR for IP addresses
This answer doesn't lend itself to a diagram - it reads best . No credits were charged.
Why there's no diagram: “”
The interactive diagram is below the answer - jump to diagram ↓ · Below it, the related concept . Jump to it ↓
What is the difference between PRIMARY KEY and UNIQUE constraints?
Answer it yourself first - out loud, or typed below.
How should your speech become text?
Listening… your words appear above as you speak - tap Stop when you're done.
Recording · cr - tap Stop & transcribe when you're done.
Transcribing with AI…
Voice:
Last attempt -
Both ensure uniqueness, but with key differences:
PRIMARY KEY:
- Cannot contain NULL values
- Only one per table
- Automatically creates a unique index
- Used for table relationships
UNIQUE:
- Can contain NULL values (multiple NULLs allowed)
- Multiple unique constraints per table
- Creates a unique index
CREATE TABLE users (
id SERIAL PRIMARY KEY, -- Cannot be NULL
email VARCHAR(255) UNIQUE, -- Can have one NULL
username VARCHAR(50) UNIQUE -- Multiple unique constraints allowed
);
This answer doesn't lend itself to a diagram - it reads best . No credits were charged.
Why there's no diagram: “”
The interactive diagram is below the answer - jump to diagram ↓ · Below it, the related concept . Jump to it ↓
Explain the difference between DELETE, TRUNCATE, and DROP.
Answer it yourself first - out loud, or typed below.
How should your speech become text?
Listening… your words appear above as you speak - tap Stop when you're done.
Recording · cr - tap Stop & transcribe when you're done.
Transcribing with AI…
Voice:
Last attempt -
- DELETE: Removes specific rows based on conditions, can be rolled back, triggers fire
- TRUNCATE: Removes all rows quickly, can be rolled back, triggers don't fire
- DROP: Removes the entire table structure and data, cannot be rolled back
DELETE FROM users WHERE age < 18; -- Removes specific rows
TRUNCATE TABLE users; -- Removes all rows
DROP TABLE users; -- Removes table entirely
This answer doesn't lend itself to a diagram - it reads best . No credits were charged.
Why there's no diagram: “”
The interactive diagram is below the answer - jump to diagram ↓ · Below it, the related concept . Jump to it ↓
What is a foreign key and how does it work in PostgreSQL?
Answer it yourself first - out loud, or typed below.
How should your speech become text?
Listening… your words appear above as you speak - tap Stop when you're done.
Recording · cr - tap Stop & transcribe when you're done.
Transcribing with AI…
Voice:
Last attempt -
A foreign key establishes and enforces a link between data in two tables. It prevents actions that would destroy links between tables.
CREATE TABLE departments (
dept_id SERIAL PRIMARY KEY,
dept_name VARCHAR(100)
);
CREATE TABLE employees (
emp_id SERIAL PRIMARY KEY,
emp_name VARCHAR(100),
dept_id INTEGER REFERENCES departments(dept_id)
);
Foreign key constraints ensure referential integrity by preventing:
- Insertion of records with non-existent foreign key values
- Deletion of referenced records (unless CASCADE is specified)
This answer doesn't lend itself to a diagram - it reads best . No credits were charged.
Why there's no diagram: “”
The interactive diagram is below the answer - jump to diagram ↓ · Below it, the related concept . Jump to it ↓
What are indexes and why are they important?
Explain different types of JOINs in PostgreSQL with examples.
What are Common Table Expressions (CTEs) and when would you use them?
Explain window functions and provide practical examples.
What is the difference between a view and a materialized view?
Explain PostgreSQL transaction isolation levels.
What are stored procedures and functions in PostgreSQL?
Explain triggers and provide a practical example.
What is MVCC and how does it work in PostgreSQL?
Explain the difference between UNION and UNION ALL.
What is PostgreSQL connection pooling and how does PgBouncer work?
Explain PostgreSQL's query execution plan and how to optimize queries.
What is table partitioning and when should you use it?
Explain different types of indexes in PostgreSQL and their use cases.
What is connection pooling and why is it important?
Explain PostgreSQL replication types and their use cases.
What are the key considerations for PostgreSQL security?
Explain VACUUM, ANALYZE, and REINDEX in PostgreSQL.
What are PostgreSQL extensions and name some important ones?
Explain JSON and JSONB data types and their differences.
How do you handle deadlocks in PostgreSQL?
Explain Write-Ahead Logging (WAL) and its importance.
What are the best practices for PostgreSQL backup and recovery?
How do you monitor and troubleshoot PostgreSQL performance?
Explain PostgreSQL's approach to handling large objects and binary data.
What are database roles and how do you implement role-based access control?
Explain PostgreSQL's foreign data wrappers (FDW).
How do you implement custom aggregates and operators in PostgreSQL?
What is logical decoding and how is it used?
How do you implement database sharding strategies in PostgreSQL?
How do you implement audit logging in PostgreSQL?
This answer is part of Pro.
The full written answer, with the trade-offs and follow-ups an interviewer will probe.
No matches
Try a different filter or search term.
PostgreSQL cheatsheet
- PostgreSQL Basics01
- Basic SQL Commands02
- Data Types03
- CRUD Operations04
- Joins05
- Indexes06
- Constraints07
- Transactions08
- Advanced Queries09
- JSON Operations10
- Performance Optimization11
- Views and Materialized Views12
- + 8 more inside
- + 14 more inside
31 of 36 PostgreSQL answers are in Pro.
Full answers, code samples, and AI explanations that go simpler or deeper. Cancel anytime.
- Full answers + code
- AI explanations, simpler or deeper
- 1,000 AI credits / month
- Cancel anytime
Change topic
Pick a different technology or stack. Your current topic stays put until you choose a new one.
MEAN
MongoDB, Express, Angular, Node.jsMERN
MongoDB, Express, React, Node.jsDjango
Python Full-Stack DevelopmentRuby on Rails
Convention over ConfigurationServerless on AWS
Serverless Architecture on AWSInterviewers also test these - they're common to every stack, whichever one you picked above.
Flutter Mobile
Flutter Cross-Platform Mobile DevelopmentInterviewers also test these - they're common to every stack, whichever one you picked above.
Spring Boot
Enterprise Java Development.NET
Microsoft EcosystemVue
Vue.js, Vite, TypeScript, Tailwind, Node.jsGo Backend
Golang, gRPC, PostgreSQL, Redis, RabbitMQInterviewers also test these - they're common to every stack, whichever one you picked above.
FastAPI
Python, FastAPI, SQLAlchemy, PostgreSQLReact Native
React, TypeScript, Redux, FirebaseiOS Native
Swift, SwiftUI, UIKit, FirebaseAndroid Native
Java, Jetpack Compose, FirebaseDevOps / Platform
Docker, Kubernetes, Terraform, CI/CDInterviewers also test these - they're common to every stack, whichever one you picked above.
AI Engineer
LLMs, RAG, Agents, EvalsAI-Powered Developer
Claude Code, Copilot, Agentic WorkflowsCore SWE Interview Prep
Data structures, algorithms, OS, concurrency, networking, gitInterviewers also test these - they're common to every stack, whichever one you picked above.
Interviewers also test these - they're common to every stack, whichever one you picked above.