My idea for for this to add metadata to the transfers, likely with a jsonb column. But I agree that the ledger won't encompass everything. You would still have other database tables and then you'd store domain identifiers in the ledger and/or ledger identifiers in your other tables.
> how do we efficiently get the balance of a certain account at a certain date time?
This depends on how you implement the ledger. For pgledger, I store the balance in every entry, so to find a historical balance, you just need to find the most recent entry before that time:
I think that's a fair point. Unnesting arrays in SQL can be annoying. Here is your first example with duckdb:
duckdb -c \
"select * from ( \
select unnest(assets)->>'browser_download_url' as url \
from read_json('https://api.github.com/repos/go-gitea/gitea/releases/latest') \
) \
where url like '%linux-amd64'"
> And it is bizarre to me that you could run a payments system without high-availability or immutability at its core.
I’m not sure what you’re referring to here as my post didn’t really go into these topics.
> Also putting logs in a database alongside your core data is such a bad idea.
I’m not suggesting putting all logs into the database. I’m suggesting that for a certain type of info (that is often logged), I find it more useful to put it in the database instead.
We have a few different types of auditing information. At that point in the timeline, it was all in PostgreSQL. Today, we keep only the critical auditing information in the main database.
For the first few years, we scaled vertically rather than horizontally. We still went through many rounds of scaling our single server, changing both hardware and software configs.
We were reluctant to do automatic failover as well, since we all hear horror stories about automatic failover causing more problems than it solves. However, we changed our minds this summer for a couple of reasons. One, our traffic continues to grow and people rely on us for payments. If our database server crashes in the middle of the night, we don't want our customers to have to wait for us to do manual failover. Two, we felt a lot more comfortable with Pacemaker and PostgreSQL streaming replication failover than we used to with DRBD. We did extensive testing and tweaking, and we believe we understand the failure scenarios.
One of the best metrics is the log of slow queries. We set our threshold at 250 milliseconds, and we investigate queries that take longer. Sometimes, we'll see INSERT or COMMIT statements show up, which is usually an indication that our write performance isn't what it needs to be. We generate graphs from these logs (using graphite) and track them over time. We also use Munin (including the PostgreSQL plugins) to monitor disk IO and various PostgreSQL stats.
To be safe, we always rebuild the old master after we fail over to a synchronous standby server. We have capistrano tasks to automate it, so it's not too bad. The async servers merely follow the new server after the IP moves, so we don't need to do anything there.
Edit: We also don't increment the timeline on failover. Instead, we stop PostgreSQL, remove the recovery.conf, and restart.
The nice part of sending the data to your servers with client side encryption is that you can do validation before sending to the payment gateway. For example, if you want to ensure everyone enters a cardholder name, you can validate the non-encrypted fields before eating the cost of calling a payment gateway.
You can do some of these validations in javascript, but javascript is error-prone (firebug) and not as flexible (have access to a lot more data server side).
We do use the sandbox and production git branches for those environments. If you want to puppet sandbox, you start with a git checkout sandbox. If you try to puppet sandbox from the production or master branch, you get a big warning and a prompt asking if you are really sure.