From JSONB to Bitmasks: Faster Flags & Cleaner RLS in Postgres with Go/TS

When you’re juggling dozens of user preferences and permission levels, it’s easy to reach for JSONB columns or sprawling tables of boolean fields. But there’s a leaner way: bitmasks. With a single BIGINT column you can encode up to 64 flags, check them in constant time, and keep your schema compact. Pair this with generated columns in Postgres, and you get the best of both worlds – compact storage and human-readable queries.

Note: This comes with some caveats. Bitmasks are fast and small, but they can add complexity – sometimes unnecessarily. Generally I would only implement this approach on a very specific class of project and where the flags are immutable or fixed – migrations can be a pain and over large, long-lived projects it’s probably not the best choice.

In the real world, I use this approach when I have a LOT of feature flags that are predictable and can be classified.

What is bitmasking?

Bitmasking is a way of packing many true/false values into a single integer by treating each bit as a flag. For example:

Decimal 1 in binary is 0001 – means flag 0 is set.
Decimal 2 in binary is 0010 – means flag 1 is set.
Decimal 4 in binary is 0100 – means flag 2 is set.
Decimal 8 in binary is 1000 – means flag 3 is set.

You can combine them:

1 | 2 = 3 (binary 0011) – flags 0 and 1 are set.
To check if a flag is present: (value & mask) != 0.
To remove a flag: value & ~mask.

In Postgres, BIGINT gives you 64 bits to work with. In Go, uint64 maps directly to the same concept. In Javascript… well, you get about 10 less just because how numbers work. More on that later.

Why bitmasks?

Compact: one integer column can track 64 permissions/flags/settings.
Fast: bitwise checks are CPU-cheap.
Simple writes: toggle flags with |, &, and ^.

Example schema:

CREATE TABLE users (
  id     BIGSERIAL PRIMARY KEY,
  username TEXT NOT NULL,
  flags  BIGINT  NOT NULL DEFAULT 0, -- permissions
  prefs  BIGINT  NOT NULL DEFAULT 0 -- user preferences
);

Generated columns for readability

Bitmasks are efficient but seven hells… they can be cryptic. You have two options that let you expose named booleans that are indexable and easy to query.

Option A – VIRTUAL (computed on read, zero storage)

With Postgres 18 released, you can use virtual generated columns (read at runtime, not stored). These can’t be indexed however, so you have to use an expression to index them.

ALTER TABLE users
ADD COLUMN can_read boolean GENERATED ALWAYS AS 
  ((flags & (1::bigint << 0)) <> 0) VIRTUAL,
ADD COLUMN can_write boolean GENERATED ALWAYS AS
  ((flags & (1::bigint << 1)) <> 0) VIRTUAL,
ADD COLUMN is_admin  boolean GENERATED ALWAYS
  AS ((flags & (1::bigint << 2)) <> 0) VIRTUAL;

Indexing: For VIRTUAL columns, create expression/partial indexes on the underlying expression:

CREATE INDEX users_is_admin_expr_idx
ON users (((flags & (1::bigint << 2)) <> 0));

CREATE INDEX users_can_read_partial_idx
  ON users (id) WHERE (flags & (1::bigint << 0)) <> 0;

Query exactly with those predicates so the planner uses the indexes:

SELECT * FROM users WHERE (flags & (1::bigint << 2)) <> 0;

Option B – STORED (computed on write, indexable by name)

ALTER TABLE users
ADD COLUMN can_read boolean GENERATED ALWAYS AS
  ((flags & (1::bigint << 0)) <> 0) STORED,
ADD COLUMN can_write boolean GENERATED ALWAYS AS
  ((flags & (1::bigint << 1)) <> 0) STORED,
ADD COLUMN is_admin  boolean GENERATED ALWAYS AS
  ((flags & (1::bigint << 2)) <> 0) STORED;

CREATE INDEX users_can_read_idx ON users (can_read);
CREATE INDEX users_is_admin_idx ON users (is_admin);

Whichever option you choose, now your queries look like:

SELECT * FROM users WHERE is_admin;

Instead of juggling (flags & (1<<2)) <> 0 everywhere, you can reference the index. This can keep your queries cleaner.

Common operations

-- grant multiple permissions
UPDATE users SET flags = flags | :mask WHERE id = :id;

-- revoke multiple permissions
UPDATE users SET flags = flags & ~:mask WHERE id = :id;

-- has all bits
SELECT * FROM users WHERE (flags & :mask) = :mask;

-- has any bits
SELECT * FROM users WHERE (flags & :mask) <> 0;

RLS that reads like English

Row-level security policies become cleaner when you can reference virtual/generated booleans:

CREATE POLICY doc_read ON documents
USING (
  owner_id = current_setting('app.user_id')::bigint
  OR EXISTS (
    SELECT 1 FROM users u
    WHERE u.id = current_setting('app.user_id')::bigint
      AND (u.can_read OR u.is_admin)
  )
);

Go integration

On the application side, bitmasks map naturally to Go’s uint64 with bitwise helpers:

package perms

const (
  PermRead uint64 = 1 << iota
  PermWrite
  PermAdmin
)

func HasAll(f, m uint64) bool { return f&m == m }
func HasAny(f, m uint64) bool { return f&m != 0 }
func Grant(f, m uint64) uint64 { return f | m }
func Revoke(f, m uint64) uint64 { return f &^ m } // AND-NOT

You can grant or check flags in Go, and Postgres queries can use the same bit positions. Just be careful with the signed BIGINT – practically speaking, avoid using the 63rd bit so values fit safely in int64.

TypeScript / Node.js integration

If you’re writing your backend in Node & TypeScript – you can use number for up to 53 bits safely. Remember, JavaScript numbers are IEEE 754 doubles. For a full 64-bit mask you’d want bigint.

Here’s a sample with bigint:

export const PermRead = 1n << 0n
export const PermWrite = 1n << 1n
export const PermAdmin = 1n << 2n

export function hasAll(flags: bigint, mask: bigint): boolean {
  return (flags & mask) === mask
}

export function hasAny(flags: bigint, mask: bigint): boolean {
  return (flags & mask) !== 0n
}

export function grant(flags: bigint, mask: bigint): bigint {
  return flags | mask
}

export function revoke(flags: bigint, mask: bigint): bigint {
  return flags & ~mask
}

// usage
let flags = 0n
flags = grant(flags, PermRead | PermWrite)
console.log(hasAll(flags, PermRead | PermWrite)) // true

Indexing strategies

VIRTUAL path: use expression or partial indexes on the bit-flag checks you actually query. Keep the conditions text identical in your queries for index use.
STORED path: create plain btree indexes on the generated boolean columns (or on grouped smallint windows) and query with the column names.
Grouped bits: expose an 8‑bit window as a smallint with another generated column if you filter by ranges.

How to pick (decision box)

Pick VIRTUAL when:

You want zero storage overhead and always‑fresh values.
You’re comfortable creating expression/partial indexes on a handful of hot conditions.
You mostly read; write amplification from recompute is a non‑issue (there is none on write).

Pick STORED when:

You want simple indexes by column name and ORM friendliness.
You prefer materialized computed values for consistent ETL.
You expect the same generated columns to be used across many queries and dashboards.

Tip: You can mix them. Make 2-3 high‑traffic flags STORED (index by name) and keep the rest VIRTUAL with expression/partial indexes as needed.

Real-world tradeoffs

Phasing out old flags: You can keep a central registry of bits (position, name, rollout date). When a flag is deprecated, leave the bit unused instead of reassigning it immediately. This keeps historical data sane and avoids subtle bugs when replaying old records.
Mixing with JSONB: Bitmasks shine for hot-path checks (permissions, feature toggles), but you don’t want to burn bits for long-tail preferences. Choose your hot & cold split: bitmask for the top 20-30 booleans, JSONB for rare or experimental prefs. Defaults live in code, so missing JSON keys don’t break anything.
Migration ceiling: If you run out of bits (63 practical flags), you can add a second BIGINT column (flags2) or move a category of flags to a BIT(n) column. Plan bit ranges by domain (0-15 auth, 16-31 billing, et cetera) so it’s easier to split later.
Auditing: Since bit flips can be security-sensitive, consider an audit table (i.e. users_flag_events table) to record who granted/revoked which bits and when.

When not to use bitmasks

If you need per-resource ACLs, use join tables.
If you need user-defined, dynamic permissions, use a roles/permissions graph table.
If you rely on ad-hoc queries across many flags, JSONB or a normalized schema might be better.

Bitmasks in Postgres give you compact storage and fast checks, while generated columns make your SQL ergonomic and indexable. Together, they’re a practical middle ground between raw integers and sprawling permission tables – and with a little planning, they can scale cleanly as your flags evolve.

Source link