11 ms·

Making Postgres scale

225 points by levkk 2y ago

999900000999 2y ago
I'm kind of interested in why we can't make a better database with all of our modern technology. Postgres is a fantastic workhorse, but it was also released in the late 80s. Who, who among you will create the database of the future... And not lock it behind bizarro licenses which force me to use telemetry.
- trescenzi 2y ago
  I guess I’d ask why is something having been first released in the late 80s, or any decade, as positive or negative? Some things are still used because they solve the problems people have. Some things are still used just because of industry capture. I’m not honestly sure where I’d put Postgres. Are there specific things you’d want from a modern database?
  - 999900000999 2y ago
    Relating to the article, better scaling. Saying run it on a bigger box is a very brute force way to optimize an application. While they come up with some other tricks here, that's ultimately what's scaling postgres means. If I imagine a better database, it would have native support for scaling, a postgres compatible data layer as well as first party support for NoSQL( JSONB columns don't cut it since if you have simultaneous writes unpredictable behavior tends to occur). It needs to also have a permissible license
    - throwaway7783 2y ago
      Can you please expand on the JSONB unpredictable behavior? We are about to embark on a journey to move some of our data from MongoDB to postgres (with some JSONB). While we don't have significant concurrent writes to a table, would be very helpful to understand the issues
      - 999900000999 2y ago
        https://github.com/shrinerb/shrine/discussions/665 https://github.com/shrinerb/shrine/discussions/665 I've never personally encountered this, but I've seen other HN contributors mention it. https://news.ycombinator.com/item?id=43189535 https://news.ycombinator.com/item?id=43189535 From what I can tell, unlike mongo, some postgres queries will try to update the entire JSONB data object vs a single field. This can lead to race conditions.
        throwaway7783 2y ago
        Ah, thanks. The first link seems something specific to Shrine. The bottomline is concurrent updates to different parts of JSONB need row level locking for correct behavior in Postgresql. This is not an important issue for us. Thank you for the pointers
        ahoka 2y ago
        You can trivially do it without locking using a version column.
      - leonidasv 2y ago
        You may be interested in FerretDB[1]. [1] https://www.ferretdb.com/ https://www.ferretdb.com/
    - aprdm 2y ago
      Why is it brute force and why is it bad ?
    - Reefersleep 2y ago
      What's an example of non-brute force scaling?
      - 999900000999 2y ago
        Something like a queuing system, re-architecting the database to handle a higher load without just throwing more machines on it. A cache.
      - HDThoreaun 2y ago
        The problem with postgres scaling is that you have to have a single master which means horizontal scaling really only gives you more reads and a failover. Eventually you wont be able to find a server big enough to handle all the writes, and if you get enough reads with even a small number of writes single master setups fall over. Distributed computing gets complicated very quickly but the gist here is basically that you need to be able to have multiple instances that can accept writes. Lots of literature on this but good starting points imo would be the paxos paper https://lamport.azurewebsites.net/pubs/time-clocks.pdf https://lamport.azurewebsites.net/pubs/time-clocks.pdf and dynamo db paper https://www.allthingsdistributed.com/files/amazon-dynamo-sosp2007.pdf https://www.allthingsdistributed.com/files/amazon-dynamo-sos...
    - mike_hearn 2y ago
      What does permissible license mean? If you mean open source, no such database exists AFAIK. If you mean you can run it locally for free for dev purposes, on prem without telemetry etc, then Oracle is clearly the best option. Compared to Postgres, Oracle DB: • Scales horizontally with full SQL and transactional consistency. That means both write and read masters, not replicas - you can use database nodes with storage smaller than your database, or with no storage, and they are fully ACID. • Has full transactional MQ support, along with many other features. • Can scale elastically. • Doesn't require vacuuming or have problems with XID wraparound. These are all Postgresisms that don't affect Oracle due to its better MVCC engine design. • Has first party support for NoSQL that resolves your concern (see SODA and JSON duality views). I should note that I have a COI because I work part time at Oracle Labs (and this post is my own yadda yadda), but you're asking why does no such database exist and whether anyone can make one. The database you're asking for not only exists but is one of the world's most popular databases. It's also actually quite cheap thanks to the elastic scaling. Spec out a managed Oracle DB in Oracle's cloud using the default elastic scaling option and you'll find it's cheaper than an Amazon Postgres RDS for similar max specs!
      - 999900000999 2y ago
        Does Oracle offer a Firebase like application/layer with authentication and stuff. Can you get me some Oracle cloud credits ? First positive thing I’ve ever heard about an oracle project !
        mike_hearn 2y ago
        Oracle Cloud (called OCI) has an always-free offering, you don't need credits. You can just sign up and use some small quantity of resources for nothing indefinitely. That includes a managed Oracle database: https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/autonomous-always-free.html https://docs.oracle.com/en/cloud/paas/autonomous-database/se... There's also some sort of startup credits program with a brochure here, apparently you can just fill out a form and get some credits with an option to apply for more. But I don't know much about that. I've used the always-free programme for some personal stuff and it worked fine so I never needed to think about credits. https://www.oracle.com/a/ocom/docs/free-cloud.pdf https://www.oracle.com/a/ocom/docs/free-cloud.pdf I have to admit I'm not really familiar with Firebase, I thought that was some managed service for mobile apps, but Oracle DB comes with some stuff that sounds similar. And Oracle Cloud is an AWS-style cloud, it has a ton of high level services for things. ORDS is a REST binding layer that lets you export tables, views, stored procedures and NoSQL JSON document stores over HTTP without writing a middleman server yourself. You can drive it directly from the browser. ORDS supports OAuth2 or can be integrated with custom auth schemes from what I understand. I've not used ORDS myself yet but probably will in the near future. https://www.oracle.com/database/technologies/appdev/rest.html https://www.oracle.com/database/technologies/appdev/rest.htm... Firebase IIRC when it first launched was known for push streaming of changes. Oracle DB lets you subscribe to the results of SQL queries and get push notifications when they change, either directly via driver callbacks or into a message queue for async processing later. It's pretty easy to hook such notifications up to web sockets or SSE or similar, in fact I've done that in my current project. There's also a thing called APEX which is a bundled visual low-code app builder. I've never used it but I've used apps built with it, and it must be quite flexible as they all had a lot of features and looked very different. You can tell you're using an APEX app because they have a lot of colons in the URLs for some reason. Here's a random example of one from outside of Oracle that exports a database of dubious scientific research papers: https://dbrech.irit.fr/pls/apex/f?p=9999:1 https://dbrech.irit.fr/pls/apex/f?p=9999:1:::::: I'm not holding it up as a great example, there are probably better examples out there, it's just a one that came to mind that's public and I used before.
- DoctorOW 2y ago
  Have you looked at CockroachDB? PostgreSQL compatibility with modern comforts (e.g. easy cloud deployments, horizontal scaling, memory safe language)
  - rednafi 2y ago
    Came here to say this. Cockroach solves the sharding issue by adopting consistent hashing-based data distribution, as described in the Dynamo paper. However, their cloud solution is a bit expensive to get started with.
    - I_am_tiberius 2y ago
      Does CockroachdB already support ltree?
      - rednafi 2y ago
        It doesn’t. It doesn’t support most of the Postgres extensions. We got away with it because we weren’t doing anything beyond vanilla Postgres.
- HighlandSpring 2y ago
  There are "better" databases but they're better given some particular definition that may not be relevant to your needs. If SQL/the relational model and ACID semantics is what you need then postgres is simply the best in class. The fact it dates back to the 80s is probably an advantage (requirement?) when it comes to solving a problem really well
- wmf 2y ago
  Anyone who creates a better database is going to want to get paid for it which either means DBaaS or those weird licenses.
- thinkingtoilet 2y ago
  Postgres 17.4 was released last month. Show some respect to the devs.
- HDThoreaun 2y ago
  Postgres is the most worked on database in the world right now. Its original release date doesnt mean work stopped. No-sql was a new thing a decade-ish ago but most companies probably dont need it. New data platforms focused on scaling like snowflake and cockroach have come too but again for most use cases postgres is better.
- moltar 2y ago
  Look at AWS presentation/talk about Aurora DSQL [1] It’s a Postgres facade. But everything beyond that is a complete reimagining and a rewrite to scale independently. I personally think it’s going to eat a lot of market share when it solves some remaining limitations. [1] https://youtu.be/huGmR_mi5dQ?si=ALw4XjdDJBxkZWRv https://youtu.be/huGmR_mi5dQ?si=ALw4XjdDJBxkZWRv
remram 2y ago
Never heard of pgdog before. How does it compare to citus?
- MuffinFlavored 2y ago
  What are the drawbacks of Citus/why isn't it perfect/what would you look for from a competitor/alternative?
  - craigkerstiens 2y ago
    Citus works really well *if* you have your schema well defined and slightly denormalized (meaning you have the shard key materialized on every table), and you ensure you're always joining on that as part of querying. For a lot of existing applications that were not designed with this in mind if can be several months of database and application code changes to get things into shape to work with Citus. If you're designing from scratch and make it worth with Citus then (specifically for a multi-tenant/SaaS sharded app) it can make scaling seem a bit magical.
- caffeinated_me 2y ago
  Seems like this is a similar philosophy, but is missing a bunch of things the Citus coordinator provides. From the article, I'm guessing Citus is better at cross-shard queries, SQL support, central management of workers, keeping schemas in sync, and keeping small join tables in sync across the fleet, and provides a single point of ingestion. That being said, this does seem to handle replicas better than Citus ever really did, and most of the features it's lacking aren't relevant for the sort of multitenant use case this blog is describing, so it's not a bad tradeoff. This also avoids the coordinator as a central point of failure for both outages and connection count limitations, but we never really saw those be a problem often in practice.
  - levkk 2y ago
    We certainly have a way to go to support all cross-shard use cases, especially complex aggregates (like percentiles). In OLTP, where PgDog will focus on first, it's good to have a sharding key and a single shard in mind, 99% of the time. The 1% will be divided between easy things we already support, like sorting, and slightly more complex things like aggregates (avg, count, max, min, etc.), which are on the roadmap. For everything else, and until we cover what's left, postgres_fdw can be a fallback. It actually works pretty well.
craigkerstiens 2y ago
Probably as useful is the overview of what pgdog is and the docs. From their docs[1]: "PgDog is a sharder, connection pooler and load balancer for PostgreSQL. Written in Rust, PgDog is fast, reliable and scales databases horizontally without requiring changes to application code." [1] https://docs.pgdog.dev/ https://docs.pgdog.dev/
saisrirampur 2y ago
Interesting technology. Similar to Citus but not built as an extension. The Citus coordinator, which is a Postgres database with the Citus extension, is replaced by a proxy layer written in Rust. That might provide more flexibility and velocity implementing distributed planning and execution than being tied to the extension ecosystem. It would indeed be a journey to catch up with Postgres on compatibility, but it's a good start.
- sroussey 2y ago
  So like the MySQL proxies of long ago? There are definitely advantages of not running inside the system you wish to orchestrate. Better keep up with the parser though!
  - levkk 2y ago
    We use the Postgres parser directly, thanks to the great work of pg_query [1]. [1] https://github.com/pganalyze/pg_query.rs https://github.com/pganalyze/pg_query.rs
mindcrash 2y ago
Well, ofcourse it does! :) Another (battle tested * ) solution is to deploy the (open source) Postgres distribution created by Citus (subsidiary of Microsoft) on nodes running on Ubuntu, Debian or Red Hat and you are pretty much done: https://www.citusdata.com/product/community https://www.citusdata.com/product/community Slap good old trusty PgBounce in front of it if you want/need (and you probably do) connection pooling: https://www.citusdata.com/blog/2017/05/10/scaling-connections-in-postgres/ https://www.citusdata.com/blog/2017/05/10/scaling-connection... *) Citus was purchased by Microsoft more or less solely to provide easy scale out on Azure through Cosmos DB for PostgreSQL
- gigatexal 2y ago
  Is it really that easy? What are the edge cases?
  - levkk 2y ago
    It's not. We tried. Plus, it doesn't work on RDS, where most of production databases are. I think Citus was a great first step in the right direction, but it's time to scale the 99% of databases that don't run on Azure Citus already.
    - mindcrash 2y ago
      That's because Amazon wants to do whatever they like themselves... you apparently can get stuff to work by running your own masters (w/ citus extension) in EC2 backed by workers (Postgres RDS) in RDS: https://www.citusdata.com/blog/2015/07/15/scaling-postgres-rds-with-pg-shard/ https://www.citusdata.com/blog/2015/07/15/scaling-postgres-r... (note that this is a old blog post -- pg_shard has been succeeded by citus, but the architecture diagram still applies) And me saying "Apparently" because I have no experience dealing with large databases on AWS. Personally had no issues with Citus too, both on bare metal/VMs and as SaaS on Azure...
  - caffeinated_me 2y ago
    Depends on your schema, really. The hard part is choosing a distribution key to use for sharding- if you've got something like tenant ID that's in most of your queries and big tables, it's pretty easy, but can be a pain otherwise.
rohan_ 2y ago
Couldn't they have just moved to Aurora DSQL and saved all the headache?
- levkk 2y ago
  We actually found Aurora to be about 3x slower than community PG for small queries. That was back then, maybe things are better now. Migrating to another database (and Aurora is Postgres-compatible, it's not Postgres) is very risky when you've been using yours for years and know where the edge cases are.
  - rednafi 2y ago
    This! Postgres to pg-compatible DBs are never as smooth as they advertise it to be.
  - sgarland 2y ago
    I’ve consistently found Aurora MySQL and PG to be slower than everything, including my 12 year old Dell R620s. You can’t beat data locality, and the 4/6 quorum requirement of Aurora combined with the physical distance kills any hope of speed.
  - SahAssar 2y ago
    Aurora is postgres but with a different storage layer, no? It uses the postgres engine, which other postgres-compatible databases like cockroach do not, right?
    - tudorg 2y ago
      That’s right, Aurora Postgres is quite close to vanilla Postgres. Aurora DSQL is a different story though. For a more scientific answer, there is this project: https://pgscorecard.com/ https://pgscorecard.com/ Note that Aurora scores 93% while Cockroach scores only 40%.
      - SahAssar 2y ago
        I actually wasn't aware of Aurora DSQL, that's incredibly bad product naming.
fmajid 2y ago
Skype open-sourced their architecture way back, using PL/Proxy to route calls based on shard. It works, is quite elegant, handled 50% of all international phone calls in the noughties. My old company used it to provide real-time analytics on about 300M mobile devices. https://wiki.postgresql.org/images/2/28/Moskva_DB_Tools.v3.pdf https://wiki.postgresql.org/images/2/28/Moskva_DB_Tools.v3.p... https://s3.amazonaws.com/apsalar_docs/presentations/Apsalar_PyPGDay_2013+with+notes.pdf https://s3.amazonaws.com/apsalar_docs/presentations/Apsalar_...
- Keyframe 2y ago
  Skype has had from the beginning the requirement that all database access must be implemented through stored procedures. That presentation starts with hard violence.
  - Tostino 2y ago
    If the database team designed a thoughtful API with stored procedures, this can actually be a quite nice way to interact with a database for specific uses. Being 100% hard and fast on that rule seems like a bad idea though.
    - dboreham 2y ago
      Fashionable 20 years ago but thankfully everyone who had that bee in their bonnet seems to have retired.
      - Kinrany 2y ago
        It'll make a comeback once stored procedures can be easily written in real programming languages using standard tools.
        sgarland 2y ago
        You already can [0]. C, PL/Perl, PL/Python, and PL/Tcl exist out of the box, in addition to PL/pgSQL, which I assume you were implying isn’t a “real programming language.” [0]: https://www.postgresql.org/docs/current/server-programming.html https://www.postgresql.org/docs/current/server-programming.h...
rednafi 2y ago
Another option is going full-scale with CockroachDB. We had a Django application backed by PostgreSQL, which we migrated to CockroachDB using their official backend. The data migration was a pain, but it was still less painful than manually sharding the data or dealing with 3rd party extensions. Since then, we’ve had a few hiccups with autogenerated migration scripts, but overall, the experience has been quite seamless. We weren’t using any advanced PostgreSQL features, so CockroachDB has worked well.
- skunkworker 2y ago
  Unless their pricing has changed, it’s quite exorbitant when you need a lot of data. To the point that one year of cockroachdb would cost 5x the cost of the server it was running on.
  - rednafi 2y ago
    This is still true. I wouldn’t use Cockroach if it were my own business. Also, they don’t offer any free version to try out the product. All you get is a short trial period and that’s it.
    - CharlesW 2y ago
      > Also, they don’t offer any free version to try out the product. The site makes it seems as if I can install CockroachDB on Mac, Linux, or Windows and try it out for as long as I like. https://www.cockroachlabs.com/docs/v25.1/install-cockroachdb-linux https://www.cockroachlabs.com/docs/v25.1/install-cockroachdb... Additionally, they claim CockroachDB Cloud is free for use "up to 10 GiB of storage and 50M RUs per organization per month".
      - rednafi 2y ago
        Oh yeah, you can run docker-compose and play with the local version as long as you want. But their cloud offers are limited and quite expensive.
aprdm 2y ago
99.9% of the companies in the world will never need more than 1 beefy box running postgres with a replica for a manual failover and/or reads.
- frollogaston 2y ago
  Availability is trickier than scalability. An async replica can lose a few recent writes during a failover, and a synchronous replica is safer but slower. A company using some platform might not even know which one they're using until it bites them.
  - wavemode 2y ago
    99.9% of companies also aren't going to feel the performance difference of synchronous replication. That being said, the setups I typically see don't even go that far. Most companies don't mitigate for the database going down in the first place. If the db goes down they just eat the downtime and fix it.
Eikon 2y ago
I run a 100 billion+ rows Postgres database [0], that is around 16TB, it's pretty painless! There are a few tricks that make it run well (PostgreSQL compiled with a non-standard block size, ZFS, careful VACUUM planning). But nothing too out of the ordinary. ATM, I insert about 150,000 rows a second, run 40,000 transactions a second, and read 4 million rows a second. Isn't "Postgres does not scale" a strawman? [0] https://www.merklemap.com/ https://www.merklemap.com/
- rednafi 2y ago
  Damn, that’s a chonky database. Have you written anything about the setup? I’d love to know more— is it running on a single machine? How many reader and writer DBs? What does the replication look like? What are the machine specs? Is it self-hosted or on AWS? By the way, really cool website.
  - Eikon 2y ago
    I'll try to get a blog post out soon! > Damn, that’s a chonky database. Have you written anything about the setup? I’d love to know more— is it running on a single machine? How many reader and writer DBs? What does the replication look like? What are the machine specs? Is it self-hosted or on AWS? It's self-hosted on bare metal, with standby replication, normal settings, nothing "weird" there. 6 NVMe drives in raidz-1, 1024GB of memory, a 96 core AMD EPYC cpu. A single database with no partitioning (I avoid PostgreSQL partitioning as it complicates queries and weakens constraint enforcement, and IHMO is not providing much benefits outside of niche use-cases). > By the way, really cool website. Thank you!
    - stuartjohnson12 2y ago
      > It's self-hosted on bare metal, with standby replication, normal settings, nothing "weird" there. I can build scalable data storage without a flexible scalable redundant resilient fault-tolerant available distributed containerized serverless microservice cloud-native managed k8-orchestrated virtualized load balanced auto-scaled multi-region pubsub event-based stateless quantum-ready vectorized private cloud center? I won't believe it.
kristianpaul 2y ago
Logical replication works both ways, thats a good start.
lamp_book 2y ago
People talk about scale frequently as a single dimension (and usually volume as it relates to users) but that can be oversimplifying for many kinds of applications. For instance, as you are thinking about non-trivial partitioning schemes (like if there is high coupling between entities of the same kind - as you see in graphs) is when you should consider alternatives like the Bigtable-inspired DBs, since those are (relatively) more batteries included for you. > It’s funny to write this. The Internet contains at least 1 (or maybe 2) meaty blog posts about how this is done It would’ve been great to link those here. I’m guessing one refers to StackOverflow which has/had one of the more famous examples of scaled Postgres.
- levkk 2y ago
  I was thinking of the Instagram post years ago. And maybe the Instacart one.
  - lamp_book 2y ago
    Maybe this one for Instagram? https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c https://instagram-engineering.com/sharding-ids-at-instagram-...
- banashark 2y ago
  Notion wrote about their experience before: https://www.notion.com/blog/sharding-postgres-at-notion https://www.notion.com/blog/sharding-postgres-at-notion https://www.notion.com/blog/the-great-re-shard https://www.notion.com/blog/the-great-re-shard
octernion 2y ago
i was reading through this and was going "huh this sounds familiar" until i read who wrote it :) neat piece of tech! excited to try it out.
fourseventy 2y ago
I run a postgresql db with a few billion rows at about 2TB right now. We don't need sharding yet but when we do I was considering Citus. Does anyone have experience implementing Citus that could comment?
- caffeinated_me 2y ago
  It can be great, depending on your schema and planned growth. Questions I'd be asking in your shoes: 1. Does the schema have an obvious column to use for distribution? You'll probably want to fit one of the 2 following cases, but these aren't exclusive: 1a. A use case where most traffic is scoped to a subset of data. (e.g. a multitenant system). This is the easiest use case- just make sure most of your queries contain the column (most likely tenant ID or equivalent), and partially denormalize to have it in tables where it's implicit to make your life easier. Do not use a timestamp. 1b. A rollup/analytics based use case that needs heavy parallelism (e.g. a large IoT system where you want to do analytics across a fleet). For this, you're looking for a column that has high cardinality witout too many major hot spots- in the IoT use case mentioned, this would probably be a device ID or similar 2. Are you sure you're going to grow to the scale where you need Citus? Depending on workload, it's not too hard to have a 20TB single-server PG database, and that's more than enough for a lot of companies these days. 3. When do you want to migrate? Logical replication in should work these days (haven't tested myself), but the higher the update rate and larger the database, the more painful this gets. There's not a lot of tools that are very useful for the more difficult scenarios here, but the landscape has changed since I've last had to do this 4. Do you want to run this yourself? Azure does offer a managed service, and Crunchy offers Citus on any cloud, so you have options. 5. If you're running this yourself, how are you managing HA? pg_auto_failover has some Citus support, but can be a bit tricky to get started with. I did get my Citus cluster over 1 PB at my previous job, and that's not the biggest out out there, so there's definitely room to scale, but the migration can be tricky. Disclaimer: former Citus employee
eximius 2y ago
Something I don't see in the pgdog documentation is how cross-shard joins work. Okay, if I do a simple `select * from users order by id`, you'll in-memory order the combined results for me. But if I have group by and aggregations and such? Will it resolve that correctly?
- levkk 2y ago
  Aggregates are a work in progress. We're going to implement them in this order: 1. count 2. max, min, sum 3. avg (needs a query rewrite to include count) Eventually, we'll do all of these: https://www.postgresql.org/docs/current/functions-aggregate.html https://www.postgresql.org/docs/current/functions-aggregate..... If you got a specific use case, reach out and we'll prioritize.
  - eximius 2y ago
    Heh, no chance I can introduce this at work and hard to have a personal project requiring it. :) I think you probably need some documentation to the effect of the current state of affairs, as well as prescriptions as to how to work around it. _Most_ live workloads, even if the total dataset is huge, have a pretty small working set. So limiting DB operations to simple fetches and doing any complex operations in memory is viable, but should be prescribed as the solution or people will consider it's omission as a fault instead of a choice.
    - levkk 2y ago
      No worries. It's early days, the code and docs are just a few months in the making. I'm happy to keep you updated on the progress. If you want, send your contact info to hi@pgdog.dev. - Lev
    - levkk 2y ago
      I ended up adding support for GROUP BY: https://github.com/pgdogdev/pgdog/pull/43 https://github.com/pgdogdev/pgdog/pull/43 I had it in the back of my mind for a while, nice to have it in code. Works pretty well, as long as columns in GROUP BY are present in the result set. Otherwise, we would need to rewrite the query to include them, and remove them once we're done.
akshayshah 2y ago
Apart from being backed by Postgres instead of MySQL, is this different from Vitess (and its commercial vendor PlanetScale)? https://vitess.io/ https://vitess.io/
- levkk 2y ago
  The goal for this project is to be analogous to Vitess for Postgres.
Karupan 2y ago
Tangentially related: is there a good guide or setup scripts to run self hosted Postgres with backups and secondary standby? Like I just want something I can deploy to a VPS/dedicated box for all my side projects. If not is supabase the most painless way to get started?
gourneau 2y ago
I’m working with several Postgres databases that share identical schemas, and I want to make their data accessible from a single interface. Currently, I’m using Postgres FDWs to import the tables from those databases. I then create views that UNION ALL the relevant tables, adding a column to indicate the source database for each row. This works, but I’m wondering if there’s a better way — ideally something that can query multiple databases in parallel and merge the results with a source database column included. Would tools like pgdog, pgcat, pganimal be a good fit for this? I’m open to suggestions for more efficient approaches. Thanks!
sourtrident 2y ago
Funny how everyone eventually hits this point and thinks they're inventing fire - but then again, pushing your trusty old tools way past comfort is where cool engineering actually happens.
roark_howard 2y ago
Shouldn't the title be 'Learning to scale Postgres'?
Nelkins 2y ago
How does PgDog handle a column like: `id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY`
- levkk 2y ago
  Replace it with a function that makes sure the id coming out matches the sharding schema. Assuming it's coming from a sequence, we're consuming it until we get a matching number. It would be good to know what is behind the generated column in your use case.
moribvndvs 2y ago
DynamoDB is most certainly not the way.

225 points by levkk 2y ago

I'm kind of interested in why we can't make a better database with all of our modern technology. Postgres is a fantastic workhorse, but it was also released in the late 80s. Who, who among you will create the database of the future... And not lock it behind bizarro licenses which force me to use telemetry.

trescenzi 2y ago
I guess I’d ask why is something having been first released in the late 80s, or any decade, as positive or negative? Some things are still used because they solve the problems people have. Some things are still used just because of industry capture. I’m not honestly sure where I’d put Postgres. Are there specific things you’d want from a modern database?
- 999900000999 2y ago
  Relating to the article, better scaling. Saying run it on a bigger box is a very brute force way to optimize an application. While they come up with some other tricks here, that's ultimately what's scaling postgres means. If I imagine a better database, it would have native support for scaling, a postgres compatible data layer as well as first party support for NoSQL( JSONB columns don't cut it since if you have simultaneous writes unpredictable behavior tends to occur). It needs to also have a permissible license
  - throwaway7783 2y ago
    Can you please expand on the JSONB unpredictable behavior? We are about to embark on a journey to move some of our data from MongoDB to postgres (with some JSONB). While we don't have significant concurrent writes to a table, would be very helpful to understand the issues
    - 999900000999 2y ago
      https://github.com/shrinerb/shrine/discussions/665 https://github.com/shrinerb/shrine/discussions/665 I've never personally encountered this, but I've seen other HN contributors mention it. https://news.ycombinator.com/item?id=43189535 https://news.ycombinator.com/item?id=43189535 From what I can tell, unlike mongo, some postgres queries will try to update the entire JSONB data object vs a single field. This can lead to race conditions.
      - throwaway7783 2y ago
        Ah, thanks. The first link seems something specific to Shrine. The bottomline is concurrent updates to different parts of JSONB need row level locking for correct behavior in Postgresql. This is not an important issue for us. Thank you for the pointers
        ahoka 2y ago
        You can trivially do it without locking using a version column.
    - leonidasv 2y ago
      You may be interested in FerretDB[1]. [1] https://www.ferretdb.com/ https://www.ferretdb.com/
  - aprdm 2y ago
    Why is it brute force and why is it bad ?
  - Reefersleep 2y ago
    What's an example of non-brute force scaling?
    - 999900000999 2y ago
      Something like a queuing system, re-architecting the database to handle a higher load without just throwing more machines on it. A cache.
    - HDThoreaun 2y ago
      The problem with postgres scaling is that you have to have a single master which means horizontal scaling really only gives you more reads and a failover. Eventually you wont be able to find a server big enough to handle all the writes, and if you get enough reads with even a small number of writes single master setups fall over. Distributed computing gets complicated very quickly but the gist here is basically that you need to be able to have multiple instances that can accept writes. Lots of literature on this but good starting points imo would be the paxos paper https://lamport.azurewebsites.net/pubs/time-clocks.pdf https://lamport.azurewebsites.net/pubs/time-clocks.pdf and dynamo db paper https://www.allthingsdistributed.com/files/amazon-dynamo-sosp2007.pdf https://www.allthingsdistributed.com/files/amazon-dynamo-sos...
  - mike_hearn 2y ago
    What does permissible license mean? If you mean open source, no such database exists AFAIK. If you mean you can run it locally for free for dev purposes, on prem without telemetry etc, then Oracle is clearly the best option. Compared to Postgres, Oracle DB: • Scales horizontally with full SQL and transactional consistency. That means both write and read masters, not replicas - you can use database nodes with storage smaller than your database, or with no storage, and they are fully ACID. • Has full transactional MQ support, along with many other features. • Can scale elastically. • Doesn't require vacuuming or have problems with XID wraparound. These are all Postgresisms that don't affect Oracle due to its better MVCC engine design. • Has first party support for NoSQL that resolves your concern (see SODA and JSON duality views). I should note that I have a COI because I work part time at Oracle Labs (and this post is my own yadda yadda), but you're asking why does no such database exist and whether anyone can make one. The database you're asking for not only exists but is one of the world's most popular databases. It's also actually quite cheap thanks to the elastic scaling. Spec out a managed Oracle DB in Oracle's cloud using the default elastic scaling option and you'll find it's cheaper than an Amazon Postgres RDS for similar max specs!
    - 999900000999 2y ago
      Does Oracle offer a Firebase like application/layer with authentication and stuff. Can you get me some Oracle cloud credits ? First positive thing I’ve ever heard about an oracle project !
      - mike_hearn 2y ago
        Oracle Cloud (called OCI) has an always-free offering, you don't need credits. You can just sign up and use some small quantity of resources for nothing indefinitely. That includes a managed Oracle database: https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/autonomous-always-free.html https://docs.oracle.com/en/cloud/paas/autonomous-database/se... There's also some sort of startup credits program with a brochure here, apparently you can just fill out a form and get some credits with an option to apply for more. But I don't know much about that. I've used the always-free programme for some personal stuff and it worked fine so I never needed to think about credits. https://www.oracle.com/a/ocom/docs/free-cloud.pdf https://www.oracle.com/a/ocom/docs/free-cloud.pdf I have to admit I'm not really familiar with Firebase, I thought that was some managed service for mobile apps, but Oracle DB comes with some stuff that sounds similar. And Oracle Cloud is an AWS-style cloud, it has a ton of high level services for things. ORDS is a REST binding layer that lets you export tables, views, stored procedures and NoSQL JSON document stores over HTTP without writing a middleman server yourself. You can drive it directly from the browser. ORDS supports OAuth2 or can be integrated with custom auth schemes from what I understand. I've not used ORDS myself yet but probably will in the near future. https://www.oracle.com/database/technologies/appdev/rest.html https://www.oracle.com/database/technologies/appdev/rest.htm... Firebase IIRC when it first launched was known for push streaming of changes. Oracle DB lets you subscribe to the results of SQL queries and get push notifications when they change, either directly via driver callbacks or into a message queue for async processing later. It's pretty easy to hook such notifications up to web sockets or SSE or similar, in fact I've done that in my current project. There's also a thing called APEX which is a bundled visual low-code app builder. I've never used it but I've used apps built with it, and it must be quite flexible as they all had a lot of features and looked very different. You can tell you're using an APEX app because they have a lot of colons in the URLs for some reason. Here's a random example of one from outside of Oracle that exports a database of dubious scientific research papers: https://dbrech.irit.fr/pls/apex/f?p=9999:1 https://dbrech.irit.fr/pls/apex/f?p=9999:1:::::: I'm not holding it up as a great example, there are probably better examples out there, it's just a one that came to mind that's public and I used before.
DoctorOW 2y ago
Have you looked at CockroachDB? PostgreSQL compatibility with modern comforts (e.g. easy cloud deployments, horizontal scaling, memory safe language)
- rednafi 2y ago
  Came here to say this. Cockroach solves the sharding issue by adopting consistent hashing-based data distribution, as described in the Dynamo paper. However, their cloud solution is a bit expensive to get started with.
  - I_am_tiberius 2y ago
    Does CockroachdB already support ltree?
    - rednafi 2y ago
      It doesn’t. It doesn’t support most of the Postgres extensions. We got away with it because we weren’t doing anything beyond vanilla Postgres.
HighlandSpring 2y ago
There are "better" databases but they're better given some particular definition that may not be relevant to your needs. If SQL/the relational model and ACID semantics is what you need then postgres is simply the best in class. The fact it dates back to the 80s is probably an advantage (requirement?) when it comes to solving a problem really well
wmf 2y ago
Anyone who creates a better database is going to want to get paid for it which either means DBaaS or those weird licenses.
thinkingtoilet 2y ago
Postgres 17.4 was released last month. Show some respect to the devs.
HDThoreaun 2y ago
Postgres is the most worked on database in the world right now. Its original release date doesnt mean work stopped. No-sql was a new thing a decade-ish ago but most companies probably dont need it. New data platforms focused on scaling like snowflake and cockroach have come too but again for most use cases postgres is better.
moltar 2y ago
Look at AWS presentation/talk about Aurora DSQL [1] It’s a Postgres facade. But everything beyond that is a complete reimagining and a rewrite to scale independently. I personally think it’s going to eat a lot of market share when it solves some remaining limitations. [1] https://youtu.be/huGmR_mi5dQ?si=ALw4XjdDJBxkZWRv https://youtu.be/huGmR_mi5dQ?si=ALw4XjdDJBxkZWRv

remram 2y ago

Never heard of pgdog before. How does it compare to citus?

MuffinFlavored 2y ago
What are the drawbacks of Citus/why isn't it perfect/what would you look for from a competitor/alternative?
- craigkerstiens 2y ago
  Citus works really well *if* you have your schema well defined and slightly denormalized (meaning you have the shard key materialized on every table), and you ensure you're always joining on that as part of querying. For a lot of existing applications that were not designed with this in mind if can be several months of database and application code changes to get things into shape to work with Citus. If you're designing from scratch and make it worth with Citus then (specifically for a multi-tenant/SaaS sharded app) it can make scaling seem a bit magical.
caffeinated_me 2y ago
Seems like this is a similar philosophy, but is missing a bunch of things the Citus coordinator provides. From the article, I'm guessing Citus is better at cross-shard queries, SQL support, central management of workers, keeping schemas in sync, and keeping small join tables in sync across the fleet, and provides a single point of ingestion. That being said, this does seem to handle replicas better than Citus ever really did, and most of the features it's lacking aren't relevant for the sort of multitenant use case this blog is describing, so it's not a bad tradeoff. This also avoids the coordinator as a central point of failure for both outages and connection count limitations, but we never really saw those be a problem often in practice.
- levkk 2y ago
  We certainly have a way to go to support all cross-shard use cases, especially complex aggregates (like percentiles). In OLTP, where PgDog will focus on first, it's good to have a sharding key and a single shard in mind, 99% of the time. The 1% will be divided between easy things we already support, like sorting, and slightly more complex things like aggregates (avg, count, max, min, etc.), which are on the roadmap. For everything else, and until we cover what's left, postgres_fdw can be a fallback. It actually works pretty well.

craigkerstiens 2y ago

Probably as useful is the overview of what pgdog is and the docs. From their docs[1]: "PgDog is a sharder, connection pooler and load balancer for PostgreSQL. Written in Rust, PgDog is fast, reliable and scales databases horizontally without requiring changes to application code." [1] https://docs.pgdog.dev/ https://docs.pgdog.dev/

saisrirampur 2y ago

Interesting technology. Similar to Citus but not built as an extension. The Citus coordinator, which is a Postgres database with the Citus extension, is replaced by a proxy layer written in Rust. That might provide more flexibility and velocity implementing distributed planning and execution than being tied to the extension ecosystem. It would indeed be a journey to catch up with Postgres on compatibility, but it's a good start.

sroussey 2y ago
So like the MySQL proxies of long ago? There are definitely advantages of not running inside the system you wish to orchestrate. Better keep up with the parser though!
- levkk 2y ago
  We use the Postgres parser directly, thanks to the great work of pg_query [1]. [1] https://github.com/pganalyze/pg_query.rs https://github.com/pganalyze/pg_query.rs

mindcrash 2y ago

Well, ofcourse it does! :) Another (battle tested * ) solution is to deploy the (open source) Postgres distribution created by Citus (subsidiary of Microsoft) on nodes running on Ubuntu, Debian or Red Hat and you are pretty much done: https://www.citusdata.com/product/community https://www.citusdata.com/product/community Slap good old trusty PgBounce in front of it if you want/need (and you probably do) connection pooling: https://www.citusdata.com/blog/2017/05/10/scaling-connections-in-postgres/ https://www.citusdata.com/blog/2017/05/10/scaling-connection... *) Citus was purchased by Microsoft more or less solely to provide easy scale out on Azure through Cosmos DB for PostgreSQL

gigatexal 2y ago
Is it really that easy? What are the edge cases?
- levkk 2y ago
  It's not. We tried. Plus, it doesn't work on RDS, where most of production databases are. I think Citus was a great first step in the right direction, but it's time to scale the 99% of databases that don't run on Azure Citus already.
  - mindcrash 2y ago
    That's because Amazon wants to do whatever they like themselves... you apparently can get stuff to work by running your own masters (w/ citus extension) in EC2 backed by workers (Postgres RDS) in RDS: https://www.citusdata.com/blog/2015/07/15/scaling-postgres-rds-with-pg-shard/ https://www.citusdata.com/blog/2015/07/15/scaling-postgres-r... (note that this is a old blog post -- pg_shard has been succeeded by citus, but the architecture diagram still applies) And me saying "Apparently" because I have no experience dealing with large databases on AWS. Personally had no issues with Citus too, both on bare metal/VMs and as SaaS on Azure...
- caffeinated_me 2y ago
  Depends on your schema, really. The hard part is choosing a distribution key to use for sharding- if you've got something like tenant ID that's in most of your queries and big tables, it's pretty easy, but can be a pain otherwise.

rohan_ 2y ago

Couldn't they have just moved to Aurora DSQL and saved all the headache?

levkk 2y ago
We actually found Aurora to be about 3x slower than community PG for small queries. That was back then, maybe things are better now. Migrating to another database (and Aurora is Postgres-compatible, it's not Postgres) is very risky when you've been using yours for years and know where the edge cases are.
- rednafi 2y ago
  This! Postgres to pg-compatible DBs are never as smooth as they advertise it to be.
- sgarland 2y ago
  I’ve consistently found Aurora MySQL and PG to be slower than everything, including my 12 year old Dell R620s. You can’t beat data locality, and the 4/6 quorum requirement of Aurora combined with the physical distance kills any hope of speed.
- SahAssar 2y ago
  Aurora is postgres but with a different storage layer, no? It uses the postgres engine, which other postgres-compatible databases like cockroach do not, right?
  - tudorg 2y ago
    That’s right, Aurora Postgres is quite close to vanilla Postgres. Aurora DSQL is a different story though. For a more scientific answer, there is this project: https://pgscorecard.com/ https://pgscorecard.com/ Note that Aurora scores 93% while Cockroach scores only 40%.
    - SahAssar 2y ago
      I actually wasn't aware of Aurora DSQL, that's incredibly bad product naming.

fmajid 2y ago

Skype open-sourced their architecture way back, using PL/Proxy to route calls based on shard. It works, is quite elegant, handled 50% of all international phone calls in the noughties. My old company used it to provide real-time analytics on about 300M mobile devices. https://wiki.postgresql.org/images/2/28/Moskva_DB_Tools.v3.pdf https://wiki.postgresql.org/images/2/28/Moskva_DB_Tools.v3.p... https://s3.amazonaws.com/apsalar_docs/presentations/Apsalar_PyPGDay_2013+with+notes.pdf https://s3.amazonaws.com/apsalar_docs/presentations/Apsalar_...

Keyframe 2y ago
Skype has had from the beginning the requirement that all database access must be implemented through stored procedures. That presentation starts with hard violence.
- Tostino 2y ago
  If the database team designed a thoughtful API with stored procedures, this can actually be a quite nice way to interact with a database for specific uses. Being 100% hard and fast on that rule seems like a bad idea though.
  - dboreham 2y ago
    Fashionable 20 years ago but thankfully everyone who had that bee in their bonnet seems to have retired.
    - Kinrany 2y ago
      It'll make a comeback once stored procedures can be easily written in real programming languages using standard tools.
      - sgarland 2y ago
        You already can [0]. C, PL/Perl, PL/Python, and PL/Tcl exist out of the box, in addition to PL/pgSQL, which I assume you were implying isn’t a “real programming language.” [0]: https://www.postgresql.org/docs/current/server-programming.html https://www.postgresql.org/docs/current/server-programming.h...

rednafi 2y ago

Another option is going full-scale with CockroachDB. We had a Django application backed by PostgreSQL, which we migrated to CockroachDB using their official backend. The data migration was a pain, but it was still less painful than manually sharding the data or dealing with 3rd party extensions. Since then, we’ve had a few hiccups with autogenerated migration scripts, but overall, the experience has been quite seamless. We weren’t using any advanced PostgreSQL features, so CockroachDB has worked well.

skunkworker 2y ago
Unless their pricing has changed, it’s quite exorbitant when you need a lot of data. To the point that one year of cockroachdb would cost 5x the cost of the server it was running on.
- rednafi 2y ago
  This is still true. I wouldn’t use Cockroach if it were my own business. Also, they don’t offer any free version to try out the product. All you get is a short trial period and that’s it.
  - CharlesW 2y ago
    > Also, they don’t offer any free version to try out the product. The site makes it seems as if I can install CockroachDB on Mac, Linux, or Windows and try it out for as long as I like. https://www.cockroachlabs.com/docs/v25.1/install-cockroachdb-linux https://www.cockroachlabs.com/docs/v25.1/install-cockroachdb... Additionally, they claim CockroachDB Cloud is free for use "up to 10 GiB of storage and 50M RUs per organization per month".
    - rednafi 2y ago
      Oh yeah, you can run docker-compose and play with the local version as long as you want. But their cloud offers are limited and quite expensive.

aprdm 2y ago

99.9% of the companies in the world will never need more than 1 beefy box running postgres with a replica for a manual failover and/or reads.

frollogaston 2y ago
Availability is trickier than scalability. An async replica can lose a few recent writes during a failover, and a synchronous replica is safer but slower. A company using some platform might not even know which one they're using until it bites them.
- wavemode 2y ago
  99.9% of companies also aren't going to feel the performance difference of synchronous replication. That being said, the setups I typically see don't even go that far. Most companies don't mitigate for the database going down in the first place. If the db goes down they just eat the downtime and fix it.

Eikon 2y ago

I run a 100 billion+ rows Postgres database [0], that is around 16TB, it's pretty painless! There are a few tricks that make it run well (PostgreSQL compiled with a non-standard block size, ZFS, careful VACUUM planning). But nothing too out of the ordinary. ATM, I insert about 150,000 rows a second, run 40,000 transactions a second, and read 4 million rows a second. Isn't "Postgres does not scale" a strawman? [0] https://www.merklemap.com/ https://www.merklemap.com/

rednafi 2y ago
Damn, that’s a chonky database. Have you written anything about the setup? I’d love to know more— is it running on a single machine? How many reader and writer DBs? What does the replication look like? What are the machine specs? Is it self-hosted or on AWS? By the way, really cool website.
- Eikon 2y ago
  I'll try to get a blog post out soon! > Damn, that’s a chonky database. Have you written anything about the setup? I’d love to know more— is it running on a single machine? How many reader and writer DBs? What does the replication look like? What are the machine specs? Is it self-hosted or on AWS? It's self-hosted on bare metal, with standby replication, normal settings, nothing "weird" there. 6 NVMe drives in raidz-1, 1024GB of memory, a 96 core AMD EPYC cpu. A single database with no partitioning (I avoid PostgreSQL partitioning as it complicates queries and weakens constraint enforcement, and IHMO is not providing much benefits outside of niche use-cases). > By the way, really cool website. Thank you!
  - stuartjohnson12 2y ago
    > It's self-hosted on bare metal, with standby replication, normal settings, nothing "weird" there. I can build scalable data storage without a flexible scalable redundant resilient fault-tolerant available distributed containerized serverless microservice cloud-native managed k8-orchestrated virtualized load balanced auto-scaled multi-region pubsub event-based stateless quantum-ready vectorized private cloud center? I won't believe it.

kristianpaul 2y ago

Logical replication works both ways, thats a good start.

lamp_book 2y ago

People talk about scale frequently as a single dimension (and usually volume as it relates to users) but that can be oversimplifying for many kinds of applications. For instance, as you are thinking about non-trivial partitioning schemes (like if there is high coupling between entities of the same kind - as you see in graphs) is when you should consider alternatives like the Bigtable-inspired DBs, since those are (relatively) more batteries included for you. > It’s funny to write this. The Internet contains at least 1 (or maybe 2) meaty blog posts about how this is done It would’ve been great to link those here. I’m guessing one refers to StackOverflow which has/had one of the more famous examples of scaled Postgres.

levkk 2y ago
I was thinking of the Instagram post years ago. And maybe the Instacart one.
- lamp_book 2y ago
  Maybe this one for Instagram? https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c https://instagram-engineering.com/sharding-ids-at-instagram-...
banashark 2y ago
Notion wrote about their experience before: https://www.notion.com/blog/sharding-postgres-at-notion https://www.notion.com/blog/sharding-postgres-at-notion https://www.notion.com/blog/the-great-re-shard https://www.notion.com/blog/the-great-re-shard

octernion 2y ago

i was reading through this and was going "huh this sounds familiar" until i read who wrote it :) neat piece of tech! excited to try it out.

fourseventy 2y ago

I run a postgresql db with a few billion rows at about 2TB right now. We don't need sharding yet but when we do I was considering Citus. Does anyone have experience implementing Citus that could comment?

caffeinated_me 2y ago
It can be great, depending on your schema and planned growth. Questions I'd be asking in your shoes: 1. Does the schema have an obvious column to use for distribution? You'll probably want to fit one of the 2 following cases, but these aren't exclusive: 1a. A use case where most traffic is scoped to a subset of data. (e.g. a multitenant system). This is the easiest use case- just make sure most of your queries contain the column (most likely tenant ID or equivalent), and partially denormalize to have it in tables where it's implicit to make your life easier. Do not use a timestamp. 1b. A rollup/analytics based use case that needs heavy parallelism (e.g. a large IoT system where you want to do analytics across a fleet). For this, you're looking for a column that has high cardinality witout too many major hot spots- in the IoT use case mentioned, this would probably be a device ID or similar 2. Are you sure you're going to grow to the scale where you need Citus? Depending on workload, it's not too hard to have a 20TB single-server PG database, and that's more than enough for a lot of companies these days. 3. When do you want to migrate? Logical replication in should work these days (haven't tested myself), but the higher the update rate and larger the database, the more painful this gets. There's not a lot of tools that are very useful for the more difficult scenarios here, but the landscape has changed since I've last had to do this 4. Do you want to run this yourself? Azure does offer a managed service, and Crunchy offers Citus on any cloud, so you have options. 5. If you're running this yourself, how are you managing HA? pg_auto_failover has some Citus support, but can be a bit tricky to get started with. I did get my Citus cluster over 1 PB at my previous job, and that's not the biggest out out there, so there's definitely room to scale, but the migration can be tricky. Disclaimer: former Citus employee

eximius 2y ago

Something I don't see in the pgdog documentation is how cross-shard joins work. Okay, if I do a simple `select * from users order by id`, you'll in-memory order the combined results for me. But if I have group by and aggregations and such? Will it resolve that correctly?

levkk 2y ago
Aggregates are a work in progress. We're going to implement them in this order: 1. count 2. max, min, sum 3. avg (needs a query rewrite to include count) Eventually, we'll do all of these: https://www.postgresql.org/docs/current/functions-aggregate.html https://www.postgresql.org/docs/current/functions-aggregate..... If you got a specific use case, reach out and we'll prioritize.
- eximius 2y ago
  Heh, no chance I can introduce this at work and hard to have a personal project requiring it. :) I think you probably need some documentation to the effect of the current state of affairs, as well as prescriptions as to how to work around it. _Most_ live workloads, even if the total dataset is huge, have a pretty small working set. So limiting DB operations to simple fetches and doing any complex operations in memory is viable, but should be prescribed as the solution or people will consider it's omission as a fault instead of a choice.
  - levkk 2y ago
    No worries. It's early days, the code and docs are just a few months in the making. I'm happy to keep you updated on the progress. If you want, send your contact info to hi@pgdog.dev. - Lev
  - levkk 2y ago
    I ended up adding support for GROUP BY: https://github.com/pgdogdev/pgdog/pull/43 https://github.com/pgdogdev/pgdog/pull/43 I had it in the back of my mind for a while, nice to have it in code. Works pretty well, as long as columns in GROUP BY are present in the result set. Otherwise, we would need to rewrite the query to include them, and remove them once we're done.

akshayshah 2y ago

Apart from being backed by Postgres instead of MySQL, is this different from Vitess (and its commercial vendor PlanetScale)? https://vitess.io/ https://vitess.io/

levkk 2y ago
The goal for this project is to be analogous to Vitess for Postgres.

Karupan 2y ago

Tangentially related: is there a good guide or setup scripts to run self hosted Postgres with backups and secondary standby? Like I just want something I can deploy to a VPS/dedicated box for all my side projects. If not is supabase the most painless way to get started?

gourneau 2y ago

I’m working with several Postgres databases that share identical schemas, and I want to make their data accessible from a single interface. Currently, I’m using Postgres FDWs to import the tables from those databases. I then create views that UNION ALL the relevant tables, adding a column to indicate the source database for each row. This works, but I’m wondering if there’s a better way — ideally something that can query multiple databases in parallel and merge the results with a source database column included. Would tools like pgdog, pgcat, pganimal be a good fit for this? I’m open to suggestions for more efficient approaches. Thanks!

sourtrident 2y ago

Funny how everyone eventually hits this point and thinks they're inventing fire - but then again, pushing your trusty old tools way past comfort is where cool engineering actually happens.

roark_howard 2y ago

Shouldn't the title be 'Learning to scale Postgres'?

Nelkins 2y ago

How does PgDog handle a column like: `id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY`

levkk 2y ago
Replace it with a function that makes sure the id coming out matches the sharding schema. Assuming it's coming from a sequence, we're consuming it until we get a matching number. It would be good to know what is behind the generated column in your use case.

moribvndvs 2y ago

DynamoDB is most certainly not the way.