When someone calls me about a performance problem, the first thing I usually hear is "the database is slow." Sometimes that's true. Most of the time it isn't. The database is doing exactly what it was asked to do. It's just being asked to do the wrong thing, or it's been cut out of the work entirely.

Buying a bigger server is the tempting fix, and sometimes it buys you a few months. But if the problem is how the work is organized, more hardware just makes the wrong approach run a little faster. Here are a few stories and lessons from projects where the fix wasn't more horsepower.

Before ~48 hours PHP doing the processing
After Under 1 hour SQL Server doing the processing

About a 98% cut in runtime. Same data, same report, no new hardware.

Two days to find out if a marketing campaign worked

I was approached about a PHP script behind a marketing report. The company ran campaigns to mailing lists, and they wanted to know which ones actually worked: how many people on each list had become customers, broken down by campaign and by mailing list. The mailing lists lived in a MySQL database. The customer accounts lived in Microsoft SQL Server. The script pulled from both and matched people up by last name, street address, and ZIP code. It worked. It also took two days to run.

Neither database was the problem. Both were perfectly capable of answering the question. The problem was that PHP was doing the processing. The script pulled the data out of both databases and did the matching and counting itself, in application code. That meant moving a lot of data over the network and then grinding through it in a language that was never designed for that kind of work.

The fix was to stop making PHP do the database's job. I set up a linked server, which lets SQL Server query the MySQL database directly, so both sets of data could be joined in one place. Then I moved the matching into a SQL Server stored procedure. PHP went from doing the heavy lifting to just calling the procedure, formatting the results, and emailing the report.

Before: PHP does the matching (2 days) MySQL raw data out SQL Server raw data out PHP script matches + counts everything After: the database does the work (under 1 hour) MySQL linked server SQL Server matches + counts in one query PHP emails the report
Same report, same data. The only change was where the processing happened.

Here's a simplified version of the stored procedure, with the table names changed. To keep it short, this one only counts by campaign; the real procedure could also break the results down by mailing list. The idea is the same either way: SQL Server reaches into MySQL through the linked server and does the matching in a single query.

-- SQL Server, with a linked server named MAILERDB pointing at MySQL
-- Simplified: counts by campaign only
WITH Mailer AS (
  SELECT * FROM OPENQUERY(MAILERDB,
    'SELECT ca.campaign_name,
            TRIM(c.last_name) AS last_name,
            TRIM(c.street1)   AS street1,
            c.zip
     FROM mailing_list_customers c
     JOIN campaigns ca ON c.campaign_id = ca.campaign_id')
)
SELECT m.campaign_name, COUNT(*) AS customer_count
FROM Mailer AS m
JOIN dbo.Members AS member
  ON  member.Last_Name = m.last_name
  AND member.Address_1 = m.street1
  AND member.Zip       = m.zip
GROUP BY m.campaign_name;

The real procedure also had the mailing-list breakdown and a fuzzy-matching version, but that's the core of it. The same report that used to take two days ran in under an hour. Nobody bought new hardware. We just put the work where it belonged.

I'm not saying linked servers are always the answer. Joining across two databases adds network latency, makes query planning harder, and means one system now depends on the other being up. In this case, taking the processing away from PHP saved so much time that the tradeoff was clearly worth it. On another project, copying the data into one place on a schedule might be the better call.

The database wasn't slow. It was being kept out of the job it was built to do.

It matters where the logic lives

Databases are built to work with sets of data: joining, filtering, grouping, and sorting millions of rows at a time. Application code tends to work one row at a time. When you pull everything into the application to process it, you pay twice: once to move all that data across the network, and again to process it in a loop with none of the optimizations the database would have used.

That doesn't mean all your logic belongs in stored procedures. Business rules that change often, calls to outside services, and anything that needs to be easy to test usually belong in the application. But when the job is "combine these tables, filter them, and summarize the result," that's what the database is for. A good rule of thumb: let the database shrink the data, and let the application decide what to do with what's left.

Read the query plan before you guess

Once the work is in the right place, the next question is how the database is actually running your query. Every major database will tell you if you ask. In PostgreSQL it's EXPLAIN ANALYZE; in SQL Server it's the actual execution plan; in MySQL it's EXPLAIN. The plan shows whether the database is jumping straight to the rows it needs or reading the entire table to find them.

-- PostgreSQL
EXPLAIN ANALYZE
SELECT id, email FROM customers WHERE email = 'pat@example.com';
--  Seq Scan on customers ...        <- reads every row in the table

CREATE INDEX idx_customers_email ON customers (email);

EXPLAIN ANALYZE
SELECT id, email FROM customers WHERE email = 'pat@example.com';
--  Index Scan using idx_customers_email on customers ...
--  (usually, if the planner decides it's cheaper; see below)

A full table scan on a small table is fine. Reading tens of millions of rows to return a handful of them, in a query that runs thousands of times a day, is where your time goes. The plan tells you which one you've got, so you're not adding indexes on a hunch.

Adding an index doesn't force the database to use it, either. PostgreSQL still decides based on table size, statistics, and how many rows it expects to return. On a small table, or a query that returns a big slice of the table, a sequential scan can genuinely be the faster choice. If the plan doesn't change after you add an index, check that the statistics are current (ANALYZE) before assuming the index is wrong.

One more thing the plan will show you: wrapping a column in a function usually stops the database from using an index on it. That same marketing report had a "fuzzy" option that matched names and addresses with SOUNDEX(), so a typo like "Smyth" would still match "Smith." Useful, but SOUNDEX(Last_Name) = SOUNDEX(@name) generally means computing the value for every row before comparing. If a query like that runs often, store the computed value in its own indexed column so the database can look it up instead of recalculating it.

Data types are a performance decision

Data types usually get picked in a hurry when a table is first created, and then nobody thinks about them again. They matter more than people expect.

  • Smaller types mean less to read. A database reads data in pages. The smaller each row is, the more rows fit on a page, and the less disk and memory every query needs. A two-letter state code doesn't need a 255-character column, and a status flag doesn't need a BIGINT.
  • Mismatched types can quietly disable your indexes. In SQL Server, comparing a VARCHAR column to an NVARCHAR value causes an implicit conversion. Depending on the types, the collation, and the query, that can stop the optimizer from using an index seek and turn it into a scan. Joining an INT column to a text column can cause the same kind of problem.
  • Dates belong in date columns. Dates stored as text sort wrong, can't be compared as ranges efficiently, and invite bad data like "13/45/2024". Use the database's real date and timestamp types.
  • Numbers belong in number columns. Prices, quantities, and IDs stored as strings have to be converted before they can be compared or added up, and that conversion usually happens on every row.

None of these feel like much on day one. A few years and a few million rows later, they're the difference between a query that takes milliseconds and one that times out.

And yes, you can have too many indexes

Once people see what one good index can do, the natural instinct is to index everything. I understand the impulse, but it backfires. Every index has to be updated on every insert, update, and delete. Every index takes up disk space and competes for memory. And a table with a dozen overlapping indexes gives the query planner more ways to make a bad choice.

Index the queries you actually run, not the ones you might run someday. And check every so often for indexes nobody uses. In PostgreSQL, this shows indexes that haven't been used since statistics were last reset:

SELECT relname AS table_name, indexrelname AS index_name, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

Don't drop everything on that list right away. Some of those indexes enforce uniqueness, some are used by monthly jobs or by a read replica, and the statistics only cover the period since they were last reset. But it's a good place to start the conversation.

An index is a trade: faster reads in exchange for slower writes. Make the trade on purpose.

Every business record needs a reliable identity

This one isn't about speed, but it's caused me more cleanup work than any slow query. I once had to explain to a junior developer why every table needs a primary key. It's a fair question if nobody has ever shown you what happens without one, so here's the short version I gave them.

Without a primary key or a unique constraint, the database has no reliable way to tell one row from another, and nothing stops the same record from being inserted twice. Picture an import that accidentally runs twice and leaves two identical rows behind:

CREATE TABLE email_signups (
  email      VARCHAR(255),
  signed_up  DATE
);

-- the import runs twice
INSERT INTO email_signups VALUES ('pat@example.com', '2026-11-01');
INSERT INTO email_signups VALUES ('pat@example.com', '2026-11-01');

-- now try to remove just one of them
DELETE FROM email_signups WHERE email = 'pat@example.com';  -- deletes both

Now you've got duplicates you can't cleanly remove, reports that count people twice, and updates that hit more rows than you meant them to. Many ORMs and replication tools also expect a primary key and behave badly or refuse to work without one. Over time, that's how data quietly gets corrupted: not in one dramatic failure, but in a slow pile-up of rows nobody can trust.

CREATE TABLE email_signups (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      VARCHAR(255) NOT NULL UNIQUE,
  signed_up  DATE NOT NULL
);

The primary key gives every row an identity. The UNIQUE constraint on the email is just as important: it's what actually stops the duplicate from getting in. A primary key alone tells you which row is which; constraints are how you tell the database what "duplicate" means for your data. Some tables, like a raw staging table that gets cleaned up right after an import, can reasonably skip a primary key. But any table holding records your business relies on needs one, or at least a unique constraint that does the same job.

How I diagnose a "slow database"

  • Measure first. Find out where the time actually goes: the application, the network, or the database. It's often not where people assume.
  • Find the expensive queries. Use the slow query log, pg_stat_statements in PostgreSQL, or Query Store in SQL Server. Look at total time, not just the single slowest run. A 50-millisecond query that runs a million times a day matters more than a 10-second report that runs once.
  • Check where the logic lives. If the application is pulling large amounts of data just to filter or combine it, move that work into the database.
  • Read the plan. Look for full scans on big tables, conversions you didn't expect, and row estimates that are wildly off.
  • Fix types and indexes deliberately. Use the plan and your workload statistics to decide which indexes would actually help. Review indexes that look unused or redundant before removing them, since some enforce constraints or serve jobs that only run occasionally.
  • Then consider hardware. Sometimes you really do need a bigger server. It should be the last answer, not the first.

Every one of these fixes is less exciting than a hardware upgrade or a new database platform. That's sort of the point. The two-day report didn't need new technology. It needed the work moved to the right place, and that's true more often than not.

Before you blame the database, make sure you've let it do its job.

Engineering leaders who want to see more of the systems I've designed and built can find my engineering portfolio at josephrounds.dev.

Joseph Rounds

Founder, Lighthouse Consulting

25+ years building enterprise software at McKesson (Fortune 10), Doctor On Demand, and IntelyCare. Now helping Boston-area businesses design and build custom software, AWS infrastructure, and AI integrations that fit how they actually operate.