:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.
Python ab-testingai-analyticsanalyticscdpdata-warehouseexperimentsfeature-flagsjavascriptproduct-analyticspythonreactsession-replaysurveystypescriptweb-analytics
use std::collections::{HashMap, HashSet};
use async_trait::async_trait;
use chrono::{DateTime, Utc};
use futures::stream::{self, StreamExt, TryStreamExt};
use sqlx::postgres::PgPool;
use sqlx::{Postgres, Transaction};
use uuid::Uuid;
use personhog_common::grpc::{current_client_name, current_method_name};
use super::{PostgresStorage, DB_BULK_CHUNKS, DB_QUERY_DURATION, DB_ROWS_RETURNED};
use crate::storage::error::{StorageError, StorageResult};
use crate::storage::traits::PersonLookup;
use crate::storage::types::{
DeletePersonsOutcome, Person, PersonListEntry, PersonTombstoneQueueEntry,
PersonVersionFloorResult, SplitResult, TombstoneTarget, TombstonedDeleteOutcome,
TombstonedDistinctId, TombstonedPerson, VersionFloorOutcome,
};
/// Version offset for split person/PDI rows — mirrors the Django convention.
const SPLIT_VERSION_OFFSET: i64 = 101;
const POOL_LABEL: &str = "replica";
const BULK_POOL_LABEL: &str = "bulk_replica";
#[async_trait]
impl PersonLookup for PostgresStorage {
async fn get_person_by_id(
&self,
team_id: i64,
person_id: i64,
) -> StorageResult<Option<Person>> {
let client = current_client_name();
let method = current_method_name();
let labels = [
("operation".to_string(), "get_person_by_id".to_string()),
("pool".to_string(), POOL_LABEL.to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), method.to_string()),
];
let _timer = common_metrics::timing_guard(DB_QUERY_DURATION, &labels);
let mut conn = PostgresStorage::acquire_timed(&self.replica_pool, POOL_LABEL).await?;
let row = sqlx::query_as!(
Person,
r#"
SELECT id, uuid, team_id::bigint as "team_id!", properties::text as "properties?",
properties_last_updated_at::text as "properties_last_updated_at?",
properties_last_operation::text as "properties_last_operation?",
created_at, version, is_identified,
CASE WHEN is_user_id IS NULL THEN NULL ELSE (is_user_id != 0) END as is_user_id,
last_seen_at
FROM posthog_person
WHERE team_id = $1 AND id = $2 AND is_deleted = false
"#,
team_id as i32,
person_id
)
.fetch_optional(&mut *conn)
.await?;
Ok(row)
}
async fn get_person_by_uuid(&self, team_id: i64, uuid: Uuid) -> StorageResult<Option<Person>> {
let client = current_client_name();
let method = current_method_name();
let labels = [
("operation".to_string(), "get_person_by_uuid".to_string()),
("pool".to_string(), POOL_LABEL.to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), method.to_string()),
];
let _timer = common_metrics::timing_guard(DB_QUERY_DURATION, &labels);
let mut conn = PostgresStorage::acquire_timed(&self.replica_pool, POOL_LABEL).await?;
let row = sqlx::query_as!(
Person,
r#"
SELECT id, uuid, team_id::bigint as "team_id!", properties::text as "properties?",
properties_last_updated_at::text as "properties_last_updated_at?",
properties_last_operation::text as "properties_last_operation?",
created_at, version, is_identified,
CASE WHEN is_user_id IS NULL THEN NULL ELSE (is_user_id != 0) END as is_user_id,
last_seen_at
FROM posthog_person
WHERE team_id = $1 AND uuid = $2 AND is_deleted = false
"#,
team_id as i32,
uuid
)
.fetch_optional(&mut *conn)
.await?;
Ok(row)
}
async fn get_persons_by_ids(
&self,
team_id: i64,
person_ids: &[i64],
include_properties: bool,
) -> StorageResult<Vec<Person>> {
if person_ids.is_empty() {
return Ok(Vec::new());
}
let client = current_client_name();
let method = current_method_name();
let labels = [
("operation".to_string(), "get_persons_by_ids".to_string()),
("pool".to_string(), BULK_POOL_LABEL.to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), method.to_string()),
];
let _timer = common_metrics::timing_guard(DB_QUERY_DURATION, &labels);
let pool = self.bulk_replica_pool.clone();
let chunks: Vec<Vec<i64>> = person_ids
.chunks(self.bulk_chunk_size)
.map(|c| c.to_vec())
.collect();
common_metrics::histogram(
DB_BULK_CHUNKS,
&[("operation".to_string(), "get_persons_by_ids".to_string())],
chunks.len() as f64,
);
let results: Vec<Vec<Person>> = stream::iter(chunks.into_iter().map(|chunk| {
let pool = pool.clone();
async move {
let mut conn =
PostgresStorage::acquire_timed(&pool, BULK_POOL_LABEL).await?;
let rows = sqlx::query_as!(
Person,
r#"
SELECT id, uuid, team_id::bigint as "team_id!",
CASE WHEN $3::boolean THEN properties::text ELSE NULL END as "properties?",
CASE WHEN $3::boolean THEN properties_last_updated_at::text ELSE NULL END as "properties_last_updated_at?",
CASE WHEN $3::boolean THEN properties_last_operation::text ELSE NULL END as "properties_last_operation?",
created_at, version, is_identified,
CASE WHEN is_user_id IS NULL THEN NULL ELSE (is_user_id != 0) END as is_user_id,
last_seen_at
FROM posthog_person
WHERE team_id = $1 AND id = ANY($2) AND is_deleted = false
"#,
team_id as i32,
&chunk,
include_properties
)
.fetch_all(&mut *conn)
.await?;
Ok::<_, StorageError>(rows)
}
}))
.buffer_unordered(self.bulk_max_concurrent_chunks)
.try_collect()
.await?;
let rows: Vec<Person> = results.into_iter().flatten().collect();
common_metrics::histogram(
DB_ROWS_RETURNED,
&[
("operation".to_string(), "get_persons_by_ids".to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), method.to_string()),
],
rows.len() as f64,
);
Ok(rows)
}
async fn get_persons_by_uuids(
&self,
team_id: i64,
uuids: &[Uuid],
include_properties: bool,
) -> StorageResult<Vec<Person>> {
if uuids.is_empty() {
return Ok(Vec::new());
}
let client = current_client_name();
let method = current_method_name();
let labels = [
("operation".to_string(), "get_persons_by_uuids".to_string()),
("pool".to_string(), BULK_POOL_LABEL.to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), method.to_string()),
];
let _timer = common_metrics::timing_guard(DB_QUERY_DURATION, &labels);
let pool = self.bulk_replica_pool.clone();
let chunks: Vec<Vec<Uuid>> = uuids
.chunks(self.bulk_chunk_size)
.map(|c| c.to_vec())
.collect();
common_metrics::histogram(
DB_BULK_CHUNKS,
&[("operation".to_string(), "get_persons_by_uuids".to_string())],
chunks.len() as f64,
);
let results: Vec<Vec<Person>> = stream::iter(chunks.into_iter().map(|chunk| {
let pool = pool.clone();
async move {
let mut conn =
PostgresStorage::acquire_timed(&pool, BULK_POOL_LABEL).await?;
let rows = sqlx::query_as!(
Person,
r#"
SELECT id, uuid, team_id::bigint as "team_id!",
CASE WHEN $3::boolean THEN properties::text ELSE NULL END as "properties?",
CASE WHEN $3::boolean THEN properties_last_updated_at::text ELSE NULL END as "properties_last_updated_at?",
CASE WHEN $3::boolean THEN properties_last_operation::text ELSE NULL END as "properties_last_operation?",
created_at, version, is_identified,
CASE WHEN is_user_id IS NULL THEN NULL ELSE (is_user_id != 0) END as is_user_id,
last_seen_at
FROM posthog_person
WHERE team_id = $1 AND uuid = ANY($2) AND is_deleted = false
"#,
team_id as i32,
&chunk,
include_properties
)
.fetch_all(&mut *conn)
.await?;
Ok::<_, StorageError>(rows)
}
}))
.buffer_unordered(self.bulk_max_concurrent_chunks)
.try_collect()
.await?;
let rows: Vec<Person> = results.into_iter().flatten().collect();
common_metrics::histogram(
DB_ROWS_RETURNED,
&[
("operation".to_string(), "get_persons_by_uuids".to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), method.to_string()),
],
rows.len() as f64,
);
Ok(rows)
}
async fn get_person_by_distinct_id(
&self,
team_id: i64,
distinct_id: &str,
) -> StorageResult<Option<Person>> {
let client = current_client_name();
let method = current_method_name();
let labels = [
(
"operation".to_string(),
"get_person_by_distinct_id".to_string(),
),
("pool".to_string(), POOL_LABEL.to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), method.to_string()),
];
let _timer = common_metrics::timing_guard(DB_QUERY_DURATION, &labels);
let mut conn = PostgresStorage::acquire_timed(&self.replica_pool, POOL_LABEL).await?;
let row = sqlx::query_as!(
Person,
r#"
SELECT p.id, p.uuid, p.team_id::bigint as "team_id!", p.properties::text as "properties?",
p.properties_last_updated_at::text as "properties_last_updated_at?",
p.properties_last_operation::text as "properties_last_operation?",
p.created_at, p.version, p.is_identified,
CASE WHEN p.is_user_id IS NULL THEN NULL ELSE (p.is_user_id != 0) END as is_user_id,
p.last_seen_at
FROM posthog_person p
INNER JOIN posthog_persondistinctid d ON p.id = d.person_id AND p.team_id = d.team_id
WHERE p.team_id = $1 AND d.distinct_id = $2
AND p.is_deleted = false AND d.is_deleted = false
LIMIT 1
"#,
team_id as i32,
distinct_id
)
.fetch_optional(&mut *conn)
.await?;
Ok(row)
}
async fn get_persons_by_distinct_ids_in_team(
&self,
team_id: i64,
distinct_ids: &[String],
include_properties: bool,
) -> StorageResult<Vec<(String, Option<Person>)>> {
if distinct_ids.is_empty() {
return Ok(Vec::new());
}
let client = current_client_name();
let method = current_method_name();
let labels = [
(
"operation".to_string(),
"get_persons_by_distinct_ids_in_team".to_string(),
),
("pool".to_string(), BULK_POOL_LABEL.to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), method.to_string()),
];
let _timer = common_metrics::timing_guard(DB_QUERY_DURATION, &labels);
let pool = self.bulk_replica_pool.clone();
// Drive from the supplied distinct IDs via UNNEST and probe the
// (team_id, distinct_id) index, rather than `d.distinct_id = ANY($2)`
// with the team predicate parked on `posthog_person`. The latter lets
// the planner start from every person in the team and hash-join, which
// is catastrophic for large teams. Deduplicate the input first so
// UNNEST emits one row per distinct ID; the response below still
// mirrors the caller's original list, repeats included.
let mut seen: HashSet<&str> = HashSet::with_capacity(distinct_ids.len());
let unique_ids: Vec<&str> = distinct_ids
.iter()
.map(|d| d.as_str())
.filter(|&d| seen.insert(d))
.collect();
let chunks: Vec<Vec<String>> = unique_ids
.chunks(self.bulk_chunk_size)
.map(|c| c.iter().map(|&s| s.to_string()).collect())
.collect();
common_metrics::histogram(
DB_BULK_CHUNKS,
&[(
"operation".to_string(),
"get_persons_by_distinct_ids_in_team".to_string(),
)],
chunks.len() as f64,
);
let chunk_results: Vec<Vec<(String, Person)>> =
stream::iter(chunks.into_iter().map(|chunk| {
let pool = pool.clone();
async move {
let mut conn =
PostgresStorage::acquire_timed(&pool, BULK_POOL_LABEL).await?;
let rows = sqlx::query!(
r#"
SELECT p.id, p.uuid as "uuid!", p.team_id::bigint as "team_id!",
CASE WHEN $3::boolean THEN p.properties::text ELSE NULL END as "properties?",
CASE WHEN $3::boolean THEN p.properties_last_updated_at::text ELSE NULL END as "properties_last_updated_at?",
CASE WHEN $3::boolean THEN p.properties_last_operation::text ELSE NULL END as "properties_last_operation?",
p.created_at as "created_at!", p.version, p.is_identified as "is_identified!",
CASE WHEN p.is_user_id IS NULL THEN NULL ELSE (p.is_user_id != 0) END as is_user_id,
p.last_seen_at,
d.distinct_id as "distinct_id!"
FROM UNNEST($2::text[]) AS batch(distinct_id)
INNER JOIN posthog_persondistinctid d
ON d.team_id = $1 AND d.distinct_id = batch.distinct_id
AND d.is_deleted = false
INNER JOIN posthog_person p
ON p.id = d.person_id AND p.team_id = d.team_id
AND p.is_deleted = false
"#,
team_id as i32,
&chunk,
include_properties
)
.fetch_all(&mut *conn)
.await?;
Ok::<_, StorageError>(
rows.into_iter()
.map(|row| {
let person = Person {
id: row.id,
uuid: row.uuid,
team_id: row.team_id,
properties: row.properties,
properties_last_updated_at: row.properties_last_updated_at,
properties_last_operation: row.properties_last_operation,
created_at: row.created_at,
version: row.version,
is_identified: row.is_identified,
is_user_id: row.is_user_id,
last_seen_at: row.last_seen_at,
};
(row.distinct_id, person)
})
.collect(),
)
}
}))
.buffer_unordered(self.bulk_max_concurrent_chunks)
.try_collect()
.await?;
let mut found: HashMap<String, Person> = chunk_results.into_iter().flatten().collect();
common_metrics::histogram(
DB_ROWS_RETURNED,
&[
(
"operation".to_string(),
"get_persons_by_distinct_ids_in_team".to_string(),
),
("client".to_string(), client.to_string()),
("method".to_string(), method.to_string()),
],
found.len() as f64,
);
Ok(distinct_ids
.iter()
.map(|did| (did.clone(), found.remove(did)))
.collect())
}
async fn delete_persons(
&self,
team_id: i64,
uuids: &[Uuid],
) -> StorageResult<DeletePersonsOutcome> {
if uuids.is_empty() {
return Ok(DeletePersonsOutcome::default());
}
let client = current_client_name();
let method = current_method_name();
let labels = [
("operation".to_string(), "delete_persons".to_string()),
("pool".to_string(), "bulk_primary".to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), method.to_string()),
];
let _timer = common_metrics::timing_guard(DB_QUERY_DURATION, &labels);
tombstone_persons_by_uuids(self, team_id, uuids, &client).await
}
async fn delete_persons_batch_for_team(
&self,
team_id: i64,
batch_size: i64,
) -> StorageResult<i64> {
if batch_size <= 0 {
return Ok(0);
}
let client = current_client_name();
let method = current_method_name();
let labels = [
(
"operation".to_string(),
"delete_persons_batch_for_team".to_string(),
),
("pool".to_string(), "bulk_primary".to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), method.to_string()),
];
let _timer = common_metrics::timing_guard(DB_QUERY_DURATION, &labels);
sqlx::query!(
"DELETE FROM person_tombstone_publish_queue WHERE team_id = $1",
team_id as i32
)
.execute(&self.bulk_primary_pool)
.await?;
// Team teardown always removes the rows, tombstoned ones included: nothing
// sweeps a deleted team's tombstones into the cleanup queue.
let mut person_ids: Vec<i64> = sqlx::query_scalar!(
r#"
SELECT id::bigint as "id!" FROM posthog_person
WHERE team_id = $1
LIMIT $2
"#,
team_id as i32,
batch_size
)
.fetch_all(&self.bulk_primary_pool)
.await?;
if person_ids.is_empty() {
return Ok(0);
}
person_ids.sort_unstable();
// Split into fixed-size chunks and delete concurrently.
let pool = self.bulk_primary_pool.clone();
let chunks: Vec<Vec<i64>> = person_ids
.chunks(self.bulk_chunk_size)
.map(|c| c.to_vec())
.collect();
common_metrics::histogram(
DB_BULK_CHUNKS,
&[(
"operation".to_string(),
"delete_persons_batch_for_team".to_string(),
)],
chunks.len() as f64,
);
let results: Vec<i64> =
stream::iter(
chunks.into_iter().map(|chunk| {
let pool = pool.clone();
let client = client.clone();
// Team teardown clears cohortpeople separately, by cohort, before this runs.
async move {
delete_persons_by_ids_chunk(&pool, team_id, &chunk, &client, false).await
}
}),
)
.buffer_unordered(self.bulk_max_concurrent_chunks)
.try_collect()
.await?;
Ok(results.iter().sum())
}
async fn get_person_tombstones(
&self,
team_id: i64,
uuids: &[Uuid],
) -> StorageResult<Vec<TombstonedPerson>> {
if uuids.is_empty() {
return Ok(Vec::new());
}
let labels = [
("operation".to_string(), "get_person_tombstones".to_string()),
("pool".to_string(), "bulk_primary".to_string()),
("client".to_string(), current_client_name().to_string()),
("method".to_string(), current_method_name().to_string()),
];
let _timer = common_metrics::timing_guard(DB_QUERY_DURATION, &labels);
let mut conn =
PostgresStorage::acquire_timed(&self.bulk_primary_pool, "bulk_primary").await?;
let mut tx = sqlx::Connection::begin(&mut *conn).await?;
sqlx::query("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY")
.execute(&mut *tx)
.await?;
let rows = sqlx::query!(
r#"
SELECT id::bigint as "id!", uuid as "uuid!",
COALESCE(version, 0)::bigint as "version!"
FROM posthog_person
WHERE team_id = $1 AND uuid = ANY($2) AND is_deleted = true
"#,
team_id as i32,
uuids
)
.fetch_all(&mut *tx)
.await?;
let persons = rows
.into_iter()
.map(|row| (row.id, row.uuid, row.version))
.collect();
let mut tombstones = with_tombstoned_distinct_ids(&mut tx, team_id, persons).await?;
tx.commit().await?;
tombstones.sort_by(|a, b| a.uuid.cmp(&b.uuid));
common_metrics::histogram(DB_ROWS_RETURNED, &labels, tombstones.len() as f64);
Ok(tombstones)
}
async fn ack_person_tombstones(
&self,
team_id: i64,
acked: &[(Uuid, i64)],
) -> StorageResult<i64> {
if acked.is_empty() {
return Ok(0);
}
let labels = [
("operation".to_string(), "ack_person_tombstones".to_string()),
("pool".to_string(), "primary".to_string()),
("client".to_string(), current_client_name().to_string()),
("method".to_string(), current_method_name().to_string()),
];
let _timer = common_metrics::timing_guard(DB_QUERY_DURATION, &labels);
let uuids: Vec<Uuid> = acked.iter().map(|(uuid, _)| *uuid).collect();
let versions: Vec<i64> = acked.iter().map(|(_, version)| *version).collect();
let mut conn = PostgresStorage::acquire_timed(&self.primary_pool, "primary").await?;
let result = sqlx::query!(
r#"
DELETE FROM person_tombstone_publish_queue q
USING UNNEST($2::uuid[], $3::bigint[]) AS a(person_uuid, person_version)
WHERE q.team_id = $1
AND q.person_uuid = a.person_uuid
AND q.person_version <= a.person_version
"#,
team_id as i32,
&uuids,
&versions
)
.execute(&mut *conn)
.await?;
Ok(result.rows_affected() as i64)
}
async fn list_person_tombstone_queue(
&self,
after: (i64, Uuid),
team_id: Option<i64>,
limit: i64,
) -> StorageResult<Vec<PersonTombstoneQueueEntry>> {
let labels = [
(
"operation".to_string(),
"list_person_tombstone_queue".to_string(),
),
("pool".to_string(), "primary".to_string()),
("client".to_string(), current_client_name().to_string()),
("method".to_string(), current_method_name().to_string()),
];
let _timer = common_metrics::timing_guard(DB_QUERY_DURATION, &labels);
let mut conn = PostgresStorage::acquire_timed(&self.primary_pool, "primary").await?;
let rows = sqlx::query!(
r#"
SELECT team_id::bigint as "team_id!", person_uuid as "person_uuid!",
person_version as "person_version!",
(EXTRACT(EPOCH FROM tombstoned_at) * 1000)::bigint as "tombstoned_at_ms!"
FROM person_tombstone_publish_queue
WHERE (team_id, person_uuid) > ($1::int, $2::uuid)
AND ($3::int IS NULL OR team_id = $3)
ORDER BY team_id, person_uuid
LIMIT $4
"#,
after.0 as i32,
after.1,
team_id.map(|t| t as i32),
limit
)
.fetch_all(&mut *conn)
.await?;
common_metrics::histogram(DB_ROWS_RETURNED, &labels, rows.len() as f64);
Ok(rows
.into_iter()
.map(|row| PersonTombstoneQueueEntry {
team_id: row.team_id,
person_uuid: row.person_uuid,
person_version: row.person_version,
tombstoned_at_ms: row.tombstoned_at_ms,
})
.collect())
}
async fn delete_tombstoned_persons(
&self,
team_id: i64,
targets: &[TombstoneTarget],
max_rows: i64,
) -> StorageResult<TombstonedDeleteOutcome> {
if targets.is_empty() {
return Ok(TombstonedDeleteOutcome::default());
}
let client = current_client_name();
let method = current_method_name();
let labels = [
(
"operation".to_string(),
"delete_tombstoned_persons".to_string(),
),
("pool".to_string(), "bulk_primary".to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), method.to_string()),
];
let _timer = common_metrics::timing_guard(DB_QUERY_DURATION, &labels);
// One outcome per uuid: a duplicate must not be counted or reported twice. It keeps its
// lowest bound, the one that deletes the least.
let mut bounds: HashMap<Uuid, i64> = HashMap::with_capacity(targets.len());
for target in targets {
bounds
.entry(target.uuid)
.and_modify(|bound| *bound = (*bound).min(target.max_version))
.or_insert(target.max_version);
}
let unique: Vec<Uuid> = bounds.keys().copied().collect();
// The caller picks the row budget; the server caps it so no call outlives its deadline.
let budget = max_rows.clamp(1, self.tombstoned_delete_max_rows as i64);
let mut tx = self.bulk_primary_pool.begin().await?;
// A held row means a revival or merge in flight: fail fast and let the caller retry. Kept
// under the router's 5 s backend deadline so the caller sees an error, not a timeout.
sqlx::query("SET LOCAL lock_timeout = '2s'")
.execute(&mut *tx)
.await?;
// This read takes no lock. The delete re-checks the tombstone and the bound under its row
// lock, so a person revived or tombstoned again after this read lands in no outcome bucket.
let persons = sqlx::query!(
r#"
SELECT id::bigint AS "id!", uuid AS "uuid!", is_deleted AS "is_deleted!",
COALESCE(version, 0)::bigint AS "version!"
FROM posthog_person
WHERE team_id = $1 AND uuid = ANY($2)
ORDER BY id
"#,
team_id as i32,
unique.as_slice()
)
.fetch_all(&mut *tx)
.await?;
let mut skipped_live = 0;
let mut skipped_version = 0;
let mut candidates: Vec<(i64, Uuid)> = Vec::with_capacity(persons.len());
for row in persons {
if !row.is_deleted {
skipped_live += 1;
} else if row.version > bounds[&row.uuid] {
skipped_version += 1;
} else {
candidates.push((row.id, row.uuid));
}
}
let mut outcome = TombstonedDeleteOutcome {
skipped_live,
skipped_version,
..TombstonedDeleteOutcome::default()
};
if candidates.is_empty() {
tx.commit().await?;
record_tombstoned_delete_rows(&outcome, &client, &method);
return Ok(outcome);
}
// Each probe reads at most `remaining + 1` index entries per table per person, so its
// cost is bounded by the budget however many rows a person owns.
let mut admission = Admission::new(budget, self.bulk_chunk_size);
for batch in candidates.chunks(PROBE_BATCH_PERSONS) {
if !admission.wants_more() {
admission.defer(batch.iter().map(|(_, uuid)| *uuid));
continue;
}
let ids: Vec<i64> = batch.iter().map(|(id, _)| *id).collect();
let counts =
probe_dependent_rows(&mut *tx, team_id, &ids, admission.probe_limit()).await?;
for (id, uuid) in batch {
admission.offer(*id, *uuid, counts.get(id).copied().unwrap_or_default());
}
}
let Admission {
remaining,
admitted,
trim,
mut pending,
..
} = admission;
// READ COMMITTED re-checks the tombstone and the bound on the row version that wins the
// lock, so a person revived or tombstoned again a moment ago drops out, trimmed or not.
// Live writers touch only live persons, which this never locks, and the identity saga locks
// persons before distinct ids in this same id order.
let (lock_ids, lock_bounds): (Vec<i64>, Vec<i64>) = admitted
.iter()
.chain(trim.iter())
.map(|(id, uuid)| (*id, bounds[uuid]))
.unzip();
let locked: HashSet<i64> = sqlx::query_scalar!(
r#"
SELECT p.id::bigint AS "id!"
FROM posthog_person p
JOIN UNNEST($2::bigint[], $3::bigint[]) AS bound(id, max_version) ON bound.id = p.id
WHERE p.team_id = $1 AND p.id = ANY($2) AND p.is_deleted
AND COALESCE(p.version, 0) <= bound.max_version
ORDER BY p.id
FOR UPDATE OF p
"#,
team_id as i32,
lock_ids.as_slice(),
lock_bounds.as_slice()
)
.fetch_all(&mut *tx)
.await?
.into_iter()
.collect();
let admitted: Vec<(i64, Uuid)> = admitted
.into_iter()
.filter(|(id, _)| locked.contains(id))
.collect();
let admitted_ids: Vec<i64> = admitted.iter().map(|(id, _)| *id).collect();
// Lock the distinct ids too and read their state under the lock. A live mapping means
// ingestion can still reach the person, so it must stay.
let mut live_owners: HashSet<i64> = HashSet::new();
if !admitted_ids.is_empty() {
live_owners = sqlx::query!(
r#"
SELECT person_id AS "person_id!", is_deleted AS "is_deleted!"
FROM posthog_persondistinctid
WHERE team_id = $1 AND person_id = ANY($2)
ORDER BY id
FOR UPDATE
"#,
team_id as i32,
admitted_ids.as_slice()
)
.fetch_all(&mut *tx)
.await?
.into_iter()
.filter(|row| !row.is_deleted)
.map(|row| row.person_id)
.collect();
}
let (blocked, victims): (Vec<(i64, Uuid)>, Vec<(i64, Uuid)>) = admitted
.into_iter()
.partition(|(id, _)| live_owners.contains(id));
outcome.blocked_uuids = blocked.into_iter().map(|(_, uuid)| uuid).collect();
let victim_ids: Vec<i64> = victims.iter().map(|(id, _)| *id).collect();
// The hash key override FK cascades in production but not in every environment built
// from the sqlx migrations, so remove the overrides here instead of relying on the cascade.
if !victim_ids.is_empty() {
let overrides = sqlx::query!(
"DELETE FROM posthog_featureflaghashkeyoverride WHERE team_id = $1 AND person_id = ANY($2)",
team_id as i32,
victim_ids.as_slice()
)
.execute(&mut *tx)
.await?;
outcome.rows_deleted += overrides.rows_affected() as i64;
}
let rows =
delete_persons_by_ids_in_tx(&mut tx, team_id, &victim_ids, &client, true).await?;
outcome.deleted = rows.persons;
outcome.rows_deleted += rows.dependents();
// The first person that did not fit gives up as many rows as the leftover allows and
// stays pending, unless it turned out to be live again or to own a live distinct id.
if let Some((id, uuid)) = trim {
let still_pending = if !locked.contains(&id) {
false
} else {
match trim_locked_person(&mut tx, team_id, id, remaining).await? {
Some(rows) => {
outcome.rows_deleted += rows;
true
}
None => {
outcome.blocked_uuids.push(uuid);
false
}
}
};
if !still_pending {
pending.retain(|u| *u != uuid);
}
}
outcome.pending_uuids = pending;
tx.commit().await?;
record_tombstoned_delete_rows(&outcome, &client, &method);
Ok(outcome)
}
async fn get_persons_by_distinct_ids_cross_team(
&self,
team_distinct_ids: &[(i64, String)],
include_properties: bool,
) -> StorageResult<Vec<((i64, String), Option<Person>)>> {
if team_distinct_ids.is_empty() {
return Ok(Vec::new());
}
let client = current_client_name();
let method = current_method_name();
let labels = [
(
"operation".to_string(),
"get_persons_by_distinct_ids_cross_team".to_string(),
),
("pool".to_string(), BULK_POOL_LABEL.to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), method.to_string()),
];
let _timer = common_metrics::timing_guard(DB_QUERY_DURATION, &labels);
let mut conn =
PostgresStorage::acquire_timed(&self.bulk_replica_pool, BULK_POOL_LABEL).await?;
let team_ids: Vec<i32> = team_distinct_ids.iter().map(|(t, _)| *t as i32).collect();
let distinct_ids: Vec<String> = team_distinct_ids.iter().map(|(_, d)| d.clone()).collect();
let rows = sqlx::query!(
r#"
SELECT p.id, p.uuid as "uuid!", p.team_id::bigint as "team_id!",
CASE WHEN $3::boolean THEN p.properties::text ELSE NULL END as "properties?",
CASE WHEN $3::boolean THEN p.properties_last_updated_at::text ELSE NULL END as "properties_last_updated_at?",
CASE WHEN $3::boolean THEN p.properties_last_operation::text ELSE NULL END as "properties_last_operation?",
p.created_at as "created_at!", p.version, p.is_identified as "is_identified!",
CASE WHEN p.is_user_id IS NULL THEN NULL ELSE (p.is_user_id != 0) END as is_user_id,
p.last_seen_at,
d.distinct_id as "distinct_id!"
FROM posthog_person p
INNER JOIN posthog_persondistinctid d ON d.person_id = p.id AND d.team_id = p.team_id
INNER JOIN UNNEST($1::integer[], $2::text[]) AS batch(team_id, distinct_id)
ON d.team_id = batch.team_id AND d.distinct_id = batch.distinct_id
WHERE p.is_deleted = false AND d.is_deleted = false
"#,
&team_ids,
&distinct_ids,
include_properties
)
.fetch_all(&mut *conn)
.await?;
common_metrics::histogram(
DB_ROWS_RETURNED,
&[
(
"operation".to_string(),
"get_persons_by_distinct_ids_cross_team".to_string(),
),
("client".to_string(), client.to_string()),
("method".to_string(), method.to_string()),
],
rows.len() as f64,
);
let mut found: HashMap<(i64, String), Person> = rows
.into_iter()
.map(|row| {
let key = (row.team_id, row.distinct_id.clone());
let person = Person {
id: row.id,
uuid: row.uuid,
team_id: row.team_id,
properties: row.properties,
properties_last_updated_at: row.properties_last_updated_at,
properties_last_operation: row.properties_last_operation,
created_at: row.created_at,
version: row.version,
is_identified: row.is_identified,
is_user_id: row.is_user_id,
last_seen_at: row.last_seen_at,
};
(key, person)
})
.collect();
Ok(team_distinct_ids
.iter()
.map(|(team_id, did)| {
let key = (*team_id, did.clone());
(key.clone(), found.remove(&key))
})
.collect())
}
async fn split_person(
&self,
team_id: i64,
person_id: i64,
distinct_ids_to_split: &[String],
) -> StorageResult<Vec<SplitResult>> {
if distinct_ids_to_split.is_empty() {
return Ok(vec![]);
}
let client = current_client_name();
let method = current_method_name();
let labels = [
("operation".to_string(), "split_person".to_string()),
("pool".to_string(), "bulk_primary".to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), method.to_string()),
];
let _timer = common_metrics::timing_guard(DB_QUERY_DURATION, &labels);
// All-or-nothing transaction on the bulk primary pool. The statement count
// is constant (not per-distinct_id), so the lock window stays short; the
// service-layer cap bounds the number of rows locked.
let mut tx = self.bulk_primary_pool.begin().await?;
// No FOR UPDATE on the source person: deletes lock the person before its
// PDIs, so locking it after the PDIs here would invert that order and risk
// deadlock. The PDI locks below are what guard the reassignment.
let person_version: i64 = sqlx::query_scalar!(
r#"
SELECT COALESCE(version, 0)::bigint as "version!"
FROM posthog_person
WHERE team_id = $1 AND id = $2
"#,
team_id as i32,
person_id
)
.fetch_optional(&mut *tx)
.await?
.ok_or_else(|| {
StorageError::NotFound(format!("person_id={person_id} (team_id={team_id})"))
})?;
// Lock the PDI rows and validate ownership under the lock: any requested
// distinct_id that didn't lock either doesn't exist or belongs to another
// person, and the whole request is rejected.
let locked_pdis = sqlx::query!(
r#"
SELECT distinct_id as "distinct_id!", COALESCE(version, 0)::bigint as "version!"
FROM posthog_persondistinctid
WHERE team_id = $1 AND person_id = $2 AND distinct_id = ANY($3) AND is_deleted = false
FOR UPDATE
"#,
team_id as i32,
person_id,
distinct_ids_to_split
)
.fetch_all(&mut *tx)
.await?;
if locked_pdis.len() != distinct_ids_to_split.len() {
let owned_set: HashSet<&str> = locked_pdis
.iter()
.map(|pdi| pdi.distinct_id.as_str())
.collect();
let unknown: Vec<&str> = distinct_ids_to_split
.iter()
.filter(|did| !owned_set.contains(did.as_str()))
.map(|s| s.as_str())
.collect();
return Err(StorageError::NotFound(format!(
"distinct_ids {unknown:?} do not belong to person_id={person_id} (team_id={team_id})"
)));
}
let new_person_version = person_version + SPLIT_VERSION_OFFSET;
// Build all arrays in request order so the response order matches the
// input order (part of the RPC contract).
let pdi_version_by_did: HashMap<&str, i64> = locked_pdis
.iter()
.map(|pdi| (pdi.distinct_id.as_str(), pdi.version))
.collect();
let dids: Vec<String> = distinct_ids_to_split.to_vec();
let derived_uuids: Vec<Uuid> = dids
.iter()
.map(|did| personhog_common::persons::person_uuid(team_id, did))
.collect();
let pdi_versions: Vec<i64> = dids
.iter()
.map(|did| pdi_version_by_did[did.as_str()] + SPLIT_VERSION_OFFSET)
.collect();
// A live person here (the source itself, for the distinct id that created it) would absorb
// the distinct id and get its ClickHouse row blanked by the split's empty-person publish.
// It is never written, so it is not locked: deletes lock the person before its PDIs.
let live_uuids: HashSet<Uuid> = sqlx::query_scalar!(
r#"
SELECT uuid as "uuid!"
FROM posthog_person
WHERE team_id = $1 AND uuid = ANY($2) AND is_deleted = false
"#,
team_id as i32,
&derived_uuids
)
.fetch_all(&mut *tx)
.await?
.into_iter()
.collect();
let new_uuids: Vec<Uuid> = derived_uuids
.into_iter()
.map(|u| {
if live_uuids.contains(&u) {
Uuid::new_v4()
} else {
u
}
})
.collect();
// No ON CONFLICT — the partitioned table has a unique index, not a
// unique constraint, so ON CONFLICT inference doesn't work.
let tombstones = sqlx::query!(
r#"
SELECT id::bigint as "id!", uuid as "uuid!", created_at as "created_at!"
FROM posthog_person
WHERE team_id = $1 AND uuid = ANY($2) AND is_deleted = true
ORDER BY id
FOR UPDATE
"#,
team_id as i32,
&new_uuids
)
.fetch_all(&mut *tx)
.await?;
let mut person_by_uuid: HashMap<Uuid, (i64, DateTime<Utc>)> = tombstones
.into_iter()
.map(|r| (r.uuid, (r.id, r.created_at)))
.collect();
let mut version_by_uuid: HashMap<Uuid, i64> = HashMap::new();
let existing_uuids: HashSet<Uuid> = person_by_uuid.keys().copied().collect();
let uuids_to_insert: Vec<Uuid> = new_uuids
.iter()
.filter(|u| !existing_uuids.contains(u))
.copied()
.collect();
if !uuids_to_insert.is_empty() {
let inserted = sqlx::query!(
r#"
INSERT INTO posthog_person (uuid, team_id, properties, created_at, version, is_identified)
SELECT u.uuid, $2, '{}'::jsonb, NOW(), $3, false
FROM unnest($1::uuid[]) AS u(uuid)
RETURNING id::bigint as "id!", uuid as "uuid!", created_at as "created_at!"
"#,
&uuids_to_insert,
team_id as i32,
new_person_version
)
.fetch_all(&mut *tx)
.await?;
for r in inserted {
person_by_uuid.insert(r.uuid, (r.id, r.created_at));
version_by_uuid.insert(r.uuid, new_person_version);
}
}
let uuids_to_update: Vec<Uuid> = existing_uuids.into_iter().collect();
if !uuids_to_update.is_empty() {
// A tombstoned row comes back to life here, one above its own
// version so it outranks its ClickHouse tombstone; the split
// offset applies when that is higher.
let updated = sqlx::query!(
r#"
UPDATE posthog_person
SET is_deleted = false,
version = GREATEST(COALESCE(version, 0) + 1, $3)
WHERE team_id = $1 AND uuid = ANY($2) AND is_deleted = true
RETURNING uuid as "uuid!", version as "version!"
"#,
team_id as i32,
&uuids_to_update,
new_person_version
)
.fetch_all(&mut *tx)
.await?;
for r in updated {
version_by_uuid.insert(r.uuid, r.version);
}
}
let new_person_rows: Vec<(i64, DateTime<Utc>)> = new_uuids
.iter()
.map(|u| {
person_by_uuid.get(u).copied().ok_or_else(|| {
StorageError::Query(format!("person insert did not return a row for uuid {u}"))
})
})
.collect::<StorageResult<_>>()?;
let new_person_ids: Vec<i64> = new_person_rows.iter().map(|(id, _)| *id).collect();
// Reassign all PDIs to their new persons in one statement.
let update_result = sqlx::query!(
r#"
UPDATE posthog_persondistinctid AS pdi
SET person_id = m.new_person_id, version = m.new_version
FROM unnest($2::text[], $3::bigint[], $4::bigint[]) AS m(distinct_id, new_person_id, new_version)
WHERE pdi.team_id = $1 AND pdi.distinct_id = m.distinct_id
"#,
team_id as i32,
&dids,
&new_person_ids,
&pdi_versions
)
.execute(&mut *tx)
.await?;
if update_result.rows_affected() != dids.len() as u64 {
return Err(StorageError::Query(format!(
"expected to reassign {} PDIs but updated {} (team_id={team_id}, person_id={person_id})",
dids.len(),
update_result.rows_affected()
)));
}
tx.commit().await?;
let results: Vec<SplitResult> = dids
.into_iter()
.zip(new_uuids)
.zip(pdi_versions)
.zip(new_person_rows)
.map(
|(((distinct_id, new_person_uuid), pdi_version), (_, new_person_created_at))| {
Ok(SplitResult {
distinct_id,
new_person_uuid,
new_person_version: version_by_uuid
.get(&new_person_uuid)
.copied()
.ok_or_else(|| {
StorageError::Query(format!(
"version not populated for uuid {new_person_uuid}"
))
})?,
pdi_version,
new_person_created_at,
})
},
)
.collect::<StorageResult<Vec<_>>>()?;
common_metrics::histogram(
DB_ROWS_RETURNED,
&[
("operation".to_string(), "split_person".to_string()),
("pool".to_string(), "bulk_primary".to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), method.to_string()),
],
results.len() as f64,
);
Ok(results)
}
async fn set_person_distinct_id_version_floor(
&self,
team_id: i64,
distinct_id: &str,
min_version: i64,
) -> StorageResult<Option<Person>> {
let client = current_client_name();
let method = current_method_name();
let labels = [
(
"operation".to_string(),
"set_person_distinct_id_version_floor".to_string(),
),
("pool".to_string(), "primary".to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), method.to_string()),
];
let _timer = common_metrics::timing_guard(DB_QUERY_DURATION, &labels);
let mut conn = PostgresStorage::acquire_timed(&self.primary_pool, "primary").await?;
// The person is returned whenever the distinct_id exists, even if its version is already
// at or above min_version. A NULL version counts as 0, as in every other personhog write.
let row = sqlx::query_as!(
Person,
r#"
WITH target AS (
SELECT person_id FROM posthog_persondistinctid
WHERE team_id = $1 AND distinct_id = $2
),
updated AS (
UPDATE posthog_persondistinctid
SET version = $3
WHERE team_id = $1 AND distinct_id = $2 AND COALESCE(version, 0) < $3
RETURNING person_id
)
SELECT p.id, p.uuid, p.team_id::bigint as "team_id!", p.properties::text as "properties?",
p.properties_last_updated_at::text as "properties_last_updated_at?",
p.properties_last_operation::text as "properties_last_operation?",
p.created_at, p.version, p.is_identified,
CASE WHEN p.is_user_id IS NULL THEN NULL ELSE (p.is_user_id != 0) END as is_user_id,
p.last_seen_at
FROM posthog_person p
INNER JOIN target t ON p.id = t.person_id AND p.team_id = $1
"#,
team_id as i32,
distinct_id,
min_version
)
.fetch_optional(&mut *conn)
.await?;
Ok(row)
}
async fn set_person_version_floor(
&self,
team_id: i64,
person_id: i64,
min_version: i64,
) -> StorageResult<bool> {
let client = current_client_name();
let method = current_method_name();
let labels = [
(
"operation".to_string(),
"set_person_version_floor".to_string(),
),
("pool".to_string(), "primary".to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), method.to_string()),
];
let _timer = common_metrics::timing_guard(DB_QUERY_DURATION, &labels);
let mut conn = PostgresStorage::acquire_timed(&self.primary_pool, "primary").await?;
// Never lowers a version. A NULL version counts as 0, as in every other personhog write.
let result = sqlx::query!(
r#"
UPDATE posthog_person
SET version = $3
WHERE team_id = $1 AND id = $2 AND COALESCE(version, 0) < $3
"#,
team_id as i32,
person_id,
min_version
)
.execute(&mut *conn)
.await?;
Ok(result.rows_affected() > 0)
}
async fn ensure_person_version_floors(
&self,
team_id: i64,
floors: &[(Uuid, i64)],
) -> StorageResult<Vec<PersonVersionFloorResult>> {
if floors.is_empty() {
return Ok(Vec::new());
}
let labels = bulk_primary_labels("ensure_person_version_floors");
let _timer = common_metrics::timing_guard(DB_QUERY_DURATION, &labels);
let mut tx = self.bulk_primary_pool.begin().await?;
// A held row means a merge, revival or delete in flight: fail fast and let
// the caller retry instead of queueing behind it.
sqlx::query("SET LOCAL lock_timeout = '2s'")
.execute(&mut *tx)
.await?;
let uuids: Vec<Uuid> = floors.iter().map(|(uuid, _)| *uuid).collect();
let mut before = lock_persons_by_uuids(&mut tx, team_id, &uuids).await?;
// Insert in uuid order, so two calls racing on the same new uuids wait on each
// other's inserts in one order and cannot deadlock.
let mut absent: Vec<(Uuid, i64)> = floors
.iter()
.filter(|(uuid, _)| !before.contains_key(uuid))
.copied()
.collect();
absent.sort_unstable_by_key(|(uuid, _)| *uuid);
let (absent_uuids, absent_mins): (Vec<Uuid>, Vec<i64>) = absent.into_iter().unzip();
let inserted =
insert_person_tombstones(&mut tx, team_id, &absent_uuids, &absent_mins).await?;
// ON CONFLICT DO NOTHING waited for a concurrent insert of the same uuid and
// skipped it. Lock and classify that row like any other existing row.
let raced: Vec<Uuid> = absent_uuids
.iter()
.filter(|uuid| !inserted.contains_key(uuid))
.copied()
.collect();
if !raced.is_empty() {
let late = lock_persons_by_uuids(&mut tx, team_id, &raced).await?;
if late.len() != raced.len() {
return Err(StorageError::FailedPrecondition(format!(
"person rows changed during ensure_person_version_floors (team_id={team_id}); retry"
)));
}
before.extend(late);
}
let (raise_ids, raise_mins): (Vec<i64>, Vec<i64>) = floors
.iter()
.filter_map(|(uuid, min_version)| {
before
.get(uuid)
.filter(|row| row.is_deleted && row.version < *min_version)
.map(|row| (row.id, *min_version))
})
.unzip();
if !raise_ids.is_empty() {
sqlx::query!(
r#"
UPDATE posthog_person p
SET version = f.min_version
FROM UNNEST($2::bigint[], $3::bigint[]) AS f(id, min_version)
WHERE p.team_id = $1 AND p.id = f.id
AND COALESCE(p.version, 0) < f.min_version
"#,
team_id as i32,
&raise_ids,
&raise_mins
)
.execute(&mut *tx)
.await?;
}
tx.commit().await?;
let results = floors
.iter()
.map(|(uuid, min_version)| {
if inserted.contains_key(uuid) {
return PersonVersionFloorResult {
uuid: *uuid,
outcome: VersionFloorOutcome::TombstoneInserted,
version: *min_version,
};
}
let row = &before[uuid];
PersonVersionFloorResult {
uuid: *uuid,
outcome: VersionFloorOutcome::for_existing(
row.is_deleted,
row.version,
*min_version,
),
version: if row.is_deleted {
row.version.max(*min_version)
} else {
row.version
},
}
})
.collect::<Vec<_>>();
record_floor_outcomes(
"ensure_person_version_floors",
results.iter().map(|r| r.outcome),
);
Ok(results)
}
async fn list_persons_for_team(
&self,
team_id: i64,
after_person_id: i64,
before_person_id: Option<i64>,
limit: i64,
) -> StorageResult<Vec<PersonListEntry>> {
let labels = [
("operation".to_string(), "list_persons_for_team".to_string()),
("pool".to_string(), "bulk_replica".to_string()),
("client".to_string(), current_client_name().to_string()),
("method".to_string(), current_method_name().to_string()),
];
let _timer = common_metrics::timing_guard(DB_QUERY_DURATION, &labels);
let mut conn =
PostgresStorage::acquire_timed(&self.bulk_replica_pool, "bulk_replica").await?;
let rows = sqlx::query!(
r#"
SELECT p.id::bigint as "id!", p.uuid as "uuid!",
COALESCE(p.version, 0)::bigint as "version!", p.is_deleted as "is_deleted!",
p.created_at as "created_at!",
EXISTS (
SELECT 1 FROM posthog_persondistinctid d
WHERE d.team_id = p.team_id AND d.person_id = p.id AND d.is_deleted = false
) as "has_live_distinct_ids!"
FROM posthog_person p
WHERE p.team_id = $1 AND p.id > $2 AND ($4::bigint IS NULL OR p.id < $4)
ORDER BY p.id
LIMIT $3
"#,
team_id as i32,
after_person_id,
limit,
before_person_id
)
.fetch_all(&mut *conn)
.await?
.into_iter()
.map(|r| PersonListEntry {
id: r.id,
uuid: r.uuid,
version: r.version,
is_deleted: r.is_deleted,
has_live_distinct_ids: r.has_live_distinct_ids,
created_at: r.created_at,
})
.collect::<Vec<_>>();
common_metrics::histogram(DB_ROWS_RETURNED, &labels, rows.len() as f64);
Ok(rows)
}
}
pub(super) fn bulk_primary_labels(operation: &str) -> [(String, String); 4] {
[
("operation".to_string(), operation.to_string()),
("pool".to_string(), "bulk_primary".to_string()),
("client".to_string(), current_client_name().to_string()),
("method".to_string(), current_method_name().to_string()),
]
}
const VERSION_FLOOR_OUTCOMES_TOTAL: &str = "personhog_replica_version_floor_outcomes_total";
fn record_floor_outcomes(operation: &str, outcomes: impl Iterator<Item = VersionFloorOutcome>) {
record_outcomes(
VERSION_FLOOR_OUTCOMES_TOTAL,
operation,
outcomes.map(|outcome| match outcome {
VersionFloorOutcome::TombstoneInserted => "tombstone_inserted",
VersionFloorOutcome::TombstoneRaised => "tombstone_raised",
VersionFloorOutcome::TombstoneAtFloor => "tombstone_at_floor",
VersionFloorOutcome::Live => "live",
}),
);
}
/// Count each outcome label once per call, so a batch costs one increment per label.
fn record_outcomes(
metric: &'static str,
operation: &str,
outcomes: impl Iterator<Item = &'static str>,
) {
let mut counts: HashMap<&'static str, u64> = HashMap::new();
for outcome in outcomes {
*counts.entry(outcome).or_default() += 1;
}
let client = current_client_name();
for (outcome, count) in counts {
common_metrics::inc(
metric,
&[
("operation".to_string(), operation.to_string()),
("outcome".to_string(), outcome.to_string()),
("client".to_string(), client.to_string()),
],
count,
);
}
}
pub(super) struct LockedPerson {
pub(super) id: i64,
pub(super) version: i64,
pub(super) is_deleted: bool,
}
/// Lock the existing persons among `uuids` in id order, the order the ingestion
/// writer and the tombstone paths take person locks in.
pub(super) async fn lock_persons_by_uuids(
tx: &mut Transaction<'_, Postgres>,
team_id: i64,
uuids: &[Uuid],
) -> StorageResult<HashMap<Uuid, LockedPerson>> {
let rows = sqlx::query!(
r#"
SELECT id::bigint as "id!", uuid as "uuid!",
COALESCE(version, 0)::bigint as "version!", is_deleted as "is_deleted!"
FROM posthog_person
WHERE team_id = $1 AND uuid = ANY($2)
ORDER BY id FOR UPDATE
"#,
team_id as i32,
uuids
)
.fetch_all(&mut **tx)
.await?;
Ok(rows
.into_iter()
.map(|row| {
(
row.uuid,
LockedPerson {
id: row.id,
version: row.version,
is_deleted: row.is_deleted,
},
)
})
.collect())
}
/// Insert scrubbed person tombstones, skipping any uuid a concurrent transaction
/// inserted first. Returns the inserted rows' ids by uuid.
async fn insert_person_tombstones(
tx: &mut Transaction<'_, Postgres>,
team_id: i64,
uuids: &[Uuid],
versions: &[i64],
) -> StorageResult<HashMap<Uuid, i64>> {
if uuids.is_empty() {
return Ok(HashMap::new());
}
let rows = sqlx::query!(
r#"
INSERT INTO posthog_person (uuid, team_id, properties, properties_last_updated_at,
properties_last_operation, created_at, version,
is_identified, is_deleted)
SELECT f.uuid, $1, '{}'::jsonb, '{}'::jsonb, '{}'::jsonb, NOW(), f.version, false, true
FROM UNNEST($2::uuid[], $3::bigint[]) AS f(uuid, version)
ON CONFLICT (team_id, uuid) DO NOTHING
RETURNING id::bigint as "id!", uuid as "uuid!"
"#,
team_id as i32,
uuids,
versions
)
.fetch_all(&mut **tx)
.await?;
Ok(rows.into_iter().map(|row| (row.uuid, row.id)).collect())
}
/// Tombstone the requested persons in one transaction, so the caller gets
/// exactly the versions committed, or none. Persons already tombstoned are
/// reported with the versions they hold, so a retry can republish them.
async fn tombstone_persons_by_uuids(
storage: &PostgresStorage,
team_id: i64,
uuids: &[Uuid],
client: &str,
) -> StorageResult<DeletePersonsOutcome> {
let mut tx = storage.bulk_primary_pool.begin().await?;
// A held row means a merge or revival in flight: fail fast and let the
// caller retry, as the tombstone drain does, instead of queueing behind it.
sqlx::query("SET LOCAL lock_timeout = '2s'")
.execute(&mut *tx)
.await?;
let (mark_op_id, claimed_ids) =
claim_lifecycle_marks(&mut tx, team_id, uuids, MarkKind::Delete).await?;
// Lock the claimed persons up front in id order, as the ingestion writer and the tombstone cleanup drain do.
// Lock by id, not uuid: a person re-created under a requested uuid after the claim holds no mark.
let rows = sqlx::query!(
r#"
SELECT id::bigint as "id!", uuid as "uuid!",
COALESCE(version, 0)::bigint as "version!", is_deleted as "is_deleted!"
FROM posthog_person
WHERE team_id = $1 AND id = ANY($2)
ORDER BY id FOR UPDATE
"#,
team_id as i32,
&claimed_ids
)
.fetch_all(&mut *tx)
.await?;
let mut tombstones: Vec<TombstonedPerson> = Vec::with_capacity(rows.len());
let mut already: Vec<(i64, Uuid, i64)> = Vec::new();
let mut live: Vec<i64> = Vec::new();
for row in rows {
if row.is_deleted {
already.push((row.id, row.uuid, row.version));
} else {
live.push(row.id);
}
}
let chunks: Vec<Vec<i64>> = live
.chunks(storage.bulk_chunk_size)
.map(|c| c.to_vec())
.collect();
common_metrics::histogram(
DB_BULK_CHUNKS,
&[("operation".to_string(), "tombstone_persons".to_string())],
chunks.len() as f64,
);
for chunk in &chunks {
// Per-person delete: also clear cohort memberships (no DB cascade).
tombstones
.extend(tombstone_persons_by_ids_in_tx(&mut tx, team_id, chunk, client, true).await?);
}
let deleted = tombstones.len() as i64;
if !tombstones.is_empty() {
let queued_uuids: Vec<Uuid> = tombstones.iter().map(|t| t.uuid).collect();
let queued_versions: Vec<i64> = tombstones.iter().map(|t| t.version).collect();
sqlx::query!(
r#"
INSERT INTO person_tombstone_publish_queue (team_id, person_uuid, person_version)
SELECT $1, t.person_uuid, t.person_version
FROM UNNEST($2::uuid[], $3::bigint[]) AS t(person_uuid, person_version)
ON CONFLICT (team_id, person_uuid) DO UPDATE
SET person_version = EXCLUDED.person_version,
tombstoned_at = now(),
attempts = 0,
last_attempt_at = NULL,
last_error = NULL,
given_up_at = NULL
WHERE EXCLUDED.person_version > person_tombstone_publish_queue.person_version
"#,
team_id as i32,
&queued_uuids,
&queued_versions
)
.execute(&mut *tx)
.await?;
}
tombstones.extend(with_tombstoned_distinct_ids(&mut tx, team_id, already).await?);
if let Some(op_id) = mark_op_id {
release_lifecycle_marks(&mut tx, team_id, op_id, MarkKind::Delete).await?;
}
tx.commit().await?;
tombstones.sort_by(|a, b| a.uuid.cmp(&b.uuid));
Ok(DeletePersonsOutcome {
deleted,
tombstones: Some(tombstones),
})
}
/// Which lifecycle operation a mark records. Every kind conflicts with every other on the same person.
#[derive(Debug, Clone, Copy)]
pub(super) enum MarkKind {
Delete,
Restore,
}
impl MarkKind {
fn op_type(self) -> &'static str {
match self {
MarkKind::Delete => "delete",
MarkKind::Restore => "restore",
}
}
fn role(self) -> &'static str {
match self {
MarkKind::Delete => "victim",
MarkKind::Restore => "target",
}
}
fn source(self) -> &'static str {
match self {
MarkKind::Delete => "personhog-replica-tombstone",
MarkKind::Restore => "personhog-replica-restore",
}
}
}
/// The person row lock cannot stop an ingestion attach, whose FK check passes on a tombstone, so the
/// attach re-checks liveness under this mark; claims go in id order, as ingestion's do, to avoid deadlock.
/// A mark held elsewhere fails the whole call, because the caller reads a missing person as already gone.
pub(super) async fn claim_lifecycle_marks(
tx: &mut Transaction<'_, Postgres>,
team_id: i64,
uuids: &[Uuid],
kind: MarkKind,
) -> StorageResult<(Option<Uuid>, Vec<i64>)> {
let persons = sqlx::query!(
r#"
SELECT id::bigint as "id!", uuid as "uuid!"
FROM posthog_person
WHERE team_id = $1 AND uuid = ANY($2)
ORDER BY id
"#,
team_id as i32,
uuids
)
.fetch_all(&mut **tx)
.await?;
if persons.is_empty() {
return Ok((None, Vec::new()));
}
let op_id = Uuid::new_v4();
sqlx::query!(
r#"
INSERT INTO lifecycle_op (op_id, op_type, team_id, step, request, completed_at)
VALUES ($1, $3, $2, 'completed', jsonb_build_object('source', $4::text), now())
"#,
op_id,
team_id as i32,
kind.op_type(),
kind.source()
)
.execute(&mut **tx)
.await?;
let person_ids: Vec<i64> = persons.iter().map(|p| p.id).collect();
let person_uuids: Vec<Uuid> = persons.iter().map(|p| p.uuid).collect();
let claimed = sqlx::query_scalar!(
r#"
INSERT INTO lifecycle_op_person (op_id, team_id, person_id, person_uuid, role, status, mark_active)
SELECT $1, $2, u.person_id, u.person_uuid, $5, 'marked', true
FROM unnest($3::bigint[], $4::uuid[]) AS u(person_id, person_uuid)
ON CONFLICT (team_id, person_id) WHERE mark_active DO NOTHING
RETURNING person_id
"#,
op_id,
team_id as i32,
&person_ids,
&person_uuids,
kind.role()
)
.fetch_all(&mut **tx)
.await?;
if claimed.len() != persons.len() {
return Err(StorageError::FailedPrecondition(format!(
"{} of {} persons are claimed by another lifecycle operation",
persons.len() - claimed.len(),
persons.len()
)));
}
Ok((Some(op_id), person_ids))
}
pub(super) async fn release_lifecycle_marks(
tx: &mut Transaction<'_, Postgres>,
team_id: i64,
op_id: Uuid,
kind: MarkKind,
) -> StorageResult<()> {
sqlx::query!(
r#"
DELETE FROM lifecycle_op
WHERE op_id = $1 AND team_id = $2 AND op_type = $3
"#,
op_id,
team_id as i32,
kind.op_type()
)
.execute(&mut **tx)
.await?;
Ok(())
}
/// Tombstone one chunk of persons inside the caller's transaction: their
/// rows get `is_deleted = true` and version + 1, properties scrubbed. The rows
/// stay so a later create on the same key revives above this version.
async fn tombstone_persons_by_ids_in_tx(
tx: &mut Transaction<'_, Postgres>,
team_id: i64,
person_ids: &[i64],
client: &str,
delete_cohortpeople: bool,
) -> StorageResult<Vec<TombstonedPerson>> {
if person_ids.is_empty() {
return Ok(Vec::new());
}
let chunk_labels = [
(
"operation".to_string(),
"tombstone_persons_chunk".to_string(),
),
("pool".to_string(), "bulk_primary".to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), current_method_name().to_string()),
];
let _chunk_timer = common_metrics::timing_guard(DB_QUERY_DURATION, &chunk_labels);
// Take the distinct-id row locks up front, in id order. The multi-row
// updates below lock in whatever order the plan visits rows, and the
// ingestion writer updates overlapping rows in sorted batches; sorted
// acquisition on both sides rules out a deadlock cycle.
sqlx::query!(
r#"
SELECT id FROM posthog_persondistinctid
WHERE team_id = $1 AND person_id = ANY($2)
ORDER BY id FOR UPDATE
"#,
team_id as i32,
person_ids
)
.fetch_all(&mut **tx)
.await?;
let tombstoned_dids = sqlx::query!(
r#"
UPDATE posthog_persondistinctid
SET is_deleted = true, version = COALESCE(version, 0) + 1
WHERE team_id = $1 AND person_id = ANY($2) AND is_deleted = false
RETURNING person_id as "person_id!", distinct_id as "distinct_id!", version as "version!"
"#,
team_id as i32,
person_ids
)
.fetch_all(&mut **tx)
.await?;
common_metrics::histogram(
DB_ROWS_RETURNED,
&[
(
"operation".to_string(),
"tombstone_distinct_ids".to_string(),
),
("pool".to_string(), "bulk_primary".to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), current_method_name().to_string()),
],
tombstoned_dids.len() as f64,
);
if delete_cohortpeople {
sqlx::query!(
r#"
DELETE FROM posthog_cohortpeople
WHERE person_id = ANY($1)
"#,
person_ids
)
.execute(&mut **tx)
.await?;
}
// The person row stays, so the FK cascade never fires; remove the overrides here.
sqlx::query!(
r#"
DELETE FROM posthog_featureflaghashkeyoverride
WHERE team_id = $1 AND person_id = ANY($2)
"#,
team_id as i32,
person_ids
)
.execute(&mut **tx)
.await?;
let tombstoned_persons = sqlx::query!(
r#"
UPDATE posthog_person
SET is_deleted = true,
version = COALESCE(version, 0) + 1,
properties = '{}'::jsonb,
properties_last_updated_at = '{}'::jsonb,
properties_last_operation = '{}'::jsonb
WHERE team_id = $1 AND id = ANY($2) AND is_deleted = false
RETURNING id as "id!", uuid as "uuid!", version as "version!"
"#,
team_id as i32,
person_ids
)
.fetch_all(&mut **tx)
.await?;
common_metrics::histogram(
DB_ROWS_RETURNED,
&[
("operation".to_string(), "tombstone_persons".to_string()),
("pool".to_string(), "bulk_primary".to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), current_method_name().to_string()),
],
tombstoned_persons.len() as f64,
);
Ok(build_tombstones(
tombstoned_persons
.into_iter()
.map(|row| (row.id, row.uuid, row.version))
.collect(),
tombstoned_dids
.into_iter()
.map(|row| (row.person_id, row.distinct_id, row.version))
.collect(),
))
}
async fn with_tombstoned_distinct_ids(
conn: &mut sqlx::PgConnection,
team_id: i64,
persons: Vec<(i64, Uuid, i64)>,
) -> StorageResult<Vec<TombstonedPerson>> {
if persons.is_empty() {
return Ok(Vec::new());
}
let person_ids: Vec<i64> = persons.iter().map(|(id, _, _)| *id).collect();
let dids = sqlx::query!(
r#"
SELECT person_id as "person_id!", distinct_id as "distinct_id!",
COALESCE(version, 0)::bigint as "version!"
FROM posthog_persondistinctid
WHERE team_id = $1 AND person_id = ANY($2) AND is_deleted = true
"#,
team_id as i32,
&person_ids
)
.fetch_all(&mut *conn)
.await?;
Ok(build_tombstones(
persons,
dids.into_iter()
.map(|row| (row.person_id, row.distinct_id, row.version))
.collect(),
))
}
/// Distinct ids are sorted by name so a retry reports the same list as the original call.
fn build_tombstones(
persons: Vec<(i64, Uuid, i64)>,
dids: Vec<(i64, String, i64)>,
) -> Vec<TombstonedPerson> {
let mut by_person: HashMap<i64, Vec<TombstonedDistinctId>> = HashMap::new();
for (person_id, distinct_id, version) in dids {
by_person
.entry(person_id)
.or_default()
.push(TombstonedDistinctId {
distinct_id,
version,
});
}
persons
.into_iter()
.map(|(id, uuid, version)| {
let mut distinct_ids = by_person.remove(&id).unwrap_or_default();
distinct_ids.sort_by(|a, b| a.distinct_id.cmp(&b.distinct_id));
TombstonedPerson {
uuid,
version,
distinct_ids,
}
})
.collect()
}
async fn delete_persons_by_ids_chunk(
pool: &PgPool,
team_id: i64,
person_ids: &[i64],
client: &str,
delete_cohortpeople: bool,
) -> StorageResult<i64> {
if person_ids.is_empty() {
return Ok(0);
}
let chunk_labels = [
(
"operation".to_string(),
"delete_persons_batch_for_team_chunk".to_string(),
),
("pool".to_string(), "bulk_primary".to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), current_method_name().to_string()),
];
let _chunk_timer = common_metrics::timing_guard(DB_QUERY_DURATION, &chunk_labels);
let mut tx = pool.begin().await?;
let rows =
delete_persons_by_ids_in_tx(&mut tx, team_id, person_ids, client, delete_cohortpeople)
.await?;
tx.commit().await?;
Ok(rows.persons)
}
/// Rows removed by one `delete_persons_by_ids_in_tx` call.
#[derive(Debug, Clone, Copy, Default)]
struct PersonRowsDeleted {
persons: i64,
distinct_ids: i64,
cohort_memberships: i64,
}
impl PersonRowsDeleted {
fn dependents(self) -> i64 {
self.distinct_ids + self.cohort_memberships
}
}
/// The delete statements every person delete path shares, run inside the caller's
/// transaction so a tombstone check can hold its row locks across them.
async fn delete_persons_by_ids_in_tx(
tx: &mut Transaction<'_, Postgres>,
team_id: i64,
person_ids: &[i64],
client: &str,
delete_cohortpeople: bool,
) -> StorageResult<PersonRowsDeleted> {
if person_ids.is_empty() {
return Ok(PersonRowsDeleted::default());
}
let mut rows = PersonRowsDeleted::default();
// Lock persons before their distinct ids, the order ingestion and the
// tombstone paths take, so a concurrent tombstone cannot deadlock with us.
sqlx::query!(
r#"
SELECT id FROM posthog_person
WHERE team_id = $1 AND id = ANY($2)
ORDER BY id FOR UPDATE
"#,
team_id as i32,
person_ids
)
.fetch_all(&mut **tx)
.await?;
// Delete distinct_id rows first — FK is NO ACTION.
let did_result = sqlx::query!(
r#"
DELETE FROM posthog_persondistinctid
WHERE team_id = $1 AND person_id = ANY($2)
"#,
team_id as i32,
person_ids
)
.execute(&mut **tx)
.await?;
rows.distinct_ids = did_result.rows_affected() as i64;
common_metrics::histogram(
DB_ROWS_RETURNED,
&[
(
"operation".to_string(),
"delete_distinct_ids_batch_for_team".to_string(),
),
("pool".to_string(), "bulk_primary".to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), current_method_name().to_string()),
],
did_result.rows_affected() as f64,
);
// Cohort memberships have no FK to posthog_person (the constraint was dropped
// during person-table partitioning), so they don't cascade — delete them
// explicitly for these persons. Gated because the team-teardown path already
// clears cohortpeople up front by cohort; only the per-person delete paths
// need this here.
if delete_cohortpeople {
let cohort_result = sqlx::query!(
r#"
DELETE FROM posthog_cohortpeople
WHERE person_id = ANY($1)
"#,
person_ids
)
.execute(&mut **tx)
.await?;
rows.cohort_memberships = cohort_result.rows_affected() as i64;
common_metrics::histogram(
DB_ROWS_RETURNED,
&[
(
"operation".to_string(),
"delete_cohortpeople_for_persons".to_string(),
),
("pool".to_string(), "bulk_primary".to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), current_method_name().to_string()),
],
cohort_result.rows_affected() as f64,
);
}
// Delete person rows (hash key overrides cascade at DB level).
let result = sqlx::query!(
r#"
DELETE FROM posthog_person
WHERE team_id = $1 AND id = ANY($2)
"#,
team_id as i32,
person_ids
)
.execute(&mut **tx)
.await?;
common_metrics::histogram(
DB_ROWS_RETURNED,
&[
(
"operation".to_string(),
"delete_persons_batch_for_team".to_string(),
),
("pool".to_string(), "bulk_primary".to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), current_method_name().to_string()),
],
result.rows_affected() as f64,
);
rows.persons = result.rows_affected() as i64;
Ok(rows)
}
/// Persons probed per statement while admitting a request.
const PROBE_BATCH_PERSONS: usize = 25;
/// Dependent rows of one tombstoned person, each count read with a `LIMIT`, so a probe never
/// costs more than that many index entries per table however many rows the person owns.
#[derive(Debug, Clone, Copy, Default, PartialEq, Eq)]
struct DependentRowCounts {
distinct_ids: i64,
hash_key_overrides: i64,
cohort_memberships: i64,
}
impl DependentRowCounts {
fn total(self) -> i64 {
self.distinct_ids + self.hash_key_overrides + self.cohort_memberships
}
}
/// Called by every exit that runs the resolve query. That query decides the counts, so the early
/// return for a call with no tombstoned candidate would otherwise hide live persons here.
fn record_tombstoned_delete_rows(outcome: &TombstonedDeleteOutcome, client: &str, method: &str) {
for (operation, value) in [
("delete_tombstoned_persons_deleted", outcome.deleted),
(
"delete_tombstoned_persons_skipped_live",
outcome.skipped_live,
),
(
"delete_tombstoned_persons_skipped_version",
outcome.skipped_version,
),
(
"delete_tombstoned_persons_blocked",
outcome.blocked_uuids.len() as i64,
),
(
"delete_tombstoned_persons_pending",
outcome.pending_uuids.len() as i64,
),
(
"delete_tombstoned_persons_rows_deleted",
outcome.rows_deleted,
),
] {
common_metrics::histogram(
DB_ROWS_RETURNED,
&[
("operation".to_string(), operation.to_string()),
("pool".to_string(), "bulk_primary".to_string()),
("client".to_string(), client.to_string()),
("method".to_string(), method.to_string()),
],
value as f64,
);
}
}
async fn probe_dependent_rows<'e, E>(
executor: E,
team_id: i64,
person_ids: &[i64],
limit: i64,
) -> StorageResult<HashMap<i64, DependentRowCounts>>
where
E: sqlx::Executor<'e, Database = Postgres>,
{
let rows = sqlx::query!(
r#"
SELECT p.id AS "id!",
(SELECT count(*) FROM (SELECT 1 FROM posthog_persondistinctid
WHERE team_id = $1 AND person_id = p.id LIMIT $3) t) AS "distinct_ids!",
(SELECT count(*) FROM (SELECT 1 FROM posthog_featureflaghashkeyoverride
WHERE team_id = $1 AND person_id = p.id LIMIT $3) t) AS "hash_key_overrides!",
(SELECT count(*) FROM (SELECT 1 FROM posthog_cohortpeople
WHERE person_id = p.id LIMIT $3) t) AS "cohort_memberships!"
FROM unnest($2::bigint[]) AS p(id)
"#,
team_id as i32,
person_ids,
limit
)
.fetch_all(executor)
.await?;
Ok(rows
.into_iter()
.map(|row| {
(
row.id,
DependentRowCounts {
distinct_ids: row.distinct_ids,
hash_key_overrides: row.hash_key_overrides,
cohort_memberships: row.cohort_memberships,
},
)
})
.collect())
}
/// Decides, in id order, which candidates one call deletes whole. A person whose dependent rows
/// fit the leftover budget is admitted, up to `max_persons`; the first that does not fit is kept
/// for the trim step; every other candidate is pending. Small persons never wait behind a big one.
#[derive(Debug)]
struct Admission {
remaining: i64,
max_persons: usize,
admitted: Vec<(i64, Uuid)>,
trim: Option<(i64, Uuid)>,
pending: Vec<Uuid>,
}
impl Admission {
fn new(budget: i64, max_persons: usize) -> Self {
Self {
remaining: budget,
max_persons,
admitted: Vec::new(),
trim: None,
pending: Vec::new(),
}
}
fn wants_more(&self) -> bool {
self.admitted.len() < self.max_persons
}
/// One more than the leftover, so a count at the limit reads as "does not fit".
fn probe_limit(&self) -> i64 {
self.remaining + 1
}
fn offer(&mut self, id: i64, uuid: Uuid, counts: DependentRowCounts) {
let total = counts.total();
if self.wants_more() && total <= self.remaining {
self.remaining -= total;
self.admitted.push((id, uuid));
return;
}
if self.trim.is_none() && total > self.remaining {
self.trim = Some((id, uuid));
}
self.pending.push(uuid);
}
fn defer(&mut self, uuids: impl IntoIterator<Item = Uuid>) {
self.pending.extend(uuids);
}
}
/// Deletes up to `budget` dependent rows of a person the caller holds locked: distinct ids
/// first, then hash key overrides, then cohort memberships. Returns the rows deleted, or `None`
/// when a live distinct id was found, in which case nothing of this person is deleted.
async fn trim_locked_person(
tx: &mut Transaction<'_, Postgres>,
team_id: i64,
person_id: i64,
budget: i64,
) -> StorageResult<Option<i64>> {
// No is_deleted filter: the scan then visits at most `budget` index entries, and a live
// mapping among them means ingestion can still reach the person.
let mappings = sqlx::query!(
r#"
SELECT id::bigint AS "id!", is_deleted AS "is_deleted!"
FROM posthog_persondistinctid
WHERE team_id = $1 AND person_id = $2
LIMIT $3
FOR UPDATE
"#,
team_id as i32,
person_id,
budget
)
.fetch_all(&mut **tx)
.await?;
if mappings.iter().any(|row| !row.is_deleted) {
return Ok(None);
}
let mut remaining = budget;
let mut deleted = 0i64;
if !mappings.is_empty() {
let ids: Vec<i64> = mappings.iter().map(|row| row.id).collect();
let n = sqlx::query!(
"DELETE FROM posthog_persondistinctid WHERE team_id = $1 AND id = ANY($2)",
team_id as i32,
ids.as_slice()
)
.execute(&mut **tx)
.await?
.rows_affected() as i64;
deleted += n;
remaining -= n;
}
if remaining > 0 {
let ids: Vec<i64> = sqlx::query_scalar!(
r#"
SELECT id::bigint AS "id!"
FROM posthog_featureflaghashkeyoverride
WHERE team_id = $1 AND person_id = $2
LIMIT $3
"#,
team_id as i32,
person_id,
remaining
)
.fetch_all(&mut **tx)
.await?;
if !ids.is_empty() {
let n = sqlx::query!(
"DELETE FROM posthog_featureflaghashkeyoverride WHERE team_id = $1 AND id = ANY($2::bigint[])",
team_id as i32,
ids.as_slice()
)
.execute(&mut **tx)
.await?
.rows_affected() as i64;
deleted += n;
remaining -= n;
}
}
if remaining > 0 {
let ids: Vec<i64> = sqlx::query_scalar!(
r#"
SELECT id::bigint AS "id!"
FROM posthog_cohortpeople
WHERE person_id = $1
LIMIT $2
"#,
person_id,
remaining
)
.fetch_all(&mut **tx)
.await?;
if !ids.is_empty() {
deleted += sqlx::query!(
"DELETE FROM posthog_cohortpeople WHERE id = ANY($1)",
ids.as_slice()
)
.execute(&mut **tx)
.await?
.rows_affected() as i64;
}
}
Ok(Some(deleted))
}
#[cfg(test)]
mod tests {
use super::{Admission, DependentRowCounts};
use uuid::Uuid;
fn counts(
distinct_ids: i64,
hash_key_overrides: i64,
cohort_memberships: i64,
) -> DependentRowCounts {
DependentRowCounts {
distinct_ids,
hash_key_overrides,
cohort_memberships,
}
}
fn offer_all(admission: &mut Admission, persons: &[(i64, DependentRowCounts)]) -> Vec<Uuid> {
let uuids: Vec<Uuid> = persons.iter().map(|_| Uuid::new_v4()).collect();
for ((id, c), uuid) in persons.iter().zip(&uuids) {
admission.offer(*id, *uuid, *c);
}
uuids
}
#[test]
fn admits_what_fits_and_keeps_the_first_misfit_for_the_trim() {
let mut admission = Admission::new(6, 100);
let persons = [
(1, counts(2, 1, 0)), // fits, 3 left
(2, counts(4, 0, 0)), // misfit: the trim candidate
(3, counts(0, 0, 3)), // fits exactly, 0 left
(4, counts(5, 0, 0)), // misfit, but the trim slot is taken
(5, counts(0, 0, 0)), // still fits with nothing left
];
let uuids = offer_all(&mut admission, &persons);
assert_eq!(
admission.admitted,
vec![(1, uuids[0]), (3, uuids[2]), (5, uuids[4])]
);
assert_eq!(admission.trim, Some((2, uuids[1])));
assert_eq!(admission.pending, vec![uuids[1], uuids[3]]);
assert_eq!(admission.remaining, 0);
assert_eq!(admission.probe_limit(), 1);
}
#[test]
fn the_person_cap_defers_the_rest_without_choosing_a_trim() {
let mut admission = Admission::new(10, 2);
let persons = [
(1, counts(0, 0, 0)),
(2, counts(1, 0, 0)),
(3, counts(0, 0, 0)),
];
let uuids = offer_all(&mut admission, &persons);
assert!(!admission.wants_more());
let deferred = Uuid::new_v4();
admission.defer([deferred]);
assert_eq!(admission.admitted, vec![(1, uuids[0]), (2, uuids[1])]);
assert_eq!(admission.trim, None);
assert_eq!(admission.pending, vec![uuids[2], deferred]);
assert_eq!(admission.remaining, 9);
}
#[test]
fn a_misfit_over_the_cap_is_still_the_trim_candidate() {
let mut admission = Admission::new(3, 1);
let persons = [(1, counts(0, 0, 0)), (2, counts(4, 0, 0))];
let uuids = offer_all(&mut admission, &persons);
assert_eq!(admission.admitted, vec![(1, uuids[0])]);
assert_eq!(admission.trim, Some((2, uuids[1])));
assert_eq!(admission.pending, vec![uuids[1]]);
}
}