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

111–120 of 170 posts

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

#111

Post by scient »

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…

Rails migrations really fail to be viable at scale. And its not really because of the migrations in Rails, its because changes in PostgreSQL get very very expensive. Things have gotten better in PG 15, but its still not quite there yet.

I've ran into an integer PK being almost exhausted in values twice now. In one of these cases I used pg-osc to rewrite a 8TB table on disk without downtime over a period of a week to fix the issue. In the other case it was about 6TB within 2 days. Its doable, but the amount of planning and time it takes is non-trivial.

I so far have no found any other viable solutions either. I keep hearing about logical replication but I haven't seen a great real-world solution at scale so far.

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

#112

Post by scient »

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.

Large tables take hours, if not days. I attempted a test case on AWS using souped up io2 disks (the fastest most expensive disks they have) and a beast of a DB server (r5.12xl I think) and it became abundantly clear that at certain scale you won't be doing any kind of in-place table updates like that on the system. Especially if your allowed downtime is one hour maintenance window per week...

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

#113

Post by traceroute66 »

alex_smart wrote: post #105
traceroute66 wrote: post #102

Slight nit-pick, but I would pick up the author on the text vs varchar section. The author effectively wastes many words trying to prove a non-existent performance difference and then concludes "there is not much performance difference between the two types". This horse bolted a long time ago. Its not "not much", its "none". The Postgres Wiki[1] explicitly tells you to use text unless you have a very good reason not…

>The author effectively wastes many words trying to prove a non-existent performance difference and then concludes "there is not much performance difference between the two types". They then also show that there is in fact a significant performance difference when you need to migrate your schema to accodomate a change in length of strings being stored. Altering a table to a change a column from varchar(300) to varcha…

> They then also show that there is in fact a significant performance difference when you need to migrate your schema to accodomate a change in length of strings being stored.

Which is a pointless demonstration if you RTFM and design your schema correctly, using text, just like the manual and the wiki tells you to.

> the text type with CHECK constraint allows you to evolve the schema easily compared to character varying or varchar(n) when you have length checks.

Which is exactly what the manual tells you ....

"For many purposes, character varying acts as though it were a domain over text"

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

#114

Post by aeyes »

scient wrote: post #112
winrid wrote: post #15

Earlier quoted context omitted.

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.

Large tables take hours, if not days. I attempted a test case on AWS using souped up io2 disks (the fastest most expensive disks they have) and a beast of a DB server (r5.12xl I think) and it became abundantly clear that at certain scale you won't be doing any kind of in-place table updates like that on the system. Especially if your allowed downtime is one hour maintenance window per week...

I did it on a r6.24xlarge RDS instance and the CPU wasn't doing much during the operation. IO peaked at 40k IOPS on EBS with provisioned IOPS, I'm not sure if a local disk would be any faster but I already know that rewriting the table and creating the indexes are all single threaded so there isn't much you could gain.

Once I got the logical replication setup to work I changed 20 tables on the replica and made the switch with 15 minutes of downtime. That saved me a lot of long nights.

You can get an idea of how long this could take by running pg_repack, it's basically doing the same thing: Copying the data and recreating all the indexes.

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

#115

Post by basil-rash »

fabian2k wrote: post #96
kfajdsl wrote: post #88

Earlier quoted context omitted.

I also switched to plain SQL migrations and queries because I find it much simpler. I hear this a lot, that ORMs are easier/quicker to use, but I've found that writing plain SQL has almost no friction for me. I mean, learning at least basic SQL is part of the first year in most CS degrees.

There are some things that are really annoying in raw SQL and that are much easier with ORMs. Dynamic queries are one of those, if you don't want to concatenate SQL strings yourself you need at least a query builder at that point. The other part is updating/creating entities with many relations, that gets very tedious quickly and is somethings ORMs can handle for you. It depends a lot on the specific application, for…

I’m not suggesting anyone implement the raw binary protocol themselves! The postgres connection library you’re using should be able to handle dynamic queries perfectly fine.

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

#116

Post by SkyMarshal »

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…

> This is especially important when you use sequential auto-incrementing identifiers with type integer or bigint since they are guessable.

I thought we had long since moved past that to GUIDs or UUIDs for primary keys. Then if you still need some kind of sequential numbering that has meaning in relation to the other fields, make a separate column for that.

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

#117

Post by modderation »

istvanu wrote: post #95
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…

I'm convinced that GitHub's decision to move away from Rails was partly influenced by a significant flaw in ActiveRecord: its lack of support for composite primary keys. The need for something as basic as PRIMARY KEY(repo_id, issue_id) becomes unnecessarily complex within ActiveRecord, forcing developers to use workarounds that involve a unique key alongside a singular primary key column to meet ActiveRecord's requir…

Perhaps too late, but Rails 7.1[1] introduced composite primary key support, and there's been a third-party gem[2] offering the functionality for earlier versions of ActiveRecord.

[1] https://guides.rubyonrails.org/7_1_release_notes.html#compos...

[2] https://github.com/composite-primary-keys/composite_primary_...

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

#118

Post by solidsnack9000 »

Merad wrote: post #52
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 y…

...and this has not just a size impact but also a substantial performance impact.

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

#119

Post by solidsnack9000 »

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…

> There's an orthogonal approach which is using bigserial and encrypting the keys...

Another variant of this approach: https://pgxn.org/dist/permuteseq/

It is also feasible to encrypt the value on display (when placing it in URLs, emails, &c):

https://wiki.postgresql.org/wiki/Pseudo_encrypt

This maintains many of the benefits of sequential indexes and does allow you to change the key. However, if the key is changed, it would break any bookmarks, invalidate anything sent in older emails -- it would have the same effect as renaming everything.

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

#120

Post by perrygeo »

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?

The 25% increase in size is true but it's 8 bytes, a small and predictable linear increase per row. Compared to the rest of the data in the row, it's not much to worry about.

The bigger issue is insert rate. Your insert rate is limited by the amount of available RAM in the case of UUIDs. That's not the case for auto-incrementing integers! Integers are correlated with time while UUID4s are random - so they have fundamentally different performance characteristics at scale.

The author cites 25% but I'd caution every reader to take this with a giant grain of salt. At the beginning, for small tables As your table grows, specifically as the size of the btree index starts reaching the limits of available memory, postgres can no longer handle the UUID btree entirely in memory and has to resort to swapping pages to disk. An auto-integer type won't have this problem since rows close in time will use the same index page thus doesn't need to hit disk at all under the same load.

Once you reach this scale, The difference in speed is orders of magnitude. It's NOT a steady 25% performance penalty, it's a 25x performance cliff. And the only solution (aside from a schema migration) is to buy more RAM.

Post reply on HN