HN Explorer

6 comments

[ 2.8 ms ] story [ 27.5 ms ] thread
apavlo
From the documentation:

> Yet SQL does not provide a way of indexing queries that involve a JOIN. Social apps that want to query "what are all the posts of all the people I follow ordered in time" must design their own systems because SQL cannot index that query (SQL will need to load all of the posts of all the people you follow over all time, and sort them in memory!)

rough query but you get the idea

  CREATE MATERIALIZED VIEW follower_posts
    SELECT f.follower_id, p.post_id, p.author_id, p.created
      FROM followers AS f JOIN posts AS p
        ON f.followee_id = p.author_id;
  CREATE INDEX ON follower_posts (follower_id, created DESC);
Please read a textbook about databases before building your own.
amirouche
Read the whole textbook, MATERIALIZED VIEW requires a REFRESH that is outside the transaction that made the change: https://www.postgresql.org/docs/current/rules-materializedvi...
apavlo
Postgres is not every system. Just because it requires manually refreshing matviews doesn't mean that every DBMS works that way.

Seriously, read a textbook:

http://db.cs.duke.edu/papers/fntdb12-ChirkovaYang-mat_views....

amirouche
Indeed, I jumped into PostgreSQL documentation, that's where I started reading about materialized views.
amirouche
I do not think that PDF is worth taking space in my personal knowledge base. Thanks for sharing.
amirouche
I am saddened they picked idioms from the database paradigm I invented, fixed the bugs I reported directly to ccorcos upon his request about this very project, and did not even subtly mention my work.

Hiding https://okvs.dev is an antipattern.

The user interface is inspiring.

See also https://ccorcos.github.io/filing-cabinets/

I killed my notion.so account.