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

Our Journey to PostgreSQL 12

tech.coffeemeetsbagel.com

Post reply on HN
Print view View original

21–30 of 116 posts

Re: Our Journey to PostgreSQL 12

#21

Post by paulryanrogers »

tommyzli wrote: post #13
ants_a wrote: post #10

pg_upgrade would have worked fine given your requirements. Just using normal streaming replication to move database over to new systems and then performing an in place pg_upgrade there would be doable with most likely a couple of minutes of downtime and a much quicker and more robust process.

How would that have worked with multiple replicas cascading from the new primary? Streaming replication doesn't work across versions, so would we have had to build out a tree of new instances, then pg_upgrade them all at the same time?

Good questions. As logical replication matures it may someday be possible to replicate among versions.

Re: Our Journey to PostgreSQL 12

#22

Post by tommyzli »

shoo wrote: post #9

> We then made the following changes to the subscriber database in order to speed up the synchronization: [...] Set fsync to off I'm curious how much risk of data loss this added. I guess the baseline is "we need to migrate before we run out of disk" I.e. you're either going to have data loss or a long period of unavailability if the migration cannot be carried out fast enough.

ants_a is correct. Also, our NVMe storage is ephemeral so you aren't recovering from a power loss anyways :)

Re: Our Journey to PostgreSQL 12

#23

Post by sharadov »

paulryanrogers wrote: post #21
tommyzli wrote: post #13

Earlier quoted context omitted.

How would that have worked with multiple replicas cascading from the new primary? Streaming replication doesn't work across versions, so would we have had to build out a tree of new instances, then pg_upgrade them all at the same time?

Good questions. As logical replication matures it may someday be possible to replicate among versions.

I've replicated across different PG versions, there is an extension called mimeo, which is fantastic for logical replication https://pgxn.org/dist/mimeo/1.5.1/doc/howto_mimeo.html

Re: Our Journey to PostgreSQL 12

#24

Post by paulryanrogers »

0xbadcafebee wrote: post #8

Am I the only one who thinks it's bizarre that a structured query language defines so much of how we choose to architect and operate our systems? Think about it for a sec: SQL is literally just a language to query and manipulate data. There's no reason that schema changes and data changes have to happen only through the one language, and only through one interface on one piece of software. For whatever reason, this h…

Why are we still using ASCII or Unicode character interfaces in shells? Because like SQL they work and are moderately well understood.

There are many query languages and having one common one as a base is useful to transfer skills. Think of it as an on ramp to more specific dialects or technologies.

Re: Our Journey to PostgreSQL 12

#25

Post by orf »

tommyzli wrote: post #6
temp667 wrote: post #3

Very nice. Did they migrate into Amazon RDS while doing this? For smaller projects I've stopped doing the self managed postgresql thing. The pricing is higher (75%?) for RDS for some use cases but can be worth it. Going to try RDS Proxy next.

Thanks! We stuck with plain EC2. RDS has a limit of 80,000 provisioned IOPS and our read replicas on Postgres 9.6 would regularly hit near double that during peak

That limit doesn’t apply to Aurora - did you consider that?

Re: Our Journey to PostgreSQL 12

#26

Post by tommyzli »

mbyio wrote: post #14

This is a very difficult thing to do. Very impressive. I have so many questions but my number one is: were you able to evaluate alternatives to your existing vertical scaling based setup? For example, cockroachdb, multi-master postgres, using sharding instead of a single DB, etc. At that database size, you are well past the point in which a more advanced DB technology would theoretically help you scale and simplify y…

basically what paulryanrogers said.

We thought about migrating to Citus, but I don't have a good idea of how to shard our dataset efficiently.

If we were to shard by user id, then creating a match between two people would require cross-shard transactions and joins. Sharding by geography is also tough because people move around pretty frequently.

Re: Our Journey to PostgreSQL 12

#27

Post by tommyzli »

orf wrote: post #25
tommyzli wrote: post #6

Earlier quoted context omitted.

Thanks! We stuck with plain EC2. RDS has a limit of 80,000 provisioned IOPS and our read replicas on Postgres 9.6 would regularly hit near double that during peak

That limit doesn’t apply to Aurora - did you consider that?

Apparently I'm living in the twilight zone because I have a vivid memory of reading the Aurora docs and seeing the same limit. Oh well, it's something to consider for the next upgrade.

Re: Our Journey to PostgreSQL 12

#28

Post by sharadov »

Given the limitations that you had ( move to larger instance, downtime restrictions), you took the most optimal path. Fantastic work! I am in the process of what you did, but across couple hundred instances ( am using pg_upgrade for most but will be using an approach similar to yours where we can't afford downtime).

Re: Our Journey to PostgreSQL 12

#29

Post by sharadov »

lmarcos wrote: post #12

Silly question: they have 5.7TB in their database... How come? It's a dating app founded in 2012, I can understand that one can accumulate such much data in 11 years, but sure you can periodically archive "unused" data and move it out of your primary database, right? I mean, are the 5.7TB of data actually needed in a daily basis by their app? (I assume data for analytical purposes is not stored in their primary DB, w…

Not a lot, but there may be options to partition, but again, you can't comment unless you know the design.

Re: Our Journey to PostgreSQL 12

#30

Post by outworlder »

0xbadcafebee wrote: post #8

Am I the only one who thinks it's bizarre that a structured query language defines so much of how we choose to architect and operate our systems? Think about it for a sec: SQL is literally just a language to query and manipulate data. There's no reason that schema changes and data changes have to happen only through the one language, and only through one interface on one piece of software. For whatever reason, this h…

> Why haven't we created a database yet which works more like the Unix operating system?

Not to be overly snarky, but have you tried? Database design is full of trade-offs.

Post reply on HN