PostgreSQL is one of those technologies that looks simple when you first meet it.
You create a table.
You add an index.
You write a SELECT.
You connect your backend.
You ship.
Then your application grows.
Suddenly the database is no longer just a place where rows live. It becomes a concurrency engine, a search engine, a job queue, a consistency boundary, a JSON processor, an event source, and sometimes the most important component in your architecture.
That is when PostgreSQL starts becoming interesting.
The difference between a beginner and an experienced backend developer is often not whether they know SQL syntax. It is whether they understand what PostgreSQL can do for them before reaching for another system.
A surprising amount of backend infrastructure can be built with PostgreSQL itself.
You can implement idempotency.
You can prevent duplicate records.
You can build reliable job queues.
You can perform atomic state transitions.
You can implement optimistic and pessimistic concurrency.
You can create partial indexes that are dramatically smaller than normal indexes.
You can use PostgreSQL's JSON capabilities without abandoning relational modeling.
You can use advisory locks.
You can use LISTEN and NOTIFY.
You can inspect query plans instead of guessing about performance.
And, perhaps most importantly, you can move business invariants from application code into the database.
This article is a collection of the PostgreSQL tricks I think every serious backend developer should understand.
The goal is not to memorize SQL.
The goal is to start thinking of PostgreSQL as part of your application architecture.
1. Stop Thinking of PostgreSQL as a Dumb Data Store
One of the biggest mistakes backend developers make is treating the database as a passive storage layer.
The architecture often looks like this:
┌───────────────┐
│ Frontend │
└───────┬───────┘
│
▼
┌───────────────┐
│ REST API │
└───────┬───────┘
│
▼
┌───────────────┐
│ Application │
│ Logic │
└───────┬───────┘
│
▼
┌───────────────┐
│ PostgreSQL │
│ "Storage" │
└───────────────┘
But PostgreSQL is capable of much more.
A better mental model is:
PostgreSQL
│
┌───────────────┼────────────────┐
│ │ │
▼ ▼ ▼
Persistence Concurrency Computation
│ │ │
▼ ▼ ▼
Tables Locks/MVCC SQL Functions
Indexes Transactions Aggregation
Constraints Isolation JSONB
│ │ │
└───────────────┼────────────────┘
▼
Business Rules
The database can enforce things your application should not be trusted to enforce alone.
For example:
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
That UNIQUE constraint is more powerful than checking:
if not User.objects.filter(email=email).exists():
User.objects.create(email=email)
Why?
Because two requests can execute that application-level check simultaneously.
Request A Request B
│ │
▼ ▼
"Does email exist?" "Does email exist?"
│ │
▼ ▼
NO NO
│ │
▼ ▼
INSERT INSERT
│ │
└───────────┬────────────────┘
▼
Collision
The database constraint is the final authority.
This is a recurring theme throughout PostgreSQL:
If something must always be true, make PostgreSQL enforce it.
2. Use RETURNING Instead of Querying Twice
This looks small.
It isn't.
Suppose you create an order:
INSERT INTO orders (user_id, total)
VALUES (42, 199.99);
Then your application needs the generated ID.
A naive implementation might perform another query:
INSERT INTO orders (user_id, total)
VALUES (42, 199.99);
SELECT id
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 1;
That is unnecessary.
Use RETURNING.
INSERT INTO orders (user_id, total)
VALUES (42, 199.99)
RETURNING id, created_at;
PostgreSQL gives you the inserted values immediately.
This is particularly useful when using generated IDs:
INSERT INTO users (email)
VALUES ('derek@example.com')
RETURNING id;
You can also return calculated values:
UPDATE accounts
SET balance = balance - 100
WHERE id = 42
RETURNING id, balance;
Now the database performs the operation and returns the resulting state in one round trip.
That matters in high-latency environments.
Less network chatter means less work.
3. ON CONFLICT Is an Idempotency Superpower
Modern backend systems constantly deal with retries.
A payment request might be retried.
A webhook might be delivered twice.
A mobile client might send the same request multiple times.
A worker might crash after processing a job but before acknowledging it.
You need idempotency.
Suppose we have:
CREATE TABLE payments (
id BIGSERIAL PRIMARY KEY,
idempotency_key TEXT NOT NULL UNIQUE,
amount NUMERIC(12,2) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
Now:
INSERT INTO payments (
idempotency_key,
amount
)
VALUES (
'payment-abc-123',
500.00
)
ON CONFLICT (idempotency_key)
DO NOTHING
RETURNING id;
The same request can arrive ten times.
Only one row wins.
Client
│
┌───────┴────────┐
│ │
Request Retry
│ │
└───────┬────────┘
▼
idempotency_key
│
▼
PostgreSQL UNIQUE
│
┌────────┴────────┐
▼ ▼
First insert Duplicate
│ │
▼ ▼
Create Ignore
You can also use DO UPDATE:
INSERT INTO users (email, name)
VALUES ('derek@example.com', 'Derek')
ON CONFLICT (email)
DO UPDATE
SET name = EXCLUDED.name
RETURNING *;
This is an elegant form of an upsert.
The important idea is that uniqueness and conflict resolution belong close to the data.
4. Partial Indexes Are One of PostgreSQL's Most Underrated Features
Imagine a table containing 50 million orders.
Only 100,000 are currently pending.
Your application constantly asks:
SELECT *
FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 50;
A normal index might be:
CREATE INDEX idx_orders_status
ON orders(status);
But PostgreSQL supports partial indexes.
CREATE INDEX idx_pending_orders
ON orders(created_at)
WHERE status = 'pending';
Now the index contains only pending orders.
orders table
────────────────────────────────────
50,000,000 rows
pending
████
100,000
completed
████████████████████████████████
49,900,000
Instead of maintaining an index for everything:
Full index
████████████████████████████████
50 million entries
you can maintain:
Partial index
█
100,000 entries
This can significantly reduce index size and maintenance overhead when the predicate selects a small subset of rows. PostgreSQL's documentation specifically describes partial indexes as indexes over a subset of table rows and notes that they can reduce index size and update work.
Another excellent example:
CREATE UNIQUE INDEX unique_active_subscription
ON subscriptions(user_id)
WHERE cancelled_at IS NULL;
Now a user can have multiple historical subscriptions, but only one active subscription.
That is business logic enforced by the database.
5. Learn Expression Indexes
Sometimes the query does not search the raw column.
Consider:
SELECT *
FROM users
WHERE LOWER(email) = LOWER('DEREK@EXAMPLE.COM');
A normal index on:
email
may not be enough for the expression being queried.
Instead:
CREATE INDEX idx_users_lower_email
ON users (LOWER(email));
Now PostgreSQL can use an index specifically designed for that expression.
The same concept works for many transformations.
CREATE INDEX idx_users_normalized_phone
ON users (regexp_replace(phone, '[^0-9]', '', 'g'));
Or:
CREATE INDEX idx_documents_title
ON documents (LOWER(title));
PostgreSQL supports indexes on expressions, meaning an index can be built on a computed transformation of one or more columns.
The general lesson:
Index the expression your query actually uses.
Not the expression you wish your query used.
6. EXPLAIN ANALYZE Is Your Database X-Ray
When a query is slow, don't guess.
Run:
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;
PostgreSQL will show you how the query was executed.
You might see:
Limit
-> Sort
-> Seq Scan on orders
Filter: (user_id = 42)
That is interesting.
Your database might be scanning the entire table.
Maybe you need:
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);
Then run the query again.
The plan could become:
Limit
-> Index Scan using idx_orders_user_created
Index Cond: (user_id = 42)
The difference is architectural.
Without index
Application
│
▼
PostgreSQL
│
▼
Scan millions of rows
│
▼
Find 20 rows
With appropriate index
Application
│
▼
PostgreSQL
│
▼
Index lookup
│
▼
Find relevant rows
Never optimize PostgreSQL based purely on intuition.
Measure the plan.
7. Composite Index Order Matters
Suppose you have:
CREATE INDEX idx_orders
ON orders(user_id, status, created_at);
This index is especially useful for queries such as:
WHERE user_id = 42
or:
WHERE user_id = 42
AND status = 'pending'
or:
WHERE user_id = 42
AND status = 'pending'
ORDER BY created_at DESC;
But you should not assume it is equally useful for:
WHERE status = 'pending';
Index design is not just:
"Which columns should I index?"
It is:
"Which query patterns am I optimizing?"
Think of a composite index like a dictionary.
If it is sorted by:
country → city → street
you can efficiently find:
country = Zambia
and:
country = Zambia
city = Lusaka
But searching primarily by:
city = Lusaka
doesn't align with the left side of the index.
This is why production indexing requires understanding actual query patterns.
8. Use Keyset Pagination Instead of Massive OFFSET
This is a classic backend problem.
You have:
SELECT *
FROM posts
ORDER BY id
LIMIT 50
OFFSET 1000000;
The query looks harmless.
But PostgreSQL may still have to walk through a huge amount of data to reach that offset.
Instead, use keyset pagination.
First request:
SELECT *
FROM posts
ORDER BY id
LIMIT 50;
Suppose the last ID is:
1050
Next request:
SELECT *
FROM posts
WHERE id > 1050
ORDER BY id
LIMIT 50;
Then:
Page 1
1 ─────────────── 50
│
▼
cursor
Page 2
51 ───────────── 100
│
▼
cursor
Page 3
101 ──────────── 150
The cursor is simply the last seen ordering value.
For descending feeds:
SELECT *
FROM posts
WHERE id < 1050
ORDER BY id DESC
LIMIT 50;
This becomes particularly powerful when combined with an index matching the ordering.
Cursor pagination is one of those things that feels unnecessary until your database contains millions of rows.
Then you realize why Twitter-like feeds rarely want to scan a million skipped rows.
9. PostgreSQL Can Be Your Job Queue
This is one of my favorite PostgreSQL tricks.
Suppose you need background workers.
You could immediately introduce:
Redis
RabbitMQ
Kafka
SQS
But sometimes your application already has PostgreSQL.
You can build a surprisingly capable queue.
Create:
CREATE TABLE jobs (
id BIGSERIAL PRIMARY KEY,
payload JSONB NOT NULL,
status TEXT NOT NULL DEFAULT 'pending',
available_at TIMESTAMPTZ NOT NULL DEFAULT now(),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
Workers can claim jobs using:
SELECT id, payload
FROM jobs
WHERE status = 'pending'
AND available_at <= now()
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 10;
Then:
UPDATE jobs
SET status = 'processing'
WHERE id IN (...);
The magic is:
SKIP LOCKED
Imagine three workers:
PostgreSQL
│
┌──────────┼──────────┐
▼ ▼ ▼
Worker A Worker B Worker C
│ │ │
▼ ▼ ▼
Job 1 Job 2 Job 3
If Worker A locks Job 1, Worker B does not need to sit there waiting for Job 1.
It can skip the locked row and claim another job.
This makes PostgreSQL useful for lightweight distributed work queues.
But there is an important implementation detail: claim and state transition should be handled carefully, usually inside a transaction.
A more compact pattern is:
WITH next_jobs AS (
SELECT id
FROM jobs
WHERE status = 'pending'
AND available_at <= now()
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 10
)
UPDATE jobs
SET status = 'processing'
WHERE id IN (SELECT id FROM next_jobs)
RETURNING *;
Now the worker can atomically claim jobs and receive the rows.
10. Atomic State Transitions Beat Read-Then-Write
Consider an order:
pending → paid → shipped → delivered
A naive API might do:
1. SELECT order
2. Check status
3. Change status
4. UPDATE order
This creates a race.
Two requests can read:
status = pending
and both decide they are allowed to transition it.
Instead:
UPDATE orders
SET status = 'paid'
WHERE id = 100
AND status = 'pending'
RETURNING *;
If zero rows are returned:
Transition rejected.
If one row is returned:
Transition succeeded.
This is beautiful because the condition and state transition happen atomically.
Current state
│
▼
┌─────────┐
│ pending │
└────┬────┘
│
UPDATE ...
WHERE status
= pending
│
┌────────┴────────┐
▼ ▼
success failure
│ │
▼ ▼
paid someone else
changed it
This technique is useful for inventory, payments, workflows, moderation systems, publishing systems, and distributed workers.
11. Use Transactions for Business Operations, Not Just Multiple Queries
A transaction is not simply:
"Put BEGIN and COMMIT around some SQL."
It defines a consistency boundary.
Consider transferring money:
BEGIN;
UPDATE accounts
SET balance = balance - 500
WHERE id = 1;
UPDATE accounts
SET balance = balance + 500
WHERE id = 2;
COMMIT;
You don't want:
Account A loses money
│
▼
Application crashes
│
▼
Account B never receives money
The transaction turns those operations into one logical unit.
But transactions become even more interesting when combined with constraints.
For example:
UPDATE accounts
SET balance = balance - 500
WHERE id = 1
AND balance >= 500
RETURNING balance;
If no row is returned, the withdrawal failed.
You didn't need:
if account.balance >= amount:
account.balance -= amount
followed by another update.
The database performed the check and modification atomically.
12. Understand PostgreSQL Isolation Levels
PostgreSQL defaults to Read Committed.
Under this isolation level, a query sees data committed before that query began, so two queries in the same transaction can observe different committed states if other transactions commit between them.
That distinction matters.
Imagine:
Transaction A Transaction B
SELECT balance
│
│
├─────────────────────► UPDATE balance
│
│ COMMIT
│
SELECT balance
│
▼
Different snapshot
For more demanding workflows, PostgreSQL supports:
Read Committed
Repeatable Read
Serializable
Serializable is particularly interesting because PostgreSQL attempts to guarantee behavior equivalent to serial execution, but applications must be prepared to retry transactions when serialization failures occur.
That means this:
try:
transaction()
except SerializationFailure:
retry()
is not necessarily a sign that your system is broken.
It can be part of the correct architecture.
Concurrency is not something you can wish away.
You design for it.
13. SELECT FOR UPDATE Is a Powerful Tool — But Don't Abuse It
Suppose two workers attempt to modify the same account.
You can lock the row:
SELECT *
FROM accounts
WHERE id = 42
FOR UPDATE;
The selected row is locked for the transaction.
This is useful when you need to read a value, make a decision, and then modify that same row.
For example:
BEGIN;
SELECT balance
FROM accounts
WHERE id = 42
FOR UPDATE;
-- application logic
UPDATE accounts
SET balance = balance - 100
WHERE id = 42;
COMMIT;
But locks are not free.
Poor locking strategies can create contention and deadlocks.
PostgreSQL explicitly documents row-level, table-level, advisory locking and deadlock behavior as part of its concurrency model.
A good rule is:
Lock as little as necessary, for as little time as necessary.
14. Advisory Locks Are Extremely Useful
PostgreSQL also supports advisory locks.
These are application-defined locks.
For example:
SELECT pg_advisory_xact_lock(12345);
You can use a number to represent a logical resource.
Imagine you have:
customer_id = 42
and you want to ensure only one process performs a particular operation for that customer at a time.
You can derive a lock key:
customer:42
│
▼
lock identifier
│
▼
pg_advisory_xact_lock(...)
Unlike ordinary row locks, advisory locks are not tied directly to a particular table row. PostgreSQL documents them specifically as application-defined locking mechanisms, available at session or transaction level.
A transaction-level lock is often convenient:
BEGIN;
SELECT pg_advisory_xact_lock(42);
-- critical section
COMMIT;
When the transaction ends, the lock is released.
This is useful for:
- scheduled jobs
- singleton tasks
- per-user operations
- resource generation
- preventing duplicate workflows
- distributed coordination
But advisory locks are conventions.
PostgreSQL doesn't magically know that your application considers 42 to mean "customer 42."
Your application must consistently use the same lock key.
15. JSONB Does Not Mean "Throw Away Your Schema"
PostgreSQL's JSONB is powerful.
But developers sometimes misunderstand it.
They see:
metadata JSONB
and decide:
"I don't need database design anymore."
You still do.
JSONB is excellent for flexible data:
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
metadata JSONB
);
You might store:
{
"color": "black",
"screen_size": 15,
"ram": 32,
"tags": ["developer", "premium"]
}
Then query:
SELECT *
FROM products
WHERE metadata @> '{"color": "black"}';
You can add a GIN index:
CREATE INDEX idx_products_metadata
ON products
USING GIN (metadata);
The key is balance.
Use relational columns for fields that are:
- heavily queried
- strongly constrained
- part of relationships
- essential to business rules
Use JSONB for:
- flexible metadata
- optional attributes
- external payloads
- configuration
- evolving schemas
The best architecture is often hybrid.
products
│
├── id
├── name
├── price
├── category_id
│
└── metadata JSONB
├── color
├── dimensions
├── vendor_data
└── optional attributes
16. Generated Columns Can Remove Repeated Application Logic
Suppose you have:
first_name
last_name
and constantly need:
full_name
You can use a generated column.
CREATE TABLE users (
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
full_name TEXT
GENERATED ALWAYS AS
(first_name || ' ' || last_name)
STORED
);
Now PostgreSQL maintains it.
The application doesn't need to remember:
user.full_name = f"{user.first_name} {user.last_name}"
This becomes especially interesting when derived values are used frequently.
The general principle:
If a value is deterministically derived from database state, consider letting the database own the derivation.
17. Constraints Are Executable Business Rules
Consider:
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
price NUMERIC(12,2) NOT NULL,
stock INTEGER NOT NULL,
CHECK (price >= 0),
CHECK (stock >= 0)
);
Now:
INSERT INTO products(price, stock)
VALUES (-50, -10);
fails.
Your application might have ten different code paths that create products.
You don't have to trust every one of them.
The database becomes the final safety net.
You can also use foreign keys:
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL
REFERENCES users(id)
);
Now you cannot accidentally create an order pointing to a nonexistent user.
This is one of the most important lessons in backend engineering:
Application validation improves user experience. Database constraints protect data integrity.
You want both.
18. Use NULL Carefully
NULL is not zero.
It is not an empty string.
It is not false.
It represents missing or unknown information.
Therefore:
WHERE deleted_at = NULL
is wrong.
Use:
WHERE deleted_at IS NULL
Likewise:
WHERE deleted_at IS NOT NULL
This becomes particularly important for soft deletes.
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email TEXT NOT NULL,
deleted_at TIMESTAMPTZ
);
Then:
CREATE UNIQUE INDEX unique_active_email
ON users(email)
WHERE deleted_at IS NULL;
Now deleted users don't prevent a new active account from using the same email.
This is another excellent combination of:
NULL semantics
+
partial index
+
business rule
19. Don't Store Everything as TEXT
PostgreSQL has rich types.
Use them.
Dates:
TIMESTAMPTZ
Money-like precise values:
NUMERIC
Boolean:
BOOLEAN
Structured flexible data:
JSONB
Arrays where appropriate:
TEXT[]
UUIDs:
UUID
Network addresses:
INET
Ranges:
INT4RANGE
The database can reason about types.
If you store everything as text, you're throwing away that intelligence.
For example, storing timestamps as text makes ordering, validation, indexing, and timezone handling harder.
Good database design starts by asking:
What is this value actually?
Then select the corresponding database type.
20. Use DISTINCT ON for "Latest Row Per Group"
This is a PostgreSQL trick I use constantly.
Suppose each user has many orders and you want the latest order for every user.
One approach uses window functions:
SELECT *
FROM (
SELECT
orders.*,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY created_at DESC
) AS rn
FROM orders
) x
WHERE rn = 1;
Very good.
But PostgreSQL also has:
SELECT DISTINCT ON (user_id)
*
FROM orders
ORDER BY user_id, created_at DESC;
This gives the first row for each user_id according to the ordering.
It is concise and extremely useful for PostgreSQL-specific backend work.
With a matching index:
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);
you can make this pattern even more efficient.
21. Window Functions Are Backend Superpowers
Window functions allow you to calculate values across related rows without collapsing them into one row.
Example:
SELECT
user_id,
amount,
SUM(amount) OVER (
PARTITION BY user_id
) AS user_total
FROM payments;
You can calculate rankings:
SELECT
product_id,
sales,
RANK() OVER (
ORDER BY sales DESC
) AS ranking
FROM product_sales;
Or running totals:
SELECT
created_at,
amount,
SUM(amount) OVER (
ORDER BY created_at
) AS running_total
FROM transactions;
This can eliminate entire loops in application code.
Instead of:
for transaction in transactions:
calculate_something(...)
sometimes you can let PostgreSQL perform the computation close to the data.
22. Use WITH for Complex Queries — But Understand What You're Building
Common Table Expressions make complex SQL much easier to reason about.
For example:
WITH recent_orders AS (
SELECT *
FROM orders
WHERE created_at >= now() - interval '30 days'
),
customer_totals AS (
SELECT
user_id,
SUM(total) AS total_spent
FROM recent_orders
GROUP BY user_id
)
SELECT *
FROM customer_totals
WHERE total_spent > 1000;
This creates a pipeline:
orders
│
▼
recent_orders
│
▼
customer_totals
│
▼
high-value customers
CTEs aren't just syntax sugar.
They allow you to express complex data transformations declaratively.
PostgreSQL also supports data-modifying CTEs, which can be useful for sophisticated operations involving multiple modifications.
23. INSERT ... SELECT Can Move Work Into the Database
Suppose you want to archive old orders.
You might write application code:
for order in old_orders:
archive(order)
That means potentially thousands of network round trips.
Instead:
INSERT INTO archived_orders (
id,
user_id,
total,
created_at
)
SELECT
id,
user_id,
total,
created_at
FROM orders
WHERE created_at < now() - interval '2 years';
Then:
DELETE FROM orders
WHERE created_at < now() - interval '2 years';
For large operations, you'd carefully consider batching, locking, transaction size, and operational impact.
But the architectural lesson remains:
Databases are optimized for set operations. Stop thinking row-by-row when SQL can operate on the set.
24. LISTEN and NOTIFY Give PostgreSQL a Lightweight Event Mechanism
PostgreSQL can notify connected clients.
Listener:
LISTEN user_events;
Another session:
NOTIFY user_events, 'user:42';
The listening application receives the notification.
PostgreSQL documents LISTEN as registering a session for notification events sent through NOTIFY.
This can be useful for lightweight scenarios such as:
PostgreSQL
│
│ NOTIFY
▼
Backend process
│
├── invalidate cache
├── refresh state
└── trigger lightweight work
But don't confuse this with Kafka.
NOTIFY is not a replacement for a durable event log.
It is better suited to lightweight signaling.
If missing an event would be catastrophic, store the event in a durable table and use notifications as a wake-up mechanism.
That architecture is much safer:
Transaction
│
├── INSERT event
│
└── NOTIFY
│
▼
Worker wakes
│
▼
Reads durable event
The database remains the source of truth.
25. Use UPSERT for Synchronization
Suppose an external API sends product data.
You can synchronize it with:
INSERT INTO products (
external_id,
name,
price
)
VALUES (
'stripe_123',
'Laptop',
1200
)
ON CONFLICT (external_id)
DO UPDATE SET
name = EXCLUDED.name,
price = EXCLUDED.price;
This is much cleaner than:
SELECT
│
├── exists → UPDATE
│
└── doesn't exist → INSERT
That two-step pattern is vulnerable to races.
ON CONFLICT lets PostgreSQL coordinate the operation.
26. Learn FILTER for Cleaner Aggregation
Instead of:
SUM(
CASE
WHEN status = 'paid'
THEN amount
ELSE 0
END
)
PostgreSQL allows:
SUM(amount) FILTER (
WHERE status = 'paid'
)
You can calculate several metrics in one query:
SELECT
COUNT(*) AS total_orders,
COUNT(*) FILTER (
WHERE status = 'paid'
) AS paid_orders,
COUNT(*) FILTER (
WHERE status = 'cancelled'
) AS cancelled_orders,
SUM(total) FILTER (
WHERE status = 'paid'
) AS paid_revenue
FROM orders;
One query.
Multiple metrics.
This is particularly useful for dashboards and analytics endpoints.
27. Build a Better Backend Architecture Around PostgreSQL
After learning these tricks, the architecture starts looking different.
Instead of:
Frontend
│
▼
API
│
▼
Business Logic
│
▼
Dumb Database
you start thinking:
API
│
▼
Application Logic
│
┌──────────┴──────────┐
│ │
▼ ▼
Business workflows PostgreSQL
│
┌─────────────────────┼──────────────────┐
│ │ │
▼ ▼ ▼
Constraints Indexes Transactions
│ │ │
▼ ▼ ▼
Invariants Performance Concurrency
│
┌───────────┼───────────┐
│ │ │
▼ ▼ ▼
JSONB Job Queue Events
This is where PostgreSQL becomes more than storage.
It becomes an active participant in your architecture.
28. A Practical Example: Building an Order System
Let's combine several techniques.
First:
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL
REFERENCES users(id),
status TEXT NOT NULL
CHECK (
status IN (
'pending',
'paid',
'shipped',
'delivered',
'cancelled'
)
),
total NUMERIC(12,2) NOT NULL
CHECK (total >= 0),
idempotency_key TEXT NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
paid_at TIMESTAMPTZ
);
Now create useful indexes:
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);
And:
CREATE INDEX idx_pending_orders
ON orders(created_at)
WHERE status = 'pending';
Creating an order:
INSERT INTO orders (
user_id,
status,
total,
idempotency_key
)
VALUES (
42,
'pending',
499.99,
'checkout-abc'
)
ON CONFLICT (idempotency_key)
DO NOTHING
RETURNING *;
Transition to paid:
UPDATE orders
SET
status = 'paid',
paid_at = now()
WHERE id = 100
AND status = 'pending'
RETURNING *;
Fetch a user's latest orders:
SELECT *
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;
Find pending work:
SELECT *
FROM orders
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 10;
We have now combined:
- foreign keys
- check constraints
- unique constraints
- idempotency
- partial indexes
- composite indexes
- atomic state transitions
- transactions
- row locking
- job-queue patterns
And we did not need five different infrastructure products to accomplish it.
29. The Backend Developer's PostgreSQL Debugging Loop
When something becomes slow or inconsistent, use a disciplined process.
Step 1: Reproduce the query
Don't optimize an imaginary query.
Get the actual SQL.
Step 2: Run EXPLAIN
EXPLAIN
SELECT ...;
Step 3: Run EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT ...;
Step 4: Look for warning signs
Sequential Scan
Huge row estimates
Large actual row counts
Unexpected joins
Expensive sorts
Repeated loops
Step 5: Check indexes
Ask:
Does an index exist?
Does its column order match the query?
Is the predicate selective?
Is a partial index appropriate?
Step 6: Check concurrency
Ask:
Are transactions too long?
Are rows being locked unnecessarily?
Are workers fighting over the same records?
Step 7: Measure again
Never stop at:
"I added an index."
The question is:
"Did the query actually become better?"
30. The Most Dangerous PostgreSQL Anti-Pattern: Application-Level Consistency
Imagine you want to enforce:
Only one active subscription per user.
You could write:
existing = Subscription.objects.filter(
user_id=user_id,
cancelled_at=None
).first()
if not existing:
Subscription.objects.create(...)
It looks correct.
But under concurrency:
Request A Request B
│ │
▼ ▼
check subscription check subscription
│ │
▼ ▼
none none
│ │
▼ ▼
insert insert
│ │
└──────────┬──────────────┘
▼
Two active subscriptions
The correct solution is:
CREATE UNIQUE INDEX one_active_subscription
ON subscriptions(user_id)
WHERE cancelled_at IS NULL;
Now:
Request A ──┐
├── PostgreSQL constraint ──► one succeeds
Request B ──┘ one fails
This is the deeper lesson.
The database is where concurrent requests converge.
Therefore, the database is often the only reliable place to enforce invariants that must survive concurrency.
31. PostgreSQL Is Teaching You Distributed Systems
This is the part many developers miss.
When you learn:
transactions
locks
MVCC
isolation
idempotency
unique constraints
queues
atomic updates
serialization failures
you are not merely learning database features.
You are learning distributed systems.
Because backend applications are distributed systems whether you call them that or not.
There may be:
10 API instances
5 background workers
3 scheduler processes
2 replicas
100 concurrent requests
all interacting with the same state.
PostgreSQL is one of the places where those competing actors meet.
That means concepts such as:
race conditions
consistency
serialization
coordination
failure recovery
idempotency
are not academic concepts.
They are everyday backend engineering.
32. The PostgreSQL Mental Model I Wish More Developers Had
I would summarize the entire article with this architecture:
┌───────────────────────┐
│ Application │
└───────────┬───────────┘
│
▼
┌───────────────────────┐
│ PostgreSQL │
└───────────┬───────────┘
│
┌────────────────────────┼────────────────────────┐
│ │ │
▼ ▼ ▼
Data Model Concurrency Performance
│ │ │
├── Tables ├── MVCC ├── Indexes
├── Types ├── Transactions ├── EXPLAIN
├── Constraints ├── Locks ├── Partial indexes
└── Relations └── Isolation └── Keyset paging
│
▼
Business Rules
│
├── Uniqueness
├── Valid states
├── Valid references
├── Valid ranges
└── Atomic transitions
The strongest backend systems don't simply put data into PostgreSQL.
They design the database to participate in correctness.
Final Thoughts
PostgreSQL has a strange property.
The more you learn about it, the less you need to build around it.
At first you use PostgreSQL as a database.
Then you discover indexes.
Then transactions.
Then constraints.
Then ON CONFLICT.
Then SKIP LOCKED.
Then advisory locks.
Then JSONB.
Then window functions.
Then LISTEN and NOTIFY.
Then you realize that the database has been quietly providing primitives for problems you were about to solve with additional infrastructure.
That doesn't mean PostgreSQL should replace every technology.
You don't need to turn PostgreSQL into Kafka.
You don't need to build Redis inside SQL.
You don't need to put your entire application inside stored procedures.
The point is balance.
Use the database for problems that are naturally about data, consistency, concurrency, and set-based computation.
Use the application for orchestration, domain workflows, external integrations, and behavior that belongs outside the persistence layer.
The most important shift is mental:
Don't ask only, "How do I store this?"
Ask:
"What guarantees should the database provide?"
Don't ask only:
"How do I make this query faster?"
Ask:
"What access pattern am I actually optimizing?"
Don't ask only:
"How do I prevent duplicate requests?"
Ask:
"Can PostgreSQL make this operation idempotent?"
Don't ask only:
"How do I stop two workers from processing the same record?"
Ask:
"Can transactions and row locking solve this?"
And don't ask:
"What new infrastructure should I add?"
until you've asked:
"Can PostgreSQL already solve enough of this problem?"
That is when PostgreSQL stops being just another item in your backend stack.
It becomes part of your system's reasoning engine.
And once you start designing with that mindset, you stop writing applications that merely store data.
You start building systems that can enforce their own invariants, coordinate concurrent work, recover from retries, optimize their own access patterns, and remain correct even when the application around them becomes distributed.
That is where PostgreSQL gets really interesting.
Top comments (0)