Diagnosing and fixing critical PostgreSQL performance issues: A deep dive

I recently worked on optimizing a PostgreSQL database that was facing serious performance issues. Some of the main complaints were: Queries were slow to return results, even when using indexed columns. Inserts, deletes, and updates were painfully slow. Maintenance tasks like reindexing, vacuuming, analyzing, and the like were nearly impossible to run. In this article, I’m going to break down what was behind the poor performance. We’ll cover things like over-indexing, bloated tables, fragmented indexes, basic maintenance tasks, some modeling tips, the roadmap I used to diagnose the problem (which you can adapt to your own case), the solution I came up with, and what I’d do differently to prevent this kind of situation from happening again. From time to time, you’ll see text formatted like this: Example of text. These are just technical side notes for certain technologies or features I mention—feel free to skip them. Text formatted like this: Example of text. Means it's a side comment. Or like this: — "Example of text." Are meant to simulate questions the reader might be thinking. Note 1: While I do explain many of the terms, causes, and consequences of most of the issues here, it’s best if you have at least a bit of background in databases beyond basic SELECTs. Note 2: This article isn’t meant to showcase clean or optimized code examples. The SQL and Scala snippets could definitely be improved. Think of it as a mental exercise—how would you improve their readability and performance? Table of Contents Diagnosing the Problem Possible causes Hardware resources and parameter tweaks Computational resources Locks Table locks Database and Tables Large volumes of data Bloated Tables & Fragmented Indexes Fixing the problem Rebuilding the table Reloading the data How can this be avoided? Conclusion Diagnosing the problem. To kick things off, it’s important to mention that we didn’t have query metadata or anything similar—pg_stat_statements wasn’t enabled (nor any analytics service like RDS’), and we had very little visibility into query history to identify areas for improvement. Possible causes. To make our “investigation” a bit easier, I laid out a few areas to check. We’ll break each one down below in this order, but feel free to jump ahead—reading them in order isn’t necessary: Lack of computational resources Table locks Too much data Bloated tables Fragmented tables Hardware resources and parameter tweaks. I started with a general check of the system. Others had already said the server wasn’t maxed out in any way, but I like to confirm things for myself. At the time, the server was showing: ~25% average CPU usage ~65% average RAM usage ~4.3k ops/s (NVMe SSD) Disk ~90% full At this point, you might be thinking: — “Only 10% free disk? That’s risky—ideally it should be at least 20%.” And you’d be right. I agree with that take. But in this case, that alone didn’t explain the massive performance drop. With all the other metrics well below danger zones, we ruled out resource bottlenecks. Computational resources. Even though resource limitations weren’t the issue, I still suggested tweaking some PostgreSQL parameters. The config had never been touched, and many default settings are made for local setups where resources are shared—unlike our case, where the server was dedicated to the database. Some of the parameters we updated were: shared_buffers work_mem effective_cache_size effective_io_concurrency maintenance_work_mem These changes made better use of the available hardware and did improve things like ORDER BY queries and some maintenance tasks (on smaller tables), but this clearly wasn’t the main issue. Locks. Certain types of long-lasting or widespread locks can definitely wreak havoc on read/write performance and even block maintenance tasks. As I said before, we didn’t have historic query or lock data, but we could monitor the locks currently active. During peak hours, I ran this query: SELECT t.schemaname, t.relname, l.locktype, l.mode, l.granted, a.usename, a.query, a.query_start, age(now(), a.query_start) AS query_duration FROM pg_locks l JOIN pg_stat_all_tables t ON l.relation = t.relid JOIN pg_stat_activity a ON l.pid = a.pid WHERE t.relname IS NOT NULL ORDER BY t.relname, l.mode; And here’s what we got: pid table locktype mode granted usename query query_start duration 22956 table_d relation AccessShareLock TRUE postgres - 2025-06-16 13:00:31.543569+00 00:00.3 24810 table_e relation AccessShareLock TRUE postgres - 2025-06-16 11:39:29.805778+00 21:02.0 24985 table_e relation ShareUpdateExclusiveLock TRUE NULL autovacuum: VACUUM ANALYZE public.table_e (to prevent wraparound) 2025-06-16 11:39:32.468211+00 20:59.3 25102 table_f relation AccessShareLock TRUE postgres - 2025-06-16 11:39:29.805778+00 21:02.0 There were dozens more rows like thes

Jun 16, 2025 - 21:40

Diagnosing and fixing critical PostgreSQL performance issues: A deep dive

I recently worked on optimizing a PostgreSQL database that was facing serious performance issues. Some of the main complaints were:

Queries were slow to return results, even when using indexed columns.
Inserts, deletes, and updates were painfully slow.
Maintenance tasks like reindexing, vacuuming, analyzing, and the like were nearly impossible to run.

In this article, I’m going to break down what was behind the poor performance. We’ll cover things like over-indexing, bloated tables, fragmented indexes, basic maintenance tasks, some modeling tips, the roadmap I used to diagnose the problem (which you can adapt to your own case), the solution I came up with, and what I’d do differently to prevent this kind of situation from happening again.

From time to time, you’ll see text formatted like this:

Example of text.

These are just technical side notes for certain technologies or features I mention—feel free to skip them.

Text formatted like this:
Example of text.
Means it's a side comment.

Or like this:
— "Example of text."
Are meant to simulate questions the reader might be thinking.

**Note 1: While I do explain many of the terms, causes, and consequences of most of the issues here, it’s best if you have at least a bit of background in databases beyond basic SELECTs.
**Note 2: This article isn’t meant to showcase clean or optimized code examples. The SQL and Scala snippets could definitely be improved. Think of it as a mental exercise—how would you improve their readability and performance?

Diagnosing the Problem
- Possible causes
- Hardware resources and parameter tweaks
- Computational resources
- Locks
- Table locks
- Database and Tables
- Large volumes of data
- Bloated Tables & Fragmented Indexes
Fixing the problem
- Rebuilding the table
- Reloading the data
How can this be avoided?
Conclusion

Diagnosing the problem.

To kick things off, it’s important to mention that we didn’t have query metadata or anything similar—pg_stat_statements wasn’t enabled (nor any analytics service like RDS’), and we had very little visibility into query history to identify areas for improvement.

Possible causes.

To make our “investigation” a bit easier, I laid out a few areas to check. We’ll break each one down below in this order, but feel free to jump ahead—reading them in order isn’t necessary:

Lack of computational resources
Table locks
Too much data
Bloated tables
Fragmented tables

Hardware resources and parameter tweaks.

I started with a general check of the system. Others had already said the server wasn’t maxed out in any way, but I like to confirm things for myself. At the time, the server was showing:

~25% average CPU usage
~65% average RAM usage
~4.3k ops/s (NVMe SSD)
Disk ~90% full

At this point, you might be thinking:

— “Only 10% free disk? That’s risky—ideally it should be at least 20%.”

And you’d be right. I agree with that take. But in this case, that alone didn’t explain the massive performance drop. With all the other metrics well below danger zones, we ruled out resource bottlenecks.

Computational resources.

Even though resource limitations weren’t the issue, I still suggested tweaking some PostgreSQL parameters. The config had never been touched, and many default settings are made for local setups where resources are shared—unlike our case, where the server was dedicated to the database.

Some of the parameters we updated were:

shared_buffers
work_mem
effective_cache_size
effective_io_concurrency
maintenance_work_mem

These changes made better use of the available hardware and did improve things like ORDER BY queries and some maintenance tasks (on smaller tables), but this clearly wasn’t the main issue.

Locks.

Certain types of long-lasting or widespread locks can definitely wreak havoc on read/write performance and even block maintenance tasks. As I said before, we didn’t have historic query or lock data, but we could monitor the locks currently active.

During peak hours, I ran this query:

SELECT 
    t.schemaname,
    t.relname,
    l.locktype,
    l.mode,
    l.granted,
    a.usename,
    a.query,
    a.query_start,
    age(now(), a.query_start) AS query_duration
FROM pg_locks l
JOIN pg_stat_all_tables t ON l.relation = t.relid
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE t.relname IS NOT NULL
ORDER BY t.relname, l.mode;

And here’s what we got:

pid	table	locktype	mode	granted	usename	query	query_start	duration
22956	table_d	relation	AccessShareLock	TRUE	postgres	-	2025-06-16 13:00:31.543569+00	00:00.3
24810	table_e	relation	AccessShareLock	TRUE	postgres	-	2025-06-16 11:39:29.805778+00	21:02.0
24985	table_e	relation	ShareUpdateExclusiveLock	TRUE	NULL	autovacuum: VACUUM ANALYZE public.table_e (to prevent wraparound)	2025-06-16 11:39:32.468211+00	20:59.3
25102	table_f	relation	AccessShareLock	TRUE	postgres	-	2025-06-16 11:39:29.805778+00	21:02.0

There were dozens more rows like these showing AccessShareLocks on other tables.

Now maybe you’re thinking:

— “Aha! It’s the locks! That’s what’s killing performance!”

Sorry to disappoint—AccessShareLocks are super permissive. They mostly coexist with everything except AccessExclusiveLock (which is used by DROP TABLE, CLUSTER, REINDEX, VACUUM FULL, table-altering commands, etc). So they’re not the problem.

But then you ask:

— “What about that ShareUpdateExclusiveLock?”

Good catch. It’s a bit more restrictive, blocking maintenance tasks and table structure changes. So maybe this is the bad guy preventing maintenance from running?

Not really. That lock was taken by an autovacuum trying to run VACUUM ANALYZE. In reality, this process probably never finishes and ends up just hanging there. Our move here was to disable autovacuum temporarily and kill the zombie process.

You can cancel it like this (replace 24985 with your PID):

SELECT pg_cancel_backend(24985);

If that doesn’t work:

SELECT pg_terminate_backend(24985);

Then, we disable autovacuum for that table:

ALTER TABLE table_e SET (autovacuum_enabled = false);

table_e wasn’t super critical—only a few queries hit it—so this helped a bit but wasn’t a game changer.

Lock Tables.

Database and tables.

At this point, the best move was to stop looking at things too broadly and zoom in on something more specific—maybe a slow query or a concrete complaint like:

— “The database is really slow.”

That kind of vague complaint doesn’t help much. Ideally, you want to narrow things down until you find the actual pain point. Is it a specific query? Then start with the EXPLAIN plan. That’s SQL tuning 101. But as I mentioned earlier, we didn’t have access to historical queries, and there wasn’t enough logging to get insights. That made the challenge more… let’s say, “fun.”

We knew all operations—reads, writes, maintenance—were way too slow. So the problem wasn’t a lack of indexes (too many indexes usually slow down writes, but reads get faster), and we couldn’t blame bad query design either—we simply didn’t have the history or even access to the codebase using the database.

So next step: maybe we’re dealing with too much data?

Checking the data volume.

Let’s start with the obvious: how many rows do these tables actually have?

SELECT COUNT(id) FROM my_table;

Yeah… that didn’t go well. It ran for 10 minutes before I killed it. Not helpful.

So, plan B: table statistics.

SELECT 
    schemaname, 
    relname AS tablename, 
    n_live_tup,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

This gives an estimate of live rows based on the last time PostgreSQL collected stats, along with timestamps for the last ANALYZE.

Results (abridged):

schema	table	live_tuples	last_analyze	last_autoanalyze
public	table_a	1.3 billion	null	2025-03-20
public	table_b	500 million	null	2025-01-03
public	table_c	200 million	2025-03-15	2025-03-16

No surprise here—the stats were outdated by months. These numbers were basically useless. But we already had a clue: missing ANALYZE runs.

ANALYZE updates internal stats used by the query planner to figure out the best way to run a query. It’s critical maintenance.

If these stats aren’t being updated—and it’s not because nobody tried—it’s probably a symptom of a deeper issue.

Estimating row count without stats.

Still, I needed a ballpark number. Since every table had a sequential id, I ran:

SELECT MAX(id) FROM table_a;

Getting the MAX of a sequential, indexed column like id is usually very fast thanks to how b-tree indexes work—the DB can just peek at the last page.

Result:

max(id)
9,846,091,813

So yeah… nearly 10 billion inserts at some point. That doesn’t mean the table has 10 billion rows now—just that it’s had a lot of activity.

To estimate how many rows were still there, I used external tooling. Since the DB couldn't handle a simple count or run ANALYZE, I turned to Spark.

val query = "(SELECT * FROM table_a WHERE id < 1000000000) AS subquery"

val df = spark.read
  .jdbc(jdbcUrl, query, connectionProps)

println(df.count())

The idea: If IDs under 1 billion return ~1 billion rows, we might really have close to 10 billion rows. If not, we extrapolate based on what we get.

— “But why use Spark to count instead of waiting it out in the DB?”

Good question. When I tried counting in Postgres, it took over 40 minutes and didn’t finish. Selecting a sample was way faster.

After 30 minutes of Spark chugging away, I got the answer: 14 million rows for IDs under 1 billion. So I estimated roughly 125 million total rows (not linear, but close enough).

Too much data.

Next question: what if we had massive delete or update operations in the past? That would create tons of dead tuples—especially if VACUUM or VACUUM FULL hadn’t been running.

Even though we know PostgreSQL’s stats were way off, I still checked them just to confirm our suspicions.

SELECT 
    n_tup_ins AS inserts,
    n_tup_upd AS updates,
    n_tup_del AS deletes,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'table_a';

Result:

inserts	updates	deletes	last_vacuum	last_autovacuum
117 million	659	3.25 billion	null	2025-01-30

Three. Billion. Deletes. That’s… a lot.

Now let’s see how bloated the table is:

SELECT 
    s.schemaname, 
    s.relname AS tablename, 
    s.n_live_tup AS live_tuples,
    s.n_dead_tup AS dead_tuples,
    CASE 
        WHEN s.n_live_tup = 0 THEN 0 
        ELSE (s.n_dead_tup::numeric / NULLIF(s.n_live_tup,0)::numeric) * 100 
    END AS dead_tuples_pct,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_stat_user_tables s
JOIN pg_class c ON s.relid = c.oid
ORDER BY s.n_live_tup DESC;

Result (simplified):

table	live_tuples	dead_tuples	dead_tuples_pct
table_a	1.38B	612M	44.2%

Similar stats for other large tables too.

This confirmed two things:

Bloated tables and probably Fragmented tables.

Even though PostgreSQL’s stats were outdated and some operations were failing, we could already see the pattern. Tons of deletes happened, and no follow-up VACUUM to clean things up. The tables were bloated, and reads/writes were getting slower by the day.

PostgreSQL doesn’t immediately remove deleted rows. Because of MVCC (Multi-Version Concurrency Control), it needs to keep old versions around for open transactions.

So you may be wondering:

— “Okay, but what makes a table fragmented?”

Or:

— “Aren’t bloated and fragmented tables the same thing?”

Not quite. Bloated tables usually are fragmented, but not always the other way around. REINDEX can help fix both—but only when it can actually run. In our case, it was completely unusable.

Fragmented tables have rows scattered across disk in a messy way. That kills sequential scans.
Bloated tables are ones where dead tuples and overhead take up a huge chunk of storage—sometimes 30–40% or more.

Fixing the problem.

Alright, so we’ve (probably) identified the culprits: the most heavily used tables are bloated and fragmented beyond hope. We also know that basic maintenance operations like VACUUM, REINDEX, or even ANALYZE don’t complete anymore. So, what options are left?

The most practical and efficient solution? Rebuild the table outside the database.

The idea is:

Extract the entire table’s data.
Apply any necessary cleanup or transformation.
Import it back into the database in a fresh, clean table.
Swap the old and new tables.

You might be wondering:

— “But if the table has around 140 million rows, won’t the extract/load process eat up a ton of resources?”

Not if you use Spark. Of course, tools like DuckDB or Polars might be even faster, but I used Spark because I already had the environment and some code ready to go.

To make life easier, I split the export into chunks (batches). This way I could:

Get feedback during the process
Resume from checkpoints in case anything failed

Here’s the Scala code I used to extract in 1-billion-row batches based on the primary key (a serial ID):

val jdbcUrl = "jdbc:postgresql://hostname:5432/database_name"
val jdbcUser = "admin"
val jdbcPassword = "admin"
val tableName = "table_a"

val connectionProperties = new java.util.Properties()
connectionProperties.setProperty("user", jdbcUser)
connectionProperties.setProperty("password", jdbcPassword)
connectionProperties.setProperty("fetchsize", "1000000")

val batchSize = 1000000000L // 1 billion
val totalRecords = 10000000000L // 10 billion
val numBatches = (totalRecords / batchSize).toInt

val outputBasePath = "data/path"

for (batchIndex <- 0 until numBatches) {
  val startId = batchIndex * batchSize
  val endId = (batchIndex + 1) * batchSize - 1

  val batchDF = spark.read
    .jdbc(
      jdbcUrl,
      s"(SELECT * FROM $tableName WHERE id BETWEEN $startId AND $endId) AS tmp",
      connectionProperties
    )

  val batchOutputPath = s"${outputBasePath}batch_${startId}_to_${endId}.parquet"
  println("Working in export...")

  batchDF.write
    .mode(SaveMode.Overwrite)
    .csv(batchOutputPath)
}

Note: This snippet isn’t complete—it’s missing Spark setup and imports, but you get the idea.

Export time averaged around 1 hour and 20 minutes per batch, plus 40 minutes to write each file as CSV. Interestingly, export times increased with each batch, likely due to DB caching. Restarting the DB between exports might’ve helped.

Oh—and fun fact: we ended up with **250 million rows, which was 110 million more than estimated.

Rebuilding the table.

Once export was done, we needed to recreate the table structure with all its indexes and constraints. Fortunately, PostgreSQL makes that easy:

CREATE TABLE new_table_a (LIKE table_a INCLUDING ALL);

This clones the entire table structure—columns, types, indexes, constraints, everything (except the data). Now we were ready to reload the data.

Reloading the data.

In our case, no data transformation was needed, so we went straight to reimporting.

I benchmarked two methods:

1. Batch inserts (Spark)

for (batchIndex <- 0 until numBatches) {
  val startId = batchIndex * batchSize
  val endId = (batchIndex + 1) * batchSize - 1
  val csvPath = s"${inputBasePath}batch_${startId}_to_${endId}.csv"

  val df = spark.read.csv(csvPath)
  val dfToWrite = df.repartition(100)

  dfToWrite.write
    .mode(SaveMode.Append)
    .option("isolationLevel", "NONE")
    .jdbc(jdbcUrl, jdbcTable, connectionProperties)
}

Using this method, we managed to import 250 million rows in about 40 minutes.

2. COPY command

val numExecutors = 3

val fs = FileSystem.get(spark.sparkContext.hadoopConfiguration)
val baseDir = new Path(inputBasePath)
val csvFiles = fs.listStatus(baseDir)
  .filter(_.getPath.getName.endsWith(".csv"))
  .map(_.getPath.toString)
  .sorted

val executor = Executors.newFixedThreadPool(numExecutors)

csvFiles.foreach { csvPath =>
  executor.submit(new Runnable {
    override def run(): Unit = {
      var connection: Connection = null
      var reader: BufferedReader = null

      try {
        connection = DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword)
        connection.setAutoCommit(false)

        val hadoopPath = new Path(csvPath)

        val localPath = if (csvPath.startsWith("file:")) {
          csvPath.substring(5)
        } else {
          val tmpDir = System.getProperty("java.io.tmpdir")
          val localFile = new File(tmpDir, s"import_${System.currentTimeMillis()}_${hadoopPath.getName}")
          fs.copyToLocalFile(hadoopPath, new Path(localFile.getAbsolutePath))
          localFile.getAbsolutePath
        }

        val copyManager = new CopyManager(connection.asInstanceOf[BaseConnection])
        val copySQL = s"""COPY $jdbcTable FROM STDIN WITH (FORMAT CSV, DELIMITER '$delimiter', HEADER)"""

        reader = new BufferedReader(new FileReader(localPath))
        val rowsCopied = copyManager.copyIn(copySQL, reader)

        connection.commit()
        println(s"[${Thread.currentThread().getName}] Imported: $rowsCopied rows.")

      } catch {
        case e: Exception =>
          if (connection != null) Try(connection.rollback())
          println(s"[${Thread.currentThread().getName}] ERROR: ${e.getMessage}")
          e.printStackTrace()
      } finally {
        if (reader != null) Try(reader.close())
        if (connection != null) Try(connection.close())
      }
    }
  })
}

Yes, I know both methods should be parallelized for a fairer comparison—but even running single-threaded, COPY was much faster.

If you’re on AWS RDS, you can still use COPY, but the files go to an S3 bucket instead of the DB server. Check the AWS docs for more.

With COPY, we loaded 250 million rows in just over 15 minutes.

The COPY command is inherently faster because it loads raw data into memory and defers constraint checks and trigger execution until after the load. One downside: it’s all-or-nothing—if something breaks, you can’t resume halfway. If you want a deeper dive into COPY, check out this great article by a friend of mine: Speed up your PostgreSQL bulk inserts with COPY

Finally, to complete the switch:

BEGIN;
DROP TABLE table_a;
ALTER TABLE new_table_a RENAME TO table_a;
COMMIT;

Note: If any foreign keys referenced the original table, you’ll need to drop and recreate them manually. PostgreSQL doesn’t have a built-in way to do this automatically.

The rebuild process was done during a service freeze—no new data was being written or queried during the whole extraction/import. Some smaller tables were fixed with just VACUUM FULL and REINDEX.

The result?

Disk usage dropped by over 60%
More than 1.5 TB of space freed.
Massive performance gains across the board.

How can this be avoided?

After solving the issue, I went digging to figure out what might’ve caused those massive delete operations that ultimately blocked PostgreSQL from running VACUUM.

I talked to a few devs who had direct access to the DB, and they told me a service was being called dozens (maybe hundreds) of times a day. And every time it ran, it inserted the incoming data into the database.

Here’s the catch: those inserts were often unnecessary. Most of the time, the service was receiving duplicated data. But instead of checking for duplicates, the application just blindly inserted everything. There were no proper constraints on the table either. So... boom: we ended up with a mountain of redundant records.

Eventually, someone realized the performance was tanking and decided to clean up the duplicated data. That’s where our villain enters the story: the way those deletes were done.

Here’s the actual code that was used:

DO $$ DECLARE
  total_count INTEGER := 0;
BEGIN
  LOOP
    CREATE TEMP TABLE table_a_duplicates AS 
      SELECT a.id 
      FROM table_a a 
      WHERE (a.name, a.age) IN (
        SELECT name, age FROM table_a 
        GROUP BY name, age HAVING COUNT(*) > 1
      ) 
      LIMIT 100000;

    SELECT count(1) INTO total_count FROM table_a_duplicates;

    DELETE FROM table_a 
    WHERE id IN (SELECT id FROM table_a_duplicates);

    DROP TABLE table_a_duplicates;

    COMMIT;

    EXIT WHEN total_count = 0;

    PERFORM pg_sleep(1);
  END LOOP;
END $$;

It’s a pretty straightforward loop:

Find duplicate rows based on name and age
Store their ids in a temp table (in batches of 100k)
Delete them from the main table
Repeat until there are no more duplicates

Performance-wise, this approach isn’t too bad. The real problem? No VACUUM was run during or after the process. This table had billions of rows. The loop ran for days, and eventually the table became so bloated that VACUUM couldn’t finish anymore. That’s what really tanked the DB.

Now, what if they had just added these three lines?

PERFORM pg_catalog.pg_advisory_lock(12345);
EXECUTE 'VACUUM (ANALYZE) table_a';
PERFORM pg_catalog.pg_advisory_unlock(12345);

With those lines, the cleanup block would’ve looked like this:

DO $$ DECLARE
  total_count INTEGER := 0;
BEGIN
  LOOP
    CREATE TEMP TABLE table_a_duplicates AS 
      SELECT a.id 
      FROM table_a a 
      WHERE (a.name, a.age) IN (
        SELECT name, age FROM table_a 
        GROUP BY name, age HAVING COUNT(*) > 1
      ) 
      LIMIT 100000;

    SELECT count(1) INTO total_count FROM table_a_duplicates;

    DELETE FROM table_a 
    WHERE id IN (SELECT id FROM table_a_duplicates);

    DROP TABLE table_a_duplicates;

    COMMIT;

    EXIT WHEN total_count = 0;

    PERFORM pg_catalog.pg_advisory_lock(12345);
    EXECUTE 'VACUUM (ANALYZE) table_a';
    PERFORM pg_catalog.pg_advisory_unlock(12345);

    PERFORM pg_sleep(1);
  END LOOP;
END $$;

This entire problem could’ve been avoided by having the right constraints in place to begin with. The issue here was the lack of a unique constraint to prevent duplicate rows. The app should’ve either rejected duplicates or used UPSERT.

This is why it's crucial to test against real-world scenarios and take time to understand your schema. Well-designed constraints can save your DB from future disaster.

For more on designing scalable, robust PostgreSQL databases, I wrote a follow-up piece: Designing robust and scalable relational databases: A series of best practices

After the rebuild, performance improved massively—insert, update, delete, read, maintenance, everything. I didn’t have any performance-monitoring tools enabled at the time (which is on me), so I couldn’t get hard numbers, but based on sample queries, we saw an average 250% improvement in response time. Oh, and we also freed up more than 1.5 TB of disk space.

Conclusion

PostgreSQL has its quirks—but with solid modeling, those quirks rarely become a problem. Of course, surprises and edge cases are unavoidable, but the goal is to reduce how often they happen.

Sometimes, a single operation executed without proper thought—especially in large-scale environments—can lead to catastrophic outcomes, potentially costing thousands of dollars in downtime, troubleshooting, and emergency fixes.

Before you run anything that touches a large amount of data, take a moment to ask yourself:

“How will the database handle this volume of information?”

And study the cost of each operation—some are cheap, others will absolutely wreck your performance if used carelessly.

Also, don’t forget: optimization should always align with how your services interact with the database. That’s where real gains happen. Having monitoring in place—like AWS Performance Insights or enabling pg_stat_statements—gives you visibility into where the real bottlenecks are, so you’re not left guessing.

(I'm planning to write a guide on using pg_stat_statements soon, by the way.)

I hope this article helped clarify the root of the problem and gave some insight into how we approached the fix.

Thanks a lot for reading