Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

We migrated our PostgreSQL database with 11 seconds downtime

gds.blog.gov.uk

Post reply on HN
Print view View original

141–150 of 210 posts

Re: We migrated our PostgreSQL database with 11 seconds downtime

#141

Post by yellowsir »

fulafel wrote: post #130
0xbadcafebee wrote: post #122

Earlier quoted context omitted.

Database changes are typically one-way. If your new change includes creating or modifying a table, such that there are new additional columns, and you populate those with data, then downgrading would destroy the changed columns and the data in them. Hence you can't downgrade once you upgrade or you'd potentially be breaking things. To downgrade safely you'd need to backup or snapshot the old database, and then restor…

DB schema migration script frameworks (at least in Python, Ruby & Java lands) do typically support both upgrade and downgrade directions. People skip implementing and testing the downgrade side if the development model doesn't need it but the problem of what happens to the data is controlled by what you put in the "down" migration script. I'd guess if you can't throw the data away, you won't do a down migration, you'…

but now you have 2 up paths. and migrations are critical, i would avoid it where possible!

Re: We migrated our PostgreSQL database with 11 seconds downtime

#142

Post by globular-toast »

Is that a picture of them doing it?

What is this weird fantasy that programming looks like this? It's always the same: a couple of people, always of diverse backgrounds, physically interacting, usually pointing and laughing at something, with perhaps another group, representing different demographics, looking on with a mixture of curiosity and awe.

Do an image search for programming to see more examples of this.

I guarantee this work was the result of guys sitting at desks, possibly in the dark, thinking. Are we collectively embarrassed that this is what programming looks like? Or are these just fantasies of management types who have never written a line in their lives?

Re: We migrated our PostgreSQL database with 11 seconds downtime

#143

Post by 15457345234 »

0xbadcafebee wrote: post #120
15457345234 wrote: post #97

Earlier quoted context omitted.

The alternative - at government scale - is absolutely 'let's host this ourselves' and that's what they should be doing, to ensure that institutional expertise remains. They should also own and operate their own datacentres which should be physically secure, not shared with commercial ventures and guarded by the armed forces, not civilian security.

Why doesn't the government manufacture their own cars? They're going to lose institutional expertise in building cars! They should also own and operate their own manufacturing facilities which should be physically secure, not shared with some 'civilian commercial venture'. By golly, the government can't do business if it isn't a datacenter operations company, a software vendor, and a car manufacturer.

> By golly, the government can't do business if it isn't a datacenter operations company, a software vendor, and a car manufacturer.

I mean, precisely, which is why some countries are rapidly turning into failed states. Too much buck-passing and outsourcing.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#144

Post by kitd »

ClassicOrgin wrote: post #64

Minor detail but did anyone else notice they are using eu-west-1? Kind of weird for the UK to be hosting there sites in another country (Ireland). I'm sure this isn't super sensitive data but still.

I think one of the bits of carry-over legislation in the Withdrawal Agreement after Brexit was the UK continuing to comply with GDPR. So using an Irish DC is still ok.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#145

Post by londons_explore »

callalex wrote: post #128
londons_explore wrote: post #40

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.

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?

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'

Re: We migrated our PostgreSQL database with 11 seconds downtime

#146

Post by londons_explore »

callalex wrote: post #128
londons_explore wrote: post #40

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.

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?

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.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#147

Post by DeathArrow »

callalex wrote: post #134
DeathArrow wrote: post #124

Seems bad that government services depend on clod providers.

Why? Do you deny the benefits of shared hosting in its entirety?

No. But I wish government agencies depend less on other entities. A government agency isn't in a business to make money. They should be concerned less with economic efficacy and more with accomplishing their goals.

They have a fiduciary duty towards the citizens.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#148

Post by alerter »

Interesting, though I have no idea why the government is using AWS in the first place. This isn't a startup hacking away trying to find PMF, or dealing with unpredictable marketing-driven traffic spikes. We know we need these services running long term, and can make solid predictions about usage patterns.

We could build a public sector cloud and/or adopt a sensible on-prem approach. This requires funding, coordination and technical leadership, but would save the taxpayer an enormous amount over the long term.

Public sector IT is a disaster in general ofc, but I know there are good engineers working there.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#149

Post by johneth »

globular-toast wrote: post #142

Is that a picture of them doing it? What is this weird fantasy that programming looks like this? It's always the same: a couple of people, always of diverse backgrounds, physically interacting, usually pointing and laughing at something, with perhaps another group, representing different demographics, looking on with a mixture of curiosity and awe. Do an image search for programming to see more examples of this. I gu…

Give it a rest, mate.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#150

Post by MrBuddyCasino »

alerter wrote: post #148

Interesting, though I have no idea why the government is using AWS in the first place. This isn't a startup hacking away trying to find PMF, or dealing with unpredictable marketing-driven traffic spikes. We know we need these services running long term, and can make solid predictions about usage patterns. We could build a public sector cloud and/or adopt a sensible on-prem approach. This requires funding, coordinatio…

If you think the public sector could improve its operational efficiency by building, operating and using their own cloud, I've go a bridge to sell you.
Post reply on HN