5 ms·

> this is true for almost every database. ...that uses fixed sized pages.

by hashmash 4y ago

smegsicle 4y ago
so true for almost every sql database? or not?
- hashmash 4y ago
  MyRocks is MySQL backed by an LSM tree, and so it doesn't have fixed sized pages.
  - smegsicle 4y ago
    so a mysql storage engine that uses the rocksdb key/value store? sounds like could be one of those rule-proving exceptions, even if it is a big facebook project
- didgetmaster 4y ago
  I think it is true for every row-oriented database that stores all the values for a single row together within a fixed-sized structure. Columnar stores on the other hand may or may not do this. I have built a database engine that is a columnar store. All the values for each column are stored together. This requires just about every query to fetch each row value separately, but it has proven to be incredibly fast. Queries on big tables run several times faster than on Postgres and it is also about 5x faster than on SQLite. https://www.youtube.com/watch?v=Va5ZqfwQXWI https://www.youtube.com/watch?v=Va5ZqfwQXWI
  - tomnipotent 4y ago
    Most columnar stores I'm aware of are hybrid, so all columns of a row are still colocated in the same page.
    - didgetmaster 4y ago
      Mine is not. If a table has 4 columns (e.g. name, address, phone, email), then all the names are stored separately from the addresses. Likewise all the phone numbers are stored separately from the emails. The data is de-duped so it is incredibly easy to find out how many of each value is in each column (e.g. there are 1,234,567 rows in the table where name = 'John').
      - tomnipotent 4y ago
        The downside is that projecting a row requires random I/O across a larger number of pages, which also means more evictions from the in-memory buffer and worse cache efficiency. Apache Arrow, Parquet, Redshift, Bigtable/Spanner, Snowflake are all hybrid columnar, for example. You get good row data locality while still being able to exploit SIMD/vectorized ops.

> this is true for almost every database. ...that uses fixed sized pages.

smegsicle 4y ago

so true for almost every sql database? or not?

hashmash 4y ago
MyRocks is MySQL backed by an LSM tree, and so it doesn't have fixed sized pages.
- smegsicle 4y ago
  so a mysql storage engine that uses the rocksdb key/value store? sounds like could be one of those rule-proving exceptions, even if it is a big facebook project
didgetmaster 4y ago
I think it is true for every row-oriented database that stores all the values for a single row together within a fixed-sized structure. Columnar stores on the other hand may or may not do this. I have built a database engine that is a columnar store. All the values for each column are stored together. This requires just about every query to fetch each row value separately, but it has proven to be incredibly fast. Queries on big tables run several times faster than on Postgres and it is also about 5x faster than on SQLite. https://www.youtube.com/watch?v=Va5ZqfwQXWI https://www.youtube.com/watch?v=Va5ZqfwQXWI
- tomnipotent 4y ago
  Most columnar stores I'm aware of are hybrid, so all columns of a row are still colocated in the same page.
  - didgetmaster 4y ago
    Mine is not. If a table has 4 columns (e.g. name, address, phone, email), then all the names are stored separately from the addresses. Likewise all the phone numbers are stored separately from the emails. The data is de-duped so it is incredibly easy to find out how many of each value is in each column (e.g. there are 1,234,567 rows in the table where name = 'John').
    - tomnipotent 4y ago
      The downside is that projecting a row requires random I/O across a larger number of pages, which also means more evictions from the in-memory buffer and worse cache efficiency. Apache Arrow, Parquet, Redshift, Bigtable/Spanner, Snowflake are all hybrid columnar, for example. You get good row data locality while still being able to exploit SIMD/vectorized ops.