Amazing engineering prowess.
So they are Postgres and then they have a table that is just a key-value store for all the binary data and then duplicate everything in order tables for indexing? What are "blocks" anyway?
I'm glad that you asked: https://gitlab.com/soapbox-pub/eclipse/-/snippets/4782163
The most important one is this:
Table "public.bsky_blocks"
Column | Type | Collation | Nullable | Default
--------+---------------+-----------+----------+---------
cid | character(59) | | not null |
bytes | bytea | | not null |
They store everything as CBOR-encoded binary values, content-addressable by their IPFS (actually IPLD) CID.
However, they still want to be able to _query_ this data...
So they end up storing everything in bytes AND in plaintext in the database.
Not only that, they also store binary _diffs_ of every single user action, making everything take up 2x more space.
Discussion
you can laugh. i had to write code to work with that tire fire
i had the option to otherwise work with weferium fartcaster... i shoved that on my junior colleague who is ignorant and young enough to not understand why i would not want to touch that shit... or the fucking javascript bullshit to prove our aggregator made things appear on their shitcoin database