5 ms·

OpenAI: Scaling PostgreSQL to the Next Level

190 points by thunderbong 1y ago

tux3 1y ago
Self-hosting postgres is appealing from a flexibility standpoint (don't be locked out of superuser or advanced features), but it sounds a little bit nerve wracking. I'm now hoping all the cloud providers read this article and start exposing the feature to try disabling an index in the query planner before you drop it for real, that should really become standard procedure. Just in case. But if you're a large scale company to the point of wanting to own and customize your stack, it can definitely make sense to self-host.
- gjvc 1y ago
  this sort of thing https://www.oracle.com/uk/autonomous-database/what-is-autonomous-database/ https://www.oracle.com/uk/autonomous-database/what-is-autono... ? "An autonomous database is a cloud database that uses machine learning to automate database tuning, security, backups, updates, and other routine management tasks traditionally performed by DBAs. Unlike a conventional database, an autonomous database performs all these tasks and more without human intervention."
  - tux3 1y ago
    That sounds like chaos engineering with extra steps! Have the AI randomly tweak your DB, and see if your infra holds up? :)
    - mike_hearn 1y ago
      It's not AI based. "Autonomous" is just the term Oracle uses to communicate that the database has a ton of self-management features, and that it was a big area of focus over time. If you use the recent versions it's a much less admin-heavy database than is conventional for an RDBMS. For example it can notice what queries are slow and add indexes for you, automatically. That's ordinary statistics and planning, GOFAI if you like. Disclosure: I have a part time job with Oracle in the research division, so I very much have conflicts of interest, but OpenAI should listen to me anyway (and this post is my own opinion, obviously). Postgres is great and I use it in personal projects, but the amount of effort our industry collectively puts into working around its limitations and problems is a bit concerning. According to the blog post, their "solutions" to the problems mostly involve just not using the database, which is no solution at all. They could just subscribe to a managed Oracle DB in Azure and every problem they're having would go away. The cost is surprisingly not much different to a managed Postgres. Specifically, in the current version: - Database nodes scale horizontally and elastically. There is not such a thing as a primary master and read replicas in an Oracle cluster, although you can have read-through cache nodes (they are not replicas). And because of the unusual way Oracle clusters are integrated with the hardware there also isn't such a thing as replication lag within the cluster: writes are fully synchronous even when scaling horizontally and reads are always up to date. - Database clusters are HA. Node lists are automatically distributed to client drivers. If a node fails or you do a rolling upgrade on the cluster, it's transparent to the client beyond a minor elevation in latency as they retry. You can not only do rolling upgrades but also roll them across major versions. - The MVCC engine in Postgres is strange and unique. Other databases don't have concepts like vacuuming or attendent table/index bloat. This isn't unique to Oracle, but nonetheless switching to something else means a massive productivity drain gone, just like that. - You can do schema changes and do them online. They say they don't allow users to create new tables which I'm sorry but that's ridiculous. It's not their fault but I'd consider a database to which you can't add tables to be basically broken. You can even do arbitrary schema changes online in Oracle because the db can create a new table and copy the data across to the new schema whilst doing incremental sync, then do an in-place rename at the end. That works under heavy load. - You can disable indexes, marking them as invisible to the planner whilst still maintaining them. - You can multiplex transactions onto sessions and do db-side connection pooling, without things like "bouncers", so idle connection management is less of an issue. - You can derive 95/99th percentile latencies from v$active_session_history because the wait times for every SQL statement are available. - You can audit schema changes along with everything else. On pricing, at these scales it just doesn't matter. An ExaData cluster might actually be cheaper. I knocked up an estimate using Azure's pricing calculator and the numbers they provide, assuming 5TB of data (under-estimate) and HA option. Even with a 1 year reservation @40% discount they'd be paying (list price) around $350k/month. For that amount you can rent a dedicated Oracle/ExaData cluster with 192 cores! That's got all kinds of fancy hardware optimizations like a dedicated intra-cluster replication network, RDMA between nodes, predicate pushdown etc. It's going to perform better, and have way more features that would relieve their operational headache. Even if it was a lot more expensive, OpenAI is opportunity-cost constrained right now, not capital constrained. All time their devs are finding complicated ways to not put things in their melting database is time lost to competitors.
      - joshsm5 1y ago
        Exadata would definitely be an option and it has it's pros (I use it at work but that decision wasn't mine). Some of the features you forgot to mention are: - The built in bloom filters for reducing number of blocks scanned for FTS - Oracle (not specific to Exadata) supports creating reverse key indexes to help address high write loads causing contention on indexes, Postgres you can use a reverse function to reverse a string but not the actual byte representation so doesn't work on int/bigint types Oracle doesn't come without it's drawbacks: - Oracle is infamous for their licensing costs and audits, TDE for example I believe is a separate licensing cost despite that fact that for most companies encryption as rest is required - You have to include the cost of DataGuard into your calculations for TCO because there isn't replication built in like Postgres provides for free to handle DR use cases - You have to include the cost of Oracle's point in time archiving backups for restoration in case of failure for TCO, the Postgres community has come up with a few solutions for this that are free. At this point in time, I wouldn't consider only daily backups acceptable. - Applications typically work with grapheme clusters, a user doesn't need nor care how many bytes the emojis they filled an input field is, you need to store it and user interfaces are going to enforce 1 emoji as 1 character generally. In existing databases clusters, a lot of places have not turned on extended strings so Oracle strugles to handle Unicode due to its limitation on VARCHAR2 of 4000 BYTES until you turn this on. - Oracle completely ignores SQL standards isolation levels, whereas Postgres adheres to them pretty well (ignoring repeatable read interpretations which happens with other RDBMS as well) > it can notice what queries are slow and add indexes for you, automatically This potentially can cause performance differences in DEV/STG/PROD where an index gets created in STG but then isn't there in PROD. Because of this possibility, it would violate the change management processes where I work but likely wouldn't be an issue for OpenAI. > Database nodes scale horizontally and elastically. There is not such a thing as a primary master and read replicas in an Oracle cluster This comes at the cost of the possibility of cluster waits, and I've seen them happen for 10 minute time period and are difficult to track down or optimize for in my experience. > Database clusters are HA. Node lists are automatically distributed to client drivers. If a node fails or you do a rolling upgrade on the cluster Everywhere I have worked does not use the full Oracle driver which is what I believe is required for this. Using the full client requires organization coordination that sometimes doesn't exist or won't exist because not every team wants to use Oracle and DevOps doesn't want to support the multitude of ways to use different database drivers. I primarily work in Java and I've only ever seen the JDBC thin client used which as far as I know doesn't support this, you can create connection strings with a list of servers for production with failover to a list of servers in another region designated as your DR database, but I don't believe they're dynamic. > You can multiplex transactions onto sessions and do db-side connection pooling This is HIGHLY dependent on what programming language/framework you're using. Spring Boot for example uses Hikari and generally avoids using the internal driver pool which likely prevents this. I also believe using the reactive drivers is required to do this in Java in general. Using this directly means you have a hard dependency to the specific database. While it's rare to change database software it can happen, my work is undergoing a switch from MSSQL to Oracle. I could be wrong on any of these, considering I have to use Oracle daily at work I'm more than happy to be wrong and learn something since I can apply it immediately.
- diggan 1y ago
  > but it sounds a little bit nerve wracking. As long as you're doing backups (you are doing backups, right?), and validate that those backups work (you are validating that those backups work, right?), what's making you nervous about it?
  - fipar 1y ago
    Backups with periodic restore validation (which is not trivial) are a must, but don’t make database maintenance any less nerve wracking. Sure, you won’t lose data, but the downtime …
  - mewpmewp2 1y ago
    Yeah, that's what is a bit odd to me. I feel like AWS and everything like that is much more of a black box compared to some things like Postgres that is so fully tested, proven to be reliable, etc.
  - tux3 1y ago
    I think managing stateless infrastructure is much easier, if anything goes haywire you can expect a readiness probe to fail, k8s quietly takes down the instance, and life continues with no downtime. It is also perfectly possible to roll your own highly-available Postgres setup, but that requires a whole another set of precise configuration, attention to details, caring about the hardware, occasionally digging into kernel bugs, and so forth that cloud providers happily handle behind the scene. I'm very comfortable with low-level details, but I have never built my own cloud. I do test my backups, but having to restore anything from backups means something has gone catastrophically wrong, I have downtime, and I probably have lost data. Everything to prevent that scenario is what's making me sweat a little bit
    - lossolo 1y ago
      > occasionally digging into kernel bugs No, it doesn't. I've been self-hosting a multi-node, highly available, and fault-tolerant PostgreSQL setup for years, and I've never had to go to that level. After reading your whole post, I'm not sure where you're getting your information from.
- lossolo 1y ago
  > but it sounds a little bit nerve wracking. > But if you're a large scale company ... it can definitely make sense to self-host. I'm not a large company like OpenAI and I've been running various PostgreSQL setups for years—ranging from single-node instances without replication to multi-node, fault-tolerant, highly available configurations with automatic failover and backups, serving 4-5 digits of updates, selects, and inserts per second. I'm not sure what you're referring to, but in my experience, once it's set up, it's almost maintenance-free.
  - hobs 1y ago
    No large scale database heterogeneous cluster is maintenance-free - plans change because data changes, query patterns change, resource utilization of certain features grows or shrinks, new application features are launched, new indexes need to be added, things are constantly migrated and might need a special case because the default migration strategy doesn't perfectly handle giant bulk changes, etc etc etc etc
    - lossolo 1y ago
      You need to add indexes on cloud-hosted instances too, perform migrations there as well, and launch new features in your application in both cases. Almost everything you're talking about has little to do with whether you're self-hosting or using a cloud provider. You still have to deal with most of it either way. EDIT: Don't get me wrong, I've also managed Kafka clusters, ClickHouse clusters, Elasticsearch clusters, etc. and I have my share of Zookeeper horror stories. Some of the tools I just mentioned are definitely not maintenance-free. But in my experience, you can't really compare PostgreSQL to them.
- whalesalad 1y ago
  self hosting psql is trivial - what is the scary part? thats how we used it for decades until things like RDS came around.
  - lossolo 1y ago
    Yeah, that's baffling to me too. I started self-hosting my databases with MySQL 21 years ago, before the cloud even existed.
    - edoceo 1y ago
      Before the cloud? More than 21 years ago that damn cloud icon was in every Visio network diagram.
      - dboreham 1y ago
        Before that it was on whiteboards.
      - lossolo 1y ago
        > Before the cloud? Yes, the cloud, in the modern sense (as in, on-demand scalable infrastructure like AWS), was just beginning to emerge back then, AWS launched S3 and EC2 in 2006 so 19 years ago. Other cloud services followed over the next several years.
      - dragonwriter 1y ago
        The popularity of the cloud icon in diagrams well precedes the term “cloud computing” being coined for on-demand scalable (usually, but not always, remote-hosted) infrastructure and services that accompanied the explosion of such services in about the mid-00s.
        edoceo 1y ago
        IME folk were saying move it to the cloud, with the meaning of a manged provider, before 2005.
  - 1y ago
- evanelias 1y ago
  > I'm now hoping all the cloud providers read this article and start exposing the feature to try disabling an index in the query planner before you drop it for real I'm surprised there isn't non-superuser DDL to handle this. For example in MySQL you can ALTER an index to make it INVISIBLE (or equivalently in MariaDB, IGNORED) and the planner won't use it.
- oa335 1y ago
  There are already several ways to force postgres to use or not use a particular index. We use cloud-hosted and managed Postgres instances on one of the major provider, and I've used a couple of these strategies to massage the query planner. 1. Fiddle with the Query Planner settings (this can be done on a per query level as well, so its not global). E.g. enable_indexscan=off, enable_indexonlyscan=off 2. Add a trivial calculation to the filter clause of the query. E.g. select * from table where indexed_col + 0 = 12345 shouldn't use the index, as the planner won't do the arithmetic. 3. Use the pg_hintplan extension, which allows you to add comments to your code to urge the planner to use certain data access strategies, including specifying which indices to use. See: https://pg-hint-plan.readthedocs.io/en/latest/hint_table.html#hints-for-scan-methods https://pg-hint-plan.readthedocs.io/en/latest/hint_table.htm...
  - bostik 1y ago
    That second trick is neat, I'll need to remember that. I also really wish I had known about it back in 2015, when we had to massage an inconveniently big (at the time) PG database and started to carve it out into smaller, purpose-specific instances. Being able to verify that an index was either useless or inefficient without jumping through hoops would have saved quite a lot of time.
- cryptonector 1y ago
  > Self-hosting ~~postgres~~ is appealing from a flexibility standpoint [...]
- affandar 1y ago
  (Disclosure: I'm from Azure's Postgres team) Clarification: OpenAI does not self-host Postgres. They use Azure's managed PostgreSQL offering (aka Azure Database for PostgreSQL Flexible server).
- bohanoai 1y ago
  Speaker here — Bohan from OpenAI. Just to clarify, we're using Azure Database for PostgreSQL, not a self-hosted setup. I mentioned "Azure Postgres" multiple times during the talk, but I should have been more specific that we're referring to Microsoft’s managed PostgreSQL service. Apologies for the confusion.
  - tux3 1y ago
    Sorry, I see this has caused some confusion, I should have clarified in my comment. I was responding to the section by Lao Feng at the end, where their conclusion tries to make the case for Pigsty and self-hosted pg.
williamdclt 1y ago
Not super interesting, this is fairly basic stuff that you'll encounter at orders of magnitude smaller scale than OpenAI. Creating indexes CONCURRENTLY, avoiding table rewrites, smoothing out traffic, tx timeouts, read replicas... It's pretty much table stakes, even at 10000x smaller scale. Their requests to Postgres devs aren't anything new either, everyone has wished for it for years. The title is kind of misleading: they're not scaling it to the "next level", they're clearly struggling with this single-master setup and trying to keep it afloat while migrating off ("no new workloads allowed"). The main "next scale" point is that they say they can "scale gracefully under massive read loads" - nothing new, that's the whole point of read replicas and horizontal scaling. Re: "Lao Feng Q&A": > PostgreSQL actually does have a feature to disable indexes. You can simply set the indisvalid field to false in the pg_index system catalog [...] It’s not black magic. No. It's not documented for this use, so it's not a feature. It's fooling around with internals without guarantees of what this will do (it might do what you want today, it might not in the next release). Plus as they point out, managed Postgres providers don't let you fiddle with this stuff (for good reasons, as this is not a feature). > there’s a simpler solution [to avoiding accidental deletion of used indexes]: just confirm via monitoring views that the index is not being used on either primary or replicas That doesn't quite solve all the same problems. It's quite frequent that an index is in use, but is not _needed_: another index would also work (eg you introduced a new index covering an extra column that's not used in this query). Being able to disable an index would allow checking that the query plan does use the other index, rather than praying and hoping.
- jfim 1y ago
  I'm pretty perplexed as well. They mention that they're not sharding PostgreSQL, without mentioning why in the article, but isn't that an obvious issue to many of their scaling problems? I don't really see what it is that they're doing that requires a single master database, it seems that sharding on a per user basis would make things way easier for them.
  - yen223 1y ago
    I can sympathise with wanting to stay on a single master architecture as long as possible. Sharding is a major step change in terms of maintenance burden.
belter 1y ago
Many of these scaling issues would be solved, if they simply used AWS and Aurora Postgres. https://pages.cs.wisc.edu/~yxy/cs764-f20/papers/aurora-sigmod-17.pdf https://pages.cs.wisc.edu/~yxy/cs764-f20/papers/aurora-sigmo... https://youtu.be/pUqVCK7Ggh0 https://youtu.be/pUqVCK7Ggh0
- v5o 1y ago
  [dead]
- belter 1y ago
  Surely someone can articulate the flaw...Unless, of course, there isn’t one worth mentioning....
  - cbg0 1y ago
    They're deployed on Azure and have a deep partnership with Microsoft, so they can't "simply" use a different cloud. Also, recommending a black box managed solution isn't an option for some large companies that have their own hardware & datacenters and which may want to use open source solutions they can easily deploy, fork and support themselves to keep costs under control.
    - belter 1y ago
      They are one of the most well capitalized company/startup/foundation/non-profit in the planet and just spent 6,5 billion to hire a designer. They should be using the best technical and cheapest solution, and they owe it to their investors. At their scale they will never be able to use anything else than a cloud solution. They could solve these issues at the number of users they report, for a monthly bill below 25 million dollars. "6,311 database instances running the PostgreSQL-compatible and MySQL-compatible editions of Amazon Aurora processed more than 376 billion transactions, stored 2,978 terabytes of data, and transferred 913 terabytes of data" - https://aws.amazon.com/blogs/aws/how-aws-powered-prime-day-2024-for-record-breaking-sales/ https://aws.amazon.com/blogs/aws/how-aws-powered-prime-day-2...
kabes 1y ago
"He finally concluded with some requests to the postgres developer community" ... You're one of the most well funded companies in the world, you shouldn't be asking for features to open aource devs, but you should be opening PRs
- onion2k 1y ago
  That assumes that every problem can be solved with money, and that the money (or the PRs the money funds) will be welcomed by the community. By talking about what OpenAI needs Bohan is hopefully starting a conversation with the community in order to engage with everyone respectfully and to work with them. Railroading an open source project with money or dev time in order to force it to go in the direction you want is not the right way. Those things should be available if the community asks, but they shouldn't be the opening offer.
  - deadbabe 1y ago
    It’s open source, you make your own fork and move on with or without the community.
    - airstrike 1y ago
      OK but then you lose the benefit of all future upstream changes and now you have to manage constant rebasing Engaging the community and intelligently advocating for improvements is a way to contribute to projects as well, especially if you're willing to use a disposable forks to explore the design space, put forth RFCs, PRs, etc.
      - WesolyKubeczek 1y ago
        > OK but then you lose the benefit of all future upstream changes and now you have to manage constant rebasing I thought they were swimming in enough money to hire someone to do the rebasing. Or dogfood their models to do the same.
      - deadbabe 1y ago
airstrike 1y ago
Title should really be "Scaling PostgreSQL to the Next Level at OpenAI", which is the actual title of the talk
vb-8448 1y ago
I wonder how much their performance can improve if they put the write-instance on dedicated servers (with local and very fast ssd) and use managed services only for read-replicas.
davidkuennen 1y ago
They seem to be using physical replication. I'm currently thinking of switching to logical replication to reduce inter region egress cost. Do you think that's a good idea? There seems to be many improvements to native logical replication since Postgres 17.
VWWHFSfQ 1y ago
> The presentation also specifically mentioned that using ORM can easily lead to inefficient queries and should be used cautiously. Every ORM is bad. Especially the "any DB" ORMs. Because they trick you into thinking about your data patterns in terms of writing application code, instead of writing code for the database. And most of the time their features and APIs are abstracted in a way that basically means you can only use the least-common-denominator features of all the database backends that they can support. I've sworn off ORMs entirely. My application is a Postgres application first and foremost. I use PG-specific features extensively. Why would I sacrifice all the power that Postgres offers me just for some conveniences in Python, or Ruby, or whatever? Nah. Just write good SQL for your database and the whole system will be very happy.
- bwfan123 1y ago
  At one time we had to switch our service out of DB2 into psql. We had to do this with minimum downtime !! ORM helped us since it abstracted the DB (to the orm the sql dialect was a plugin). The application was largely untouched. Also, not all developers can roll their own sqls and sqls embedded in code make it harder to refactor or reason with and eventually sqls will be abstracted into a crud library of sorts anyway.
  - sgarland 1y ago
    > Also, not all developers can roll their own sqls If you can't write SQL, don't use an RDBMS until you learn it. This sounds like gatekeeping because it is: I don't understand why so many people are willing to accept that they need to know their main language to use it, but not that they need to know SQL to use it.
- jilles 1y ago
  I've been using the Django ORM for years and it feels like an amazing piece of software. But recently I started using sqlc. Which turns my queries into Go (simplification). I think this is actually the sweet spot between ORM and rawdogging SQL.
  - 1y ago
bhouston 1y ago
Argh. Shard the damn database already. Why are they not sharing by user/org yet? It is so simple and would fix the primary issue they are running into. All these work arounds they go through to avoid a straight forward fix.
- iampims 1y ago
  Not sure I would qualify sharding a DB that get 1M qps as straight forward. I agree with you that it seems that an org would be a natural sharding key, but we know that at this scale, nothing really is ever straight forward, especially when it's your first rodeo.
  - evanelias 1y ago
    That's true, but that's also why you really should shard long before hitting that point... If your company is growing at this insane rate, it should be obvious that eventually you must shard. And the longer you delay this, the more painful it will be to accomplish.
  - bhouston 1y ago
    > Not sure I would qualify sharding a DB that get 1M qps as straight forward. Sharding at the application layer (basically figure out the shard from org/user in your application code prior to interacting with the DB), will scale to any QPS rate. This is what I was referring to.
- samwillis 1y ago
  The message of the talk was very much that you can scale to massive throughput without having to shard and having only a single master. Of course they considered it, but the tradeoffs didn't match what they wanted to do - plus they found you could scale to this level without sharding.
  - vanviegen 1y ago
    The talk seems to be mostly about all the limitations and workarounds they've had to deal with, because they choose not to shard. Apparently, they have a policy of adding no new functionality to the database, which presumably means additional separate database services being setup for each new feature. That sounds a lot like accumulating tech debt very rapidly, just because sharding is not on the table, for whatever reason.
samwillis 1y ago
I was at PGConf last week, and this was one of the most packed talks - a great insight into using Postgres, where most of the conference was fairly inward facing, with talks around the development of Postgres itself (pgconf.dev is very much that one, out of all the others each year). What you have to remember is that for many teams, when their product takes off, they are not equipped with the deep internal knowledge of how to scale a particular part of their stack. This was an awesome story from a small team having to tackle those challenges, and how they were learning as they went. So, while there are some of those "can't you just", and "whats interesting about this?" comments here, with the narrative of the growth rate, and the very high profile of the product, it was the perfect user talk for a internal development focused conference. The key insight, and main message, of the talk was that if you are not too write heavy you can scale Postgres to very high read throughput with read replicas and only a single master! That is exactly the message that needs to be spelled out as that covers the vast majority of apps. As an observation, in the Q&A at the end of the talk the questions, primarily from core Postgres developers, were focused on learning about the use case, and not an opportunity to suggest that they were doing anything wrong (not quite the same as this thread could get). A genuinely awesome group of very friendly and welcoming people in the Postgres community.
- krembo 1y ago
  Do you know where I can find the slides/records of the talks?
  - samwillis 1y ago
    I believe the videos of the talks will be published at some point - I think "a few weeks" was mentioned.
- Swizec 1y ago
  > if you are not too write heavy you can scale Postgres to very high read throughput with read replicas and only a single master The number of interviewees (I do the sys design question) who want to jump straight into massively distributed eventually consistent complicated systems for 5 reads/second is too damn high. 1,000,000 users is not a lot. I wish we did better with teaching folks that while we (as an industry) were focused on horizontal this and that, computers got fast. And huge. Amazon will rent you a 32TB RAM server these days. Your database will scale just fine and ACID is far too valuable to throw.
cryptonector 1y ago
> Feature Requests > Concerning schema changes: they desire PostgreSQL to record a history of schema change events, such as adding or removing columns and other DDL operations. You can do this right now today by using `EVENT TRIGGER`s. You can check out things like Aquameta[0] (if I remember correctly) to see how it's done. [0] https://github.com/aquametalabs/aquameta https://github.com/aquametalabs/aquameta
- weitendorf 1y ago
  We are building similar functionality for our own postgres setup right now. Of course, postgres is very powerful and you can implement anything like this in many different ways. But at the same time, maintaining DDL history and tracking major changes to the database is a very common requirement, and unfortunately many people don't realize that until they learned that lesson the hard way. Relatedly are not DDL changes per-se, but big/important db operations that you want to also keep a record of so that you can look back and understand why something changed. I am not sure if this is the right term, but basically when we update our pricing model or skus, or set custom pricing for someone, we want those updates to be "auditable". Actually, I think this is a relatively common use case too: a fully relational model often leaves you with a large number of "static" tables that only change when you're making updates to your application. They support the smaller number of big, important, dynamic tables that your application is regularly modifying. If you had the foresight to recognize that you'd likely need to change those static tables in the future, you probably organized them so you could do so by adding new rows. It is not quite a DDL change but it is a big, risky change to your application logic that only happens rarely, and you basically just want to keep a list of all those big changes in case things get messed up or you find yourself unable to make sense of older data.
  - cryptonector 1y ago
    There's lots of extensions that automatically create audit tables and record history in them. The trick is to a) create audit tables for all _existing_ tables, b) create an event trigger so you can have audit tables created automatically for any future new tables. Here's an example of how to do it: https://github.com/twosigma/postgresql-contrib/blob/master/audit.sql https://github.com/twosigma/postgresql-contrib/blob/master/a... Another thing you might do is to go with a schema that follows the event shipping pattern. In this pattern you have the "truth" held in insert-only tables (deletes and updates not allowed), then turn those "event" tables into ones you can query naturally using VIEWs, MATERIALIZED VIEWs, or live tables that you update with triggers on the event tables. Then your event tables _are your history/audit_ tables.
philosophty 1y ago
OpenAI and these companies hires inexperienced people with zero operational experience and this is how they run things. It's almost funny if you didn't see how unreliable the end result was. Postgres is powerful but just not suited for this role. But if your only tool is a hammer...
- phillipcarter 1y ago
  OpenAI and these companies do nothing of the sort.
  - deleted 1y ago
    [deleted]
  - philosophty 1y ago
    Yes, they absolutely do and this is an example of it.
    - phillipcarter 1y ago
      I would advise actually clicking through to see who the speaker is, who is categorically not a new grad nor someone with no experience in this domain.
      - philosophty 1y ago
        I don't want to single anyone out but if you think this is "expert" level experience you don't have the relevant experience yourself. I actually don't want to be overly critical but I do find the arrogance of these companies annoying.
        phillipcarter 1y ago
        Based on your replies I have zero reason to believe you know any better at all, considering the false statements you've already made (trivially proven, no less!) and lack of any meaningful critique of the source material.
redwood 1y ago
What I find odd about this is there's no mention of all the other engines that must be in the mix powering different types of queries: I have no doubt they're using a little of everything, from scaling key-value to search, vector search, caches... They must be doing summersaults to avoid over-saturating this over-saturated Postgres env... yet only Postgres is discussed here.
harisund1990 1y ago
Seems like a perfect use case for a distributed database like YugabyteDB. Have you looked into it?
venriq 1y ago
I don't deny how powerful PostgreSQL is, but it appears that the decision to choose PostgreSQL was the most significant issue all along. It's shocking how often we find teams selecting Postgres to implement a solution that it's not suitable for. Yet, you see a document with a forced and non-logical narrative trying to justify the decision. With the NewSQL options available today, which provide distributed relational databases with multi-masters out of the box, it seems to me that many teams select Postgres simply because that's all they know, and that's the source of the problem.

190 points by thunderbong 1y ago

tux3 1y ago

Self-hosting postgres is appealing from a flexibility standpoint (don't be locked out of superuser or advanced features), but it sounds a little bit nerve wracking. I'm now hoping all the cloud providers read this article and start exposing the feature to try disabling an index in the query planner before you drop it for real, that should really become standard procedure. Just in case. But if you're a large scale company to the point of wanting to own and customize your stack, it can definitely make sense to self-host.

gjvc 1y ago
this sort of thing https://www.oracle.com/uk/autonomous-database/what-is-autonomous-database/ https://www.oracle.com/uk/autonomous-database/what-is-autono... ? "An autonomous database is a cloud database that uses machine learning to automate database tuning, security, backups, updates, and other routine management tasks traditionally performed by DBAs. Unlike a conventional database, an autonomous database performs all these tasks and more without human intervention."
- tux3 1y ago
  That sounds like chaos engineering with extra steps! Have the AI randomly tweak your DB, and see if your infra holds up? :)
  - mike_hearn 1y ago
    It's not AI based. "Autonomous" is just the term Oracle uses to communicate that the database has a ton of self-management features, and that it was a big area of focus over time. If you use the recent versions it's a much less admin-heavy database than is conventional for an RDBMS. For example it can notice what queries are slow and add indexes for you, automatically. That's ordinary statistics and planning, GOFAI if you like. Disclosure: I have a part time job with Oracle in the research division, so I very much have conflicts of interest, but OpenAI should listen to me anyway (and this post is my own opinion, obviously). Postgres is great and I use it in personal projects, but the amount of effort our industry collectively puts into working around its limitations and problems is a bit concerning. According to the blog post, their "solutions" to the problems mostly involve just not using the database, which is no solution at all. They could just subscribe to a managed Oracle DB in Azure and every problem they're having would go away. The cost is surprisingly not much different to a managed Postgres. Specifically, in the current version: - Database nodes scale horizontally and elastically. There is not such a thing as a primary master and read replicas in an Oracle cluster, although you can have read-through cache nodes (they are not replicas). And because of the unusual way Oracle clusters are integrated with the hardware there also isn't such a thing as replication lag within the cluster: writes are fully synchronous even when scaling horizontally and reads are always up to date. - Database clusters are HA. Node lists are automatically distributed to client drivers. If a node fails or you do a rolling upgrade on the cluster, it's transparent to the client beyond a minor elevation in latency as they retry. You can not only do rolling upgrades but also roll them across major versions. - The MVCC engine in Postgres is strange and unique. Other databases don't have concepts like vacuuming or attendent table/index bloat. This isn't unique to Oracle, but nonetheless switching to something else means a massive productivity drain gone, just like that. - You can do schema changes and do them online. They say they don't allow users to create new tables which I'm sorry but that's ridiculous. It's not their fault but I'd consider a database to which you can't add tables to be basically broken. You can even do arbitrary schema changes online in Oracle because the db can create a new table and copy the data across to the new schema whilst doing incremental sync, then do an in-place rename at the end. That works under heavy load. - You can disable indexes, marking them as invisible to the planner whilst still maintaining them. - You can multiplex transactions onto sessions and do db-side connection pooling, without things like "bouncers", so idle connection management is less of an issue. - You can derive 95/99th percentile latencies from v$active_session_history because the wait times for every SQL statement are available. - You can audit schema changes along with everything else. On pricing, at these scales it just doesn't matter. An ExaData cluster might actually be cheaper. I knocked up an estimate using Azure's pricing calculator and the numbers they provide, assuming 5TB of data (under-estimate) and HA option. Even with a 1 year reservation @40% discount they'd be paying (list price) around $350k/month. For that amount you can rent a dedicated Oracle/ExaData cluster with 192 cores! That's got all kinds of fancy hardware optimizations like a dedicated intra-cluster replication network, RDMA between nodes, predicate pushdown etc. It's going to perform better, and have way more features that would relieve their operational headache. Even if it was a lot more expensive, OpenAI is opportunity-cost constrained right now, not capital constrained. All time their devs are finding complicated ways to not put things in their melting database is time lost to competitors.
    - joshsm5 1y ago
      Exadata would definitely be an option and it has it's pros (I use it at work but that decision wasn't mine). Some of the features you forgot to mention are: - The built in bloom filters for reducing number of blocks scanned for FTS - Oracle (not specific to Exadata) supports creating reverse key indexes to help address high write loads causing contention on indexes, Postgres you can use a reverse function to reverse a string but not the actual byte representation so doesn't work on int/bigint types Oracle doesn't come without it's drawbacks: - Oracle is infamous for their licensing costs and audits, TDE for example I believe is a separate licensing cost despite that fact that for most companies encryption as rest is required - You have to include the cost of DataGuard into your calculations for TCO because there isn't replication built in like Postgres provides for free to handle DR use cases - You have to include the cost of Oracle's point in time archiving backups for restoration in case of failure for TCO, the Postgres community has come up with a few solutions for this that are free. At this point in time, I wouldn't consider only daily backups acceptable. - Applications typically work with grapheme clusters, a user doesn't need nor care how many bytes the emojis they filled an input field is, you need to store it and user interfaces are going to enforce 1 emoji as 1 character generally. In existing databases clusters, a lot of places have not turned on extended strings so Oracle strugles to handle Unicode due to its limitation on VARCHAR2 of 4000 BYTES until you turn this on. - Oracle completely ignores SQL standards isolation levels, whereas Postgres adheres to them pretty well (ignoring repeatable read interpretations which happens with other RDBMS as well) > it can notice what queries are slow and add indexes for you, automatically This potentially can cause performance differences in DEV/STG/PROD where an index gets created in STG but then isn't there in PROD. Because of this possibility, it would violate the change management processes where I work but likely wouldn't be an issue for OpenAI. > Database nodes scale horizontally and elastically. There is not such a thing as a primary master and read replicas in an Oracle cluster This comes at the cost of the possibility of cluster waits, and I've seen them happen for 10 minute time period and are difficult to track down or optimize for in my experience. > Database clusters are HA. Node lists are automatically distributed to client drivers. If a node fails or you do a rolling upgrade on the cluster Everywhere I have worked does not use the full Oracle driver which is what I believe is required for this. Using the full client requires organization coordination that sometimes doesn't exist or won't exist because not every team wants to use Oracle and DevOps doesn't want to support the multitude of ways to use different database drivers. I primarily work in Java and I've only ever seen the JDBC thin client used which as far as I know doesn't support this, you can create connection strings with a list of servers for production with failover to a list of servers in another region designated as your DR database, but I don't believe they're dynamic. > You can multiplex transactions onto sessions and do db-side connection pooling This is HIGHLY dependent on what programming language/framework you're using. Spring Boot for example uses Hikari and generally avoids using the internal driver pool which likely prevents this. I also believe using the reactive drivers is required to do this in Java in general. Using this directly means you have a hard dependency to the specific database. While it's rare to change database software it can happen, my work is undergoing a switch from MSSQL to Oracle. I could be wrong on any of these, considering I have to use Oracle daily at work I'm more than happy to be wrong and learn something since I can apply it immediately.
diggan 1y ago
> but it sounds a little bit nerve wracking. As long as you're doing backups (you are doing backups, right?), and validate that those backups work (you are validating that those backups work, right?), what's making you nervous about it?
- fipar 1y ago
  Backups with periodic restore validation (which is not trivial) are a must, but don’t make database maintenance any less nerve wracking. Sure, you won’t lose data, but the downtime …
- mewpmewp2 1y ago
  Yeah, that's what is a bit odd to me. I feel like AWS and everything like that is much more of a black box compared to some things like Postgres that is so fully tested, proven to be reliable, etc.
- tux3 1y ago
  I think managing stateless infrastructure is much easier, if anything goes haywire you can expect a readiness probe to fail, k8s quietly takes down the instance, and life continues with no downtime. It is also perfectly possible to roll your own highly-available Postgres setup, but that requires a whole another set of precise configuration, attention to details, caring about the hardware, occasionally digging into kernel bugs, and so forth that cloud providers happily handle behind the scene. I'm very comfortable with low-level details, but I have never built my own cloud. I do test my backups, but having to restore anything from backups means something has gone catastrophically wrong, I have downtime, and I probably have lost data. Everything to prevent that scenario is what's making me sweat a little bit
  - lossolo 1y ago
    > occasionally digging into kernel bugs No, it doesn't. I've been self-hosting a multi-node, highly available, and fault-tolerant PostgreSQL setup for years, and I've never had to go to that level. After reading your whole post, I'm not sure where you're getting your information from.
lossolo 1y ago
> but it sounds a little bit nerve wracking. > But if you're a large scale company ... it can definitely make sense to self-host. I'm not a large company like OpenAI and I've been running various PostgreSQL setups for years—ranging from single-node instances without replication to multi-node, fault-tolerant, highly available configurations with automatic failover and backups, serving 4-5 digits of updates, selects, and inserts per second. I'm not sure what you're referring to, but in my experience, once it's set up, it's almost maintenance-free.
- hobs 1y ago
  No large scale database heterogeneous cluster is maintenance-free - plans change because data changes, query patterns change, resource utilization of certain features grows or shrinks, new application features are launched, new indexes need to be added, things are constantly migrated and might need a special case because the default migration strategy doesn't perfectly handle giant bulk changes, etc etc etc etc
  - lossolo 1y ago
    You need to add indexes on cloud-hosted instances too, perform migrations there as well, and launch new features in your application in both cases. Almost everything you're talking about has little to do with whether you're self-hosting or using a cloud provider. You still have to deal with most of it either way. EDIT: Don't get me wrong, I've also managed Kafka clusters, ClickHouse clusters, Elasticsearch clusters, etc. and I have my share of Zookeeper horror stories. Some of the tools I just mentioned are definitely not maintenance-free. But in my experience, you can't really compare PostgreSQL to them.
whalesalad 1y ago
self hosting psql is trivial - what is the scary part? thats how we used it for decades until things like RDS came around.
- lossolo 1y ago
  Yeah, that's baffling to me too. I started self-hosting my databases with MySQL 21 years ago, before the cloud even existed.
  - edoceo 1y ago
    Before the cloud? More than 21 years ago that damn cloud icon was in every Visio network diagram.
    - dboreham 1y ago
      Before that it was on whiteboards.
    - lossolo 1y ago
      > Before the cloud? Yes, the cloud, in the modern sense (as in, on-demand scalable infrastructure like AWS), was just beginning to emerge back then, AWS launched S3 and EC2 in 2006 so 19 years ago. Other cloud services followed over the next several years.
    - dragonwriter 1y ago
      The popularity of the cloud icon in diagrams well precedes the term “cloud computing” being coined for on-demand scalable (usually, but not always, remote-hosted) infrastructure and services that accompanied the explosion of such services in about the mid-00s.
      - edoceo 1y ago
        IME folk were saying move it to the cloud, with the meaning of a manged provider, before 2005.
- 1y ago
evanelias 1y ago
> I'm now hoping all the cloud providers read this article and start exposing the feature to try disabling an index in the query planner before you drop it for real I'm surprised there isn't non-superuser DDL to handle this. For example in MySQL you can ALTER an index to make it INVISIBLE (or equivalently in MariaDB, IGNORED) and the planner won't use it.
oa335 1y ago
There are already several ways to force postgres to use or not use a particular index. We use cloud-hosted and managed Postgres instances on one of the major provider, and I've used a couple of these strategies to massage the query planner. 1. Fiddle with the Query Planner settings (this can be done on a per query level as well, so its not global). E.g. enable_indexscan=off, enable_indexonlyscan=off 2. Add a trivial calculation to the filter clause of the query. E.g. select * from table where indexed_col + 0 = 12345 shouldn't use the index, as the planner won't do the arithmetic. 3. Use the pg_hintplan extension, which allows you to add comments to your code to urge the planner to use certain data access strategies, including specifying which indices to use. See: https://pg-hint-plan.readthedocs.io/en/latest/hint_table.html#hints-for-scan-methods https://pg-hint-plan.readthedocs.io/en/latest/hint_table.htm...
- bostik 1y ago
  That second trick is neat, I'll need to remember that. I also really wish I had known about it back in 2015, when we had to massage an inconveniently big (at the time) PG database and started to carve it out into smaller, purpose-specific instances. Being able to verify that an index was either useless or inefficient without jumping through hoops would have saved quite a lot of time.
cryptonector 1y ago
> Self-hosting ~~postgres~~ is appealing from a flexibility standpoint [...]
affandar 1y ago
(Disclosure: I'm from Azure's Postgres team) Clarification: OpenAI does not self-host Postgres. They use Azure's managed PostgreSQL offering (aka Azure Database for PostgreSQL Flexible server).
bohanoai 1y ago
Speaker here — Bohan from OpenAI. Just to clarify, we're using Azure Database for PostgreSQL, not a self-hosted setup. I mentioned "Azure Postgres" multiple times during the talk, but I should have been more specific that we're referring to Microsoft’s managed PostgreSQL service. Apologies for the confusion.
- tux3 1y ago
  Sorry, I see this has caused some confusion, I should have clarified in my comment. I was responding to the section by Lao Feng at the end, where their conclusion tries to make the case for Pigsty and self-hosted pg.

williamdclt 1y ago

Not super interesting, this is fairly basic stuff that you'll encounter at orders of magnitude smaller scale than OpenAI. Creating indexes CONCURRENTLY, avoiding table rewrites, smoothing out traffic, tx timeouts, read replicas... It's pretty much table stakes, even at 10000x smaller scale. Their requests to Postgres devs aren't anything new either, everyone has wished for it for years. The title is kind of misleading: they're not scaling it to the "next level", they're clearly struggling with this single-master setup and trying to keep it afloat while migrating off ("no new workloads allowed"). The main "next scale" point is that they say they can "scale gracefully under massive read loads" - nothing new, that's the whole point of read replicas and horizontal scaling. Re: "Lao Feng Q&A": > PostgreSQL actually does have a feature to disable indexes. You can simply set the indisvalid field to false in the pg_index system catalog [...] It’s not black magic. No. It's not documented for this use, so it's not a feature. It's fooling around with internals without guarantees of what this will do (it might do what you want today, it might not in the next release). Plus as they point out, managed Postgres providers don't let you fiddle with this stuff (for good reasons, as this is not a feature). > there’s a simpler solution [to avoiding accidental deletion of used indexes]: just confirm via monitoring views that the index is not being used on either primary or replicas That doesn't quite solve all the same problems. It's quite frequent that an index is in use, but is not _needed_: another index would also work (eg you introduced a new index covering an extra column that's not used in this query). Being able to disable an index would allow checking that the query plan does use the other index, rather than praying and hoping.

jfim 1y ago
I'm pretty perplexed as well. They mention that they're not sharding PostgreSQL, without mentioning why in the article, but isn't that an obvious issue to many of their scaling problems? I don't really see what it is that they're doing that requires a single master database, it seems that sharding on a per user basis would make things way easier for them.
- yen223 1y ago
  I can sympathise with wanting to stay on a single master architecture as long as possible. Sharding is a major step change in terms of maintenance burden.

belter 1y ago

Many of these scaling issues would be solved, if they simply used AWS and Aurora Postgres. https://pages.cs.wisc.edu/~yxy/cs764-f20/papers/aurora-sigmod-17.pdf https://pages.cs.wisc.edu/~yxy/cs764-f20/papers/aurora-sigmo... https://youtu.be/pUqVCK7Ggh0 https://youtu.be/pUqVCK7Ggh0

v5o 1y ago
[dead]
belter 1y ago
Surely someone can articulate the flaw...Unless, of course, there isn’t one worth mentioning....
- cbg0 1y ago
  They're deployed on Azure and have a deep partnership with Microsoft, so they can't "simply" use a different cloud. Also, recommending a black box managed solution isn't an option for some large companies that have their own hardware & datacenters and which may want to use open source solutions they can easily deploy, fork and support themselves to keep costs under control.
  - belter 1y ago
    They are one of the most well capitalized company/startup/foundation/non-profit in the planet and just spent 6,5 billion to hire a designer. They should be using the best technical and cheapest solution, and they owe it to their investors. At their scale they will never be able to use anything else than a cloud solution. They could solve these issues at the number of users they report, for a monthly bill below 25 million dollars. "6,311 database instances running the PostgreSQL-compatible and MySQL-compatible editions of Amazon Aurora processed more than 376 billion transactions, stored 2,978 terabytes of data, and transferred 913 terabytes of data" - https://aws.amazon.com/blogs/aws/how-aws-powered-prime-day-2024-for-record-breaking-sales/ https://aws.amazon.com/blogs/aws/how-aws-powered-prime-day-2...

kabes 1y ago

"He finally concluded with some requests to the postgres developer community" ... You're one of the most well funded companies in the world, you shouldn't be asking for features to open aource devs, but you should be opening PRs

onion2k 1y ago
That assumes that every problem can be solved with money, and that the money (or the PRs the money funds) will be welcomed by the community. By talking about what OpenAI needs Bohan is hopefully starting a conversation with the community in order to engage with everyone respectfully and to work with them. Railroading an open source project with money or dev time in order to force it to go in the direction you want is not the right way. Those things should be available if the community asks, but they shouldn't be the opening offer.
- deadbabe 1y ago
  It’s open source, you make your own fork and move on with or without the community.
  - airstrike 1y ago
    OK but then you lose the benefit of all future upstream changes and now you have to manage constant rebasing Engaging the community and intelligently advocating for improvements is a way to contribute to projects as well, especially if you're willing to use a disposable forks to explore the design space, put forth RFCs, PRs, etc.
    - WesolyKubeczek 1y ago
      > OK but then you lose the benefit of all future upstream changes and now you have to manage constant rebasing I thought they were swimming in enough money to hire someone to do the rebasing. Or dogfood their models to do the same.
    - deadbabe 1y ago

airstrike 1y ago

Title should really be "Scaling PostgreSQL to the Next Level at OpenAI", which is the actual title of the talk

vb-8448 1y ago

I wonder how much their performance can improve if they put the write-instance on dedicated servers (with local and very fast ssd) and use managed services only for read-replicas.

davidkuennen 1y ago

They seem to be using physical replication. I'm currently thinking of switching to logical replication to reduce inter region egress cost. Do you think that's a good idea? There seems to be many improvements to native logical replication since Postgres 17.

VWWHFSfQ 1y ago

> The presentation also specifically mentioned that using ORM can easily lead to inefficient queries and should be used cautiously. Every ORM is bad. Especially the "any DB" ORMs. Because they trick you into thinking about your data patterns in terms of writing application code, instead of writing code for the database. And most of the time their features and APIs are abstracted in a way that basically means you can only use the least-common-denominator features of all the database backends that they can support. I've sworn off ORMs entirely. My application is a Postgres application first and foremost. I use PG-specific features extensively. Why would I sacrifice all the power that Postgres offers me just for some conveniences in Python, or Ruby, or whatever? Nah. Just write good SQL for your database and the whole system will be very happy.

bwfan123 1y ago
At one time we had to switch our service out of DB2 into psql. We had to do this with minimum downtime !! ORM helped us since it abstracted the DB (to the orm the sql dialect was a plugin). The application was largely untouched. Also, not all developers can roll their own sqls and sqls embedded in code make it harder to refactor or reason with and eventually sqls will be abstracted into a crud library of sorts anyway.
- sgarland 1y ago
  > Also, not all developers can roll their own sqls If you can't write SQL, don't use an RDBMS until you learn it. This sounds like gatekeeping because it is: I don't understand why so many people are willing to accept that they need to know their main language to use it, but not that they need to know SQL to use it.
jilles 1y ago
I've been using the Django ORM for years and it feels like an amazing piece of software. But recently I started using sqlc. Which turns my queries into Go (simplification). I think this is actually the sweet spot between ORM and rawdogging SQL.
- 1y ago

bhouston 1y ago

Argh. Shard the damn database already. Why are they not sharing by user/org yet? It is so simple and would fix the primary issue they are running into. All these work arounds they go through to avoid a straight forward fix.

iampims 1y ago
Not sure I would qualify sharding a DB that get 1M qps as straight forward. I agree with you that it seems that an org would be a natural sharding key, but we know that at this scale, nothing really is ever straight forward, especially when it's your first rodeo.
- evanelias 1y ago
  That's true, but that's also why you really should shard long before hitting that point... If your company is growing at this insane rate, it should be obvious that eventually you must shard. And the longer you delay this, the more painful it will be to accomplish.
- bhouston 1y ago
  > Not sure I would qualify sharding a DB that get 1M qps as straight forward. Sharding at the application layer (basically figure out the shard from org/user in your application code prior to interacting with the DB), will scale to any QPS rate. This is what I was referring to.
samwillis 1y ago
The message of the talk was very much that you can scale to massive throughput without having to shard and having only a single master. Of course they considered it, but the tradeoffs didn't match what they wanted to do - plus they found you could scale to this level without sharding.
- vanviegen 1y ago
  The talk seems to be mostly about all the limitations and workarounds they've had to deal with, because they choose not to shard. Apparently, they have a policy of adding no new functionality to the database, which presumably means additional separate database services being setup for each new feature. That sounds a lot like accumulating tech debt very rapidly, just because sharding is not on the table, for whatever reason.

samwillis 1y ago

I was at PGConf last week, and this was one of the most packed talks - a great insight into using Postgres, where most of the conference was fairly inward facing, with talks around the development of Postgres itself (pgconf.dev is very much that one, out of all the others each year). What you have to remember is that for many teams, when their product takes off, they are not equipped with the deep internal knowledge of how to scale a particular part of their stack. This was an awesome story from a small team having to tackle those challenges, and how they were learning as they went. So, while there are some of those "can't you just", and "whats interesting about this?" comments here, with the narrative of the growth rate, and the very high profile of the product, it was the perfect user talk for a internal development focused conference. The key insight, and main message, of the talk was that if you are not too write heavy you can scale Postgres to very high read throughput with read replicas and only a single master! That is exactly the message that needs to be spelled out as that covers the vast majority of apps. As an observation, in the Q&A at the end of the talk the questions, primarily from core Postgres developers, were focused on learning about the use case, and not an opportunity to suggest that they were doing anything wrong (not quite the same as this thread could get). A genuinely awesome group of very friendly and welcoming people in the Postgres community.

krembo 1y ago
Do you know where I can find the slides/records of the talks?
- samwillis 1y ago
  I believe the videos of the talks will be published at some point - I think "a few weeks" was mentioned.
Swizec 1y ago
> if you are not too write heavy you can scale Postgres to very high read throughput with read replicas and only a single master The number of interviewees (I do the sys design question) who want to jump straight into massively distributed eventually consistent complicated systems for 5 reads/second is too damn high. 1,000,000 users is not a lot. I wish we did better with teaching folks that while we (as an industry) were focused on horizontal this and that, computers got fast. And huge. Amazon will rent you a 32TB RAM server these days. Your database will scale just fine and ACID is far too valuable to throw.

cryptonector 1y ago

> Feature Requests > Concerning schema changes: they desire PostgreSQL to record a history of schema change events, such as adding or removing columns and other DDL operations. You can do this right now today by using `EVENT TRIGGER`s. You can check out things like Aquameta[0] (if I remember correctly) to see how it's done. [0] https://github.com/aquametalabs/aquameta https://github.com/aquametalabs/aquameta

weitendorf 1y ago
We are building similar functionality for our own postgres setup right now. Of course, postgres is very powerful and you can implement anything like this in many different ways. But at the same time, maintaining DDL history and tracking major changes to the database is a very common requirement, and unfortunately many people don't realize that until they learned that lesson the hard way. Relatedly are not DDL changes per-se, but big/important db operations that you want to also keep a record of so that you can look back and understand why something changed. I am not sure if this is the right term, but basically when we update our pricing model or skus, or set custom pricing for someone, we want those updates to be "auditable". Actually, I think this is a relatively common use case too: a fully relational model often leaves you with a large number of "static" tables that only change when you're making updates to your application. They support the smaller number of big, important, dynamic tables that your application is regularly modifying. If you had the foresight to recognize that you'd likely need to change those static tables in the future, you probably organized them so you could do so by adding new rows. It is not quite a DDL change but it is a big, risky change to your application logic that only happens rarely, and you basically just want to keep a list of all those big changes in case things get messed up or you find yourself unable to make sense of older data.
- cryptonector 1y ago
  There's lots of extensions that automatically create audit tables and record history in them. The trick is to a) create audit tables for all _existing_ tables, b) create an event trigger so you can have audit tables created automatically for any future new tables. Here's an example of how to do it: https://github.com/twosigma/postgresql-contrib/blob/master/audit.sql https://github.com/twosigma/postgresql-contrib/blob/master/a... Another thing you might do is to go with a schema that follows the event shipping pattern. In this pattern you have the "truth" held in insert-only tables (deletes and updates not allowed), then turn those "event" tables into ones you can query naturally using VIEWs, MATERIALIZED VIEWs, or live tables that you update with triggers on the event tables. Then your event tables _are your history/audit_ tables.

philosophty 1y ago

OpenAI and these companies hires inexperienced people with zero operational experience and this is how they run things. It's almost funny if you didn't see how unreliable the end result was. Postgres is powerful but just not suited for this role. But if your only tool is a hammer...

phillipcarter 1y ago
OpenAI and these companies do nothing of the sort.
- deleted 1y ago
  [deleted]
- philosophty 1y ago
  Yes, they absolutely do and this is an example of it.
  - phillipcarter 1y ago
    I would advise actually clicking through to see who the speaker is, who is categorically not a new grad nor someone with no experience in this domain.
    - philosophty 1y ago
      I don't want to single anyone out but if you think this is "expert" level experience you don't have the relevant experience yourself. I actually don't want to be overly critical but I do find the arrogance of these companies annoying.
      - phillipcarter 1y ago
        Based on your replies I have zero reason to believe you know any better at all, considering the false statements you've already made (trivially proven, no less!) and lack of any meaningful critique of the source material.

redwood 1y ago

What I find odd about this is there's no mention of all the other engines that must be in the mix powering different types of queries: I have no doubt they're using a little of everything, from scaling key-value to search, vector search, caches... They must be doing summersaults to avoid over-saturating this over-saturated Postgres env... yet only Postgres is discussed here.

harisund1990 1y ago

Seems like a perfect use case for a distributed database like YugabyteDB. Have you looked into it?

venriq 1y ago

I don't deny how powerful PostgreSQL is, but it appears that the decision to choose PostgreSQL was the most significant issue all along. It's shocking how often we find teams selecting Postgres to implement a solution that it's not suitable for. Yet, you see a document with a forced and non-logical narrative trying to justify the decision. With the NewSQL options available today, which provide distributed relational databases with multi-masters out of the box, it seems to me that many teams select Postgres simply because that's all they know, and that's the source of the problem.