3 ms·

Note that the enemy of low/zero downtime migrations like this is long running queries. Ie. a single update query which takes 30 mins. You either have to kill

Note that the enemy of low/zero downtime migrations like this is long running queries. Ie. a single update query which takes 30 mins. You either have to kill and roll back that query, or suffer 30 mins of unavailability. As far as I know, there is no way to migrate a currently in progress query.

Amezarak 3y ago
It's hard for me personally to imagine a 30-minute update query that is not written extremely inefficiently, or else a one-time huge data migration. There are a lot of the former in the wild to be sure. I've had a lot of run turning minutes-hours into milliseconds. :)
- londons_explore 3y ago
  Data scientists... And when you look at the query, it'll be 800 lines of SQL...
  - hobs 3y ago
    Heh, that's not so bad - 2 jobs ago I had to fix a KPI generation process that was 70k lines of dynamic sql, that unwrapped to up to 1m LOC :)
    - chaz6 3y ago
      Did you ever run into a hard limit of statement size?
      - hobs 3y ago
        No, but all the code generation was happening in the server itself so I dont know how hard and fast it applies, iirc SQL Server's limits are like 64k x packet size.
necovek 3y ago
For a software engineering project, you probably want to limit your transactions to much less than that (set statement_timeout is your friend). If you've got extremely long transactions, you can probably avoid doing the switch-over when they run (hopefully they are not a random occurrence but a result of a scheduled job or similar). In combination with transaction time limit and fail-over configuration (where you fail the old primary), you can control the slowdown (instead of downtime, eg. with pgbouncer) very precisely. I would be more concerned with the DNS TTL being respected in the entire stack (and external caching DNS servers you rely on), tbh. But it is usually not critical to avoid a dozen seconds of downtime for an app, so whatever is simpler for you should be your go to solution.
callalex 3y ago
Well you’ve certainly introduced a teaching moment to me! What are the nature of writes you deal with that last 30+ minutes? What kind of data/what kind of people are involved with such DB writes where you need to rely on the DB engine to work so hard instead of something more split up by queues at a higher layer?
- londons_explore 3y ago
  It's usually badly engineered things. However, just because it's badly engineered doesn't mean it's fine for it to break :-P Things like a super complex n^3 complexity query to find all users who were 3 hops away from a known fraudster, where a 'hop' means 'shared any piece of account data in common'
  - chaz6 3y ago
    Would that be more suited to a graph database such as Neo4j?
- londons_explore 3y ago
  Postgres queries are streaming. Ie. If the query result has 1 billion rows, but the client only has enough ram for 1 million rows, then the query will be slowed down until the client is ready to accept more rows. If the client is slowly processing through the result set, then the query can take many minutes/hours.

Amezarak 3y ago

It's hard for me personally to imagine a 30-minute update query that is not written extremely inefficiently, or else a one-time huge data migration. There are a lot of the former in the wild to be sure. I've had a lot of run turning minutes-hours into milliseconds. :)

londons_explore 3y ago
Data scientists... And when you look at the query, it'll be 800 lines of SQL...
- hobs 3y ago
  Heh, that's not so bad - 2 jobs ago I had to fix a KPI generation process that was 70k lines of dynamic sql, that unwrapped to up to 1m LOC :)
  - chaz6 3y ago
    Did you ever run into a hard limit of statement size?
    - hobs 3y ago
      No, but all the code generation was happening in the server itself so I dont know how hard and fast it applies, iirc SQL Server's limits are like 64k x packet size.

necovek 3y ago

For a software engineering project, you probably want to limit your transactions to much less than that (set statement_timeout is your friend). If you've got extremely long transactions, you can probably avoid doing the switch-over when they run (hopefully they are not a random occurrence but a result of a scheduled job or similar). In combination with transaction time limit and fail-over configuration (where you fail the old primary), you can control the slowdown (instead of downtime, eg. with pgbouncer) very precisely. I would be more concerned with the DNS TTL being respected in the entire stack (and external caching DNS servers you rely on), tbh. But it is usually not critical to avoid a dozen seconds of downtime for an app, so whatever is simpler for you should be your go to solution.

callalex 3y ago

Well you’ve certainly introduced a teaching moment to me! What are the nature of writes you deal with that last 30+ minutes? What kind of data/what kind of people are involved with such DB writes where you need to rely on the DB engine to work so hard instead of something more split up by queues at a higher layer?

londons_explore 3y ago
It's usually badly engineered things. However, just because it's badly engineered doesn't mean it's fine for it to break :-P Things like a super complex n^3 complexity query to find all users who were 3 hops away from a known fraudster, where a 'hop' means 'shared any piece of account data in common'
- chaz6 3y ago
  Would that be more suited to a graph database such as Neo4j?
londons_explore 3y ago
Postgres queries are streaming. Ie. If the query result has 1 billion rows, but the client only has enough ram for 1 million rows, then the query will be slowed down until the client is ready to accept more rows. If the client is slowly processing through the result set, then the query can take many minutes/hours.