Data Architect Fix a High-Volume TikTok Analytics Pipeline

The problem Our TikTok pipeline has twice now exhausted our operational PostgreSQL database’s memory, forcing us to pause the ETL so portal users aren’t affected. Two things are going wrong at once: Volume. We retain daily per-post history to compute the metrics the portal displays. Compute. The metric recalculations run against the same database that serves the application. Scaling the database instance up would roughly double our hosting cost, so we’d rather fix the design. We’re not building a sophisticated analytics product: the input volume is large, but the output our users actually see is a small set of aggregated metrics per creator. Scope of work Phase 1: Assessment and recommendation Review the TikTok schema, the ETL, the metric calculations and our usage patterns, then deliver a written recommendation covering: Approach you recommend and why, with the trade-offs of the ones you rejected What data we genuinely need to retain versus what we can discard or downsample, given the metrics we display If a separate store is warranted: the engine (ClickHouse, partitioned PostgreSQL, TimescaleDB or other), the schema and how aggregates reach the portal database How the cross-database relationships and the planned influencer join would work under your design A sizing estimate for how long the fix holds as TikTok data keeps growing Phase 2: Implementation Build what we agree on. Depending on the approach that will include some mix of: schema and retention changes, moving the ETL and recalculation off the operational database, incremental aggregation to replace full recalculation, the aggregate sync into the portal’s PostgreSQL, monitoring and alerting for pipeline lag, and documentation that lets our data engineer own it going forward. Success looks like: the TikTok pipeline runs continuously without affecting portal performance, metric recalculation finishes in a predictable window, and we can scale our operational database back down. Required skills Strong PostgreSQL: partitioning, memory and query tuning, replication, ETL patterns between operational and analytical stores Analytics data modeling at scale: columnar stores, time-series data, incremental aggregation, retention and downsampling strategy ClickHouse, TimescaleDB or a comparable analytical engine in production (not just evaluated) Python ETL (our pipelines are Python) Judgment about maintenance burden; we’d rather have a simple system we can run than an elegant one we can’t Clear written English and the ability to explain trade-offs to a technical founder Nice to have Social-media or marketing analytics data of a similar shape Experience working alongside an existing dev team rather than replacing them Self-hosted deployment experience (we’re moving off AWS onto a dedicated server in parallel) To apply, please tell us How you’d decide between discarding raw history and building a separate analytics store, for a pipeline whose end product is a small set of per-creator metrics A comparable system you’ve built: data volume, engine, and how the aggregates reached the application What you’d look at first in our setup to form a view Your availability over the next 6 weeks and your rate