Database Performance Tuning and Scaling Questions

Direct answer

A production database monitoring dashboard needs metrics from four categories, resource utilization, replication, query performance, and capacity, because a healthy reading in one category can hide a real problem in another. Nine metrics cover the ground well: CPU utilization, buffer cache hit ratio, disk input/output operations per second (IOPS) utilization, disk space used, active connections against the configured maximum, replication lag, replica connectivity state, the 95th and 99th percentile (p95 and p99) query latency, and lock wait time or blocked query count.

How to think about it

This list holds whether the instance is standalone or serving a mixed workload of online transaction processing (OLTP, many small read and write transactions) and online analytical processing (OLAP, fewer, larger queries that scan and aggregate data); a mixed workload just means watching the query-performance metrics separately per workload type, since an analytical query's normal duration and a transactional query's normal duration are entirely different baselines, and one threshold cannot serve both honestly.

Worked example

Real output pulled from a running instance, illustrating what these look like in practice:

-- buffer cache hit ratio
 cache_hit_pct | blks_hit | blks_read
         99.95 |  1562989 |       718

-- per-table scan pattern (a missing-index signal when seq_scan dominates)
      relname       | seq_scan | idx_scan
 order_items_denorm  |        1 |        1
 orders               |        5 |        0

And the replication query returning correctly empty on an instance with no replica attached:

SELECT application_name, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;
-- (0 rows)

Zero rows here is the correct, real answer for this specific instance, not a broken query, a genuine replication setup returns the lag columns shown in a read-replica health check instead.

Trade-offs and pitfalls

Alerting on every metric at the same severity causes alert fatigue, and real pages get ignored along with the noise. Thresholds copied from someone else's blog post instead of your own baseline will page every day if your normal steady state already runs at that level. Missing the replica connectivity state specifically, because a lag graph can look fine while actually frozen at its last reading. Monitoring only resource metrics or only query-performance metrics, never both, hides real incidents either way: high CPU with no p99 alarm can be an entirely healthy scheduled batch job, and a p99 spike with normal CPU is often a lock or an external dependency, not the database's own fault at all. Connections being rejected outright rather than degrading gracefully once the cap is hit is exactly why that threshold should warn well before 100%, not at it.

Direct answer

Adding the column itself should be near-instant: a plain ADD COLUMN with a constant (or
NULL) default is a metadata-only change on modern PostgreSQL and doesn't rewrite the
table. The actual risk is in populating it: run that as an explicit incremental backfill
in small batches with pacing between them, have the application dual-write the new column
on every new or updated row going forward before the backfill starts, and monitor replication lag (how far behind the primary a replica's copy of the data has fallen) and lock wait as hard caps that pause the job automatically.

The plan

The ADD COLUMN step itself. On PostgreSQL 11 and later, ADD COLUMN with a
constant default, including NULL, is metadata-only and does not rewrite existing rows.
That's a genuine change from older versions, and a common misconception is that any ADD COLUMN with a default rewrites the whole table; that's only true for a volatile,
non-constant default, or certain constraint additions. This step takes a brief exclusive
lock, but for milliseconds, a fundamentally different risk than the population step.

If a full backfill is needed (computing a derived value per row, the harder case this
question is really about):

• Incremental technique: iterate in small batches by primary-key range, each batch its
  own short transaction, with a brief pause between batches so replication and any read
  replicas can keep up, and so row locks don't accumulate into one long-held transaction.
• Dual-write: ship the application change that writes the new column on every new or
  updated row first, before the batch backfill starts, so the backfill isn't chasing a
  moving target. The batch job then only needs to cover rows written before that deploy.
• Monitoring and capping impact: watch replication lag (don't let a replica fall far
  enough behind that a failover would lose data, or that it starts serving badly stale
  reads); watch lock-wait time and set a lock_timeout so a batch that's queued too long
  aborts rather than backs up behind unrelated traffic; watch dead-tuple growth (a dead tuple is the old copy of a row that Postgres leaves behind after an UPDATE, since it writes a new row version instead of editing the old one in place); the backfill itself generates a dead row version for every update, a real bloat event, meaning the table fills up with dead tuples faster than they get cleaned out, at this row count that needs its own vacuum planning (VACUUM is the background process that reclaims the space those dead tuples hold so the table doesn't just keep growing); and cap the overall rate (target
  rows/sec) rather than running batches back to back as fast as possible.
• Backing out: since the column can be added nullable with nothing yet depending on it,
  backing out mid-backfill just means stopping the job. The column can sit partially
  populated with no correctness impact as long as nothing treats it as authoritative
  until the backfill and validation are complete, and the dual-write path can be
  feature-flagged off independently of the backfill's progress.

Worked example: backfill timing (pinned inputs)

total_rows = 400_000_000
batch_size_rows = 5_000
sleep_between_batches_ms = 50
exec_time_per_batch_ms = 20  # illustrative per-batch UPDATE execution time

num_batches = total_rows / batch_size_rows
time_per_batch_s = (sleep_between_batches_ms + exec_time_per_batch_ms) / 1000.0
total_time_s = num_batches * time_per_batch_s

print(f"num_batches = {num_batches:,.0f}")
print(f"time_per_batch = {time_per_batch_s*1000:.0f}ms")
print(f"total wall time = {total_time_s:,.0f}s = {total_time_s/3600:.1f} hours")

num_batches = 80,000
time_per_batch = 70ms
total wall time = 5,600s = 1.6 hours

Halving the batch size to 2,500 rows roughly doubles num_batches to 160,000 and, for a
similar per-batch overhead, roughly doubles the wall time to about 3.2 hours, a direct
trade between how gentle each batch is on the live system and how long the whole backfill
takes. That trade is worth sizing explicitly against how much time the migration is
actually allowed to take, not defaulted to "as small as possible."

Trade-offs and pitfalls

• Assuming ADD COLUMN itself is the risky step; on modern PostgreSQL with a constant
  default it usually isn't. The real risk in this scenario is almost always the
  subsequent full-table update to populate a derived value; don't spend the caution
  budget on the step that doesn't need it.
• Smaller batches and longer pauses are safer for the shared system but make the backfill
  take proportionally longer, size this trade-off deliberately, not by habit.
• Shipping the backfill before the dual-write path is verified working means the backfill is chasing a target that's still drifting from concurrent, uninstrumented writes: rows the backfill already processed keep getting changed by callers the dual-write path never reached, so the column silently falls out of sync with its source again after the batch job has already moved past that row. This is the same kind of drift failure that hits any derived copy of data, a cache, a summary table, a replica, whenever its update logic misses a write instead of catching every one.
• Not planning for the backfill's own bloat: 400 million updates generate 400 million
  dead tuples somewhere, a genuinely large vacuum workload competing with the same system
  the whole plan is trying not to disrupt.

Direct answer

tempdb is a single system database shared by every session on the instance: it holds explicitly created temporary tables and table variables, the database engine's own internal work tables (for sort and hash spills, intermediate results, cursors), and the version store (a temporary area that keeps older copies of changed rows around) that backs row-versioning features like snapshot isolation (an isolation level where a transaction sees a consistent snapshot of the data as it stood when the transaction began, instead of seeing other transactions' changes land as they commit) and online index operations. Because every connection shares it, tempdb is usually the first place contention shows up on a busy transactional server. The concrete configuration levers are: split it into multiple equally sized data files (roughly one per logical CPU up to eight, then increase in groups of four only if contention persists), give every file identical initial size and autogrow settings, preallocate enough space that autogrow rarely fires, and put the files on your fastest available storage, separated from user database files only if you are actually seeing shared I/O contention.

Structured elaboration

What tempdb actually holds

• User objects: explicit temporary tables and table variables, temporary stored procedures, cursors.
• Internal objects: the database engine's own scratch space for sort and hash spills, worktables for cursors, and intermediate results for operations like index creation with SORT_IN_TEMPDB.
• Version stores: row versions generated by READ COMMITTED SNAPSHOT or SNAPSHOT isolation, and by online index operations. This means a long-running transaction under snapshot-style isolation can quietly grow tempdb even if nobody is creating a single temp table.

tempdb is recreated from scratch every time the database engine restarts and cannot be backed up, so nothing in it is meant to survive a restart; that also means none of its configuration can be "wrong forever," only wrong until the next restart applies a fix.

Number and sizing of files

Current Microsoft guidance ties file count to logical CPU count on the machine: if the number of logical processors is eight or fewer, use that many data files; if it is more than eight, start with eight; if allocation contention is still observed after that, add more files in groups of four until contention drops to an acceptable level, rather than jumping straight to a much larger number. All tempdb data files must be created with the same initial size and the same autogrow (file growth) settings. This is not a cosmetic preference: SQL Server's proportional-fill allocation algorithm favors whichever file currently has more free space, so unequal file sizes reintroduce the exact single-file hot spot that splitting into multiple files was meant to fix.

Autogrowth settings

Set growth to a fixed size (for example 64 MB) rather than a percentage, and use the same increment across every file. A percentage-based increment grows in ever-larger absolute steps as the file grows, which makes growth events slower and less predictable over the life of the database. Preallocate each file to a size that comfortably covers the workload's typical peak tempdb usage; if the initial size is too small, the engine spends time and I/O growing tempdb back up to a working size every time the instance restarts, right when the server is already under load from restart-time work.

Storage placement

Put tempdb's files on the fastest I/O subsystem available. Individual files do not need to live on separate physical disks from each other unless you are actually seeing disk-level I/O bottlenecks; splitting files is primarily about reducing allocation-page contention, not about spreading I/O across spindles. If tempdb and user databases compete for the same physical I/O and that contention is measurable, separate them onto different storage.

Common pitfalls behind tempdb contention

Two structurally different problems both surface as PAGELATCH waits (a latch is a short-duration, in-memory protection on a page's physical consistency, distinct from a row or table lock that can be held for a transaction's whole lifetime):

1. Allocation-page contention: the Page Free Space (PFS), Global Allocation Map (GAM), and Shared Global Allocation Map (SGAM) pages track which pages and extents (an extent is a fixed group of eight consecutive pages, the unit SQL Server actually allocates space in, rather than one page at a time) in a data file are free. When many sessions create and drop small temporary objects concurrently, they all queue up on the same bookkeeping pages even though they are allocating unrelated objects. Splitting tempdb into multiple equally sized files spreads this load across more independently scanned pages.
2. Metadata contention: every temp-object create and drop also touches rows in tempdb's own system catalog. Under very high create/drop rates this catalog itself becomes a latch hot spot, and adding more data files does nothing for it. Since SQL Server 2019, memory-optimized tempdb metadata (enabled with ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON, which requires a service restart) makes those catalog tables latch-free.

A common, outdated fix worth naming so it is not reached for reflexively: trace flags 1117 (uniform autogrowth across all files in a filegroup) and 1118 (uniform extent allocation) were needed on SQL Server 2014 and earlier. Since SQL Server 2016 both behaviors are the default for tempdb, so setting these flags today is harmless but signals the diagnosis is coming from an old playbook rather than the current engine's actual behavior.

Worked example

Sizing the file count and per-file size for a concrete server is simple arithmetic once the CPU-count rule is applied:

def recommended_tempdb_files(logical_cpus):
    if logical_cpus <= 8:
        return logical_cpus
    return 8  # start at 8; only add more, in groups of 4, if contention persists

logical_cpus = 24
tempdb_budget_gb = 96

n_files = recommended_tempdb_files(logical_cpus)
per_file_gb = tempdb_budget_gb / n_files
print(f"{logical_cpus} logical CPUs -> {n_files} tempdb data files")
print(f"{tempdb_budget_gb} GB budget / {n_files} files = {per_file_gb:.1f} GB per file")

Output (executed):

24 logical CPUs -> 8 tempdb data files
96 GB budget / 8 files = 12.0 GB per file

That gives eight files of 12 GB each. The following is the documented Transact-SQL syntax for that layout (reference syntax, not executed here since this sandbox has no SQL Server instance to run it against): it adds seven additional data files to the default single tempdev file, sizing every file identically and giving each the same fixed-size autogrowth:

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 12000MB, FILEGROWTH = 512MB);

ALTER DATABASE tempdb ADD FILE (NAME = temp2, FILENAME = 'D:\tempdb\temp2.ndf', SIZE = 12000MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb ADD FILE (NAME = temp3, FILENAME = 'D:\tempdb\temp3.ndf', SIZE = 12000MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb ADD FILE (NAME = temp4, FILENAME = 'D:\tempdb\temp4.ndf', SIZE = 12000MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb ADD FILE (NAME = temp5, FILENAME = 'D:\tempdb\temp5.ndf', SIZE = 12000MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb ADD FILE (NAME = temp6, FILENAME = 'D:\tempdb\temp6.ndf', SIZE = 12000MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb ADD FILE (NAME = temp7, FILENAME = 'D:\tempdb\temp7.ndf', SIZE = 12000MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb ADD FILE (NAME = temp8, FILENAME = 'D:\tempdb\temp8.ndf', SIZE = 12000MB, FILEGROWTH = 512MB);

-- confirm the resulting layout is uniform, the property that actually matters:
SELECT name, size * 8.0 / 1024 AS size_mb, growth * 8.0 / 1024 AS growth_mb
FROM tempdb.sys.database_files
WHERE type_desc = 'ROWS';

Every file gets the same 12,000 MB size and 512 MB growth increment on purpose. A layout where one file starts smaller, or grows by a different amount, defeats the point: the proportional-fill allocator will preferentially fill the file with more free space, and the workload funnels right back onto that one file.

Trade-offs & pitfalls

• Do not apply trace flags 1117/1118 on SQL Server 2016 or later. Their behavior is already the default; setting them is not harmful, but it is a sign the diagnosis was made against old documentation rather than the running engine's actual behavior.
• More files is not strictly better. Adding files beyond what CPU concurrency and I/O bandwidth justify adds round-robin scanning overhead without reducing real contention. The "increase in groups of four only if contention persists" guidance exists precisely to stop this from becoming an unbounded knob.
• Memory-optimized tempdb metadata is not a default-on fix. It requires a restart to enable or disable, and its dedicated memory consumer can grow unbounded under heavy load if it is not bound to a resource governor pool with an explicit memory cap. Turn it on once a diagnostic query has actually shown session buildup on tempdb's system catalog tables, not speculatively.
• A long-open transaction under snapshot isolation is a different failure mode entirely. It can grow the version store inside tempdb regardless of how well the files are sized or split, because the engine has to keep old row versions around as long as that transaction might still need to see them. File configuration does not fix this; finding and ending the long-running transaction does.
• Uneven sizing silently defeats the whole exercise. If one file is added later, sized differently, or given a different growth increment than the rest, the fix for allocation-page contention quietly stops working while everything still looks configured correctly at a glance.

Direct answer

Vertical scaling means moving the same single database instance onto bigger hardware: more vCPUs, more RAM, faster disks. Horizontal scaling means splitting the workload across multiple database instances, either read replicas for read traffic or shards for both reads and writes. Vertical buys time with almost no application changes; horizontal buys a much higher ceiling at the cost of new failure modes and a genuinely harder system to operate. For most teams the right sequence is: get everything reasonable out of a single well tuned instance first, and only take on horizontal scaling once the growth curve shows you will hit the ceiling of the largest instance your vendor sells, or once a single write bottleneck is the actual limiter, not a guess.

How to think about it

Two terms worth pinning down first. ACID (atomicity, consistency, isolation, durability) is the set of guarantees a relational database makes about each transaction. OLTP (online transaction processing) means many small read/write transactions, like placing one order. OLAP (online analytical processing) means fewer, larger queries that scan and aggregate a lot of data, like a nightly revenue report. The vertical-vs-horizontal decision plays out differently for each, covered below.

Worked example

Start with the clearest pair of archetypes. A single-node, ACID-compliant financial ledger, for example a payments settlement system where every debit must match a credit inside one transaction, is the clearest case for staying vertical as long as possible: the correctness property you need, one atomic transaction across arbitrary rows, gets much harder once those rows can live on different nodes. Contrast that with a globally distributed social feed or clickstream store, where reads vastly outnumber writes and individual records rarely need cross-row transactions; sharding by user id is the natural fit from day one. That is also the concrete case for still choosing vertical scaling: any workload whose correctness depends on cross-row transactions over the whole dataset, not just a subset of it, should stay on a single node until there is no other option.

Now a growth scenario. A SaaS billing OLTP database is at 40% CPU on a 16 vCPU instance, and volume is projected to triple in 6 months. First confirm the bottleneck is real capacity, not a missing index or an undersized connection pool, since those get mistaken for "we need to shard" constantly. If the ceiling really is coming, the cost shape matters, not just the size of the number. The script below models unit economics illustratively (the dollar figure is a made-up placeholder for comparing the SHAPE of the two cost curves, not a real vendor price):

unit_cost_per_core = 10          # illustrative $/core-month at the commodity tier
premium_tier_cores = 64          # cores beyond this require premium, fewer-vendor SKUs
premium_multiplier = 2.5

def vertical_cost(total_cores):
    if total_cores <= premium_tier_cores:
        return total_cores * unit_cost_per_core
    commodity_part = premium_tier_cores * unit_cost_per_core
    premium_part = (total_cores - premium_tier_cores) * unit_cost_per_core * premium_multiplier
    return commodity_part + premium_part

def horizontal_cost(total_cores, node_cores=8):
    import math
    n_nodes = math.ceil(total_cores / node_cores)
    coordination_overhead_pct = 0.03 * (n_nodes - 1)
    return n_nodes * node_cores * unit_cost_per_core * (1 + coordination_overhead_pct)

Running it prints this table (real output, this run):

total_cores   vertical_$/mo   horizontal_$/mo  horizontal_nodes
          8              80                80                 1
         32             320               349                 4
         64             640               774                 8
         96            1440              1277                12
        128            2240              1856                16
        256            5440              4941                32

Below the premium tier (64 cores here), vertical is cheaper because horizontal pays a coordination tax with no benefit. Past it, vertical's premium-SKU jump outweighs horizontal's coordination tax, and horizontal wins. The crossover point, not a fixed rule of thumb, is what should drive the decision, and it moves with your own vendor's real pricing tiers.

A gentler-growth variant worth naming: a 5 TB machine-learning training dataset growing 50% a year, with a mix of scheduled batch training runs and interactive analyst queries. A 50%-per-year slope is far gentler than the 3x-in-6-months case above, so a single well-provisioned instance can absorb years of that growth without forcing a decision at all; the batch-vs-interactive mix affects maintenance-window planning (batch tolerates a pause, interactive access does not), not the scale decision itself.

For OLAP workloads specifically, horizontal has an extra cost most people underweight: a "total revenue across all shards" query has to scatter to every shard, gather the partial results, and merge them, so its cost is bounded by the slowest shard plus a network fan-in step, and that shape is much less predictable than a single node's query planner cost. Horizontal analytics only pays off once a single node's OLAP query latency, not OLTP throughput, is the real ceiling, and that is usually solved by separating the analytical workload out to its own store rather than sharding the OLTP database itself.

Resolving the disagreement, and trade-offs and pitfalls

When a product owner wants to start sharding now and a CEO wants to just buy a bigger managed instance, treat it as a data problem, not an opinion problem. Ask for three things: the actual growth trajectory (current queries per second, QPS, and storage plus the last two quarters' real trend, not a guess), which resource is closest to its ceiling today and how far that is from the largest instance the vendor offers, and whether the write path has one genuine hot-table bottleneck sharding would relieve, versus a mostly-read workload that a replica or cache would fix more cheaply. Commit to a number from that data, for example "we are at 45% of the largest available instance's ceiling and growing 15% a month, giving roughly five to six months before a resize stops being an option." A migration plan that includes rollback: build the router or shard layer behind a feature flag, dual-write to the old and new topology during cutover, keep the pre-migration instance caught up as a fallback for a defined rollback window, and only decommission it after the new topology has run through at least one full peak cycle without incident.

The most common mistake is paying the operational complexity tax of horizontal scaling years before the ceiling actually required it. The second is assuming vertical scaling is free forever: vendors sell a finite, discrete set of instance sizes, so there is always a last resize available. The third is easy to miss entirely: as a single instance's data grows, backup and restore duration grow with it, and that is a second capacity ceiling, your recovery time objective, independent of CPU or input/output operations per second (IOPS, how many read or write operations the storage layer can do each second).

Direct answer

Do not decide this purely from a fixed fragmentation percentage. The widely repeated rule of thumb (reorganize between 5% and 30% average fragmentation, rebuild above 30%) is a reasonable starting filter, but Microsoft's own current guidance for SQL Server explicitly warns against relying on fixed thresholds alone, and points out that a lot of the performance improvement people attribute to a rebuild actually comes from the full statistics refresh a rebuild does as a side effect, not from the physical defragmentation itself. For a 500 GB online transaction processing (OLTP) table under continuous load, the practical default is REORGANIZE (always a fully online, low-impact operation) and REBUILD is reserved for when page density (a page is the fixed-size block of storage, 8 KB by default, that a table or index is physically built from; page density measures how full each page actually is, a different and often more consequential number than fragmentation) is materially low, when the table needs a genuine full-scan statistics refresh, or when fragmentation keeps returning quickly after a reorganize, which is itself a signal about fillfactor, not about the maintenance method.

Structured elaboration

Two different numbers get conflated as "fragmentation"

SQL Server's sys.dm_db_index_physical_stats reports both avg_fragmentation_in_percent (logical fragmentation: how out-of-order the leaf pages are relative to the index's key order, which mainly hurts range and full scans) and avg_page_space_used_in_percent (page density: how full each page actually is, which affects every kind of read because a less-full page means more I/O and less usable buffer-pool cache (the in-memory cache of recently used pages that lets the database skip a disk read on repeat access) per byte of real data). A table can have low logical fragmentation and still perform poorly because of low page density, or vice versa; treating "fragmentation" as one number hides this.

REORGANIZE versus REBUILD

The statistics angle, and why it changes the decision

Microsoft's current guidance is explicit that customers "often incorrectly attribute" a post-rebuild performance improvement to reduced fragmentation, when the real cause was the full-scan statistics update that happened alongside it. The query optimizer's plan choices depend on statistics currency and sampling quality, not on physical page order; a rebuild refreshes both at once, which is why it can look like a fragmentation fix when it was really a statistics fix achieved at far higher cost. The stated recommendation: if rebuilding an index seems to help, try replacing it with a plain UPDATE STATISTICS ... WITH FULLSCAN next time; the resource cost of updating statistics is minor and the operation typically finishes in minutes, versus hours for a rebuild on a large table.

Where fillfactor fits in

Fillfactor controls how full a page is left when it is built (a fillfactor of 90 leaves 10% of each page empty). Both REORGANIZE and REBUILD compact pages back to the index's configured fillfactor. A lower fillfactor leaves headroom for future row insertions without immediately triggering a page split (the event that causes fragmentation in the first place on a table with a non-sequential key), at the ongoing cost of more pages, more I/O, and less usable buffer-pool cache per byte of real data, every single day, not just during maintenance. Current Microsoft guidance is not to set fillfactor away from 100 by default; do it only where there is actual evidence of a high page-split rate, most commonly an index whose leading column is a non-sequential value like a randomly generated identifier. For this scenario, if fragmentation keeps climbing back to 30% shortly after a reorganize, that recurrence is the evidence to look for before lowering fillfactor, not something a maintenance schedule alone can fix.

A maintenance approach that minimizes user impact on 500 GB

Default to REORGANIZE on a recurring cadence driven by measured fragmentation and page density, not a fixed calendar. It is always online, so it does not need a maintenance window. Reserve REBUILD (with ONLINE = ON) for cases where reorganize is not enough, and if the table is partitioned, rebuild or reorganize one partition at a time rather than the whole 500 GB at once, to bound both the resource footprint and the disk headroom needed for any given operation. Before running any REBUILD, confirm there is enough free disk space for roughly a second copy of the index; on a table this size that is a real, checkable constraint, not a detail to skip.

Worked example

PostgreSQL does not expose an avg_fragmentation_in_percent DMV (Dynamic Management View, SQL Server's term for a system view exposing live internal engine statistics), but its pgstattuple and pgstatindex extensions measure the same underlying ideas (dead space in the table, and leaf-page density in a B-tree index). The following was executed against a real PostgreSQL 16 instance to show what "the index still looks full of garbage until you actually rebuild it" looks like in practice, not just claimed:

CREATE EXTENSION pgstattuple;
CREATE TABLE frag_demo (id bigint PRIMARY KEY, val text) WITH (fillfactor = 90);
INSERT INTO frag_demo SELECT g, repeat('a', 100) FROM generate_series(1, 300000) g;
DELETE FROM frag_demo WHERE id % 3 = 0;              -- delete a third of the rows
UPDATE frag_demo SET val = repeat('b', 100) WHERE id % 2 = 0;  -- update half

SELECT * FROM pgstattuple('frag_demo');
SELECT * FROM pgstatindex('frag_demo_pkey');

Output (executed), before any cleanup:

 dead_tuple_percent | free_percent
---------------------+--------------
               46.21 |          2.38

 avg_leaf_density | leaf_pages
-------------------+------------
             96.36 |        820

The index's avg_leaf_density looks high here (96.36%) but that number is misleading: those leaf pages are still full of index entries pointing at now-dead heap rows. Running an ordinary VACUUM reclaims the dead heap space for reuse, but it also removes the corresponding dead entries from the index leaf pages without compacting them, which actually makes the reported density worse, not better:

VACUUM (VERBOSE) frag_demo;
SELECT * FROM pgstattuple('frag_demo');
SELECT * FROM pgstatindex('frag_demo_pkey');

Output (executed):

 dead_tuple_percent | free_percent
---------------------+--------------
                0.00 |         49.63

 avg_leaf_density | leaf_pages
-------------------+------------
             60.13 |        820

Only an actual rebuild (REINDEX, PostgreSQL's equivalent of ALTER INDEX ... REBUILD) repacks the pages:

REINDEX INDEX CONCURRENTLY frag_demo_pkey;
SELECT * FROM pgstatindex('frag_demo_pkey');

Output (executed):

 avg_leaf_density | leaf_pages
-------------------+------------
             90.00 |        547

before, after = 6_758_400, 4_513_792  # index size in bytes, before and after REINDEX
print(f"index size dropped {(1 - after/before)*100:.1f}%, from {before/1024/1024:.2f} MiB to {after/1024/1024:.2f} MiB")
print(f"leaf pages: 820 -> 547 (density is back to the table's own fillfactor, 90%)")

Output (executed):

index size dropped 33.2%, from 6.45 MiB to 4.30 MiB
leaf pages: 820 -> 547 (density is back to the table's own fillfactor, 90%)

REINDEX CONCURRENTLY (available since PostgreSQL 12) is PostgreSQL's non-blocking equivalent of an online rebuild: it builds a new index alongside the old one and swaps them, so it needs roughly double the index's space temporarily, mirroring SQL Server's own space requirement during an online rebuild, and it cannot be run inside an explicit transaction block. For a full table rewrite rather than just an index, the pg_repack extension does the same "build a shadow copy, then swap" trick for the whole table without holding a long exclusive lock, which is PostgreSQL's rough analog to a SQL Server online table rebuild.

For the SQL Server side of this scenario specifically, here is reference syntax for the ONLINE rebuild and default-preferred reorganize (documented, not executed here, since this environment has no SQL Server instance to run it against):

-- default lever: always online, low impact
ALTER INDEX PK_Orders ON dbo.Orders REORGANIZE;

-- reserved for when reorganize isn't enough, or a full stats refresh is wanted anyway
ALTER INDEX PK_Orders ON dbo.Orders
REBUILD WITH (ONLINE = ON, FILLFACTOR = 90, SORT_IN_TEMPDB = ON);

-- the diagnostic query behind the decision. page_count isn't filtered to a specific
-- vendor-documented cutoff here; skip whatever indexes are small enough on your table
-- that mixed-extent allocation and sampling noise make their reported numbers unreliable.
SELECT i.name, ips.avg_fragmentation_in_percent, ips.avg_page_space_used_in_percent, ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.Orders'), NULL, NULL, 'SAMPLED') ips
JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
ORDER BY ips.avg_fragmentation_in_percent DESC;

Trade-offs & pitfalls

• Don't schedule blanket rebuilds on a fixed calendar. Correlate maintenance with measured query performance (SQL Server's Query Store A/B comparison, or a before/after pgstatindex reading as shown above) rather than assuming a rebuild helped just because it ran.
• REBUILD's roughly 2x space requirement is a real operational risk on a 500 GB table, not a footnote; confirm headroom before running one, and prefer partition-level rebuilds over whole-table rebuilds if the table is partitioned.
• A reorganize can fail on a nearly-full data file or filegroup, because it can only allocate its temporary work pages within the same file, not elsewhere in the filegroup, even when the filegroup as a whole has free space.
• Lowering fillfactor "just in case" is a permanent, ongoing cost, not a one-time maintenance decision; only do it where the evidence (recurring fragmentation shortly after a reorganize, a non-sequential leading key with a high page-split rate) actually supports it.
• Fragmentation numbers on very small indexes are frequently not meaningful, and maintaining them rarely reduces the reported fragmentation regardless of how it looks, so it is not worth the resource cost of including them in a maintenance pass.
• Plain VACUUM never fixes index bloat (extra, wasted space left inside an index by updates and deletes, making it physically larger than the live data needs and slower to scan) by itself in PostgreSQL. It reclaims heap space for reuse and removes dead index entries, but as the numbers above show, it can leave the index itself less dense, not more; a genuinely bloated index needs a REINDEX, not just a VACUUM.