How to materialize it in roadmap and keep only what is important?
Idea is good, but the devil usually hides in the details and you want to uncover it ASAP.
MVP should help you assess demand and validate your product hypothesis.
In order to convert your idea to a list of action items – it is necessary to sum up what we are about to build – functional and non-functional requirements as well as known constraints. We should reflect there not only what we want to create, but also a list of things that we do not want to have in the initial version.
to collect functional/non-functional requirements we firstly outline answers to several questions:
Who – our users – roles/persona
Why – unique value proposition = problem to solve (better than anything else at the market)
What – solution to pressing problem. How to check if it is pressing? especially if there is no product yet? There are a number of ways – but my favorite one is Concierge MVP – you manually do all the things yourself to catch all nuances and how you will automate it with tech later and assess demand.
When/Where – to document our market hypothesis and prepare for GTM
Then we explore existing constraints – usually resource based – time, budget, people, …
At the next stage we should zoom in into our solution and prepare a list of use cases per user role. No need to worry about complexity or implementation yet – just offload everything to have a clear picture how all things should work in an ideal world.
Functional wireframes – document every step of user’s journey for every scenario – it is not about design – paper’s memo with text or Miro’s stickers more than enough.
Then it is handy to better understand value vs complexity. What functions will be part of the initial version? We can grade every use case with two values – business impact and technical complexity. That is a right time to start thinking about a solution to uncover the volume of work required for specific tasks within a future product.
Note, that feature driven development might be too costly – hence better to stick with core functionality – when users come – you should have much more resources to extend your product.
It helps us to sketch several courses of actions – Hows – with various trade offs and still do one thing better than all other solutions.
Q: What if someone steals my idea?
A: Don’t be afraid to share it. Execution – is all that really matters.
“Stealth” startups are missing a crucial point – feedback from real users. The faster you start absorbing it and learn from real life scenarios – the more you have chances to succeed.
Patents take time and a lot of it! in addition to money. NDA or non-compete might be more than enough to get things started rolling.
Building a successful company is about much much more than the idea (С) YCombinator
Q: Should you hire a sub-contractors to build your dream?
A: Most likely not:
We don’t recommend hiring a devshop or contractor to build a v1 you do need a co-founder who can and will write code
(C) YCombinator
And at the same time an independent point of view can save you tons of time (hence money).
I’ve seen people spend months(!) fighting with no-code tools instead of weeks worth of development efforts or choosing not appropriate tech stack choosing short-term gains in exchange of unavoidable burden of maintenance during ideation phase.
Few observations in regards to hash usage, in projects where data volume and spikes in load shift engineering from the KISS paradigm to the extent where you have to really care about underlying technologies and data structures.
There are different hash functions tailored for different use cases – quick pattern matching in Rabin-Karp algorithm, prefix search of geohash, HMAC in JWT token generation or CRC checks:
sometimes you may want that similar input grouped together maybe by producing similar hash values – Locality-Sensitive Hashing
sometimes you worry about collisions or want to encrypt original values – Cryptographic Hash Functions
in other cases your focus is to compute unique key for input values fast enough – Simple hashing
maybe you need to check integrity or authenticity for some payload with arbitrary size and type – special hash functions
Lets say you have to manage some records, in a read-heavy environment with ocasional writes, that sometimes may happen in big batches. Few dimensions of such data fall under PII/GDPR – hence have to be anonymised. At the same time we want to search entries using those anonymised fields. Initial implementation was built computing its MD5 fingerprint – simple and fast.
When number of entries exceed 7 billions, one of asserts, related to duplicates was triggered. Collision occurred.
In usual hash function probability of collision depends on the ranges of possible keys. I.e. if keys are unsigned int with 4 bytes – number of possible values is 2^32. In an ideal world – there should be a number of unique elements that this function can hash before first collision, in reality a perfect hash function is easy to build only for a known set of keys. Gperf and similar minimal hash function libraries can help with it, otherwise hash functions aim only to minimize collisions, while focusing on other aspects of operations – performance and different input.
And as a consequence, probability theory strikes back with the Birthday paradox – as with every new generated key we increase the probability of collision for the next key. For the example above, after we created 2^16 keys – just 65k(!) – for every new key we will have a 50% chance of collision.
MD5 is cryptographic hash function with key range of 2^128, cryptographic functions aim to:
making hard to recover an input string that produce given hash
making even harder to find different input that resolve to the same hash – i.e. decrease probability of collision
under the hood we took hash from the hash from the hash … – hence single letter difference _should_ produce avalanche effect – when similar input produce hashes that didn’t have anything common
Almost 20(!) years ago was reported first collision in MD5, which was considered a vulnarability in respect to usage for cryptographic application. And it still widely used! Recently stumble upon publication in regards of its usage by internet providers as part of RADIUS protocol.
SHA-1 was reported to have collisions back in 2017.
Currently SHA-2 or SHA-3 are the recommended crypto hash functions to avoid headache of backfilling datasets and updating client’s code. Dead letter queue – is a convenient mechanism for handling collisions – to persist messages that can’t be further processed with existing app’s logic.
Another part of the story is an app cache. When you have a read only dataset it is straightforward – fill a hash table in your favorite language. What if data can’t fit into memory? Rule of thumbs is to cache 5-20% percent of data. Depending on access patterns you need to choose when to add new entries and when to get rid of existing values. There are various cache eviction strategies apart of classical least recently used (LRU) or least frequently used (LFU) :
random replacement
expiry based eviction – TTL
basic mix of LRU and LFU segmented LRU or adaptive replacement cache (ARC)
taking into account other factors – access frequency within time window, value’s sizes, value’s weight when some entries are more important than others, etc
And then you start to gradually fill the hashtable, load growth and suddenly you hit noticeable delays when trying to insert new entries. How come?! Average insert time is O(1) but what about worst? What the heck amortized cost means?
Many languages implement hash tables as dynamic arrays – hash function compute index and store key and value in corresponding cell. When the number of entries in array exceed the load factor – a new array is allocated and all existing keys are copied and rehashed into it. So if we already have few hundreds millions of entries – OS would have a lot of work to do. In some languages it also means iterators or references become invalid. To be fair is worth noting that sometimes re-hashing is spread over-time to minimize such drastic performance drop.
To tackle it we tune:
capacity – to reserve big enough, contiguous chunk of memory from the start
load factor – to control when it should be reallocated (if any)
however note that it in some cases it serve as a hint and implementation might do what it think is right
if you know your data – it might be handy to specify your own hash function
let me reiterate – perfect hashing can be a great option if keyset is fixed and known in advance
There is also an option to use more optimized data structures like a Slot map – that is cache friendly and guarantee worst case constant time for insert and lookups.
Overview of available type of indexes in different database engines and corresponding use cases.
So, you have some data. Most likely you want to work with it – i.e. retrieve information according to some conditions = filtering what is important for you. Probably, even change the shape of this information – transform the data or get summary over it – i.e. apply some aggregations.
Usual question that arise – how to organize data schema – which db engine and indexes to choose. Here I want to provide a brief overview of different kinds of indexes available in modern databases.
When data volume is small – i.e. less than a few millions of rows – it usually doesn’t really matter – even fullscan will be sufficient for the majority of use cases.
With growth of data and load = number of read and write operations – it becomes paramount to structure your data in a way that facilitates processing without the need to read every row from the disk.
Data might be stored just in RAM memory or have a persistent layer at disk. You might have different requirements for data access depending on its age – i.e. ability to quickly deep dive into data from the last 3 months and keep 5 years worth of data in cold storage to comply with regulatory rules.
Data can take different shapes:
numbers of various sizes and precisions from uint8 to float point with configurable precision
enum-like categories from fixed list of possible dimensions
sequences of DNA or community relationships
tree-like hierarchy with number of nested fields
big binary blobs – like images or video
geometrical figures – line’s segments or polygons
gis info – locations and coordinates
text fields – fixed length strings with hash values or variable length texts, with some articles
simple key-values with types that you know upfront
complex documents, where you have no idea which attributes provider might add next day
To simplify, indexes, at the end – are search trees and hash maps.
Data are tend to it evolve over time – new fields or dimensions might be added, old – change their type or cardinality or just disappear.
Query – mean you specify some filter criteria to pull only necessary data points. Filter criterias = we need to perform search or lookups. Hence data is organized in some structures that facilitate its retrieval according to specified conditions.
Golden rule of data schema design – define your schema around queries.
Let’s dive into the diversity of available indexes – how data type and query type affect choice of index type (and sometimes even database engine).
B-Tree, and in many case its ancestor – B+-Tree is the most universal type of index – used for majority RDBMS databases as a default index type (from SQLite to MS SQL) as well as for NoSQL (from ScyllaDB to Aerospike).
Used to filter based on equality (==, <>, !=), compare – (>, <,>=, <=) and range conditions – BETWEEN.
Can be used with almost any data type – from numbers and strings, arrays or fields inside nested structures.
Hash based index is ideal choice for cases when you need to check for exact match – equality conditions (==, <>, !=) – faster then B-Tree based indexes.
Fit almost any simple data type, but not suitable for composite entities, nested documents or collections, or cases where more complex filtering condition is required – prefix or range searches.
Variations of inverse indexes are handy in case we work with textual data. Specialized engines – Sphinx or Elasticsearch can go the extra mile with stem and n-grams. And in case your data can be expressed as a numerical vector = feature vectors – ANN/KNN kind of queries can be used to rank results in terms of distance to target queries – to express similarity relationship.
Spatial data – geometry figures or locations require special treatment and better suited for indexes tailored for typical kind of queries – proximity search, bounding box or containment tasks served best with multidimensional trees.
It is possible to create indexes on top of results for some functions execution – `upper` for strings or convert epoch based number into timestamp – using expression or function based indexes.
Collections – arrays or sets usually require various logic to check for overlaps or membership as well as checking specific elements is present – for that best suited multikey or inverted indexes – but sometimes expression based indexes can be handy as well.
Semi-structural data – when we are not sure about schema of documents and every next entry might have some deviations – best served with indexes from these groups as well as at the end it is an intermix of array, text and probably expression and vector representations.
Graph and hierarchical relationship can be expressed in RDBMS terms – at the end all of it are vertices and edges – but for efficient traversals special types of indexes might be much more beneficial – Graph or Hierarchical.
OLAP and columnar type of database engines, introduce various tricks to efficiently deal with big data volume across multiple dimensions – mainly to skip non-relevant data partitions and be able to get a rough understanding of data statistics for a chunk of storage.
Wide-column NoSQL engines tend to rely on secondary indexes, sometimes stored locally within data nodes to facilitate filtering of non-primary keys or take advantages of sorting order.
Database Index Selection Guide
Structured Data
Type of Data
Type of Query
Type of Index
Supported DB Engines
Numbers
Equality, Range
B-tree, Hash, GiST, BRIN, Expression, Skip, Zone Map
My ultimate SQL Memo (and why I need one as AI everywhere 😀 )
Majority of apps are built around data. We applaud (or pray) for data-driven decisions. And, sometimes, have no idea about what we store (sometimes though we think we know — which probably even worse).
Why is AI not there (yet)?
AI nowadays definitely raise the expectation’s bar to a new level — sometimes I shocked when people have a blind faith in the AI. This hope comparable to some magic wand, that allow to retrieve precious pearls of insights from the deepest trenches of the data sea.
It is lovely to have some solid looking UI of Google’s Looker or AWS Quick Insights or some custom-made LLM with fine-tuned RAG on top of company’s data — but what would be its output if we are dealing with raw data — with all its defects? What if those data are from different sources — where silly issues like mismatched timestamps (UTC and local timezone) can quite efficiently distort the whole picture? What about situations where identity resolutions are only in plans?
Conclusions based on malformed data leads to wasteful expensive actions. Old good saying — garbage in — garbage out.
One of the first step to probe the data — is EDA — exploratory data analysis — aka “What the hell I am dealing with?!” — exciting journey which sometimes lead to amazing (and not so) discoveries. How to quickly grasp the gist of the data? Compute common statistics — distributions of values — which sometimes lead to amazing (or not so) surprises.
And it is often an issue — recently I have done a lot of EDA — and was surprised about many things, that people tend to do outside of data layer.
Why?
Because they do not know what is actually possible in old good sql. For sure, SQL have its own issues — verbosity, challenges with testing — but for quick probe on the data surface — what can be better? Pull something as csv in pandas and play locally? (Not the worst idea by the way!)
Moreover, many things already exist to help with it – given prompt and schema— TEXT2SQL or RAG2SQL can generate a query for you. NLQ (natural language queries) engines sometimes on par to seasoned DBAs.
What if data is big? LLM can generate PySpark code for you – as in pyspark-ai project – (especially if you share hints in regards of schema). You can combined it with unstruct — it try to derive the schema from unstructured data, and build corresponding pipelines to Extract-Transform-Load.
Automatic EDA is also possible – YData profiling — connect to data source — pandas or spark dataframe – and it will compute standard metrics, priceless when data is fully terra incognito — i.e. it is your first encounter.
But then questions arise: How those data points produced? How right data looks like? End even more important – how wrong data looks like? What if there are several sources of data? How are they connected?
In all those cases you have to know what you can ask. How to ask – less important, especially now, when we have number of AI assistants.
What SQL can do?
Find myself too lazy to memorize exact syntax, instead I am trying to remember what conceptually is possible. Examples below not necessary ANSI SQL — it works in PostgreSQL/Google Big Query — for vanilla SQL some specific tweaking might be necessary — but overall you can do same things.
Let’s start with aggregate functions — first step to get statistics over dataset for EDA.
Percentile is helpful to grasp the gist of particular value distribution, and variance and deviation highlights how data is spread:
SELECT
direction_id,
MIN(orders_per_sec) AS min_orders,
MAX(orders_per_sec) AS max_orders,
AVG(orders_per_sec) AS avg_orders,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY orders_per_sec) AS median_orders,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY orders_per_sec) AS p25_orders,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY orders_per_sec) AS p75_orders,
VAR_POP(orders_per_sec) AS population_variance,
-- or VAR_SAMP(orders_per_sec) AS sample_variance,
STDDEV_POP(orders_per_sec) AS population_std_dev,
-- or STDDEV_SAMP(orders_per_sec) AS sample_std_dev
FROM
order_counts
GROUP BY
direction_id
Histograms like view of data distribution accros the bin – is great help to identify outliers (in the example below with 3-sigma rule):
WITH stats AS (
SELECT
AVG(numeric_column) AS avg_value,
STDDEV(numeric_column) AS std_dev,
MIN(numeric_column) AS min_value,
MAX(numeric_column) AS max_value
FROM your_table
),
bucketed_data AS (
SELECT
*,
WIDTH_BUCKET(
numeric_column,
(SELECT min_value FROM stats),
(SELECT max_value FROM stats),
10 -- number of buckets
) AS bucket,
(SELECT avg_value FROM stats) AS avg_value,
(SELECT std_dev FROM stats) AS std_dev
FROM your_table
)
SELECT
*,
CASE
WHEN numeric_column > avg_value + 3 * std_dev THEN 'High Outlier'
WHEN numeric_column < avg_value - 3 * std_dev THEN 'Low Outlier'
ELSE 'Normal'
END AS outlier_status
FROM bucketed_data
ORDER BY bucket, numeric_column;
Hierarchical aggregation is possible via old good Group by clause — i.e. given the sales data:
make | model | sales
-------|---------|------
Toyota | Camry | 500
Toyota | Camry | 500
Toyota | Corolla | 300
Toyota | Corolla | 500
Honda | Civic | 400
Honda | Civic | 500
Honda | Accord | 600
Honda | Accord | 500
we want to have sum of sales per make and model and their combinations — then use GROUPING and ROLLUP/CUBE:
SELECT
make,
model,
GROUPING(make, model),
sum(sales)
FROM
items_sold
GROUP BY ROLLUP(make, model);
-- or CUBE if we need include make as NULL
Note, that you can combine aggregate functionswith conditions or take into account only unique values:
COUNT(CASE WHEN fk_role = 1 AND fk_status = -13 THEN 1 END) AS removed_users,
AVG(CASE WHEN order_count > 1 THEN order_total ELSE NULL END),
COUNT(DISTINCT user_id) AS distinct_user_count
Wanna check whether two or more variables are related — compute whole covariance matrix:
SELECT
group_id,
COVAR_SAMP(x1, x1) AS cov_x1_x1,
COVAR_SAMP(x1, x2) AS cov_x1_x2,
COVAR_SAMP(x2, x2) AS cov_x2_x2
FROM
data_points
GROUP BY
group_id;
Some DB engines provide a way to compute coefficients of linear regression — machine learning in SQL? Nah, just simple check effectiveness of advertising per region — and whether it explain sales number at all:
SELECT
region,
REGR_SLOPE(sales, advertising_spent) AS slope,
REGR_INTERCEPT(sales, advertising_spent) AS intercept,
REGR_R2(sales, advertising_spent) AS r_squared
FROM
sales_data
GROUP BY
region;
You can use window functions to trace the trends over data points: Rank regular patients that had more than 5 appointments by number of appointments (or dense_rank — depending how you want treat similar values):
SELECT
patient_id,
COUNT(appointment_id) AS total_appointments,
SUM(amount) AS total_amount,
RANK() OVER (ORDER BY COUNT(appointment_id) DESC) AS appointment_rank
--, DENSE_RANK() OVER (...) AS appointment_rank
FROM
dental_appointments
GROUP BY
patient_id
HAVING
COUNT(appointment_id) > 5
AND SUM(amount) > 500;
get the most recent appointment per patient
SELECT
patient_id,
appointment_id,
appointment_date,
ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY appointment_date DESC) AS rn
FROM
dental_appointments
WHERE
status = 'completed'
ORDER BY
patient_id, rn;
or to see trends in scheduling appointment, show previous and next appointment for every row:
SELECT
patient_id,
appointment_id,
appointment_date,
LAG(appointment_date, 1) OVER (PARTITION BY patient_id ORDER BY appointment_date) AS previous_appointment,
LEAD(appointment_date, 1) OVER (PARTITION BY patient_id ORDER BY appointment_date) AS next_appointment
FROM
dental_appointments
ORDER BY
patient_id, appointment_date;
Note the difference:
OVER (ORDER BY date) — apply function to the whole dataset, but not limiting the columns in resultset as in GROUP BY clause
OVER (PARTITION BY customer_id ORDER BY order_date) — apply function to the data in every bucket = partition independently
You also can compute percentile — CUME_DIST — using moving window — for example within specific time frame (30 days in example below):
SELECT
date,
stock_symbol,
price,
CUME_DIST() OVER (
PARTITION BY stock_symbol
ORDER BY price
ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
) AS price_position_30day
FROM
stock_prices
ORDER BY
stock_symbol,
date;
Subqueries are created not only to create confusing code, you can copy of row with specific data:
INSERT INTO my_table_name (
field_1,
... ,
field_n
)
SELECT -- 1
field_1,
... ,
field_n * 10 -- 2
FROM my_table_name
WHERE id = ( -- 3
SELECT id
FROM my_table_name
WHERE name = 'John'
AND last_name = 'Doe'
LIMIT 1
) AND
NOT EXISTS ( -- 4
SELECT
1
FROM
some_other_table where fk_key_field = 42 -- 5
)
1 — using subquery to select necessary fields
2 — changing original value
3 — using subquery to select id of row
4 — using subquery to check that precondition is met for insertion
If boilerplate of subqueries feels too scary — it is time bring CTE (Common Table Expression) into play — to organize it in a bit more procedural way — split your data wrangling in stages — divide and conquer (conquer complexity and your data!)
Recursive query — is your friend if you are dealing with hierarchical data — if entities inside table have relationship to each other — no need (yet) for dedicated mapping table and data migrations. Yep, just for tree or connection’s graph traversal (and not so perfect schema).
-- descendants
-- id parent_id name
-- 1 NULL John
-- 2 1 Mary
WITH RECURSIVE family_tree AS (
-- Base case: start with the root ancestor
SELECT
id,
parent_id,
name,
0 AS generation -- Root starts at generation 0
FROM
descendants
WHERE
id = 1 -- Starting with John (id = 1)
UNION ALL
-- Recursive case: find all descendants of the previous level
SELECT
d.id,
d.parent_id,
d.name,
ft.generation + 1 AS generation -- Increment generation for each descendant
FROM
descendants d
INNER JOIN
family_tree ft ON d.parent_id = ft.id
)
-- Final selection: retrieve the complete family tree
SELECT
id,
parent_id,
name,
generation
FROM
family_tree
ORDER BY
generation, id;
those operations are similar to ordinal boolean logic — UNION ~ OR (UNION ALL allowing duplicates though), INTERSECT ~ AND and EXCEPT — difference:
A = 13 # Binary: 1101
B = 6 # Binary: 0110
# A AND NOT B
result = A & ~B # 9 (Binary: 1001)
Schema of queries should match — but you can cast types, use alias and shape data as you need. Un-nest specific field from struct in array inside a json field or pivot columns to rows and vice versa.
So, lets say I have couple of tables:
cities with zip_codes
clinics details
list of demanded treatments
appointment details
And what I want to understand is demand distribution across different parts of city:
SELECT DISTINCT
ar.request_details->>'clinic_id' AS unique_clinic_id, -- 1
array_agg(DISTINCT t.name) AS treatment_names, -- 2
ar.request_details->>'treatment_date' AS treatment_date,
ar.request_details->>'appointment_time' AS appointment_time,
date(ar.created_at) AS created_date, -- 3
ar.patient_id,
zc.city,
zc.zip_code
FROM
appointment_requests ar
JOIN
clinics c
ON
ar.request_details->>'clinic_id' = c.clinic_id::text -- 4
JOIN
zip_codes zc ON c.zip_code_id = zc.zip_code_id
LEFT JOIN LATERAL -- 5
jsonb_array_elements_text( -- 6
ar.request_details->'required_treatments'
) AS req_treatment(treatment_id)
ON true
LEFT JOIN
treatments t ON t.treatment_id = req_treatment.treatment_id::integer
WHERE
EXTRACT(EPOCH FROM (ar.updated_at - ar.created_at)) > 60*60 -- 7
AND ar.status = 'pending'
AND ar.patient_id NOT IN (-1, 20000)
GROUP BY
ar.request_details->>'clinic_id',
ar.request_details->>'treatment_date',
ar.request_details->>'appointment_time',
date(ar.created_at),
ar.patient_id,
zc.city,
zc.zip_code
;
1 — we extracting some fields from json’s structure field
2 — we combine all unique values into array
3 — we extract date from timestamp
4 — we use field from json in join condition casting some other value to string
5 — we expand every value from array appointment_requests
6 — into independent row = equal to number of treatment_id from json array
7 — which were updated in the last hour
JOINing tables might be not necessary the most performant operations, but in the 3NF world it is the common tool to organize the data. Simple illustration where rows fall for OUTER/INNER/LEFT and RIGHT:
Just bear in mind of possibility of duplicates when joining multiple tables — in situations when you have main entity and several related tables with one-to-many relationship:
Let’s say we have a user with: — 2 occupations — 3 addresses — 2 phone numbers
and this naive query — get you a Cartesian product of those multiple entries = 2 (occupations) × 3 (addresses) × 2 (phone numbers) = 12 rows for this single user:
SELECT
u.name,
o.occupation,
a.address,
p.phone_number
FROM
users u
JOIN occupations o ON u.id = o.user_id
JOIN addresses a ON u.id = a.user_id
JOIN phone_numbers p ON u.id = p.user_id;
Also don’t forget about self joins, for cases like finding duplicates or searching for next/prev values or missing entries in the sequential data!
Just bear in mind what is possible — you can always lookup syntax when needed (and it will stick with you through repetition — i.e. when it is really needed)
SQL methods and functions memo
Examples below are for PostgreSQL, with all its issues (real or not) and possible workarounds . Generally, I would agree with moto “Use postgres for everything!”. Rich type system and type specific functions allow you to transform data to desired domain. It is literally like programming languages:
Math
bitwise operations, cube root and exponent
logarithms and radian to degree
random (sampling and shuffling, for array as well)
width_bucket — fill histograms for you
String
all tools that you need for data cleansing:
trim/substring + lpad/rpad — replace characters at specific position
concat and encoding manipulation, including binary and md5/sha hashes computation
format — sprintf like functions
slicing, using left/right and reverse
regexp — replace, split, count and match
Date
age, date_bin or date_part or justify_days or hours
day of week or year or week of year — a lot of fun do manually for leap year
overlap between dates
of course — time zones
Geometry
point/box/circle types as a first citizen
closest point to object or distance between them
overlap and inclusion
area or diagonal or bounding box
IP Address
subnet contains another subnet or compute netmask
Json
extracting elements from json array or nested keys
check whether json is contained in another one
row to json or unnesting json to key/value rows
Array
including, min/max, unnesting, appending
ranges
generate_series — integer or date with steps
SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,
'2008-03-04 12:00', '10 hours');
PS
Treat GenAI as a pairing buddy with encyclopedic memory and confidence of person under Dunning–Kruger effect when it just think up answers. You know what you want from your data — you have to learn what is possible! (And ask AI to do boring work of typing).
Discovering for myself recent advancements in frontend development via Python:
Lets say, you would need to quickly sketch the UI for EDA and share it with someone else. You started playing with data in Positron IDE but what next? Which options do you have, when matplotlib/plotly/seaborn/altair/ <you named> rendered diagrams are not enough?
Metabase – can be a good first step to share access to data in a friendly way, however with growth of data volume it becomes not so convenient.
Specifically for dashboards building, without too much actual coding involved there are low code solutions:
Appsmith – open source and can be self hosted and customized (via js code)
Retool – allow to define complete workflow with necessary aggregation but will charge you some fee
Sure, you can start with old school jinja templates and plug something like Vega (declarative language and d3.js for visualization) on top of pre-computed aggregations to render static graphics via REST API.
But if you are fine with predefined UI components and want that someone else have fun with hosting, then your first choice for interactive diagrams are:
Gradio – spin up dashboards in notebooks and webpages and handy to expose your models from HuggingFace (fresh new LLM anyone?) – however performance and customisation are not the best
Streamlit – interactive UI out of the box to crunch the data but not intended for complex web app (or should I say real app(?) – authorisation, routing, state handling, … ,)
Couple of frameworks trying to overcome limitations of those initial cohort
Taipy – much easier to setup for multi-page applications, scheduled data cranching tasks but advanced functionality (SSO, ACL, drag-n-drop GUI builder, …) for a fee
Mesop – build by Google – data-centric, allow custom CSS and hot reload out of the box, in addition to authentication, multipage apps and even ability to integrate it on top of FastAPI
From simplicity perspective my favorite one is FastHTML – it build on top of two simple ideas with zero external dependencies:
use htmx – extension of html, that allow to build interactive web apps not as SPA – with dynamic content loading and refreshing
hyperview – backend-rendering components and screens = no more issues with backend frontend api compatibility – just render what you got
Time of engineers should be spent on value-added tasks — something that push your business in front of competitors. But if you start asking around — you will be surprised to know that there decent amount of precious engineer’s hours wasted on the last mile code delivery — building and deploying code. Tedious and error-prone part of work that can be eliminated.
Let me showcase few scenario to better align on what is possible via Continuous Integration and Delivery:
Update static websites by replacing content at AWS S3
Push down into inference layer new version of ML model
using a wide range of build systems (bazel, ant, maven, gradle, cmake/ninja, … you name it!) do app packaging for further distribution (pypi, artifactory, helm charts)
Create a screenshots of specific widgets in mobile or web application and compare it with what it expected to be for specific data – validate that layout is not affected — run so called Snapshot or Golden tests
Build prod/dev version of mobile application for Android and iOS platforms via fastlane, using Github Actions and distribute it among QA team
One click manual promoting for mobile apps into appstore and google play – collect from PR titles entries for changelog, generate new screenshots and submit an app for in-store review
Package a FastAPI backend application into Docker container, publish it in docker registry and prepare a new Helm chart
When PR is created with changes in core python library — in addition to base repository checks — trigger auto-tests in all dependent downstream repositories (where core library is defined as dependency) to validate backwards compatibility
Mutate state of infrastructure in cloud (whether it is AWS/GCD/Azure or Digital Ocean) using Terraform —change deployment strategy for Kubernetes cluster from Recreate to Rolling update, choose faster volume type for EC2 instances, deploy new frontend application
Build computer vision C++ library for x86_64 architecture, run tests over test video and images — to validate that core algorithms are not degraded in quality or speed — cross-compile code for several embedded arm based platforms as a self-contained static library
Setup nightly end to end tests — validating business critical scenarios: spin up temporary database, generate data according to curated use cases, create full setup — backend , frontend or mobile (running in emulators or real device on device farm — Saucelabs or Firebase test lab) — and run series of UI tests (Selenium, appium, maestro, patrol) involving various 3rd party providers — Stripe, Zendesk, Getstream or Twillio— in case of errors sending alert into Slack or right away opening a Jira tickets
Formatting, static analysis, checks for committed secrets or huge blobs, vulnerability scans (including in outdated dependencies), auto-versioning — there are endless number of scenarios that can make code’s housekeeping much easier and free the hands of engineers to focus on product.
What is required from CI/CD:
it should be fast — to save human’s time
it should automate all manual routine — to avoid human errors
to document and standardize release process
What good CI/CD can do?
maintain code hygiene— format, lint, detect code smells — automatically discarding something that is not aligned with the team’s convention
maintain an app version number — SemVer or simple increment Build Number — anything that allow to answer simple question — what version of code is used
Run automated tests — such safety nets is crucial aspect of continues and quick changes
Containerisation of application — isolated environment for Docker with versioning, Docker registry to store artifacts, Helm — if app is used in Kubernetes
Build release candidate — different endpoints for staging or production, activated observability, depending ot tech stack — with debug or extended instrumentation for QA, preferably not injecting secrets but implement mechanism to acquire it from environment
Distribute app — whether it is to staging or QA environment, publish it at artifactory or appstore. Simple Github release with tag do the magic as well — rollout to portion of users
Use Postgres! Use whatever you have the most experience with!
Every software design discussion should start with the game of questions to clarify what we are about to build. And what not. Boundaries are important to have a clear cut from a very shiny but probably too remote future with hundreds of thousands requests per second vs what is actually needed for PMF validation (speed!) or existing/prospecting clients in the nearest three years (predicted stability).
Start with numbers — old good capacity planning:
data volume
what kind of data — payload size, number of attributes = columns, data types — numbers or text, gis or nested documents, media (i.e. blobs) or special data types
expected load (read heavy, write heavy, and if mixed — in what proportion) — read/write amplification — actual number of operations— is the thing in the world of High Load
peak number of operations per second
how long data should be in hot storage — i.e. available for ad-hoc queries — one month worth of data or last 3 years
Is there any chance that data will be changing (110% yes!) — i.e. how convenient is to have a schema evolution? Some programming frameworks and databases do have existing mature solutions for this.
What if there is a need to deduce a new attribute from several existing fields? Are you ready to write backfill utilities to crunch all datasets? Taking into account up-time requirements and limitations for various client apps?
Identify read usage pattern — i.e.
what questions your data should answer — aggregation, fuzzy search or geospatial queries
how often
how fast is acceptable vs desirable
who ask those questions — i.e. is it required to provide dashboards over materialized views from independent data marts or SQL query interface over data lake will be sufficient for analysts?
is it require last 3 months or the whole history,
whether it is worth to prepare thumbnails or always need to load full image
This should allow defining some base ground for requirements to choose DB and schema.
In 99,99% of cases – migration is not needed – updating schema and application – resolve all hiccups!
Many teams have concerns related to the burden of db engine migration and invest too much time in debate of best db choice when they have no understanding of real usage. Time box design choice and embrace possible changes.
But before it – trace issues with existing database solution — what is the main pain points we try to overcome with migration? In around 99,99% of cases that we meet – migration was not needed – updating schema according to db engine paradigm and adjusting application resolve all hiccups.
Yes, migration will require careful planning to switch applications to new data sources to avoid downtime, several tests to run in a sandbox in order to better understand possible data issues and extract some static data for normalisations (roles or product types for example) if needed.
It is true – every migration that I have been part of, reveal number of issues:
data quality issues — missing foreign references, logically impossible data — i.e. negative prices or missing mandatory fields
data schema issues — when original idea of target schema doesn’t conform to particular data outliers
performance — depending on load patterns, app and business logic it might lead to hotspot tables that accessed to often (because of joins or aggregations or more tricky issues related to hotspot nodes)
And sure, you can find your way to tackle them:
for data quality issues — sample data statistics and start asking questions — i.e. what we consider a duplicate? Is it okay if profit value is negative? Is it accepted when the last name is null? It is very helpful if you can sync with domain experts as well as with data producers and consumers to figure out the best outcome (ignore, decide case by case, use some agreed defaults or just fix it). Based on this discovery stage data quality checks or even alerts can be setup — if necessary documenting it, using tools like greatexpectations.io or DDataFlow
map current and forecasted use cases of data usage to target database and tailor schemas. Sometimes, it might be handy to split data in several layers as in medalion architecture — with raw data that we later use to derive aggregations for dashboards or domain specific tables, to avoid frequent heavy queries.
sometimes there’s simply no ready to use migrator available or existing solution is not flexible enough hence not sufficient — so you need to build a tooling and make them configurable, so it can be reused
Yep, application can’t handle load anymore. Alerts, exceptions, timeouts. Vertical scaling helps for couple of weeks and then everything is start failing again. And it become noticeably expensive for short-term solutions. Ouch.
Good news – it means that users love your product = strong signal in regards to PMF journey!
Challenge: Random spikes of 100% CPU utilisation for Postgres database, that lead to degradation of user’s experience in Web-app (delays, errors, reduced functionality)
Result:
first week – stabilize system, identify hotspots and extend monitoring
second week: improve system throughput 10x times, downscale DB instance x3 times, confirm CPU usage is below 20% (even during peak hours)
and one more day: to prepare system for further growth – create a roadmap to evolve architecture and gradually improve codebase
First rule of working with a system under pressure – Pareto principle – identify and address issues, with the most severe contribution to performance hiccups. Refactoring, changing tech stack, comprehensive code analysis – should be a strategic, educated choice of the core team at a later stage. When a system struggles to serve its main purpose – top priority is to fix it ASAP no matter what. Actually, the same applies to org’s change management but it is a topic for another essay.
Identifying bottlenecks and hotspots might be tricky. In that case system have moderate size – dozens of thousands lines of code, multiple modules with a few flows of load: web hooks, triggered by 3rd party systems; jobs in queues ranged by priorities and usual API calls by user’s actions through product’s dashboard, all of it has seasonality within week and day time, as well as random bursts because of new client’s onboarding.
How do you find the root cause of poor system performance?
First of all – perform “due diligence” – collect information about system:
deployment
database(s)
application’s key components and their interactions
external dependencies – 3rd parties services
observability: logging, monitoring, alerting – what we have and what we don’t
ask(!) team about their concerns/suspicious
what is the current system throughput = number of operations per second
if it is not yet under monitoring – we should start collecting this info and watch it after every change in the system (given we get)
Determine and agree on our target number – i.e. is it x10? x100?
Based on initial info – check the most suspicious parts:
DB: sizes – table, index, data in tables, does schemas match queries, what about index usage, what are the hottest tables, do we have full scans, how often vacuum running, etc
App: if have access to pod/node where app is running – session with atop/htop + netstat/lsof to see whether we hit ceiling – and where is our bottleneck – CPU/RAM/DISK/NETWORK?
Depending on app – add/view logs to see hotspots in terms of most frequently called methods and their mean time or endpoints with longest tail of response time
Deployment: cpu type, disk type, network type, throttling, is it shared env (with famous CPU steal time) or dedicated or even spot, CDN, caching, load balancing, geo location, etc
Depending on findings – prepare action plan to address the most demanding parts – i.e. tuning schema, sharding db or cache data, extract long running processes to async jobs, use auto-scaling in case of high CPU usage
Pick the most impactful item to start working on it
Compare metrics before and after of changes
How long to collect them depend on the project and domain – i.e. some jobs of functions might be triggered not so often – but might be leading to noticeable performance degradation
Sometimes it can be super helpful to check changes of performance in isolated setup and use wrk or similar tools for stress testing to reproduce bottlenecks locally and view system throughput. Depending on complexity of setup – it can bring better control, and strategically (if expected even more growth in the nearest future) – can help to measure limits of the system.
Why is my Postgres so slow?
In this case the vector of investigation was clear – as we know where to start from – database’s CPU was heavily used. But why? What can we get from postgres system tables? Actually a lot:
Active operations happening right now:
SELECT
count(1),
wait_event_type,
wait_event,
backend_type
FROM
pg_stat_activity
GROUP BY
wait_event_type,
wait_event,
backend_type;
Red flags are:
growing number of connections – either more and more clients try to do something or DB can not handle flow of queries and they are queued
event types like:
IO – frequent number of write ops – delete update insert
BufferMapping – full scans
idle in transaction – connection in use, db locks row for modification and client after lock acquisition do something – ideally everything should be ready – so transaction are fast
if total number of connections exceeding 500
Database access patterns – number of rows added/readed/updated or deleted per table – to determine most heavily used tables and type of their usage:
Do we have full scans – i.e. are there any missing indexes?
SELECT
relname,
seq_scan,
seq_tup_read,
idx_scan
FROM
pg_stat_user_tables
WHERE
seq_tup_read > idx_scan
ORDER BY
seq_tup_read
DESC LIMIT 10;
and index usage within table
SELECT
relid,
relname,
indexrelname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM
pg_stat_all_indexes
Not used indexes:
-- not used indexes per table, bigger then 100 MB
-- pg_toast - contains blob of data
SELECT
s.schemaname,
s.relname,
s.indexrelname,
pg_size_pretty(pg_total_relation_size(c.oid)) AS index_size
FROM
pg_stat_all_indexes s
JOIN
pg_class c ON c.relname = s.indexrelname
JOIN
pg_namespace n ON n.oid = c.relnamespace
WHERE
s.idx_scan = 0
AND s.idx_tup_read = 0
AND s.idx_tup_fetch = 0
AND pg_total_relation_size(c.oid) > 100 * 1024*1024 AND s.relname not like 'pg_toast_%';
With activated query statistics that collect information about most frequent queries with number of calls, number of rows involved as well as mean time and max time. There are handy extensions, that can be helpful to increase transparency and find suspicious code’s:
Using all of it – we can export it into prometheus and expose it in grafana’s dashboard to correlate with CPU spikes – i.e. what are the hottest tables? What are access pattern? are there full scans = extensive disk IO/data buffer operations in db? How many rows fetched? When was the last time a vacuum or analyze executed? Can we map most demanding and frequent queries to the ORM and code? How code itself is organized – i.e. does ORM boilerplate is hidden behind wrappers that re-used across the project (hence all optimisations can be done in a single place and it is easier to find all references).
There are some basic precautions, that always worth to keep an eye on – probably even with some static checkers:
using function in conditions that need to modify every row, multi table joins, using `in` in conditions and looong list of values to check with,
Additionally, can be beneficial to integrate into code:
Upserts, checks to avoid updates in DB if data not changed
Pre-computed aggregations and caching (old good LRU with)
Sometimes, it can be much more efficient to zoom out a bit and think from the level of business logic – are there any redundant calls in the function’s flow? What about data schemas – i.e. we should always design storage based on usage patterns – how we are going to query this data? are we read or write heavy => Are there any advantages of some de-normalisation or maybe there is a need for an extra layer of aggregated data?
Whatever hypothesis you have – golden rule of optimisation – iteratively change system – one thing at time – redeploy – and watch metrics!
hype wave, pragmatic evidence vs need to move fast
Tech side of startups sometimes can be very fluid and contain a lot of unknowns. What tech stack to use? Which components might be overkill for now but worth keeping an eye for in the future? How to balance the pace of business features development while keeping the quality bar high enough to be able to have a maintainable code base?
Here I want to share our experience of building https://cleanbee.syzygy-ai.com/ from the ground up – how we shape our processes based on needs and how our processes evolved as we extended our tech stack with new components.
Business want to conquer the market, engineers – try cool stuff and stretch their brains. Meanwhile industry produces new languages, frameworks and libraries in such quantities that no way you will be able to check them all. And, usually, if you scratch the shiny surface of NextBigThing you will find a good old concept. Good – if you are lucky.
One of the most exciting topics to argue about – is the processes – whether you rely on trunk-based development or prefer more monstrous github flow, are you fan of mobbing or find it more efficient to spend time in PR-based code-reviews.
I have experience working in an environment where artifacts were thrown away on users without any standardized process, and, in case of issues, developers had a lot of fun (nope!) trying to figure out what version of components was actually deployed.
On another spectrum is the never ending queue to CI – after you create PR you have to entertain yourself in the nearest 30 min by betting whether CI cluster will find a resource to run tests over your changes. Sometime the platform team introduced new, exciting and certainly very useful features, that might break compatibility with existing boilerplate for CI, that resulted in failing all your checks at the last minute, after an hour of waiting.
I have a strong belief that as usual it all depends – on team maturity, kind of software you are building and various business constraints a-la existence of error’s budget and importance of time-to-market versus SLXs.
I think what is really important – have some agreed process in place, that everyone is aware of and follows, as well as having balls to challenge and change it, if there is evidence that there is a better alternative.
Start shaping the process
What we have at the start:
less than dozen developers – in house-team and temporary contractors
completely greenfield project – no single line of code is written yet, requirements a big vague, but already started shaping into something
tech wise – clear need for backend that should talk with mobile clients
and some simple web frontend – static pages should be enough! (nope)
We have started simple – code at github and PR based flow with single requirement – to have tickets splittable in order to be delivered in 1-3 days. This required some practice of story slicing and it seems that sense of visible fast progress ~ ability to move tickets to Done, can be a great motivational factor for the team to onboard of that idea.
Linters and static analyzers to skip exciting discussions a-la how many arguments per method are too much (6!), gradually adding auto-tests. We also try codesense – they have very promising approach to highlight important part of code (those bits that changed frequently – should definitely have a higher maintainability bar!) and identifying complexity by looking at the level of nestness in the code, probably it is a bit expensive for startups in initial stage – but 100% provide a decent level of hints for engineers.
On the architecture side of thing – there were temptation to dive deep in wonderland of microservices. but looking at horrifying diagrams of connections between them from big players, need to trace requests between them – it really seems suicidal approach for teams on early stage, that want to move fast.
Analysis of requirements allow us to detect three groups of job:
core API with usual CRUD like activities
search and recommendations
temporary workload that do something very useful according to schedule (almost at time with casual delays are ok)
Choice of tech stack – situations when time a bit limited and expectations are high – use what you know and master (yeah, maybe for some one it is boring technology): hence Fastapi, REST, stateless, python, redis and postgres our best friends (yeah, we like Go and Rust – but need to pay one’s dues a bit more!).
With mobile clients the situation was a bit different – we foresee a lot of screens with states, interactions with remote services but not too much custom, platform specific tweaking – hence the idea of having a single code base for both iOS and Android was very appealing.
Nowadays the choice of frameworks is really wide – but again, due to some experience with flutter we decided to give it a go. Within mobile development – one of the important aspects to better decide on is state management – and here you will have a nice abundance of the acronyms to be puzzled about from various languages and frameworks – MVC, MVVM, VIPER, TCA, RIBs, BLOC, … . Our moto – start with the most simple (*) solutions sufficient to support necessary functionality. (*) Simple – well, lets put it this way – we think that we understand it.
However, we definitely make a mistake after building MVP – decided to build on top – instead of throwing it away. Hence at one wonderful (nope!) sunny day I was questioning my sanity: when after I commented out code, clean all possible caches and still doesn’t saw my changes in new screen. Yeah, dead code should be removed!
Start building!
After those initial formalities were settled, the next thing that was necessary – to be able to check client-server interactions.
API contract is for sure a great thing – but it will be much more obvious that something is wrong when you have a real server throw on you “schema validation error” or miserably fail with HTTP 500 error code.
Backend services were initially split into two groups – API monolith and Search & Recommender. First contains more or less straightforward logic to interact with DB, second contains CPU intensive computations that might require specific hardware configuration. Every service – its own scalability group.
As we were still thinking about rollout strategy (and arguing which domain to buy) – solution was simple: to minimize struggles of mobile engineers of dealing with backend = alien stack – lets pack everything into docker.
When we prepare everything to be deployable locally – mobile engineers can run docker-compose commands and have everything ready (after a few painful attempts that reveal flaws in documentation – but the real value of such exercises is to react to every “WTF!11” and improve it).
`Everything` is good, but what is the point of an API running on top of an empty DB? Manually entering necessary data – shortly start leads to depression (and risk to increase duration of development cycles). Hence we prepared a curated dataset that was inserted into local DB to be able to play with. We also started using it for auto-tests. Win-win!11 Auth becomes less problematic in defining testing scenarios, when you have dozens of dummy users with similar passwords!
Try new things or choosing 3rd party providers
Dealing with new technology is always a bit dangerous – you and your team can’t know everything (and sometimes things that you think you know can full you, but that’s another story). And still it is often required to assess, investigate something that no one has touched.
Payments, email, chat, sms, notifications, analytics, etc – every modern application usually represents a bunch of business logic glued with a number of 3rd party providers.
Our approach to choosing with whom we work – time-capped try-to-build-with-it activities to try the most promising one chosen by features, supported languages and, in case of providers, pricing.
How did we got into terraform?
Backend, apart of DB also should have some object/file storage. Sooner or later we also should have DNS so our services are ready to play with the big cruel world.
Choice of cloud provider was again purely based on existing expertise within the team – we already use AWS for other projects – so we decided to stick with it. For sure it is possible to do everything in the AWS console – but as times go, things become a classic big ball of mud that everyone is terrified to touch and no one remembers why this bit exists at all.
Based on experience with terraform… you already got the idea how we choose things? 😀
Yeah, initial setup will take some time (and without control can easily become the same big ball of mud in TF as well 😀 ) but at least it will have some documentation over infra and visibility WHY it is there. Another major advantage – whatever you manage through TF – will be updated automatically (well, when you or CI/CD run corresponding commands)
Secrets managements
For AWS itself given we run everything inside AWS we can rely on iam and assumed roles by attaching necessary policies to our VMs. But we need integration with 3rd party services, as well as some way to pass some secrets to our apps – for example password for db. We need some solution for secret management. AWS have KMS, github actions have its own secrets, and apart of it there are bunch of other providers, so real question is: what do YOU need from secret management:
audit
path based access
integration with Kubernets
ability to issue of temporary credentials
Web UI
Free
secrets versioning
… ?
KMS was very handy, and we managed to add it into github actions but the UI of vault and ability to use it for free (if you run it by yourself) was a kind of deal breaker on this matter.
Path to Kubernetes:
And once we have dockerized app – we have started considering Kubernetes as it offers few goodies out of the box – the most important one is to be able to spin up necessary amount of pods to meet performance demands and ability to define all your needs in declarative fashion – so, given sufficient level of automation no human being should run kubectl apply. AWS has EKS to start with that can be managed via terraform.
On the other hand – steep learning curve (to grasp the idea that it is exactly defined what should be up and running) and a bit of specific tooling to play with – were the fair reasons to think about it twice.
Helm charts.
If we talk kubernetes, and already have apps in docker that are released on every merge to main – helm chart become next steps in adaptation of modern infra stack: we have plugged AWS ECR to keep track of every new releases and publish helm chart in dedicated S3 bucket, that become our internal helm chart registry.
Plugging it at all together was not as straightforward as expected – kubernetes nodes initially can’t connect to ECR and pull necessary docker images, terraform modules (aws-ssm-operator) intended to work with secrets in AWS KMS was deprecated and didn’t support recent kubernetes API, secrets and config maps wasn’t in mood to be exposed into pods.
First rollout of services brings happiness to mobile folks – no need to care about instructions for local setup! Initial week or so though it was not really stable, but then – one less thing to care about.
Do you need all of it? Not necessary.
I must admit – this mix – kubernetes with vault via terraform & helm probably not for everyone and you most likely will not need it on the initial stage. Simple docker push to ECR on merge to main and doing ssh into ec2 && docker pull && docker-compose stop-start during release from CICD – can work well (at least for a happy path) AND will be clear for everyone from the first glance. That’s exactly how we re-deploy our static websites at the moment – ci build new version of it and just copy into corresponding s3 bucket.
Maturing the infrastructure
AWS is nice enough to offer credits for those who are crazy enough to explore shady paths of the startup world. Can we use it to save a few bucks on github minutes and expose less secrets and infras to github VMs?
How about self-hosted runners – i.e. when you open PR it is not Github VMs but your own Kubernetes allocate pod to run your CI checks? Sure, it is not easy to prepare everything for iOS releases (more about it below) but Android and backend surely should work on old good Linux?!
There is a lot of marketing fluff around terms like monitoring & alerting.
In some companies those things are implemented just for the sake of bragging “We have X for that!”, although engineers are still blind to what is happening with their production, when there are real issues or alerts channels have to be muted as it contains non actionable noise.
And I must say – here we are still having a looong way to go.
First thing that you will find as soon as you search for those kind of solution is ELK stack and bunch of paid providers. Now, after measuring time and efforts to maintain our own setup – I start thinking that a paid solution might be really worth it. If and only if you really can delegate burden of squeezing the most important info about your apps and state of infra to existing solutions – it is all depends whether they have preset of metrics, log parsers and index mapping that you can easily adapt for your project.
For logging currently we rely on ELK. Yeah, it is more or less straightforward to setup and most likely there are people who find the query language of elastic very convenient to use on a day to day basis.
Here we are still exploring options – as it seems that old good kubectl logs with grep produce insights for questions like “What is the last error from app1 pods ” in a much more timely fashion, without being lost among endless UI controls. But most probably in the UI of Kibana still hide the levers that we should pull to add proper ingestion pipeline and choose corresponding mapping for elastic index for filebeat?
For alerting we setup prometheus and integrated it for the slack – again mainly due to the fact that we have experience with it beforehand.
Now, why with all of that we need Azure?!
As it usually happens when product evolving – new requirements introduce new kind of things
now apart of having something publicly visible we need some resources available for team only
to manage feature flags, access vault UI or struggle with elastic to figure out the last API error.
Sure, there are paid solution for that or you can mix some Identity as a Service providers (Azure active directory) for authentication your team mates with any VPN providers (we choose OpenVPN due to their free tiers) by exposing necessary services to internal network only so those who should can login using their credentials – and it has one clear advantage in comparison with using AWS stack – it is free (for limiting number of connections).
Okay, why do we need google cloud!?!
So far we are mainly discussing the backend part of things. But there are more. The thing that you see first – mobile apps! Flutter or something else – they also have to be builded, linted and tested. And published somehow somewhere, so stakeholders can immediately be in awe of the new features (and find new bugs).
For rolling out to the production – you would need to pass through a bunch of formalities (screenshots, change log = whats new, review) that will delay your audience from enjoying those pieces of art.
I must say that the API of stores is not really friendly for frequent release – when your build and sign app – publishing can easily take 15+ min. API of app stores – as every other API – may and will fail sooner or later. Yeah, and signing might be a nightmare as it is different between platforms. And it would be REALLY nice if engineers didn’t waste time on all of those things preparing releases from their laptops.
First (and probably single?) thing that you should consider is fastlane – initially I did have some prejudice with all those new terms like gems (like that name though!) and bundle, but it really works. Yes, in order to run them from CI some efforts will be required to deal with secrets jks for Android or match for iOS.
Towards the “dark” side
Next you will start thinking about app distribution: testflight is a handy tool for iOS world, what about Android? We endup using App Distribution – solution from Firebase – mainly because it worked for us after first try, but there are other options (that actually claim to be working for both platforms). What is important – you can do everything from fastlane! Even when your app evolving and you start adding various extras – analytics, chats, maps, geo, … – many of them were from Google directly of Firebase. As Firebase offering many goodies – it was natural steps to collect analytical events and after few tweaking with their IAM policy setup export of raw events into gs-buckets to be able to play with BigQuery.
Prod vs Staging – The Great Split!
For backend we have auto-tests right from the start and various practices like test double prove quite efficient to prevent regressions even in cases of complex business logic with integrations from side services. On the mobile side we were a bit limited due to coexistence of code from MVP and auto-tests were not so helpful for complex business scenarios like someone wanting to use our services but we can’t charge his bank card.
Manual testing was very time consuming and error-prone, especially when business logic dynamically evolved over time and state of data in the database after recent updates became become non-possible from the point of view of domain rules.
Yeah, so it would be nice to be able to run e2e tests clicking through the app with data that we are maintaining (and sure that it is valid). Would be REALLY nice if those tests don’t pollute the actual database, S3 buckets and 3rd party providers.
We have started with a single main branch and a single environment (rds, redis, k8s namespace and s3) that was used by first testers and developers. We were not exposed to the public, but as we move closer and closer to release it becomes clear that some kind of distinction is necessary in places where we can break things and have a stable environment.
In mobile applications it was matter to change URL of API during building. On backend – few aspects have to be done to support deploy-specific configurations: infrastructure-wise by creating dedicated policies and resources, and parameterized few bits in the code where specific URLs were expected. Apart of it there are several repositories, some of them independent but some are dependent – as in cases of shared functionality.
You know what will happen when you update shared functionality without immediate redeployment and testing all dependent apps? After a few days when you completely forget about it, you do some innocent – purely cosmetic changes, somewhere else in the dependent repo that will lead to redeployment and pull the latest dependency. Surely, during important demo right after it you would see some stupid errors related to lack of compatibility for single condition “that no way can happen” and you forget to double check.
So first important consideration for spliting environment – automate overall rollout of all dependent applications if some base repo was updated, you may ask the team to do it and everyone agrees but forget to run pull.
Second aspect – what do we actually need to deploy? Do we need to maintain all apps in every environment, including temporary jobs that are responsible for sending email or notifications? Seems that some kind of flag to include or exclude job into deployment might be helpful.
E2E, and, later, probably Staging not necessary should be reachable by everyone on the internet.
promoting new release to e2e and staging have to be automating
promoting new release to prod, at least now, better have controlled and manual
Currently we have three envs, which fulfill all the things above:
E2E – environment where integration tests will be run on curated data to ensure base functionality is still in place
Staging – where core development is happening and where beta testers can try to break what we build
Prod – that happy to greet new users
Kubernetes cluster is still a single one – everything was split on the namespace level. Similar thing with RDS – where several databases co-living together in RDS instance.
On the side of automation of mobile testing choice is not really big. First thing that you have to choose – are you going to use any device-in-the-cloud provider or run tests by yourself? For sure you can plug a smartphone into a laptop and run tests – but wouldn’t it be nice (and right!) if CI will do it instead? When you start considering vendors that provide emulators and real devices to play with, you will find that the choice of testing framework for mobile is not really wide – but that the second choice you have to make (and choice of provider might limit you here). Another important consideration – is there any specific hardware requirements – i.e. using gpu or npu – hence any emulator was sufficient for us.
We identify two main options for the mobile e2e testing framework – flutter integration tests and appium based pytests. Firebase Test Lab were supporting flutter integration tests, although it required some tweaking to allow requests from their ip ranges (VM with running emulators) to reach our E2E API. Appium, apart of python api was very promising, as using something like a testproject (you guys rock!) – you can record all the clicks through the application per scenario – hence doesn’t require specific programming knowledge (but allow you to gradually learn it though). So far Appium is much more comprehensive in our setup in terms of scenario coverage.
E2E tests have one tiny (nope!) issue – cold-start of app in an emulator not very fast, if we add on top of it the time necessary to build the app and the duration of copying the debug build to the provider – it becomes a real bottleneck of moving fast.
So far we experimenting with running them twice in day – but lets see how is going.
What’s next?
Many interesting tasks are still on our todo list:
on infra side – performance testing, security testing, trying out flutter for web
on the development side – serving and updating ML models for recommendation engine and prediction of cleaning duration, building cache with feature vector for recommendation, intermix of optimisation problems for matching engine: jobs scheduling and game theory, …
And the most important – nothing can replace real world usage – many crazy things you will be able to see only when you start collecting real data about user’s behavior – so we are looking forward to the upcoming launch!
Few learnings on migrating computer vision pipeline from python prototype at AWS to cpp in smartphones
One of the projects that we have been working was Smart Mirror – we want to build a technology that will hint user how to looks better at the photo.
It has bunch of fascinating tasks to tackles – from philosophical – what is “better” and what does this “better” mean for particular user to more close to earth issues like scale-independent facial expression comparison between frames. So we decided to split it on several stages to roll out one by one and started with application that show realtime hints how to looks similar to the best photo that user choose by himself.
Currently, under the hood, our computer vision pipeline has 10 neural networks in place and can chew 10-20 frames per second – depending on smartphone’s hardware.
In this article I want to share not only what we are doing at Syzygy AI, but mainly highlight our journey of bringing machine learning from cloud to smartphones.
Data Science\AI is still a hot buzzword to attract attentions to what are you doing, but statistics shows that majority of prototypes doesn’t reach productions. Maybe something dangerous lurking behind curtains of golden cage of Jupiter notebooks?
When we started exploring idea – we started simple – lets run MVP on laptop and use webcam to grab video frames to play with!
We need real-time processing – speed, speed, speed – so lets use our skills in low level programming and do everything in cpp and maybe cuda!
There are maturelibraries that you can utilize to plug required algorithm. And there are a lot of pre-trained models (pay attention to a license!) that can be comparably easy added together to sketch a prototype. If the thing that you want to do is already have been done – you just run face detector and after that face landmark detector and then … .
Couple of weeks after – time to share results with stakeholders – early feedback loop is important. So how they can check it out? Well, that’s easy – clone the code, make sure that you have a cmake and required dependencies installed…
You asking what is clone? Do you have a nvidia GPU card? No, we can’t prepare app for iPad yet. Ummm, yeah, let us think about something else!
We have measured time of frame processing and the biggest contribution to duration was inference time of models (and most likely IO to and from GPU) – hence pipe was re-written in python for the sake of development speed. Cloud providers were happy to share VM with powerful GPU – just pay money, it is easy! – so we don’t depend on user’s hardware anymore! Let’s prepare some simplistic frontend and expose UI via web page (but still work with web camera).
It was significant breakthrough for shaping the initial idea and, along the way, it reveals two issues:
1. Usually, ready to use model perform their best when the data, on which we run inference are similar to what was used for training. Hence studio quality portraits of white male in their 40s with beards might not be the best training set for use cases when your target audience is girl-teenagers taking selfi in environment with non-uniform lighting conditions. If what you want to do is not standard cat or dog classifier – you better be aware what ML is really about – digging deep into papers and be able to experimenting fast.
2. When your software is running in the cloud – it can bring all kind of surprises from pallet of distributed system challenges – from cpu steal time to client side data buferization before you flush it to the socket. And of course you have to setup compatible version of drivers and libcuda*.so libraries so your python code will work. Ah, yeah, hardware there might be a bit outdated in terms of computing capabilities in comparison with recent version of consumer video cards. As a result, classical dilemma of “It works on my machine” might be contradicting with user experience at clouds.
As for first point – solution was obvious (not simple though!): We need data. Specific data. So let’s crawl! With our own custom annotation – thanks to CVAT – you can go crazy there.
But second point poses a fair question – what options do we have in regards of productization of our solution. Or, to put it simply – is there any alternative for complex and expensive client-server system with meaty GPU servers?
At the end what we want – run dozens of neural networks with different architectures (few example of tasks that we are solving: facial landmark detection, head pose estimation, segmentations, lighting condition estimation) and classical image manipulations (resizing, cropping, filtering) with close to real time requirements.
It was time to look up – what actually we have inside modern smartphones? Can we offload some work to them? At least to decrease network IO?
The second is AI benchmarks for mobile hardware – from brief view it is challenging to understand how to treat those numbers, but the fact that there is benchmark for mobile was quite intriguing!
Hence we dive deeper:
Let me add here exact extract from one of Jira ticket from 2020:
Mystery of TFLOPs: surprising power in our pockets
Snapdragon is not a single CPU but SoC – i.e. system on chip i.e. it contains various components.
Snapdragon 865
CPU: Kryo 585, Up to 2.84 GHz, 64-bit, instructions set ARMv8-A NOTE: derivative of ARM’s Cortex-A77 and Cortex-A55
2020 A14: architecture – A64 – ARMv8-A, six-core CPU, 2 up to 3.1 GHz + 4 up to 1.8 GHz, LPDDR4X
Huawei:
end of 2019: Kirin 990: 4 ARM Cortex-A76 + 4 ARM Cortex-A55 Cores, Mali-G76 M16
2020: Kirin 9000: 4 ARM Cortex-A77 up to 3.13 GHz + 4 ARM Cortex-A55 Cores up to 2.05 GHz, Mali-G78 MP24
Samsung:
Q4 2019: Exynos 980: 2 ARM Cortex-A77 + 6 ARM Cortex-A55 Cores, LPDDR4
2020: Exynos 990: 2 custom up to 2.73 GHz + 2 ARM Cortex-A77 up to 2.5 GHz + 4 ARM Cortex-A55 Cores up to 2 GHz, Mali-G77 MP11, LPDDR5X, dedicated Dual core NPU
But Cortex-A* itself is just a instructions set – that implemented by particular CPU. GPU – is also clear, but what the hell is DSP\NPU\TPU\VPU\?!
There is class of neural networks – convolutional one – that rely on heavy matrix multiplication operations. Often used for tasks related to computer vision. If matrix is not two dimensional but three – is cube or higher it is called tensor.
As many tasks on devices are related to images – manufactures decided to create a specialized compute unit ~ ASIC – to efficiently perform those operations
In regards of difference between GPU & TPU:
TPU is very limited in terms of supported operations (and as we learn later actually to input size) – memory organization is very simple, while GPU have much higher set of supported commands and memory hierarchy with caches. Modern higher end smartphones usually have dedicated NPU units, so it is worth to explicitly specify corresponding capabilities of top tier devices (as of 2020).
Kirin: NPU is split to two compute parts: 1 Lite + 1 Tiny
Da Vinci Lite features 3D Cube Tensor Computing Engine (2048 FP16 MACs + 4096 INT8 MACs), Vector unit (1024bit INT8/FP16/FP32)
Da Vinci Tiny features 3D Cube Tensor Computing Engine (256 FP16 MACs + 512 INT8 MACs), Vector unit (256bit INT8/FP16/FP32)
Snapdragon 865: 15 TOPS
For comparison (*) – characteristics of accelerators used for AI tasks:
In computing type of data (8 bit integer or 32 bits float) on which you operate really matter – as you might have a hardware tuned to perform operations with specific data types very fast. Or not.
Hence a bit terminology is necessary:
TOPS – terra operations per second ~ throughput.
MAC = Multiply–accumulate operation.
FMA (fused multiply–add) – most of modern hardware architectures uses instructions for operations with tensors. FMA computes: a*x+b as one operation. Roughly GMACs = 0.5 * GFLOPs
Bonus point – that you can’t directly compare those numbers between different hardware as a part of synthetic nature of them, there are other factors drastically affecting performance – related to data coping back and forth and capabilities of other hardware, that post or pre-process those data.
As in many such cases – it was a good reason to just try it – so we prepare Raspberi Pi with Google Coral and fun is started.
ML frameworks for mobile – is there really a choice?
First of all, let distinct two use cases:
inference – when you have a model and want to run it on device
training – when you do not have a model (but want to have one!)
Majority of mobile ML frameworks doesn’t support training on device.
There are wide majority of different devices – read as different hardware – top tier devices have NPU and GPU, and low end sometimes can rely on CPU only. Presence of necessary hardware doesn’t necessary mean that software layer can properly utilize it in place. I.e. that is YOUR duty to make sure that app will run in one way or another on user’s device.
There are number of vendor specific library and SDK that can help you to accelerate specific computation on vendor specific hardware. With one tiny caveat – they all works in the following fashion:
you create a model -> then convert to framework specific format -> then you should deploy at device framework specific runtime, that know how to squeeze extra operations from supported hardware underneath:
Obviously, you usually want to have your app installed on as many devices as possible instead of having list of supported processors.
One of possible workaround here – prepare set of models tailored for different devices – so when app start you can ask that existential question – “Where I am running and what I have access to” and pull from your model’s zoo what can is the best fit and fallback to CPU only in case there are no optimized versions available.
There are open source – TVM – and proprietary solutions that try to do exactly it – take your model and optimize it for different hardware.
Sometimes, it is not necessary even take that thorny route – if what you want to do via ML is semi-standartish – probably you can try your luck with semi-ready solutions from industry: ML Kit, Create ML, MediaPipe. Some of them can serve your models via API and you can also try to train them by submitted labelled data: Firebase ML, Lobe.ai, MakeML.
If you are keen to explore what options, you have for vendor agnostic setup to run it completely offline choice is really not so big:
What is the golden rule of choosing tech stack for important project? Use things that you are familiar with! That’s how we started with tensorflow lite.
Dark art of model conversions
First things first – you can’t just run your keras model in TF lite – you have to convert it to tflite format.
And guess what? – after conversion it will be another model. With another precision and recall.
Because what convertor actually do – it took a computational graph of network and try to throw away redundant operations and replace operations with those that supported by TFLite interpreter.
Term “supported operations” are ambiguous one – do you remember somewhere above I’ve mention that hardware may support operation but software not? Well, opposite is also may happen (but we will get to it in a minute)!
In theory, conversion should be straightforward but in practice, if your model is somewhere advanced you may need to drop first or last layers if they not supported or dive in model refactoring to help convertor doesn’t screw up your model characteristics too much.
When model is finally converted – nothing is finished actually – you now need to make sure that
it can run on real device
it produces outcome more or less close to what your keras model did
Actually, you have an option to run TF Lite interpreter on your laptop\desktop, however it is non-optimized for such case at all and inference time might contribute to your procrastination heavily, but you will be able to get a first grasp of actual quality of inference.
Another false assumption that we had – that we can test converted model using available single board computers(read Raspberi Pi) and TPU that support Tensorflow Flow Lite (read Google Coral). Initially idea was promising – friendly linux system is up and running, compatible CPU architecture – ARMv8. But characteristics of CPUs much lower than modern phone have: less cores, no mobile GPU and performance of TPU units seems to be overkill in comparison even with best ordinal GPU – i.e. we can’t reasonably assess what we can or can not do on the real clients.
But the main thing – and lesson we learned hard way – when something is running on Raspberry with Coral – it doesn’t necessary mean it will run on smartphones.
Damn, okay, I have a python and cpp code, TF Lite model and two devices with Android and iOS, what’s next?
This bit might be surprisingly easy (*) – you just need to build a corresponding bench tool from Tensorflow source tree:
(*) easy, if you keen to get familiar with bazel as a goto tool for building monorepo, like flexibility of adb and do not mind to tweak XCode project – for example to exclude emulator from target.
Not sure that I find iOS tool very useful as it working as a black box – do not allow you to specify where exactly you want to run your model. But what was useful outcome of that exercise – to learn that at iOS you are not run tflite model directly – after start of app it gets converted to CoreML format and those model will be used for inference.
On the other hand, with Android benchtool you can directly explore what will happen if you try to run your model at NPU\GPU\CPU.
And it will reveal sad truth:
ERROR: Attempting to use a delegate that only supports static-sized tensors with a graph that has dynamic-sized tensors.
or
ERROR: NN API returned error ANEURALNETWORKS_OP_FAILED at line 3779 while completing NNAPI compilation.
ERROR: Node number 536 (TfLiteNnapiDelegate) failed to prepare.
else you can find something more specific at logcat:
07-17 15:44:42.210 14380 15006 I ExecutionPlan: Device qti-dsp can't do operation MAX_POOL_2D
In some cases – it can be even worse – i.e. it supports some operations at NPU, some at GPU, and remaining at CPU.
INFO: CoreML delegate: 41 nodes delegated out of 315 nodes, with 46 partitions.
From my past experience working with GPU and more recent within Spark and distributed computing – I remember one crucial thing – IO time – to transfer data to accelerator\compute node – can neglect and drastically decrease performance of such computation.
I.e. it might be faster to run everything on CPU than run portion of computations at CPU and another portion at GPU.
Another interesting bit is “delegate”: let’s say that your network has some operation – i.e. ReLu. TF lite can try to use its own code to express it via simple math operations, or if you are lucky (?) – you can delegate those computations to optimized library that know how to use processor’s instructions to increase performance. So you have NNAPI delegate for Android, CoreML delegate for iOS that try to choose best hardware for your operations, or dedicated delegates for GPU or CPUs.
In order to overcome conversion-compatibility hiccups – there are no straight path to success – it is really matter of trial and failure: when you use different format (saved model – or h5 – or pb – just or protocol buffer) resulting model might be different (very!).
I have heard funny stories where people have to run chain of conversions to get compatible operation set: Pytorch -> Onnx -> CoreML and specify particular version of opset or doing amazing things like tweaking operation from a - b into a + (-b) that make model successfully run.
In our cases we have to give up ideas of using NPU for computations – first of all because of operation’s compatibility issue – i.e. for example 2D max pooling in NNAPI works only with input tensor with rank equal to 4 and data layout have to be those that NNAPI implementation expect.
Hence you can’t prepare single model and be sure that it will run on all devices. Efforts required to tune model for particular NPU not (yet?) overweight its possible speed benefit. GPU though show itself quite impressive options – in our benchmarks, on average, we observe ~ x5 time speedup in comparison with CPU. Now majority of our network run successfully at GPU (well, at the moment we do not have bug reports related to it!)
But when model run – it doesn’t necessary mean it going to work!
All those delegates have to support operations in your network and, with all optimization in place, must return similar results – otherwise accumulated deviation of precision can lead to wrong class label at the end – that was exactly case that bite us along the journey.
Which imply that we should not just run model at device with random noise input to test compatibility and performance, but run it on the data from test set to compare results.
And in order to do it some more interactive application can be quite handy.
But how actually run it – i.e. you have business logic in Java\Kotlin or Swift\Objective-C or maybe flutter \react native on the client and there python code with actual pipeline that is a bit more than just run this model?
Our approach was to embed everything to old(?) good(?) C++ library – i.e. single code-base – single entry point for clients to interact with ML functionality.
Single code base – was a good idea in theory, but in reality it leads to few consequences:
you have to compile it for target arch – welcome to the world of cross-compilation!
in order to use it on device – you also have to link it with all necessary dependencies – i.e. Tensorflow lite and OpenCV and their 3rd party dependencies!
Did I mention that in Tensorflow repo Bazel is a first class citizen and old (?) good (?) CMake not so supported so we have to tweak a bit Tensorflow (and sadly it is not everything that was necessary – didn’t have a chance to prepare remaining bits) itself to make it happen.
After some fun with Docker – we have prepared an image with all what was necessary to use it in our CI for tests.
And then start paying attention to actual benchmark results and model size. Usually they quite related.
For sure you are aware of TensorRT that can be handy for model optimization on Cuda device, for the edge you still can utilize it to prepare model before conversion and apply similar tweaks for model optimizations:
Quantization
Model Pruning
Knowledge Distillation
What was really breakthrough for us – quantitative aware learning, that helps narrow down model size from 150 MB to ~ 1 MB without noticeable accuracy loss.
While we are still arguing with UX designers in regards of user flow through the app – we didn’t touch topic of model on device protection (encryption, obfuscation) – which definitely something that cloud solutions doesn’t have to deal with. However, given everything what is written above – probably people who can understand from the structure of model and disassemble c++ code from inside package of mobile app – how it should be used – better should work with us?
