23 ms·

Why we lost Uber as a user

1234 points by areski 10y ago

cyberferret 10y ago
I am guessing that VACUUM only kicks in for row deletions? With that busy a table, can't you just bypass VACUUM and reuse dead keys? I am assuming because you are talking indexes here, you are talking fixed length records and not dynamically allocated VARCHARs etc.??
- cesarb 10y ago
  Postgres uses MVCC, which is basically copy-on-write: every UPDATE to a row actually creates a new row. Once all transactions that could see the old row have finished, the old row can be pruned.
  - cyberferret 10y ago
    Ah, thanks for the clarification - the bloat issue makes more sense now.
    - deleted 10y ago
      [deleted]
cemerick 10y ago
This only talks about a particular write amplification issue. A far better message IMO is just a few clicks upthread: https://www.postgresql.org/message-id/579795DF.10502%40commandprompt.com https://www.postgresql.org/message-id/579795DF.10502%40comma... Provides a link to the rationale post from uber (https://eng.uber.com/mysql-migration/ https://eng.uber.com/mysql-migration/), and a tl;dr of it. Prior discussion here: https://news.ycombinator.com/item?id=12166585 https://news.ycombinator.com/item?id=12166585
- dspillett 10y ago
  It is nice to see that while the thread does question some of Uber's motivation for the change, where there is a genuine problem with their product there is a frank admission (with quotes like "this is a common problem case we don't have an answer for yet" and "limitations of our current replication system are real, or we wouldn't have so many people working on alternatives") which people discuss seriously (asking for clarification and/or suggesting ways forward) rather than reacting with a knee-jerk defensive posture.
- MustardTiger 10y ago
  Yes, and an even better message is the reply to it, pointing out how almost all of their issues aren't really issues: https://www.postgresql.org/message-id/20160727002711.GI4028%40tamriel.snowman.net https://www.postgresql.org/message-id/20160727002711.GI4028%...
gnur 10y ago
Wow, nice explanation. I was expecting a lengthy post with refuting everything uber said and explaining why pgsql was a better choice and uber was wrong. Nice and clean post showing that acknowledging a weakness isn't a terrible choice. I do wonder whether a different data structure would have mitigated the issue instead of transitioning to a different storage engine.
- collyw 10y ago
  Yes, I have a lot more respect for this than say MongoDb which claims to be great at everything.
  - spriggan3 10y ago
    > MongoDb which claims to be great at everything lol right, with MongoDb, Map Reduce is a joke, GridFS is slow and barely usable, the storage is extremely inefficient, the "query engine" slow, and don't get me started on their "full text search" engine. MongoDb is a successful marketing stunt in the "Nodejs era".
    - oneloop 10y ago
      Also, wasn't there a ridiculous issue that they had where the db can't be bigger than 4gb on a 32 bit file system because that's the largest size a file can have...?
      - thom_nic 10y ago
        Yes, apparently the limit on 32-bit is 2GB actually. MongoDB has always stated upfront that 32-bit architectures are not recommended for production use for precisely this reason: http://blog.mongodb.org/post/137788967/32-bit-limitations http://blog.mongodb.org/post/137788967/32-bit-limitations This has also has been stated on their download page for 32-bit binaries as well.
        StavrosK 10y ago
jjp 10y ago
The original Uber blog post that led to the discussion - https://eng.uber.com/mysql-migration/ https://eng.uber.com/mysql-migration/
jhh 10y ago
The attitude with which the article is discussed in the mailing list is admirable.
- coldtea 10y ago
  Couldn't help contrasting it to some popular programming language mailing lists, especially a CSP inspired language by a major search company.
  - anamoulous 10y ago
    Couldn't help contrasting it to the comments on this very site.
  - eternalban 10y ago
    In contrast, there is the community of a "useless language" (to quote one of its leading lights) that has never adopted [the] acceptance of paternalism as a requirement of espirit de corps. imho some of the glaring shortcomings of the technology you refer to can be traced back to the very issue that you are highlighting.
grandalf 10y ago
How well will INNODB scale for this particular use case? It seems that the architecture is a bit too reliant on the complex performance characteristics of one subsystem.
matthewmacleod 10y ago
The PoststgreSQL project never fails to impress me. I know that there are some use cases which are not currently covered by it versus alternatives, but I have consistently got the feeling that everybody involved in the project is supremely professional and interested in building an excellent database – and the focus is on how to work to fix these use cases, instead of pointless mudslinging. Class act.
- babuskov 10y ago
  > The PoststgreSQL project never fails to impress me. Except the name is a bit clunky and hard to write ;)
  - scardine 10y ago
    If only a clunky name was the worst problem with some software (even closed source) we have to deal with.
    - Grishnakh 10y ago
      Slightly clunky names are fine as long as they're somewhat cute or clever. Names are important because it's how we identify and remember things, and a catchy name (even if it seems a little ridiculous or clunky) is better than a boring, banal name if the catchy name is memorable. I'll take PostgreSQL's name (which I consider clever) any day over some name completely devoid of originality or thoughtfulness, such as most of the names Microsoft uses for its software.
      - kbenson 10y ago
        Yes, at least it's searchable. The current trend of using common words as names was ridiculous as of a few years ago. Now it's just painful. Someone's going to name their project "The" and I'm not sure I'll be able to remain civil at that point.
        teddyh 10y ago
erikb 10y ago
I agree it's a nice thing. But some kind of answer may also be good. Like restructuring your data to become faster. I also wonder what happened the last few (10) years. When I was in university I'm pretty sure I learned that JOIN was Satan's mother and if you have a big DB you need to avoid JOINs as much as possible. That's not a big deal today anymore, it seems.
- mnw21cam 10y ago
  That kind of thinking is probably what spawned the whole "do the join in the app, not the database" anti-pattern. The truth is, the database is going to be much faster at performing a join than loading the contents of two tables into your app and iterating. If you need the data that results from doing a join, doing a join is the best way to get it. Unless you already have your entire database in-memory in your app, that is. In that case, why do you have a database?
  - dagss 10y ago
    What you are missing here is "denormalization" -- e.g. many-to-many relationships. You can either use a JOIN with a table on a "normalized" database, or keep managing the result of the join in application code. Loading the entire tables into application code very seldom has anything to do with it... A more realistic example is, do you get Alice's pets by doing a JOIN on tables Person, Pet, PetOwnedByPerson ("SQL") -- or by having an array column "pets" in Person? ("NoSQL")
    - erikb 10y ago
      Yes "denormalization" was the word for that. Is that still a thing?
      - lostcolony 10y ago
        It's a term used frequently in NoSQL land, to explain a key difference to people coming from SQL. In SQL land, normalizing your data is still the canonical thing to do, and I don't recall anyone in academia officially talking about denormalizing ever having its place...but in industry, the realities of use cases and performance have meant its usage. But I don't know that it's a standard tool given out to graduating software devs and DBAs.
ZanyProgrammer 10y ago
Its weird seeing a post mortem for losing a user (I really want to say customer) from a piece of FOSS. Its also weird (still!) to consider Uber a tech company, rather than a company that happens to use tech.
- menzoic 10y ago
  Just curious, what's your definition of "Tech Company"? All services provided by Uber are purely technical. Drivers and Riders are customers of Uber's technology. The full name of the company is "Uber Technologies Inc."
  - ZanyProgrammer 10y ago
    Well if that's what their name says it must be true!
  - vmateixeira 10y ago
    Acording to UK's Companies House search, Uber's nature of business (SIC) is 74990 - Non-trading company. This is quite vague but it doesn't mention technology. Interestingly, they were also called UBER TECHNOLOGIES LTD and UBERTECHNOLOGY LIMITED at some point in time, but these companies are now dissolved.
    - HappyTypist 10y ago
      That's because Uber UK is a pure support office, at least that's what their lawywers and accountants argue. The real company in Luxembourg.
      - caoilte 10y ago
        If that were true the nature of their company would be 'tax dodge'
  - rplst8 10y ago
    I wasn't sure what to think of that comment either when I first read it, but I sort of see where he is coming from. One side you have companies like Oracle, Microsoft, and IBM types that actually develop new forms of technology and sell the technology to people. Then there are companies that leverage technology in other industries to "disrupt" like OpenTable, Uber, and AirBnB. Then there are companies like Google and Facebook that straddle that line. They've developed and contributed back huge advances in technology, they mostly make their money from another industry, but also sell some tech in certain areas (mostly Google).
cel1ne 10y ago
> "In some ways, people worry about the bugs they have seen, not the bugs they haven't seen." [0] This is a key take-away for me. I used mySQL extensively and switched to postgres for everything years back. I would need extremely good reasons to use mySQL again. What you get with postgres is a lot more consistency and peace-of-mind, in my opinion. [0] https://www.postgresql.org/message-id/20160727160408.GA23585%40momjian.us https://www.postgresql.org/message-id/20160727160408.GA23585...
mrfusion 10y ago
I don't understand if they mean that foreign key relationships are contributing to the problem. If so is it possible to turn off foreign key constaints during a big load? Would it be worth removing indexes during a big load? (Currently fighting with an etl that can't get above 500 rows / second even after using copy from)
- richardwhiuk 10y ago
  The reason the indexes are their is because the table is involved in multiple JOINs, which without the indexes are very slow. I suspect it's a straight index, not a foreign key constraint.
- barrkel 10y ago
  All indexes on a pg table need updating if any value in the row is modified, that's the write amplification. The indexes are needed for joins, they're not so important for foreign keys. Certainly checking foreign key constraints works better with indexes, but those are usually the other way around - verifying row exists with primary key, ie only one index. Updates to primary key would benefit from index on foreign key columns, but that's much rarer.
- lmm 10y ago
  Indices trade reads for writes (there is no free lunch); if you index every column of your database you'll be able to do any read query reasonably fast but all writes will be slow, if you index nothing then writes will be fast but every read will be a full table scan. Presumably they have their indices because they need them for their read queries.
- xfalcox 10y ago
  Dropping constraints when doing ETL is common. You can create then after and rollback if problems arise.
rimantas 10y ago
but have you used MySQL? Maybe I am too nitpicky, but I always get suspicious when people make claims about technology whose name they cannot spell right.
- babuskov 10y ago
  He also spelled Postgres "postgres" ;)
- LeifCarrotson 10y ago
  In this case, Fabian's CV (linked from his GitHub profile, linked from his HN profile) does claim MySQL experience - correctly capitalized. But yes, I also get suspicious when people can't spell a technology correctly. For example, anyone who has spent time reading some documentation will know that Lua is capitalized in title case, and not written LUA.
  - lonelycoder2 10y ago
    Yes this, it's Xcode not xCode people
  - ZoFreX 10y ago
    Really? I will claim you will not meet many engineers who know more about Tcl than I do, and I can't remember whether it's TCL or Tcl. The absence of that knowledge has not impeded me from editing the Tcl interpreter itself. I am not convinced such pedantic measure of spelling is an accurate predictor of knowledge, to me it sounds more like elitism.
    - ngcazz 10y ago
      Table manners, itsy-bitsy rules for itsy-bitsy people as Robert Pirsig put it.
      - zeveb 10y ago
        Or, table stakes to distinguish folks worth spending time on & folks not worth spending time on. Since it's so easy to get the little things right, anyone who doesn't is just a bit suspect. It's like discarding resumés which arrive with large food stains on them. It's just too easy to print off a clean copy, that anyone who doesn't must be a bit off.
brightball 10y ago
In reading that, my first thought was "why in the world would Uber do that?" In every single performance tuning a scale story that I've read over the past decade, the very first point of order is: remove joins from high traffic queries. It seems like Uber has gone the complete opposite direction.
- gdulli 10y ago
  You can shape yourself to fit a problem or you can shape a problem to fit you. Wisdom is knowing when to do which.
- emn13 10y ago
  It's unrealistic to remove all joins (usually), and most joins aren't very expensive. In normal cases a join is strictly faster than running an extra query to get that extra data. Joins start getting particularly tricky when you do expensive stuff like filter over the correlated results of many tables. But in those cases, there's also no easy alternative: you'll need to redesign the way you store your data, if possible.
- angelbob 10y ago
  Sometimes this is very hard to do. This is in effect Twitter's core use case as well, with the list of accounts you follow. And it's a hard problem. Twitter spawned a huge wave of NoSQL to deal with this, and it's still not really dealt with.
- MustardTiger 10y ago
  But that is largely incorrect advice, given based on the fact that mysql never implemented any reasonable join algorithms, just nested loops. It didn't apply to real databases.
merb 10y ago
Actually after watching their video. I think that even MySQL wouldn't helped them. Ignoring disk space full is really not a good idea.
solarengineer 10y ago
Here's the entire thread: https://www.postgresql.org/message-id/flat/7663dfec-e46a-401b-50a9-a1a02c53d9e6%402ndquadrant.fr#7663dfec-e46a-401b-50a9-a1a02c53d9e6@2ndquadrant.fr https://www.postgresql.org/message-id/flat/7663dfec-e46a-401...
landmark3 10y ago
one other reason is that postgresql.org forum comes straight from 1984
- mixedCase 10y ago
  Yeah we should switch everything to Mongo DB. They have a pretty website after all.
  - boubiyeah 10y ago
    Almost as importantly, it is web scale and thus also uber scale.
BilalBudhani 10y ago
A huge respect for PostgreSQL team on (always) being so transparent about their shortcomings and giving credits where its due (InnoDB). Posts like these makes me more confident on PostgreSQL as a product. Also I'd like to take this moment and address those people, who will start rubbing this "Uber switched to MySQL from PostgreSQL" argument without considering that not every app is "Uber". You can't simply take their use case and start throwing stuff against PostgreSQL.
curiousgal 10y ago
Are there any recommended resources to extensively learn about databases?
EugeneOZ 10y ago
Initial email is even more impressive - huge respect for this style.
dumbfounder 10y ago
Sounds like postgres needs to support pluggable storage engines. I am sure Uber (and many others) would have paid some license fee to someone who developed a storage engine that fixed this use case. Think of how much it cost them to switch...
- spotman 10y ago
  Yea would be amazing to see an innodb backend for PG! ( I'll let myself out ;)
  - spacehunt 10y ago
    There _is_ a Foreign Data Wrapper that wraps MySQL[1]. Not exactly the same thing though, and no idea how well it works. [1] https://github.com/EnterpriseDB/mysql_fdw https://github.com/EnterpriseDB/mysql_fdw
    - dumbfounder 10y ago
      I can tell you it doesn't work very well for wrapping remote postgres tables... it doesn't even send the where clause to the remote server, instead does a table scan across the network and matches locally.
      - zejn 10y ago
        Depends which Postgres version you are using: 9.1 added read only FDW support, 9.3 made it writable, 9.5 also has join pushdown into remote table. Obviously, a lot depends on the FDW extension you are using - it doesn't help if there is support in postgres, if your specific extension does not utilize it.
tombert 10y ago
I think it's very cool that Postgres didn't just post some long thing about Uber saying "they're using it wrong omg!!!" and instead address the problems, understand their decision, and move on. I think that's very cool, there's clearly a good team working on this DB.
chewbacha 10y ago
Yea, damn that cache invalidation!!!
- tjsousa 10y ago
  I sense an off by one error in this sentence...
  - brianwawok 10y ago
    No it's premature optimization.
    - ben_jones 10y ago
      I've never been accused of premature optimization
- sctb 10y ago
  We detached this subthread from https://news.ycombinator.com/item?id=12201894 https://news.ycombinator.com/item?id=12201894 and marked it off-topic.
jimktrains2 10y ago
Here is the original message in the thread, and it gives some additional reasons as well. https://www.postgresql.org/message-id/579795DF.10502%40commandprompt.com https://www.postgresql.org/message-id/579795DF.10502%40comma...
scotty79 10y ago
That's the mature way to react to reality. Vacuuming can be a problem for users.
geophile 10y ago
Question about Postgres architecture: Why were secondary indexes designed to refer to ctids instead of primary keys?
- wiredfool 10y ago
  There's no concept of a secondary index. An index is an index. Some are unique indexes, which force each value to be unique, some are not. Primary Key is essentially syntactic sugar for not null and a unique index.
- Jweb_Guru 10y ago
  Among other things, there's a substantial performance penalty for secondary index lookups with clustered indices (since they need to traverse two index structures).
  - GrayShade 10y ago
    I'm not sure about the other databases, but in MS SQL you can include columns in the index leaves. This mitigates the need for the second lookup and can be even faster than the Postgres approach.
    - Jweb_Guru 10y ago
      Keep in mind that the complaint Uber had was with writes in Postgres affecting secondary indices that didn't cover a particular column. As far as I know, in all databases, if a covered column is modified the secondary index must be, too. So that does not really resolve Uber's problem. Additionally, I can't quite recall whether this is the case in MS SQL (since it uses pessimistic locking by default, not snapshot isolation, which has different performance characteristics), but in most MVCC architectures there's the additional problem that the underlying row value could have been changed concurrently with your query (e.g., deleted or modified). While this might not seem so bad for simple row-level lookups, this gets much more problematic if you are doing something like a range query, where the index might contain rows that weren't in the database at the beginning of your snapshot. There are a variety of ways of dealing with that problem, but most of them involve increased write traffic (index sizes get even more bloated because now they need versioning information), increased read traffic (index reads that touch out-of-date rows may have to follow an undo pointer, requiring the extra seeks you were trying to avoid in the first place), more locking (for instance, you could lock the entire index when a transaction modified it to keep it consistent--but that would decrease concurrency--or you could try to do range locking--which can lead to increased probability of deadlocks and also decreases concurrency and increases contention on the lock manager), opportunistic optimizations that only work on mostly-immutable data (Postgres and HyPer's solutions is to maintain a much smaller visibility map with bits indicating whether it's safe to assume the index is unchanged), or giving up multi-key read consistency (I'm assuming if this were an option you would be using another storage engine, because lots of them can perform unbelievably well if that restriction is relaxed!). My point being, there's no such thing as a free lunch. Personally, I'm usually extremely happy to give up on pessimistic locking for the concurrency benefits of MVCC, and index utility decreases sharply as it gets larger, but as with many other things it entirely depends on your workload. Two-phase locking actually works far better than MVCC of any sort under heavy contention with short transactions (what Uber is apparently doing), so they really probably should have investigated SQL Server or another database optimized for pessimistic concurrency control.
ythl 10y ago
pg-13sequel
- Finnucane 10y ago
  Adult supervision required?
  - _RPM 10y ago
    Reddit styles puns aren't allowed here, that's why you're getting downvoted.
    - deleted 10y ago
      [deleted]
    - spacecop 10y ago
      what an enlightened community of robot people
      - cdelsolar 10y ago
        I upvoted the puns, don't worry.
      - gpvos 10y ago
        You misspelt "people who like intelligent discussion". A joke now and then is okay, but try to give it enough redeeming value. (And for the rest, don't worry too much about downvotes.)
      - brazzledazzle 10y ago
        I don't think it's that at all. Pun threads generally make for very boring reading/discussion and tend to rise to the top because they're quick to digest, uncontroversial and mildly amusing. If you're looking for more substantial comments (arguably most HN users are) these can be a real chore to slog through.
    - brazzledazzle 10y ago
MadWombat 10y ago
I see some comments call this a "very specific user case". This is not. Pretty much every major web project is going to have tables like that. User sessions are just one example. Sure, you can design around this, but it is a problem and no design is going to make it completely go away.
- fleetfox 10y ago
  Storing sessions in rdbms is a rather poor choice espesially at scale.
  - encoderer 10y ago
    On the other hand, sessions are important and if you're only using redis/etc for caching and not as a primary data store, it may not be a very good idea to treat it as a primary store of session data. Sessions are vital for any product that works better with logged-in users.
- MustardTiger 10y ago
  User sessions is not an example of that, why would your user session have lots of indexes? And sessions don't belong in a database to begin with. I've never made a table like this in 15 years, and can't even think of a reason I would want to.
lcfcjs 10y ago
Oh what a shame, I wonder who'll miss the other most? An industry-changing multi-billion dollar company, with a massive user base. Or an open source database company? Postgres sucks anyways, mongo is much better.
methehack 10y ago
Does anyone else think the scenario in the explanation is an unreasonable request to make of a relational database? I think that if you've created a design that requires you to update a 50K row table 500 times a second that itself is heavily indexed and used heavily in joins, you have a software design problem more than a database problem. I wouldn't expect any database to handle that and am surprised that mysql does. One has to ask: for how long will it work? Surely the clock is running out on such a design.
- angelbob 10y ago
  You're not wrong. But mostly we haven't collectively agreed that relational databases aren't great for highly-indexed rapid-update join tables. I think we will at some point. That's the primary original use case for a lot of NoSQL, and the reason Twitter had so much trouble with relational databases. But these are cultural understandings, and those move slowly. Also, we're poorly (collectively) equipped to handle subtlety in these discussions, so mostly we're trying to move from "relational databases are perfect for all use cases" to "NoSQL databases are perfect for all use cases" -- which is even less true, not more true. Culturally, this is a hard thing to keep in our collective brain.
  - tomcam 10y ago
    That was an elegantly nuanced answer with just the right amount of context. Thank you.
  - duaneb 10y ago
    How has NoSQL addressed join tables? All the approaches I've seen are much, much slower than with a traditionally vertically scaled relational database—or by moving away from joining altogether—it's traditionally been the twin pressures of scale and replication that force people to move to a distributed database.
    - bind 10y ago
      NoSQL doesn't do table joins.
cobbzilla 10y ago
great explanation by the Postgres crew. it makes me wonder, though: if I had a table with 50k rows, updated hundreds of times per second and used in joins throughout the database, is there any way I can just stick that whole table into memcached or redis? I know there are some cases where this works, some where it doesn't. curious if this option was explored.
- lemcoe9 10y ago
  Is this not what Redis was designed for? In this situation, I would value Redis over memcached just based on its performance values and the cheapness of RAM in the cloud right now.
- bsaul 10y ago
  Completely agreee. Highly mutable => in memory ( then add a secondary stream for colder storage backups just in case). A relational db is originaly meant for fetching data from a slow storage to a fast one, and the other way around, in a smart way. Doing it 500 times a second isn't a scenario for any db. Build your own in memory data + process tructure, maybe using something like and agent network and using eg akka or erlang for failovers. I'm curious as to why uber had to rely on a relational db for this case...
  - tylermauthe 10y ago
    It's often simpler to keep data in a relational DB for reporting, especially if you've got data lake tooling -- it's just easier to get wider insights if your data is stored relationally. I know it's possible to do this without relational DB, but you get it for free with SQL. As you say though, put it in memory first and write it out to a DB every now and then.
- qopp 10y ago
  Just to keep it in memory? Postgres does this already-- it will put tables or parts of tables in memory to speed up reads [1]. [1] Source: https://momjian.us/main/writings/pgsql/hw_performance/ https://momjian.us/main/writings/pgsql/hw_performance/
jrochkind1 10y ago
Refreshing alternative to many posts saying "It wasn't really about postgres at all"
eric_the_read 10y ago
This might explain a lot of the performance problems we've seen trying to use Postgresql as an event store, dumping some 10s of millions of rows/day into a table that has a few indexes (no foreign keys, but trying to speed up queries). Sounds like it's time to investigate alternatives.
- wmfiv 10y ago
  I would think it's unrelated. The issue described by Uber and the Postgres team is specifically related to UPDATEs against highly indexed tables. In an event store the data is typically completely immutable.
Myrmornis 10y ago
It would be more helpful if this linked to the entire thread: https://www.postgresql.org/message-id/flat/7663dfec-e46a-401b-50a9-a1a02c53d9e6%402ndquadrant.fr https://www.postgresql.org/message-id/flat/7663dfec-e46a-401...
combatentropy 10y ago
This later comment in the thread (https://www.postgresql.org/message-id/flat/7663dfec-e46a-401b-50a9-a1a02c53d9e6%402ndquadrant.fr https://www.postgresql.org/message-id/flat/7663dfec-e46a-401...), by Merlin Moncure, describes a set-up that most applications have, and I agree with his assessment of it: Taking a step back, from the outside, it looks like uber: *) has a very thick middleware, very thin database with respect to logic and complexity *) has a very high priority on quick and cheap (in terms of bandwidth) replication *) has decided the database needs to be interchangeable *) is not afraid to make weak or erroneous technical justifications as a basis of stack selection (the futex vs ipc argument I felt was particularly awful -- it ignored the fact we use spinlocks) The very fact that they swapped it out so easily suggests that they were not utilizing the database as they could have, and a different technical team might have come to a different result. Postgres is a very general system and rewards deep knowledge such that it can outperform even specialty systems in the hands of a capable developer . . . It's all the rage to use an object-relational wrapper to abstract away the database brand, so that the database can be "interchangeable." In my 11 years experience with web apps and databases, particularly Postgres, this is a false economy: - When you use an object-relational wrapper, you are trading one kind of lock-in for another. Instead of locking yourself into Postgres, you are locking yourself into PHP. That seems a good trade to most developers, who know their middleware language better than SQL. - However I have found that moving your application code from the middle layer, to SQL, results in shorter code and much faster execution. I don't mean moving the Python code into Postgres functions written in PL/Python (which you can do). I mean porting them to SQL. The simplest example is if your first version of your application just used SQL to fetch all the rows from the database and using a Python if-statement to find the rows you need: rewriting the Python if-statement as an SQL where-clause will be much faster. I'm sure few of you are doing something like that, but there are many, more complex examples like that, which I have learned and slowly replaced over the years. - Furthermore SQL is just like any other language in that there are many ways to do the same thing, and some ways are a thousand times faster than others. People who only know basic SQL likely are writing inefficient apps. I have often rewritten queries to use a fraction of the memory and time they were using. Moral of the story: Learn more SQL, instead of learning some avant-garde storage engine. Stepping deeper into PostgreSQL (and thereby locking yourself more and more into it) is often better than locking yourself deeper into Python, PHP, or whatever your middle language is. Who knows, you may want to switch out your middle language before you want to switch out Postgres! I try to make Postgres my "application": put all your business logic in there. It may have an arcane "user-input interface" (SELECT . . . FROM sometable WHERE . . .) and a primitive "user-output interface" (plain tables) but that's where the middleware finally comes in, to cover the queries with checkboxes and buttons, and to decorate the output into various boxes, color, layout, and type, and maybe some nice images, graphs, and so on.
neves 10y ago
I've already read 2 very long about this issue, but this very succinct text is really way better than any of others. The best computer programming article I've read in months.
systematical 10y ago
If only politicians gave answers like that, country would be a better place. Kudos for the answer. I use both MySQL and Postgresql depending on my use case.
bind 10y ago
Tom Lane's response: this seems like an annoyance, not a time-for-a-new-database kind of problem. https://www.postgresql.org/messageid/13659.1469570853%40sss.pgh.pa.us https://www.postgresql.org/messageid/13659.1469570853%40sss....
zhangela 10y ago
What does VACUUM, SNAPSHOT TOO OLD, HOT mean?
machine-wisdom 10y ago
The whole discussion (this and [1] and [2]) is very interesting. I find it that Uber is holding on the ACID guarantees of a relational DB so much, in a way that is clearly hurting them. IMHO it will do them a big service to break down the responsibility of such a DB into multiple distributed systems that can work on a global scale. For example, a distributed lock system can help them when they need transactions. If they keep moving from one relational DB to another, they are bound to hit problems of Availability because they are choosing very strong Consistency, and the CAP theorem says we can't have it all (Partition tolerance is required). [1] https://news.ycombinator.com/item?id=12166585 https://news.ycombinator.com/item?id=12166585 [2] https://news.ycombinator.com/item?id=12179222 https://news.ycombinator.com/item?id=12179222

1234 points by areski 10y ago

I am guessing that VACUUM only kicks in for row deletions? With that busy a table, can't you just bypass VACUUM and reuse dead keys? I am assuming because you are talking indexes here, you are talking fixed length records and not dynamically allocated VARCHARs etc.??

cesarb 10y ago
Postgres uses MVCC, which is basically copy-on-write: every UPDATE to a row actually creates a new row. Once all transactions that could see the old row have finished, the old row can be pruned.
- cyberferret 10y ago
  Ah, thanks for the clarification - the bloat issue makes more sense now.
  - deleted 10y ago
    [deleted]

cemerick 10y ago

This only talks about a particular write amplification issue. A far better message IMO is just a few clicks upthread: https://www.postgresql.org/message-id/579795DF.10502%40commandprompt.com https://www.postgresql.org/message-id/579795DF.10502%40comma... Provides a link to the rationale post from uber (https://eng.uber.com/mysql-migration/ https://eng.uber.com/mysql-migration/), and a tl;dr of it. Prior discussion here: https://news.ycombinator.com/item?id=12166585 https://news.ycombinator.com/item?id=12166585

dspillett 10y ago
It is nice to see that while the thread does question some of Uber's motivation for the change, where there is a genuine problem with their product there is a frank admission (with quotes like "this is a common problem case we don't have an answer for yet" and "limitations of our current replication system are real, or we wouldn't have so many people working on alternatives") which people discuss seriously (asking for clarification and/or suggesting ways forward) rather than reacting with a knee-jerk defensive posture.
MustardTiger 10y ago
Yes, and an even better message is the reply to it, pointing out how almost all of their issues aren't really issues: https://www.postgresql.org/message-id/20160727002711.GI4028%40tamriel.snowman.net https://www.postgresql.org/message-id/20160727002711.GI4028%...

gnur 10y ago

Wow, nice explanation. I was expecting a lengthy post with refuting everything uber said and explaining why pgsql was a better choice and uber was wrong. Nice and clean post showing that acknowledging a weakness isn't a terrible choice. I do wonder whether a different data structure would have mitigated the issue instead of transitioning to a different storage engine.

collyw 10y ago
Yes, I have a lot more respect for this than say MongoDb which claims to be great at everything.
- spriggan3 10y ago
  > MongoDb which claims to be great at everything lol right, with MongoDb, Map Reduce is a joke, GridFS is slow and barely usable, the storage is extremely inefficient, the "query engine" slow, and don't get me started on their "full text search" engine. MongoDb is a successful marketing stunt in the "Nodejs era".
  - oneloop 10y ago
    Also, wasn't there a ridiculous issue that they had where the db can't be bigger than 4gb on a 32 bit file system because that's the largest size a file can have...?
    - thom_nic 10y ago
      Yes, apparently the limit on 32-bit is 2GB actually. MongoDB has always stated upfront that 32-bit architectures are not recommended for production use for precisely this reason: http://blog.mongodb.org/post/137788967/32-bit-limitations http://blog.mongodb.org/post/137788967/32-bit-limitations This has also has been stated on their download page for 32-bit binaries as well.
      - StavrosK 10y ago

jjp 10y ago

The original Uber blog post that led to the discussion - https://eng.uber.com/mysql-migration/ https://eng.uber.com/mysql-migration/

jhh 10y ago

The attitude with which the article is discussed in the mailing list is admirable.

coldtea 10y ago
Couldn't help contrasting it to some popular programming language mailing lists, especially a CSP inspired language by a major search company.
- anamoulous 10y ago
  Couldn't help contrasting it to the comments on this very site.
- eternalban 10y ago
  In contrast, there is the community of a "useless language" (to quote one of its leading lights) that has never adopted [the] acceptance of paternalism as a requirement of espirit de corps. imho some of the glaring shortcomings of the technology you refer to can be traced back to the very issue that you are highlighting.

grandalf 10y ago

How well will INNODB scale for this particular use case? It seems that the architecture is a bit too reliant on the complex performance characteristics of one subsystem.

matthewmacleod 10y ago

The PoststgreSQL project never fails to impress me. I know that there are some use cases which are not currently covered by it versus alternatives, but I have consistently got the feeling that everybody involved in the project is supremely professional and interested in building an excellent database – and the focus is on how to work to fix these use cases, instead of pointless mudslinging. Class act.

babuskov 10y ago
> The PoststgreSQL project never fails to impress me. Except the name is a bit clunky and hard to write ;)
- scardine 10y ago
  If only a clunky name was the worst problem with some software (even closed source) we have to deal with.
  - Grishnakh 10y ago
    Slightly clunky names are fine as long as they're somewhat cute or clever. Names are important because it's how we identify and remember things, and a catchy name (even if it seems a little ridiculous or clunky) is better than a boring, banal name if the catchy name is memorable. I'll take PostgreSQL's name (which I consider clever) any day over some name completely devoid of originality or thoughtfulness, such as most of the names Microsoft uses for its software.
    - kbenson 10y ago
      Yes, at least it's searchable. The current trend of using common words as names was ridiculous as of a few years ago. Now it's just painful. Someone's going to name their project "The" and I'm not sure I'll be able to remain civil at that point.
      - teddyh 10y ago

erikb 10y ago

I agree it's a nice thing. But some kind of answer may also be good. Like restructuring your data to become faster. I also wonder what happened the last few (10) years. When I was in university I'm pretty sure I learned that JOIN was Satan's mother and if you have a big DB you need to avoid JOINs as much as possible. That's not a big deal today anymore, it seems.

mnw21cam 10y ago
That kind of thinking is probably what spawned the whole "do the join in the app, not the database" anti-pattern. The truth is, the database is going to be much faster at performing a join than loading the contents of two tables into your app and iterating. If you need the data that results from doing a join, doing a join is the best way to get it. Unless you already have your entire database in-memory in your app, that is. In that case, why do you have a database?
- dagss 10y ago
  What you are missing here is "denormalization" -- e.g. many-to-many relationships. You can either use a JOIN with a table on a "normalized" database, or keep managing the result of the join in application code. Loading the entire tables into application code very seldom has anything to do with it... A more realistic example is, do you get Alice's pets by doing a JOIN on tables Person, Pet, PetOwnedByPerson ("SQL") -- or by having an array column "pets" in Person? ("NoSQL")
  - erikb 10y ago
    Yes "denormalization" was the word for that. Is that still a thing?
    - lostcolony 10y ago
      It's a term used frequently in NoSQL land, to explain a key difference to people coming from SQL. In SQL land, normalizing your data is still the canonical thing to do, and I don't recall anyone in academia officially talking about denormalizing ever having its place...but in industry, the realities of use cases and performance have meant its usage. But I don't know that it's a standard tool given out to graduating software devs and DBAs.

ZanyProgrammer 10y ago

Its weird seeing a post mortem for losing a user (I really want to say customer) from a piece of FOSS. Its also weird (still!) to consider Uber a tech company, rather than a company that happens to use tech.

menzoic 10y ago
Just curious, what's your definition of "Tech Company"? All services provided by Uber are purely technical. Drivers and Riders are customers of Uber's technology. The full name of the company is "Uber Technologies Inc."
- ZanyProgrammer 10y ago
  Well if that's what their name says it must be true!
- vmateixeira 10y ago
  Acording to UK's Companies House search, Uber's nature of business (SIC) is 74990 - Non-trading company. This is quite vague but it doesn't mention technology. Interestingly, they were also called UBER TECHNOLOGIES LTD and UBERTECHNOLOGY LIMITED at some point in time, but these companies are now dissolved.
  - HappyTypist 10y ago
    That's because Uber UK is a pure support office, at least that's what their lawywers and accountants argue. The real company in Luxembourg.
    - caoilte 10y ago
      If that were true the nature of their company would be 'tax dodge'
- rplst8 10y ago
  I wasn't sure what to think of that comment either when I first read it, but I sort of see where he is coming from. One side you have companies like Oracle, Microsoft, and IBM types that actually develop new forms of technology and sell the technology to people. Then there are companies that leverage technology in other industries to "disrupt" like OpenTable, Uber, and AirBnB. Then there are companies like Google and Facebook that straddle that line. They've developed and contributed back huge advances in technology, they mostly make their money from another industry, but also sell some tech in certain areas (mostly Google).

cel1ne 10y ago

> "In some ways, people worry about the bugs they have seen, not the bugs they haven't seen." [0] This is a key take-away for me. I used mySQL extensively and switched to postgres for everything years back. I would need extremely good reasons to use mySQL again. What you get with postgres is a lot more consistency and peace-of-mind, in my opinion. [0] https://www.postgresql.org/message-id/20160727160408.GA23585%40momjian.us https://www.postgresql.org/message-id/20160727160408.GA23585...

mrfusion 10y ago

I don't understand if they mean that foreign key relationships are contributing to the problem. If so is it possible to turn off foreign key constaints during a big load? Would it be worth removing indexes during a big load? (Currently fighting with an etl that can't get above 500 rows / second even after using copy from)

richardwhiuk 10y ago
The reason the indexes are their is because the table is involved in multiple JOINs, which without the indexes are very slow. I suspect it's a straight index, not a foreign key constraint.
barrkel 10y ago
All indexes on a pg table need updating if any value in the row is modified, that's the write amplification. The indexes are needed for joins, they're not so important for foreign keys. Certainly checking foreign key constraints works better with indexes, but those are usually the other way around - verifying row exists with primary key, ie only one index. Updates to primary key would benefit from index on foreign key columns, but that's much rarer.
lmm 10y ago
Indices trade reads for writes (there is no free lunch); if you index every column of your database you'll be able to do any read query reasonably fast but all writes will be slow, if you index nothing then writes will be fast but every read will be a full table scan. Presumably they have their indices because they need them for their read queries.
xfalcox 10y ago
Dropping constraints when doing ETL is common. You can create then after and rollback if problems arise.

rimantas 10y ago

but have you used MySQL? Maybe I am too nitpicky, but I always get suspicious when people make claims about technology whose name they cannot spell right.

babuskov 10y ago
He also spelled Postgres "postgres" ;)
LeifCarrotson 10y ago
In this case, Fabian's CV (linked from his GitHub profile, linked from his HN profile) does claim MySQL experience - correctly capitalized. But yes, I also get suspicious when people can't spell a technology correctly. For example, anyone who has spent time reading some documentation will know that Lua is capitalized in title case, and not written LUA.
- lonelycoder2 10y ago
  Yes this, it's Xcode not xCode people
- ZoFreX 10y ago
  Really? I will claim you will not meet many engineers who know more about Tcl than I do, and I can't remember whether it's TCL or Tcl. The absence of that knowledge has not impeded me from editing the Tcl interpreter itself. I am not convinced such pedantic measure of spelling is an accurate predictor of knowledge, to me it sounds more like elitism.
  - ngcazz 10y ago
    Table manners, itsy-bitsy rules for itsy-bitsy people as Robert Pirsig put it.
    - zeveb 10y ago
      Or, table stakes to distinguish folks worth spending time on & folks not worth spending time on. Since it's so easy to get the little things right, anyone who doesn't is just a bit suspect. It's like discarding resumés which arrive with large food stains on them. It's just too easy to print off a clean copy, that anyone who doesn't must be a bit off.

brightball 10y ago

In reading that, my first thought was "why in the world would Uber do that?" In every single performance tuning a scale story that I've read over the past decade, the very first point of order is: remove joins from high traffic queries. It seems like Uber has gone the complete opposite direction.

gdulli 10y ago
You can shape yourself to fit a problem or you can shape a problem to fit you. Wisdom is knowing when to do which.
emn13 10y ago
It's unrealistic to remove all joins (usually), and most joins aren't very expensive. In normal cases a join is strictly faster than running an extra query to get that extra data. Joins start getting particularly tricky when you do expensive stuff like filter over the correlated results of many tables. But in those cases, there's also no easy alternative: you'll need to redesign the way you store your data, if possible.
angelbob 10y ago
Sometimes this is very hard to do. This is in effect Twitter's core use case as well, with the list of accounts you follow. And it's a hard problem. Twitter spawned a huge wave of NoSQL to deal with this, and it's still not really dealt with.
MustardTiger 10y ago
But that is largely incorrect advice, given based on the fact that mysql never implemented any reasonable join algorithms, just nested loops. It didn't apply to real databases.

merb 10y ago

Actually after watching their video. I think that even MySQL wouldn't helped them. Ignoring disk space full is really not a good idea.

solarengineer 10y ago

Here's the entire thread: https://www.postgresql.org/message-id/flat/7663dfec-e46a-401b-50a9-a1a02c53d9e6%402ndquadrant.fr#7663dfec-e46a-401b-50a9-a1a02c53d9e6@2ndquadrant.fr https://www.postgresql.org/message-id/flat/7663dfec-e46a-401...

landmark3 10y ago

one other reason is that postgresql.org forum comes straight from 1984

mixedCase 10y ago
Yeah we should switch everything to Mongo DB. They have a pretty website after all.
- boubiyeah 10y ago
  Almost as importantly, it is web scale and thus also uber scale.

BilalBudhani 10y ago

A huge respect for PostgreSQL team on (always) being so transparent about their shortcomings and giving credits where its due (InnoDB). Posts like these makes me more confident on PostgreSQL as a product. Also I'd like to take this moment and address those people, who will start rubbing this "Uber switched to MySQL from PostgreSQL" argument without considering that not every app is "Uber". You can't simply take their use case and start throwing stuff against PostgreSQL.

curiousgal 10y ago

Are there any recommended resources to extensively learn about databases?

EugeneOZ 10y ago

Initial email is even more impressive - huge respect for this style.

dumbfounder 10y ago

Sounds like postgres needs to support pluggable storage engines. I am sure Uber (and many others) would have paid some license fee to someone who developed a storage engine that fixed this use case. Think of how much it cost them to switch...

spotman 10y ago
Yea would be amazing to see an innodb backend for PG! ( I'll let myself out ;)
- spacehunt 10y ago
  There _is_ a Foreign Data Wrapper that wraps MySQL[1]. Not exactly the same thing though, and no idea how well it works. [1] https://github.com/EnterpriseDB/mysql_fdw https://github.com/EnterpriseDB/mysql_fdw
  - dumbfounder 10y ago
    I can tell you it doesn't work very well for wrapping remote postgres tables... it doesn't even send the where clause to the remote server, instead does a table scan across the network and matches locally.
    - zejn 10y ago
      Depends which Postgres version you are using: 9.1 added read only FDW support, 9.3 made it writable, 9.5 also has join pushdown into remote table. Obviously, a lot depends on the FDW extension you are using - it doesn't help if there is support in postgres, if your specific extension does not utilize it.

tombert 10y ago

I think it's very cool that Postgres didn't just post some long thing about Uber saying "they're using it wrong omg!!!" and instead address the problems, understand their decision, and move on. I think that's very cool, there's clearly a good team working on this DB.

chewbacha 10y ago

Yea, damn that cache invalidation!!!

tjsousa 10y ago
I sense an off by one error in this sentence...
- brianwawok 10y ago
  No it's premature optimization.
  - ben_jones 10y ago
    I've never been accused of premature optimization
sctb 10y ago
We detached this subthread from https://news.ycombinator.com/item?id=12201894 https://news.ycombinator.com/item?id=12201894 and marked it off-topic.

jimktrains2 10y ago

Here is the original message in the thread, and it gives some additional reasons as well. https://www.postgresql.org/message-id/579795DF.10502%40commandprompt.com https://www.postgresql.org/message-id/579795DF.10502%40comma...

scotty79 10y ago

That's the mature way to react to reality. Vacuuming can be a problem for users.

geophile 10y ago

Question about Postgres architecture: Why were secondary indexes designed to refer to ctids instead of primary keys?

wiredfool 10y ago
There's no concept of a secondary index. An index is an index. Some are unique indexes, which force each value to be unique, some are not. Primary Key is essentially syntactic sugar for not null and a unique index.
Jweb_Guru 10y ago
Among other things, there's a substantial performance penalty for secondary index lookups with clustered indices (since they need to traverse two index structures).
- GrayShade 10y ago
  I'm not sure about the other databases, but in MS SQL you can include columns in the index leaves. This mitigates the need for the second lookup and can be even faster than the Postgres approach.
  - Jweb_Guru 10y ago
    Keep in mind that the complaint Uber had was with writes in Postgres affecting secondary indices that didn't cover a particular column. As far as I know, in all databases, if a covered column is modified the secondary index must be, too. So that does not really resolve Uber's problem. Additionally, I can't quite recall whether this is the case in MS SQL (since it uses pessimistic locking by default, not snapshot isolation, which has different performance characteristics), but in most MVCC architectures there's the additional problem that the underlying row value could have been changed concurrently with your query (e.g., deleted or modified). While this might not seem so bad for simple row-level lookups, this gets much more problematic if you are doing something like a range query, where the index might contain rows that weren't in the database at the beginning of your snapshot. There are a variety of ways of dealing with that problem, but most of them involve increased write traffic (index sizes get even more bloated because now they need versioning information), increased read traffic (index reads that touch out-of-date rows may have to follow an undo pointer, requiring the extra seeks you were trying to avoid in the first place), more locking (for instance, you could lock the entire index when a transaction modified it to keep it consistent--but that would decrease concurrency--or you could try to do range locking--which can lead to increased probability of deadlocks and also decreases concurrency and increases contention on the lock manager), opportunistic optimizations that only work on mostly-immutable data (Postgres and HyPer's solutions is to maintain a much smaller visibility map with bits indicating whether it's safe to assume the index is unchanged), or giving up multi-key read consistency (I'm assuming if this were an option you would be using another storage engine, because lots of them can perform unbelievably well if that restriction is relaxed!). My point being, there's no such thing as a free lunch. Personally, I'm usually extremely happy to give up on pessimistic locking for the concurrency benefits of MVCC, and index utility decreases sharply as it gets larger, but as with many other things it entirely depends on your workload. Two-phase locking actually works far better than MVCC of any sort under heavy contention with short transactions (what Uber is apparently doing), so they really probably should have investigated SQL Server or another database optimized for pessimistic concurrency control.

ythl 10y ago

pg-13sequel

Finnucane 10y ago
Adult supervision required?
- _RPM 10y ago
  Reddit styles puns aren't allowed here, that's why you're getting downvoted.
  - deleted 10y ago
    [deleted]
  - spacecop 10y ago
    what an enlightened community of robot people
    - cdelsolar 10y ago
      I upvoted the puns, don't worry.
    - gpvos 10y ago
      You misspelt "people who like intelligent discussion". A joke now and then is okay, but try to give it enough redeeming value. (And for the rest, don't worry too much about downvotes.)
    - brazzledazzle 10y ago
      I don't think it's that at all. Pun threads generally make for very boring reading/discussion and tend to rise to the top because they're quick to digest, uncontroversial and mildly amusing. If you're looking for more substantial comments (arguably most HN users are) these can be a real chore to slog through.
  - brazzledazzle 10y ago

MadWombat 10y ago

I see some comments call this a "very specific user case". This is not. Pretty much every major web project is going to have tables like that. User sessions are just one example. Sure, you can design around this, but it is a problem and no design is going to make it completely go away.

fleetfox 10y ago
Storing sessions in rdbms is a rather poor choice espesially at scale.
- encoderer 10y ago
  On the other hand, sessions are important and if you're only using redis/etc for caching and not as a primary data store, it may not be a very good idea to treat it as a primary store of session data. Sessions are vital for any product that works better with logged-in users.
MustardTiger 10y ago
User sessions is not an example of that, why would your user session have lots of indexes? And sessions don't belong in a database to begin with. I've never made a table like this in 15 years, and can't even think of a reason I would want to.

lcfcjs 10y ago

Oh what a shame, I wonder who'll miss the other most? An industry-changing multi-billion dollar company, with a massive user base. Or an open source database company? Postgres sucks anyways, mongo is much better.

methehack 10y ago

Does anyone else think the scenario in the explanation is an unreasonable request to make of a relational database? I think that if you've created a design that requires you to update a 50K row table 500 times a second that itself is heavily indexed and used heavily in joins, you have a software design problem more than a database problem. I wouldn't expect any database to handle that and am surprised that mysql does. One has to ask: for how long will it work? Surely the clock is running out on such a design.

angelbob 10y ago
You're not wrong. But mostly we haven't collectively agreed that relational databases aren't great for highly-indexed rapid-update join tables. I think we will at some point. That's the primary original use case for a lot of NoSQL, and the reason Twitter had so much trouble with relational databases. But these are cultural understandings, and those move slowly. Also, we're poorly (collectively) equipped to handle subtlety in these discussions, so mostly we're trying to move from "relational databases are perfect for all use cases" to "NoSQL databases are perfect for all use cases" -- which is even less true, not more true. Culturally, this is a hard thing to keep in our collective brain.
- tomcam 10y ago
  That was an elegantly nuanced answer with just the right amount of context. Thank you.
- duaneb 10y ago
  How has NoSQL addressed join tables? All the approaches I've seen are much, much slower than with a traditionally vertically scaled relational database—or by moving away from joining altogether—it's traditionally been the twin pressures of scale and replication that force people to move to a distributed database.
  - bind 10y ago
    NoSQL doesn't do table joins.

cobbzilla 10y ago

great explanation by the Postgres crew. it makes me wonder, though: if I had a table with 50k rows, updated hundreds of times per second and used in joins throughout the database, is there any way I can just stick that whole table into memcached or redis? I know there are some cases where this works, some where it doesn't. curious if this option was explored.

lemcoe9 10y ago
Is this not what Redis was designed for? In this situation, I would value Redis over memcached just based on its performance values and the cheapness of RAM in the cloud right now.
bsaul 10y ago
Completely agreee. Highly mutable => in memory ( then add a secondary stream for colder storage backups just in case). A relational db is originaly meant for fetching data from a slow storage to a fast one, and the other way around, in a smart way. Doing it 500 times a second isn't a scenario for any db. Build your own in memory data + process tructure, maybe using something like and agent network and using eg akka or erlang for failovers. I'm curious as to why uber had to rely on a relational db for this case...
- tylermauthe 10y ago
  It's often simpler to keep data in a relational DB for reporting, especially if you've got data lake tooling -- it's just easier to get wider insights if your data is stored relationally. I know it's possible to do this without relational DB, but you get it for free with SQL. As you say though, put it in memory first and write it out to a DB every now and then.
qopp 10y ago
Just to keep it in memory? Postgres does this already-- it will put tables or parts of tables in memory to speed up reads [1]. [1] Source: https://momjian.us/main/writings/pgsql/hw_performance/ https://momjian.us/main/writings/pgsql/hw_performance/

jrochkind1 10y ago

Refreshing alternative to many posts saying "It wasn't really about postgres at all"

eric_the_read 10y ago

This might explain a lot of the performance problems we've seen trying to use Postgresql as an event store, dumping some 10s of millions of rows/day into a table that has a few indexes (no foreign keys, but trying to speed up queries). Sounds like it's time to investigate alternatives.

wmfiv 10y ago
I would think it's unrelated. The issue described by Uber and the Postgres team is specifically related to UPDATEs against highly indexed tables. In an event store the data is typically completely immutable.

Myrmornis 10y ago

It would be more helpful if this linked to the entire thread: https://www.postgresql.org/message-id/flat/7663dfec-e46a-401b-50a9-a1a02c53d9e6%402ndquadrant.fr https://www.postgresql.org/message-id/flat/7663dfec-e46a-401...

combatentropy 10y ago

This later comment in the thread (https://www.postgresql.org/message-id/flat/7663dfec-e46a-401b-50a9-a1a02c53d9e6%402ndquadrant.fr https://www.postgresql.org/message-id/flat/7663dfec-e46a-401...), by Merlin Moncure, describes a set-up that most applications have, and I agree with his assessment of it: Taking a step back, from the outside, it looks like uber: *) has a very thick middleware, very thin database with respect to logic and complexity *) has a very high priority on quick and cheap (in terms of bandwidth) replication *) has decided the database needs to be interchangeable *) is not afraid to make weak or erroneous technical justifications as a basis of stack selection (the futex vs ipc argument I felt was particularly awful -- it ignored the fact we use spinlocks) The very fact that they swapped it out so easily suggests that they were not utilizing the database as they could have, and a different technical team might have come to a different result. Postgres is a very general system and rewards deep knowledge such that it can outperform even specialty systems in the hands of a capable developer . . . It's all the rage to use an object-relational wrapper to abstract away the database brand, so that the database can be "interchangeable." In my 11 years experience with web apps and databases, particularly Postgres, this is a false economy: - When you use an object-relational wrapper, you are trading one kind of lock-in for another. Instead of locking yourself into Postgres, you are locking yourself into PHP. That seems a good trade to most developers, who know their middleware language better than SQL. - However I have found that moving your application code from the middle layer, to SQL, results in shorter code and much faster execution. I don't mean moving the Python code into Postgres functions written in PL/Python (which you can do). I mean porting them to SQL. The simplest example is if your first version of your application just used SQL to fetch all the rows from the database and using a Python if-statement to find the rows you need: rewriting the Python if-statement as an SQL where-clause will be much faster. I'm sure few of you are doing something like that, but there are many, more complex examples like that, which I have learned and slowly replaced over the years. - Furthermore SQL is just like any other language in that there are many ways to do the same thing, and some ways are a thousand times faster than others. People who only know basic SQL likely are writing inefficient apps. I have often rewritten queries to use a fraction of the memory and time they were using. Moral of the story: Learn more SQL, instead of learning some avant-garde storage engine. Stepping deeper into PostgreSQL (and thereby locking yourself more and more into it) is often better than locking yourself deeper into Python, PHP, or whatever your middle language is. Who knows, you may want to switch out your middle language before you want to switch out Postgres! I try to make Postgres my "application": put all your business logic in there. It may have an arcane "user-input interface" (SELECT . . . FROM sometable WHERE . . .) and a primitive "user-output interface" (plain tables) but that's where the middleware finally comes in, to cover the queries with checkboxes and buttons, and to decorate the output into various boxes, color, layout, and type, and maybe some nice images, graphs, and so on.

neves 10y ago

I've already read 2 very long about this issue, but this very succinct text is really way better than any of others. The best computer programming article I've read in months.

systematical 10y ago

If only politicians gave answers like that, country would be a better place. Kudos for the answer. I use both MySQL and Postgresql depending on my use case.

bind 10y ago

Tom Lane's response: this seems like an annoyance, not a time-for-a-new-database kind of problem. https://www.postgresql.org/messageid/13659.1469570853%40sss.pgh.pa.us https://www.postgresql.org/messageid/13659.1469570853%40sss....

zhangela 10y ago

What does VACUUM, SNAPSHOT TOO OLD, HOT mean?

machine-wisdom 10y ago

The whole discussion (this and [1] and [2]) is very interesting. I find it that Uber is holding on the ACID guarantees of a relational DB so much, in a way that is clearly hurting them. IMHO it will do them a big service to break down the responsibility of such a DB into multiple distributed systems that can work on a global scale. For example, a distributed lock system can help them when they need transactions. If they keep moving from one relational DB to another, they are bound to hit problems of Availability because they are choosing very strong Consistency, and the CAP theorem says we can't have it all (Partition tolerance is required). [1] https://news.ycombinator.com/item?id=12166585 https://news.ycombinator.com/item?id=12166585 [2] https://news.ycombinator.com/item?id=12179222 https://news.ycombinator.com/item?id=12179222