19 ms·

PostgreSQL is enough

• jon_adler 3y ago
  Somewhat related is Dan McKinley’s Choose Boring Technology. https://boringtechnology.club/ https://boringtechnology.club/
• superb-owl 3y ago
  I often go down rabbit holes like this, trying to collapse and simplify the application stack. But inevitably, as an application grows in complexity, you start to realize _why_ there's a stack, rather than just a single technology to rule them all. Trying to cram everything into Postgres (or lambdas, or S3, or firebase, or whatever other tech you're trying to consolidate on) starts to get really uncomfortable. That said, sometimes stretching your existing tech is better than adding another layer to the stack. E.g. using postgres as a message queue has worked very well for me, and is much easier to maintain than having a totally separate message queue. I think the main takeaway here is that postgres is wildly extensible as databases go, which makes it a really fun technology to build on.
  • nextaccountic 3y ago
    > But inevitably, as an application grows in complexity, Some applications never grow that much
    • ejb999 3y ago
      I would go further and even say 'most' applications never grow that much.
    • LaGrange 3y ago
      But it surely will! It will! See, now that we're profitable, we're gonna become a _scale up_, go international, hire 20 developers, turn everything into microservices, rewrite the UI our customers love, _not_ hire more customer service, get more investors, get pressured by investors, hire extra c-levels, lay-off 25 developers and the remaining customer service, write a wonderful journey post. The future is so bright!
      • zwnow 3y ago
        Honestly, that's one of the reasons I never want to monetize my work and stay miles away from the software industry. Modern world is all web apps that require you to subscribe to 20 different 3rd party services to even build your app. So you rack up bills before your product is even remotely lucrative... Building an app with no third party dependencies seems impossible nowadays. At least if you plan to compete.
        • LaGrange 3y ago
          I mean, you _can_ host your staging environment on a Minisforum PC hidden in your closet and then deploy to Hetzner, and probably save a _ton_ unless your service benefits from things like, say, autoscaling or global low-latency access. Niches where you can get away with that are limited, not just by technical challenges but because large parts of the social ecosystem of IT won't like that. But they do exist. There's also still things that aren't webapps _at all_, there's software that has to run without internet access. It's all far apart and often requires specialized knowledge, but it exists.
          • zwnow 3y ago
            Yea I mostly learned web dev so far but wanted to get into IoT stuff so I might find something cool to do in there.
  • twosdai 3y ago
    The same argument of UNIX design patterns (Single responsibility, well defined interfaces and communication protocals) vs Monolithic design patterns comes up a lot. I think that its mainly because both are effective at producing products, its just that they both have downsides.
    • macksd 3y ago
      I read a meme yesterday about how you can just interject "it's all about finding that balance" into any meeting and people will just agree with you. I'm gonna say it here. Sometimes a flexible tool fits the bill well. Sometimes a specialized tool does. It's all about finding that balance. Thank you for coming to my TED talk.
    • e12e 3y ago
      Just noting that sometimes one can do both: seperate postgres DBs/clusters for different use-case, seperate instances of a web server for TLS termination, caching, routing/rewriting, Ed:static asset serving. Benefit is orderly architecture, and fewer different dependencies.
  • diggan 3y ago
    I think a lot of the industry struggles with the idea that maybe there is no "one size fits all", and what makes sense when you're a one person company with 100 customer probably doesn't make sense when you're a 1000 people company with millions of customers. If you use a stack meant for a huge userbase (with all the tradeoffs that comes with it) but you're still trying to find market fit, you're in for a disappointment Similarly, if you use a stack meant for smaller projects while having thousands of users relying on you, you're also in for a disappointment. It's OK to make a choice in the beginning based on the current context and environment, and then change when it no longer makes sense. Doesn't even have to be "technical debt", just "the right choice at that moment".
    • davidw 3y ago
      > It's OK to make a choice in the beginning based on the current context and environment, and then change when it no longer makes sense. Yep. And Postgres is a really good choice to start with. Plenty of people won't outgrow it. Those who do find it's not meeting some need will, by the time they need to replace it, have a really good understanding of what that replacement looks like in detail, rather than just some hand-wavy "web scale".
      • tracker1 3y ago
        True enough and with modern hardware that barrier is relatively high. IIRC Stack overflow was handling several million users in a single database server over a decade ago... We've got over 8x the compute power and memory now. Still need to understand the data model and effects on queues though.
  • wg0 3y ago
    I think SQS is cheap enough to build on as a messaging queue even if you're not hosting within AWS. Out of the widely underrated AWS services include SNS and SES and they are not a bad choice even if you're not using AWS for compute and storage.
    • qaq 3y ago
      SQS is at least once PG can give you exactly once
      • qaq 3y ago
        Not sure why this is making people upset.
        • williamdclt 3y ago
          Because it's incorrect. If you have any non-postgres side-effect, you can't have exactly-once (unless you do 2PC or something like that). There isn't any technology that gives you "exactly once" in the general case.
          • qaq 3y ago
            That's not how exactly once is defined for queue. We are talking about semantics of what queue systems is providing.
            • silon42 3y ago
              Nobody will understand it like that.
              • qaq 3y ago
                Anyone who has ever selected queue service/product will understand it like that. Because thats one of the most prominent features that gets highlighted by those products: SQS Standard queues support at-least-once message delivery. NATS offers "at-most-once" delivery etc.
  • TheCapeGreek 3y ago
    On top of that, a lot of discourse seems to happen with an assumption that you only make the tech/stack choice once. For the majority of apps, just doing basic CRUD with a handful of data types, is it that hard to just move to another DB? Especially if you're in framework land with an ORM that abstracts some of the differences, since your app code will largely stay the same.
    • philihp 3y ago
      The way you've trivialized a database vendor swap makes me curious how often you do this.
  • brightball 3y ago
    PG works really well as a message queue and there's several excellent implementations on top of it. Most systems are still going to need Redis involved just as a coordinator for other pub/sub related work unless you're using a stack that can handle it some other way (looking at BEAM here). But there are always going to be scenarios as an application grows where you'll find a need to scale specific pieces. Otherwise though, PostgreSQL by itself can get you very, very far.
    • dfee 3y ago
      Worth noting that Postgres has a pubsub implementation built in: listen/notify. https://www.postgresql.org/docs/current/sql-notify.html https://www.postgresql.org/docs/current/sql-notify.html
      • brightball 3y ago
        Oh yea, definitely aware of it. I believe many of the queuing solutions utilize it as well. I've ready a lot of reports (on here) that it comes with several unexpected footguns if you really lean on it though.
      • cpursley 3y ago
        Yep, I should add that. One of the libraries in my list (that I maintain) is WalEx: https://github.com/cpursley/walex/issues https://github.com/cpursley/walex/issues It subscribes to the Postgres WAL and let you do the same sort of thing you can do with listen/notify, but without the drawbacks like need for triggers or character limits.
        • brightball 3y ago
          Going to add this to my research list.
          • cpursley 3y ago
            Ping me if you have any questions. Long time fan of your blog.
  • legohead 3y ago
    My saying has always been: be nice to the DB Don't use it anymore than you have to for your application. Other than network IO it's the slowest part of your stack.
    • niels_bom 3y ago
      Would you say it's slower than file IO too?
      • alternatex 3y ago
        Kind of irrelevant since a DB provides some guarantees that a simple file does not by default.
        • samtheprogram 3y ago
          GP was responding to a comment comparing it to network IO in terms of bottlenecks in your application stack ...?
      • Too 3y ago
        It’s not slow by itself. It’s a single point of bottleneck that will inevitably become slow as you cram everything into it.
        • dagss 3y ago
          ...but by trying to avoid the bottleneck and moving things to backend, you make things 10x worse resource wise for the DB. So it is not a easy tradeoff. Take any computation you can do in SQL like "select sum(..) ...". Should you do that in the database, or move each item over the network and sum them in the backend? Summing in the database uses a lot less resources FOR THE DB than the additional load the DB would get from "offloading" this to backend. More complex operations would typically also use 10x-100x less resources if you operate on sets and amortize the B-tree lookups over 1000 items. The answer is "it depends" and "understand what you are doing"; nothing about it is "inevitable". Trying to avoid computing in the DB is a nice way of thinking you maxed out the DB ...on 10% of what it should be capable of.
  • deathanatos 3y ago
    I have certain experience with some technologies, e.g., SQS and Postgres. Say I'm on your team, and you're an application developer, and you need a queue. If you're taking the "we're small, this queue is small, just do it in PG for now and see if we ever grow out of that" — that's fine. "Let's use SQS, it's a well-established thing for this and we're already in AWS" — that's fine, I know SQS too. I've seen both of these decisions get made. (And both worked: the PG queue was never grown out of, and generally SQS was easy to work with & reliable.) But what I've also seen is "Let's introduce bespoke tech that nobody on the team, including the person introducing it, has experience in, for a queue that isn't even the main focus of what we're building" — this I'm less fine with. There needs to be a solid reason why we're doing that, and that we're going to get some real benefit, vs. something that the team does have experience in, like SQS or PG. Instead, this … thing … crashes on the regular, uses its own bespoke terminology, and you find out the documentation is … very empty. This does not make for a happy SRE.
    • cnity 3y ago
      This desire can sometimes be so strong that people insist on truly wacky decisions. I have before demonstrated that Postgres performs perfectly well (and in fact exceeds) compared with a niche graph database, and heard some very strange reasons for why this approach should be avoided. A lot of the time you hear that it's engineers who chase shiny technology, but I've seen first hand what can happen when it's leadership.
      • tracker1 3y ago
        I've been on both sides of this.. Rabbit MQ and Elastic Search for a public facing site. The dedicated queue for workers to denormalize and push updates. To elastic. Why, because the $10k/month RDBMS servers couldn't handle the search load and were overly normalized. Definitely a hard sell. I've also seen literally hundreds of lambda functions connecting to dozens of dynamo databases. I'm firmly in the camp of use an RDBMS (PostgreSQL my first choice) for most things in most apps. A lot of times you can simply apply the lessons from other databases at scale in pg rather than something completely different. I'm also more than okay leveraging a cloud's own MQ option, it's usually easy enough to swap out as/if needed.
  • brlewis 3y ago
    > you start to realize _why_ there's a stack, rather than just a single technology to rule them all Architecturally, there are other cases besides message queues where there's no reason for introducing another layer in the stack, once you have a database, other than just because SQL isn't anybody's favorite programming language. And that's the real reason there's a stack.
  • wvh 3y ago
    That's a nicely balanced view. I've been working on the intersection between dev, sec and ops for many, many years and one of the most important lessons has been that every dependency is a liability. That liability is either complexity, availability, security, wasting resources or projects or key people disappearing. Do anything to avoid adding more service, library or technology dependencies; if necessary, let people have their side projects and technological playgrounds to distil future stacks out of. There are good reasons to go OLAP or graph for certain kinds of problems, but think carefully before adding more services and technologies because stuff has a tendency to go in easily but nothing ever leaves a project and you will inevitably end up with a bloated juggernaut that nobody can tame. And it's usually those people pushing the hardest for new technologies that are jumping into new projects when shit starts hitting the fan. If a company survives long enough (or cough government), a substantial and ever increasing amount of time, money and sec/ops effort will go into those dependencies and complexity cruft.
  • tetha 3y ago
    This is very much the way I'm pushing in our internal development platform: I want to offer as little middlewares as possible, but as many as necessary. And ideally these systems are boring, established tech covering a lot of use cases. From there, Postgres ended up being our relational storage for the platform. It is a wonderful combination of supporting teams by being somewhat strict (in a flexible way) as well as supporting a large variety of use cases. And after some grumbling (because some teams had to migrate off of SQL Server, or off of MariaDB, and data migrations were a bit spicy), agreement is growing that it's a good decision to commit on a DB like this. We as the DB-Operators are accumulating a lot of experience running this lady and supporting the more demanding teams. And a lot of other teams can benefit from this, because many of the smaller applications either don't cause enough load on the Postgres Clusters to be even noticeable or we and the trailblazer teams have seen many of their problems already and can offer internally proven and understood solutions. And like this, we offer a relational storage, file storage, object storage and queues and that seems to be enough for a lot of applications. We're only now adding in Opensearch after a few years as a service now for search, vector storage and similar use cases.
  • agumonkey 3y ago
    The more I do fullstack work the more I see an obesity crisis. I under the need to modularize (I dearly think I do) but god you have relational model, reimplemented in your framework, reencoded as a middleware to handle url parsing, the one more layer to help integrate things client side. I find that insane. And Postgrest was a refreshing idea.
    • cpursley 3y ago
      Seriously. There's like 7000 duplicates of the very same data layer in a single stack: database, back-end ORM/data mapper, front end and various caching things in between. Things like PostgREST and Hasura area great pared with fluent clients.
      • agumonkey 3y ago
        And then there's the failed microservice case.. what some people describe a distributed monolith where data has to be passed around through every layer, with domain logic replicated here and there.
• wesselbindt 3y ago
  Seems like it's less than a week ago that someone (rightly) posted that it isn't [1]. Postgres is a relational database, and it does this really well. It's not a convection oven. [1] https://news.ycombinator.com/item?id=39243655 https://news.ycombinator.com/item?id=39243655
  • karmakaze 3y ago
    That is specifically about AWS/RDS and relates to EBS/IOPS moreso than PostgreSQL. In fact their solution was to run the db but not as an RDS instance. > The Solution: Roll your own: For us the solution was fairly simple: don’t use a managed database services and roll our own infrastructure on EC2. I've done the same running PostgreSQL on EC2 instances with NVMe local raid storage. It's very fast, but then you're responsible for its uptime, updates, backups, etc. That setup was used for performance sensitive but less critical data.
  • nahnahno 3y ago
    Did you read the article? It’s about how expensive RDS is, not about how Postgres didn’t work.
• Thaxll 3y ago
  It's enough until you start working with json and how painful it is to manipulate data.
  • cpursley 3y ago
    Postgres is great at json, I use it a bunch.
• adlpz 3y ago
  Love postgres and use it extensively. However, there's always an issue when I start doing the more advanced stuff: how do I combine that with all my years of experience with version control, code reviews, types, tests, static analysis and all the niceties of coding in general? Migrations?
  • vmfunction 3y ago
    If you want to serious develop postgres as how you develop code, then you should also use a LSP. https://github.com/supabase/postgres_lsp https://github.com/supabase/postgres_lsp
    • cpursley 3y ago
      Thanks, I need to add this to my list as well.
    • realaleris149 3y ago
      If you are using IntelliJ (Ultimate), it also have very good support for SQL (syntax highlighting, autocomplete, etc.) and is Postgresql flavor aware. https://www.jetbrains.com/help/idea/database-tool-window.html https://www.jetbrains.com/help/idea/database-tool-window.htm...
  • agentultra 3y ago
    It's not a great DX for sure. Once you start stuffing a ton of application logic in Postgres triggers (or worse split logic between your application layer and triggers) your system will explode in complexity really fast. Developers will insert a row in an inconspicuous table and things will break in ways that seem mysterious because it's not obvious, looking at the application code, why the system is misbehaving. There are commands in most postgres clients, even psql, to view the _currently_ defined functions... but when you go to debug those you will have to look through migrations to see how the function came into it's current state... bisecting through history here is not very useful since each change to the function is a new file. I think this can be fixed though and made much easier, it's just not there yet. In general I don't think the developer tooling is up to par to push very much of your application logic into postgres itself. I recommend using triggers for consistency and validation or table-local updates (ie: timestamps, audit logs) but keep process-oriented behaviour (iow: when this happens, then that, else this, wait for call and insert here, etc) in the application layer (chasing a cascade of triggers is not fun and quite annoying). ... all that being said, you can do unit testing in Postgres. And there is decent support for languages other than pgSQL (ie: javascript, ocaml, haskell, python, etc). It's possible to build dev tooling that would be suitable to make Postgres itself an application development platform. I'm not aware of anyone who has done it yet.
• CraigJPerry 3y ago
  Aspiring to "use the right tool for the job" leads you into conflict with some of the ideas posted on the gist. To give a concrete example on the first item in the gist: if i need periodic jobs - and all the operational headaches that go with (rerunning, ordering, dependencies, logging, yada yada...) - is postgres the right tool for the job? It CAN be, but for most people in most circumstances, it's probably not.
  • jerrygenser 3y ago
    If the purpose is to do database specific things then pg cron can be useful for e.g. periodic vacuums. However for running application logic, maybe less so.
• benrutter 3y ago
  On the [Simplify: move code into databases](https://sive.rs/pg https://sive.rs/pg), has anyone actually tried this? My instinct is that the output won't be simpler at all, but a big twisty web of varying parts and competing use cases, all extremely hard to discover. To me, having two things that do something distinct is simpler, and ironically, Rich Hickey says this exact thing in Simplicity Matters, which is quoted in the article.
  • jjice 3y ago
    > To me, having two things that do something distinct is simpler I completely agree. Inside the DB, it's hard to argue about performance benefits in a lot of situations. That said, debugging, testing, and developing are so much harder. I love to have a well written bit of code that I can integration test as well as mock up and use for unit tests with other components of my application. It's much trickier to do that in the DB. Sometimes it is a lot more performant, and that's a situation you may want to consider moving towards, but I would try to stay away from it for as long as the performance gain isn't huge.
  • wg0 3y ago
    One caveat is when you have to upgrade across major database versions. The more your application utilises these features, the more you're likely to hit when next major version upgrade has to happen. Not that major database versions are any simpler even if you just stick to CRUD for databases.
    • KronisLV 3y ago
      > Not that major database versions are any simpler even if you just stick to CRUD for databases. I mean, even LTS versions have EOL dates, so even at the least frequent, you'll need to do upgrades around every 5 years or so.
    • cpursley 3y ago
      This is also true of upgrading dependencies in the application layer.
  • Kinrany 3y ago
• bearjaws 3y ago
  Postgres is enough as long as you have a good multi-tenant setup e.g. a separate database per customer. Ran a single postgres instance with multi-tenant SaaS product that crossed 4B records in a few tables, even with partitions and all the optimization in the world, it still hurts to have one massive database. We still got bought tho, so I will agree its enough
  • breckenedge 3y ago
    Worked at a company that suffered from this too. Compounded by the feature that let customers share data with other customers, so single DB was a decent architecture to begin with. And we were on postgis v1, which had no straightforward upgrade path to 2.0, nor was it supported by later versions of Postgres. Restoring a backup became a multi-day affair.
  • Ensorceled 3y ago
    > Postgres is enough as long as you have a good multi-tenant setup e.g. a separate database per customer. Separate per customer has a lot of advantages (especially around customer security and things like deletion) but you can also shard by customer right from the beginning; customer #1 in the "odd" shard, customer #2 in the "even" shard etc. Switching to database per customer if that is working well is relatively easy so you're future proofed both ways.
  • cpursley 3y ago
    That's easier now than ever with services like neon.tech and fly.io where you can quickly spin up new databases via api.
    • refset 3y ago
      Better still, take a look at Nile's "tenant virtualization" concept: https://www.thenile.dev/ https://www.thenile.dev/
      • cpursley 3y ago
        That is cool, thanks! I feel like this is going to solve a lot of saas businesses problems.
• mcdonje 3y ago
  Obligatory "depends". I'm aware of more than one company that uses postgres for huge datasets serving workflows with high throughput and importance. It's truly a workhorse, it's comparatively a pleasure to work with, and its extensibility makes it useful in places some other DBs aren't suitable for. But a relational DB isn't right for every workload.
  • sgarland 3y ago
    > But a relational DB isn't right for every workload. While sometimes true, I'll counter that it's more common that the application was not truly designed for a relational DB, and instead was designed for reading and storing JSON.
    • mcdonje 3y ago
      What's more common is domain dependent.
    • SJC_Hacker 3y ago
      You can store JSON (since 9x) and JSONB in PostgresQL (since 9.4 back in 2014)
      • mcdonje 3y ago
        imo, the value in that is interop with relational data. If you're primarily working with json, it's probably best to use a document db.
        • sgarland 3y ago
          Yes. I don't mind the occasional JSON[B] column when it makes sense, but it should not be used as an excuse to not design a good schema. In general, if you find yourself trying to fit a non-scalar value into RDBMS, you should reconsider the data model.
      • sgarland 3y ago
        I'm very aware of this fact. The issue is that by definition, JSON (or any other non-scalar) cannot be in normalized form, and so what you end up with is devs using RDBMS as a K/V store that has a few joins thrown in. JSON performance (or JSONB, it really doesn't matter) is abysmal compared to more traditional column types, especially in Postgres due to TOAST. Properly normalized tables (ideally out to 5NF, but at least 3NF) are what RDBMS were designed to deal with, and are how you don't wind up with shit performance and referential integrity issues.
• kevinmershon 3y ago
  Thank you for this post. I've been looking high and low for weeks trying to find a way to connect Looker to a GraphQL API unsuccessfully and apparently "PostgreSQL emulators" are called FDWs as I learned from this link. I had searched every term I could think of for how to simulate or proxy a SQL database into an API but calling it a foreign data wrapper would have never occurred to me.
  • pcthrowaway 3y ago
    Did you try describing the problem and the thing you're looking for to chatgpt to see if it knows of a name? I find that making connections like this is one of the things it actually does well
    • kevinmershon 3y ago
      I actually did, repeatedly. I threw away 3 or 4 chat sessions with it about this very subject. It kept hallucinating features into libraries and naming products that don't actually support this translation direction. And with Google searches the vast majority of results are going the opposite way: creating a GraphQL API from a real SQL schema.
    • darkerside 3y ago
      Remember when you could do this with Google?
• karol 3y ago
  Could you write an OS based on PostgreSQL?:)
  • petercooper 3y ago
    It depends on your definitions, but if you took psql and Postgres and compared it to something like an OS on a 1980s microcomputer, you're kinda there. You can run arbitrary commands, you can program it, you can debug those programs, you can run programs, and you can access storage. If you're talking more like Linux or Windows, you have.. a bit more work to do ;-)
  • papichulo2023 3y ago
    Isnt SQL turing complete?
  • hot_gril 3y ago
    I've thought about writing a general-purpose programming language that uses Postgres as its runtime, with the twist that it can save checkpoints to survive system reboots and also interop closely with SQL. Postgres already has a lot of nice built-in types and functions.
  • cpursley 3y ago
    Not quite an OS, but they are doing some neat things: https://omnigres.com/ https://omnigres.com/
  • rbrewer123 3y ago
    Not postgres, but led by its inventor, who knows a lot about high-performance databases: https://dbos-project.github.io/ https://dbos-project.github.io/
• MaxMoney 3y ago
  [dead]
• emseetech 3y ago
  This makes a strong case, but I've decided to start every new project with sqlite and not switch until absolutely necessary. If Postgres is the 90% case, then sqlite is the 80% case and is also dead simple to get going and genuinely performant. So when vertical scaling finally fails me, I know I'll be at a wonderful place with what I'm building.
  • turnsout 3y ago
    I’m with you in general, but what about vector search? It really feels like the DB industry has taken a huge step backward from the promise of SQL. Switching from Postgres to SQLite is easy because the underlying queries are at least similar. But as soon as you introduce embeddings, every system is totally different (and often changing rapidly).
    • drittich 3y ago
      Vector similarity searches can be done with simple SQL statements, and while the performance will not match a vector db, it's often good enough. For a db like SQLite that can run in-memory, I suspect they would be reasonably fast.
    • emseetech 3y ago
      There are vector search solutions for sqlite that basically work, so if my project doesn't rely on that 100% then I'm willing to use those as stop-gaps until it does. Of course, if there's a shortcoming of sqlite that I know I need right out of the gate, that would be a situation where I start with postgres.
    • codingjaguar 3y ago
      SQL was introduced in 1970s. Considering vector search was only adopted in the last 5 years, I’m not surprised by the lack of standards on vector API. At Google embedding as retrieval became popular in 2019-2020. This is the new kid in town so you would see soon all major SQL dbs will support vector. However, any serious user, O(10M) vectors or above, would still require a dedicated vector db for performance reasons.
    • 3y ago
• cyrialize 3y ago
  I love postgres - although there are some things I dislike about it - mainly due to me using mysql for a years. All dislikes are mainly syntactical things, like having USING in deletes instead of JOINs. I also liked the output of EXPLAIN more in mysql. Using postgres made me realize that many of the people I work with had no idea what an ambiguous group by is, because unlike mysql, postgres will never, ever let you do an ambiguous group by. Running into many different things like that over the years has really made me realize how nice postgres is.
  • sgarland 3y ago
    While I love MySQL for a variety of reasons (and think it's legitimately better for certain features – tangential to your point, being able to ORDER BY in a DELETE is great), its lax GROUP BY option isn't really a great idea in terms of correctness. You can emulate it in Postgres with DISTINCT ON if desired, and as long as you also ORDER BY something that makes sense for your query, it should work similarly.
• qaq 3y ago
  One cool thing if you are running off Postgres - DDL is transactional so you get clean deploy of your new code version (stored procedures) with precise switchover for free.
• notpublic 3y ago
  time-series data? Maybe include timescaledb
• deleted 3y ago
  [deleted]
• jackschultz 3y ago
  Was talking to coworker yesterday about a spectrum of where code lives, and the differences from where I started to where I am now in understanding. Start after college and backend web dev was fully in scripting language, Python or Ruby, and ORMs that completely fogged where any of the data was stored. Rails and ActiveRecord is so good at shrouding the database to the point where you type commands that create databases and you never see them. Classes are written to describe what we want the data to look like and poof! SQL commands are created to build the schema that we never need to see. On this end of the spectrum, the scripting language will stay the same, but we want to be agnostic to where the data is stored. On on the other end of the spectrum, Postgres is enough. More than enough. Like in the link, it can do all the tasks you ever care about. The code you're writing for the backend / data is about data, not about the script. We care where it's stored, that it's clear the structure, the reads and updates are efficient. We can write all statements in SQL to create tables, functions, trigger, queues, and efficient read queries with indexes to make the data come back to the scripting language in the exact form that's wanted. On this end, we know and optimize how the data is stored, agnostic to the scripting language that uses the data. I went from the first end of the spectrum to the second. Everything can be done in Postgres. Audibility, clarity, efficiency is much better there than in Python, is my position. The only thing holding it back is that people don't see development from the data side yet, and if you're deciding on tech, it's not easy to use a tech that people don't have as good of development ability yet. There are no Postgres bootcamps right now. But There's more and more adoption of this I'm seeing, and the money and development of Postgres leads me to trust that it'll be around a very long time, only getting better. Posts about the power of databases, Postgres and some SQLite for example are becoming more and more common. It's a cool change to follow and watch grow.
  • pjmlp 3y ago
    >....backend web dev was fully in scripting language, Python or Ruby... Not on my bubble, it has been fully in .NET and Java since 2001, with exception of a couple of services written in C++.
  • cpursley 3y ago
    This was similar to my journey as well. I'm a self-taught developer and was so green when I learned Rails I didn't even understand that there was such a thing as SQL behind the ORM. Took some grey beard .net folks to share the and power of the database.
• juliend2 3y ago
  Also, it has all the encryptions functions one would need to implement authentication mechanisms, in pure SQL. https://www.postgresql.org/docs/current/pgcrypto.html https://www.postgresql.org/docs/current/pgcrypto.html
• mathnode 3y ago
  Well it is obviously not enough as most of these are addons. Most of these are already built into MariaDB. Excerpt from the motivaion of "PostgreSQL HTTP Client": > Wouldn't it be nice to be able to write a trigger that called a web service? Rather you than me.
  • cryptonector 3y ago
    > > Wouldn't it be nice to be able to write a trigger that called a web service? > Rather you than me. Yes, don't do that. Instead consume notification streams or logical replication streams and act on those -- sure, you'll now have an eventually consistent system, but you won't have a web service keeping your transactions from making progress. You don't want to engage in blocking I/O in your triggers.
• thatxliner 3y ago
  What about offline sync
  • cpursley 3y ago
    https://github.com/electric-sql/electric https://github.com/electric-sql/electric
• geon 3y ago
  There is https://github.com/pipelinedb/pipelinedb https://github.com/pipelinedb/pipelinedb for streaming time series data, but it is abandoned since 6 years.
  • philippemnoel 3y ago
    There is now https://github.com/paradedb/paradedb/tree/dev/pg_analytics https://github.com/paradedb/paradedb/tree/dev/pg_analytics (disclaimer: I am one of the makers of pg_analytics)
• throwaway918274 3y ago
  My Trifecta is: Postgres, Redis, S3 Hasn't steered my wrong yet. Every once in a while I'm tempted to try to use Postgres for Pub/Sub but then I realize that I need Redis for caching and sidekiq anyways, and Redis is amazing too, so why bother.
  • cpursley 3y ago
    If you're open to Elixir (you'll like it coming from Ruby) then you don't even need Redis. Oban + Postgres for jobs, WalEx for database events, Nebulex for distributed caching. It simplifies things so much (and is cheaper to run).
    • throwaway918274 3y ago
      I keep hearing about it, just never got around to actually doing anything with it...but I might have that itch now. I'll look into these things thanks.
  • gen220 3y ago
    Have you had to deal with read-heavy/OLAP queries? Of the kind that can't be cached effectively (i.e. arbitrary filters).
    • throwaway918274 3y ago
      Our company has spend a lot of resources optimizing these kinds of queries in our application. It's been a bit of a struggle since we also use GraphQL, but we've been doing alright. Partial indexes and liberal use of jsonb have been doing a lot of heavy lifting for us. Our application is extremely read heavy, so the performance hit from updating lots of indexes hasn't been an issue for us (yet).
• ThePhysicist 3y ago
  How about metrics? Would it be possible to do this in Postgres as well? I'm talking e.g. about simple counters that are not directly vital to business logic. I'm currently using Redis for those things as I'm thinking that Postgres seems to be the wrong choice for this kind of data, would anyone disagree?
  • __s 3y ago
    So, having worked on a managed postgres service, we had an extension pushing metrics from the database, but we also used redis to keep service metrics Redis is great. This gist is more about how it's up to you how much you centralize on postgres. But overall being able to offload from the database has value, so "PostgreSQL is Enough" should not read as "You shouldn't need more than PostgreSQL" Metrics in an unlogged table could be great if you want to query those metrics against existing data. It depends
  • tutfbhuf 3y ago
    You can use the TimescaleDB PostgreSQL extension. It works really well and has a high compression rate for time series data, such as metrics. It can also downsample data. I have also tried InfluxDB, but you have to rewire your mental model to fit InfluxDB (it has its own query syntax and internal mechanism). They have versions v1, v2, and v3, and it's not clear which one to use, probably v2 at this point in time. It's kind of confusing and the state of development is very much in flux. I think TimescaleDB works well up to the single-digit terabyte range (according to various sources). If you need a solution in the multi-digit terabyte or petabyte range, then you probably need something like a distributed VictoriaMetrics setup. https://github.com/timescale/timescaledb https://github.com/timescale/timescaledb
  • hagen1778 3y ago
    > Would it be possible to do this in Postgres as well? Of course! The question is only in your requirements. Keeping a simple counter with limited cardinality should work just great. But nowadays monitoring is much more serious than that. For monitoring k8s clusters the average ingestion rate of metrics per second varies from 100K to 2Mil. I don't know if, resource-wise, it would be a right decision to use Postgres for storing this. So when requirements are high, and they are for real-time infrastructure and applications monitoring, it is better to consider something like ClickHouse (for people familiar with Postgres) or VictoriaMetrics (for people familiar with Prometheus).
• brightball 3y ago
  https://x.com/winsletts/status/1745837166465536093?s=20 https://x.com/winsletts/status/1745837166465536093?s=20
• api 3y ago
  No it's not, because it's very hard to set up in clustered HA environments. This is 2024. It should be possible to just add database nodes.
  • sgarland 3y ago
    It is possible, if you pay for it. You can do Multi-AZ Clustered Instances in RDS, where you get the benefits of Multi-AZ failover with traffic sharing. If you can run your own infra – at least on an EC2 level – you can do things like Citus [0] for Postgres, which is about as close to "just add database nodes" as you'll get. [0]: https://www.citusdata.com/ https://www.citusdata.com/
    • api 3y ago
      Very clunky compared with things like CockroachDB. Ultimately using something like Postgres in 2024 is just an on-ramp for expensive managed cloud database services, which is probably why it's promoted so much.
      • refset 3y ago
        > which is probably why it's promoted so much I think what you're actually observing is simply that Postgres is by far the most vendor-neutral DBMS (/API) available, and therefore the volume of conversation & marketing around it stacks up very disproportionately. In contrast, asides from MySQL all other DBMS options require getting invested in ~one company and relying entirely on the whims & fortunes of their commercial support organisation. A relevant article and comment thread: https://news.ycombinator.com/item?id=31425872 https://news.ycombinator.com/item?id=31425872
  • mjjp 3y ago
    YugabyteDB seems interesting, I have not personally used it but really considering switching to it from Couchbase. It's a fork of postgresql with distributed architecture, so you can add and remove nodes as you wish. And it's free if you self-host. If anyone has experience with YugabyteDB (or any other multi-master PostgreSQL like DBs please let me know!)
• KingOfCoders 3y ago
  I think so too [0] glad more and more people come to this realization. It reduces cognitive load tremendously. [0] https://www.amazingcto.com/postgres-for-everything/ https://www.amazingcto.com/postgres-for-everything/
• talkingtab 3y ago
  There is a lot here that I find useful. Mastering multiple technologies: html, node, JavaScript, SQL, CSS - grids and flex, blah, blah, blah is simply impossible. Developing a working set of knowledge is not. The problem is that you don't know what you should know. A working set raises another set of issues: how do the pieces relate? What is the proper role of each technology? This gist is extremely useful to me both in helping me to understand more of what I need to know. The article https://sive.rs/pg https://sive.rs/pg was very helpful as one way to think about how postgresql could and should fit in.
• code-faster 3y ago
  A response: https://codefaster.substack.com/p/postgres-is-enough-but-is-it-fast https://codefaster.substack.com/p/postgres-is-enough-but-is-...
• adamcharnock 3y ago
  Is anyone aware of a streams implementation for Postgresql? I see we can do simple queues, but how about a stream with multiple consumers / consumer groups (much like the Redis streams implementation)?
• philippemnoel 3y ago
  I'm one of the makers of ParadeDB, a modern alternative to Elasticsearch. We build Postgres extensions to do fast search (pg_bm25) and analytics (pg_analytics). I love Postgres. If you have a small workload, like a startup, it certainly makes sense to stay within Postgres as long as you can. The problem is, at scale, Postgres isn't the answer to everything. Each of the workloads one can put in Postgres start to grow into very specific requirements, you need to isolate systems to get independent scaling and resilience, etc. At this point, you need a stack of specialized solutions for each requirement, and that's where Postgres starts to no longer be enough. There is a movement to build a Postgres version of most components on the stack (we are a part of it), and that might be a world where you can use Postgres at scale for everything. But really, each solution becomes quite a bit more than Postgres, and I doubt there will be a Postgres-based solution for every component of the stack.
  • chasd00 3y ago
    what is "at scale"? Is there a specific metric or range of metrics that raises a flag to begin considering something else? For example, in the olden days when it was my problem, page load times were the metric. Once it got high enough you looked for the bottleneck, solved it, and waited. When the threshold was broken again you re-ran the same process. Is there an equivalent for postgres?
    • ndriscoll 3y ago
      This bugs me every time performance comes up. No one is ever concrete, so they can never be wrong. If Michael Jackson rose from the dead to host the Olympics opening ceremony and there were 2B tweets/second about it, then postgres on a single server isn't going to scale. A crud app with 5-digit requests/second? It can do that. I'm sure it can do a lot more, but I've only ever played with performance tuning on weak hardware. Visa is apparently capable of a 5-digit transaction throughput ("more than 65,000")[0] for a sense of what kind of system reaches even that scale. Their average throughput is more like 9k transctions/second[1]. [0] https://usa.visa.com/solutions/crypto/deep-dive-on-solana.html https://usa.visa.com/solutions/crypto/deep-dive-on-solana.ht... [1] PDF. 276.3B/year ~ 8.8k/s: https://usa.visa.com/dam/VCOM/global/about-visa/documents/aboutvisafactsheet.pdf https://usa.visa.com/dam/VCOM/global/about-visa/documents/ab...
• jbverschoor 3y ago
  Sure, but I love SQLite’s single file simplicity
• bun_terminator 3y ago
  As a hardcore c++ guy, I recently switched to a company heavily into databases. I never had contact to databases before. And I'd like to go one step further: Why databases? I come from an industry that heavily uses custom binary file formats. And I'm still bewildered by the world of databases. They seem to solve many issues on the surface, but not really in pratice. The heavy limitations on data types, the update disasters, the incompatibility between different SQL engines etc all make it seem like an awful idea. I get the interop benefits, and maybe with extreme data volumes. But for anything else, what's the point? Genuinely asking
  • treflop 3y ago
    Well a database is just a binary file format with an API attached on. Because having hundreds or thousands of concurrent file handles across a data center is kind of hard.
    • bun_terminator 3y ago
      ok I get that, but at least our applications usually only have one thing (one thread of one process on one machine) operating on the database. So maybe it's just our silly usecase
      • deleted 3y ago
        [deleted]
      • afandian 3y ago
        It's a narrow use-case that doesn't cover RDMS. SQLite fits it though.
        • bun_terminator 3y ago
          The first thing I did was ask why we don't use sqlite or at least postgres. The answer was that they are free and therefore we don't trust them. So Oracle it is. Which is bananas because our customers have to buy an oracle license, which is money that we don't get. Pretty wild
• jwoq9118 3y ago
  Supabase integrates a lot of the open source tools listed here. A Postgrs backend as a service offering. Just started using it. Very impressed.
• didip 3y ago
  Specifically with PostgreSQL, number of connections is still the killer (if you don’t have smart proxies). So you can’t be cavalier about putting as many use cases as possible. For example, when using PG pub/sub, you will run out of connections quick. Generally, all DBMS needs a smart self adjusting query killer. Without it, one bad query will ruin it for everyone.
  • cpursley 3y ago
    WalEx instead of pub/sub (listen/subscribe): https://github.com/cpursley/walex https://github.com/cpursley/walex Supavisor connection pooler: https://github.com/supabase/supavisor https://github.com/supabase/supavisor
• chasers 3y ago
  btw recently cleaned up my wal cache busting code quite a bit if you're interested. https://github.com/Logflare/logflare/blob/main/lib/logflare/context_cache.ex https://github.com/Logflare/logflare/blob/main/lib/logflare/... Need to make a lib out of this!!
  • cpursley 3y ago
    Thanks from one Elixir-using Chase to another ;)
• throwawaaarrgh 3y ago
  If you twist yourself into a pretzel enough you can make Bash do all these things. Doesn't make it a good idea. The biggest problem with depending on Postgres is it's a large complex monolith. Any problem you have only has two possible solutions: 1) spend a ton of time trying to twist yourself into a more pretzely shape to get it to do what you want, or 2) replace that thing you wanted with some external thing. Both of these waste valuable time on something that isn't providing any business value. They also are entirely preventable/avoidable, by simply not putting all your eggs into one basket. Basically the whole "just use Postgres" philosophy boils down to: I don't want to learn new things, and I just like making things from scratch with custom code. That's great if you're an engineer and you want job security. But it's bad strategy, engineering, and use of time and resources. Anyone who approves of using Postgres in this way should not be in charge of engineering decisions.
  • cpursley 3y ago
    > I don't want to learn new things I've learned a bunch of new things (running all the things - not fun, putting biz logic in the application layer - slow). Putting them in the database is simply simpler and more performant. And it requires less code and time to develop (and maintain).
  • int_19h 3y ago
    Given how things look in, say, the Node.js ecosystem, a large complex monolith has its attractions - chiefly that it doesn't fall apart at you, because it wasn't cobbled together with duck tape. It also has the benefit that it's a stable solution that has been around for decades, and will likely be around for just as long. Meaning that you won't have to constantly rewrite and update things to keep up with whatever the developers of your favorite framework decided is the Right Way to do things this year.
• redskyluan 3y ago
  Regarding scale, PostgreSQL may not cover all bases. Though I'm a PostgreSQL fan, I prefer specialized services for specific tasks. Using PG-based plugins could help, but a dedicated SQL-compatible database is often a better fit. For vector retrieval, going with a database like Milvus, designed for vectors, is usually more efficient and cost-effective. Similar principles apply across domains. Is there any vectordb under PG format? What if we've got a deeply customized distributed vector search service on PostgreSQL, that's impressive!
  • SparkyMcUnicorn 3y ago
    pgvector[0] is supported by AWS RDS, Azure, etc. I haven't gotten a chance to try out Latern[1] yet, but have heard some good things[2]. [0] https://github.com/pgvector/pgvector https://github.com/pgvector/pgvector [1] https://github.com/lanterndata/lantern https://github.com/lanterndata/lantern [2] https://tembo.io/blog/postgres-vector-search-pgvector-and-lantern https://tembo.io/blog/postgres-vector-search-pgvector-and-la...
• cynicalsecurity 3y ago
  This is really ridiculous
  • cpursley 3y ago
    Specifically how so?
    • cynicalsecurity 3y ago
      Imagine driving nails using a screwdriver instead of a nail gun or a hammer. This is how this article looks like to me.
• pissed_customer 3y ago
  Write me when you can change column order or type
  • cpursley 3y ago
    You can change type. But that's a good point regarding column order, sometimes I find myself wanting to do that.
  • cryptonector 3y ago
    Why would column order need changing? You have control over output column order in SELECT. Do not `SELECT *` in code.
• samtheprogram 3y ago
  I just wasted a lot of time debugging database triggers. I wish the ergonomics around some PostgreSQL features were better, but I do love the idea that it supports so much at the database level.
• tegdude 3y ago
  Has anyone made “I am Postgrenough” merch yet?
• LaGrange 3y ago
  I'm gonna go ahead and make a half-serious half-joking hot take: Actually you should be using an embedded database. Half-joking because there's like, tons of infrastructure, both literal and theoretical, that you're gonna miss out, and because I'm not sure we have on-disk standards (so less "good software" than "popular software") other than sqlite and libdb, both of which have some issues that make me hesitate before saying "use this." But often the reason you're ignoring 90% of your RDBMS is because you for some (good or bad) reason want it elsewhere, and the built-in stuff can even get in the way a bit. And this approach means you no longer have to worry about how to version the stored procedures or whatever. In a way, this is the same take as that gist, but inside out - instead of putting all your code inside Postgres, put Postgres inside your code.
  • cpursley 3y ago
    Are you familiar with Erlang's ETS? It's pretty neato: https://elixirschool.com/en/lessons/storage/ets https://elixirschool.com/en/lessons/storage/ets
    • LaGrange 3y ago
      No, but Erlang having something like that seems very in-character.
• hot_gril 3y ago
  I use Postgres for a lot, but I can't imagine using it to make HTTP requests.
  • Chiron1991 3y ago
    https://github.com/pramsey/pgsql-http https://github.com/pramsey/pgsql-http
    • cryptonector 3y ago
      No. (You don't want to cause blocking I/O in transactions.)
      • cpursley 3y ago
        Non-blocking: https://github.com/supabase/pg_net https://github.com/supabase/pg_net
        • hot_gril 3y ago
          Impressive!
          • cpursley 3y ago
            Yeah, still not sure I’d use it but a pretty interesting thing to do with the database.
    • hot_gril 3y ago
      That's what I was referring to, wondering if anyone uses it and why. It even says "Why This is a Bad Idea" at the bottom. Maybe for databases queried directly by users who also need to mix in API responses, and you're wrapping this all up in triggers or some other stored procedure cause you really want to use Postgres without some controller written in Python or JS.
• RockyMcNuts 3y ago
  Michael Stonebraker invented Postgres and has long said 'one size fits all' is an idea whose time has come and gone. https://cs.brown.edu/~ugur/fits_all.pdf https://cs.brown.edu/~ugur/fits_all.pdf the relational ACID model is overkill for mostly-read data warehouse and verticality helps; streaming is different; graph dbs are different. Postgres may not be 'all you need', it will take you pretty far though, maybe it's 'all you need most of the time'. 60%+ of the time it works every time.
• 0xbadcafebee 3y ago
  So, rather than use the right tool for the job, use one tool for everything.
  • int_19h 3y ago
    Use one tool that's known to be "good enough" for most things. If you run into actual perf issues, then look at something else.
  • rrr_oh_man 3y ago
    "I'm stuck in the Taiga and a hammer is all I have"
• mihaic 3y ago
  Almost every single time I've seen it was better to first prototype a new feature with the existing stack rather than introduce something new. And by careful curation, that initial prototype could be turned into production code with the same stack as well. At some point though the system does creek, and you're starting to wish you'd have a Redis or another specialized tool. The only important thing though for me is making sure I'd spend some time on writing a nice API wrapper on my end, and only call that. When I really need to use Redis, all that should be needed is changing the implementation inside your wrappers, and testing the migration very well. People are surprised how long you can delay to make a technical decision, until it becomes obvious.
• HermitX 3y ago
  I really like Postgres, but I still don't think Postgres can do everything. From my own experience, when the scale of data becomes very large, when you need to analyze massive amounts of data, your Postgres always seems somewhat inadequate. I think Postgres performs perfectly when dealing with OLTP-type workloads. However, if you need more OLAP support, I recommend you use StarRocks (www.starrocks.io). We now import data from Postgres into StarRocks for data analysis, and the experience is fantastic. Moreover, StarRocks also supports direct queries on data lakes, which is incredible.
• jurschreuder 3y ago
  The thing I'm missing in PG is jsonb compression the way mongo does it. We have both Mongo and PG. PG is much simpler and I would love to ditch Mongo for simplicity. Only thing I would need is a "dumb" compressed jsonb column. No updates, no queries, only insert, select, delete. And the same 80-90% compression without maintenance as Mongo on highly repetitive json keys.
• Rudism 3y ago
  I worked for years on a massive PostGraphile project where 99% of the backend application code was written as stored procedures and table triggers, and let me tell you, once the complexity of the system moves beyond basic CRUD operations this kind of system is an absolute chore to work with. The code base is extremely difficult to organize and manage, code completion and compile-time error safety is virtually non-existent, deploying new versions will probably be non-intuitive and confusing for everyone, training new programmers on the code is chaos... it is absolutely something I would never recommend to anyone under any circumstance. That being said, I do fully agree that things like Redis, ElasticSearch, RabbitMQ, and Kafka are probably unnecessary in a large number of projects that decide to add them to the stack if those projects are also already using PostgreSQL as a database.
  • cpursley 3y ago
    How does PostGraphile handle migrations? Hasura, a similar tool - has really nice migration tooling inspired by Rails. But your point is well taken, even with their nice migrations, I find myself struggling with changes to objects with dependencies (you have to drop all dependents and recreate which is a pita).
    • Rudism 3y ago
      PostGraphile didn't have any first-class migration solution back when we started using it. It looks like they have a `migrate` tool, but I can't speak to that since I've never used it. The way I set everything up, there's one schema containing just the bare tables and data, and then everything else (stuff that PostGraphile generates and all of our stored procedures with business logic) are in a separate schema. That way the migration process is a shell script that essentially 1. drops the entire non-table schema, 2. runs through all of the SQL files that define the application (there's some custom logic to control dependencies by looking for a `-- PRIORITY N` comment on the first line of the source files), 3. attach all the triggers to the tables (since they live in the schema that was dropped). It means we didn't have to worry about figuring out what all needs to be dropped to make a change because literally everything is dropped and recreated from scratch each time we deploy a new version. I'm speaking in present tense because this application still exists, we've just (more or less) frozen the code base and are slowly migrating all its functionality piecemeal into a ASP.NET Core WebAPI project.