800 Users Exposed Performance Problems I Didn’t Know My App Had - Here’s Everything I Found
Building an application is one thing.
Building an application that still feels fast when real users start using it is a completely different problem.
When I started building Archilo, I was mostly focused on features, UI, authentication, uploads, search, feeds, profiles, comments, likes, saves, and everything else a growing product needs.
At first, everything felt fine.
Then Archilo started getting real usage.
Around 800 users, I began noticing something that every developer eventually encounters:
The application worked. It just didn't feel fast anymore.
Pages were taking longer to load. Some interactions felt delayed. The feed was doing far more work than it should. And the interesting part was that there wasn't one obvious bug causing everything.
So I stopped guessing and started investigating.
What I found was a collection of small problems that became a big problem when combined.
This is how I found them and what I changed.
What is Archilo?
For context, Archilo is an architecture platform built for discovering, sharing, and exploring architectural work, research, and creative projects.
Users can publish content, explore designs, interact with posts, like and save content, comment, search, manage profiles, and more.
The application is built around a modern web stack including:
-
Next.js
-
React
-
Prisma
-
PostgreSQL
-
Neon
-
Firebase authentication
-
S3-based asset storage
So when performance started becoming an issue, I had quite a few places to investigate.
Frontend?
API?
Database?
Images?
Authentication?
Network?
Turns out, the answer was:
A little bit of everything.
But the database and query patterns were some of the biggest offenders.
The First Mistake: Looking for One Big Problem
My first instinct was the same instinct many developers have:
"There must be one really slow query."
So I started looking at individual API routes.
But that wasn't the real problem.
The problem was how many things the application was asking the database to do.
One feed request could trigger additional requests for things like:
-
Whether the user liked a post
-
Whether the user saved a post
-
Assets and images
-
Mentioned users
-
Related content
-
Comments
-
Counts
Individually, each query looked reasonable.
Together?
Not so reasonable.
The N+1 Problem Was Hiding in Plain Sight
Imagine the feed contains 20 posts.
A naive implementation might do this:
Get 20 posts
↓
Check like status for post 1
Check like status for post 2
Check like status for post 3
...
Check like status for post 20
And then potentially repeat the same pattern for saves and assets.
Suddenly, a page that looks like one request from the user's perspective can turn into dozens of database operations.
That's the infamous N+1 query problem.
And it gets worse as the number of cards increases.
The fix
Instead of asking:
"Did I like this post?"
20 separate times, I changed the approach to:
"Give me all liked posts for this user from these 20 post IDs."
Conceptually:
SELECT post_id
FROM post_likes
WHERE user_id = ?
AND post_id IN (...);
One query.
The same idea was applied to saves and other repeated lookups.
The feed API now folds viewerLiked and viewerSaved into the response itself instead of making every card independently figure it out.
That was one of the biggest wins.
Then I Found Another Problem: We Were Fetching Too Much Data
This one is surprisingly common.
The UI might need:
title
thumbnail
author
likes
date
But the database could be returning:
title
thumbnail
author
likes
date
description
attachments
collaborators
metadata
external links
technologies
project information
...
Why?
Because it was convenient.
But convenience has a cost.
The database has to retrieve it.
Prisma has to process it.
The server has to serialize it.
The network has to transfer it.
And the browser has to receive it.
So I started adding explicit Prisma select statements to list queries.
Instead of:
findMany({
include: ...
})
the query became much more intentional:
findMany({
select: {
id: true,
title: true,
...
}
})
For example, the Designs list stopped fetching the large TipTap description field and many other columns that the card never needed.
This led to a simple rule I now follow:
List pages should be thin. Detail pages can be fat.
Your card doesn't need the entire article.
Then Came the Database Indexes
At this point I had reduced the number of queries.
But there was another question:
Are the remaining queries easy for PostgreSQL to find?
This is where indexes came in.
Think of a database index like the index at the back of a textbook.
Without it, you might have to search through hundreds of pages.
With it, you jump much closer to the answer.
For Archilo, I added indexes around the queries we actually use.
For example, content listing queries commonly filter around things like:
status
isPublic
isActive
isArchived
publishedAt
So I added a composite index around those fields.
I also added:
(userId, createdAt)
indexes for activity-related tables such as likes, saves, and shares.
These indexes were applied to the live Neon database.
The important lesson here is:
Don't add indexes because a blog post told you indexes are good. Add indexes because your queries need them.
Search Had Its Own Problem
Archilo also has search.
And search often uses queries conceptually similar to:
WHERE title ILIKE '%architecture%'
Traditional B-tree indexes aren't designed to handle arbitrary substring searches particularly well.
So I added PostgreSQL pg_trgm GIN indexes for the fields being searched heavily:
posts.title
posts.category
designs.title
designs.category
research_papers.title
research_papers.category
users.name
users.username
This is one of those database features you don't necessarily need when you're starting out.
But as your dataset grows, the difference becomes increasingly important.
Tags and Keywords Were Another Story
Archilo also stores things like tags and research keywords in arrays.
Queries such as:
tags.has
tags.hasSome
keywords.has
keywords.hasSome
can become expensive when the dataset gets larger.
So I added PostgreSQL GIN indexes to the relevant array columns:
posts.tags
designs.tags
research_papers.keywords
Again, the important part wasn't simply "add an index."
It was:
Look at what the application actually asks the database. Then design the database around those questions.
We Were Also Counting Things We Didn't Need to Count
Another interesting optimization happened in feed pagination.
For an infinite feed, I don't necessarily need to know:
"There are exactly 13,482 matching posts."
I only need to know:
"Are there more posts?"
So instead of running an expensive COUNT(*) for every infinite-feed request, the API fetches one extra record.
If I want 20:
Fetch 21
If I receive 21:
20 displayed
1 hidden
hasMore = true
If I receive only 20:
hasMore = false
Simple.
And much more appropriate for an infinite scroll experience.
50 Posts Became 20
This was another small change with a surprisingly large impact.
The feed originally loaded up to 50 posts on the first request.
I reduced that to 20.
Why make the browser process 50 cards when the user can only see a handful of them initially?
Every additional item can mean:
-
Database work
-
JSON serialization
-
Network transfer
-
React rendering
-
Image loading
-
Asset processing
So instead of:
50 posts
the initial request now starts with:
20 posts
and loads more as the user continues scrolling.
Fewer Queries Isn't Enough
There was another pattern I found.
Some queries were independent but were being executed one after another.
Something like:
Query A
↓
wait
↓
Query B
↓
wait
↓
Query C
If A takes 50ms, B takes 40ms, and C takes 30ms, the total waiting time can approach 120ms.
If they don't depend on each other, why wait?
So I changed appropriate server-side operations to run concurrently:
await Promise.all([
queryA(),
queryB(),
queryC()
]);
Now the request can move closer to the duration of the slowest operation instead of the sum of all three.
This was applied to several API routes, including the posts API and post detail API.
And Then There Was the Connection Pool
This was another piece of the puzzle.
The application initially had a database connection pool of only 5 connections.
Once the query storms were fixed, I increased the pool to 12 while using Neon's pooled/PgBouncer endpoint.
But notice the order:
I didn't solve N+1 by throwing more database connections at it.
That would be like fixing a traffic jam by building more parking spaces while leaving the road blocked.
First:
Reduce unnecessary queries
↓
Batch queries
↓
Select only required data
↓
Add appropriate indexes
↓
Parallelize independent work
↓
Then increase pool capacity
That's a much healthier approach.
The Bigger Lesson
The most important thing I learned wasn't about PostgreSQL.
It was about thinking in systems.
A page can feel slow even when no individual query looks terrible.
For example:
1 reasonable query
+
20 unnecessary queries
+
20 more asset lookups
+
large database payload
+
large images
+
serial server operations
+
connection pool waiting
None of those problems necessarily screams:
"I AM THE PERFORMANCE BUG."
Together, they absolutely do.
What Changed in Archilo?
The optimization ended up covering several layers:
Database
-
Composite indexes
-
Trigram GIN indexes
-
Array GIN indexes
-
Better query patterns
API
-
N+1 query elimination
-
Batched database lookups
-
Explicit
select -
Parallel queries
-
Pagination improvements
-
Bounded request parameters
Frontend
-
Fewer redundant requests
-
Viewer state included in list responses
-
Smaller initial feed
-
Better React Query invalidation
Infrastructure
-
Increased connection pool
-
Neon connection pooling
-
Background processing for non-critical work
And importantly, these weren't theoretical optimizations.
They came from looking at what Archilo was actually doing in production.
The Takeaway for Developers
If your application has 10 users, you can get away with a lot.
At 100 users, you start noticing some cracks.
At 800 users, those cracks can start introducing themselves properly.
And eventually, your application tells you exactly where you cut corners.
So if your application is becoming slow, don't immediately add Redis, a bigger server, or a new database.
First ask:
How many queries are happening?
Which queries are repeated?
Am I fetching data I don't use?
Does my database have indexes matching my real queries?
Can independent operations run in parallel?
Do I really need that COUNT(*)?
Am I loading 50 things when I only need 20?
Because sometimes the fastest database query isn't a faster query.
It's the query your application no longer needs to make.
