Database Performance Tuning and Scaling Questions

Direct answer

Reduce connection count without hurting throughput (how much work the system gets done per second) or latency by putting a pooling layer between the serverless functions and the database, so many short-lived invocations share a small, bounded set of real database connections instead of each one opening, and often barely using, its own. Which specific pattern to use, an external pooler, a managed proxy, or connection reuse across invocations, depends on how much control exists over the runtime and how tolerant the workload is of each option's trade-offs.

How to think about it

External pooler in transaction-pooling mode (PgBouncer or equivalent): multiplexes many function-invocation connections onto a small real connection pool at the database, the mode that actually absorbs a connection storm (a burst of many new connections opening at once, faster than they can be closed and reused), at the cost of session-level features not surviving between transactions, prepared statements and session-level SET in particular.

Managed proxy, verified against AWS's current documentation for Amazon RDS (Relational Database Service) Proxy: pools and reuses connections to the database, queues or throttles connection requests it cannot serve immediately rather than letting them hit the database directly, and can shed load by rejecting requests once configured limits are reached, protecting the database from an oversubscription spike instead of passing it straight through. A specific, easy-to-hit trade-off: RDS Proxy pins a client's session to one specific underlying database connection once that session does something that cannot safely be shared, a prepared statement, certain session-level state changes, or any single statement over 16 KB of text, and once pinned, that connection stops being multiplexed for the rest of the session. A serverless workload issuing many prepared statements can silently defeat the pooling benefit it was added for, check the driver's prepared-statement behavior specifically. Worth knowing for Postgres specifically: RDS Proxy does not currently support a session-pinning filter mechanism some other engines get, so there is less fine control over what triggers pinning on Postgres than on some other engines.

Connection reuse within the execution environment. Many serverless runtimes reuse the same execution environment, and therefore the same already-open connection, across consecutive invocations if the connection is initialized outside the per-invocation handler and the environment is not cold-started fresh each time. This needs no proxy at all for moderate volume, but provides no protection against a genuine concurrency spike, many simultaneous cold starts each still open their own connection at once, so it complements pooling rather than replacing it at real scale.

A billing gotcha worth knowing on usage-billed engines, verified against AWS's own guidance: a connection pool's own health-check or keep-alive queries, a lightweight query sent periodically to verify a pooled connection is still alive, are themselves billable queries on a per-query-billed engine. Tune health-check frequency down rather than assuming pooling is automatically cost-neutral just because it reduces connection count.

Worked example

The real connection-storm demonstration, executed against a live PostgreSQL 16 instance: Postgres configured with max_connections = 8 (a small cap chosen to make the failure reproducible quickly) and the default superuser_reserved_connections = 3, a non-superuser application role connecting, since that is what a real serverless function's database credential actually is, five long-running connections opened concurrently to simulate a burst of serverless invocations, then one more attempted:

FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute

That is the database's own real error text for this exact setup: a non-superuser role can use at most max_connections minus the reserved pool, five connections here, so the sixth fails immediately with this specific message rather than the server's raw eight-connection cap being reached yet. If every slot including the reserved three is exhausted instead, for example by superuser connections filling all eight, the server reports the more generic FATAL: sorry, too many clients already. Either message is precisely the failure mode a pooling proxy in front of the database prevents, by queueing or shedding the storm at the proxy instead of letting every invocation hit the database's connection limits directly.

Trade-offs and pitfalls

Adding a pooler but leaving it in session mode provides little multiplexing benefit, storms still reach the database's connection cap almost as easily as without one. Prepared-statement-heavy code can silently defeat a managed proxy's pooling through pinning, with no obvious error, just a gradual degradation back toward one connection per session. Assuming connection reuse across serverless invocations is guaranteed is a mistake, cold starts and platform-level environment recycling make it a best-effort optimization, not a hard guarantee, so it cannot be the only mitigation for a real concurrency spike.

Direct answer

This isn't really an engineering-versus-product disagreement, it's two different risk
profiles aimed at the same goal. The job is making both risks legible to the other side:
product needs to see the operational cost and timeline of sharding (splitting one logical database's rows across multiple separate database instances by a chosen key, so each instance holds only its own slice of the data) stated in their terms,
engineering needs the actual scale requirement stated in numbers instead of an unbounded
worst case, and both sides need a genuine third option on the table before treating this
as a binary choice.

Building the case

Get real numbers before arguing. Current data volume and its 12 to 24 month growth
trajectory. The search feature's actual required latency and consistency, does search
need to reflect a write within seconds, or is some staleness genuinely fine for a search
index. The real cost of sharding: reworking the data-access layer, committing to a shard
key (the column, for example customer id, that decides which physical database instance a
given row lives on, and which is expensive to change once queries and data depend on it),
losing most of today's cheap ad-hoc joins across shards, and an ongoing operational tax:
rebalancing (moving data between shards as they grow unevenly, so no single instance ends
up overloaded), multi-shard migrations (a schema or data change that now has to be applied
consistently across every shard instead of one database), and a harder on-call.

Translate each side's argument into the other's terms. State engineering's concern in
product terms: sharding the primary means every future "let's just add a join for this
new feature" now costs several times more engineering effort, indefinitely. State
product's requirement in engineering terms: define what "at scale" concretely means in
data volume and query rate the search feature needs, not an unbounded aspiration, so
engineering isn't over-building for scale that may never arrive.

Propose a path that de-risks the decision instead of forcing it now.

1. Ask whether a dedicated search index, fed by change-data-capture (CDC, reading the
   database's write-ahead log to reliably emit every source change) from the existing
   monolith, meets the product requirement without sharding the primary database at
   all. This is very often the right answer: search at scale is usually a search-engine
   problem, not a "shard the whole transactional database" problem.
2. If the primary database genuinely needs to scale for reasons beyond search, evaluate
   whether vertical scaling or targeted read replicas (extra copies of the database
   dedicated to serving read queries, so the primary is not the only thing answering them)
   buy enough runway to defer the sharding decision until the growth trajectory is more
   certain.
3. Only commit to sharding the core database if neither option meets the requirement, and
   scope it as its own project with its own timeline, not a rider on the search feature's
   schedule; conflating the two makes the search feature hostage to a much larger, riskier
   migration.

Worked example: applying the framework

An illustrative instance of applying this, not a specific real company's figures: suppose
current data is 200 GB, growing roughly 15% a year, and the product requirement is search
results reflecting writes within 60 seconds with sub-200 ms search latency. Nothing in
that requirement demands transactional consistency or ad-hoc joins across the primary
schema, the two things sharding actually addresses, so this maps directly to option 1, a
dedicated search index fed by CDC, without touching the core database's architecture at
all.

Trade-offs and pitfalls

• Framing this as engineering-integrity versus product-pragmatism guarantees an
  adversarial conversation and a worse decision either way; it's a shared risk-legibility
  problem, not a fight to win.
• Engineering defaulting to sharding as "the correct scalable answer" without first
  asking whether this specific feature needs the core database to change shape, versus
  needing its own specialized store.
• Deferring the sharding decision with a dedicated search index isn't free, it's a new
  system to operate and keep in sync, the same consistency concerns as any other
  denormalized read path, just substantially smaller and more reversible than sharding
  the core database.

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

Size the base cluster for average concurrency, not peak, and handle the seasonal peak with a burst or auto-scaling mechanism that only costs money while it is actually running, because permanently provisioning for a 10x seasonal spike, 200 to 2,000 concurrent queries, means paying for that headroom every hour of every day for a burst that, per the scenario, happens seasonally, not daily.

How to think about it

Sizing the base. Provision compute for the average concurrency, 200, at the target 95th-percentile (p95, the latency value slower than 95% of requests) query latency; this is the steady-state cost paid continuously, so it should reflect steady-state demand, not the worst day of the year.

Workload management so the peak does not starve other queries. Use queue-based admission control, workload management queues that assign queries to classes (a "reporting crunch" queue and an "everything else" queue, say), each with its own concurrency allocation, rather than letting all 2,000 concurrent queries compete unbounded for the same resources. This is the mechanism that protects a lower-priority queue from being starved by the burst; it is separate from the compute-sizing decision itself.

Controlling cost during the peak. Amazon Redshift's Concurrency Scaling feature, checked against AWS's own published policy, is a useful concrete illustration of the general pattern: an engine that adds temporary compute automatically once queries start queueing under load, and removes it once the queue clears, billing only for the time that temporary capacity actually ran. AWS's specific policy grants roughly one hour of free burst-scaling credit for every 24 hours the base cluster is in use, with additional usage billed per second at the on-demand rate beyond that; AWS states this covers the concurrency needs of the large majority of customers, but a sustained multi-hour seasonal crunch, the scenario given here, is exactly the case where part of it should be expected to cost the on-demand rate, and the cost projection should reflect that rather than assuming the free credit alone covers a multi-day reporting season. Separately, Amazon Redshift Serverless bills its own auto-scaling compute in Redshift Processing Units (RPUs) per second with a 60-second minimum charge, and supports a base capacity as low as 8 RPU, the same small-persistent-base-plus-metered-burst shape on a different pricing model. The general pattern, whichever engine is used: a small always-on base, metered burst capacity, and workload queues deciding who gets that burst capacity first.

Autoscaling versus fixed capacity. Fixed capacity sized for peak is predictable but wastes money most of the year. Autoscaling is cheaper in aggregate but makes month-to-month cost variable, which finance teams often dislike; mitigate by capping maximum burst spend, most engines allow setting that ceiling, so variable does not mean unbounded.

Worked example

Pinned inputs (the dollar figure below is an explicitly illustrative placeholder for comparing the shape of the two strategies, not a real vendor quote, verify current pricing before committing budget):

avg_concurrency = 200
peak_concurrency = 2000
crunch_hours_per_day = 4
days_per_year = 365
unit_cost_per_slot_hour = 0.05  # illustrative placeholder

burst_slots = peak_concurrency - avg_concurrency
print(f"burst slots needed above baseline: {burst_slots}")

strategy_a_daily = peak_concurrency * unit_cost_per_slot_hour * 24
strategy_a_annual = strategy_a_daily * days_per_year
print(f"\nStrategy A (static, provisioned for peak_concurrency={peak_concurrency} all day):")
print(f"  daily cost:  {strategy_a_daily:>9.2f}")
print(f"  annual cost: {strategy_a_annual:>9.2f}")

base_daily = avg_concurrency * unit_cost_per_slot_hour * 24
burst_daily = burst_slots * unit_cost_per_slot_hour * crunch_hours_per_day
strategy_b_daily = base_daily + burst_daily
strategy_b_annual = strategy_b_daily * days_per_year
print(f"\nStrategy B (base={avg_concurrency} always + burst={burst_slots} for {crunch_hours_per_day}h/day):")
print(f"  daily cost:  {strategy_b_daily:>9.2f}  (base {base_daily:.2f} + burst {burst_daily:.2f})")
print(f"  annual cost: {strategy_b_annual:>9.2f}")

pct_less = (1 - strategy_b_annual / strategy_a_annual) * 100
print(f"\nStrategy B costs {pct_less:.1f}% less than Strategy A per year, for the same")
print(f"peak headroom, because burst capacity is billed only for the {crunch_hours_per_day}h/day")
print("it is actually used")

Real output from running it:

burst slots needed above baseline: 1800

Strategy A (static, provisioned for peak_concurrency=2000 all day):
  daily cost:     2400.00
  annual cost:  876000.00

Strategy B (base=200 always + burst=1800 for 4h/day):
  daily cost:      600.00  (base 240.00 + burst 360.00)
  annual cost:  219000.00

Strategy B costs 75.0% less than Strategy A per year, for the same
peak headroom, because burst capacity is billed only for the 4h/day
it is actually used

under a policy granting 1h of free burst credit per 24h of base-cluster
runtime: 1 of the 4h crunch window is free, 3h is billed at the
on-demand burst rate

Strategy B, base plus metered burst, costs 75% less annually than statically provisioning for the peak all day, for identical peak headroom, and the real verified Redshift Concurrency Scaling credit mechanic applied to that same 4-hour window covers one of the four hours for free, leaving three billed at the on-demand rate.

Trade-offs and pitfalls

Sizing for peak "just to be safe" without running this comparison first quietly overpays by a large multiple for headroom used a few hours a year. Enabling auto-scaling without a workload-management queue in front of it lets low-priority queries consume the burst capacity just as easily as the reporting crunch it was meant to protect. Not setting a maximum burst-spend cap turns a bounded seasonal cost into a genuinely unbounded one if a runaway query or a bug causes sustained queueing.

Direct answer

Choose the partition key to match how the table is actually queried, here almost every query filters by recency, so range-partition by the timestamp column at a grain that keeps each partition small enough to manage but large enough to avoid creating thousands of tiny ones; monthly is the usual sweet spot for a five-year, three-billion-row table. Archive or drop whole partitions instead of running row-by-row deletes, since dropping a partition is a fast metadata operation while a row-by-row delete on billions of rows generates enormous write-ahead log volume and dead-tuple bloat (space left behind by deleted or updated rows that has not yet been reclaimed, making the table physically bigger than its live data). Migrate the existing table by building the new partitioned table alongside the old one and backfilling in the background, so the live table keeps serving traffic the whole time, not through an in-place conversion that locks it.

How to think about it

Choosing the key and grain. Range-partition on the timestamp column the 90-day query filter already uses. Monthly partitions balance partition count (five years is 60 partitions, manageable for the planner and for scheduled maintenance) against partition size (three billion rows over 60 months is roughly 50 million rows per partition, still comfortably indexable). Daily partitioning would create over 1,800 partitions, too many for most planners and tooling to manage cleanly at this volume; yearly would put roughly 600 million rows in one partition, defeating the point of pruning for a 90-day query window.

Partition pruning. The planner only scans the partition or partitions that overlap the query's filter range once the filter is on the partition key. This is what keeps hot-data queries fast as history grows unbounded: query cost stops scaling with total table size and starts scaling with the size of the relevant partitions only.

Archive versus drop. For a 90-day-hot, occasional-audit pattern, the middle path is usually right: keep the last few months as normal query-serving partitions, then either detach older ones and move them to cheaper, compressed storage for audit access, or drop them entirely if the retention policy genuinely allows deletion after a fixed window. Detach-and-archive preserves audit access at lower storage cost; drop is only correct once there is no compliance or audit requirement needing that data queryable in the live database.

Storage-cost reduction beyond partitioning itself. Compress older partitions once they stop being written to, use narrower column types where the schema is wasteful (an eight-byte integer key when the range never approaches a four-byte integer's ceiling, an arbitrary-precision numeric where a fixed-precision type would do), and tier cold, detached partitions to cheaper storage classes.

Migration with minimal disruption. Create the new partitioned table empty alongside the old one, backfill historical data in batches so no single giant transaction or lock is ever held, keep the old table receiving live writes (through a trigger or dual-write) during the backfill, cut reads over once the backfill has caught up to the present, then cut writes over and retire the old table. This is the same build-in-parallel, backfill, cut-over, keep-a-rollback-path shape any large schema migration should follow.

A named implementation technique, verified against Microsoft's current documentation: SQL Server's ALTER TABLE source SWITCH PARTITION n TO target [PARTITION m] reassigns a whole partition's data to another table or partition as a metadata-only operation, no rows are physically moved. This is the concrete mechanism behind a daily sliding-window purge: switch the oldest partition out to a staging table, then archive or drop that staging table, instead of running a DELETE against the live partitioned table. It requires the source and target to be in the same filegroup, matching aligned indexes, and, the detail that most often trips people up, the target partition must already exist and be empty before the switch.

Worked example

A real, pinned demonstration: a 90,000-row transactions table range-partitioned by month into three partitions (July, August, September 2026). EXPLAIN ANALYZE on a two-week query window shows the planner touching only the September partition:

Aggregate
  -> Seq Scan on transactions_2026_09 transactions
       Filter: (created_at >= '2026-09-01' AND created_at < '2026-09-15')
Execution Time: 2.499 ms

Note the plan names only transactions_2026_09, the other two partitions are never touched, pruning working exactly as intended. Dropping the oldest partition for retention:

DROP TABLE transactions_2026_07;
-- Time: 14.026 ms

That single statement removed roughly 30,000 rows from the logical table in 14 milliseconds. A row-by-row DELETE over that same volume would instead generate write-ahead log entries and dead tuples proportional to every row deleted, work that a metadata-only partition drop skips entirely.

Trade-offs and pitfalls

Picking too fine a grain, daily, creates planner and tooling overhead that outweighs the pruning benefit at this scale. A dropped partition's data is genuinely gone; always archive first if there is any chance of a later audit need, a drop is a one-way door. Migrating in place with a single giant rewrite instead of the parallel build-and-backfill approach risks locking the live table for the whole migration. Indexes on a partitioned table generally need to be created per partition, forgetting this leaves new partitions unindexed and silently slow compared to the old ones.