17 ms·

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

488 points by daigoba66 3y ago

kelnos 3y ago
> 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 makes a mistake. Beyond that, there are probably a lot of small, toy projects that have no issues at all, or at most a few. Quickly-abandoned projects will suffer the same fate. I suspect that even though there are certainly some projects with hundreds and thousands of issues, the average across all 128M of those repos is likely pretty small, probably keeping things well under the 2B limit. 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, github.com included.
- justinclift 3y ago
  > github.com included Typo?
- rapfaria 3y ago
  > 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
  - aeyes 3y ago
    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.
    - winrid 3y ago
      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.
      - aeyes 3y ago
        Disk wasn't the limit in my case, index creation is single threaded.
        winrid 3y ago
        Which is still very IO bound... Wonder what kinda IOPS you were observing? Also they make pretty fast CPUs these days :)
      - scient 3y ago
        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...
        aeyes 3y ago
        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.
        sgarland 3y ago
  - tengbretson 3y ago
    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.
    - Rapzid 3y ago
      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.
      - duskwuff 3y ago
        > 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.
  - jameshart 3y ago
    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.
- zX41ZdbW 3y ago
  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#U0VMRUNUIHVuaXEocmVwb19uYW1lKSBBUyByZXBvcywgdW5pcShyZXBvX25hbWUsIG51bWJlcikgQVMgaXNzdWVzX2FuZF9wdWxsX3JlcXVlc3RzLCBpc3N1ZXNfYW5kX3B1bGxfcmVxdWVzdHMgLyByZXBvcyBGUk9NIGdpdGh1Yl9ldmVudHM= https://play.clickhouse.com/play?user=play#U0VMRUNUIHVuaXEoc...
  - nly 3y ago
    That query took a long time
  - zx8080 3y ago
    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.
    - zx8080 3y ago
      Also, > `repo_name` LowCardinality(String), This is not a low cardinality: 7133122498 = 7.1B Don't use low cardinality for such columns!
      - zX41ZdbW 3y ago
        The LowCardinality data type does not require the whole set of values to have a low cardinality. It benefits when the values have locally low cardinality. For example, if the number of unique values in `repo_name` is a hundred million, but for every million consecutive values, there are only ten thousand unique, it will give a great speed-up.
        zx8080 3y ago
        > LowCardinality data type does not require the whole set of values to have a low cardinality. Don't mislead others. It's not true unless low_cardinality_max_dictionary_size is set to some other value than the default one: 8192. It does not work well for hundred million values.
- mvdtnz 3y ago
  Do we know for sure if gitlab cloud uses a multi-tenanted database, or a db per user/customer/org? In my experience products that offer both a self hosted and cloud product tend to prefer a database per customer, as this greatly simplifies the shared parts of the codebase, which can use the same queries regardless of the hosting type. If they use a db per customer then no one will ever approach those usage limits and if they do they would be better suited to a self hosted solution.
  - Maxion 3y ago
    I've toyed with various SaaS designs and multi tenanted databses always come to th forefront of my mind. It seems to simplify the architecture a lot.
  - tcnj 3y ago
    Unless something has substantially changed since I last checked, gitlab.com is essentially self-hosted gitlab ultimate with a few feature flags to enable some marginally different behaviour. That is, it uses one multitennant DB for the whole platform.
    - karolist 3y ago
      Not according to [1] where the author said > This effectively results in two code paths in many parts of your platform: one for the SaaS version, and one for the self-hosted version. Even if the code is physically the same (i.e. you provide some sort of easy to use wrapper for self-hosted installations), you still need to think about the differences. 1. https://yorickpeterse.com/articles/what-it-was-like-working-for-gitlab/ https://yorickpeterse.com/articles/what-it-was-like-working-...
- golergka 3y ago
  Being two orders of magnitude away from running out of ids is too close for comfort anyway.
- iurisilvio 3y ago
  Migrating primary keys from int to bigint is feasible. Requires some preparation and custom code, but zero downtime. I'm managing a big migration following mostly this recipe, with a few tweaks: http://zemanta.github.io/2021/08/25/column-migration-from-int-to-bigint-in-postgresql/ http://zemanta.github.io/2021/08/25/column-migration-from-in... FKs, indexes and constraints in general make the process more difficult, but possible. The data migration took some hours in my case, but no need to be fast. AFAIK GitLab has tooling to run tasks after upgrade to make it work anywhere in a version upgrade.
- istvanu 3y ago
  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 requirements—a less than ideal solution. Moreover, the use of UUIDs as primary keys, while seemingly a workaround, introduces its own set of problems. Despite adopting UUIDs, the necessity for a unique constraint on the (repo_id, issue_id) pair persists to ensure data integrity, but this significantly increases the database size, leading to substantial overhead. This is a major trade-off with potential repercussions on your application's performance and scalability. This brings us to a broader architectural concern with Ruby on Rails. Despite its appeal for rapid development cycles, Rails' application-level enforcement of the Model-View-Controller (MVC) pattern, where there is a singular model layer, a singular controller layer, and a singular view layer, is fundamentally flawed. This monolithic approach to MVC will inevitably lead to scalability and maintainability issues as the application grows. The MVC pattern would be more effectively applied within modular or component-based architectures, allowing for better separation of concerns and flexibility. The inherent limitations of Rails, especially in terms of its rigid MVC architecture and database management constraints, are significant barriers for any project beyond the simplest MVPs, and these are critical factors to consider before choosing Rails for more complex applications.
  - rglynn 3y ago
    Whilst I would agree that a monolith can run into scalability issues, I am not sure your characterisation of Rails as such is proportionate. To say that Rails' architecture is a "sigificant barrier for any project beyond the simplest MVPs" is rather hyperbolic, and the list of companies running monolithic Rails apps is a testament to that. On this very topic, I would recommend reading GitLab's own post from 2022 on why they are sticking with a Rails monolith[1]. [1] - https://about.gitlab.com/blog/2022/07/06/why-were-sticking-with-ruby-on-rails/ https://about.gitlab.com/blog/2022/07/06/why-were-sticking-w...
  - zachahn 3y ago
    I can't really comment on GitHub, but Rails supports composite primary keys as of Rails 7.1, the latest released version [1]. About modularity, there are projects like Mongoid which can completely replace ActiveRecord. And there are plugins for the view layer, like "jbuilder" and "haml", and we can bypass the view layer completely by generating/sending data inside controller actions. But fair, I don't know if we can completely replace the view and controller layers. I know I'm missing your larger point about architecture! I don't have so much to say, but I agree I've definitely worked on some hard-to-maintain systems. I wonder if that's an inevitability of Rails or an inevitability of software systems—though I'm sure there are exceptional codebases out there somewhere! [1] https://guides.rubyonrails.org/7_1_release_notes.html#composite-primary-keys https://guides.rubyonrails.org/7_1_release_notes.html#compos...
martinald 3y ago
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 this jsonb columns and I feel it is really easy to screw things up and suffer bad data loss. For example, renaming a column (at least in EF) will result in a column drop and column create on the autogenerated migrations. Why can't I give the compiler/migration tool more context on this easily? Also the point about external IDs and internal IDs - why can't the database/ORM do this more automatically? I feel there really hasn't been much progress on this since migration tooling came around 10+ years ago. I know ORMs are leaky abstractions, but I feel everyone reinvents this stuff themselves and every project does these common things a different way. Are there any tools people use for this?
- sjwhevvvvvsj 3y ago
  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.
  - nly 3y ago
    Exactly that. Sitting down and thinking about your data structures and APIs before you start writing code seems to be a fading skill.
    - EvanAnderson 3y ago
      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.
  - doctor_eval 3y ago
josephg 3y ago
> 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?
- AprilArcus 3y ago
  UUIDv4s are fully random, and btree indices expect "right-leaning" values with a sensible ordering. This makes indexing operations on UUIDv4 columns slow, and was the motivation for the development of UUIDv6 and UUIDv7.
  - couchand 3y ago
    I'm curious to learn more about this heuristic and how the database leverages it for indexing. What does right-leaning mean formally and what does analysis of the data structure look like in that context? Do variants like B+ or B* have the same charactersistics?
- stephen123 3y ago
  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.
- barrkel 3y ago
  Random distribution in the sort order mean the cache locality of a btree is poor - instead of inserts going to the last page, they go all over the place. Locality of batch inserts is also then bad at retrieval time, where related records are looked up randomly later. So you pay taxes at both insert time and later during selection.
- perrygeo 3y ago
  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 < a few million rows, the insert penalty is almost negligible. If you did benchmarks here, you might conclude there's no practical difference. 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.
Mphetbug 3y ago
[flagged]
hansonpeter 3y ago
[dead]
vinnymac 3y ago
I always wondered what the purpose of that extra “I” was in the CI variables `CI_PIPELINE_IID` and `CI_MERGE_REQUEST_IID` were for. Always assumed it was a database related choice, but this article confirms it.
LuciBb 3y ago
[dead]
zetalyrae 3y ago
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 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/ https://uuid7.com/ [1]: https://en.wikipedia.org/wiki/Snowflake_ID https://en.wikipedia.org/wiki/Snowflake_ID
- s4i 3y ago
  The v7 isn’t a silver bullet. In many cases you don’t want to leak the creation time of a resource. E.g. you want to upload a video a month before making it public to your audience without them knowing.
- newaccount7g 3y ago
  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.
- canadiantim 3y ago
  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?
  - Horffupolde 3y ago
    Yes sure but now you have to handle two ids and guaranteeing uniqueness across machines or clusters becomes hard.
bluerooibos 3y ago
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.
- heyoni 3y ago
  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 https://news.ycombinator.com/item?id=39303323 Ps: I do not use gitlab enough to notice performance issues but thought you might appreciate the article
  - anoopelias 3y ago
    More comments on this submission: https://news.ycombinator.com/item?id=39333220 https://news.ycombinator.com/item?id=39333220
  - imiric 3y ago
    > I mean GitHub in general has been pretty reliable minus the two outages they had last year Huh? GitHub has had major outages practically every other week for a few years now. There are pages of HN threads[1]. There's a reason why githubstatus.com doesn't show historical metrics and uptime percentages: it would make them look incompetent. Many outages aren't even officially reported there. I do agree that when it's up, performance is typically better than Gitlab's. But describing GH as reliable is delusional. [1]: https://hn.algolia.com/?dateRange=all&page=0&prefix=false&query="Github%20is%20down"&sort=byDate&type=story https://hn.algolia.com/?dateRange=all&page=0&prefix=false&qu...
    - heyoni 3y ago
      Delusional? Anecdotal maybe…I was describing my experience so thanks for elaborating. I only use it as a code repository. Was it specific services within GitHub that failed a lot?
gfody 3y ago
> 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
- klysm 3y ago
  Yeah it doesn't make sense to pick something that's not a power of 2 unless you are packing it.
  - postalrat 3y ago
    I guess that depends on how the index works.
  - gfody 3y ago
    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
Linda231 3y ago
[dead]
sidcool 3y ago
Great read! And even better comments here.
exabrial 3y ago
Foreign keys are expensive is an oft repeated rarely benched claim. There are tons of ways to do it incorrectly. But in your stack you are always enforcing integrity _somewhere_ anyway. Leveraging the database instead of reimplementing it requires knowledge and experimentation, and it more often than not it will save your bacon.
rob137 3y ago
I found this post very useful. I'm wondering where I could find others like it?
- rglynn 3y ago
  I like this, although a bit narrower - https://blog.mastermind.dev/indexes-in-postgresql https://blog.mastermind.dev/indexes-in-postgresql
- aflukasz 3y ago
  I recommend Postgres FM podcast, e.g. available as video on Postgres TV yt channel. Good content on its own, and many resources of this kind are linked in the episode notes. I believe one of the authors even helped Gitlab specifically with Postgres performance issues not that long ago.
- cosmicradiance 3y ago
  Here's another - https://zerodha.tech/blog/working-with-postgresql/ https://zerodha.tech/blog/working-with-postgresql/
yellowapple 3y ago
> 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 it reeks of security theater. If an attacker is able to do anything useful with a guessed ID without being authenticated and authorized to do so, then something else has gone horribly, horribly, horribly wrong and that should be the focus of one's energy instead of adding needless complexity to the schema. The only case I know of where this might be valuable is from a business intelligence standpoint, i.e. you don't want competitors to know how many customers you have. My sympathy for such concerns is quite honestly pretty low, and I highly doubt GitLab cares much about that. In GitLab's case, I'm reasonably sure the decision to use id + iid is less driven by "we don't want people guessing internal IDs" and more driven by query performance needs.
- s4i 3y ago
  It’s mentioned in the article. It’s more to do with business intelligence than security. A simple auto-incrementing ID will reveal how many total records you have in a table and/or their growth rate. > If you expose the issues table primary key id then when you create an issue in your project it will not start with 1 and you can easily guess how many issues exist in the GitLab.
  - no-dr-onboard 3y ago
    Business intelligence isn’t really applicable on the database level with guids..way too many abstraction layers down.
- JimBlackwood 3y ago
  I follow this best practice, there’s a few reasons why I do this. It doesn’t have to do with using a guessed primary ID for some sort of privilege escalation, though. It has more to do with not leaking any company information. When I worked for an e-commerce company, one of our biggest competitors used an auto-incrementing integer as primary key on their “orders” table. Yeah… You can figure out how this was used. Not very smart by them, extremely useful for my employer. Neither of these will allow security holes or leak customer info/payment info, but you’d still rather not leak this.
deleted 3y ago
[deleted]
azlev 3y ago
It's reasonable to not have auto increment id's, but it's not clear to me if there is benefits to have 2 IDs, one internal and one external. This increases the number of columns / indexes, makes you always do a lookup first, and I can't see a security scenario where I would change the internal key without changing the external key. Am I missing something?
- Aeolun 3y ago
  You always have the information at hand anyway when doing anything per project. It’s also more user friendly to have every project’s issues start with 1 instead of starting with two trillion, seven hundred billion, three hundred and five million, sevenhundred and seventeen thousand three hundred twentyfive.
traceroute66 3y ago
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 to. And indeed the docs themselves[2] tell us that "For many purposes, character varying acts as though it were a domain over text" and further down in the docs in the green Tip box, "There is no performance difference among these three types". Therefore Gitlab's use of (mostly) text would indicate that they have RTFM and that they have designed their schema for their choice of database (Postgres) instead of attempting to implement some stupid "portable" schema. [1] https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_varchar.28n.29_by_default https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use... [2] https://www.postgresql.org/docs/current/datatype-character.html https://www.postgresql.org/docs/current/datatype-character.h...
- alex_smart 3y ago
  >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 varchar(200) needs to rewrite every single row, where as updating the constraint on a text column is essentially free, just a full table scan to ensure that the existing values satisfy your new constraints. FTA: >So, as you can see, 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.
  - traceroute66 3y ago
    > 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"
eezing 3y ago
We shouldn’t assume that this schema was designed all at once, but rather is the product of evolution. For example, maybe the external_id was added after the initial release in order to support the creation of unique ids in the application layer.
firemelt 3y ago
so anyone use schema.rb in production? even dhh once campfire use .sql instead schema.rb

488 points by daigoba66 3y ago

> 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 makes a mistake. Beyond that, there are probably a lot of small, toy projects that have no issues at all, or at most a few. Quickly-abandoned projects will suffer the same fate. I suspect that even though there are certainly some projects with hundreds and thousands of issues, the average across all 128M of those repos is likely pretty small, probably keeping things well under the 2B limit. 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, github.com included.

justinclift 3y ago
> github.com included Typo?
rapfaria 3y ago
> 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
- aeyes 3y ago
  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.
  - winrid 3y ago
    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.
    - aeyes 3y ago
      Disk wasn't the limit in my case, index creation is single threaded.
      - winrid 3y ago
        Which is still very IO bound... Wonder what kinda IOPS you were observing? Also they make pretty fast CPUs these days :)
    - scient 3y ago
      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...
      - aeyes 3y ago
        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.
        sgarland 3y ago
- tengbretson 3y ago
  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.
  - Rapzid 3y ago
    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.
    - duskwuff 3y ago
      > 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.
- jameshart 3y ago
  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.
zX41ZdbW 3y ago
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#U0VMRUNUIHVuaXEocmVwb19uYW1lKSBBUyByZXBvcywgdW5pcShyZXBvX25hbWUsIG51bWJlcikgQVMgaXNzdWVzX2FuZF9wdWxsX3JlcXVlc3RzLCBpc3N1ZXNfYW5kX3B1bGxfcmVxdWVzdHMgLyByZXBvcyBGUk9NIGdpdGh1Yl9ldmVudHM= https://play.clickhouse.com/play?user=play#U0VMRUNUIHVuaXEoc...
- nly 3y ago
  That query took a long time
- zx8080 3y ago
  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.
  - zx8080 3y ago
    Also, > `repo_name` LowCardinality(String), This is not a low cardinality: 7133122498 = 7.1B Don't use low cardinality for such columns!
    - zX41ZdbW 3y ago
      The LowCardinality data type does not require the whole set of values to have a low cardinality. It benefits when the values have locally low cardinality. For example, if the number of unique values in `repo_name` is a hundred million, but for every million consecutive values, there are only ten thousand unique, it will give a great speed-up.
      - zx8080 3y ago
        > LowCardinality data type does not require the whole set of values to have a low cardinality. Don't mislead others. It's not true unless low_cardinality_max_dictionary_size is set to some other value than the default one: 8192. It does not work well for hundred million values.
mvdtnz 3y ago
Do we know for sure if gitlab cloud uses a multi-tenanted database, or a db per user/customer/org? In my experience products that offer both a self hosted and cloud product tend to prefer a database per customer, as this greatly simplifies the shared parts of the codebase, which can use the same queries regardless of the hosting type. If they use a db per customer then no one will ever approach those usage limits and if they do they would be better suited to a self hosted solution.
- Maxion 3y ago
  I've toyed with various SaaS designs and multi tenanted databses always come to th forefront of my mind. It seems to simplify the architecture a lot.
- tcnj 3y ago
  Unless something has substantially changed since I last checked, gitlab.com is essentially self-hosted gitlab ultimate with a few feature flags to enable some marginally different behaviour. That is, it uses one multitennant DB for the whole platform.
  - karolist 3y ago
    Not according to [1] where the author said > This effectively results in two code paths in many parts of your platform: one for the SaaS version, and one for the self-hosted version. Even if the code is physically the same (i.e. you provide some sort of easy to use wrapper for self-hosted installations), you still need to think about the differences. 1. https://yorickpeterse.com/articles/what-it-was-like-working-for-gitlab/ https://yorickpeterse.com/articles/what-it-was-like-working-...
golergka 3y ago
Being two orders of magnitude away from running out of ids is too close for comfort anyway.
iurisilvio 3y ago
Migrating primary keys from int to bigint is feasible. Requires some preparation and custom code, but zero downtime. I'm managing a big migration following mostly this recipe, with a few tweaks: http://zemanta.github.io/2021/08/25/column-migration-from-int-to-bigint-in-postgresql/ http://zemanta.github.io/2021/08/25/column-migration-from-in... FKs, indexes and constraints in general make the process more difficult, but possible. The data migration took some hours in my case, but no need to be fast. AFAIK GitLab has tooling to run tasks after upgrade to make it work anywhere in a version upgrade.
istvanu 3y ago
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 requirements—a less than ideal solution. Moreover, the use of UUIDs as primary keys, while seemingly a workaround, introduces its own set of problems. Despite adopting UUIDs, the necessity for a unique constraint on the (repo_id, issue_id) pair persists to ensure data integrity, but this significantly increases the database size, leading to substantial overhead. This is a major trade-off with potential repercussions on your application's performance and scalability. This brings us to a broader architectural concern with Ruby on Rails. Despite its appeal for rapid development cycles, Rails' application-level enforcement of the Model-View-Controller (MVC) pattern, where there is a singular model layer, a singular controller layer, and a singular view layer, is fundamentally flawed. This monolithic approach to MVC will inevitably lead to scalability and maintainability issues as the application grows. The MVC pattern would be more effectively applied within modular or component-based architectures, allowing for better separation of concerns and flexibility. The inherent limitations of Rails, especially in terms of its rigid MVC architecture and database management constraints, are significant barriers for any project beyond the simplest MVPs, and these are critical factors to consider before choosing Rails for more complex applications.
- rglynn 3y ago
  Whilst I would agree that a monolith can run into scalability issues, I am not sure your characterisation of Rails as such is proportionate. To say that Rails' architecture is a "sigificant barrier for any project beyond the simplest MVPs" is rather hyperbolic, and the list of companies running monolithic Rails apps is a testament to that. On this very topic, I would recommend reading GitLab's own post from 2022 on why they are sticking with a Rails monolith[1]. [1] - https://about.gitlab.com/blog/2022/07/06/why-were-sticking-with-ruby-on-rails/ https://about.gitlab.com/blog/2022/07/06/why-were-sticking-w...
- zachahn 3y ago
  I can't really comment on GitHub, but Rails supports composite primary keys as of Rails 7.1, the latest released version [1]. About modularity, there are projects like Mongoid which can completely replace ActiveRecord. And there are plugins for the view layer, like "jbuilder" and "haml", and we can bypass the view layer completely by generating/sending data inside controller actions. But fair, I don't know if we can completely replace the view and controller layers. I know I'm missing your larger point about architecture! I don't have so much to say, but I agree I've definitely worked on some hard-to-maintain systems. I wonder if that's an inevitability of Rails or an inevitability of software systems—though I'm sure there are exceptional codebases out there somewhere! [1] https://guides.rubyonrails.org/7_1_release_notes.html#composite-primary-keys https://guides.rubyonrails.org/7_1_release_notes.html#compos...

martinald 3y ago

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 this jsonb columns and I feel it is really easy to screw things up and suffer bad data loss. For example, renaming a column (at least in EF) will result in a column drop and column create on the autogenerated migrations. Why can't I give the compiler/migration tool more context on this easily? Also the point about external IDs and internal IDs - why can't the database/ORM do this more automatically? I feel there really hasn't been much progress on this since migration tooling came around 10+ years ago. I know ORMs are leaky abstractions, but I feel everyone reinvents this stuff themselves and every project does these common things a different way. Are there any tools people use for this?

sjwhevvvvvsj 3y ago
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.
- nly 3y ago
  Exactly that. Sitting down and thinking about your data structures and APIs before you start writing code seems to be a fading skill.
  - EvanAnderson 3y ago
    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.
- doctor_eval 3y ago

josephg 3y ago

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

AprilArcus 3y ago
UUIDv4s are fully random, and btree indices expect "right-leaning" values with a sensible ordering. This makes indexing operations on UUIDv4 columns slow, and was the motivation for the development of UUIDv6 and UUIDv7.
- couchand 3y ago
  I'm curious to learn more about this heuristic and how the database leverages it for indexing. What does right-leaning mean formally and what does analysis of the data structure look like in that context? Do variants like B+ or B* have the same charactersistics?
stephen123 3y ago
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.
barrkel 3y ago
Random distribution in the sort order mean the cache locality of a btree is poor - instead of inserts going to the last page, they go all over the place. Locality of batch inserts is also then bad at retrieval time, where related records are looked up randomly later. So you pay taxes at both insert time and later during selection.
perrygeo 3y ago
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 < a few million rows, the insert penalty is almost negligible. If you did benchmarks here, you might conclude there's no practical difference. 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.

Mphetbug 3y ago

[flagged]

hansonpeter 3y ago

[dead]

vinnymac 3y ago

I always wondered what the purpose of that extra “I” was in the CI variables `CI_PIPELINE_IID` and `CI_MERGE_REQUEST_IID` were for. Always assumed it was a database related choice, but this article confirms it.

LuciBb 3y ago

[dead]

zetalyrae 3y ago

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 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/ https://uuid7.com/ [1]: https://en.wikipedia.org/wiki/Snowflake_ID https://en.wikipedia.org/wiki/Snowflake_ID

s4i 3y ago
The v7 isn’t a silver bullet. In many cases you don’t want to leak the creation time of a resource. E.g. you want to upload a video a month before making it public to your audience without them knowing.
newaccount7g 3y ago
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.
canadiantim 3y ago
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?
- Horffupolde 3y ago
  Yes sure but now you have to handle two ids and guaranteeing uniqueness across machines or clusters becomes hard.

bluerooibos 3y ago

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.

heyoni 3y ago
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 https://news.ycombinator.com/item?id=39303323 Ps: I do not use gitlab enough to notice performance issues but thought you might appreciate the article
- anoopelias 3y ago
  More comments on this submission: https://news.ycombinator.com/item?id=39333220 https://news.ycombinator.com/item?id=39333220
- imiric 3y ago
  > I mean GitHub in general has been pretty reliable minus the two outages they had last year Huh? GitHub has had major outages practically every other week for a few years now. There are pages of HN threads[1]. There's a reason why githubstatus.com doesn't show historical metrics and uptime percentages: it would make them look incompetent. Many outages aren't even officially reported there. I do agree that when it's up, performance is typically better than Gitlab's. But describing GH as reliable is delusional. [1]: https://hn.algolia.com/?dateRange=all&page=0&prefix=false&query="Github%20is%20down"&sort=byDate&type=story https://hn.algolia.com/?dateRange=all&page=0&prefix=false&qu...
  - heyoni 3y ago
    Delusional? Anecdotal maybe…I was describing my experience so thanks for elaborating. I only use it as a code repository. Was it specific services within GitHub that failed a lot?

gfody 3y ago

> 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

klysm 3y ago
Yeah it doesn't make sense to pick something that's not a power of 2 unless you are packing it.
- postalrat 3y ago
  I guess that depends on how the index works.
- gfody 3y ago
  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

Linda231 3y ago

[dead]

sidcool 3y ago

Great read! And even better comments here.

exabrial 3y ago

Foreign keys are expensive is an oft repeated rarely benched claim. There are tons of ways to do it incorrectly. But in your stack you are always enforcing integrity _somewhere_ anyway. Leveraging the database instead of reimplementing it requires knowledge and experimentation, and it more often than not it will save your bacon.

rob137 3y ago

I found this post very useful. I'm wondering where I could find others like it?

rglynn 3y ago
I like this, although a bit narrower - https://blog.mastermind.dev/indexes-in-postgresql https://blog.mastermind.dev/indexes-in-postgresql
aflukasz 3y ago
I recommend Postgres FM podcast, e.g. available as video on Postgres TV yt channel. Good content on its own, and many resources of this kind are linked in the episode notes. I believe one of the authors even helped Gitlab specifically with Postgres performance issues not that long ago.
cosmicradiance 3y ago
Here's another - https://zerodha.tech/blog/working-with-postgresql/ https://zerodha.tech/blog/working-with-postgresql/

yellowapple 3y ago

> 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 it reeks of security theater. If an attacker is able to do anything useful with a guessed ID without being authenticated and authorized to do so, then something else has gone horribly, horribly, horribly wrong and that should be the focus of one's energy instead of adding needless complexity to the schema. The only case I know of where this might be valuable is from a business intelligence standpoint, i.e. you don't want competitors to know how many customers you have. My sympathy for such concerns is quite honestly pretty low, and I highly doubt GitLab cares much about that. In GitLab's case, I'm reasonably sure the decision to use id + iid is less driven by "we don't want people guessing internal IDs" and more driven by query performance needs.

s4i 3y ago
It’s mentioned in the article. It’s more to do with business intelligence than security. A simple auto-incrementing ID will reveal how many total records you have in a table and/or their growth rate. > If you expose the issues table primary key id then when you create an issue in your project it will not start with 1 and you can easily guess how many issues exist in the GitLab.
- no-dr-onboard 3y ago
  Business intelligence isn’t really applicable on the database level with guids..way too many abstraction layers down.
JimBlackwood 3y ago
I follow this best practice, there’s a few reasons why I do this. It doesn’t have to do with using a guessed primary ID for some sort of privilege escalation, though. It has more to do with not leaking any company information. When I worked for an e-commerce company, one of our biggest competitors used an auto-incrementing integer as primary key on their “orders” table. Yeah… You can figure out how this was used. Not very smart by them, extremely useful for my employer. Neither of these will allow security holes or leak customer info/payment info, but you’d still rather not leak this.

deleted 3y ago

[deleted]

azlev 3y ago

It's reasonable to not have auto increment id's, but it's not clear to me if there is benefits to have 2 IDs, one internal and one external. This increases the number of columns / indexes, makes you always do a lookup first, and I can't see a security scenario where I would change the internal key without changing the external key. Am I missing something?

Aeolun 3y ago
You always have the information at hand anyway when doing anything per project. It’s also more user friendly to have every project’s issues start with 1 instead of starting with two trillion, seven hundred billion, three hundred and five million, sevenhundred and seventeen thousand three hundred twentyfive.

traceroute66 3y ago

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 to. And indeed the docs themselves[2] tell us that "For many purposes, character varying acts as though it were a domain over text" and further down in the docs in the green Tip box, "There is no performance difference among these three types". Therefore Gitlab's use of (mostly) text would indicate that they have RTFM and that they have designed their schema for their choice of database (Postgres) instead of attempting to implement some stupid "portable" schema. [1] https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_varchar.28n.29_by_default https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use... [2] https://www.postgresql.org/docs/current/datatype-character.html https://www.postgresql.org/docs/current/datatype-character.h...

alex_smart 3y ago
>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 varchar(200) needs to rewrite every single row, where as updating the constraint on a text column is essentially free, just a full table scan to ensure that the existing values satisfy your new constraints. FTA: >So, as you can see, 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.
- traceroute66 3y ago
  > 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"

eezing 3y ago

We shouldn’t assume that this schema was designed all at once, but rather is the product of evolution. For example, maybe the external_id was added after the initial release in order to support the creation of unique ids in the application layer.

firemelt 3y ago

so anyone use schema.rb in production? even dhh once campfire use .sql instead schema.rb