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

Thoughts on Uber’s List of Postgres Limitations

blog.2ndquadrant.com

Post reply on HN
Print view View original

41–50 of 108 posts

Re: Thoughts on Uber’s List of Postgres Limitations

#41

Post by pilif »

bigato wrote: post #39
pilif wrote: post #22

Earlier quoted context omitted.

you can use pg_upgrade with -k - it will complete within seconds. Afterwards, things will be slow until a complete analyze updates the statistics, but the update itself can be done in seconds. I have updated ~2TB of database from 9.0 all the way to 9.5 over the years.

The problem with this is that if anything fails, you can potentially corrupt your data and have no backup plan. To make that option safe, you would have to copy your data directory first, and you need to be offline for that. So you have to add the time it takes to make that copy.

This is why I ensure that the slaves are up to date, then disconnect them, pg_upgrade the master and resync the slaves (which is required anyways). If something goes wrong, I would fail over to the slave.

Also: You don't need to be offline to copy the data directory. Check `pg_start_backup` or `pg_basebackup` (which calls the former)

Re: Thoughts on Uber’s List of Postgres Limitations

#42

Post by lentil »

bigato wrote: post #39
pilif wrote: post #22

Earlier quoted context omitted.

you can use pg_upgrade with -k - it will complete within seconds. Afterwards, things will be slow until a complete analyze updates the statistics, but the update itself can be done in seconds. I have updated ~2TB of database from 9.0 all the way to 9.5 over the years.

The problem with this is that if anything fails, you can potentially corrupt your data and have no backup plan. To make that option safe, you would have to copy your data directory first, and you need to be offline for that. So you have to add the time it takes to make that copy.

You don't need to be offline to make a copy of the data directory. You can do that ahead of time, keeping all the WAL segments up until the point that you make the switch.

(See also https://www.postgresql.org/docs/9.5/static/continuous-archiv...)

Re: Thoughts on Uber’s List of Postgres Limitations

#43

Post by [deleted] »

tehbeard wrote: post #12
Dowwie wrote: post #8

Does anyone know the back story to Uber - why didn't it try to improve Postgres rather than move on to feed on another host?

Most likely they don't have any engineers with the skills necessary to build/improve a RDBMS. (note I said build, not use, different skillsets between driving a car and re-configuring the engine to run on seed oil)

[deleted]

Re: Thoughts on Uber’s List of Postgres Limitations

#44

Post by bigato »

pilif wrote: post #41
bigato wrote: post #39

Earlier quoted context omitted.

The problem with this is that if anything fails, you can potentially corrupt your data and have no backup plan. To make that option safe, you would have to copy your data directory first, and you need to be offline for that. So you have to add the time it takes to make that copy.

This is why I ensure that the slaves are up to date, then disconnect them, pg_upgrade the master and resync the slaves (which is required anyways). If something goes wrong, I would fail over to the slave. Also: You don't need to be offline to copy the data directory. Check `pg_start_backup` or `pg_basebackup` (which calls the former)

That requires the master and slaves to run different versions for a while. And that is not possible with stock postgresql, is it?

Regarding your second point, I meant copying the data directory as in a 'cp' command. Or rsync if you will. The functions you mentioned are only useful when doing a dump, isn't it? And recovering from a upgrade problem using a dump is way slower than just starting the previous version in the backup data directory.

Re: Thoughts on Uber’s List of Postgres Limitations

#45

Post by jasonlotito »

Dowwie wrote: post #8

Does anyone know the back story to Uber - why didn't it try to improve Postgres rather than move on to feed on another host?

Considering some of the things they wanted to improve were presented almost a decade before as problems (along with technically sound solutions to those problems), I actually think they did the best thing they could for the Postgres community.

Re: Thoughts on Uber’s List of Postgres Limitations

#46

Post by mason55 »

mhurron wrote: post #31
Dowwie wrote: post #8

Does anyone know the back story to Uber - why didn't it try to improve Postgres rather than move on to feed on another host?

> why didn't it try to improve Postgres Honestly, why would they? If there is a product that does what they need, why spend resources improving another. Just because it's open source and they could spend money improving it, doesn't mean a company will spend that money improving it.

> If there is a product that does what they need, why spend resources improving another.

I would hardly say MySQL does what they need without any improvements. They built an entire second platform on top of it.

https://eng.uber.com/schemaless-part-one/

Re: Thoughts on Uber’s List of Postgres Limitations

#47

Post by debaserab2 »

arnarbi wrote: post #34
f055 wrote: post #10

I came here to write a snarky comment, but now I can write two ;) first: if you think any particular db platform is clearly a winner in "db wars", you are naive. there are so many factors involved in configuring the db, the backend, the frontend etc. that you can always find a case where: the supposedly winning db is failing, or the supposedly worse db is performing perfectly fine. and from my experience, you should…

That second point is a really unnecessarily belittling straw man, and I think such comments are counterproductive to the discussion.

I don't know, if you're going to critique the very talented engineers at Uber, seeing your blog fall over due to capacity doesn't lend you a lot of credibility.

Re: Thoughts on Uber’s List of Postgres Limitations

#48

Post by pilif »

bigato wrote: post #44
pilif wrote: post #41

Earlier quoted context omitted.

This is why I ensure that the slaves are up to date, then disconnect them, pg_upgrade the master and resync the slaves (which is required anyways). If something goes wrong, I would fail over to the slave. Also: You don't need to be offline to copy the data directory. Check `pg_start_backup` or `pg_basebackup` (which calls the former)

That requires the master and slaves to run different versions for a while. And that is not possible with stock postgresql, is it? Regarding your second point, I meant copying the data directory as in a 'cp' command. Or rsync if you will. The functions you mentioned are only useful when doing a dump, isn't it? And recovering from a upgrade problem using a dump is way slower than just starting the previous version in t…

> That requires the master and slaves to run different versions for a while. And that is not possible with stock postgresql, is it?

Yes. That's not possible. But if I announce the downtime, bring master and slave down, migrate the slave and run our test-suite, migrate the master, run the test suite again and bring the site back up, then I know whether the migration worked.

If the migration on the slave fails, well, then I can figure out where the problem lies and just bring master back.

If the migration on master fails, but works on slave, then I can bring slave up as the new master.

No matter what, there's always one working copy and the downtime is limited to two `pg_upgrade -k` runs (which is measured in minutes).

> Regarding your second point, I meant copying the data directory as in a 'cp' command. Or rsync if you will.

Yes. You execute `select pg_start_backup()` to tell the server that you're now going to run cp or rsync and to thus keep the data files in a consistent state. Once you have finished cp/rsync, you execute `select pg_stop_backup()` to put the server back in the original mode.

This works while the server is running.

If you don't want the hassle of executing these commands, you can also invoke the command-line tool `pg_basebackup` which does all of this for you.

Re: Thoughts on Uber’s List of Postgres Limitations

#49

Post by lentil »

bigato wrote: post #44
pilif wrote: post #41

Earlier quoted context omitted.

This is why I ensure that the slaves are up to date, then disconnect them, pg_upgrade the master and resync the slaves (which is required anyways). If something goes wrong, I would fail over to the slave. Also: You don't need to be offline to copy the data directory. Check `pg_start_backup` or `pg_basebackup` (which calls the former)

That requires the master and slaves to run different versions for a while. And that is not possible with stock postgresql, is it? Regarding your second point, I meant copying the data directory as in a 'cp' command. Or rsync if you will. The functions you mentioned are only useful when doing a dump, isn't it? And recovering from a upgrade problem using a dump is way slower than just starting the previous version in t…

pg_start_backup/pg_basebackup are used when doing an rsync-style copy. You'll end up with a copy of the data directory, rather than a dump. You can then start up a server instance directly in the resulting backup directory.

Re: Thoughts on Uber’s List of Postgres Limitations

#50

Post by mnw21cam »

bigato wrote: post #44
pilif wrote: post #41

Earlier quoted context omitted.

This is why I ensure that the slaves are up to date, then disconnect them, pg_upgrade the master and resync the slaves (which is required anyways). If something goes wrong, I would fail over to the slave. Also: You don't need to be offline to copy the data directory. Check `pg_start_backup` or `pg_basebackup` (which calls the former)

That requires the master and slaves to run different versions for a while. And that is not possible with stock postgresql, is it? Regarding your second point, I meant copying the data directory as in a 'cp' command. Or rsync if you will. The functions you mentioned are only useful when doing a dump, isn't it? And recovering from a upgrade problem using a dump is way slower than just starting the previous version in t…

Yes, that does allow you to copy the database directory with the "cp" command. The command tells postgres to stop deleting obsolete WAL files until further notice. As long as you start your copy after you issue the command, and copy across at least all of the files that are present (as in, you can ignore new files that are created), then the data is safe. Just don't forget to tell postgres that the backup has finished afterwards.
Post reply on HN