What Powers Instagram: Hundreds of Instances, Dozens of Technologies

Ideas you will meet

Distributed Systems

Consensus, time, failure and the fundamentals of scale-out.

What we already know about this

Our own lessons on these ideas. Read them here before you start, so it makes sense.

• Databases Deep Dive · 11 minNormalisation and schema designOrganising tables so each fact is stored once, the first three normal forms in plain words, and when to break the rules on purpose.Read hereClose

  The idea in one sentence

  Normalisation means organising data so that each fact is stored in exactly one place. When a fact is stored in many places, changes must be made in many places, and sooner or later they disagree.

  The anomalies it prevents

  Imagine one big orders table with the customer's name and email on every row.

  • Update anomaly: the customer changes email; you must update every order row, and missing one leaves two different emails.
  • Insert anomaly: you cannot record a new customer until they place an order.
  • Delete anomaly: deleting a customer's only order deletes the only record of the customer.

  The first three normal forms, in plain words

  100%

  Figure SDA-DDV-01-1 · Normal forms as three questions · © forcsguy.com

  1. 1NF: no repeating groups or lists in a cell (not tags = "a,b,c"; use a related table); each row has a primary key.
  2. 2NF: relevant when the key has several columns (like order_id, product_id). A column such as product_name depends on only product_id, so move it to a products table.
  3. 3NF: a column must depend on the key, "the whole key and nothing but the key". If zip_code determines city, store city with the zip code table, not on every customer row.

  Most application schemas aim for 3NF and stop there.

  Keys and relationships

  • Primary key: unique row identity. Prefer a stable surrogate id (for example a number or UUID) over data that can change, such as an email.
  • Foreign key: a column referencing another table's key; the database then guarantees the referenced row exists.
  • One-to-many: put the foreign key on the "many" side (orders.customer_id).
  • Many-to-many: use a join table with two foreign keys (book_authors).
  • Constraints (not null, unique, check) are free correctness: let the database enforce what it can.

  CREATE TABLE customers (id BIGINT PRIMARY KEY, email TEXT NOT NULL UNIQUE, name TEXT NOT NULL);
  CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
  );
  CREATE TABLE order_items (
    order_id BIGINT NOT NULL REFERENCES orders(id),
    product_id BIGINT NOT NULL REFERENCES products(id),
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price_cents INT NOT NULL,            -- copied on purpose: the price at purchase time
    PRIMARY KEY (order_id, product_id)
  );
  

  Notice unit_price_cents is deliberately duplicated: the order must remember what was charged even if the product price changes. That is a snapshot, not an anomaly; the distinction is whether the value should change when the source changes.

  Denormalisation: breaking the rules on purpose

  Normalised data needs joins to read. Sometimes reads dominate and joins are too costly. Common, deliberate denormalisation:

  • Precomputed counts and totals (likes_count on a post) updated with each change.
  • Copies of frequently read fields (author name on a post row) to avoid a join.
  • Summary or reporting tables rebuilt periodically.
  • Document stores that embed related data inside one record.

  The price is keeping copies in sync: update both in one transaction, rebuild from the source on a schedule, or accept eventual consistency. Rule of thumb: normalise first; denormalise a specific, measured hot path, and document it.

  OLTP and analytics

  Transactional systems (OLTP) favour normalised schemas for correct writes. Analytical systems (OLAP) favour wide, denormalised tables, often a star schema (a central fact table of events with dimension tables around it), because they scan lots of data and rarely update it.

  Habits that prevent pain

  • Model the real-world facts first, then tables.
  • Use clear, consistent names; pick one convention.
  • Store money as integers (minor units) or exact decimals, never floating point.
  • Store timestamps in UTC with time zone awareness.
  • Prefer soft deletes (a deleted_at column) only where you need history, and index accordingly.
  • Plan changes as migrations (small, reversible, tested), not manual edits.

  Real-world reading

  Related reading

  • LISA '10 - Operations at Twitter: Scaling Beyond 100 Million UsersYouTubeYouTube
  • Espresso: Distributed Document Store at LinkedInLinkedIn engineers · LinkedInCase Studies
  • Akutan: Distributed Knowledge Graph Store at eBayeBay engineers · eBayCase Studies
  • How Twitter Stores 250 Million Tweets a Day Using MySQL - High Scalability -High Scalability engineers · High ScalabilityCase Studies

  Show 6 moreShow fewer

  • TAO: Distributed Data Store for the Social Graph at FacebookFacebookPapers
  • Scaling Infrastructure at Instagram - Lisa Guo, Instagram EngineeringYouTubeYouTube
  • Cassandra for Keeping Counts In Sync at SoundCloudSoundCloud engineers · SoundCloudCase Studies
  • Roshi: Distributed Storage System for Time-Series Event at SoundCloudSoundCloud engineers · SoundCloudCase Studies
  • What Powers Instagram: Hundreds of Instances, Dozens of TechnologiesTumblr engineers · TumblrCase Studies
  • Zipkin: Distributed Systems Tracing at TwitterTwitter engineers · TwitterCase Studies

  Open this lesson with its quiz and exercise