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

91–100 of 116 posts

Re: Our Journey to PostgreSQL 12

#91

Post by skinkestek »

tommyzli wrote: post #26
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.

What kind of data is this?

My best guesses are

- either it is SAAS in which case shard it should make sense to shard by customer

- or it is something-to-consumer (social networking?) on which case I guess you'll have to take a step back and see if you can sacrifice one of your current assumptions

... but I feel I'm missing something since what I am saying feels a bit trivial.

Re: Our Journey to PostgreSQL 12

#92

Post by m_st »

foxhill wrote: post #90
hans_castorp wrote: post #83

Earlier quoted context omitted.

> Jumping from 9 to 12 is quite a few major versions! Just a little side note. They were jumping from 9.6 to 12, not from 9.0 to 12. Before Postgres 10 was released, the first two digits defined a "major" version). So from 9.6 to 12 it's three major releases (9.6 -> 10, 10 -> 11, 11 -> 12)

still, changes between 9.6 and 12 are _numerous_, both in features and performance: llvm based query compilation, CTE de-materialisation, proper procedures, and that's just off the top of my head. i wish the process for upgrading postgres were easier/more dynamic. i'm sure plenty of people are still using versions 9.6 or earlier.

The changes you mention like llvm, CTE de-mat and such: Were these for "free" or did you have to adapt your code?

And if you didn't adapt: Would your code still running after upgrading (backwards compatible)?

I'm asking as a dev mostly working with MS-SQL and seriously considering moving to PostgreSQL one day.

Re: Our Journey to PostgreSQL 12

#93

Post by KptMarchewa »

rossmohax wrote: post #87
lumost wrote: post #66

Earlier quoted context omitted.

Why does my browser routinely eat 8GB while it used to only require 32MB 25 years ago? because it can. Web services likewise come up with features and data to fill databases. For $8/hr you can rent a DB with 500 GB of memory and 64 cores, complete with redundancy, automated backups, and failover. For the hourly rate of an oracle consultant you can rent a DB with 2TB of memory for the day. Bear in mind that many of th…

If data is sharable, it doesn't mean that it is trivial. In your example with shards by user, simple message sent between users in app becomes are very non trivial dance to be done reliably.

Then you don't do chats on your primary RDBMS.

Re: Our Journey to PostgreSQL 12

#94

Post by alexhutcheson »

skinkestek wrote: post #91
tommyzli wrote: post #26

Earlier quoted context omitted.

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.

What kind of data is this? My best guesses are - either it is SAAS in which case shard it should make sense to shard by customer - or it is something-to-consumer (social networking?) on which case I guess you'll have to take a step back and see if you can sacrifice one of your current assumptions ... but I feel I'm missing something since what I am saying feels a bit trivial.

It’s a dating app.

Re: Our Journey to PostgreSQL 12

#95

Post by wiredfool »

m_st wrote: post #92
foxhill wrote: post #90

Earlier quoted context omitted.

still, changes between 9.6 and 12 are _numerous_, both in features and performance: llvm based query compilation, CTE de-materialisation, proper procedures, and that's just off the top of my head. i wish the process for upgrading postgres were easier/more dynamic. i'm sure plenty of people are still using versions 9.6 or earlier.

The changes you mention like llvm, CTE de-mat and such: Were these for "free" or did you have to adapt your code? And if you didn't adapt: Would your code still running after upgrading (backwards compatible)? I'm asking as a dev mostly working with MS-SQL and seriously considering moving to PostgreSQL one day.

I've done PG upgrades on a code base from ~7.x now through 11. For the most part it's pretty seamless, though there are occasionally backwards incompatible changes that pop up, but they're called out in the release notes.

The biggest bites have been changing the database driver from pypgsql to psycopg2 and there was a binary column default wire format change at the 9/9.1 upgrade. In another job, I've got essentially the same code running against 9.5 and 12, and I don't notice the difference.

Performance improvements for a feature are almost always 'free', but obviously your code isn't just going to start using CTEs if you haven't been using them.

Re: Our Journey to PostgreSQL 12

#96

Post by strokirk »

lmm wrote: post #58
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…

You're not the only one. There are lots of better alternatives to SQL databases for most use cases (I'm lucky enough to have worked in some places where SQL datastores were the exception rather than the rule). But it takes a long time for cultural change to happen.

Would you mind mentioning some good options? I've always been interested in databases, but find it hard to know which ones to learn more about and when they'd actually be worth investing in (especially since it's hard to build knowledge from toy projects).

Re: Our Journey to PostgreSQL 12

#97

Post by dijit »

m_st wrote: post #92
foxhill wrote: post #90

Earlier quoted context omitted.

still, changes between 9.6 and 12 are _numerous_, both in features and performance: llvm based query compilation, CTE de-materialisation, proper procedures, and that's just off the top of my head. i wish the process for upgrading postgres were easier/more dynamic. i'm sure plenty of people are still using versions 9.6 or earlier.

The changes you mention like llvm, CTE de-mat and such: Were these for "free" or did you have to adapt your code? And if you didn't adapt: Would your code still running after upgrading (backwards compatible)? I'm asking as a dev mostly working with MS-SQL and seriously considering moving to PostgreSQL one day.

> The changes you mention like llvm, CTE de-mat and such: Were these for "free" or did you have to adapt your code?

The changes were made "for-free" as in no code needed to be changed, but when changing query planners caution should be heeded..

I remember a time where a mysql 5.6->5.7 upgrade crippled our entire monitoring stack:

https://bugs.mysql.com/bug.php?id=87164

https://dba.stackexchange.com/questions/121841/mysql-5-7-inn...

Re: Our Journey to PostgreSQL 12

#98

Post by namibj »

ddorian43 wrote: post #85
namibj wrote: post #68

Earlier quoted context omitted.

CockroachDB is pretty good at encapsulating the complexity of multi-master. You'll have to accept that transactions can fail due to conflicts, so if they are interactive, you'll have to retry manually. Edit: I'd like hear criticism, instead of just seeing disapproval.

(as a downvoter) Distributed transactions don't scale, they are NOT efficient. You can't co-partition data in Cockroachdb, so the only way is the slow way. Interleaved-data don't count.

Are you suggesting it hits scaling boundaries earlier than that single-node postgres? The TPC-C benchmark suggests fairly strong scaling up to medium~high double digit node counts, and my understanding is that it's decently conflict prone and very much relying on distributed transactions.

Of course a distributed architecture costs efficiency, but as long as it still scales further (and after some point, cheaper) than single-node alternatives, the efficiency loss is tolerable.

You can affect partitioning, but forcing it requires the payed version.

Re: Our Journey to PostgreSQL 12

#99

Post by ddorian43 »

namibj wrote: post #98
ddorian43 wrote: post #85

Earlier quoted context omitted.

(as a downvoter) Distributed transactions don't scale, they are NOT efficient. You can't co-partition data in Cockroachdb, so the only way is the slow way. Interleaved-data don't count.

Are you suggesting it hits scaling boundaries earlier than that single-node postgres? The TPC-C benchmark suggests fairly strong scaling up to medium~high double digit node counts, and my understanding is that it's decently conflict prone and very much relying on distributed transactions. Of course a distributed architecture costs efficiency, but as long as it still scales further (and after some point, cheaper) than…

> Are you suggesting it hits scaling boundaries earlier than that single-node postgres?

This is nosql scenario all over again. You have to think in cost($)/query and your data layout.

> Of course a distributed architecture costs efficiency, but as long as it still scales further (and after some point, cheaper) than single-node alternatives, the efficiency loss is tolerable.

The efficiency loss is tolerable. The question is, when ? For primary-key get, efficiency-loss is, let's say, none. So you can start distributed from the start.

For another query, especially multi-tenant when you can put a tenant in 1 box, it may start making sense AFTER scaling to 2TB memory node.

Imagine a select query doing a join. It has to read 1GB from nvme-array or from network.

Imagine a write, ending up as a distributed-write, it needs to wait for 2x+ more servers in the network.

Re: Our Journey to PostgreSQL 12

#100

Post by darkr »

esseti wrote: post #88

if pglogical better than a min downtime with pgdump/psql? it seems a lot of work to setup pglogical to migrate versions (or am i missing anything?)

With 5.7 TB data, you're probably looking at something like 24 hours for a dump/restore including index rebuilds
Post reply on HN