Where is data compression more effective — in columnar or row storage, and why?

Data compression generally works better in columnar storage than in row-based storage. The reason is that in columnar databases, data for a single column is stored sequentially, and the values often have similar characteristics (for example, the same type, close in value, or repeated data). This allows compression algorithms to effectively utilize repetitions and patterns, achieving a high level of compression.

In row-based storage, data from different columns is mixed, which reduces compression efficiency because values of different types and with different statistics appear consecutively.

Example: if a "Country" column has many repeated values like "Russia," "USA," etc., compression will be very effective, whereas in row format, these values are scattered across different rows and mixed with other data.