Key Technologies
PostgreSQL
Learn when and how to use PostgreSQL in your system design interviews
There's a good chance you'll find yourself discussing PostgreSQL in your system design interview. After all, it's consistently ranked as the most beloved database in Stack Overflow's developer survey and is used by companies from Reddit to Instagram and even the very website you're reading right now.
That said, it's important to understand that while PostgreSQL is packed with features and capabilities, your interviewer isn't looking for a database administrator. They want to see that you can make informed architectural decisions. When should you choose PostgreSQL? When should you look elsewhere? What are the key trade-offs to consider?
I often see candidates get tripped up here. They either dive too deep into PostgreSQL internals (talking about MVCC and WAL when the interviewer just wants to know if it can handle their data relationships), or they make overly broad statements like "NoSQL scales better than PostgreSQL" without understanding the nuances.
In this deep dive, we'll focus specifically on what you need to know about PostgreSQL for system design interviews. We'll start with a practical example, explore the key capabilities and limits that should inform your choices, and build up to common interview scenarios.
For this deep dive, we're going to assume you have a basic understanding of SQL. If you don't, I've added an Appendix: Basic SQL Concepts at the end of this page for you to review.
A Motivating Example
Imagine we're designing a social media platform - not a massive one like Facebook, but one that's growing and needs a solid foundation.
Our platform needs to handle some fundamental relationships:
- Users can create posts
- Users can comment on posts
- Users can follow other users
- Users can like both posts and comments
- Users can create direct messages (DMs) with other users
This is exactly the kind of scenario that comes up in interviews. The relationships between entities are clear but non-trivial, and there are interesting questions about data consistency and scaling.
What makes this interesting from a database perspective? Well, different operations have different requirements:
- Multi-step operations like creating DM threads need to be atomic (creating the thread, adding participants, and storing the first message must happen together)
- Comment and follow relationships need referential integrity (you can't have a comment without a valid post or follow a non-existent user)
- Like counts can be eventually consistent (it's not critical if it takes a few seconds to update)
- When someone requests a user's profile, we need to efficiently fetch their recent posts, follower count, and other metadata
- Users need to be able to search through posts and find other users
- As our platform grows, we'll need to handle more data and more complex queries
This combination of requirements - complex relationships, mixed consistency needs, search capabilities, and room for growth - makes it a perfect example for exploring PostgreSQL's strengths and limitations.
Core Capabilities & Limitations
With a motivating example in place, we can map out what PostgreSQL can and can't do well. Most system design discussions about PostgreSQL will center around its read performance, write capabilities, consistency guarantees, and replication. Understanding these core characteristics will help you make informed decisions about when to use PostgreSQL in your design.
Read Performance
First up is read performance - this is critical because in most applications, reads vastly outnumber writes. In our social media example, users spend far more time browsing posts and profiles than they do creating content.
In system design interviews, you don't need to dive into query planner internals. Instead, focus on practical performance patterns and when different types of indexes make sense.
When a user views a profile, we need to efficiently fetch all posts by that user. Without proper indexing, PostgreSQL would need to scan every row in the posts table to find matching posts - a process that gets increasingly expensive as our data grows. By creating an index on the user_id column of our posts table, we can quickly locate all posts for a given user without scanning the entire table.
Basic Indexing
The most fundamental way to speed up reads in PostgreSQL is through indexes. By default, PostgreSQL uses B-tree indexes, which work great for:
- Exact matches (WHERE email = 'user@example.com')
- Range queries (WHERE created_at > '2024-01-01')
- Sorting (ORDER BY username if the ORDER BY column match the index columns' order)
By default, PostgreSQL will create a B-tree index on your primary key column, but you also have the ability to create indexes on other columns as well.
-- This is your bread and butter index
CREATE INDEX idx_users_email ON users(email);
-- Multi-column indexes for common query patterns
CREATE INDEX idx_posts_user_date ON posts(user_id, created_at);A common trap in interviews is to suggest adding indexes for every column. Remember that each index:
- Makes writes slower (as the index must be updated)
- Takes up disk space
- May not even be used if the query planner thinks a sequential scan would be faster
Beyond Basic Indexes
Query Optimization Essentials
Write Performance
Throughput Limitations
Write Performance Optimizations
Replication
Scaling reads
High Availability
Data Consistency
Transactions
When to Use PostgreSQL (and When Not To)
When to Consider Alternatives
Summary
Appendix: Basic SQL Concepts
Relational Database Principles
ACID Properties
Atomicity (All or Nothing)
Consistency (Data Integrity)
Isolation (Concurrent Transactions)
Durability (Permanent Storage)
Why ACID Matters
SQL Language
SQL Command Types
Purchase Premium to Keep Reading
Unlock this article and so much more with Hello Interview Premium
Currently up to 20% off
Hello Interview Premium
Reading Progress
On This Page