may I ask what the v0, v1, ...v10 mean in those graphs? different pages?
B-Trees and Database Indexes
41–50 of 84 posts
Re: B-Trees and Database Indexes
#42great piece of education. The interactive demos like these help a lot.
Re: B-Trees and Database Indexes
#43This is why you should _never_ make your UUID column the primary key. For one, it's enormous. Now you have to copy that 128bit int to every side of the relation. Two, in most cases it's completely random. Unless you had the forethought to use something other than UUIDv4 (gen_random_uuid). So now you just have a bunch of humongous random numbers clogging up your indexes and duplicated everywhere for no good reason. Us…
> Use regular bigserial (64bit) PKs for internal table relations and UUIDs (128bit) for application-level identifiers and natural keys.
If you are worried about size ballooning, rather than the randomness, make sure you actually need more than 32-bit standard integers (well, 31-bit as they tend to be signed and we usually start from 0 or 1 not -2,147,483,648).
Avoiding roll-over issues is sometimes a valid concern sometimes with 32-bit INTs, but I've seen people specify 64-bit surrogate keys when their data is not likely to grow to where 31 would be an issue in the next few hundred years… There is an argument that 64-bit values are more efficient in modern CPUs, but I've not seen any good tests that show a measurable difference in the context of DB keys where there are other overheads to consider due to the doubling in size.
Re: B-Trees and Database Indexes
#44The cookie modal doesn't work on Firefox mobile and it takes half the height. Why don't let the user set that up on their browser
> it takes half the height.
Ah so that's what the "planet scale" name refers to /j
Re: B-Trees and Database Indexes
#45I realized after a few years of doing it that my strategy for keeping Wikis useful is to treat them as B-Trees. When the landing page gets too full/too many outgoing links, I start pushing links and paragraphs down into the child pages, to leave space for a fair share of timely links and on-boarding docs. Similar and older links get pushed down into the sibling that best represents the topic. Then if the destination…
Do you have a recommendation for Wiki software you like to use? My team is in need of an internal knowledge base, and I like the structure of wikis. Most of the SaaS products I've tried or looked at are a bit too shiny/fancy and don't seem to match my mental model of how a wiki-style knowledge base should work.
If you can self host, wiki.js is easy to set up in a docker container.
Mediawiki (What Wikipedia runs on) is pretty easy too.
If you have a small team, Obsidian and a syncing solution like git or obsidian sync might work.
I was able to work with my company’s it team to set up a wiki which is only accessible from within the network, including by vpn, and is hosted on a vps.
Re: B-Trees and Database Indexes
#46This is why you should _never_ make your UUID column the primary key. For one, it's enormous. Now you have to copy that 128bit int to every side of the relation. Two, in most cases it's completely random. Unless you had the forethought to use something other than UUIDv4 (gen_random_uuid). So now you just have a bunch of humongous random numbers clogging up your indexes and duplicated everywhere for no good reason. Us…
Won't you still need indexes on those UUIDs anyway? And possibly have to do more joins to resolve them?
> Won't you still need indexes on those UUIDs anyway?
Depends on how index pages are structured in the DB. With MySQL (assuming the InnoDB engine) the primary key is pretty much always a clustered index, in MS SQL Server this is the case more often than not too. This means that any page expansion due to splits from the randomness of the data being inserted affects the whole table not just the key index, and rebuilding to claim back the wasted space later will take a lot longer as you are moving all your based data around. With the UUID as a supplementary key, likely indexed on its own, all that gets enlarged by excess page splitting is those 128-bit values not the entire rows and an index rebuild to fix that up after the fact moves a lot less data around.
So yes, you have an extra index on top of your primary key and other additional ones needed by your apps and reports, but not having a random UUID as your clustering key can be a significant benefit. Using more ordered UUIDs minimises this difference considerably though.
Even ignoring the random-key-causes-page-splits issue, if you've mitigated it with more ordered UUIDs, a wider primary key increases the size of all supplementary indexes assuming MySQL arranges things similarly to SQL Server: rather than having a hidden page/row identifier (as SQL Server does without any clustered key defined, it calls such arrangements heap tables), each supplementary index includes the clustering key value with every row. So while having a 32-bit primary key for internals and the 128-bit UUID as an extra key adds 4 bytes per row (for the extra INT32) to the base data, it saves 12 bytes from each row in each non-clustered index (as the INT32 is included, not the UUID).
> And possibly have to do more joins to resolve them?
Usually not. Usually when you have both the INT32 (or sometimes IN64) surrogate key is for all internal use and a UUID only for external references, so the UUIDs are only important as the initial filter and not likely not taking part in a JOIN at all. After the initial filter to find the item you want in the main table of the query, the JOINs to collect data from other tables will all be by the surrogate (INT) keys. The UUID is almost never used as a foreign key reference in this arrangement.
Re: B-Trees and Database Indexes
#47The cookie modal doesn't work on Firefox mobile and it takes half the height. Why don't let the user set that up on their browser
Nor Chrome desktop
Re: B-Trees and Database Indexes
#48may I ask what the v0, v1, ...v10 mean in those graphs? different pages?
I think it's "value #0", "value #1", etc. Confused me too.
Re: B-Trees and Database Indexes
#49I realized after a few years of doing it that my strategy for keeping Wikis useful is to treat them as B-Trees. When the landing page gets too full/too many outgoing links, I start pushing links and paragraphs down into the child pages, to leave space for a fair share of timely links and on-boarding docs. Similar and older links get pushed down into the sibling that best represents the topic. Then if the destination…
Do you have a recommendation for Wiki software you like to use? My team is in need of an internal knowledge base, and I like the structure of wikis. Most of the SaaS products I've tried or looked at are a bit too shiny/fancy and don't seem to match my mental model of how a wiki-style knowledge base should work.
My recommendation is, if you want a wiki for developers, use something that is based on a markup language, sources pages from a git repo, and does not do too much magic behind the scenes. This will result in a more maintained and liked wiki than any bloated SaaS wiki.
Re: B-Trees and Database Indexes
#50Earlier quoted context omitted.
Note that, in the abstract, “vulgar” means “common” (as in “vulgar latin”). Indeed, its negative connotations come from that same sense: “common” people are unrefined.
The association between vulgarity and propriety (and class distinctions) sort of ruins that word, particularly in the english speaking west. I wonder if that's as big of a problem in the romance languages (which all treat left/right the same way - left = bad, right = good)
This goes pretty deep in English. I'd argue that the semantic intention behind the colloquial usage of "vulgar" is nearly inseparable from the "class distinction" baggage it carries. Consider these common synonyms and their etymologies:
- Rude: "coarse, rough, unfinished, unlearned" (https://www.etymonline.com/word/rude#etymonline_v_16610)
- Mean: "shared by all, common, general" (https://www.etymonline.com/word/mean#etymonline_v_12495)
And even synonyms like obscene, indecent, or disgusting, which don't evoke this distinction directly, still almost always ultimately rely on separating things based on what is "good" and "clean" according to class distinctions.