6 ms·

> Now, there are extensions out there that implement versioning – temporal_tables being the most popular I think – but none are supported for managed Postgres i

> Now, there are extensions out there that implement versioning – temporal_tables being the most popular I think – but none are supported for managed Postgres instances on e.g. Azure or AWS. This means that if we want system-versioned tables, we’re forced to make it ourselves. This really provokes a wild geek rage inside me. Not that it is untrue, but that this is the state of things. It's that cloud providers are now often seen as the only responsible way to operate infrastructure, and in the process we hand over so much control of our tooling. We let ourselves be convinced that simplicity was worth a 10x premium, and admitted we were not grown up enough to handle the tools we had made for ourselves. Sure, I know it is more complex than that. But I also feel sad about it.

devnonymous 3y ago
Exactly ! What's worse that we seem to have developed this all-in-or-none-at-all approach towards cloud services. Whatever happened to a hybrid approah of using the cloud only for things that you wanted ?
- cqqxo4zV46cp 3y ago
  A database is near the top of most people’s list for things that they don’t want to manage themselves. This rant is verging on irrelevant.
  - sroussey 3y ago
    I used to have some MySQL servers running for so many years that I was afraid to restart the machines.
  - DaiPlusPlus 3y ago
    > manage themselves. Be careful with the language there.... I assume you meant that no-one wants to have to run and administer an RDBMS like Postgres, MSSQL, Oracle, etc - especially not things like backups, restoration, disaster-recovery, figure out storage, ensure high-availability, and maybe even things like user administration and index management: i.e. a DBA's grunt-work - and yes, I agree with you entirely. ...but using the other interpretation of "manage", people might think you meant that people don't want to know how to properly design a normalized schema and apply the business-domain, knowing how to clean-up data appropriately (i.e. the other half of a DBA's job), because we can either shove it all into a cloud vendor's proprietary (and horrendously expensive) non-relational database or data-lake solution (CosmosDB, etc) - or that we can outsource database modelling and implementation of DDL to an LLM - which I'll admit, is quite capable as-is for a first-draft for DB scaffolding and getting better all the time, but ultimately isn't able to take a proactive or autonomous approach to managing an org's databases; I don't want to seem like I'm advocating economic-protectionism of DBAs, but I get the impression LLM-based DBA-bots are being aggressively pitched to smaller-sized orgs that don't know any better, at a time when the technology is still very new, with potentially serious repercussions w.r.t. data-security and compliance (and ChatGPT still gives me T-SQL that uses Oracle functions...).
    - nyrikki 3y ago
      Most people I interact with are targeting CQRS as the CRUD model is what most people use. The problem with the DB central approach is many faceted as as most people really just need a repository the inherent problems testing, vertical scaling, availability, typical default of procedural cohesion etc... is why people don't care. The self managed DB servers aren't cheap once you have to scale, typically have availability issues due to ACID rules typically limiting servers to only two AZ's per region etc... It is actually adopting best practices for software maintainability and scalability that are getting rid of most DBA needs....because most databases use aren't doing batch processing, report generation, backups, job scheduling, or other needs that tend to be the right use cases for relational databases. Concepts like encapsulation, persistence ignorance, and other methods of ensuring that the persistence layer is as loosely coupled as possible have proven to be cheaper, easier to test, and avoid the lock in you mention above. As i have had a professional relationship with AWS/GCS here are a few anti-patterns from Microsoft that demonstrate this. https://learn.microsoft.com/en-us/azure/architecture/antipatterns/monolithic-persistence/ https://learn.microsoft.com/en-us/azure/architecture/antipat... https://learn.microsoft.com/en-us/azure/architecture/antipatterns/busy-database/ https://learn.microsoft.com/en-us/azure/architecture/antipat... https://learn.microsoft.com/en-us/azure/architecture/antipatterns/synchronous-io/ https://learn.microsoft.com/en-us/azure/architecture/antipat... I made a good living as a consultant in the .com era helping companies throw money at their foreign key loving branching stored procedure systems that they couldn't even get to scale past the angel round some times. And entire companies like AT&T wireless went under in a large part due to failed upgrades due to issues related to having logic in the DB. DBs can be a good persistence layer when a system is simple and small. But really to work well within a cloud or distributed context they need to be treated as repositories unless there is a compelling technical reason to treat them differently. 'When all you have is a hammer' approach monolithic persistence layer what the primary blocker when I worked with cloud providers to help customers migrate. And they always were paranoid of moving things out of the database. And the funny thing is that most of them were trying to move to the cloud because vertical scaling on-prem DB servers became too expensive and didn't deliver the performance vendors promised. Had companies followed say the design patterns of micro-services, where each service context has it's own DB, they probably could have stayed on-prem at lower costs. But for the clients I worked with, who had failed to migrate on their own. Complex fragile schemas, triggers, sp's, etc... where almost always the main issue. And we knew this even before the commercial internet existed.
refset 3y ago
It's especially tragic knowing that Postgres did originally have system-time-like versioning built-in. Instead we get to enjoy being upsold on proprietary ETL-to-Redshift, AlloyDB, etc.
- ttfkam 3y ago
  Citation needed? There have been one-off, incomplete iterations in extensions, but core and contrib have never had this.
  - refset 3y ago
    I have never researched the timeline properly to understand at which point the concept/code got ditched, but it was part of Stonebraker's 1985 vision: https://dsf.berkeley.edu/papers/ERL-M85-95.pdf https://dsf.berkeley.edu/papers/ERL-M85-95.pdf > POSTQUEL allows users to save and query historical data and versions. By default, data in a relation is never deleted or updated. Conventional retrievals always access the current tuples in the relation. Historical data can be accessed by indicating the desired time when defining a tuple variable. I looked this up during another thread which also has some other sources if you want to dig further: https://news.ycombinator.com/item?id=37956687 https://news.ycombinator.com/item?id=37956687
    - ttfkam 3y ago
      So pre-PostgreSQL then.
      - refset 3y ago
        I would guess so, yes.
  - krab 3y ago
    https://www.postgresql.org/docs/6.3/c0503.htm https://www.postgresql.org/docs/6.3/c0503.htm
    - 3y ago
zer00eyz 3y ago
>>> It's that cloud providers are now often seen as the only responsible way to operate infrastructure 23 billion. Thats the profit for AWS in 2023. 23 billon that AWS users pissed away. AWS is NOT cheap. I have quite a few clients who, could save lots of money dumping their cloud provider and setting up their own racks, hiring SA's. I have others who, if they optimized any of their stack could cut their AWS bills by half. Having survived the 2000 bubble the lessons are going to have to be re-learned. Everything has cost and value and managing that is going to become an engineering task in the near future.
- Rapzid 3y ago
  Business transactions aren't zero sum.
- Cthulhu_ 3y ago
  I mean AWS optimization is a valid consultancy/self employment career; it's like tech debt in coding, you make it work first, prove that it works, get value from it, and only then look into optimizing. Would these clients have been able to make the upfront investment to set up their own racks and hire / train the relevant staff? That's the sales pitch for AWS and other cloud providers, you can get started a lot faster, make money, only then look into optimizing. Running your own servers is easy to underestimate. Running it with the reliability, backup and fallbacks that cloud providers offer is also easily overlooked. How much does it cost to set up a multi region cluster yourself? That said, there are some real life examples of companies that moved away from AWS in favor of their own servers, like Dropbox which bootstrapped on AWS, then built their own eight years later (https://techcrunch.com/2017/09/15/why-dropbox-decided-to-drop-aws-and-build-its-own-infrastructure-and-network/ https://techcrunch.com/2017/09/15/why-dropbox-decided-to-dro...).
  - sgarland 3y ago
    > you can get started a lot faster, make money, only then look into optimizing. IME, companies often will first fall into the trap of minimum spend with AWS for larger discounts, such that even when you _do_ try to optimize things, it doesn’t matter. There is no greater disincentive than saving 5 digits per month, only to be told “thanks, but it doesn’t matter.” Tangentially, I think the rise of cloud and SaaS to blame more than anything for the I-shaped engineer. Why bother learning anything about optimization when K8s will silently add another node to the cluster as needed? Why bother learning SQL when ORMs abound, and your Serverless DB will happily upsize to deal with your terrible schema? After all, you’re being told to ship, ship, ship, so there’s not really any time to learn those things anyway. I simultaneously love and hate cloud providers, and am eagerly awaiting the slow but steady turn towards on-prem/colo. 37Signals has the right idea.
olavgg 3y ago
I don't feel sad at all, I feel my competition is slacking and that is my benefit.
verticalscaler 3y ago
For a small greenfield project rolling your own Postgres is a very viable option with attractive trade offs for some. If it is now considered rarefied knowledge in your circles you should be glad as this might a competitive advantage. Let the other startups spend x10-100 on their resume driven development and blaze through their runway. Common story actually. But suppose you don't need it and RDS as-is covers all your needs, which it often does, why rage? This is a continuum. If an early decision to punt and outsource it to the cloud was made it could always be reversed at an opportune moment. I don't think competent people see it as "the only way" just the most common and pragmatic option.
- cpursley 3y ago
  For a small project (assuming the goal is profitability), rolling your own Postgres seems mighty distracting.
hobs 3y ago
eh, in my experience system managed temporal tables are crap anyway, they have a specific set of circumstances built in and anytime you break out of those you basically have to design your own trigger based solution anyway.
- DaiPlusPlus 3y ago
  Can you give some examples of the problems you've had with them? We use SQL Server's temporal-tables - and while they do introduce some restrictions (e.g. you can't mark a column as "ignored" for temporal changes, and only system-versioning (not application-versioning) is currently supported) the feature has overall been an absolute lifesaver for allowing us to do data-recovery from user-error on a per-record basis - all from within our application or directly in T-SQL; whereas previously we'd need to get do a full mount of a PITR backup which requires a lot of manual steps and can take at least an hour in most cases - and then hope that we got the exact point-in-time correct. Even for its use as an auto-auditing system, it's great: we often get customer complaints about us "losing their data" or that we corrupted their records, when in-fact, we have the historical rows and can see that it was one of their own employees (or family-members...) mucking around. Previously we'd have cheerfly accepted the user's complaint and given them a refund or service-credit because it was too much hassle to dig-up the weeks-old HTTP logs or have anxieties about we having any non-transaction-safe OLTP code running causing corruption that we don't know about :S
  - hobs 3y ago
    Schema changes basically causing heterogeneous representations of data. If you only add columns forever and never change types its fine. Oh, and being able to specify the time a thing happened is sometimes useful (though you don't want everyone doing that of course...) Also double checking the docs because its been 5 years since I have reasonably touched SQL Server - no indexes on historical tables so it gets slow fast, Filetable doesn't work* /nvarchar(max) records bloat you hard core. I find that a log record is much better as a non relational/document type record that is isomorphic to the record so I can schema on read if I need to, and if you want to JSON query that stuff it works fine too. * but nobody uses it anymore
donor20 3y ago
RDS is 50 cents or less a day for postgresql on a three year term for a small instance. I use AWS / RDS - I’ve got to believe RDS is spinning money for AWS because of all the parts I want to be reliable this is way up there.
deterministic 3y ago
I am sorry to say this but most developers either don’t have the ability to think for themselves or can’t be bothered. You might disagree with this statement of course but that is truly my experience (30+ years of working professionally in software development). Try this: Next time a developer confidently suggests that you should use tech X, ask them that the pros/cons are compared with using tech Y/Z. Ask about performance/failover/latency/team skills required etc. Most developers in my experience completely fail to give an intelligent answer. They are just loudspeakers confidently copying the opinions of somebody else. Usually somebody who benefits financially from developers using tech X.
arjunlol 3y ago
This is actually what we're building @ Bemi https://bemi.io/ https://bemi.io/ We're hoping to make it so that this becomes automatic with Postgres vs moving to specialized databases. We recently open sourced the core tech that implements system versioned tables https://github.com/BemiHQ/bemi https://github.com/BemiHQ/bemi, check it out if interested :)

devnonymous 3y ago

Exactly ! What's worse that we seem to have developed this all-in-or-none-at-all approach towards cloud services. Whatever happened to a hybrid approah of using the cloud only for things that you wanted ?

cqqxo4zV46cp 3y ago
A database is near the top of most people’s list for things that they don’t want to manage themselves. This rant is verging on irrelevant.
- sroussey 3y ago
  I used to have some MySQL servers running for so many years that I was afraid to restart the machines.
- DaiPlusPlus 3y ago
  > manage themselves. Be careful with the language there.... I assume you meant that no-one wants to have to run and administer an RDBMS like Postgres, MSSQL, Oracle, etc - especially not things like backups, restoration, disaster-recovery, figure out storage, ensure high-availability, and maybe even things like user administration and index management: i.e. a DBA's grunt-work - and yes, I agree with you entirely. ...but using the other interpretation of "manage", people might think you meant that people don't want to know how to properly design a normalized schema and apply the business-domain, knowing how to clean-up data appropriately (i.e. the other half of a DBA's job), because we can either shove it all into a cloud vendor's proprietary (and horrendously expensive) non-relational database or data-lake solution (CosmosDB, etc) - or that we can outsource database modelling and implementation of DDL to an LLM - which I'll admit, is quite capable as-is for a first-draft for DB scaffolding and getting better all the time, but ultimately isn't able to take a proactive or autonomous approach to managing an org's databases; I don't want to seem like I'm advocating economic-protectionism of DBAs, but I get the impression LLM-based DBA-bots are being aggressively pitched to smaller-sized orgs that don't know any better, at a time when the technology is still very new, with potentially serious repercussions w.r.t. data-security and compliance (and ChatGPT still gives me T-SQL that uses Oracle functions...).
  - nyrikki 3y ago
    Most people I interact with are targeting CQRS as the CRUD model is what most people use. The problem with the DB central approach is many faceted as as most people really just need a repository the inherent problems testing, vertical scaling, availability, typical default of procedural cohesion etc... is why people don't care. The self managed DB servers aren't cheap once you have to scale, typically have availability issues due to ACID rules typically limiting servers to only two AZ's per region etc... It is actually adopting best practices for software maintainability and scalability that are getting rid of most DBA needs....because most databases use aren't doing batch processing, report generation, backups, job scheduling, or other needs that tend to be the right use cases for relational databases. Concepts like encapsulation, persistence ignorance, and other methods of ensuring that the persistence layer is as loosely coupled as possible have proven to be cheaper, easier to test, and avoid the lock in you mention above. As i have had a professional relationship with AWS/GCS here are a few anti-patterns from Microsoft that demonstrate this. https://learn.microsoft.com/en-us/azure/architecture/antipatterns/monolithic-persistence/ https://learn.microsoft.com/en-us/azure/architecture/antipat... https://learn.microsoft.com/en-us/azure/architecture/antipatterns/busy-database/ https://learn.microsoft.com/en-us/azure/architecture/antipat... https://learn.microsoft.com/en-us/azure/architecture/antipatterns/synchronous-io/ https://learn.microsoft.com/en-us/azure/architecture/antipat... I made a good living as a consultant in the .com era helping companies throw money at their foreign key loving branching stored procedure systems that they couldn't even get to scale past the angel round some times. And entire companies like AT&T wireless went under in a large part due to failed upgrades due to issues related to having logic in the DB. DBs can be a good persistence layer when a system is simple and small. But really to work well within a cloud or distributed context they need to be treated as repositories unless there is a compelling technical reason to treat them differently. 'When all you have is a hammer' approach monolithic persistence layer what the primary blocker when I worked with cloud providers to help customers migrate. And they always were paranoid of moving things out of the database. And the funny thing is that most of them were trying to move to the cloud because vertical scaling on-prem DB servers became too expensive and didn't deliver the performance vendors promised. Had companies followed say the design patterns of micro-services, where each service context has it's own DB, they probably could have stayed on-prem at lower costs. But for the clients I worked with, who had failed to migrate on their own. Complex fragile schemas, triggers, sp's, etc... where almost always the main issue. And we knew this even before the commercial internet existed.

refset 3y ago

It's especially tragic knowing that Postgres did originally have system-time-like versioning built-in. Instead we get to enjoy being upsold on proprietary ETL-to-Redshift, AlloyDB, etc.

ttfkam 3y ago
Citation needed? There have been one-off, incomplete iterations in extensions, but core and contrib have never had this.
- refset 3y ago
  I have never researched the timeline properly to understand at which point the concept/code got ditched, but it was part of Stonebraker's 1985 vision: https://dsf.berkeley.edu/papers/ERL-M85-95.pdf https://dsf.berkeley.edu/papers/ERL-M85-95.pdf > POSTQUEL allows users to save and query historical data and versions. By default, data in a relation is never deleted or updated. Conventional retrievals always access the current tuples in the relation. Historical data can be accessed by indicating the desired time when defining a tuple variable. I looked this up during another thread which also has some other sources if you want to dig further: https://news.ycombinator.com/item?id=37956687 https://news.ycombinator.com/item?id=37956687
  - ttfkam 3y ago
    So pre-PostgreSQL then.
    - refset 3y ago
      I would guess so, yes.
- krab 3y ago
  https://www.postgresql.org/docs/6.3/c0503.htm https://www.postgresql.org/docs/6.3/c0503.htm
  - 3y ago

zer00eyz 3y ago

>>> It's that cloud providers are now often seen as the only responsible way to operate infrastructure 23 billion. Thats the profit for AWS in 2023. 23 billon that AWS users pissed away. AWS is NOT cheap. I have quite a few clients who, could save lots of money dumping their cloud provider and setting up their own racks, hiring SA's. I have others who, if they optimized any of their stack could cut their AWS bills by half. Having survived the 2000 bubble the lessons are going to have to be re-learned. Everything has cost and value and managing that is going to become an engineering task in the near future.

Rapzid 3y ago
Business transactions aren't zero sum.
Cthulhu_ 3y ago
I mean AWS optimization is a valid consultancy/self employment career; it's like tech debt in coding, you make it work first, prove that it works, get value from it, and only then look into optimizing. Would these clients have been able to make the upfront investment to set up their own racks and hire / train the relevant staff? That's the sales pitch for AWS and other cloud providers, you can get started a lot faster, make money, only then look into optimizing. Running your own servers is easy to underestimate. Running it with the reliability, backup and fallbacks that cloud providers offer is also easily overlooked. How much does it cost to set up a multi region cluster yourself? That said, there are some real life examples of companies that moved away from AWS in favor of their own servers, like Dropbox which bootstrapped on AWS, then built their own eight years later (https://techcrunch.com/2017/09/15/why-dropbox-decided-to-drop-aws-and-build-its-own-infrastructure-and-network/ https://techcrunch.com/2017/09/15/why-dropbox-decided-to-dro...).
- sgarland 3y ago
  > you can get started a lot faster, make money, only then look into optimizing. IME, companies often will first fall into the trap of minimum spend with AWS for larger discounts, such that even when you _do_ try to optimize things, it doesn’t matter. There is no greater disincentive than saving 5 digits per month, only to be told “thanks, but it doesn’t matter.” Tangentially, I think the rise of cloud and SaaS to blame more than anything for the I-shaped engineer. Why bother learning anything about optimization when K8s will silently add another node to the cluster as needed? Why bother learning SQL when ORMs abound, and your Serverless DB will happily upsize to deal with your terrible schema? After all, you’re being told to ship, ship, ship, so there’s not really any time to learn those things anyway. I simultaneously love and hate cloud providers, and am eagerly awaiting the slow but steady turn towards on-prem/colo. 37Signals has the right idea.

olavgg 3y ago

I don't feel sad at all, I feel my competition is slacking and that is my benefit.

verticalscaler 3y ago

For a small greenfield project rolling your own Postgres is a very viable option with attractive trade offs for some. If it is now considered rarefied knowledge in your circles you should be glad as this might a competitive advantage. Let the other startups spend x10-100 on their resume driven development and blaze through their runway. Common story actually. But suppose you don't need it and RDS as-is covers all your needs, which it often does, why rage? This is a continuum. If an early decision to punt and outsource it to the cloud was made it could always be reversed at an opportune moment. I don't think competent people see it as "the only way" just the most common and pragmatic option.

cpursley 3y ago
For a small project (assuming the goal is profitability), rolling your own Postgres seems mighty distracting.

hobs 3y ago

eh, in my experience system managed temporal tables are crap anyway, they have a specific set of circumstances built in and anytime you break out of those you basically have to design your own trigger based solution anyway.

DaiPlusPlus 3y ago
Can you give some examples of the problems you've had with them? We use SQL Server's temporal-tables - and while they do introduce some restrictions (e.g. you can't mark a column as "ignored" for temporal changes, and only system-versioning (not application-versioning) is currently supported) the feature has overall been an absolute lifesaver for allowing us to do data-recovery from user-error on a per-record basis - all from within our application or directly in T-SQL; whereas previously we'd need to get do a full mount of a PITR backup which requires a lot of manual steps and can take at least an hour in most cases - and then hope that we got the exact point-in-time correct. Even for its use as an auto-auditing system, it's great: we often get customer complaints about us "losing their data" or that we corrupted their records, when in-fact, we have the historical rows and can see that it was one of their own employees (or family-members...) mucking around. Previously we'd have cheerfly accepted the user's complaint and given them a refund or service-credit because it was too much hassle to dig-up the weeks-old HTTP logs or have anxieties about we having any non-transaction-safe OLTP code running causing corruption that we don't know about :S
- hobs 3y ago
  Schema changes basically causing heterogeneous representations of data. If you only add columns forever and never change types its fine. Oh, and being able to specify the time a thing happened is sometimes useful (though you don't want everyone doing that of course...) Also double checking the docs because its been 5 years since I have reasonably touched SQL Server - no indexes on historical tables so it gets slow fast, Filetable doesn't work* /nvarchar(max) records bloat you hard core. I find that a log record is much better as a non relational/document type record that is isomorphic to the record so I can schema on read if I need to, and if you want to JSON query that stuff it works fine too. * but nobody uses it anymore

donor20 3y ago

RDS is 50 cents or less a day for postgresql on a three year term for a small instance. I use AWS / RDS - I’ve got to believe RDS is spinning money for AWS because of all the parts I want to be reliable this is way up there.

deterministic 3y ago

I am sorry to say this but most developers either don’t have the ability to think for themselves or can’t be bothered. You might disagree with this statement of course but that is truly my experience (30+ years of working professionally in software development). Try this: Next time a developer confidently suggests that you should use tech X, ask them that the pros/cons are compared with using tech Y/Z. Ask about performance/failover/latency/team skills required etc. Most developers in my experience completely fail to give an intelligent answer. They are just loudspeakers confidently copying the opinions of somebody else. Usually somebody who benefits financially from developers using tech X.

arjunlol 3y ago

This is actually what we're building @ Bemi https://bemi.io/ https://bemi.io/ We're hoping to make it so that this becomes automatic with Postgres vs moving to specialized databases. We recently open sourced the core tech that implements system versioned tables https://github.com/BemiHQ/bemi https://github.com/BemiHQ/bemi, check it out if interested :)