Five PostgreSQL Anti-Patterns
As Rails developers, we’re taught that code is the expensive part and that the database will mostly take care of itself, as long as you throw enough RAM, CPU, and indexes at it. But that approach doesn’t lead to high performance.
High-performance, low-latency apps are table stakes now. Users expect things to be fast, and budgets no longer support just throwing money at the problem. In modern high-throughput, resource-constrained environments, every byte and every millisecond matters. And the database is often the biggest leverage point.
Rails and SaaS apps often stumble in the same predictable ways: UUIDv4 keys, wide tables, and bloated indexes keep showing up, adding latency and cost as data grows.
In this post, I’ll cover five common Postgres anti-patterns I see in production Rails setups, and what to do instead.
1. Using UUIDv4 as Primary Keys
Why it’s a problem
Because UUIDv4 is completely random, every insert lands in a different place in the index. That randomness forces Postgres to spread keys across way more pages than necessary. More pages means more I/O and more shuffling to find records that match a query. Even if you throw all the RAM in the world at it, UUIDv4 still forces the database to work harder, and the performance hit is real, even if it’s not always obvious or easy to trace.
What to do instead
- Use
BIGINTs. They support up to 9 quintillion values, more than enough for anything a Rails app will ever need. They’re also compact (just 8 bytes) and naturally sequential, so inserts tend to land on the same page. That means fewer page splits, faster inserts, and better performance for lookups and range scans. - If you really need globally unique IDs, use
UUIDv7. They’re time-ordered, so new records tend to land on the same page or the next sequential page. They use 16 bytes (twice the size of aBIGINT), so you’re paying more in storage, but you still avoid the page explosion problem of UUIDv4.
2. Ultra-Wide Tables
Why it’s a problem
Tables with lots of columns hurt performance in a few ways.
First, there’s the obvious cost of pulling all that data. It still has to come off disk, travel over the network, and get instantiated into large in-memory objects. Second, even when you’re not querying everything, Postgres still has to manage all those extra columns. TOAST (Postgres’s system for handling large field values) adds more I/O, more CPU work, and more complexity. And finally, the queries themselves get huge and unreadable. Most slow query logs won’t even capture the full text, which means the part of the SQL that could actually help with indexing or optimization is lost.
What to do instead
- The best fix is to split the table. Keep the core attributes in one table and move the sparse or optional fields into another, connected with a
has_onerelationship. That way most queries only touch the smaller table, and you only hit the larger one when the app actually needs those extra fields. - If you’re stuck with a wide table, be deliberate about which data is selected. Use methods like
pluckor pass an explicit column list toselectto avoid pulling back more data than needed. This won’t fix the schema, but it will reduce payload size and help maintain faster response times.
3. Letting Indexes Get Big and Slow
Why it’s a problem
In PostgreSQL, every update or delete leaves behind dead index entries that don’t get cleaned up right away.
On high-churn tables like delayed_jobs, solid_queue_jobs, and good_jobs, indexes grow fast and lose efficiency quickly. A query that took 2 ms with a fresh index can be crawling at 20 ms only a few hours later. As those dead entries accumulate, the index gets spread across more pages than necessary. That means more pages to scan, more random I/O, higher memory usage, and more CPU time spent navigating bloated index trees.
What to do instead
- Run
REINDEX CONCURRENTLYregularly on high-churn tables to rebuild indexes. Reindexing clears out dead entries and helps restore index efficiency and performance. - For very large or heavily read indexes, consider using
pg_repack. LikeREINDEX CONCURRENTLY, it rebuilds indexes without blocking reads and writes, but it does so in parallel, which results in a smaller online performance hit. The trade-off is that it requires temporary disk space roughly equal to the size of the index. - Track index health regularly. The
pgstattupleextension provides detailed metrics on density, making it easier to identify when an index has become inefficient and needs reindexing.
4. Storing Statuses as Strings
Why it’s a problem
Storing statuses as plain text like 'pending', 'shipped', or 'canceled' feels easy and flexible. You can add new states whenever without too much work. I made the same mistake, encoding 'up' and 'down' states as strings in httpscout.
The problem with storing statuses as text is that the data isn’t dense. Strings use more bytes than necessary to represent a small, fixed set of states. That extra size makes rows bigger, indexes less efficient, and forces Postgres to push more data through disk and memory than it needs to. Multiply that overhead across millions of rows, and it adds up to a real performance cost.
What to do instead
- Use a small integer column to encode statuses. It’s a denser representation that requires less storage and keeps rows and indexes smaller. In Rails, you can use an enum to keep meaningful names:
class Order < ApplicationRecord
enum status: { pending: 0, shipped: 1, canceled: 2 }
end
This gives you human-readable statuses in the app code, while the database stores them in a more compact and efficient form.
5. Leaving Around Dead and Unused Indexes
Why it’s a problem
In Rails, it’s easy to add indexes. We’re taught to be proactive and treat them as free. But every index has a cost.
Whenever a record is inserted, updated, or deleted, Postgres has to update every relevant index, even the ones no queries are using. As the number of indexes grows, write performance gets worse. I’ve seen p90 insert times jump from sub-millisecond to over 15 ms, purely because of how many indexes the table had. That’s time your web thread is blocked, stuck waiting on Postgres instead of handling the next request.
What to do instead
- Track index usage over time. If Postgres hasn’t used an index for a reasonable period, drop it.
- In Rails, you can use RailsPgExtras to surface unused indexes. If an index has zero scans and isn’t unique, it’s probably safe to drop.
Conclusion
PostgreSQL can be incredibly fast, but only if you use it with care. These anti-patterns show up quietly: one UUID primary key, one index, one column at a time. Before long, the app slows down, queries drag, infra bills grow, and the app spends more and more time waiting on the database.
Performance isn’t magic. It’s the result of small, intentional decisions that add up over time: lean tables, healthy indexes, and regular maintenance. In high-throughput systems, those details make all the difference.