4 ms·
When studying the internals of Postgres years ago, I learned that pages could greatly affect the disk space required to hold a table. If the page size was 8192
by didgetmaster 4y ago
When studying the internals of Postgres years ago, I learned that pages could greatly affect the disk space required to hold a table. If the page size was 8192 bytes (the default) and if the schema defined each row as holding 4097 bytes, then two rows would not fit within a single page. This would cause every row to be within its own page and would waste almost half of the space.
Anyone know if this is still true?
- j16sdiz 4y agoAfaik, this is still true in most cases.* this is true for almost every database. * Except, maybe, toast, hot or compressed..
- hashmash 4y ago> this is true for almost every database. ...that uses fixed sized pages.
- smegsicle 4y agoso true for almost every sql database? or not?
- hashmash 4y agoMyRocks is MySQL backed by an LSM tree, and so it doesn't have fixed sized pages.
- smegsicle 4y agoso 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 agoI 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 agoMost columnar stores I'm aware of are hybrid, so all columns of a row are still colocated in the same page.
- didgetmaster 4y agoMine 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 agoThe 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.
- jeff-davis 4y agoWhat you are describing is called "internal fragmentation" and it's always a problem at some level in any system. There are tons of ways to mitigate the problem including variable-length data, out-of-line storage, and compression. Postgres does those things, but I suppose there's always room to improve. Best to just see how much storage a given table uses, and see if it's a problem.
- Helmut10001 4y agoOnly slightly related, but the biggest increase in space I saw was after transitioning my 1.8 TB Postgres database to ZFS, which has compression turned on by default. Afterwards, the size needed was 310 GB, with no noticeable loss in speed.
- TheNewsIsHere 4y agoThat is insanely impressive. Could you share more about the characteristics and nature of that workload? How did it perform above typical loads?
- Helmut10001 4y agoYes. This is likely not the typical workload. Our PG database is only used in research, in "burst" situations - e.g. big batch jobs written to the DB (2 days 400 Million tuples) and read in big chunks (e.g. 400 Million tuples exported in 2 hours to CSV/Postgres FDW). ZFS is on spinning Rust (Sata 6GB), 6x8TB drives in a Raidz2 pool, the ZFS dataset is both compressed and encrypted. In Proxmox, I do not see I/O in any way limited during these burst writes/reads, the bottleneck is the CPU. However, the CPU was the bottleneck also before ZFS, so I cannot say how much impact the compression/encryption has. Other ZFS parameters are default (eg. filesystem recordsize is 128KB - lower values will yield better read speed, but less compression, and we were aiming for a lot of compression). Likely no impact, but we use Postgres in Docker on unprivileged LXC, the `/data` directory is mounted from the LXC from the host ZFS pool. Since LXC runs all processes on the host, the performance impact is negligible (unlike, e.g. running this in a full VM). I have the _feeling_ (not actually tested) that the Postgres database is faster with ZFS, since less data needs to be read, especially since we have a lot of sequential scans.
- Helmut10001 4y agoJust looked it up [1] > LZ4 is lossless compression algorithm, providing compression speed > 500 MB/s per core (>0.15 Bytes/cycle). It features an extremely fast decoder, with speed in multiple GB/s per core (~1 Byte/cycle). [1]: https://lz4.github.io/lz4/ https://lz4.github.io/lz4/
- to11mtm 4y agoYeah it's probably still true. It's also true of other DBs; There is usually a certain amount of overhead per-row on the page, or per-row. So yeah, you have to either shrink to a size acceptable within padding, or pick a more appropriate page size, or play with some TOAST settings in the case of PG.