9 ms·

I think it’s worth mentioning that most of these problems only occur at a scale that only top 1% of companies will reach. I’ve been using PostgreSQL for over a

• I_am_tiberius 6y ago
  The problem is that you want to build something that can scale in the future.
  • theshrike79 6y ago
    I think I've heard a saying about this, something about premature optimisation...
    • I_am_tiberius 6y ago
      Sure you shouldn't care about scaling at the beginning. But why should you start using a system that you already know won't scale in the future?
      • oblio 6y ago
        > Sure you shouldn't care about scaling at the beginning. But why should you start using a system that you already know won't scale in the future? Because it's well supported and solid otherwise? There's a wealth of documentation, resources of many kinds, software built around it (debugging, tracing, UIs, etc.). Because there's a solid community available that can help you with your problems? What alternative technology is there that scales better? I guess MySQL could be it, but doesn't MySQL also come with a ton of its own footguns?
        • I_am_tiberius 6y ago
          I use Postgres at the moment and I'm happy except for the process per connection part and the upgrade part. Knowing what I know now I think MySQL would have made me happier. On the other hand, it may have caused other issues I don't have with Postgres. I just hope the Postgres team maintains its roadmap based on posts like this.
      • why_Mr_Anderson 6y ago
        But you have to get to that future first! If you lose your customers because you can't deliver something on time due to complexity of your 'scaling-proof' system or because you can't accommodate changes requested by clients because they would compromise your architecture, scaling will be last of your worries.
      • theshrike79 6y ago
        Only if I _know_ I'm creating something that will definitely have huge amounts of concurrent users and someone pays me to make it scale from the start. For a hobby project that might take off or might not, there's really no point in making everything "webscale"[0] just in case. [0] https://youtu.be/b2F-DItXtZs https://youtu.be/b2F-DItXtZs
      • WJW 6y ago
        Because the hyperscalable databases are much more difficult to set up, use and administer. It's not a "free" upgrade, it'll slow down everything else you do.
      • spaetzleesser 6y ago
        There are a lot of dimensions to scaling. It's hard to predict where you really will have to scale up.
  • o_p 6y ago
    Its better to work on getting all those users before planning what color the ferrari will be..
    • I_am_tiberius 6y ago
      Exactly. That's why it would be good to have a system which is prepared for scaling in the future.
      • oblio 6y ago
        What's that system? MySQL? Are there any other OSS RDBMSes which are comparable and scale better?
        • I_am_tiberius 6y ago
          I would only have thought of MySQL.
          • doctor_eval 6y ago
            MySQL isn't a general solution to problems of scale, because you don't know what problems you're going to have until you have them. So for example if your scaling problem is ACID compliant database updates - say you're the next fintech - then I was under the impression that MySQL would be the last database you'd want to be using. Have I missed something?
            • redis_mlc 6y ago
              The default MySQL storage engine since 2010 has been Innodb, which is ACID-compliant. Overall, MySQL and Postgres are great Open Source databases, but MySQL has the edge for enterprise features (ie. replication options) and a simpler grant system. Source: DBA.
            • I_am_tiberius 6y ago
              I'm no expert and can't answer that. It was just my impression, and I might be wrong, that for scaling purposes MySQL is better suited. Currently I'm working on a Saas product and the test instance that runs on Digital Ocean sometimes causes connection limit issues (with connection pool) sometimes. Sure my code is maybe not perfectly utilizing connections but I'm really afraid that this happens in production and I don't know how to fix it. On my test environment I just restart everything but on a productive environment I can't do that all the time.
  • marcus_holmes 6y ago
    ffs, this attitude causes massively more problems than it solves. 1. You can always change later. Uber switched from Postgres to MySQL when they had already achieved massive scale. 2. You don't know what scaling problems you're going to get until you've scaled. 3. Systems designed to scale properly sacrifice other abilities in order to do that. You're actively hurting your velocity with this attitude. 4. Every single expert in the field who has done this, says to start with a monolith and break it out into microservices as the product matures. Yet every startup is founding on K8s because "we'll need it when we hit scale so we might as well start with it" 5. Twitter's Fail Whale - the problems that failing to scale properly bring are less than the problems of not being flexible enough in the early stages. Build it simple, and adapt it as you go. Messing up your architecture and slowing down your development now to cope with a problem you don't have is crazy.
    • I_am_tiberius 6y ago
      ffs, this attitude causes massively more problems than it solves. I don't think that it causes so many problems to just use MySQL instead of Postgres from the very beginning of a project. I like using Postgres and I understand that I shouldn't care about scaling but if a make a good decision from the very beginning it can't hurt.
      • doctor_eval 6y ago
        Is MySQL a general solution to scaling? What if your scaling problem is with writes?
        • derekperkins 6y ago
          That's why Vitess is so awesome - you can scale writes infinitely. There's not a truly comparable option for Postgres
      • chrisandchris 6y ago
        I would rather use Postgres and have a RDBMS that is quite strict and migrate data later instead of having a RDBMS that just does what it likes sometimes. For example, query your table „picture“ with a first column „uuid“ (varchar) with the following query: SELECT * FROM picture WHERE uuid = 123; I don‘t know what you expect, I expect the query to fail because a number is not a string. MySQL thinks otherwise.
• latch 6y ago
  PostgreSQL is great, but I don't think your statement is particularly true. Process per connection is pretty easy to accidentally run into, even at small scale. So now you need to manage another piece of infrastructure to deal with it. Downtime for upgrades impacts everyone. Just because you're small scale doesn't mean your users don't expect (possibly contractually) availability. Replication: see point above. General performance: Query complexity is the other part of the performance equation, and it has nothing to do with scale. Small data (data that fits in RAM) can still be attacked with complex queries that can benefit from things such as clustered index and hints.
  • andoriyu 6y ago
    > Process per connection is pretty easy to accidentally run into, even at small scale. So now you need to manage another piece of infrastructure to deal with it. Most places I saw this as an issue, are where developers think that by tweaking the number of connections will give them a linear boost in performance. Those are the same people that think adding more writers in RWLock will improve writing performance. I agree that it's easy to run into and pretty silly concurrency pattern for today's time. At the same time, it's just a thing you need to be aware of when using PostgreSQL and design your service with that in mind.
  • yarcob 6y ago
    > Downtime for upgrades impacts everyone. Just because you're small scale doesn't mean your users don't expect (possibly contractually) availability. I don't understand this mindset. Every tiny startup thinks they need zero downtime migrations. At the same time, major banks and government institutions just announce maintenance windows. They just pick a time when few people use the service and then shut the whole system off for a few hours. Sure, it's nice if your service is never down. But I'm also pretty sure that most customers prefer paying for new features rather than preparing for zero downtime migrations. Also, considering how long PostgreSQL versions are supported, you only need to do major version upgrades every five years or so.
    • latch 6y ago
      We're a small transport company, trying to get employees to and from work. No matter the time of day, there are always people commuting (or planning to commute). When's a good time for outage? Imagine it's 3:30am, you just got off a shift, you can't afford a cab and the nearest subway is 10KM away. How fun is it that the transport app you rely on it down for maintenance? Maybe that helps you understand the mindset?
• KptMarchewa 6y ago
  >I think it’s worth mentioning that most of these problems only occur at a scale that only top 1% of companies will reach. If you're talking about 1% of all software companies, then it's not true. You don't need to be B2C company with XXX millions users to have a lot of data. >PostgreSQL is still the best general purpose database in my opinion, and you can then consider using something else for parts of your application if you have special needs. Well, yes, you're already talking about one mitigation strategy to not get to this scaling problems.
• john8903 6y ago
  Not true - I work at a company of 400 people, and we ran into the Process-Per-Connection / pgbouncer issue.
  • hattar 6y ago
    Company of 20 here. Same.
  • mikl 6y ago
    I guess that’s very dependent of what kind of framework you’re using. The only PostgreSQL-driver I’ve seen that does not have connection pooling built-in is the PHP one (since PHP’s runtime model does not work in a way where that would be easily possible).
    • darksaints 6y ago
      It's still fairly easy to hit problems even when you're using an application level connection pool, simply because it's so damn easy to scale up application nodes.
    • tomnipotent 6y ago
      > not have connection pooling Local connection pooling only goes a very small way to mitigate this issue. If you have enough servers hitting PG, you're going to need to add in something like PgBouncer sooner or later.
• barrkel 6y ago
  I've hit many query performance regression problems with ~10 million rows, which required rewriting with CTEs and other techniques to tweak the planner. This isn't a large scale at all.
• remus 6y ago
  I don't think this is necessarily true. Say you have 100 sensors sampling at 1kHz for a year, you'd have ~3 trillion rows in your database and plenty of potential for scaling issues at a very reasonable price.
  • refenestrator 6y ago
    In that specific case, you probably want to roll up that time-series data as it gets older, while keeping the full dataset in a flat file system for data science etc if you need it. You probably never need a millisecond-granularity data point from 6 months ago in your database.
    • sitkack 6y ago
      They probably shouldn't be rows at all. They are effectively low frequency sound files. I'd probably store them in parquet and use a FDW wrapper in Postgres.
      • nomel 6y ago
        I have time series of 2d 64x64 sensor data, resulting in a few billion values that I'm trying to cram into some custom parquet format. I'm often surprised that it's 2021 and we're still stuck in tabular data, with n-dimensional arrays often not even considered.
        • sitkack 6y ago
          Thermal or depth map camera? Really depends on how you want to process and query it. At 16k per frame, I'd store each one sequentially. Do you need to only look at a single pixel across a million frames? Or do you process groups of 20-100 frames at a time?
  • dls2016 6y ago
    I'm starting a project in this realm right now, though only three sensors to begin with. Generally I'm leaning towards "everything in Postgres", but I think I'm going to store the raw sensor data in the filesystem.
• darkstar_16 6y ago
  I think a lot of issues that people complain about PostgreSQL come from the fact that the default config is not very good if one wants to run it in production, even for relatively small workloads. Things like process per connection can kick one in the foot if one is not aware of how PG works.
• holtalanm 6y ago
  > I think it’s worth mentioning that most of these problems only occur at a scale that only top 1% of companies will reach I'll echo what another commenter said. Tons of data != tons of profit. Tons of data just means tons of data. Source: Worked on an industrial operations workflow application that handled literally _billions_ of records in the database. Sure, the companies using the software were highly profitable, but I wouldn't have called the company I worked with 'top 1%' considering it was a startup.
  • deleted 6y ago
    [deleted]
  • hobs 6y ago
    It's really not that hard to have billions of rows in a modern data ingest situation, especially if you allow soft/deleting/versioning. Honestly anything that fits on one hard drive shouldnt be called "tons of data."
  • jholman 6y ago
    > handled literally _billions_ of records in the database Classic example of Medium Data
• mekster 6y ago
  Everytime I see comments that praises PostgreSQL on top of MySQL without any explanations, I tend to think they're trying to bury a product from Oracle than from a real need of one over the other.
  • sli 6y ago
    Maybe some younger developers, but I'd imagine a lot of us grew to dislike MySQL years before Oracle bought it (in 2010). I'd switched to Postgres already by then.