Store 8 feature flags in 1 integer (Bitwise operations in MySQL)
Vložit
- čas přidán 24. 07. 2024
- This video is a tutorial on using bits & bitmasks for storing feature flags in a database. It involves compressing all data into a single integer column and using bitmasks to activate specific features.
While this method is technically interesting and efficient for compact data storage, it can be convoluted to understand there's no way to properly index it.
In future videos, alternative techniques such as storing the flags in a JSON column or a separate table using a many-to-many relationship will be discussed.
You can watch the second video (storing as JSON) here: • Storing feature flags ... .
To learn more about PlanetScale, head to planetscale.com/youtube!
------
00:00 Feature flags
00:57 Bits and bytes
02:42 Bitwise operators
03:58 Querying with bitmasks
09:06 Adding and removing flags
13:17 Finding unused flags and indexing
14:54 Pros and cons
------
💬 Follow PlanetScale on social media
• Twitter: / planetscale
• Discord: / discord
• TikTok: / planetscale
• Twitch: / planetscale
• LinkedIn: / planetscale - Věda a technologie
This is equally beautiful and horrifying.
It really threads the needle doesn't it 🥰
It gets a lot easier to use when you also use bit shifting. WHERE flag & (1
Very good explanation of Bitwise. I will most likely need to watch again at a later times. Thanks for the video.
I'm at awe with how good the explanations are, the editing helps so much and the delivery is just on point. Didn't think the yt channel for a database company would be so good but am pleasantly surprised!
Steve the Editor is doing so much of the lifting here! I appreciate the kind words
Steve needs a bonus for this video! Good information, interesting video... that's all I can say.
Steve is the man
PlanetSclae absolutely rocks, these videos are incredibly helpful!
bitwise is great, i've learned it from how discord handle permission when i developed a bot. love the explanation on this video
I've using this idea for several years. For readability in code, you can use either the binded parameters or enums/classess instead of integers on programming side.
Also, for 4:45-4:49 you can skip for quering "= 1" so instead of "where `flags` & 1 = 1" simply "where `flags` & 1"
3) indexing performance; with programming support + binding parameters, bitwise adding as (option1 | option2 | option3) works well. like so:
where `flags` = :options
we used todo this in the 80ies when we hardly had any memory in the computers :) works great.
@KsaR-99 ENUM is single value, SET is multi-value.
In this case you would use a SET type.
@@vukkulvar9769 yup, I have meant enum like in php or c/c++, not in db side.
While it looks unreadable, inefficient and not wise to use it in modern applications, well, this was a complete wisdom in the 8bit era where you need to make use of every bit to save memory (real tough programmer back then were able to to do logical operation really quick).
Now, regardless of the size it does take, isn't column of type SET is a good alternative to this, I read it can hold up to 64 different string values.
Thanks Aaron!
Hmmm SET might be a great alternative. I'll have to look into that one
Yes, SET() is stored as bit mask, and descriptive at the same time. And if we need to get numeric value (e.g. by software), it's easy too.
knew all the bitwise magic beforehand,
but I watched till the end for entertainment :)
Mission accomplished then! Thanks for sticking around
I did something similar for a local isp when I was learning linux by automating iptables and traffic control with bash cron scripts to handle assigning and changing each subscriber's access tier and allocated bandwidth bucket and other network privilege flags encoded like this.
That system worked flawlessly for years and logged everything - also encoded 😂to save on space. I fully understand why you say "please don't use this if it's supposed to interact with humans". Luckily there was a web frontend available that had the logic to decode this in human language.
Great vid 🥰🥰
Reminded me about when I reprogrammed my Audi: +2 ambient mirror, +4 mirror turn signals, +8 interior door handle, +16 automatic run etc. Was painfull to figure out what was on/off in the early 2000 with just a regular calculator 😂
I remember using this system when developing a tiny packet system that needed to transfer quite a lot of flags. i think not using them inside of SQL is a lot more intuitive when dealing with things on a constrained hardware level (e.g. playing around with networked Arduino stuff)
Makes a ton of sense at the hardware layer, good point!
We used bitwise operations and other numeric tricks a lot to store tabulation data on an Old TI 59 calculator that had just 99 store positions that had to be shared by data and programs... good old times
I mean, I do use it too in a status cookie, setting bits for different things that happens. Could be invalid login, could be a succesful post by a user, or a special setting, or a page redirect. The user has to be informed of such things when it happens. And then I test the bits of the status cookies and when it has been acted upon, I set the bit to 0. And when the whole status byte is zero, I remove it from the cookies.
Why would I want to send a cookie, if it is not neccessary. Why would I want to use several cookies for status either.
I didn't go this far, but let's say you have 8 bits. You can use a counter on the first 4, such that it is between 0-15 for language selection, then the rest of the bits for status of the page.
Cookies are expensive in transportation.
I am also very aware not to store anything on a users computer outside of what the browser itself does, unless it is absolutely neccesaary (like with login, I do need a cookie there).
I love bit masks (when managed by my project and not the DB obv). In PHP I set up an integer backed enum, and set each case as `0x1` while bitshifting left using the `
exacly, if you use an enum it is very easy to read
I used to do it with "2**x" (x=0,4,6,8 etc.) same but different
@@MaxHeadro0m 2**x isn't as universal as 1
@@OrioPrisco sure, it's true, in C is that not possible but I don't care. In PHP it is and it is much more readable and decently shorter as well. If I'd review the bitwise I wouldn't mention it, it's ok in the end. Doesn't matter.
My relationship with MySQL more than 20 years but this video explains me how Bitwise operations work. Thank you for this nice explanation. I understand that this is not useful for me to use in any production application :)
Truly incredible.
*This **_flags_** thing is amazing!* I constantly use it in my applications; I had never used it in SQL.
But, to update it adding/removing flags in the application layer, obviously, you have to fetch the current value from the database first (which can be outdated when you push the new value without locking the row). I never thought about using bitwise in SQL queries to update the values. 🤯
What do you mean the new value might be outdated if you push without locking the row? You would get the newest value?
@@connorb.3796 i meant... If there are 2 applications editing the same flag, in the front layer you will push the integer value. Like 3, that is 2|1 when originally was 2, but in the other instance they add 4 to that 2, 2|4, sending 6. Depending on which one was sent last.
Using the method in this video, that is not a problem, because you are not sending the current value along with the flag you are adding, but the flag to add instead. The final integer value will be 7, 2|4|1, as expected
You can do binary updates without first fetching the value.
Addition: UPDATE users SET flags = flags | :added_flag WHERE id = :id
Subtraction: UPDATE users SET flags = flags & ~:removed_flag WHERE id = :id
@@vukkulvar9769 That's what I said. 😅 I was talking about application layer.
the good stuff bro 🔥
😇
love it !
thank you so much
Thanks and congrats on the video, guys! It was educational and entertaining as always! as @jesusrdev said "I appreciate the way you break down complex concepts and explain the 'why' behind things."
I appreciate that! 🫶
Thanks for the great SQL videos! They've been really helpful, especially as I'm designing a complex database schema for a multi-tenant SaaS app.
I appreciate the way you break down complex concepts and explain the "why" behind things.
Keep up the great work!
Absolutely love to hear that. THank you so much!
damn, this video was awesome, you got yourself a new subscriber
Yessss love to hear it. Thank you!
Great video!
Thanks!
That made me chuckle a bit. You basically created dip switches. Not that unhinged. I might actually use this.
Would you recommend this bit mask approach to store access control (r, w, d, x) information on data, like the Linux access control management? We have several portal usergroups and each group should have specialized access to datasets, for example they can only display data, update data, even delete data or execute special functions.
I learned about bitwise operations and how to use them for config flags when I was setting up multiplayer games in Quake 2 around 25 years ago. Turning on and off settings was updating a number on screen which was then saved in a config file. I'm proud of the fact that I figured out how it works on my own. I was 15 or so at that time.
Haha, that was my immediate thought too! Cool that we had the same experience at the same age. What a time to be alive! :D
I am amazed by your channel. Your presentation is amazing - i love every single second! I know it's impolite to ask, but how do you do your presentation?
I think Steve the Editor uses After Effects
Best case scenario for this is probably if you’re using a free cloud db for a small hobby project that only gives you a tiny amount of storage space like 5mb, but there's probably better ways to manage that space too before trying something like this.
what would be advantage of using this method over usual boolean column types? How does it impact IOPS performance? I assume it would increase? Since we'd have to read one particular column multiple times for flag checks.
I first saw this idea used for a scheduler program. Each bit represented a day of the week so 1= run on Sunday only, 2=Monday only, 3 = Sun+Mon, etc. A fun challenge was converting the tinyint into a readable string (e.g. 42 becomes "-M-W-F-")
Hmmm that's actually kind of a fun idea for another video. A neat challenge. I might do that!
That sounds like an interview question
That's hard because there are two t's and two s's.
But you could look up the ASCII code for M,T,W,F,S
and then try to find some correlations between the bits of the flags and which bits sets them out.
Just doesn't look to me like it can be put on an algorithm at first glance at least.
The starting point would be the "-" I think?
For fun here are the ASCII codes and bits
- 55, 00101101
M 115, 01001101
T 124, 01010100
W 127, 01010111
T 124, 01010100
F 106, 01000110
S 123, 01010011
S 123, 01010011
I mean, it's not hard if you do it with a loop
FlagByte = 42
FlagList = Array("M","T","W","T","F","S","S")
For BitNo = 0 To 6
BitDec = BitNo^2
If FlagByte AND BitDec Then
Print FlagList(BitNo)
Else
Print "-"
Next
Next
But I don't know if that was the real question. You can instead make 6 single operations and then just shift the bits necessary for each bitNO such that it matches the ASCII character for that BitNo, maybe that will be more efficient.
Set BitDec = 1 by default
and then Shift Left on BitDec for each iteration instead of using ^ operand
Bitwise is great, but I wouldn't use it without some kind of helpful and easy to understand wrappers that I build myself or something that an ORM provides. Either way, great explanation as always!
Fully agree!
There's the SQL native SET type, which is meant for such situation and not bother with an unmaintainable mess of binary numbers without meaning.
Column: flags SET("dark_mode", "super_admin", "notification_opt_in")
Query: SELECT * FROM users WHERE FIND_IN_SET("dark_mode", flags) LIMIT 10;
Internally it's the same binary trick, but the SQL engine automatically convert string to binary and binary to string.
The ENUM type is the equivalent for when it can only have a single value.
Hi, which tool are you using to generate the presentation at 09:50 for example? I would love to use graphics like that.
I believe Steve the Editor uses After Effects!
Been using bitwise forever and still do in DB; You're not limited to just 8 values, you can go up to at least 31 bits before you need to worry about sign. And especially if you're passing this value to a Javascript frontend they are restricted to 32bit integers regardless of architecture so watch out for this.
A suggestion I would throw in is to keep track of the bit offset values in the flags comment field. Eg. 1:Admin, 2:User, 32:Disabled, 64:Deleted
hey can you make video over your thoughts on mysql and potsgreSQL
It's *such* a divisive topic... but it's on my list
Great for common properties like permissions, brutal for random, unrelated config values
Using the CONV() functions would make this way more human readable on the DB level too. You could use CONV(flags, 10, 2) to convert the flags column from int to the binary, which would allow humans to check more easily which flags are set. For updating, you could use something like CONV(00000010, 2, 10) to identify the mask more easily.
Oh nice, didn't know about CONV!
Or you could simply use the SET type, which is meant for such situation and not bother with an unmaintainable mess of binary numbers without meaning.
Column: flags SET("dark_mode", "super_admin", "notification_opt_in")
Query: SELECT * FROM users WHERE FIND_IN_SET("dark_mode", flags) LIMIT 10;
Cool video! What's the benefit of using this instead of a column of type BIT for each flag?
The only benefit would be that you don't have to alter the table to add new flags if they are all shoved in the same integer column
just googled out of curiosity, and it seems like MySql supports bit-value literals. It seems like perfect way to present the flags and prevent confusion, for instance sth like this : b'01000001' or 0b01000001.
Oooo that's neat
SQL also support ENUM and SET type which let you have human readable strings while internally managing things as binary fields.
@@vukkulvar9769 I would just store my enums on my app instead, not on mysql
I’m surprised there was no mention of where developers use bitmasks regularly with maybe not even knowing it: file permissions on Linux and macOS. So people may have seen file permissions expressed as “tokens” using “r” “w” and “x”, but also expressed as numerical values like 0755.
Haha I told this exact thing to fideloper on Twitter!
I actually really like stuff like this, because you can send multiple settings in a single parameter. It should be used for things with a very limited scope though.
It does mean your team will have to understand bitwise operations or at least bitmasks though.
Bitwise operation is a core part of the computer, if you do not understand bitwise operations, how can you even become programmer. I grew up with C64, where this was the first thing you learned, because there were small turtorials shipped with the machine on floppy. But watch Ben Eaters video tutorials on how to build a bread board computer, you will get much more respect for bits. The instructions in micro code would be extremely expensive without bitwise operations. I don't even know how that would be done.
@@AntiAtheismIsUnstoppable It's not something you need to know in most cases as the things you'd normally do with bitwise operations are simplified greatly with more common syntax.
I've used it for some embedded stuff, but that was for performance reasons, not because I didn't have simpler options available.
@@CottidaeSEA I can see what you mean, because you can abstract away lots of the hard stuff, but don't you think in order to do programming you need to understand what is going on under the hood?
A taxi driver who doesn't understand how his car functions underneath, I find that a little odd. I mean, it is his tool for his work. He should be very interested in that tool and how it works, since it is his livelihood.
I can say the same about frameworks, which also abstract away all the hard stuff, but if I use a framework, I want to know the language it is built on first and how it functions under the hood. That is also the general advice that I have gotten from others. You use frameworks, when you already know vanilla JS and just want to do the programming itself faster, instead of you yourself making your own framework, which takes a lot longer.
@@AntiAtheismIsUnstoppable Do you need to know the exact chemical composition of your car fuel in order to drive it?
If not, I hope you see my point.
@@CottidaeSEA That's not what I mean. Programming can be done in many ways, and you can come to the same result using many different strategies. Not all strategies are equal when it comes to performance and security for example, both which are important on the net.
I used my blog as an example. It has 30 different labels, which I can choose from for a blog post. And because I use bits, I cannot change the meaning of the label, when it is defined, although I can use a translation to other languages. And the limitation is also 30 different labels.
But you need to ask something first here. Because why would you use more than 30 different labels, which is already a lot, and why would you want to change already defined labels, which means basically restructuring the whole blog anyways?
My objections to the video are, that it says this technique cannot be used in real production. Which is not true. It is used every day. And many are not aware it is used either. It's not even a technique actually, it is how the computer is built. On OR, AND and NOT gates. It's simple boolean logic, which I would expect people learn in university.
Very interesting and explained in a super smooth way, but I would love to see a performance test with this technique vs another with 6 individual tiny ints columns measuring query's time ;) Also great work Steve, the editor!
Steve is the GOAT. I'm not sure how much difference there would be as compared to six tiny int columns. I think the win here is that you can continue to add new flags without adding new columns. (Which you could do with a JSON column, too!)
It's extremely efficient, especially when filtering, because you can do many filters in one go. I don't understand how anyone can become programmer without knowing bitwise operations. I didn't think that was possible.
But imagine a DJ who runs a light show. Now he wants to program 16 channels to make sequenses. HOW will you do that without using bitwise operations?
So, the first 4 channels are:
1 2 4 8 -> Bit value
0 1 2 3 ->bit #
You can for example sets channel 0+2 and 3 using only one operation, just OR it with 1+4+8 = 13.
You can reverse using XOR, and with little math, you can use AND to unset them.
If you name them in bytes, you will lose a whole byte for each channel, and when setting or unsetting, you will have to do each operation individually.
@@AntiAtheismIsUnstoppable programming has next to nothing to do with bitwise operations, because the scope of actual "bits" in reallife is very narrow. Literally what you said still can be done and will not really have problems apart from performance if done in any other way without bitwise operations.
And especially considering how low the bar for programming is nowadays people tend to never learn the hardware, too many high-level programmers nowadays that have no clue how anything works. But then again, the demand for such developers is incredibly small, you are basically bound not to find a job for yourself if you spend your time on that
@@noobgam6331 Tell me how you can set a sequense using bytes instead. So, instead of having bit 0-7 in one single address, you now have address 0-7, which is each 0-255, and all you use of each addfress is the first bit. The rest is unused.
So you want to waste 7 addresses in a chip, where yhou could have done it with 1 address. And lets go to comparing. Instead of comparing many bits with each other, which can be done in one operation, now you have to load the value from 7 more addresses and compare them how? The easiest way is to again use bitwise comparison, just using indirect addressing which to me seems rather dumb.
Remember, that each time you have to read the channels or set the channels, in your version will be wasted 7 addresses each time, and all operations will waste at least 7 cycles.
Now imagine that these channels are shifting with the music, you will be wasting billions of cycles.
It will cost you even in the electrical bill.
To be honest... I would never hire a _programmer_ who doesn't understand bitwise operations. It's like adding and subtraction, it's something I will expect a programmer to know. It takes literally two days to learn, if you're very slow. Notice we are talking about a _programmer._ A programmer is expected to know certain things about his tool, or else he should call himself something else than programmer. It's like a front end designer, who doesn't know any CSS, because all these CSS frameworks abstracts the CSS away. How can you even be front end desinger and not know CSS. I do not understand.
what if you want to check whether 2 or more bites are turned on/off with 1 query - would that be:
(ex. checking if the 3. and 4. bit is both turned on)
1. WHERE flags & 4 = 4 & AND flags & 8 = 8
or
2. WHERE flags & 4 = 4 & 8 = 8
what if we use 8 bool columns instead? same functionality, size, but more readable. Not sure but I do think we could index those as well.
Furthet we could break those down into another table if we don't to increase number of columns
The main difference is the size. Bool columns take up 1 byte (8 bits) for a yes/no value.
That's why in our current project, we're using int columns to store a lot of boolean values.
Yup, @minhquangpham is exactly right! It's about 1) size and 2) flexibility. You can keep adding new flags without having to alter the table to add new columns.
@minhquangpham I'm so pumped to hear you're actually using it!
Bitwise is for performance, at the cost of human readability.
@@minhquangpham my bad, I didn't know that. It seems like MySQL takes one byte for bool columns.
I just assumed that bool would be 1 bit for yes/no.
@@phoneywheeze9959 Glad to help :D
I think 1 byte per boolean column is universal. PostgreSQL follows that, too.
Worked on a microsoft sql server project that had hundreds of stored bits in multiple bigints for user profiles. Saves memory, but indexing isn't possible anymore, so every select results in full table scans which can get slow with millions of rows
Yup! One of the reasons I'm not sure I recommend it (mentioned at the end)
Please explain databases indexing
Coming up soon! Probably 3-4 videos out
@@PlanetScaleamazing thanks, would love if you can go quite deep, as understand how it’s done under the hood would help us make better use of MySQL !
Interesting idea, Now I'll just put it right back down where I found it.
Probably the correct conclusion.
Possibly dumb question but can you share your lighting setup? As someone who also wears glasses, getting lighting so the reflection isn't visible in the glasses is difficult...
Generated column for indexing?
What SQL client do you use?
TablePlus! czcams.com/video/7V_CJBPZPes/video.html
I wanted to know which software you are using for running the queries and where can I get it from. Also, love the content! 💙
I'm pretty sure he did a video on that, either on this channel or on his own channel.
Hooray! Another chance to plug the video I did on it! czcams.com/video/7V_CJBPZPes/video.html
@@PlanetScale I probably consumed 12+ videos in last 15 hours... HOW IN THE WORLD I MISSED THIS ONE🌍🤔
@@TES-A5 No idea, but ask and ye shall receive!
when will you release the next video, eagerly waiting!!!
Early next week!
@@PlanetScalethat means many to many relations video might come 2 weeks later, is there any article you may give me with details cause that I am really in need of a good solution
The only question I have is: what SQL GUI are you using?
Just kidding! I love your videos!
Hahaha man, I saw the notification pop up and thought... here we go again. You got me
9:55 You are describing the XOR operator here, not OR operator. OR is: "At least one of both is ON". I know that's a bit picky, but XOR is not OR. ("bit picky": that pun was not intented)
Haha shoot, there's always some mistake!
XOR inverts a bit, while OR sets it
@@AntiAtheismIsUnstoppable that is also not precise. XOR returns false if both are true. OR will return true if both are true. So both operators can set to true.
@@arnothar8035 What I mean is, you can turn a bit on and off based on what it was before (toggle). So, if you want to inverse the n'th bit of byte X just use X XOR n^2.
But more what this is saying is, I want this, this and this bit to toggle, not any other bits.
And this can be used to toggle many switches at the same time and then make sequenses, which can be used for lightning for example. I learned about this when I worked with DJ controller programs.
But yes, it is more than that, as it also has the same truth table as addition. That was something I didn't realize until I watched Ben Eaters channel, but it's so obvious really when you know.
This type of knowledge I have no idea about😂
I used to implement this in one of my college projects. It seems genius, but I probably wouldn't apply it in any professional projects
My takeaway as well
To find unused values, isn’t possible to make a bitwise or over the whole column ? Any bit at 0 would be an unused flag.
Thanks for that deep dive !
Yes. If you have to do a loop over many bits, you can test it in chunks, and each chunk you see if it is zero before going on.
For example,
0101 0000
The upper four bits are all zero and this can be tested in one operation. In this case bit 4-7, which is 16+32+64+128, so, test that your flag byte AND 240 is zero, then the four upper flags are all zero and you do not need to test them individually.
This can also (to an extend) be used to set a counter such that it always is less than X. If you have a number, and you AND it with X-1, then you will always have a counter which keeps in a range, starting from 0.
Where bit number value (X) is 2, 4, 8, 16, 32, 64, 128
So, for example, 1 AND 15 is 1
but 16 AND 15 is 0, and 17 AND 15 is 1 aso. So the result will never be higher than 15. It's a cheap mod operation.
How indexing works with flags? Would it run full scan every time I need to filter by some flag? also it seems do not satisfy 1st normal form. I believe this video should definitely starts with a disclaimer: "never use this in real project"
i want to query to list of flag by flags on user data. How to get it?
lets say:
1: dark_mode
2: super_admin
3: notification_opt_in
and on user id=1 have flag 9.
how to create query to list the flag?
You cannot use 9 for one flag because 9 consists of two bits, 0000 01001 => Bit#3 + Bit#0 => 2^3 + 2^0 => 8 + 1
Each flag must be exactly one bit, and each bit is 2^Bit#, so your setup could instead be:
1: Dark_mode: Bit#0, 2^0 = 1 for ON and 0 for OFF
2: Super_admin: Bit#1, 2^1 = 2 for ON and 0 for OFF
3: Notification: Bit#2, 2^2 = 4 for ON and 0 for OFF
Now there are no collisions between the flags, because each flag is exactly one bit and their own bit too, so now you can use any boolean logic on them. You can clear one or more flags, set one or more flags and test one or more flags, all in one go.
just use the built-in SET data type instead...
If you implement this combined flag as one integer, would it violate any of the normal forms?
I'm not sure, but I don't think so?
@@PlanetScale Fair, it’s a pretty obscure question. I just figured it’s analogous to lumping all the flag data into one multi-valued field, which would usually violate 1NF. Don’t know if it translates to this case though.
It definetly violates 1NF, value in column must be atomic. So do not ever use flags in rdbms. If you need something like this - go for wide tables with columnsets. Not to mention SARGability of this.
Yup my teammates will definitely hate me because what in the BIT is going on 😅
I thought we all agreed to go for clarity over being concise and smart Aaron 😫?
This is really cool. I will certain use in my personal prohect
so is this available only to MySQL?
Nah should work anywhere
Could you please tell me if your glasses have glass in it? I want to ask you this question for a very long time,
It's a secret 🤐
I just prefer redis when feature flags
but now since I know I can do this in 1 col in squel, I guess its time to annoy some coworkers
Good luck, lemme know how much they hate it
13:45 you can test leftest used bit (no bits lefter can be 1) using less than (=) comparisons:
you can replace `flags & {number where only leftest used bit is 1} = {number where only leftest used bit is 1}` with `flags >= {number where only leftest used bit is 1}`
you can replace `flags & {number where only leftest used bit is 1} = 0` with `flags < {number where only leftest used bit is 1}`
examples (02:33):
test if new_legal_disclaimer is on: `flags >= 128`
test if new_legal_disclaimer is off: `flags < 128`
Interesting way to do feature flags but I agree with you it's not very user/developer friendly. If I'm being honest my brain exploded half way through trying to keep up. 🤯
The solution to readability for this is functions and/or functional index (Generated Columns). You're all welcome :]
Personally, I would use booleans though.
Mentioned at the end! Sadly you can only index individual bits that way 😥
I used to do this a lot back in the days. Now I do not, because it's do hard to understand what the stored number means when just looking at it. The developer experience benefits if having dedicated columns are much much better than the drawbacks of having more columns.
For readability, it's almost worse than regex 6 months later.
Instead of flags & 9 = 9... you could do flags & 8 + 1 = 8 + 1... trivial for MySQL to make those into 9, but helps the human brain to see "oh, that's the 1 flag and the 8 flag"
oh that's a nice compromise, I like that. I'd probably wrap in parenthesis because I'm paranoid 😅
Very messy but interesting way
Haha, exactly
Am I missing a point? Why don't store individual flags into their own column with true/false values?
It's primarily about flexibility! Feature flags can be relatively short lived and not last forever, so adding and removing columns can be a bit of a pain.
True and false takes up one byte. But if you use bitwise operations, then one byte can hold 8 flags, each which is either true or false, and can be set and tested and cleared in one operation. And you can test, set and clear up to 8 flags at the same time too.
So, when you store a boolean as true or false as one byte, then you basically waste 7/8 bits, compared to if it is an integer byte, you then use all avalable space, 8 flags.
It's basically the same problem, which is found when you store a number as ASCII in a text file. Each cifre is then one byte, so for example, number 255 would take up 3 bytes. But if you could store it as binary, then one number would be based on the type, so for example 0 and 255 would both take up one byte, because 255 is the highest number that can fit into one byte.
I've been doing this for DECADES. Kills me how ORM's like Prisma don't even support this.
The Primeagen should drop Flip in favor of Steve
Steve is the best
steve ftw
Standard Steve W
Me watching the bit where you use dark mode as an example: *calm*
Me when you turn dark mode off: 😳👀👁🔥😖🤕
Surely you saw it coming though right!
@@PlanetScale yeah for like a second! 😂
I know this is probably just for information's sake, but it doesn't make sense to develop something as unreadable as this just to save a few bytes. Feature flags are typically in the order of tens, hundreds at most.
Yeah in the video itself I say "probably dont do this"
It's not just to save a few bytes. It's extremely efficient to do filtering using bits, which makes performance very high. And because you know it has to be a number, you also can use it to enhance security.
I use a layered security structure with small tests, very fast, which each gives an indication of attack. But if I get a qery value for one or more flags in the filter and it's not integer value, then I know I can throw away the resquest. Why would I bother serving the whole page to someone who is obviously just attacking the site.
I'm hoping to learn that I'm not giving up anything by mindlessly sticking with booleans
🫡 I think you shall. (The next video on JSON may convince you otherwise tho)
PR REJECTED!!
Probably... or maybe we could try it. No, probably not. Unless... Ha, well? No. Ok let's try it!
Shout-outs to Holly
Bless. Helping a guy out, I appreciate that
2:23 was a bit painful
I'm fine with bit storage. However, it's already questionable in most modern languages given that systems have gigabytes of main memory and it's almost insane to be using it at the database level. It's actual use cases are pretty limited.
1. If you can name the items, there can't be that many of them. If you have 10 or even 15 flags, saving 15-60 bytes of storage per record on in a SQL database is meaningless. If it has a string string field or memo-type field, any storage space savings are pretty pointless.
2. If you have a lot more items, then you run into issues with the size of the underlaying integer field. Is it 16, 32, 64 bits? Is it signed?
3. If you add more items than it has bits than the system has room for, you need multiple integer fields.
4. If you later find an item has more than two values, you now need multiple bits for it and complex logic. What if DarkMode goes from true | false, to yes | no | use system setting? Now they add another bit DarkModeSystemSetting (not even next to DarkMode) but it now DarkMode should be ignored if DarkModeSystemSetting is on? Or should it?
5. If you later remove a field you have all these unused bits taking up space vs. just deleting a column or noting that it is unused.
6. You can't index these fields. Sorts and selects become brutal. If you want to see all records with the 'Sale' flag, you force the database to search all records.
7. Dealing with these can be a nightmare. If you export it to Excel, need some analytics tool or reporting tool to look at this, etc. not only do you have to explain this to someone, but their language/tool might be completely unable to deal with this.
8. Switching databases or transferring data, rewriting applications, etc. can be a nightmare if the base integer type changes or the new system handles these differently.
9. Adding items after the fact become far more difficult.
10. Because of the bitwise operations, you expose the data to massive loss due to programming errors. If you write a program to set a bit and it does an AND instead of an OR, it clears out all your settings. Likewise documentation errors could be utterly fatal. Developers use the wrong bit values they are destroying data.
11. Besides just using separate fields (which solves nearly all issues), there are lots of other ways to handle this which are far easier for other systems and programmers to figure out.
a) Many databases support JSON fields { "DarkMode": false, "SuperAdmin": true }
b) Many databases support array fields SELECT * FROM USERS WHERE Settings[1] = true // get superadmins
c) You can encode multiple values into strings. "DARK_MODE,SUPER_ADMIN" You can see from the data what flags it holds, you can query it, third-party systems can report on it, etc.
d) You can encode into strings by code or string position. "DSN" or "D N"
etc.
I don't know how many times I've seen fancy encoding schemes on databases and had to write views to display them as different columns anyway so they can be exported or used by some reporting tool. Why is saving a few dozen bytes on disk worth it?
this is a simplified take, but couldn't you just translate it back into binary and process it like thus:
const array1 = [1, 0, 1, 1, 0, 1, 0, 1];
const array2 = ["feature1", "feature2", "feature3", "feature4", "feature5", "feature6", "feature7", "feature8"];
for (let i = 0; i < array1.length; i++) {
if (array1[i] === 1) {
console.log(`Enable ${array2[i]}`);
// Perform the action to enable the feature (e.g., set a flag or perform an operation)
} else {
console.log(`Disable ${array2[i]}`);
// Perform the action to disable the feature (e.g., set a flag or perform an operation)
}
????
The question is why? Why Aaron why?!
Rarely do you come across an idea that is equal parts awesome and terrible. Sometimes you just gotta go for it!
@@PlanetScale Explain terrible. PLC controls are built on bits and only bits. Lots of companies wouldn't be able to produce anything without bitwise operations.
DJ light and sound, uses bits for channels.
Actually, anything that uses channels, also uses bits.
Maybe you think it is terrible, because you have never done any real research on it, outside of this video?
And ev en then, yhou haven't done research on where it _is_ used in production on websites using SQL?
This is amazing and horrrible at the same time. I'm so confused with my feelings rright now. I mean, it's so practical and at the same time I know I'm gonna hate myself in 6 montyhs when reading my own code.
😏 why not give it a try... what's the worst that can happen? (don't blame me!)
You know, we can same more space if we only store the last 2 digits of the year instead of all 4 digits. Someone else will come along and fix it before it becomes a problem.
Just 8? Isn't integer 32bit 😭
Tiny int is one byte! That was the example I was using
This feels like it’s worth being sent to jail over
Like it's so awesome you'd do it even if you went to jail for it? Or like I should go to jail for even doing it?
Either way, yeah, agreed.
This is cool but you could not store a flag that could be null.
That's true, it'd have to either be true or false!
No, but you can combine counters and flags in one byte, which is very neat. You could use a counter to store null and undefined and then the flag, so 3 bits. Don't know why you would do it though. But using flags is very nice for transportation of data, because it's so packed. And you can test, set and clear more than one flag in one go, which makes it very very efficient. Remember, when you do comparison, almost each time you use a number which can be refered to as a bit, you can use tricks to speed things up. For example
X mod 64, can be sped up because 64 is one bit, Bit#6, 2^6 = 64, so instead you can do X And (2^6-1) => X And 63, which is faster and gives same result. Some compilers know this trick, but it's good to know as a programmer too.
Unless you're constantly selecting or updating this at an extremely high rate, do not do this in real world applications unless you absolutely have to.
Cosign
While the knowledge on bitwise operations is incredibly useful, it is very useless in the example given here. Why store a flag and made it complicated when MySQL has a `BIT` type that can just store a boolean instead? This way you can define a range of booleans to search on and this does not force to to stick to a certain flag.
Love the opening - this is interesting but you’re going to be disliked.
You speaking as if this wasn’t required for many apis across every operating system and a fundamental skill of computer science is confusing and alarming me.
One day (perhaps a long time ago) you learned this for the first time. Why can't that day be today for someone else?
@@PlanetScale I think its great you're teaching this! I just don't get your strangely negatives attitude towards it. Bitwise operators are the fundamental language of the CPU and its incredibly fast as well as being small. If you don't like having to remember the flag values you can do small bitfield mapping tables with power of 2 check constraints and simple "... where & ( select Sum(bitflag) from bitflagmappingtabel where name in ('flag1', flag2')" even bury it in a stored procedure.
You presented this as esoteric and a bad idea when its very commonly used way to speed up large data sets in enterprise Just didn't get you kept circling back around to making it sound like a bad idea.
@@Diamonddrake Exactly, that surprised me too. And so many others here who are not even curious about it and why it is a thing. I have never before I went to this channel, heard of a programmer who doesn't know bitwise operations.