61–70 of 96 posts
You can jump through hoops or just use an RDBMS that supports transactional DDL.
That does not solve the problem. Transactional DDL still needs a full table lock for most operations, which on large tables can take minutes to hours. Then it's not really an online schema migration anymore.
Earlier quoted context omitted.
In certain scenarios if you need to modify the schema for a table in MySQL it will lead to the entire table being locked, and for large tables this could lead to a noticeable outage for users if you need to run queries on that table. One case I had where we faced this problem was changing the primary key for a table from 32 bit to 64 bit ints since we were running out of space. We used Percona's online schema change…
Modern advice: always use 64 bit integer ids. If it's a small table, it won't matter. If it's a big table, you'll need them anyway.
Consider a table that you know will only have a few rows, but is referenced in one or more FK columns. If I use a 64bit integer when 32bit (or smaller!) is enough, I’m now using twice as much space for the FK column. If there are millions of rows, plus indexes, that can add up pretty fast.
We evaluated gh-ost, but the killer for us is that it doesn't support any kind of foreign keys. I understand that at GitHub's scale, foreign keys might be more of a hassle than what they are worth, but for a smallish company that values data integrity over scale and uptime, this is not an acceptable choice.
Earlier quoted context omitted.
I'm interested to know what companies are doing "at scale" to not need to use foreign keys. Do they just write user ids or whatnot into other tables?
I'm guessing they are validating the constraints in their applications instead of their databases. While this puts more requirements on your setup and developers, it offloads a lot of stress from your database.
At NoRedInk, We've been using gh-ost for a few years now, and it's been a pleasure. - The ability to control a running migration is crucial. We have pretty predictable load, and we generally run long-running migrations during off-peak hours. If a migration runs longer than we were expecting and might run into peak hours, we can pause the migration and have the migration not impact users. - hooks make it trivial to in…
I'm probably really ignorant asking this, but how do you "pause" schema migrations period. And even if you did, how do you ensure a consistent experience for your users if your db is broken? Some sort of application logic to deal with inconsistencies? That seems really expensive (from a development work perspective).
Earlier quoted context omitted.
I'm currently working on a product that uses JSONb columns extensively. To be honest, I don't like it. I'm not sure if it's bad design, or if it's just bad to mix relational databases with JSON, but I'm constantly battling to do things that I would find trivial in SQL. I guess it really depends on your requirements though. I've found that JSONb is great for storing historical data and results, write-once sort of stuf…
If I want to use it as a write only table where I would like to get virtual indexes for values inside the JSONb column. Would you recommend using Postgres for this usecase?
http://bitnine.net/blog-postgresql/postgresql-internals-json...
The GIN index is an inverted index, if you're expecting to query against several keys; alternatively if you have a large keyspace and no need to query outside a small number of properties, you could create individual hash or btree indexes for each one.
Postgres is good for this usecase, but as always, YMMV, consider alternatives/optimizations if your scale or write-volume dictate otherwise (e.g. sharding, Citusdb etc.)
At NoRedInk, We've been using gh-ost for a few years now, and it's been a pleasure. - The ability to control a running migration is crucial. We have pretty predictable load, and we generally run long-running migrations during off-peak hours. If a migration runs longer than we were expecting and might run into peak hours, we can pause the migration and have the migration not impact users. - hooks make it trivial to in…
I'm probably really ignorant asking this, but how do you "pause" schema migrations period. And even if you did, how do you ensure a consistent experience for your users if your db is broken? Some sort of application logic to deal with inconsistencies? That seems really expensive (from a development work perspective).
The first point depends on the mechanism used to keep up with changes to the original table. You can’t fully pause migrations on pt-online-schema-change for example, as it leverages triggers for that part.
From my phone so sorry if too brief, gh-ost’s docs are great and would tell the whole story.
We evaluated gh-ost, but the killer for us is that it doesn't support any kind of foreign keys. I understand that at GitHub's scale, foreign keys might be more of a hassle than what they are worth, but for a smallish company that values data integrity over scale and uptime, this is not an acceptable choice.
How could foreign keys ever be a "hassle"?
They're also a performance impact on large tables since inserts/deletes must make multiple trips to the tables/indexes. That's a growing operational hassle as tables grow larger.
Earlier quoted context omitted.
I'm interested to know what companies are doing "at scale" to not need to use foreign keys. Do they just write user ids or whatnot into other tables?
I'm guessing they are validating the constraints in their applications instead of their databases. While this puts more requirements on your setup and developers, it offloads a lot of stress from your database.
Also, pardon my further ignorance, but if you're not going to use foreign key constraints, what is the point of using a relational db? Why not just a fast key-value store for each index?