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

21–30 of 170 posts

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

#21

Post by dxdm »

winrid wrote: post #16
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.

I'm assuming this is why you say it's not a silver bullet, but to make it more explicit: the Django ORM will happily generate migrations that will lock crucial tables for long amounts of time and bring down your production application in the process.

You still need to know what SQL the migration will run (take a look at `manage.py sqlmigrate`) and most importantly how your database will apply it.

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

#22

Post by EvanAnderson »

nly wrote: post #20
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.

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

It absolutely shows in the final product, too.

I wish more companies evaluated the suitability of software based on reviewing the back-end data storage schema. A lot of sins can be hidden in the application layer but many become glaringly obvious when you look at how the data is represented and stored.

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

#23

Post by basil-rash »

dxdm wrote: post #21
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.

I'm assuming this is why you say it's not a silver bullet, but to make it more explicit: the Django ORM will happily generate migrations that will lock crucial tables for long amounts of time and bring down your production application in the process. You still need to know what SQL the migration will run (take a look at `manage.py sqlmigrate`) and most importantly how your database will apply it.

Dealing with a bunch of automigrate headaches in the Prisma ORM convinced me to just drop the layer entirely and write plain old SQL everywhere. It’s forced me to learn a bunch of new stuff, but the app runs faster now that I can optimize every query and migrations are much simpler with a single idempotent SQL setup script I can run to provision whatever deployment of the DB I need. I’m sure some problem spaces might benefit from all the additional complexity and abstraction, but the average app certainly can make do without for a long time.

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

#24

Post by tengbretson »

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

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.

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

#25

Post by aeyes »

winrid wrote: post #15
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…

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.

Disk wasn't the limit in my case, index creation is single threaded.

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

#27

Post by Rapzid »

tengbretson wrote: post #24
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

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.

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

#28

Post by zetalyrae »

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 approach which is using bigserial and encrypting the keys: https://github.com/abevoelker/gfc64

But this means 1) you can't rotate the secret and 2) if it's ever leaked everyone can now Fermi-estimate your table sizes.

Having separate public and internal IDs seems both tedious and sacrifices performance (if the public-facing ID is a UUIDv4).

I think UUIDv7 is the solution that checks the most boxes.

[0]: https://uuid7.com/

[1]: https://en.wikipedia.org/wiki/Snowflake_ID

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

#29

Post by newaccount7g »

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…

If I’ve learned anything in my 7 years of software development it’s that this kind of expertise is just “blah blah blah” that will get you fired. Just make the system work. This amount of trying to anticipate problems will just screw you up. I seriously can’t imagine a situation where knowing this would actually improve the performance noticeably.

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

#30

Post by semiquaver »

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…

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.
Post reply on HN