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).

self-plug, but you could give https://pgexercises.com/ a try. No need to spin up your own DB etc.

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?

Indeed - People In The Know have some cocnerns with this approach: https://ardentperf.com/2021/07/26/postgresql-logical-replica... .

At $work we did use this approach to upgrade a large, high throughput PG database, but to mitigate the risk we did a full checksum of the tables. This worked something like:

    * Set up logical replica, via 'instacart' approach
    * Attach physical replicas to the primary instance and the logical replica, wait for catchup
    * (very) briefly pause writes on the primary, and confirm catchup on the physical replicas
    * pause log replay on the physical replicas
    * resume writes on the primary
    * checksum the data in each physical replica, and compare

This approach required <1s write downtime on the primary for a very comprehensive data validation.

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.

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.

Can you give an example? I’m not aware of a mechanism like that that will protect you from concurrency artifacts reliably - certainly not a general one.

> so long as your code is correct

This is a pretty tough definition of correct, though. Without foreign key constraints you'll have a really tough time dealing with concurrency artifacts without raising your isolation levels, which generally brings larger performance concerns.

Table User: userid, etc

Table Resources: resourceid, userid, etc

If I want to restrict deletion of a user to only be possible after all the resources are deleted, I'm forced into using higher-than-default isolation levels in most DBs. This has significant performance implications. It's also much easier to make a mistake - for example, if when creating a resource I check that the user exists prior to starting the transaction, then start the tran, then do the work, it will allow insertion of data into a nonexistent user.

How does this stay correct in the presence of concurrent activity?

HA on rds uses synchronous replication - you won’t lose data on automated failover under any normal circumstances.

This is the c-store paper, which was evolved into Vertica: http://www.cs.umd.edu/~abadi/vldb.pdf . It's very readable, worth a look.

edit: column stores existed before c-store, but c-store did some very nifty stuff around integrating compression awareness into the query executor

Fair enough - I've seen it more commonly in smaller machines, but they're also more common in the fleets I've observed (and the ones that are more likely to run close to the edge memory-wise). I have also seen it in systems up to 32GB RAM, so it's by no means a non-issue in systems that are at least somewhat larger. The general observation that oomd/earlyoom + swap is a better solution than no swap still generally applies.

I think the key here is what you mean by using swap. Having a lot of data swapped out is not bad in and of itself - if the machine genuinely isn't using those pages much, then now you have more space available for everything else.

What's bad is a high frequency of moving pages in and out of swap. This is something that can cause your machine to be functionally unavailable. But it is important to note that you can easily trigger somewhat-similar behaviour even with swap disabled, per my previous comment. I've seen machines without swap go functionally unavailable for > 10 minutes when they get low on RAM - with the primary issue being that they were grinding on disk reloading dropped executable pages.

I agree that in low memory situations killing off something ASAP is often the best approach, my main point here is that relying on the Linux OOM killer is not a good way to kill something off ASAP. It kills things off as a last resort after trashing your machine's performance - userspace OOM killers in concert with swap typically give a much better availability profile.

> If you have a build farm / CI machines, don't use swap. With swap, if a user schedules too many compiles at once, machine will slow to a halt and become kinda-dead, not quite tripping dead timer, but not making any progress either. Instead, set up the OOM priority on the users processes so they are killed first. If OOM hits, clang is killed, build process fails, and we can go on.

This doesn't really work that well. It's true that if you enable swap and have significant memory pressure for any extended period your machine will grind to a halt, but this will _also_ happen if you don't use swap and rely on the Linux OOM killer.

Indeed, despite the lack of swap, as part of trying to avoid OOM killing applications, Linux will grind the hell out of your disk - because it will drop executable pages out of RAM to free up space, then read them back in again on demand. As memory pressure increases, the period of time between dropping the page and reading it back in again becomes very short, and all your applications run super slowly.

An easy solution to this is a userspace OOM-kill daemon like https://facebookmicrosites.github.io/oomd/ . This works on pressure stall information, so it knows when your system is genuinely struggling to free up memory.

On the historical fleets I've worked on pre-OOMD/PSI, a reasonable solution was to enable swap (along with appropriate cgroups), but target only allowing brief periods of swapin/out. This gives you two advantages:

* allows you to ride out brief periods of memory overconsumption

* allows genuinely rarely accessed memory to be swapped out, giving you more working space compared to having no swap

just a note that in newer versions of PG I believe partition changes no longer require an access exclusive lock on the parent table, which I'm looking forward to when we upgrade...

As they note table lock acquisition can be super painful. It's possible to write a script to use pg_stat_activity to detect that the table alteration is waiting on a lock and kill conflicting work. For some workloads the kill is unacceptable, but on highly active systems killing off a few operations is usually much less damaging than letting sessions pile up on a lock wait.

Personally I would love it if it were possible to set a mode on postgres whereby access exclusive locks break other locks and cause the transactions holding them to roll back (similar to what happens with vacuums).

Particularly in the dynamo case, you're working outside of a common buffer pool. One of the key benefits of normalization in a typical db is that you can fit more stuff into memory if you normalize - dynamo renders that point largely moot.

To confirm, RDS does support in place major version upgrade for postgres.

The query hint thing is a serious issue. I get the concern that people won't report issues and so on, but on heavily loaded DBs having no decent operational response to a bad plan change is really scary. In an ideal world something like oracle's plan stability would be pretty helpful.

> That's a language design issue.

Agreed. And, also, who cares? Yes, Java is a mediocre language and way too verbose. I spend a bunch of time in Java and I don't notice the worst of that because I (like literally every Java dev I know) use an IDE. You shouldn't separate a language from the standard tooling it comes with/enables - they're intertwined.