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

101–110 of 170 posts

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

#101

Post by EE84M3i »

lordgrenville wrote: post #76
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…

> The only case where this might be valuable is business intelligence Nitpick: I would not call this "business intelligence" (which usually refers to internal use of the company's own data) but "competitive intelligence". https://en.wikipedia.org/wiki/Competitive_intelligence

See also "German Tank Problem" https://en.m.wikipedia.org/wiki/German_tank_problem

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

#102

Post by traceroute66 »

Slight nit-pick, but I would pick up the author on the text vs varchar section.

The author effectively wastes many words trying to prove a non-existent performance difference and then concludes "there is not much performance difference between the two types".

This horse bolted a long time ago. Its not "not much", its "none".

The Postgres Wiki[1] explicitly tells you to use text unless you have a very good reason not to. And indeed the docs themselves[2] tell us that "For many purposes, character varying acts as though it were a domain over text" and further down in the docs in the green Tip box, "There is no performance difference among these three types".

Therefore Gitlab's use of (mostly) text would indicate that they have RTFM and that they have designed their schema for their choice of database (Postgres) instead of attempting to implement some stupid "portable" schema.

[1] https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use... [2] https://www.postgresql.org/docs/current/datatype-character.h...

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

#103

Post by imiric »

heyoni wrote: post #99
imiric wrote: post #77

Earlier quoted context omitted.

> I mean GitHub in general has been pretty reliable minus the two outages they had last year Huh? GitHub has had major outages practically every other week for a few years now. There are pages of HN threads[1]. There's a reason why githubstatus.com doesn't show historical metrics and uptime percentages: it would make them look incompetent. Many outages aren't even officially reported there. I do agree that when it's…

Delusional? Anecdotal maybe…I was describing my experience so thanks for elaborating. I only use it as a code repository. Was it specific services within GitHub that failed a lot?

My apologies, that came off a bit harsh.

> Was it specific services within GitHub that failed a lot?

Well, everything from its frontends, to the CI service, to the Git service itself. People weren't able to push, pull, deploy or review code for hours at a time. Just major disruptions all around, which happened regularly.

I do think this has improved slightly over the past few months, but you were lucky if you weren't affected much.

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

#104

Post by plagiarist »

dijit wrote: post #92
tetha wrote: post #79

Earlier quoted context omitted.

> I see this "best practice" advocated often, but to me it reeks of security theater. If an attacker is able to do anything useful with a guessed ID without being authenticated and authorized to do so, then something else has gone horribly, horribly, horribly wrong and that should be the focus of one's energy instead of adding needless complexity to the schema. Yes, but the ability to guess IDs can make this security…

Case in point, a recent security issue Gitlab experienced (CVE-2023-7028; arbitrary password reset by knowing one of the accounts associated mail addresses) was made worse by a feature of gitlab that few people know about; that the "userID" is associated with a meta/internal mail address. This meant that people could send password resets for any user if they knew their userID. The mail format was like user-1@no-reply…

I've already resolved to never use Gitlab entirely on the basis of that CVE but that makes it worse.

Password resets should just never go to an email that hasn't been deliberately attached to an account by the account's owner, full stop. There should not be a code path where it is possible to send any such thing to arbitrary emails. And redirect emails should never be treated as account emails in any way.

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

#105

Post by alex_smart »

traceroute66 wrote: post #102

Slight nit-pick, but I would pick up the author on the text vs varchar section. The author effectively wastes many words trying to prove a non-existent performance difference and then concludes "there is not much performance difference between the two types". This horse bolted a long time ago. Its not "not much", its "none". The Postgres Wiki[1] explicitly tells you to use text unless you have a very good reason not…

>The author effectively wastes many words trying to prove a non-existent performance difference and then concludes "there is not much performance difference between the two types".

They then also show that there is in fact a significant performance difference when you need to migrate your schema to accodomate a change in length of strings being stored. Altering a table to a change a column from varchar(300) to varchar(200) needs to rewrite every single row, where as updating the constraint on a text column is essentially free, just a full table scan to ensure that the existing values satisfy your new constraints.

FTA:

>So, as you can see, the text type with CHECK constraint allows you to evolve the schema easily compared to character varying or varchar(n) when you have length checks.

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

#106

Post by zachahn »

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…

I can't really comment on GitHub, but Rails supports composite primary keys as of Rails 7.1, the latest released version [1].

About modularity, there are projects like Mongoid which can completely replace ActiveRecord. And there are plugins for the view layer, like "jbuilder" and "haml", and we can bypass the view layer completely by generating/sending data inside controller actions. But fair, I don't know if we can completely replace the view and controller layers.

I know I'm missing your larger point about architecture! I don't have so much to say, but I agree I've definitely worked on some hard-to-maintain systems. I wonder if that's an inevitability of Rails or an inevitability of software systems—though I'm sure there are exceptional codebases out there somewhere!

[1] https://guides.rubyonrails.org/7_1_release_notes.html#compos...

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

#107

Post by JimBlackwood »

tomnipotent wrote: post #85
JimBlackwood wrote: post #72

Earlier quoted context omitted.

I follow this best practice, there’s a few reasons why I do this. It doesn’t have to do with using a guessed primary ID for some sort of privilege escalation, though. It has more to do with not leaking any company information. When I worked for an e-commerce company, one of our biggest competitors used an auto-incrementing integer as primary key on their “orders” table. Yeah… You can figure out how this was used. Not…

> extremely useful for my employer. I've been in these shoes before, and finding this information doesn't help you as an executive or leader make any better decisions than you could have before you had the data. No important decision is going to be swayed by something like this, and any decision that is probably wasn't important. Knowing how many orders is placed isn't so useful without average order value or items p…

That’s not correct. Not every market is the same in it’s dynamics.

Yes, most of the time that information was purely insightful and was simply monitored. However, at some moments it definitely drove important decisions.

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

#108

Post by silasb »

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…

Do you have any sources on GitHub moving away from Rails? This is the first that I've heard and my googlefu has returned zero results around this. Just last year they had a blog post around Building GitHub with Ruby and Rails[0] so your remark caught my off guard.

[0]: https://github.blog/2023-04-06-building-github-with-ruby-and...

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

#110

Post by karolist »

tcnj wrote: post #75
mvdtnz wrote: post #34

Earlier quoted context omitted.

Do we know for sure if gitlab cloud uses a multi-tenanted database, or a db per user/customer/org? In my experience products that offer both a self hosted and cloud product tend to prefer a database per customer, as this greatly simplifies the shared parts of the codebase, which can use the same queries regardless of the hosting type. If they use a db per customer then no one will ever approach those usage limits and…

Unless something has substantially changed since I last checked, gitlab.com is essentially self-hosted gitlab ultimate with a few feature flags to enable some marginally different behaviour. That is, it uses one multitennant DB for the whole platform.

Not according to [1] where the author said

> This effectively results in two code paths in many parts of your platform: one for the SaaS version, and one for the self-hosted version. Even if the code is physically the same (i.e. you provide some sort of easy to use wrapper for self-hosted installations), you still need to think about the differences.

1. https://yorickpeterse.com/articles/what-it-was-like-working-...

Post reply on HN