Senior Site Reliability Engineer - FAANG Interview Preparation Guide

perf — Use-case: CPU- and kernel-level hotspots. If a backend shows high CPU load or latency spikes, run perf record / perf report on the service to find heavy call stacks, syscalls, or context-switch hotspots. Example: identify a particular libc function or JITed method consuming cycles, then optimize code or tune CPU affinity. Good for offline sampling and generating flamegraphs.

bpftrace / bcc (eBPF) — Use-case: low-overhead, live tracing across kernel and user space. For intermittent, production-sensitive latency issues (can't stop service), write short bpftrace scripts to trace syscalls, scheduling delays, tail-latency stacks, or network skb drops aggregated by PID. Example: measure syscall latencies for requests >95th percentile to find syscall-heavy code paths without heavy overhead.

atop (or pidstat/top) — Use-case: continuous resource accounting with historical context. If resource usage evolves over minutes/hours (memory growth, IO wait, thread counts), atop/pidstat provide per-process CPU, memory, disk I/O, and thread info over time so you can correlate with deployments or traffic surges. Use atop to identify processes causing sustained iowait or page faults.

When to use strace: For a specific PID when you suspect problematic syscalls (blocking open/read/write, excessive futexes). Use sparingly in production due to overhead; apply to short windows or staging to see exact syscall sequences and args.

When to use iostat: When you suspect disk subsystem bottlenecks. iostat shows device-level throughput, IOPS, and %util; use it to determine whether high response times are due to saturated disks vs. application CPU or network issues.

Direct answer

Start with one low-risk, high-visibility pilot rather than a mandate, ship a minimal GitOps loop for it in two to four weeks, and let measured deployment-lead-time and MTTR improvements make the case to the next team instead of a slide deck. The real objections (loss of control, upfront cost, "what if Git is down") get answered by keeping the pilot reversible: manual reconcile (someone approves each change before it's applied) before auto-sync (the operator applies every git change immediately, with no human step), PR review on every change, and a documented rollback path from day one.

Rollout plan

• Kickoff: a one to two hour session to align on the team's actual pain points (manual rollbacks, inconsistent environments, incidents tied to drift) and agree success metrics up front, before any tooling decisions.
• Minimal viable automation (2-4 weeks): pick a low-risk service (non-customer-facing, or a single-node internal API) as the pilot. Stand up a small repo with declarative infra (Terraform, or Helm/Kustomize for Kubernetes) and a GitOps pipeline (Flux or Argo CD) that stores manifests in git with PR-based changes, runs automated plan/lint checks, and deploys on merge with signed commits. Hand the team a one-page runbook and pair with them for the first onboarding session.
• Pilot metrics (baseline and after 4-8 weeks): deployment lead time (code to prod), change failure rate and MTTR, frequency of environment drift incidents, time spent on manual ops per sprint, and number of rollback events plus time to rollback.
• Risk mitigation: start the GitOps operator in read-only, manual-reconcile mode before enabling auto-sync; run it with least-privilege IAM scoped to its namespace; keep automated backups and tested restores for any stateful resource; and keep the rollback procedure to "revert the git commit," nothing more exotic.

Scaling across teams

Build a reusable starter repo and bootstrap script so a new team gets a GitOps repo, CI checks, and RBAC defaults without reinventing them. Run office hours and a documented playbook (patterns for secrets, multi-environment promotion, tagging) instead of a top-down mandate. Offer templated policy-as-code checks and a shared module library so each new team starts from something already proven, and run short, hands-on migration sprints per team, measuring the same baseline metrics each time to replicate the pilot's result rather than assume it generalizes.

Worked example

Situation: a skeptical application team is hesitant to move from manual deployment and ad hoc infra changes to IaC and GitOps because they fear disruption, loss of control, and upfront work.

Action: the team's most-complained-about service (frequent manual rollbacks) is picked as the pilot, not their most critical one. A small GitOps repo is stood up with read-only reconcile mode, PR-based changes, and a one-hour paired onboarding session. Baseline metrics are captured before the change: deployment lead time, change failure rate, and time spent on manual ops that sprint.

Result: after four weeks in manual-reconcile mode, the team is comfortable enough to flip to auto-sync. The pilot's before/after numbers (not vanity counts of how many GitOps deploys happened, but the same lead-time and MTTR numbers the team already tracked) become the evidence used to onboard the next team, along with direct feedback from the pilot team's engineers about easier peer review and reproducibility.

Addressing common objections

• "Too much upfront work": the minimal viable automation reduces immediate toil and produces a reusable template; the platform/SRE side does the initial scaffold, not the app team.
• "Loss of control, fear of automation mistakes": keep the initial mode manual-reconcile, insist on PR review, and make rollback a single git revert.
• "Security or compliance concerns": integrate secrets management, policy-as-code, and audit logs into the GitOps flow from the pilot, not bolted on later.
• "We'll be blocked if git is down": ensure git/CI redundancy and keep a documented manual runbook as a fallback for genuine emergencies.

Trade-offs & pitfalls

• Picking the wrong pilot (something too central, too complex, or too political) burns the one credibility shot this approach depends on; a service with real stakes but low blast radius is worth the extra time spent choosing it.
• Vanity metrics ("we deployed via GitOps ten times") do not move skeptics. The metrics that land are the ones the team already complains about, measured the same way before and after.
• Automating too fast, meaning full auto-sync from day one, removes the safety valve that makes a skeptical team comfortable. Earn auto-sync by first proving the manual-reconcile version is trustworthy.

Direct answer

Pull doesn't work here: the collector can't open an inbound connection through a corporate firewall with no inbound access, so this comes down to push versus a local relay. Use agent-initiated push, authenticated with mutual TLS, and add a local relay inside networks where many hosts share one restrictive egress path, so the relay makes the one outbound connection to the central collector and the individual hosts only ever talk to something already inside their own network.

Structured elaboration

Recommended architecture: agents push. Where a corporate network has many hosts behind one firewall, deploy a relay inside that network; agents push to the relay over the local network, and the relay makes the single outbound mTLS connection to the central ingest tier. Where no relay is present, agents push directly to a regional ingest endpoint.

Authentication: mutual TLS with short-lived certificates (issued via an internal PKI or an SPIFFE/SPIRE-style workload identity, an open standard that automatically issues short-lived cryptographic identities to services and hosts), not long-lived static tokens. Each agent proves its identity on every connection; the collector or relay validates the certificate chain and maps the identity to a tenant/authorization scope before accepting data.

Service discovery: agents resolve a priority list, local relay first (via internal DNS SRV records), falling back to a regional ingest load balancer if no relay is configured. Relays themselves register with a central control plane over their own mTLS connection so the platform knows which relays are healthy.

Traversing NAT and firewalls in practice: rely on standard outbound HTTPS (443) so no firewall exception is needed beyond what's already open for normal internet-bound traffic; use HTTP/2 or a persistent WebSocket-style multiplexed stream so one connection carries many events instead of opening a new TLS handshake per batch, which also reduces the load a busy proxy has to inspect.

flowchart LR
    H1[Host behind firewall] -- push, mTLS --> R[Local relay]
    H2[Host behind firewall] -- push, mTLS --> R
    R -- single outbound, mTLS --> LB[Regional ingest LB]
    H3[Host, direct egress allowed] -- push, mTLS --> LB
    LB --> Q[(Durable ingest queue)]

Worked example

Sizing one relay for a corporate network segment. Assume $N = 5{,}000$ agents behind one relay, each pushing a batch every 15 seconds, averaging 50KB/batch:

Converting to megabits/sec ($\times 8$ bits, $\div 1000$):

Against an illustrative 1 Gbps relay uplink:

One relay comfortably serves 5,000 agents at this batch rate with 7.5x headroom before the uplink itself becomes the bottleneck, which is the concrete answer to "does one relay per corporate segment suffice or do we need to shard relays" for a segment this size.

Certificate rotation load. With 24-hour certificate lifetimes for those 5,000 agents:

Tightening to 1-hour lifetimes for a higher-security posture:

Going from 24-hour to 1-hour certificate lifetimes is a 24x increase in renewal traffic against the PKI/issuance service, still under 1.4 renewals/sec average for this fleet size, which is the kind of load a normal internal CA handles without needing dedicated scaling, but it's worth stating the number rather than assuming "short-lived certs are free."

Trade-offs & pitfalls

Common wrong turns: defaulting to pull because it feels simpler to reason about ("the collector controls the schedule"), without checking that it's actually infeasible the moment a single host sits behind NAT with no inbound rule, which is the stated constraint here; putting authentication only at the relay and trusting everything on the internal network behind it, which means a compromised host on that network can inject or read telemetry for every other host sharing the relay; and skipping short-lived certificates in favor of long-lived static tokens to avoid the rotation-load question above, which trades a manageable, quantifiable renewal cost for an unbounded blast radius if one token leaks.

Direct answer

As a security architect you rarely have line authority over another team's backlog, so you get findings prioritized by making them cheap to accept and costly to ignore: translate the finding into the other team's own vocabulary (a defect, a customer risk, a compliance control they must attest to) and attach it to a decision they are already about to make, rather than asking them to open a brand-new work item. You lead with a specific, demonstrated risk instead of a policy citation, offer a menu of remediation options at different costs, and use an existing recurring forum, like a design review or architecture council, so the tradeoff is made visible to the team's own stakeholders, not just to you.

Structured elaboration

• Translate, don't mandate: reframe the threat-modeling finding in terms the team already tracks (a customer-facing incident scenario, a compliance control, a defect class QA can reproduce) instead of a generic "security best practice."
• Time it to their planning cycle: bring a written finding before backlog grooming or sprint planning, not after code is merged, so accepting it is a normal prioritization decision instead of a rework request.
• Offer options, not a mandate: propose two or three remediation paths (a quick mitigating control now, a full fix next sprint, an explicit accepted-risk sign-off) so the team's own product owner makes an informed tradeoff instead of feeling overridden.
• Borrow a forum, don't invent one: attach the ask to a ritual the team already respects, like their design review, so it reads as peer-level influence rather than a unilateral security gate.
• Make patterns visible upward: when a team consistently deprioritizes findings, escalate the pattern, not the individual finding, to a shared forum with both engineering and security leadership present, so someone with authority over both sides makes the call.

Worked example (illustrative, adapt to your own experience)

A security architect threat-models a new payments feature two weeks before the product team's sprint planning. Instead of filing a ticket titled "add input validation" into the team's backlog and hoping it gets picked up, they write a one-page finding: the specific attack path, the customer-facing scenario it enables, and three remediation options ranked by effort. They bring it to the team's existing design review, present it alongside the team's own product owner, and let the team choose between a lightweight mitigating control shippable in the current sprint or a fuller fix in the next one. The team picks the lightweight option and schedules the fuller fix on their own board, because the tradeoff was made visible and owned by them, not imposed from outside.

Trade-offs and pitfalls

• Too formal (a mandatory sign-off gate) breeds resentment and workarounds; too informal (a message in passing) gets lost in someone else's priority queue.
• Offering remediation options is powerful but risks a team always choosing the cheapest option indefinitely, so track accepted-risk decisions somewhere durable so a pattern of chronic deferral becomes visible over time.
• Borrowing an existing ritual only works if that ritual has real teeth; if the design review itself gets skipped or ignored, attaching your ask to it just inherits its weakness.

What the interviewer probes next

They typically follow up on how you handle a team that keeps saying "next sprint" indefinitely, whether you would ever reach for a hard gate like a release-blocking scan instead of persuasion, and how this influence model holds up when you are supporting a dozen teams at once instead of just one.

Direct answer
When someone publicly blames a colleague during an active incident, the immediate priority is de-escalating without derailing the response itself: move the personal comment to a private channel quickly, keep the shared incident channel focused on resolving the problem, and address both the blaming behavior and its target separately, afterward, once the incident is stable.

Structured elaboration

• In the moment, prioritize the response, not the conflict. A brief, calm redirect in the shared channel ("let's keep this channel focused on mitigation, happy to discuss root cause separately") is usually enough; a longer confrontation in the middle of an active incident just adds noise and delay to something that's still actively harming users.
• Move the personal conversation private and prompt. Message the person who made the comment directly and briefly, acknowledging the stress of the moment but making clear that public blame isn't how the team operates, even under pressure; this shouldn't wait until after the incident closes.
• Check in with whoever was blamed. A quick, private, supportive message during or immediately after the incident, separate from the group, matters even if it feels like a small gesture; being publicly blamed during a stressful live incident is genuinely uncomfortable and worth acknowledging directly.
• Address it properly afterward, not just in the moment. A brief private conversation with the person who made the comment once things are calm, focused on what was actually happening for them in that moment (panic, feeling exposed, genuine frustration) and reinforcing the expectation clearly but without punitive framing, since this was very likely a stress reaction, not a considered decision.
• Ensure the eventual postmortem stays genuinely blameless despite what happened live. The public comment doesn't get carried into the written postmortem's tone or content; the facilitator should be deliberate about resetting the frame explicitly at the start of that meeting.

Worked example
During a live incident, a junior engineer posts in the shared channel, "this is happening because the PM pushed us to ship without proper testing." The incident commander responds immediately in-channel: "Let's keep this thread on mitigation, I'll follow up on that separately," and continues coordinating the response. Within the hour, they privately message the junior engineer: acknowledging the stress of the moment, but being direct that publicly blaming a specific person, even under pressure, isn't how the team handles incidents, and that there's a structured space (the postmortem) for exactly this kind of concern to be raised constructively. Separately, they check in privately with the PM, who appreciated the acknowledgment. When the postmortem runs a few days later, the facilitator opens by explicitly restating the blameless ground rules, and the underlying concern (a real gap in test coverage before this kind of deploy) does get surfaced and addressed, just through the process designed for it rather than as a live accusation.

Trade-offs and pitfalls
The most common mistake is either ignoring the comment entirely (which signals it's tacitly acceptable) or turning it into a bigger disruption in the moment than the underlying incident already is. A second is failing to follow up afterward at all, assuming the moment passed once the incident resolved, which misses both the coaching opportunity with the person who made the comment and the chance to support the person who was blamed.

First three pieces of information to gather (why each matters)

1. Scope & impact: which services, regions, customer-facing features, and percent of traffic affected.

   • Why: tells you containment boundaries and prioritizes where to focus mitigation (e.g., blocklist vs full rollback).

2. Error symptoms & timelines: dominant error types (502/timeout/latency), recent deploys/config changes, and exact time degradation started.

   • Why: links cause (deploy/config) to effect and speeds root-cause narrowing so containment can be targeted.

3. Current mitigation and owner status: who is already working on it, any mitigations in place (circuit breakers, autoscaling, throttles), and pager overload.

   • Why: avoids duplicated work, surfaces gaps, and enables rapid coordination/hand-offs.

First three actions in the first five minutes (why each matters)

1. Announce a short incident status message (impact summary + “I’m acting as coordinator for now”) in channel and set communication cadence (eg. updates every 5 min).

   • Why: reduces noise, prevents fragmented efforts, and gives stakeholders a predictable update rhythm.

2. Triage and isolate: run quick health checks/dashboards, mute non-actionable alerts, and, if possible, throttle/redirect traffic or engage failover for the most impacted service.

   • Why: containment reduces blast radius and buys time for diagnosis.

3. Assign roles & next steps: name an owner for investigation, one for mitigation, and one for communications/logging; capture actions in the channel.

   • Why: clear responsibility accelerates work, ensures progress is tracked, and preserves audit trail for postmortem.

Prioritizing reliability engineering work against feature requests is not a one-time policy decision, it's a recurring negotiation that works best when it's anchored to an agreed, visible signal rather than relitigated from scratch every sprint. The strongest approach ties the trade-off to a shared reliability target (a service-level objective, or SLO, and the resulting error budget) so that whether reliability work takes priority becomes a mostly mechanical question once that target is trending badly, and a genuine judgment call the rest of the time.

A structure for the recurring decision

1. Anchor to a shared number. When the team has an agreed reliability target and the current state relative to it is visible to both engineering and product, "should we ship this feature or fix reliability" stops being a values argument and becomes "are we inside or outside our agreed margin."
2. Separate toil from real engineering investment. Toil (repetitive manual operational work) competes with both features and deeper reliability engineering; automating it away is rarely controversial and should usually win by default, since it pays back quickly and frees capacity for everything else.
3. Make the cost of deferral visible. Reliability work deferred long enough compounds (a known issue causes repeat incidents, each one costing far more engineering time than the original fix would have); surface that compounding cost in terms product stakeholders can weigh, not just "we should be doing this."
4. Reserve explicit capacity, don't just compete for it. Many teams that get this right allocate a standing percentage of each cycle to reliability and toil work up front, so it isn't re-litigated feature by feature.

Worked example (a concrete trade-off)

A team's checkout API is inside its reliability target, but a recurring, low-severity issue (an intermittent third-party payment-processor timeout) has caused three minor incidents in the past quarter, none severe enough to trigger the error-budget policy on their own. Meanwhile product wants a new checkout feature shipped this sprint. The negotiation: the team quantifies that the recurring timeout has already cost roughly two engineer-days of incident response this quarter and is trending worse, proposes shipping the feature this sprint as planned but committing the next sprint's reliability-work allocation specifically to that fix rather than leaving it in the general backlog, and gets product's buy-in by showing the incident-time cost directly rather than arguing reliability work is important in the abstract.

Trade-offs and pitfalls

Treating every reliability request as equally urgent erodes trust with product stakeholders, who correctly notice when "everything is a fire" and start discounting reliability asks across the board; the credibility of a hard stop (this must happen now) depends on it being genuinely rare. The opposite failure, always deferring reliability work in favor of the next feature, quietly accumulates risk until an incident forces the trade-off on the team's behalf, usually at a worse time and a higher cost than if it had been scheduled deliberately. The healthiest teams treat this as a standing, visible allocation decision rather than a fight that has to be re-won every time.

Overview: Six-month structured mentorship with biweekly 1:1s + weekly asynchronous check-ins. Focus areas: core technical SRE skills, incident leadership, documentation & runbooks, and career/professional growth.

Cadence:

• 1:1 (50 min) every two weeks — progress, blockers, feedback
• Weekly 15-min async status (Slack/email) — tasks completed
• Shadowing during at least one on-call rotation per month

Milestones & Hands-on Tasks:
Month 0–1 (Onboarding / fundamentals)

• Milestone: understand stack, CI/CD, alerting, runbooks
• Tasks: deploy a small service to dev; add one alert to Prometheus/Grafana; write a dev runbook
• Success metric: demo deploy + PR merged; runbook reviewed

Month 2–3 (Automation & reliability)

• Milestone: contribute automation and monitoring improvements
• Tasks: implement an automated rollback script or K8s job; add dashboards; write a playbook for common incidents
• Success metric: automation merged + reduced manual steps by X%; dashboard used in ops

3-month assessment (formal)

• Technical: completes assigned tasks, shows debugging workflow, testable automation
• Incident leadership: led 1 post-incident review (PIR) as scribe/coordinator
• Documentation: 3 reviewed runbooks
• Behavioral: demonstrates ownership, communication
• Pass criteria: shipping work independently, positive feedback from on-call peers

Month 4–5 (Incident leadership & SLOs)

• Milestone: lead incidents, own a small SLO/error-budget
• Tasks: be primary responder for low-sev incidents; define an SLO, instrument metrics, present to team
• Success metric: SLO defined + dashboard, led ≥2 incident responses

Month 6 (Ownership & career growth)

• Milestone: autonomous SRE contributor and mentor-in-training
• Tasks: run a blameless PIR as lead, mentor a new hire on a small task, propose a reliability improvement project
• Success metric: PIR outcomes implemented, mentee feedback positive, project proposal approved

6-month assessment

• Technical: consistent, production-safe changes; automation in place
• Incident leadership: led multiple incidents; clear comms under pressure
• Documentation: comprehensive runbooks covering top 10 incidents; ownership assigned
• Professional: growth plan defined (next 6–12 months), demonstrates coaching ability
• Pass criteria: meets ≥80% of milestones, manager and peer endorsements, readiness to manage larger services

Evaluation methods:

• Work artifacts (PRs, runbooks, dashboards)
• 360 feedback from on-call team and engineers
• Objective metrics: mean time to acknowledge/resolve for incidents they handled, number of manual steps automated, number of runbooks authored/reviewed

Adjustments: iterate plan every 1:1 based on progress; escalate training (pair-programming, courses, certifications) if gaps persist.

Direct answer

Normalize when write correctness and storage efficiency matter most: each fact lives in exactly one place, so an update touches one row and there is no duplicate copy to drift out of sync. Denormalize when read speed matters most: copying a value into the table that needs it removes a join at read time, at the cost of extra storage and extra write work to keep every copy consistent. The decision is really about where you are willing to pay a cost: on the write path (normalized) or on the read path (denormalized).

Structured elaboration

What normalization buys you

• A single source of truth for each fact (a customer's name lives in one row in the customers table). Rename a customer once, and every order referencing that customer's ID sees the new name immediately, because nothing else stored a copy.
• No update anomalies: you cannot end up with two rows disagreeing about the same customer's e-mail address, because there is only one row.
• Smaller row sizes and less redundant storage, since each attribute is stored once.

What it costs

• Reads that need a full picture (an order plus the customer's name and the product's title) require joining across multiple tables. As the number of tables in the join grows, so does read latency and database load per request.

What denormalization buys you

• Fast reads: a single table scan or index lookup returns everything the page needs, no join required. This matters most for read-heavy, latency-sensitive paths (a product listing page, an order-history feed).
• Fewer round-trips and less join computation on the database, which matters at high read volume.

What it costs

• Duplicated data: the same fact (a product's name, a customer's e-mail) now lives in more than one row.
• Write amplification and staleness risk: change the source fact once, and every duplicate copy must also be updated, or the duplicates drift and become wrong. If you skip updating one copy, you now have silently inconsistent data.
• More total storage, since the same bytes are stored multiple times.

How to actually decide

1. Estimate the read:write ratio on the specific table or field in question, not the system as a whole. A field read a thousand times for every write is a strong denormalization candidate; a field written as often as it is read is not.
2. Ask how often the would-be-duplicated value actually changes. A product's category ID rarely changes; a live inventory count changes constantly. Denormalizing something that changes constantly multiplies your write cost and your staleness risk.
3. Ask how expensive staleness is if a duplicate briefly lags. A denormalized display name that is a few seconds stale is usually fine; a denormalized account balance is usually not.
4. Consider partial solutions before going fully one way: a materialized view or a cached read model gives you denormalized-shaped reads without hand-maintaining duplicate columns in the source tables, at the cost of a refresh lag you must define and tolerate.

Worked example

Take an orders schema. Normalized (third normal form): an orders table (order ID, customer ID, timestamp), an order_items table (order ID, product ID, quantity, unit price), a customers table, and a products table. Rendering an order-detail page means joining order_items to products (for the product name and image) and joining orders to customers (for the customer's name), a three- to four-way join.

Suppose the system processes 1,000,000 orders a month, averaging 3 line items per order, so 3,000,000 order_items rows are written per month. A normalized order_items row (order ID, product ID, quantity, unit price as fixed-width fields) is roughly 28 bytes. A denormalized version that also copies in the product name (about 24 bytes), product category (about 12 bytes), customer name (about 20 bytes), and customer e-mail (about 24 bytes) adds about 80 bytes per row:

At 3,000,000 rows a month, that is:

of pure duplicate data added every month, before counting index overhead or replication. That is the storage side of the cost. The write side shows up when a product gets renamed: if that product already appears in 50,000 historical order_items rows, a normalized schema needs a single row updated in products; a denormalized schema that copied the product name into order_items needs all 50,000 rows updated (or accepts that historical order rows show the old name, which is a legitimate choice for orders specifically, since an order should arguably show the name as it was at purchase time, not the current name).

That last point is the real lesson: denormalizing an order line item's product name is often correct, not just a performance hack, because an order is a historical record and should not silently change when a product is renamed later. Denormalizing a customer's current e-mail address into the same row would be the wrong call, because you want that field to always reflect the customer's latest value, and a copy will drift.

Trade-offs & pitfalls

• Over-normalizing a read-heavy path (a product catalog page hit thousands of times a second) forces the database to redo the same multi-table join on every request, which is real, measurable load that a single denormalized read model would remove.
• Over-denormalizing a field that changes often multiplies write cost for a marginal read benefit, and creates a data-integrity bug class (stale duplicates) that is easy to miss in testing and expensive to debug in production.
• A common pitfall is denormalizing before measuring the actual read:write ratio, based on an assumption that reads are always dominant. Analytics and reporting schemas intentionally denormalize heavily (star-schema fact and dimension tables in an online analytical processing, OLAP, warehouse), because they are overwhelmingly read-heavy and batch-loaded; the live transactional path behind an online transaction processing (OLTP) system usually should not copy that pattern wholesale.
• The strongest senior answer treats this as a per-field decision, not a whole-schema philosophy: a single table can normalize some columns and denormalize others based on how each specific column is actually read and written.

Precise (exact) solution — exact 95th percentile per service per day using window functions (Postgres example). Two options: percentile_cont (ordered-set aggregate) or percentile_disc via DISTINCT ON if you need an observed value.

-- Using ordered-set aggregate (Postgres)
SELECT
  service,
  date_trunc('day', ts) AS day,
  percentile_cont(0.95) WITHIN GROUP (ORDER BY latency_ms) AS p95_latency_ms
FROM events
GROUP BY service, date_trunc('day', ts)
ORDER BY service, day;

If your SQL engine lacks percentile_cont, you can compute rank-based percentile with window functions:

WITH daily AS (
  SELECT
    service,
    date_trunc('day', ts) AS day,
    latency_ms,
    row_number() OVER (PARTITION BY service, date_trunc('day', ts) ORDER BY latency_ms) AS rn,
    count(*) OVER (PARTITION BY service, date_trunc('day', ts)) AS cnt
  FROM events
)
SELECT
  service,
  day,
  max(latency_ms) FILTER (WHERE rn = ceil(0.95 * cnt)) AS p95_latency_ms
FROM daily
GROUP BY service, day
ORDER BY service, day;

Approximate method for very large datasets

• Use t-digest or histogram sketches to aggregate summaries per shard/partition and merge them. This is suitable for streaming or map-reduce style ingestion and reduces memory/IO.

Example approach (conceptual):

1. At ingestion, build a t-digest per (service, day) for a time window (or per shard).
2. Merge digests periodically into a daily digest per service.
3. Query percentile from merged digest.

If using Postgres with tdigest extension:

-- pseudo-example; exact funcs depend on extension
SELECT service, day, tdigest_percentile(merged_tdigest, 0.95) AS p95
FROM daily_tdigests;

Trade-offs and guidance

• Exact methods require sorting or holding rows per group -> high memory/IO for hot services or long tails.
• t-digest preserves tail accuracy and merges well; error typically <<1–2% for p95 with reasonable compression.
• Histogram sketches (DDSketch, Quantile Digest) provide deterministic error bounds; choose based on error guarantees needed for SLOs.
• Validate sketch accuracy with sampling and store metadata (count, min, max) for monitoring.
• For alerts/SLOs, prefer conservative thresholds (account for sketch error) and surface confidence intervals.