Postgres 42,000x Slower: The Ultimate 2025 Anti-Patterns

The `SELECT *` Trap in Production Code

It's the first thing we learn in SQL, and its convenience is undeniable. But using SELECT * in your application's production code is a ticking time bomb for performance.

The Problem: When you use SELECT *, you're asking the database to retrieve every single column for the rows that match your criteria. This often includes large text or bytea columns, or columns you don't even use in your application logic. As your table schema evolves and new columns are added, this query silently becomes heavier and more expensive.

The Impact:
1. Increased I/O: The database has to read more data from disk into memory.
2. Network Congestion: More data is sent from the database server to the application server, consuming precious bandwidth.
3. Wasted CPU/Memory: Both the database and your application waste resources serializing and deserializing data that is never used.
4. Index Inefficiency: It can prevent Postgres from using an "index-only scan," a highly efficient operation where the result is fetched directly from the index without ever touching the table's heap.

The Solution: Be explicit. Always specify the exact columns you need in your SELECT statement. This insulates your application from schema changes and ensures you're only fetching the data you require.

Anti-Pattern: SELECT * FROM users WHERE id = 123;
Best Practice: SELECT id, email, created_at FROM users WHERE id = 123;

Abusing JSONB for Relational Data

Postgres's JSONB type is incredibly powerful for storing schemaless or semi-structured data. The anti-pattern emerges when developers use it as a dumping ground for data that is, in fact, highly structured and relational.

The Problem: It's tempting to store a user's profile, preferences, and address all within a single JSONB column to avoid creating new tables. While flexible during early development, this approach undermines the very foundation of a relational database.

The Impact:
1. No Data Integrity: You lose the ability to enforce foreign key constraints, data types, and NOT NULL constraints at the database level.
2. Querying Complexity: Simple relational joins become complex queries with JSON operators and functions, which are often harder to write, read, and optimize.
3. Indexing Challenges: While you can use GIN indexes on JSONB, they are generally less efficient for the highly selective queries that a traditional B-tree index on a normalized column excels at.
4. Bloated Tables: Storing everything in one column can lead to bloated tables and inefficient data retrieval, especially when you only need a small piece of the JSON document.

The Solution: Use JSONB for what it's designed for: unstructured or semi-structured data that truly varies per row (e.g., user settings, tags, event metadata). For core, relational entities (like addresses, profiles, or orders), use normalized tables with foreign keys. This leverages the full power of the relational model.

Ignoring Index Bloat and Maintenance

You've created indexes on all your foreign keys and frequently queried columns. Your work is done, right? Wrong. Indexes, like the tables they serve, require regular maintenance.

The Problem: PostgreSQL's MVCC (Multi-Version Concurrency Control) architecture means that when rows are updated or deleted, the old versions aren't immediately removed. They are marked for deletion and cleaned up later by a process called VACUUM. This process also affects indexes, leading to "index bloat"—where the index grows much larger than necessary, filled with dead entries.

The Impact:
1. Slower Reads: A bloated index contains many empty or dead pages. The query planner might still scan these pages, leading to more I/O and slower queries.
2. Wasted Disk Space: Bloat consumes disk space unnecessarily.
3. Slower Writes: Larger indexes are more expensive to update on every INSERT, UPDATE, or DELETE operation on the table.

The Solution: Proactive maintenance is key. Ensure your autovacuum settings are tuned for your workload. For high-traffic tables, you may need more aggressive settings. Periodically monitor for index bloat using built-in statistics or tools like pgstattuple. In severe cases, you may need to run REINDEX to rebuild the index from scratch, which can be done concurrently in modern Postgres versions to minimize locking.

The Late-Row-Fetching Fiasco

This is a more subtle anti-pattern that often lies at the heart of massive performance degradation, especially with `LIMIT` clauses on large tables. This is a key contributor to our "42,000x slower" scenario.

The Problem: Consider a query that sorts a huge table and then picks a few rows:
SELECT * FROM large_table ORDER BY non_indexed_column LIMIT 10;
Postgres has to fetch all the rows, sort them in memory or on disk (a very slow process), and then discard all but 10. A more insidious version happens even with an index. Imagine:
SELECT * FROM user_posts ORDER BY created_at DESC LIMIT 10;
If the `user_posts` table is large, and you have an index on `created_at`, Postgres will use the index to find the 10 latest post IDs. But because you used SELECT *, it then has to perform 10 separate random disk reads to fetch the full row data for each of those IDs from the table's heap. This is "late row fetching."

The Impact: When combined with a full table scan (like a LIKE '%substring%' query on an unindexed column), the database must read the entire table from disk, sort it, and then fetch the rows. When combined with an index, the random I/O from fetching each row individually can become a major bottleneck, negating much of the index's benefit.

The Solution: Defer fetching the full row data until the very end. Use a subquery or a Common Table Expression (CTE) to identify the primary keys of the rows you need first, using indexes as much as possible. Then, join back to the original table to retrieve the full column data for only that small subset of keys.

Anti-Pattern:
SELECT * FROM user_posts WHERE content LIKE '%database%' ORDER BY created_at DESC LIMIT 10;
(This forces a full table scan and sort on millions of rows)

Best Practice (with Full-Text Search Index):
WITH top_posts AS (
SELECT id
FROM user_posts
WHERE fts_vector @@ to_tsquery('database')
ORDER BY created_at DESC
LIMIT 10
)
SELECT p.id, p.title, p.content, p.created_at
FROM user_posts p
JOIN top_posts tp ON p.id = tp.id
ORDER BY p.created_at DESC;
This approach uses the index to find the 10 relevant `id`s quickly, then performs a fast `JOIN` on the primary key to fetch the full data for only those 10 rows.

Inefficient Connection Management

Database performance isn't just about queries; it's also about how your application communicates with the database.

The Problem: Establishing a connection to a PostgreSQL database is an expensive operation. It involves a network handshake, authentication, and the creation of a new backend process on the server. A naive application that opens and closes a new connection for every single query will spend a huge amount of its time and resources just managing connections.

The Impact:
1. High Latency: The overhead of connection setup adds significant latency to every database interaction.
2. Server Resource Exhaustion: Each connection consumes memory and process slots on the database server. A high rate of new connections can quickly exhaust server resources, leading to connection refusals.

The Solution: Always use a connection pooler. A connection pool is a cache of database connections maintained by your application or a separate middleware (like PgBouncer). When your application needs to run a query, it borrows a connection from the pool and returns it when done. This amortizes the high cost of connection setup over thousands of queries, dramatically improving performance and stability.