41–50 of 81 posts
Am I the only one who is shocked to discover that payments (even very small sums) are stored for years on end, maybe indefinite?? Simple deleting a row that is 366 days old is not an option to keep the PostgreSQL DB relatively small?
Thats not shocking. Its probably due to regulatory laws that transactions are saved that long.
Earlier quoted context omitted.
You can buy boxes with 30x more RAM without going super boutique. You can buy quad-socket server motherboards, so you can have 64x4 cores. For the vast majority of businesses, vertical scaling is quite feasible.
128x4 (EPYC 7000 series go up to 128) assuming you can get a 4 socket board.
http://rhaas.blogspot.com/2012/04/did-i-say-32-cores-how-abo...
They're halting database traffic for 15-30 minutes. I'm confused how this won't mean they have downtime? The application can queue transactions but how is the application handling reads after those queued trsnactions and what if 2 queued transactions conflict?
And how can you at a bank run a transaction if you don't know if there is money or not in the account ???
Am I the only one who is shocked to discover that payments (even very small sums) are stored for years on end, maybe indefinite?? Simple deleting a row that is 366 days old is not an option to keep the PostgreSQL DB relatively small?
It’s a regulatory requirement keep it for 5-7 years e.g for disputes etc
To add on that, for some types of transaction the regulatory requirements are much, much longer - I was working in a jurisdiction where data of housing loan repayments had to be stored, by law, for 70 years after the end of the loan; so for a 30-year mortgage you'd have to be prepared to store every repayment for 100 years; and information on salaries calculated and paid has to be stored for 75 years (IMHO to resolve retirement-related disputes where it matters where you worked decades ago), passing on to national archive if the company is dissolved.
Based on their numbers, they should be archiving their historical data in parquet format partitioned by YYYYMMDD onto something like Clickhouse. This way, they can run a lean Postgres instance(s) at all times yet still get benefits of real-time reporting. Based on their use case, they can retain up-to 30 days of data in Postgres and offload the rest onto Clickhouse.
You assume too much. For example, you assume that analytics aren't already run on a separate data warehouse. Ease of accessing older data is an important aspect of database holding transaction (not meaning transactional db). Wouldn't you want to check your transactions on bank page that are older than 30 days?
Based on my personal experience of achieving 94% compression on 2TB of data using snappy parquet file format, they could be looking at a final dataset size of 3.5TB on Clickhouse.
Earlier quoted context omitted.
You assume too much. For example, you assume that analytics aren't already run on a separate data warehouse. Ease of accessing older data is an important aspect of database holding transaction (not meaning transactional db). Wouldn't you want to check your transactions on bank page that are older than 30 days?
All modern columnnar/analytical databases have no problem in pulling back older data; in fact, that's the point of running large scale analytics.
Unless you want to use columnar database to retrieve 15 transaction that particular user did month ago...
Earlier quoted context omitted.
You assume too much. For example, you assume that analytics aren't already run on a separate data warehouse. Ease of accessing older data is an important aspect of database holding transaction (not meaning transactional db). Wouldn't you want to check your transactions on bank page that are older than 30 days?
Your comment clearly illustrates that you have no working knowledge of Clickhouse or parquet file format or data archiving capabilities available in 2021. It's OK! I was in the same boat until I needed to implement such a solution for my use case. What I'm suggesting does not limit their customers from searching any historical data. Matter of fact, it might be 100x to 1000x faster for them to do so with the suggested…
That must be trolling. 1000x faster than single digit millisecond indexed query retrieving 15 rows?
The fact that you keep talking about storage size means that you're talking about analytics not transactional needs.
>Your comment clearly illustrates that you have no working knowledge of Clickhouse or parquet file format or data archiving capabilities available in 2021. It's OK! I was in the same boat until I needed to implement such a solution for my use case.
Also, fuck off with that condescension.
Earlier quoted context omitted.
All modern columnnar/analytical databases have no problem in pulling back older data; in fact, that's the point of running large scale analytics.
I agree, but analytics is orthogonal topic. Unless you want to use columnar database to retrieve 15 transaction that particular user did month ago...
They're halting database traffic for 15-30 minutes. I'm confused how this won't mean they have downtime? The application can queue transactions but how is the application handling reads after those queued trsnactions and what if 2 queued transactions conflict?
This indeed means downtime on database level. But their system design allows processing transactions with DB down for some time. I guess they are caching account balances and limits in some in-memory DB. This is a common design choice for payment processing systems. Sadly, PostgreSQL doesn't have a built-in solution for online upgrade yet. This is one of the few points why commercial DBMSes are often worth their mone…