THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
Page History
...
Data is stored in-memory in compressed form. Data is accomodated in data block blocks in raw form. When certain threshold is reached data is compressed and more records could be added to it. Original row structure may be maintained to certain extent, so that subsequent reads do not need to uncompress data.
Advantages:
- Less IO reads as more data fits memory
- Uncompression may be avoided on reads in some cases
Disadvantages:
- Lower compression rates
- More complex algorithms
Examples:
- Oracle Advanced Compression [1], [2]
- MS SQL Page compression [3]
[1] https://blogs.oracle.com/dbstorage/updates-in-row-compressed-tables
[2] https://blogs.oracle.com/dbstorage/advanced-row-compression-improvements-with-oracle-database-12c-release-2
[3] https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/page-compression-implementation?view=sql-server-2017
Draft materials
Compression options:
...
7) Per-column compression
8) Row compression
9) WAL compression
10) New algorithms (LSM, BWTree, ...)
...
------------------------------
SQL Server1) Row compression - metadata, varlen for numeric types, trimming for CHAR types https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/row-compression-implementation?view=sql-server-2017
1.1) NULL and 0 take no bytes!
2) Page compression https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/page-compression-implementation?view=sql-server-2017
3) COMPRESS command https://docs.microsoft.com/en-us/sql/t-sql/functions/compress-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/enable-compression-on-a-table-or-index?view=sql-server-2017------------------------------
Oracle
1) Basic compression - compression during bulk loads only (direct load, CREATE TABLE AS SELECT)
2) Advanced Row Compression (ex. OLTP Table Compression) - used for DML, keep data compressed in-memory; more CPU but less IO - reads gets gain anyway
3) Advanced LOB compression and deduplication - appears to be something similar to PG TOAST (?)
4) Index compression - just prefix compression https://blogs.oracle.com/dbstorage/compressing-your-indexes:-index-key-compression-part-1
?? 5) Advanced Index Compression
?? 6) Hybrid columnar compression (query level, archive level) - tremendous compression rates (up to 50x), 5-15x typical, "query" - improves scan performance, "archive" - for data archives
7) Compression at tablespace and table levels
8) Tablespace encryption - after compression, column encryption - before compression, no effect
9) Indexes are compressed separately from data!
10) Clustered tables - only prefix compression is applicable
11) Heat Map - insight on how data is accessed
12) Advanced Row Compression - can read specific attributes without full decompression
------------------------------
MongoDB
3) WAL compression https://docs.oraclemongodb.com/databasemanual/121core/ADMINwiredtiger/tables.htm#ADMIN#storage-GUID-34D15DD1-0925-4C9A-BE8A-3EE91671E526wiredtiger-journal
Best practices
"With Advanced Row Compression, when the block is full, it is compressed. More rows are then added (since more rows can now fit into the block), and the process of recompression is repeated several times until the rows in the block cannot be compressed further. Blocks are usually compressed and reformatted in their entirety, but, starting with Oracle Database 12c Release 2, in some cases the block can be partially compressed, hence resulting in CPU savings and extra compression."
------------------------------
MongoDB
3) WAL compression https://docs.mongodbmicrosoft.com/manual/core/wiredtiger/#storage-wiredtiger-journalen-us/sql/relational-databases/data-compression/data-compression?view=sql-server-2017
Granularity
1) Oracle: Compression at tablespace and table levels
4) MongoDB: 4) Configurable per-collection and per-index https://www.mongodb.com/blog/post/new-compression-options-mongodb-30
...
Overview
Content Tools
Apps