Squeezing 4 million habits into 1 megabyte

I was building the database for a habit tracker inside my non-profit educational platform, and on paper it looked easy: people check off habits each day, you save the result, you show some streaks. The thing that made it tricky was the scale. Each user tracks around 40 to 50 habits a day, and I was planning for about 1,000 daily active users. All of that had to live on Supabase's free tier, which gives you 500 MB of Postgres and nothing more.

And the 500 MB was a hard limit, not a polite suggestion. This is a personal side project, so there was no budget to jump to a paid Supabase plan the second the database started filling up. Either the design fit in the free tier for good, or the project slowly turned into a monthly bill I didn't want. That constraint is the whole reason I bothered to solve this properly instead of just paying my way out of it.

So I went through five designs and ended up storing a whole day of habits in about 10 bytes. That's roughly 20 times smaller than my first reasonable attempt, and well over a hundred times smaller than my very first one. A year in, that adds up: the ~4 million habits the app has tracked so far take up about 1 MB of actual data. But honestly, the real win wasn't the storage trick. It was a moment halfway through where I realized I'd been modeling the whole thing wrong.

Attempt 0: log every habit

My first idea wasn't clever at all. A habit gets tracked, so you write down that it got tracked: one row per habit, per user, per day. A plain completion log.

create table habit_logs (
  id        bigserial primary key,
  user_id   uuid references auth.users,
  habit_id  int,
  log_date  date,
  done      boolean
);

It's clean, it's normalized, it's the kind of schema you write on autopilot. Then I did the multiplication and felt a bit sick:

41 habits × 1,000 users × 365 days ≈ 15,000,000 rows / year

Fifteen million rows for what is, really, a handful of yes/no answers per person per day. And in Postgres a row is never just its columns. Every single one carries a ~24-byte tuple header, plus a null bitmap, plus alignment padding, plus an entry in the (user_id, log_date) index you need to read it efficiently. Once you add the uuid (16 bytes), the bigserial, the date, and the index leaf, you're spending 60 to 80 bytes of machinery to record a single boolean. That design blows past 1.5 GB in a year, which is three times over the cap before the app is even interesting.

The lesson hit right away: at this scale, the overhead is the data. I was paying for fifteen million row headers to store information that barely existed.

A fork: only log what's done

Before giving up on row-per-record, I tried to make it leaner. What if I only logged the completions? If a habit isn't in the table, it just wasn't done. That splits into two tables: a static registry that defines every habit (and which weekdays it's actually due), and a log that only gets a row when someone finishes something.

-- defined once, rarely changes
create table accountability_tasks (
  task_id  int primary key,
  slug     text,      -- 'habit_1', 'habit_2', ...
  required boolean,
  weekdays int[]      -- which days it's due at all
);

-- one row per completion
create table task_completions (
  user_id  uuid,
  task_id  int,
  log_date date
);

I actually liked this one. The registry's weekdays column is what tells you a habit isn't due today, and you work out "done vs due" with a join. But the log still grows with every checkbox: about 25 completed habits × 1,000 users × 730 days ≈ 18 million rows, around 700 MB over two years. Better than logging all 41 states, but I'm still spending a row, and all its overhead, on every single tick. And now every read needs a join just to know what was even due. Same problem, smaller version of it.

The aha moment

Both designs were fighting the same instinct. I kept treating this like an event stream ("user completed habit at time") and it just isn't one. Habits aren't events that fire at random. They're a fixed checklist that gets resolved once a day. That's the moment it all turned around.

Here's the part that made everything click. On any given day, every habit on that checklist is in exactly one of three states, and I started writing them down as numbers:

• 1  did it. You completed the habit.
• -1  mandatory today, and you didn't do it. This is the only state that actually costs you. It pulls your day's score down.
• 0  optional today. Doing it can only help (it turns into a 1), and skipping it doesn't hurt you at all. A habit that isn't scheduled today, or that's been temporarily excused, sits here too.

Why three states and not just a boolean? Because the scoring needs it. The database only stores these states. The weights and the actual math live on the frontend, and a day's score comes out like this:

day_score = Σ(state × weight) ÷ Σ(weight × |initial_state|)

Each habit has a weight, and every habit starts the day in an initial state: -1 if it's mandatory, 0 if it's optional. The initial state is really just an on/off mask: a habit that starts at 0 drops out of the denominator completely, so optional habits can lift your score but never drag it down. Mandatory ones start at -1, so missing one genuinely costs you. That's the whole reason 0 and -1 had to be different numbers instead of both meaning "not done."

And those three states weren't something I invented to save space. They fell straight out of the app's real rules. Not every habit applies to every person: some are mandatory for one gender and optional or simply non-existent for another. Some only apply on certain weekdays. And some get temporarily excused for a stretch of time, where a normally-required habit shouldn't count for or against you. In every one of those cases the answer is the same: this slot doesn't count today, which is exactly what 0 means. So the encoding that turned out to be tiny was, first, just an honest description of how the app already worked.

That reframe is the whole story. Everything after it is just me asking the same question over and over (how few bytes can this little vector take?), and the row-explosion problem fixed itself, because now there's exactly one record per user per day:

1,000 users × 365 days ≈ 365,000 rows / year   // not 15,000,000

That's a 40× cut in row count before I'd optimized a single byte. The other four attempts are all about how to encode that little vector.

How far down the row-count axis?

One detour worth mentioning first. If fewer rows is the win, why stop at a day? Why not pack a whole week, or a month, into one row? This was more tempting than it sounds, because the schedule itself is weekly: some habits only show up on Fridays, others on Mondays or Thursdays. A week-per-row lines up neatly with that, and on paper it's the most compact option of all, roughly 47 MB over two years versus ~95 MB for per-day.

But I stopped at one day per row and let the weekday logic live inside the values instead. A Friday-only habit is just 0 on every other day, so the daily vector already encodes the weekly schedule for free, no chunkier rows needed. A day is also the unit I actually read and write. Packing a week means rewriting the whole blob to flip one habit, messier partial updates, and more awkward indexing. Smaller on disk isn't the only thing that matters, so I traded a few megabytes for operations that stay simple.

Attempt 1: the day as a JSONB object

The most natural first cut was to give each habit an id and map id to state in a single JSONB object, one row per user-day:

{
  "habit_1":  1,
  "habit_2":  0,
  "habit_3": -1,
  "habit_4":  1,
  ...
}

This already killed the row explosion (one row per user-day instead of one per habit), and it's nice and self-documenting: you can read a row and know exactly what it means. But it has a quiet, expensive flaw. Every row also stores the key strings, and they're the same 41 names ("habit_1", "habit_2", and so on) repeated in every single record, forever. Between the key name, the value, and JSONB's per-key bookkeeping (a jentry plus an offset), each pair runs about 40 to 50 bytes:

41 pairs × ~48 bytes ≈ 2,000 bytes / row
1,000 users × 365 days × ~2 KB ≈ 730 MB / year

Better than the naive table, but ~730 MB/year blows past the 500 MB cap inside a single year, and most of those bytes are the literal text "habit_1" written down hundreds of thousands of times. JSONB isn't free at read time either: every access has to parse the container and re-scan the keys.

Attempt 2: drop the keys

Then the obvious question: why store the keys at all? The habits are a fixed, ordered checklist. Habit #7 is always at index 7, so the position already tells you which habit it is. The names are pure redundancy. So I dropped them and kept just the values, in order:

  habits  jsonb   -- [-1, 0, 1, 1, 0, 1, ...]

This is the single biggest drop in the whole thing, and it cost nothing but deleting 41 strings per row. JSONB still isn't a tight encoding, it stores a self-describing binary container, but now it's only describing values, not keys:

• a container header and type marker, about 4 bytes,
• an array header with the element count and a jentry (type tag plus offset/length) for every element, call it ~20 bytes,
• each value stored as a typed number. Even -1 averages about 5 bytes once you include its tag and 4-byte alignment.

~4 (header) + ~20 (array meta) + 41 × ~5 (values) ≈ 245 bytes / row   →  ~89 MB/year

From ~2,000 bytes down to ~245, roughly 8× smaller, purely from realizing the identity didn't need to be stored at all. But I was still spending ~5 bytes describing each value, and each value only has three possible states. That's a lot of metadata for a number that needs less than a byte.

Attempt 3: drop the self-description

The array was still wrapped in JSONB's binary self-description: a type tag and an offset entry for every element, even though all 41 are the same type. I didn't need any of it. Postgres has a native array that stores the values flat behind one thin header.

  habits  smallint[]   -- {-1, 0, 1, 1, 0, 1, ...}

A smallint[] stores each value in a flat 2 bytes behind a thin array header, no jentries, no type tag repeated 41 times:

~24 (array header) + 41 × 2 (values) ≈ 164 bytes / row   →  ~60 MB/year

Less dramatic than the JSONB jump, but it stripped off the whole encoding layer for free, and the access pattern (index in, value out) is exactly how the app uses the data. Still, look at the width: a smallint is 16 bits per value, and I'm using it to store one of three states. I'm burning more than 5× the bits I actually need.

Attempt 4: count the bits, not the bytes

This is where I stopped thinking in bytes. Three states need 2 bits. Two bits hold four values, so three fit with room to spare. So I packed them by hand: four habits per byte, stored as a raw bytea.

// 0b00 = 0 (n/a) · 0b01 = 1 (done) · 0b10 = -1 (missed)
const CODE: Record<number, number> = { 0: 0b00, 1: 0b01, [-1]: 0b10 };

function pack(states: number[]): Uint8Array {
  const bytes = new Uint8Array(Math.ceil(states.length / 4));
  states.forEach((s, i) => {
    bytes[i >> 2] |= CODE[s] << ((i % 4) * 2);
  });
  return bytes;            // 41 habits → 11 bytes
}

The math is almost suspiciously clean: 41 habits × 2 bits = 82 bits, which rounds up to 11 bytes, call it ~12 with the column's own length header. Unpacking is the same trick in reverse: shift and mask. And the best part is that the encoding is invisible to the rest of the app, which still hands me a plain array of -1 / 0 / 1 and has no idea it got squeezed into a handful of bytes on the way to disk.

What 20× actually looks like

Lining the five designs up next to each other is where it really shows. Notice that each step went after a different kind of waste: first the row overhead (the reframe), then the key names (object to array), then JSONB's binary self-description (array to smallint[]), then the width of the values themselves (smallint to 2 bits).

The number I care about: the habits column went from ~245 bytes in the first array-based design down to ~12 bytes, the ~20× reduction I came for. Counting from the keyed JSONB object I actually started with (~2 KB, mostly repeated key strings), it's closer to 165×. On the free tier that's the difference between bumping into 500 MB within a year and having room to grow for something like a decade.

A year later

This isn't a thought experiment anymore. The app has been running for about a year. Here's the live count, pulled straight from the same database:

That number is the part I'm happiest about. The naive design would have created one row for every one of those habits, millions of rows, each dragging its 60-plus bytes of overhead, and I'd have blown through the free tier and into a paid plan months ago. I'd be paying real money every month to store what currently fits in a couple of megabytes. Instead it's a few tens of thousands of tiny packed rows, and the bill is still exactly zero.

The trade-offs I made on purpose

Packed bits aren't free of cost, they're free of storage cost. In exchange:

• You can't query inside the blob in SQL anymore, no where habits->>3 = 1. For this app that's fine: I always read a whole user-day and do the math on the client.
• The position-to-habit mapping has to stay stable. Index 7 has to always mean the same habit, so I keep that mapping versioned separately instead of baking it into the row.
• There's a small encode/decode layer to maintain. It's a dozen lines that never change, so I'll take it.

If I needed rich server-side analytics across habits, I'd have stopped at the integer array, or kept a JSONB copy for the queryable path. Optimization is only "right" relative to how you actually use the data, and mine is overwhelmingly "read one day, render it."

What I took away

It's tempting to tell this as a story about bit-packing, but the 2-bit trick was the easy part. It only became possible after the reframe. The real leverage came from noticing I wasn't storing an event stream at all. I was storing a fixed checklist that gets resolved into three states once a day. Once the data had the right shape, shrinking it was almost mechanical.

Most database size problems are really one of three things in disguise: too many rows, too much per-value encoding, or values that are simply wider than the information they carry. Figuring out which one you're fighting, and being willing to question the model itself before reaching for compression, is most of the work.