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

41–50 of 170 posts

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

#41

Post by CubsFan1060 »

semiquaver wrote: post #30
aeyes wrote: post #12

Earlier quoted context omitted.

The migration has to rewrite the whole table, bigint needs 8 bytes so you have to make room for that. I have done several such primary key migrations on tables with 500M+ records, they took anywhere from 30 to 120 minutes depending on the amount of columns and indexes. If you have foreign keys it can be even longer. Edit: But there is another option which is logical replication. Change the type on your logical replic…

In practice the only option that I’ve seen work for very large teams and very large relational databases is online schema change tools like https://github.com/shayonj/pg-osc and https://github.com/github/gh-ost (the latter developed for GitHub’s monolith). It’s just too difficult to model what migrations will cause problems under load. Using a binlog/shadowtable approach for all migrations mostly obviates the problem…

You can also migrate it using logical replication.

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

#42

Post by gfody »

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…

think of the primary keys in a database like typedef void* ie it's your fundamental pointer and the size of it will impact every aspect of performance throughout - memory/disk footprint and corresponding throughput bottlenecks, cpu time comparing keys which is what every operation reduces to in the deepest inner-most loops of joins and lookups etc.

when x86-64 cpus were new the performance impact from switching to 64-bit pointers was so bad we had to create x32/ilp32 and the reason .NET still has "prefer 32-bit" as a default even today.

using 128-bit uuids as PKs in a database is an awful mistake

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

#43

Post by postalrat »

klysm wrote: post #40
gfody wrote: post #36

> 1 quintillion is equal to 1000000000 billions it is pretty wild that we generally choose between int32 and int64. we really ought to have a 5 byte integer type which would support cardinalities of ~1T

Yeah it doesn't make sense to pick something that's not a power of 2 unless you are packing it.

I guess that depends on how the index works.

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

#44

Post by Horffupolde »

canadiantim wrote: post #37
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…

Would it ever make sense to have a uuidv7 as primary key but then anther slug field for a public-id, e.g. one that is shorter and better in a url or even allowing user to customize it?

Yes sure but now you have to handle two ids and guaranteeing uniqueness across machines or clusters becomes hard.

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

#45

Post by gfody »

klysm wrote: post #40
gfody wrote: post #36

> 1 quintillion is equal to 1000000000 billions it is pretty wild that we generally choose between int32 and int64. we really ought to have a 5 byte integer type which would support cardinalities of ~1T

Yeah it doesn't make sense to pick something that's not a power of 2 unless you are packing it.

we usually work with some page size anyways, 64 5-byte ints fit nicely into 5 512-bit registers, and ~1T is a pretty flexible cardinality limit after ~2B

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

#46

Post by zx8080 »

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

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.

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

#47

Post by anoopelias »

heyoni wrote: post #38
bluerooibos wrote: post #31

Has anyone written about or noticed the performance differences between Gitlab and GitHub? They're both Rails-based applications but I find page load times on Gitlab in general to be horrific compared to GitHub.

I mean GitHub in general has been pretty reliable minus the two outages they had last year and is usually pretty performant or I wouldn’t use their keyboard shortcuts. There are some complaints here from a former dev about gitlab that might provide insight into its culture and lack of regard for performance: https://news.ycombinator.com/item?id=39303323 Ps: I do not use gitlab enough to notice performance issues but…

More comments on this submission: https://news.ycombinator.com/item?id=39333220

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

#48

Post by zx8080 »

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.

Also,

> `repo_name` LowCardinality(String),

This is not a low cardinality:

7133122498 = 7.1B

Don't use low cardinality for such columns!

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

#49

Post by timacles »

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…

Theres no right abstraction for it because everyones data is different. From my experience what most developers dont realize is that data is more complex than code. Code is merely the stuff that sits on top of the data, shuffling it around... but designing and handling the data in an efficient way is the real engineering problem.

Any abstraction you could come up with wouldnt fit 90% of the other cases

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

#50

Post by vrosas »

Horffupolde wrote: post #44
canadiantim wrote: post #37

Earlier quoted context omitted.

Would it ever make sense to have a uuidv7 as primary key but then anther slug field for a public-id, e.g. one that is shorter and better in a url or even allowing user to customize it?

Yes sure but now you have to handle two ids and guaranteeing uniqueness across machines or clusters becomes hard.

That and a uuid is going to be unique across all tables and objects, whereas a slug will only be unique within a certain subset e.g. users within an organization. I’ve seen a production issue IRL where someone (definitely not me) wrote a query fetching objects by slug and forgot to include the ‘AND parent_slug = xxx’
Post reply on HN