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

51–60 of 170 posts

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

#51

Post by jameshart »

rapfaria wrote: post #4
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…

> Having said that, I agree that using a 4-byte type (well, 31-bit, really) for that table is a ticking time bomb for some orgs A bomb defused in a migration that takes eleven seconds

11 seconds won't fix all your foreign keys. And all the code written against it that assumes an int type will accommodate the value.

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

#52

Post by Merad »

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…

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

But it's not just the size of that one column, it's also the size of all the places that id is used as a FK and the indexes that may be needed on those FK columns. Think about something like a user id that might be referenced by dozens or even hundreds of FKs throughout your database.

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

#53

Post by ssalka »

zX41ZdbW wrote: post #17
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…

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

I'm guessing this won't be including issues & PRs from private repos, which could be substantial

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

#54

Post by Merad »

martinald wrote: post #5

Is it just me that thinks in general schema design and development is stuck in the stone ages? I mainly know dotnet stuff, which does have migrations in EF (I note the point about gitlab not using this kind of thing because of database compatibility). It can point out common data loss while doing them. However, it still is always quite scary doing migrations, especially bigger ones refactoring something. Throw into t…

> Also the point about external IDs and internal IDs - why can't the database/ORM do this more automatically?

It has pretty big implications for how your application code interacts with the database. Queries that involve id's will need to perform joins in order to check the external id. Inserts or updates that need to set a foreign key need to perform an extra lookup to map the external id to the correct FK value (whether it's literally a separate query or a CTE/subquery). Those are things that are way outside the realm of what EF can handle automatically, at least as it exists today.

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

#55

Post by jupp0r »

doctor_eval wrote: post #32
sjwhevvvvvsj wrote: post #8

Earlier quoted context omitted.

One thing I like about hand designing schema is it makes you sit down and make very clear choices about what your data is, how it interrelates, and how you’ll use it. You understand your own goals more clearly.

So many people I encounter seem to think it’s the code that’s important when building the back end of an application. You see this when people discussing database schemas start comparing, say, rails to hibernate. But ORMs emphasise code instead of data, which in my experience is a big mistake. In my experience, getting the data structures right is 99% of the battle. If you get that right, the code that follows is sim…

This is true for in memory data as well. Object oriented programming is great for some problems, but it's also limiting the way we think about data by putting it close to the code operating on it. ORMs do the same to databases by pretending that rows are objects when that's only one way of modeling your problem.

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

#56

Post by sjwhevvvvvsj »

doctor_eval wrote: post #32
sjwhevvvvvsj wrote: post #8

Earlier quoted context omitted.

One thing I like about hand designing schema is it makes you sit down and make very clear choices about what your data is, how it interrelates, and how you’ll use it. You understand your own goals more clearly.

So many people I encounter seem to think it’s the code that’s important when building the back end of an application. You see this when people discussing database schemas start comparing, say, rails to hibernate. But ORMs emphasise code instead of data, which in my experience is a big mistake. In my experience, getting the data structures right is 99% of the battle. If you get that right, the code that follows is sim…

Yup - and you can’t code your way to real scale either. At real scale the game is all about data structures. Code just gets them from A to B.

Or as they say at Google, the job of SWE is “moving protos”.

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

#57

Post by emodendroket »

martinald wrote: post #5

Is it just me that thinks in general schema design and development is stuck in the stone ages? I mainly know dotnet stuff, which does have migrations in EF (I note the point about gitlab not using this kind of thing because of database compatibility). It can point out common data loss while doing them. However, it still is always quite scary doing migrations, especially bigger ones refactoring something. Throw into t…

I think that stuff works about as well as it possibly could. If you think that's painful think about something like DynamoDB where if you didn't really think through the access patterns up front you're in for a world of pain.

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

#58

Post by jimbokun »

doctor_eval wrote: post #32
sjwhevvvvvsj wrote: post #8

Earlier quoted context omitted.

One thing I like about hand designing schema is it makes you sit down and make very clear choices about what your data is, how it interrelates, and how you’ll use it. You understand your own goals more clearly.

So many people I encounter seem to think it’s the code that’s important when building the back end of an application. You see this when people discussing database schemas start comparing, say, rails to hibernate. But ORMs emphasise code instead of data, which in my experience is a big mistake. In my experience, getting the data structures right is 99% of the battle. If you get that right, the code that follows is sim…

It’s part of the dirty little secret of why document databases and other NoSQL systems became popular.

Required even less up front thinking about how to model your data. Throw some blobs of JSON into Mongo or whatever, and worry about the rest later.

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

#59

Post by duskwuff »

Rapzid wrote: post #27
tengbretson wrote: post #24

Earlier quoted context omitted.

In JavaScript land, postgres bigints deserialize as strings. Is your application resilient to this? Are your downstream customers ready to handle that sort of schema change? Running the db migration is the easy part.

Depends on the lib. Max safe int size is like 9 quadrillion. You can safely deserialize serial bigints to this without ever worrying about hitting that limit in many domains.

> Max safe int size is like 9 quadrillion.

2^53, to be precise. If your application involves assigning a unique identifier to every ant on the planet Earth (approx. 10^15 ≈ 2^50), you might need to think about this. Otherwise, I wouldn't worry about it.

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

#60

Post by winrid »

mvdtnz wrote: post #35
winrid wrote: post #16

Earlier quoted context omitted.

Not a silver bullet for every project but the Django ORM largely solves this with its migrations. You define your table classes and it just generates the migrations. Throw in a type checker and you're in pretty good shape. Rust also has sqlx which will type check your code against the DB.

An ORM is NEVER the solution, ever ever ever. Repeat after me: ORMs are not the solution to this problem. They work in your little toy apps with 4 customers but they are nothing but pain on real enterprise grade software.

I don't think insults are really necessary here.

Also ORMs can be very useful, just don't do dumb stuff, like with any technology.

I use them when appropriate.

Post reply on HN