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

131–140 of 170 posts

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

#131

Post by coldtea »

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…

>I see this "best practice" advocated often, but to me it reeks of security theater.

The idea of "security theater" is overplayed. Security can be (and should be) multilayered, it doesn't have to be all or nothing. So that, when they break a layer (say the authentication), they shouldn't automatically gain easy access to the others

>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.

Sure. But by that time, it's will be game over if you don't also have the other layers in place.

The thing is that you can't anticipate any contigency. Bugs tend to not preannounce themselves, especially tricky nuanced bugs.

But when they do appear, and a user can "do [something] useful with an ID without being authenticated and authorized to do so" you'd be thanking all available Gods that you at least made the IDs not guassable - which would also give them also access to every user account on the system.

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

#132

Post by mvdtnz »

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?

You can't throw around specific metrics ("two outages they had last year") then claim you were just sharing a personal anecdote when someone calls you out and brings receipts. Just take the L.

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

#133

Post by heyoni »

mvdtnz wrote: post #132
heyoni wrote: post #99

Earlier quoted context omitted.

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?

You can't throw around specific metrics ("two outages they had last year") then claim you were just sharing a personal anecdote when someone calls you out and brings receipts. Just take the L.

I absolutely can because this is a discussion. Being called delusional for sharing my experience on a platform I use _at least_ 40 hours a week was a bit much but in case you missed it, I relented. They brought receipts and I came down from my position because there was none. I wasn't arguing with anyone or saying anyone was wrong about anything.

Do you expect all of your conversations to be backed by facts and citation? Is that why the only comments you make are aggressive and confrontational? Because it's easier than the bar you're setting for everyone else? Yea, I looked back at your history, not a single url.

And now I'm taking a position.

> No worries you’re fine. I guess downtime was much more widespread than I could even imagine.

Did you miss that?

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

#135

Post by kehers »

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…

One good argument I found [^1] about not exposing primary keys is that primary keys may change (during system/db change) and you want to ensure users have a consistent way of accessing data.

[^1]: https://softwareengineering.stackexchange.com/questions/2183...

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

#136

Post by worksonmine »

yellowapple wrote: post #129
worksonmine wrote: post #94

Earlier quoted context omitted.

> What value would there be in preventing guessing? It prevents enumeration, which may or may not be a problem depending on the data. If you want to build a database of user profiles it's much easier with incremental IDs than UUID. It is at least a data leak but can be a security issue. Imagine a server doing wrong password correctly returning "invalid username OR password" to prevent enumeration. If you can still cr…

> If you can still crawl all IDs and figure out if someone has an account that way it helps filter out what username and password combinations to try from previous leaks. Right, but like I suggested above, if you're able to get any response other than a 404 for an ID other than one you're authorized to access, then that in and of itself is a severe issue. So is being able to log in with that ID instead of an actual u…

> Right, but like I suggested above, if you're able to get any response other than a 404 for an ID other than one you're authorized to access, then that in and of itself is a severe issue. So is being able to log in with that ID instead of an actual username.

You're missing the point and you're not thinking like a hacker yet. It's not about the ID itself or even private profiles, but the fact that you can build a database of all users with a simple loop. For example your profile here is '/user?id=yellowapple' not '/user?id=1337'.

If it was the latter I could build a list of usernames by testing all IDs. Then I would cross-reference those usernames to previous leaks to know what passwords to test. And hacking an account is not the only use of such an exploit, just extracting all items from a competitors database is enough in some cases. It all depends on the type data and what business value it has. Sometimes an incrementing ID is perfectly fine, but it's more difficult to shard across services so I usually default to UUID anyway except when I really want an incrementing ID.

Most of the time things don't have to go "horribly horribly wrong" to get exploited. It's more common to be many simple unimportant holes cleverly combined.

The username can still always be checked for existence on the sign-up step, and there aren't many ways of protecting from that. But it's easier to rate-limit sign-ups (as one should anyway) than viewing public profiles.

Do you leave your windows open when you leave from home just because the burglar can kick the front door in instead? It's the same principle.

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

#137

Post by zX41ZdbW »

zx8080 wrote: post #46
zX41ZdbW wrote: post #17

Earlier quoted context omitted.

It is still under the limit today with 362,107,148 repositories and 818,516,506 unique issues and pull requests: https://play.clickhouse.com/play?user=play#U0VMRUNUIHVuaXEoc...

Elapsed: 12.618 sec, read 7.13 billion rows, 42.77 GB This is too long, seems the ORDER BY is not set up correctly for the table.

This is an ad-hoc query. It does a full scan, processing slightly less than a billion rows per second on a single machine, and finishes in a reasonable time with over 7 billion events on GitHub from 2015. While it does not make sense to optimize this table for my particular query, the fact that it works well for arbitrary queries is worth noting.

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

#138

Post by zX41ZdbW »

zx8080 wrote: post #48
zx8080 wrote: post #46

Earlier quoted context omitted.

Elapsed: 12.618 sec, read 7.13 billion rows, 42.77 GB This is too long, seems the ORDER BY is not set up correctly for the table.

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.

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

#139

Post by doctor_eval »

sjwhevvvvvsj wrote: post #122
doctor_eval wrote: post #100

Earlier quoted context omitted.

You are absolutely correct; my previous business ended up ejecting almost all database logic from Java ORMs, and moved almost everything to straight SQL manipulated by stored procedures. Doing so resulted in a significant performance and productivity increase, relative to all the layers of nonsense we used to jump through. One of the difficulties of doing this was that the tooling isn’t great. We had to write our own…

A simple function call as a wrapper for a well optimized sql query just can’t be beat for performance. I have never understood why anybody would use an ORM, it’s usually as much work to learn and maintain them as SQL.

Yes, agreed. I spent a few years away from the technical side of things and when I jumped back in I was horrified at the amount of complexity these things added.

In Java it appeared that JPA was trying to completely abstract the database into the JVM, but it just meant a million annoying cache problems, terrible performance, a serious cognitive load, and poor interoperability with other database users.

The guy who promoted this internally kind of wrote the problems off as being a "leaky abstraction" but it wasn't even that. JPA entirely failed to resolve the object-relational "impedance mismatch" by failing to acknowledge that the boundary between the database and the application is actually a legitimate surface, like any other API surface.

What a nightmare it was.

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

#140

Post by paulddraper »

zetalyrae wrote: post #28

The point about the storage size of UUID columns is unconvincing. 128 bits vs. 64 bits doesn't matter much when the table has five other columns. A much more salient concern for me is performance. UUIDv4 is widely supported but is completely random, which is not ideal for index performance. UUIDv7[0] is closer to Snowflake[1] and has some temporal locality but is less widely implemented. There's an orthogonal approac…

> 128 bits vs. 64 bits doesn't matter much when the table has five other columns.

!!!!

But those 5 other columns are not indexed.

---

There are three levels of database performance:

1. Indices and data fit in memory.

2. Indices fits in memory, data does not.

3. Neither indices not data fit in memory.

If you can do #1 great, but if you don't have that, fight like a madman for #2.

---

Doubling your index sizes is just makes it harder.

Post reply on HN