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

The part of Postgres we hate the most: Multi-version concurrency control

ottertune.com

Post reply on HN
Print view View original

121–130 of 148 posts

Re: The part of Postgres we hate the most: Multi-version concurrency control

#121

Post by zie »

dalyons wrote: post #93
zie wrote: post #76

Earlier quoted context omitted.

Of course subtlety matters, but as you start scaling and noticing pain points, that is when you start working towards fixing them. First you just throw hardware at the problem and that tends to scale really really well for a really long time. It's pretty rare, even at very large scale that you MUST move off of PG, there are plenty of well tested scaling solutions, if you have the $$$'s to spend. 10+ years of dev work…

Yes again the common refrains - just throw hardware at it. I/we of course know this and all the systems I’m referring to did that first until they couldn’t. But you’re kind of missing my point - im saying by the time you are noticing scale pain points it’s often too late. Too late insofar as your system has likely grown so much in breadth (complexity, features, subsystems, lines of code, services, etc) that all depen…

I think we are basically in agreement about everything, but coming from different perspectives. There is no "right" answer, but pre-mature optimization is almost always the wrong answer.

Re: The part of Postgres we hate the most: Multi-version concurrency control

#122

Post by mickeyp »

tuatoru wrote: post #119
steve_taylor wrote: post #66

Earlier quoted context omitted.

PostgreSQL became the internet's darling DBMS long before that. Oracle's acquisition of MySQL in 2008 made people finally take notice of PostgreSQL. Before that, most developers barely knew it existed.

Yeah, Postgresql was the FreeBSD of DBMSes. Solid, conceptually integral, well documented.* I recall doing an evaluation of open source databases in 2001. MySQL didn't even have row-level locking, let alone any concept of transactions. I summarised it as "easy to use; but only for data you don't care about". * Not that Postgres (as it was then) was without warts in 2001. A huge one was its "object orientation": table…

You can create types in postgresql and use them as columns... so you can have your "object" style encapsulation at a column level. So you can have a "currency" type that has both the amount and the currency.

Re: The part of Postgres we hate the most: Multi-version concurrency control

#124

Post by zinclozenge »

avinassh wrote: post #87
zinclozenge wrote: post #38

I guess the question is, which MVCC strategy would be the "right" one to pick for a modern relational database? The paper linked focuses on main memory databases, and being main memory allows you to do things you can't do when disk based.

I have same the question. I skimmed through the linked paper for conclusion, they highlight the techniques which can be used to improve, but does not say which MVCC to use for a modern database. May be I need to do a careful reading.

I took a look at https://github.com/orioledb/orioledb which is a project attempting to remedy some of Postgres' shortcomings, including MVCC. It looks like they're doing something similar to MySQL with a redo log, as well as some other optimizations. So maybe this is the answer.

Re: The part of Postgres we hate the most: Multi-version concurrency control

#125

Post by ExoticPearTree »

hodgesrm wrote: post #48
mmaunder wrote: post #12

I must admit as a web practitioner since 1994 I have a bit of an issue with this: > In the 2000s, the conventional wisdom selected MySQL because rising tech stars like Google and Facebook were using it. Then in the 2010s, it was MongoDB because non-durable writes made it “webscale“. In the last five years, PostgreSQL has become the Internet’s darling DBMS. And for good reasons! Different DB's, different strengths and…

> A non-trivial component to MySQL popularity was that easy installation ...Along with replication and being joined with the hip to PHP. As to installation, there was a point in time in the early 2000s where you could sudo to root, type 'mysql' and be talking to a live MySQL on most Linux distros that I used. No wonder a lot of people defaulted to it.

Yes, replication. MySQL made it dead easy to have DB clusters in minutes.

I'd wish PostgreSQL would have as simple when it comes to replication and failover like MySQL does. It's always a pain when switching masters back and forth.

Re: The part of Postgres we hate the most: Multi-version concurrency control

#126

Post by kermatt »

mmaunder wrote: post #12

I must admit as a web practitioner since 1994 I have a bit of an issue with this: > In the 2000s, the conventional wisdom selected MySQL because rising tech stars like Google and Facebook were using it. Then in the 2010s, it was MongoDB because non-durable writes made it “webscale“. In the last five years, PostgreSQL has become the Internet’s darling DBMS. And for good reasons! Different DB's, different strengths and…

> Oracle was one of the reasons for the catastrophic failure of eToys circa 2001

Would love to hear a from-the-trenches summary of that.

Re: The part of Postgres we hate the most: Multi-version concurrency control

#127

Post by dalyons »

re-thc wrote: post #101
dalyons wrote: post #93

Earlier quoted context omitted.

Yes again the common refrains - just throw hardware at it. I/we of course know this and all the systems I’m referring to did that first until they couldn’t. But you’re kind of missing my point - im saying by the time you are noticing scale pain points it’s often too late. Too late insofar as your system has likely grown so much in breadth (complexity, features, subsystems, lines of code, services, etc) that all depen…

And what's a more scalable solution? (in your mind)

unfortunately i have no direct experience with anything that i would consider a direct replacement for the generic utility of postgres(or any RDBMS). Mostly I have been involved in moving specific domains to storage technology that has opinions that work well with the problem at hand.

eg if it looks key-value ish, or key + timestamp (eg user transaction table), dynamodb is incredible. Scales forever, never have to think about operations. But not generally queryable like pg.

if it looks event-ish or log-ish, offload to a redshift/snowflake/bigtable. But append only & eventually consistent.

if you really need distributed global mutations, and are willing to pay with latency, spanner is great.

if you can cleanly tenent or shard your data and theres little-to-no cross-shard querying then vitess or some other RDBMS shard automation layer can work.

There are a few "postgres but distributed" dbs maturing now, like cockroach - i havent personally used them at a scale that i could tell you if it actually works or not though. AFAIU these systems still have tradeoffs around table layout and access patterns that you have to think about.

Re: The part of Postgres we hate the most: Multi-version concurrency control

#128

Post by audioheavy »

geenat wrote: post #109
kragen wrote: post #94

Earlier quoted context omitted.

yeah, i was surprised at the cluelessness of that remark. lamp was definitely not a 'rising tech stars' thing. hopefully the author is more careful about accuracy when it comes to database architecture than when it comes to www history did google even use mysql? certainly if they did they never talked about it publicly in the early 02000s, and of course facebook didn't even exist then lj, though, they used the fuck o…

MySQL (then Vitess) ran Youtube, but nowadays I do believe most product teams are using Spanner.

Yeah I think (heard anecdotally) both google/YouTube and Facebook (and many others) started with MySQL. Spanner for distributed writes has inspired most implementations although Google is the only one I know about that implements TrueTime (atomic clocks). The same year that the Spanner paper came out (after Percolator) an alternate approach (Calvin) was also published, and some of us are using that (our DB's design is inspired by it, but we've done a lot of enhancements since then).

Re: The part of Postgres we hate the most: Multi-version concurrency control

#129

Post by pmontra »

mmaunder wrote: post #12

I must admit as a web practitioner since 1994 I have a bit of an issue with this: > In the 2000s, the conventional wisdom selected MySQL because rising tech stars like Google and Facebook were using it. Then in the 2010s, it was MongoDB because non-durable writes made it “webscale“. In the last five years, PostgreSQL has become the Internet’s darling DBMS. And for good reasons! Different DB's, different strengths and…

In the early days of the web MySQL was extremely faster than anything else because it was an ISAM file with no support for transactions. That was OK for many people that self-hosted the db on the not very powerful CPUs of the time.

I remember people stating that transactions are useless, and maybe they are for some workloads, see the success of MongoDB years later.

The transactional engine InnoDB was added in version 3.23 [1] in 2001 [2] .

[1] https://en.wikipedia.org/wiki/Comparison_of_MySQL_database_e...

[2] https://en.wikipedia.org/wiki/MySQL

Re: The part of Postgres we hate the most: Multi-version concurrency control

#130

Post by edmundsauto »

p_l wrote: post #71
edmundsauto wrote: post #69

Earlier quoted context omitted.

I remember it differently - we needed replication for "hot" backups. At that time, scalability was a major issue - so anyone (including businesspeople) wanted to have a scalable architecture. MySQL spoke to the practical (default install on cPanel hosts, easy replication) and the aspirational (you're going to blow up and need to scale). Digg.com also had a really influential technical team - hearing about how they di…

maybe you were on the more funded side of history in this. As for me, Digg is way after LAMP got solidly plonked into "what I need for a dynamic website on cheap". Essentially, start at 2000-2001 and more and more people going into running websites for all kinds of reasons (forums, blogs, webshops, etc. often hosted on low end offerings)

I didn't enter the workforce until 2004, so yeah missed some of the early early days of PHP/MySQL. I used it for government work, was definitely not well funded haha! But I suspect digg started with MySQL b/c of similar reasons as anyone else, then helped amplify the cycle.
Post reply on HN