4 ms·

That is very cool and much needed, but I am _paranoid_ about hosting the data myself. I've lost too many databases over the years to the most random mistakes. G

That is very cool and much needed, but I am _paranoid_ about hosting the data myself. I've lost too many databases over the years to the most random mistakes. Granted, technology has matured a lot over the last decode, but still... the cost (the nights spent debugging and troubleshooting) of maintaining the database yourself is just not worth it when managed solutions exist.

berkle4455 3y ago
I think with any database the solution is simply backups no? backups that preferably aren’t tied to the hosted solution at all.
- panyam 3y ago
  Actually not quite. Backups (assuming you are doing single node) still needs you to decide on your SLOs - RTO (how long it takes to restore) and RPO (how much data lose you can suffer) numbers. On the instant snazy end you have streaming backups and recovery and then on the other extreme you have backup once in N hours/days and restore taking how ever long it takes to restore (so you have customer outages you need to negotiate. Now let us involve multi node, (both replication and partitioning of shards). As shards go and up and down ensuring data is in sync etc is a hard consistency problem and needs man years of operational excellence and bug fixing. So when people think databases - they think of the cool stuff - the database engine that does relational algebra and handles SQL queries. That is (IMO) only 1% of a practical, performant, reliable database (offering).
  - tetha 3y ago
    Also what about the customer that deleted an important thing 6 weeks ago and absolutely needs it recovered? BTW, it's just one tentant in that DB, the other shouldn't be recovered, naturally.
    - aprilllll 3y ago
      In that case, it’d probably be best to just handle deletions at the application layer (e.g., setting a “deleted_at” timestamp field with scheduled permanent deletions later). And in terms of data compliance, it’s very important to make sure permanent deletions propagate through your backup systems within a reasonable amount of time - Google Cloud[1], for example, is ~180 days. [1] https://services.google.com/fh/files/misc/gcp_data_deletion_nda.pdf https://services.google.com/fh/files/misc/gcp_data_deletion_...
  - api 3y ago
    Maybe if you are gigantic, but there is a long tail of people with <1TB database needs that don’t really need shards and can be well served by a fail over cluster with a master and one or two replicas that can become masters. These days you don’t really need shards until you hit many terabytes or even more depending on your read and especially write load. NVMe storage is really fast and lots of RAM for caching has become cheap.
    - panyam 3y ago
      So my point was around all things a managed for gives you (eg sharding and replication). Even by the time I had to setup streaming replication and have to worry about wal drifts it is easier to pay a managed provider no?
- crabbone 3y ago
  Backups? Do you want to share your idea about how you'd do backups? Especially to a distributed database? Here are some of the questions you'll have to answer and some options you will have to consider before you go there: Let's start with the heavy stuff: consistency groups. I.e. groups of bulk storage that underlines your entire infrastructure that ensure that your application and database(s) all recover to the shared state once they crash. To better explain this concept, consider this: you have an application that works with two databases, let's say a document database to store documents uploaded by users (which are later parsed by the application and transformed into records in a relational database). Now, each database provides best consistency guarantees... but they still can fail independently and subsequently recover to different state, where, for example, the document database can be ahead of the relational one (and lose some data). Similar problems face sharded databases. How geographically far are you going to send your backups? You see, the closer to the working server they are, the higher is the chance you'll lose them together. But, here's the problem: the further away the backups are, the lower is your ability to keep the backup up-to-date with the database, and, subsequently, more data to lose. Well, backups inherently lose data (for the time between the last backup and the time of the crash). So, if you don't want to lose data at all, you probably want replication rather than backups. And you probably want online replication (but then the distance between the replicas is even more important than in the case with backups). Also, backups are huge. If you want to ship them outside of the facilities of the storage vendor... that's going to be expensive. Another point to consider: databases provide consistency guarantees, but does your database provide consistency guarantees you want? Is every relation encoded by using foreign keys, or does the application have some knowledge of how to interpret pieces of data and stitch them together into relationships unknown to your database? Are you sure that every operation that requires atomicity is implemented in a database rather than application (which doesn't enforce atomicity)? What if you stick a backup (recovery point) in a precise moment when your application was doing something that was meant to be atomic, but the application author didn't know how to express in SQL (because in their fear of technology they chose to use Hybernate or SQLAlchemy etc.)? And if you do so, it spoils your backup...
  - gbartolini 3y ago
    I actually do not understand the point here. And maybe you are not very familiar with the concept of transactions. Backups can only account for committed transactions. However, we are talking about Postgres, here, not a generic database. PostgreSQL natively provides continuous backup, streaming replication, including synchronous (controlled at transaction level), cascading, and logical. You can easily implement with Postgres, even in Kubernetes with CloudNativePG, architectures with RPO=0 (yes, zero data loss) and low RTO in the same Kubernetes cluster (normally a region), and RPO <= 5 minutes with low RTO across regions. Out of the box, with CloudNativePG, through replica clusters. We are also now launching native declarative support for Kubernetes Volume Snapshot API in CloudNativePG with the possibility to use incremental/differential backup and recovery to reduce RTO in case of very large databases recovery (like ... dozens of seconds to restore 500GB databases). So maybe it is time to reconsider some assumptions.
smartbit 3y ago
The whole clue of cloudnative-pg is that it makes supporting Postgres so much easier. My DevOps colleague and I had no experience with running PG at all, studied the backup and replication chapters of Simon Riggs book [0], read the excellent cnpg documentation and deployed Postgres clusters on Lab, Dev & Prod k8s clusters. Production started sept 2022. Not a huge use case, 5TB of data, constant stream of IoT type of data. Continuous backup to Minio on TrueNAS Core. Later we added both a hot-standby and a backup replica in a secondary site for disaster recovery. No production issues like you describe. Many EDB & 2nd Quadrant have 10+ years experience as core Postgres commiters. I had the pleasure meeting some at Kubecon EU in Amsterdam, friendly Italian & UK Engineers I felt I can trust to make good software. They saw the issues you describe and took a step forward by engineering a proper Kubernetes Operator, introducing it at Kubecon EU 2022 to the public when it reached version 1.5, after they had been running it on their DBaaS production clusters for some time. Highly recommended. IMHO running Postgres on-prem in most use cases is cheaper than a hosted version. Especially taking into account Schrem 1, 2 & (upcoming) 3 [1]. [0] https://www.packtpub.com/product/postgresql-14-administration-cookbook/9781803248974 https://www.packtpub.com/product/postgresql-14-administratio... [1] https://noyb.eu/en/european-commission-gives-eu-us-data-transfers-third-round-cjeu https://noyb.eu/en/european-commission-gives-eu-us-data-tran...
- crabbone 3y ago
  > supporting Postgres so much easier. Easier in the sense that you don't know what it's doing... It's not hard to deploy PostgreSQL and make it do something. Making it do what it needs to do well is a completely different thing. Tools like this one (or any other management tool that comes from outside) aren't helping you to make it easier. To make things easy you need to learn how to do them. They make it easy to waste a ton of resources on things you don't need for the faction of performance you can get.
  - smartbit 3y ago
    The OP was complaining it was difficult to maintain Postgres, with late nights. Expensive in human resource costs. You’re now switching to a different topic, from total cost of ownership to resource cost of ownership, not taking into account human operator costs. EDB argues that human costs of operating an highly available cluster with hot standby etc using cnpg & the abstraction it offers is much lower than without an Kubernetes operator. Of course a highly skilled and experienced Postgres guru with years of experience can run a highly available cluster on less compute resources. But what happens if that person is not available? On holiday leave, with pension? How many of those gurus would a company need to employ? How many of these self maintained pg clusters can a couple of these gurus maintain?
ilyt 3y ago
If you fuck up backups cloud won't save you... > Granted, technology has matured a lot over the last decode, Running PostgreSQL database by yourself was just as easy decade ago as now. Like, I get not wanting to, especially if ops is not your job, but compared to actually programming apps it's not that hard job job till you get to TB+ sizes. At least compared to writing the more complex apps using it.
api 3y ago
This is a strong indictment of most database software. It shouldn’t be this hard.
- spion 3y ago
  The problem (very reliable storage of large amounts of data) is really hard too.
  - api 3y ago
    So are compilers, virtual networks, and encryption, but I have tools for all those that don’t inspire the kind of nameless terror that databases do. The failure modes are different but the problems are not really easier intellectually speaking. There are databases like CockroachDB that are more modern and a lot more approachable for high availability but for some reason everyone adores Postgres. I’m not sure why. It’s arcane and clunky and feels like 1980s Unix software.
    - spion 3y ago
      That's because they're not really that hard. Compilers are essentially pure functions, encryption is as well. State is another beast entirely. If I had to put on my innovator cap and do a relatively weakly informed guess, I'd say its because querying capabilities and reliable storage are still too conflated. If we focused on reliable storage that only has great replication support to other querying systems, the problem might get easier.
    - smilliken 3y ago
      PostgreSQL is the very best of the clunky 80s unix software. Its features and reliability are unmatched. The core contributors have earned the trust of the community. You lose a lot of features and performance when you go from a single server database to a distributed system. Distributed systems are significantly more complex to set up, administer, and debug. For nearly all databases in use, the tradeoff isn't worth it. It's really no wonder that postgresql is as popular as it is.

berkle4455 3y ago

I think with any database the solution is simply backups no? backups that preferably aren’t tied to the hosted solution at all.

panyam 3y ago
Actually not quite. Backups (assuming you are doing single node) still needs you to decide on your SLOs - RTO (how long it takes to restore) and RPO (how much data lose you can suffer) numbers. On the instant snazy end you have streaming backups and recovery and then on the other extreme you have backup once in N hours/days and restore taking how ever long it takes to restore (so you have customer outages you need to negotiate. Now let us involve multi node, (both replication and partitioning of shards). As shards go and up and down ensuring data is in sync etc is a hard consistency problem and needs man years of operational excellence and bug fixing. So when people think databases - they think of the cool stuff - the database engine that does relational algebra and handles SQL queries. That is (IMO) only 1% of a practical, performant, reliable database (offering).
- tetha 3y ago
  Also what about the customer that deleted an important thing 6 weeks ago and absolutely needs it recovered? BTW, it's just one tentant in that DB, the other shouldn't be recovered, naturally.
  - aprilllll 3y ago
    In that case, it’d probably be best to just handle deletions at the application layer (e.g., setting a “deleted_at” timestamp field with scheduled permanent deletions later). And in terms of data compliance, it’s very important to make sure permanent deletions propagate through your backup systems within a reasonable amount of time - Google Cloud[1], for example, is ~180 days. [1] https://services.google.com/fh/files/misc/gcp_data_deletion_nda.pdf https://services.google.com/fh/files/misc/gcp_data_deletion_...
- api 3y ago
  Maybe if you are gigantic, but there is a long tail of people with <1TB database needs that don’t really need shards and can be well served by a fail over cluster with a master and one or two replicas that can become masters. These days you don’t really need shards until you hit many terabytes or even more depending on your read and especially write load. NVMe storage is really fast and lots of RAM for caching has become cheap.
  - panyam 3y ago
    So my point was around all things a managed for gives you (eg sharding and replication). Even by the time I had to setup streaming replication and have to worry about wal drifts it is easier to pay a managed provider no?
crabbone 3y ago
Backups? Do you want to share your idea about how you'd do backups? Especially to a distributed database? Here are some of the questions you'll have to answer and some options you will have to consider before you go there: Let's start with the heavy stuff: consistency groups. I.e. groups of bulk storage that underlines your entire infrastructure that ensure that your application and database(s) all recover to the shared state once they crash. To better explain this concept, consider this: you have an application that works with two databases, let's say a document database to store documents uploaded by users (which are later parsed by the application and transformed into records in a relational database). Now, each database provides best consistency guarantees... but they still can fail independently and subsequently recover to different state, where, for example, the document database can be ahead of the relational one (and lose some data). Similar problems face sharded databases. How geographically far are you going to send your backups? You see, the closer to the working server they are, the higher is the chance you'll lose them together. But, here's the problem: the further away the backups are, the lower is your ability to keep the backup up-to-date with the database, and, subsequently, more data to lose. Well, backups inherently lose data (for the time between the last backup and the time of the crash). So, if you don't want to lose data at all, you probably want replication rather than backups. And you probably want online replication (but then the distance between the replicas is even more important than in the case with backups). Also, backups are huge. If you want to ship them outside of the facilities of the storage vendor... that's going to be expensive. Another point to consider: databases provide consistency guarantees, but does your database provide consistency guarantees you want? Is every relation encoded by using foreign keys, or does the application have some knowledge of how to interpret pieces of data and stitch them together into relationships unknown to your database? Are you sure that every operation that requires atomicity is implemented in a database rather than application (which doesn't enforce atomicity)? What if you stick a backup (recovery point) in a precise moment when your application was doing something that was meant to be atomic, but the application author didn't know how to express in SQL (because in their fear of technology they chose to use Hybernate or SQLAlchemy etc.)? And if you do so, it spoils your backup...
- gbartolini 3y ago
  I actually do not understand the point here. And maybe you are not very familiar with the concept of transactions. Backups can only account for committed transactions. However, we are talking about Postgres, here, not a generic database. PostgreSQL natively provides continuous backup, streaming replication, including synchronous (controlled at transaction level), cascading, and logical. You can easily implement with Postgres, even in Kubernetes with CloudNativePG, architectures with RPO=0 (yes, zero data loss) and low RTO in the same Kubernetes cluster (normally a region), and RPO <= 5 minutes with low RTO across regions. Out of the box, with CloudNativePG, through replica clusters. We are also now launching native declarative support for Kubernetes Volume Snapshot API in CloudNativePG with the possibility to use incremental/differential backup and recovery to reduce RTO in case of very large databases recovery (like ... dozens of seconds to restore 500GB databases). So maybe it is time to reconsider some assumptions.

smartbit 3y ago

The whole clue of cloudnative-pg is that it makes supporting Postgres so much easier. My DevOps colleague and I had no experience with running PG at all, studied the backup and replication chapters of Simon Riggs book [0], read the excellent cnpg documentation and deployed Postgres clusters on Lab, Dev & Prod k8s clusters. Production started sept 2022. Not a huge use case, 5TB of data, constant stream of IoT type of data. Continuous backup to Minio on TrueNAS Core. Later we added both a hot-standby and a backup replica in a secondary site for disaster recovery. No production issues like you describe. Many EDB & 2nd Quadrant have 10+ years experience as core Postgres commiters. I had the pleasure meeting some at Kubecon EU in Amsterdam, friendly Italian & UK Engineers I felt I can trust to make good software. They saw the issues you describe and took a step forward by engineering a proper Kubernetes Operator, introducing it at Kubecon EU 2022 to the public when it reached version 1.5, after they had been running it on their DBaaS production clusters for some time. Highly recommended. IMHO running Postgres on-prem in most use cases is cheaper than a hosted version. Especially taking into account Schrem 1, 2 & (upcoming) 3 [1]. [0] https://www.packtpub.com/product/postgresql-14-administration-cookbook/9781803248974 https://www.packtpub.com/product/postgresql-14-administratio... [1] https://noyb.eu/en/european-commission-gives-eu-us-data-transfers-third-round-cjeu https://noyb.eu/en/european-commission-gives-eu-us-data-tran...

crabbone 3y ago
> supporting Postgres so much easier. Easier in the sense that you don't know what it's doing... It's not hard to deploy PostgreSQL and make it do something. Making it do what it needs to do well is a completely different thing. Tools like this one (or any other management tool that comes from outside) aren't helping you to make it easier. To make things easy you need to learn how to do them. They make it easy to waste a ton of resources on things you don't need for the faction of performance you can get.
- smartbit 3y ago
  The OP was complaining it was difficult to maintain Postgres, with late nights. Expensive in human resource costs. You’re now switching to a different topic, from total cost of ownership to resource cost of ownership, not taking into account human operator costs. EDB argues that human costs of operating an highly available cluster with hot standby etc using cnpg & the abstraction it offers is much lower than without an Kubernetes operator. Of course a highly skilled and experienced Postgres guru with years of experience can run a highly available cluster on less compute resources. But what happens if that person is not available? On holiday leave, with pension? How many of those gurus would a company need to employ? How many of these self maintained pg clusters can a couple of these gurus maintain?

ilyt 3y ago

If you fuck up backups cloud won't save you... > Granted, technology has matured a lot over the last decode, Running PostgreSQL database by yourself was just as easy decade ago as now. Like, I get not wanting to, especially if ops is not your job, but compared to actually programming apps it's not that hard job job till you get to TB+ sizes. At least compared to writing the more complex apps using it.

api 3y ago

This is a strong indictment of most database software. It shouldn’t be this hard.

spion 3y ago
The problem (very reliable storage of large amounts of data) is really hard too.
- api 3y ago
  So are compilers, virtual networks, and encryption, but I have tools for all those that don’t inspire the kind of nameless terror that databases do. The failure modes are different but the problems are not really easier intellectually speaking. There are databases like CockroachDB that are more modern and a lot more approachable for high availability but for some reason everyone adores Postgres. I’m not sure why. It’s arcane and clunky and feels like 1980s Unix software.
  - spion 3y ago
    That's because they're not really that hard. Compilers are essentially pure functions, encryption is as well. State is another beast entirely. If I had to put on my innovator cap and do a relatively weakly informed guess, I'd say its because querying capabilities and reliable storage are still too conflated. If we focused on reliable storage that only has great replication support to other querying systems, the problem might get easier.
  - smilliken 3y ago
    PostgreSQL is the very best of the clunky 80s unix software. Its features and reliability are unmatched. The core contributors have earned the trust of the community. You lose a lot of features and performance when you go from a single server database to a distributed system. Distributed systems are significantly more complex to set up, administer, and debug. For nearly all databases in use, the tradeoff isn't worth it. It's really no wonder that postgresql is as popular as it is.