AlisdairO

Only on a per-connection basis

It supports transactional schema changes, but that's not what I'm talking about. Most schema changes require heavyweight locks on the tables they're altering. The locks might be short lived (for example, just a catalogue update to add a column), but they are nevertheless heavyweight and both block and are blocked by other work.

SELECT or DML operations take a lightweight lock on the table that doesn't block most other work, but it does block these schema changes. While the schema change is waiting to acquire the table lock, all new operations (like new SELECTs, for example) get blocked until the schema change completes.

So the following scenario can be pretty disastrous:

* Start a long-running SELECT operation on table

* Attempt to apply schema change to the table

* All new work on the table is blocked until the SELECT completes and the schema change can apply.

* Production outage

What the ChatGPT folks do is set a lock timeout when applying the schema change to make it 'give up' after a few seconds. This works to avoid truly excessive impact (in their case, they may have micro-outages of up to 5s while trying to apply schema), but has problems - firstly, they then need to retry, which may lead to more micro-outages, and secondly there's no guarantee on a system with mixed workload that they will be able to force the change through, and the schema change just ends up getting starved out.

A better alternative for most workloads is to build a system that detects what workload is blocking your schema change and kills it, allowing the schema change to go through quickly and unblock all the work behind it. You'd still use a lock timeout with this to be on the safe side, but it shouldn't be necessary in most cases.

Side note on transactional DDL - for Postgres systems with high throughput, most people just use autocommit. Table level locks that get taken to perform the schema change get held for the duration of the transaction, and you generally want to really minimize the amount of time you hold them for.

Regarding schema changes and timeouts - while having timeouts in place is good advice, you can go further. While running the schema rollout, run a script alongside it that kills any workload conflicting with the aggressive locks the schema change is trying to take. This will greatly reduce the pain caused by lock contention, and prevent you from needing to repeatedly rerun statements on high-throughput tables.

This would be a particularly nice-to-have feature for Postgres - the option to have heavyweight locks just proactively cancel any conflicting workload. For any case where you have a high-throughput table, the damage of the heavyweight lock sitting there waiting (and blocking all new traffic) is generally much larger than just cancelling some running transactions.

Depending on exactly what you need, you can often fake this with a functional index on mod(queue_value_id, 5000). You then query for mod(queue_value_id,5000) between m and n. You can then dynamically adjust the gap between m and n based on how many partitions you want

Me> different people will have different strengths of addiction

You> It's profoundly obvious you're missing the point, and conflating somehow having a low degree of addiction to something with not being addicted at all to it

Suggest applying some of that willpower towards paying attention to what you're reading.

> You also seem to imply that the degree to which you're addicted to something is the sole factor determining whether you will overcome your addiction or not

I don't imply anything of the sort. Willpower is one variable, level of addiction is another. What I do imply is that without deeper observation of a person's life, and the other areas in which they might demonstrate willpower, you can't make strong conclusions about their lacking willpower based simply on their weight.

Based on all I know about you (or you about me), we could each be people of tremendous willpower who overcame titanic odds to beat our food addiction, or we could simply be people who really quite like food who tried hard and overcame our mild predisposition.

> How have fat people gotten thinner without those meds up until now, then?

Mostly, they haven't. You and I are outliers.

The population-level data tells us that overweight people are mostly unable to control their weight in the face of modern food. That being the case, it doesn't seem unreasonable to look for alternative solutions to the failed option of just telling people to eat less.

edit: regarding strength of addiction - I mean, of course, isn't it profoundly obvious that different people will have different strengths of addiction? I can drink without the slightest inclination to excess, while others are broken alcoholics. My grandfather didn't have the slightest interest in food beyond the calories needed to survive, while I have to fight every day to eat well.

Respectfully, have you ever had anything in your life that you have struggled desperately with, and needed help? Anything at all that might give you a little empathy on the topic?

I was obese twenty years ago, and lost the weight via diet and exercise. Keeping that weight off is the single hardest thing I have ever done, and a battle I still have to consciously fight every single day. Doing so causes me a great deal of pain and frustration, and I know that I'm someone who is right on the edge of not being able to control my weight. Why should it be that difficult? So that I can pass some kind of purity test?

The fact is that the food we eat has evolved over time, and is too hard to resist overconsuming for a large fraction of our population. If we can create more addictive food, why not create antidotes? If we could easily treat alcohol addiction with a pill, would we tell alcoholics to just apply willpower instead? Why would we want people to suffer like that?

I used to (and I didn't find much relief from eye drops). For the headaches, turned out they were migraines, which I was getting from screwing up my face because my eyes were uncomfortable.

   * In that link, V11 is not the version of Postgres, it's the version of the test. Scroll down to DB Version.
   * Lots of versions are tested, but 9.2.1 is the only version I see on the same hardware that the top MariaDB versions are tested against. The others are on much weaker hardware.
   * Postgres 9.2.1 is 12 years old.

This site is not a good like-for-like comparison.

That link isn't particularly convincing. As far as I can see, the only Postgres test performed on the hardware that the top MariaDB entries had was on a positively ancient Postgres version (9.2.1).

Why shouldn't they want the easy way out? I was obese twenty years ago, and lost the weight via diet and exercise. Keeping that weight off is the single hardest thing I have ever done, and a battle I still have to consciously fight every single day. Why should it be that difficult? So that I can pass some kind of purity test?

The fact is that the food we eat has evolved over time, and is too hard to resist overconsuming for a large fraction of our population. If we can create more addictive food, why not create antidotes? If we could easily treat alcohol addiction with a pill, would we tell alcoholics to just apply willpower instead?

Do that in most relational dbs in the default isolation level (read committed), and concurrently executing transactions will still be able to delete users underneath you after the select.

If we take postgres as an example, performing the select takes exactly zero row level locks, and makes no guarantees at all about selected data remaining the same after you’ve read it.

edit: my mistake - I missed that the select is for update. Yes, this will take explicit locks and thus protect you from the deletion, but is slower/worse than just using foreign keys, so it won't fundamentally help you.

further edit: let's take an example even in a higher isolation level (repeatable read):

  -- setup
  postgres=# create table user_table(user_id int);
  CREATE TABLE
  postgres=# create table resources_table(resource_id int, user_id int);
  CREATE TABLE
  postgres=# insert into user_table values(1);
  INSERT 0 1

  Tran 1:
  postgres=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  BEGIN
  postgres=# select * from user_table where user_id = 1;
 user_id 
  ---------
       1
  (1 row)

  Tran 2:
  postgres=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  BEGIN
  postgres=# select * from resources_table where user_id = 1;
   resource_id | user_id 
  -------------+---------
  (0 rows)
  postgres=# delete from user_table where user_id = 1;
  DELETE 1
  postgres=# commit;
  COMMIT

  Tran 1:
  postgres=# insert into resources_table values (1,1);
  INSERT 0 1
  postgres=# commit;
  COMMIT

  Data at the end:

  postgres=# select * from resources_table;
   resource_id | user_id 
  -------------+---------
             1 |       1
  (1 row)

  postgres=# select * from user_table;
   user_id 
  ---------
  (0 rows)

You can fix this by using SERIALIZABLE, which will error out in this case.

This stuff is harder than people think, and correctly indexed foreign keys really aren't a performance issue for the vast majority of applications. I strongly recommend just using them until you have a good reason not to.

I think it because a significant fraction of my career has been spent fixing db-concurrency-related mistakes for people once they hit scale :-).

I’m not talking about using cascade - this applies perfectly well to use of on delete restrict. FKs are more or less the only standard way to reliably keep relationships between tables correct without raising up the isolation level (at least, in most dbs) or doing explicit locking schemes that would be slower than the implicit locking that foreign keys perform.