I sail the high seas of the Lemmyverse, posting snarky + Lefty comments

  • 0 Posts
  • 26 Comments
Joined 2 years ago
Cake day: May 7th, 2023
  • subversive_dev@lemmy.mltoAnnouncements@lemmy.ml•Lemmy AMA March 2025English
    5·
    3 months ago

    Good evening Dessalines, I have started looking at the posts query.

    The lowest hanging fruit I think would be if we could replace some of the joins with WHERE EXISTS which can have a huge impact on the query time. It seems this is supported in Diesel: https://stackoverflow.com/a/74300447

    This is my first time looking at the codebase so I can’t tell yet which joins are purely for filtering (in which case they can be replaced by WHERE EXISTS) and which joins need to be left in because some of their columns end up in the final SELECT

    I can’t tell for sure yet but it also looks like this might also be using LIMIT...OFFSET pagination? That can be a real drag on performance but isn’t as easy to fix.

    EDIT:

    Looking some more, and reading some linked github discussion - I think to really get this out of the performance pits will require some denormalization like a materialized view or manual cache tables populated by triggers. I really like the ranking algorithm but so far I’m finding it difficult to optimize from a query perspective

  • subversive_dev@lemmy.mltoTechnology@lemmy.world•Wall Street Banks Prepare to Sell Billions of Dollars of X LoansEnglish
    12·
    5 months ago

    Hard to say right now. The article suggests that the banks are just trying to free up cash on their balance sheets. However, this could instead be an indication that they are looking for a “bigger fool” to take the losses. I personally think the bottom won’t fall out of Xitter until the tesla share price crashes. Elon bought some time with credulous fools with the ridiculous cybercab demo, but that can’t last forever. At that point I think the only question is whether SpaceX gets pulled down with all the rest of the musk ventures

  • subversive_dev@lemmy.mltoTechnology@lemmy.world•Databases and their real world use examplesEnglish
    3·
    1 year ago

    ELI5: a database is the “memory” of a program.

    Every piece of data that any software uses almost certainly comes from and goes to multiple databases.

    Once the data is stored, you can execute “queries” to have powerful access to update many records at a time, read particular records based on their relationship to other records, and so much more.

    Your bank balances, your purchase history, your emails, every part of your digital life is almost certainly spread across a constellation of databases.

    Bonus Fediverse content:

    Lemmy itself uses the Postgres database extensively. Posts, users, comments, votes and more are all individually stored in the database.

    Mastodon also uses Postgres. If a post goes up on Lemmy, and a Mastodon server is federated with it, the Lemmy server will send out a HTTP request to the Mastodon server containing the contents of the post. The Mastodon server will use this information to write its own record of the post in its own database.

    Regarding your question about VMs: You can run a database inside a VM, or give the VM access to an outside database via queries, or both! You might run SQLlite (a small and excellent embedded database) on the VM to track its local state, while also running queries against a large postgres database to synchronize with other services in the cluster.