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

11–20 of 170 posts

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

#11

Post by stephen123 »

josephg wrote: post #6

> As I discussed in an earlier post[3] when you use Postgres native UUID v4 type instead of bigserial table size grows by 25% and insert rate drops to 25% of bigserial. This is a big difference. Does anyone know why UUIDv4 is so much worse than bigserial? UUIDs are just 128 bit numbers. Are they super expensive to generate or something? Whats going on here?

I think its because of btrees. Btrees and the pages work better if only the last page is getting lots of writes. Iuids cause lots of un ordered writes leading to page bloat.

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

#12

Post by aeyes »

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

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 replica, then switch over. This way the downtime can be reduced to minutes.

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

#13

Post by Atotalnoob »

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…

EF core doesn’t to drop/create for columns in db providers that support renaming columns. It only does it for ones that don’t like MySQL or SQLite

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

#15

Post by winrid »

aeyes wrote: post #12
rapfaria wrote: post #4

Earlier quoted context omitted.

> 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

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…

This largely depends on the disk. I wouldn't expect that to take 30mins on a modern NVME drive, but of course it depends on table size.

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

#16

Post by winrid »

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…

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.

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

#17

Post by zX41ZdbW »

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

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

#18

Post by nly »

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

That query took a long time

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

#20

Post by nly »

sjwhevvvvvsj wrote: post #8
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…

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.

Exactly that. Sitting down and thinking about your data structures and APIs before you start writing code seems to be a fading skill.
Post reply on HN