⚡ Promptolis Original · Coding & Development
📐 Technical Spec Writer
Writes the 5-page design doc that gets approved — covering context, decisions, alternatives, risks, and rollout in the format senior engineers actually want to read.
Why this is epic
Most 'design docs' are either 30-page novels nobody reads or 1-page stubs that don't answer the real questions. This Original produces the 5-page format that senior engineers actually want.
Covers the 4 sections that determine approval (context + problem, solution + alternatives considered, risks + mitigations, rollout + rollback) — skipping any of these kills the doc.
Produces the 'devil's advocate' section that pre-empts the 5 most common objections so the review meeting is about nuance, not about basics.
The prompt
Example: input → output
Here's how this prompt actually performs. Real input below, real output from Claude Opus 4.
<project-summary>Migrate primary database from PostgreSQL 11 to PostgreSQL 15 to access newer features (better indexing, JSON path queries, partitioning improvements).</project-summary> <problem-statement>PG11 EOL in 6 months. Query performance suffering on 50M-row tables. Some new queries require PG12+ features.</problem-statement> <proposed-solution>Use logical replication to set up PG15 replica, sync, test, failover. Blue/green deployment pattern.</proposed-solution> <alternatives-considered>In-place upgrade with pg_upgrade. Dump-and-restore migration. Keep PG11 and work around limitations.</alternatives-considered> <scope>In: primary PG migration. Out: any schema changes, any sharding decisions, app code changes.</scope> <timeline>8 weeks.</timeline> <reviewer-audience>Tech lead + infra lead + CTO.</reviewer-audience>
Technical Spec: PostgreSQL 11 → 15 Migration via Blue/Green Replication
TL;DR
Migrate primary database from PostgreSQL 11 (EOL in 6 months) to PostgreSQL 15 using logical replication + blue/green cutover. Estimated 8 weeks of effort with < 5 min production downtime during final cutover. Unlocks 2x query performance on 50M-row tables and enables upcoming features requiring PG12+.
Context + Problem
Current state:
- Primary DB: PostgreSQL 11, deployed 2020.
- Size: ~500GB total, largest table 50M rows.
- PG11 reaches end-of-life November 2025 — no more security patches.
- Two open tickets blocked on PG12+ features (JSON path, partition pruning).
- Recent query performance regression on the 50M-row table due to planner limitations in PG11.
Why now:
- EOL deadline creates hard constraint.
- Performance pain is active — 2 incidents in last quarter.
- Later = more risk. Later versions drop faster than earlier ones.
Proposed Solution
Blue/green migration with logical replication:
1. Setup (Week 1-2): Provision PG15 instance sized ~1.5x current production. Configure logical replication from PG11 primary.
2. Sync (Week 2-4): Replicate all data. Monitor replication lag. Full sync + keep lag <1 second during steady state.
3. Verify (Week 4-6): Run read-only queries against PG15 to verify data integrity. Dual-write test environment. Performance benchmarks.
4. App compat (Week 6-7): Validate all ORM queries + raw SQL against PG15. Any breaking changes noted.
5. Cutover (Week 8): Off-hours cutover. Flip app connection string, verify, monitor. Target <5 min downtime for connection flip.
6. Retire (Week 8, post-validation): Keep PG11 as read-only rollback-ready for 2 weeks, then decommission.
Key design decisions:
- Logical, not physical replication. Allows cross-version (PG11→PG15). Physical requires same version.
- Same region, different instance. Avoids network-latency considerations during migration.
- No schema changes during migration. Out of scope per intent. Reduces variables.
- Connection string via env var, not hardcoded. Allows rapid rollback (flip env var).
Alternatives Considered
Alt 1: In-place pg_upgrade.
- Pro: simpler, one-step.
- Con: ~30 min downtime (unacceptable for our SLA). No rollback without full restore. Version jump >3 versions carries upgrade-specific risks.
- Rejected: downtime + rollback story worse than blue/green.
Alt 2: Dump-and-restore.
- Pro: well-understood.
- Con: 500GB dump = ~4-6 hours downtime. Not acceptable.
- Rejected: downtime.
Alt 3: Stay on PG11 indefinitely.
- Pro: no migration work.
- Con: EOL = security risk, performance issues continue, blocked on 2 features.
- Rejected: EOL is a hard constraint.
Risks + Mitigations
| Risk | Probability | Impact | Mitigation |
|---|---|---|---|
| Replication lag grows during cutover | Medium | High | Pre-cutover checklist: lag <1s verified before flip |
| App incompatibility with PG15 | Medium | Medium | Full test suite + manual query verification in Week 6-7 |
| Silent data divergence during sync | Low | High | Row-count + checksum validation in Week 4-6 |
| Failed cutover requires rollback | Low | High | PG11 kept read-available 2 weeks; connection-string flip |
| Performance regression on PG15 | Low | Medium | Benchmark key queries pre-cutover; query plan comparison |
Rollout + Rollback Plan
Rollout (Week 8, Saturday 2am local):
1. Confirm replication lag < 1s.
2. Set app to read-only mode.
3. Wait 30 seconds for in-flight writes to complete.
4. Stop replication.
5. Flip app connection string to PG15.
6. Verify reads + writes on PG15.
7. Enable full traffic.
8. Total: ~5-10 min.
Rollback (if Week 8 cutover fails):
1. Flip app connection string back to PG11 (env var update + restart).
2. PG11 has been kept up-to-the-cutover-second; no data loss.
3. Debug the PG15 issue; retry cutover next maintenance window.
4. Rollback total: ~2-3 min.
Post-cutover monitoring (48 hours):
- Error rate parity check (PG15 vs. PG11 historical).
- P50/P95/P99 latency comparison.
- Connection pool health.
- Any specific query plan regressions flagged.
Success Metrics
- Downtime: <5 min measured. (Target met if green.)
- Data integrity: 0 row discrepancies in pre-cutover checksum validation.
- Query performance: P95 latency on key queries (5 identified) at least as fast as PG11. Target: 30%+ improvement on partition-eligible queries.
- Incident rate: 0 migration-induced incidents in the 2 weeks post-cutover.
Devil's Advocate Section
Objection 1: 'Why not wait until summer when we're less busy?'
→ EOL deadline is November. Doing it in Q3/Q4 increases risk of EOL overlap with holiday freeze. Now is actually the best window.
Objection 2: 'Why migrate to 15 instead of 14 (more mature)?'
→ 15 adds query parallelism improvements and better partition pruning that solve our actual pain. 14 gets us off EOL but leaves the performance issue. 15 is supported for 5+ more years.
Objection 3: 'Logical replication seems fragile. Have you tested?'
→ Planned extensive Week 4-6 verification with row-count + checksum validation. Also: this pattern has been used at Segment, GitLab, and Heroku with publicly documented success.
Objection 4: 'What if an ORM query breaks silently post-cutover?'
→ Week 6-7 dedicated to this. All queries run through shadow PG15 replica. ORM compatibility layer checked. No silent failures expected.
Objection 5: 'Can we compress the timeline to 6 weeks?'
→ 8 weeks includes 2 weeks of verification that's non-negotiable. Could compress to 6 only by removing the read-replica rollback period. Not recommended.
Open Questions
1. Maintenance window: Saturday 2am local vs. Sunday 4am? Need to confirm with product team on user impact.
2. Connection pooler upgrade? PgBouncer version may need a bump to support PG15 — worth confirming with infra lead.
3. Backup strategy during migration: should we have an extra full backup before starting, or is standard daily backup enough? Infra lead's call.
Timeline
| Week | Milestone |
|---|---|
| 1-2 | PG15 instance provisioned; logical replication configured |
| 2-4 | Full sync + steady-state lag <1s verified |
| 4-6 | Data integrity verification + benchmarks |
| 6-7 | App compatibility testing |
| 8 | Cutover + 48-hr monitoring |
| 8+2 | PG11 decommissioned |
Key Takeaways
- Blue/green replication pattern with <5 min cutover downtime. Only viable approach given SLA + EOL constraints.
- 8-week timeline non-negotiable. 2 weeks of verification prevents silent data issues.
- Rollback story is strong. PG11 kept read-available 2 weeks; connection-string flip enables fast rollback if needed.
Common use cases
- Proposing a new feature / system before building
- Proposing an architectural change
- RFC / Design Review process at FAANG-style companies
- Writing specs for a new hire's first project
- Documenting a decision that will matter in 2 years
- Getting approval for a major refactor or migration
- Coordinating across teams on a shared system
Best AI model for this
Claude Opus 4 or Sonnet 4.5. Technical writing benefits from top-tier for precision.
Pro tips
- 5-10 pages max. Longer = nobody reads. Shorter = not serious.
- Always include 'alternatives considered' section. Without it, readers assume you didn't consider any.
- Write the TL;DR last. It's harder than it looks and it matters most.
- Include specific success metrics. Vague success ('it should be faster') means unclear approval.
- Name the rollback plan. Seniors read this first.
- Share as draft, get feedback, finalize. Iteration beats first-draft brilliance.
Customization tips
- Write the TL;DR LAST. Draft the rest first; the TL;DR writes itself once you know what matters.
- Share as a Google Doc, not a Notion page, unless your team prefers otherwise. Comments drive feedback.
- Ask 2 people for feedback BEFORE formal review. Pre-review cleanup saves meeting time.
- If the doc has no 'alternatives considered,' it will get challenged on that alone. Never skip.
- Keep the 'open questions' section. Explicitly asking for reviewer input invites engagement.
Variants
Feature Spec Mode
For product feature specs. More user-facing, less architectural.
Architectural RFC Mode
For system / architectural changes. Heavier on tradeoffs + risks.
Migration / Refactor Spec
For refactor or migration proposals. Rollout + rollback heavy.
Frequently asked questions
How do I use the Technical Spec Writer prompt?
Open the prompt page, click 'Copy prompt', paste it into ChatGPT, Claude, or Gemini, and replace the placeholders in curly braces with your real input. The prompt is also launchable directly in each model with one click.
Which AI model works best with Technical Spec Writer?
Claude Opus 4 or Sonnet 4.5. Technical writing benefits from top-tier for precision.
Can I customize the Technical Spec Writer prompt for my use case?
Yes — every Promptolis Original is designed to be customized. Key levers: 5-10 pages max. Longer = nobody reads. Shorter = not serious.; Always include 'alternatives considered' section. Without it, readers assume you didn't consider any.
Explore more Originals
Hand-crafted 2026-grade prompts that actually change how you work.
← All Promptolis Originals