Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

My notes on Gitlab's Postgres schema design (2022)

shekhargulati.com

Post reply on HN
Print view View original

161–170 of 170 posts

Re: My notes on Gitlab's Postgres schema design (2022)

#161

Post by no-dr-onboard »

s4i wrote: post #71
yellowapple wrote: post #70

> It is generally a good practice to not expose your primary keys to the external world. This is especially important when you use sequential auto-incrementing identifiers with type integer or bigint since they are guessable. What value would there be in preventing guessing? How would that even be possible if requests have to be authenticated in the first place? I see this "best practice" advocated often, but to me i…

It’s mentioned in the article. It’s more to do with business intelligence than security. A simple auto-incrementing ID will reveal how many total records you have in a table and/or their growth rate. > If you expose the issues table primary key id then when you create an issue in your project it will not start with 1 and you can easily guess how many issues exist in the GitLab.

Business intelligence isn’t really applicable on the database level with guids..way too many abstraction layers down.

Re: My notes on Gitlab's Postgres schema design (2022)

#162

Post by yellowapple »

worksonmine wrote: post #159
yellowapple wrote: post #158

Earlier quoted context omitted.

> Then how would you iterate it? Like with any other number. UUIDs are just 128-bit numbers, ranging from 00000000-0000-0000-0000-000000000000 to FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF. I'll concede that iterating through the entirety of that range would take a very long time, but this still presumes that said iteration in its entirety is necessary in the first place. > If you generate a billion UUIDs every second for…

> I'll concede that iterating through the entirety of that range would take a very long time You don't say? > but this still presumes that said iteration in its entirety is necessary in the first place. It is, because it's compared to sequential IDs where you know exactly the start and end. No way of knowing with UUIDs. > Maybe, if they're indeed randomly-generated. Are they indeed UUIDv4? Is your RNG up to snuff? St…

> This entire thread was a complete waste of my time.

The feeling is mutual. Have a nice day.

Re: My notes on Gitlab's Postgres schema design (2022)

#163

Post by sgarland »

SkyMarshal wrote: post #116
yellowapple wrote: post #70

> It is generally a good practice to not expose your primary keys to the external world. This is especially important when you use sequential auto-incrementing identifiers with type integer or bigint since they are guessable. What value would there be in preventing guessing? How would that even be possible if requests have to be authenticated in the first place? I see this "best practice" advocated often, but to me i…

> This is especially important when you use sequential auto-incrementing identifiers with type integer or bigint since they are guessable. I thought we had long since moved past that to GUIDs or UUIDs for primary keys. Then if you still need some kind of sequential numbering that has meaning in relation to the other fields, make a separate column for that.

Except now people are coming back around, because they’re realizing (as the article mentions) that UUID PKs come with enormous performance costs. In fairness, any non-k-sortable ID will suffer the same fate, but UUIDs are the most common of the bunch.

Re: My notes on Gitlab's Postgres schema design (2022)

#164

Post by sgarland »

aeyes wrote: post #114
scient wrote: post #112

Earlier quoted context omitted.

Large tables take hours, if not days. I attempted a test case on AWS using souped up io2 disks (the fastest most expensive disks they have) and a beast of a DB server (r5.12xl I think) and it became abundantly clear that at certain scale you won't be doing any kind of in-place table updates like that on the system. Especially if your allowed downtime is one hour maintenance window per week...

I did it on a r6.24xlarge RDS instance and the CPU wasn't doing much during the operation. IO peaked at 40k IOPS on EBS with provisioned IOPS, I'm not sure if a local disk would be any faster but I already know that rewriting the table and creating the indexes are all single threaded so there isn't much you could gain. Once I got the logical replication setup to work I changed 20 tables on the replica and made the sw…

> not sure if a local disk would be any faster

Uh, yes. NVMe drives can hit 1,000,000 IOPS, plus the latency is orders of magnitude lower than EBS.

Re: My notes on Gitlab's Postgres schema design (2022)

#165

Post by neonsunset »

gfody wrote: post #157
neonsunset wrote: post #141

Earlier quoted context omitted.

Prefer 32-bit does nothing for modern .NET targets. This is actually the first time I've heard the term being used in many years, even back in .NET Framework 4.6.x days it wasn't much of a concern - the code would be executed with 64-bit runtime as a default on appropriate hosts.

the 32bitpref corflag isn't part of a .net core target since those are always il, it is more properly a runtime concern as it should be it's still the default in .net as of 4.8.1 (has been since it was introduced in 4.5 roughly coinciding w/java's pointer compression feature which is also still the default today)

The .NET Framework target might as well not exist :D

Many libraries are straight up dropping NS2.0 TFM and overall community tends to perceive the request to still target it very negatively. Sure, Visual Studio still runs some in-process features on top of it, and so do Office add-ins, but it's an active work-in-progress to get rid of any new code having to be written while still targeting it.

So, in that regard, this setting does not matter.

Re: My notes on Gitlab's Postgres schema design (2022)

#166

Post by gfody »

neonsunset wrote: post #165
gfody wrote: post #157

Earlier quoted context omitted.

the 32bitpref corflag isn't part of a .net core target since those are always il, it is more properly a runtime concern as it should be it's still the default in .net as of 4.8.1 (has been since it was introduced in 4.5 roughly coinciding w/java's pointer compression feature which is also still the default today)

The .NET Framework target might as well not exist :D Many libraries are straight up dropping NS2.0 TFM and overall community tends to perceive the request to still target it very negatively. Sure, Visual Studio still runs some in-process features on top of it, and so do Office add-ins, but it's an active work-in-progress to get rid of any new code having to be written while still targeting it. So, in that regard, thi…

in .net core there's no setting, you target the x32 abi by choosing a runtime or target platform ending in "32" or "x86"

Re: My notes on Gitlab's Postgres schema design (2022)

#167

Post by rglynn »

istvanu wrote: post #95
kelnos wrote: post #2

> For example, Github had 128 million public repositories in 2020. Even with 20 issues per repository it will cross the serial range. Also changing the type of the table is expensive. I expect the majority of those public repositories are forks of other repositories, and those forks only exist so someone could create pull requests against the main repository. As such, they won't ever have any issues, unless someone m…

I'm convinced that GitHub's decision to move away from Rails was partly influenced by a significant flaw in ActiveRecord: its lack of support for composite primary keys. The need for something as basic as PRIMARY KEY(repo_id, issue_id) becomes unnecessarily complex within ActiveRecord, forcing developers to use workarounds that involve a unique key alongside a singular primary key column to meet ActiveRecord's requir…

Whilst I would agree that a monolith can run into scalability issues, I am not sure your characterisation of Rails as such is proportionate.

To say that Rails' architecture is a "sigificant barrier for any project beyond the simplest MVPs" is rather hyperbolic, and the list of companies running monolithic Rails apps is a testament to that.

On this very topic, I would recommend reading GitLab's own post from 2022 on why they are sticking with a Rails monolith[1].

[1] - https://about.gitlab.com/blog/2022/07/06/why-were-sticking-w...

Re: My notes on Gitlab's Postgres schema design (2022)

#169

Post by tianzhou »

semiquaver wrote: post #155
scient wrote: post #111

Earlier quoted context omitted.

Rails migrations really fail to be viable at scale. And its not really because of the migrations in Rails, its because changes in PostgreSQL get very very expensive. Things have gotten better in PG 15, but its still not quite there yet. I've ran into an integer PK being almost exhausted in values twice now. In one of these cases I used pg-osc to rewrite a 8TB table on disk without downtime over a period of a week to…

What has worked in my experience: - decouple migration execution from code deployment. Require that all migrations are in totally separate PRs can be run well-in-advance of the code that depends on the db change being run - maintain a stateful migration execution system using an online schema change system supervised by DBAs - PRs adding migrations still need to be merged and deployed, but can only do so after the ac…

+1. We have been working on a schema migration tool called Bytebase for 3+ years. And we follow the similar principles.

Re: My notes on Gitlab's Postgres schema design (2022)

#170

Post by zx8080 »

zX41ZdbW wrote: post #138
zx8080 wrote: post #48

Earlier quoted context omitted.

Also, > `repo_name` LowCardinality(String), This is not a low cardinality: 7133122498 = 7.1B Don't use low cardinality for such columns!

The LowCardinality data type does not require the whole set of values to have a low cardinality. It benefits when the values have locally low cardinality. For example, if the number of unique values in `repo_name` is a hundred million, but for every million consecutive values, there are only ten thousand unique, it will give a great speed-up.

> LowCardinality data type does not require the whole set of values to have a low cardinality.

Don't mislead others. It's not true unless low_cardinality_max_dictionary_size is set to some other value than the default one: 8192.

It does not work well for hundred million values.

Post reply on HN