The Challenge: When Search Performance Undermines Confidence
Every product team hears it eventually.
“Why does something this simple take so long?”
In our case, the complaint was about finding locations. What appeared to be a minor usability issue was actually a signal of a much larger problem. Search was slow, results were inconsistent, and as customers scaled their deployments, the experience degraded further. For enterprise users managing hundreds or thousands of locations, this was not an inconvenience. It was a blocker.
Solving it required more than tuning queries. It required rethinking how search works, how data is modeled, and how the product communicates intent. The result was an 80 percent reduction in load times and a search experience that scales cleanly as customers grow.
Originally, our locations table relied on a single search field designed to handle everything. Location names, street addresses, serial numbers, MAC addresses, SIM identifiers, and model names were all treated as interchangeable text.
From a user perspective, this created uncertainty. Typing “11” might return Building 11, or it might return dozens of unrelated locations simply because a device somewhere contained “11” in its identifier. The system was technically working, but it was not working in a way users could trust.
From a platform perspective, the cost was even higher. Each location could include multiple routers, gateways, SIM cards, power sources, and associated configuration data. Supporting a single search meant joining across many tables every time a user typed a query. As deployments grew, so did query complexity, database load, and response times.
Solution: Making Search Intent Explicit
The first improvement came from a simple product insight; users were not searching incorrectly. The interface was asking them to be ambiguous. We redesigned search to reflect how customers actually think about their data by introducing two explicit search modes:
- Location Search, focused only on location attributes such as name, street address, city, state, and postal code
- Hardware Search, focused only on equipment identifiers such as MAC addresses, serial numbers, static IPs, IMEIs, SIM ICCIDs, and model names
Instead of guessing intent, the product now asks for it. A reusable dropdown makes the distinction clear and consistent across the platform. The immediate benefit was more accurate results and a noticeable reduction in user frustration. For decision makers, this change matters because it restores predictability. Teams can find what they need quickly without second-guessing the system or running redundant searches.
Scalable Component Architecture
As part of this effort, we introduced a shared table component that now powers all full-page tables across the platform, including locations, organizations, users, and contacts. The component standardizes search, filtering, sorting, and pagination while allowing each surface to define its own data sources and search modes.
Although the locations table triggered the initiative, the outcome was broader. Performance improvements made in one place now benefit the entire application.
Aggregate View Tables
For each location, rather than storing equipment across many rows and relying on complex joins, we store all related data in a single row using pipe-delimited (|) lists.
Instead of returning multiple rows:
| location_id | router_mac | sim_iccid | gateway_mac |
|---|---|---|---|
| 1 | AA:BB:CC:DD:01 | 89012345… | NULL |
| 1 | AA:BB:CC:DD:02 | 89012346… | NULL |
| 1 | NULL | NULL | FF:EE:DD:CC:01 |
We return a single row with aggregated values:
| location_id | routersMacList | simsIccIdList | gatewaysMacList |
|---|---|---|---|
| 1 | AA:BB…|AA:BB… | 8901234…|890123… | FF:EE:DD:CC:01 |
The view uses PostgreSQL’s STRING_AGG to combine values per location. Subqueries handle counts and filtered data to keep grouping simple, and commonly used fields like address data are flattened into the view.
This turns complex multi-table joins into straightforward queries against one view. Location searches become simple filters, and hardware searches are substring matches against the aggregated lists. The stored values match the source tables, so no parsing is required unless the application explicitly needs arrays.
Indexes were added to foreign key columns used by the view, including organization, account, address, location, and equipment relationships. This ensures the view can efficiently combine data at query time.
The Impact: Measurable Results That Scale With Growth
The impact of these changes was immediate and quantifiable.
Performance Improvements
- 80 percent reduction in query time, from over 10 seconds to 100 to 500 milliseconds
- Significant reduction in database load by eliminating repeated joins
- Consistent performance regardless of equipment count per location
User and Business Impact
- Search results now align with user intent
- Faster access to operational data improves day-to-day decision making
- Teams spend less time waiting and more time acting
Operational Benefits
- Real-time updates to data are able to be queried instantly
- Linear scalability supports customer growth without rework
Because this architecture is shared across the platform, future performance improvements and features can be delivered broadly, not table by table.
Looking Ahead: Why This Matters for IT and Business Leaders
Search performance is not just a technical concern. It directly affects how quickly teams can respond to issues, validate information, and scale operations. By clarifying intent at the product level and redesigning data access for scale, we turned a growing liability into a durable advantage. The system is faster today, and more importantly, it is designed to stay fast as customers grow.
For organizations evaluating platforms to support large and expanding deployments, this approach ensures that operational visibility does not degrade as complexity increases. Performance remains predictable, and the product remains trustworthy.
That is the difference between a system that works today and one that is built to grow with you.