(I'm trying to predict long-term SQL backup storage needs and if/where to enable compression, on a storage appliance with de-duplication. I know the best answer is "test both and see", which we are doing.) I seem to recall that SQL backups (and restores) are performed upon SQL database *extents* (8x 8kb data pages). My question is, *are those extents handled predictably and consistently by the SQL backup process* in a way that the storage de-duplication can rely on? By that I mean, if we do a full backup each night, *will the unchanged extents result in identical bits on disk (for those unchanged portions) from night to night?* Or is that not reliable/predictable? What if we enable SQL backup compression? Will identical extents result in identical compressed bits from night to night? Or is that not guaranteed?

I have setup two test systems for primary/stanbdy replication. On the bigger VM with more CPU power I did default_toast_compression = lz4 and wal_compression=on. When i created tables on that VM I could see the compression method in the pg_column_compression. Now I added a second VM with only 2 vCPUs that plays the role of primary in replication wal_level=replica and also default LZ4 but when i create tables it does not show up as compressed. Not even if I use pglz or lz4. test01=# \d+ sales_record Table "public.sales_record" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ----------------+-------------------+-----------+----------+---------+----------+-------------+--------------+------------- region | character varying | | | | extended | lz4 | | country | character varying | | | | extended | lz4 | | item_type | character varying | | | | extended | lz4 | | sales_channel | character varying | | | | extended | lz4 | | order_priority | character(1) | | | | extended | lz4 | | order_date | date | | | | plain | | | order_id | integer | | | | plain | | | I can see that the table has LZ4 but when I issue SELECT pg_column_compression(country) from public.sales_record; I see only NULL values. test01=# SELECT pg_column_compression(region) from public.sales_record limit 10; pg_column_compression ----------------------- (10 rows) What have i missed here? I just wanted to test out LZ4. Is it because I only used COPY to import CSV file ? I inserted data into sales_record using: COPY sales_record FROM '/var/lib/pgsql/14/import/50000SalesR.csv' CSV HEADER; Additional test when I do simple INSERT on another table I see that it is being compressed. CREATE TABLE tbl (id int, col1 text COMPRESSION pglz, col2 text COMPRESSION lz4, col3 text); INSERT INTO tbl VALUES (1, repeat('abc',1000), repeat('abc',1000),repeat('abc',1000)); Then I double check: test01=# SELECT pg_column_compression(col3) from public.tbl limit 5; pg_column_compression ----------------------- lz4 lz4 lz4 lz4 lz4 (5 rows) test01=# SELECT pg_column_compression(col1) from public.tbl limit 5; pg_column_compression ----------------------- pglz pglz pglz pglz pglz (5 rows) So it seems I need to use some switches if COPY command can use compression during import of CSV data ?

I'm experiencing some issues with Apache CouchDB's database compression (version 3.3.3). Sometimes the process gets stuck midway, which causes significant difficulties. I'd like to know if it's possible to adjust certain settings—such as specifying which databases should be compressed, or setting the level of compression to be applied. I just asked ChatGPT, and it told me that CouchDB uses Snappy compression starting from version 3.x, and that this compression method isn’t configurable. So I decided to ask here in case someone has another idea or workaround. Thanks!

MariaDB offers multiple compression methods : bzip2, lz4, lzma, lzo and snappy. Why are there so many? Which one is recommended to use? Why isn't zstd and option? If one compression method is adopted with e.g. SET GLOBAL innodb_compression_algorithm = lz4; and it turns out some other method would be better, is there any way to migrate to another method in-place or does it require a full logical dump-and-restore cycle?

when I take a diff backup of my database without specifying compression It still does compress. what if I explicitly want without compression?

BACKUP DATABASE MyDatabase TO DISK='\\myserver\SQLBackups$\SQLBackups\Non_Prod\dev_server\mydatabase\DIFF\myserver_myDatabase_DIFF_20250504_060118__.bak'
WITH  FORMAT,INIT, STATS=1,BLOCKSIZE=4096,differential,COPY_ONLY

when I run this query:

SELECT 	
	 A.Backup_finish_date
	,BackupSize_GB              = CAST(COALESCE(ISNULL(a.compressed_backup_size,A.BACKUP_SIZE),0)/1024.00/1024.00/1024.00 AS NUMERIC(18,2)) 
	,BackupSize_MB              = CAST(COALESCE(ISNULL(a.compressed_backup_size,A.BACKUP_SIZE),0)/1024.00/1024.00 AS NUMERIC(18,2))
	,[Backup Duration]          = (CAST(DATEDIFF(s,A.backup_start_date,A.backup_finish_date) AS int))
	,A.type
	,F.physical_device_name
	,A.is_copy_only
	,A.media_set_id
    ,A.backup_start_date
	,A.database_name
	,A.has_backup_checksums
	,F.physical_block_size
	,is_compressed = CASE WHEN ISNULL(a.compressed_backup_size,A.BACKUP_SIZE) = ISNULL(a.compressed_backup_size,-1) THEN 1 ELSE 0 END
            from msdb.dbo.backupset A WITH(NOLOCK)

	INNER JOIN msdb.dbo.backupmediafamily F WITH(NOLOCK)
			ON A.media_set_id = F.media_set_id
    where a.backup_finish_date >= DATEADD(DAY,-30,SYSDATETIME())
	AND database_name = 'MyDatabase'
	and has_backup_checksums = 0

I get it as compressed . is there a way, without changing the settings, to get it non-compressed?

We are currently using Cassandra version 1.2.14 and mirroring across 3 IPs. We have 29TB of storage space, with approximately 24TB of data currently stored. I have a few questions: During internal compaction, the storage space becomes full, causing the compaction to fail and the Cassandra node to go down. What steps can we take to ensure compaction completes successfully in such situations? Where is the compaction frequency configured in Cassandra? Is it possible for us to modify this frequency? When an administrator executes a compaction command, it fails due to file size mismatches. What are the possible solutions for this issue?

I have a mariadb database set up for logging experiment data. In one of the tables, I store huge raw images every row. With a few million rows each containing 3 512*512px images, I run out of disk space. I have 12TB disk and the database has consumed ~10TB. The difference between images is small, so I believe there is a lot of room for compression. I want to consider compression the database, but with limited free space I want a method to compress it in place. I use mariadb 10.3.39 on Ubuntu 20.04. I am not an expert on database, but I am happy to learn more. I tried: ALTER TABLE images ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 ALGORITHM=INPLACE; and I got: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ALGORITHM=INPLACE' at line 1 I also considered maybe I can partition my table into chunks, and comrpress each partition individually. However I am also not sure if partitioning existing table requires more space. I would like to get some recommendation before trying blindly.

I use backup compression everywhere. Recently, I discovered that my backups were getting a little too big. I decided to fix this by compressing the indexes in the database (typically PAGE compression). The database got much smaller. But to my surprise, the full backups didn't get smaller! Is this expected behaviour?

The current setup is comprised of two nodes, a Master-Slave setup. No encryption, no compression. I would love to switch this to a Galera Cluster, with encryption, compression and, to make things spicy, no downtime. I was able to insert the Slave into the cluster, and make the other nodes sync from it. The only issue is that, even though I have set the cluster nodes to use encryption and compression, this is not happening. My money on why this isn't working is that the sync is performed via SST - file are being transferred - while in order to enable encryption and compression, I'd either need to import a mysqldump (downtime, so no go), or somehow force an IST. So this is the question: is there a way to force an IST on a new node (no data)? Or, if anyone got any other ideas on making this happen, I'm willing to try it. Thank you.

An [old Microsoft paper](https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd894051(v=sql.100)?redirectedfrom=MSDN) says to consider using ROW compression by default if you have lots of CPU room (emphasis mine). > If row compression results in space savings and the system can accommodate a 10 percent increase in CPU usage, **all data should be row-compressed** Today, we have even more CPU space than we did back when the paper was written. [Wise men](https://youtu.be/tHfeCstrDAw?t=514) have said that we should consider using PAGE compression everywhere unless we have a compelling reason not to. This is all good advice and I often see sp_estimate_data_compression_savings agree. However, what should be done with tiny tables that are frequently accessed? For example, I have some extremely small dimension tables. 100 rows at most and very few columns. Because they are so small, the space-saving benefit from any compression is minimal. What is considered the best practice for applying ROW or PAGE compression to such tiny tables on boxes that have a huge amount of free CPU room? For the purposes of this question, ignore columnstore. We are only talking about old-school rowstore indexes on disk-based tables.

I created a table with partitions by following this SQL Server 2012 partitioned index document. I created partitions monthly based on Date_Id column; CREATE PARTITION FUNCTION Fnc_Prt_Fact_Sales (INT) AS RANGE RIGHT FOR VALUES ('20200101', '20200201', '20200301','20200401') CREATE PARTITION SCHEME Prt_Scheme_Fact_Sales AS PARTITION Fnc_Prt_Fact_Sales ALL TO ([PRIMARY]) CREATE TABLE [dbo].[Fact_Sales]( [Slip_No] [nvarchar](155) NULL, [Date_Id] [int] NOT NULL, [City_Id] [int] NOT NULL, [Store_Id] [int] NOT NULL, [Sales] FLOAT ) ON Prt_Scheme_Fact_Sales(Date_Id) GO CREATE CLUSTERED INDEX [Ix_Fact_Sales] ON [dbo].[Fact_Sales] ( [Date_Id] ASC, [City_Id] ASC, [Store_Id] ASC ) WITH ( DATA_COMPRESSION = PAGE ON PARTITIONS (1,2,3,4) ) ON Prt_Scheme_Fact_Sales(Date_Id) GO I want to add partitions dynamically (monthly). If I do that, how do I make data compression include newly added partitions? In the blog, partitions are written out manually: --> DATA_COMPRESSION = PAGE ON PARTITIONS (1,2,3,4) If it matters, I use SQL Server 2017 Standard Edition.

GridDB 5.6 has a new compression method that I wanted to test. I made a simple test where I ingested X amount of rows and tested the compression method against the old compression available prior to 5.6 (ZLIB) and against no compression. The results were what you would expect: no compression had the highest data footprint for 100,000,000 rows, ZLIB next, and finally, the new compression method ZSTD had the smallest footprint. I also tested the query speed of these compression methods and to my surprise, the one with the smallest footprint (ZSTD) also had the quickest lookup times. I am curious as to how this could be -- from my understanding, there must be some tradeoff when doing a more advanced method of compressing similar data. I'd at the very least expect that the newest compression method would be on par with ZLIB but with a smaller footprint. And now for the results. As explained above, I inserted 100m rows of 'random' data and timed ingestion, the directory size of the data dir, and lookup times. Here are the results: | | NO_COMPRESSION | COMPRESSION_ZLIB | COMPRESSION_ZSTD | |---------------------|------------------|------------------|-----------------| | Search (ms) | 32644 | 20666 | 11475 | | Agreggation (ms) | 30261 | 13302 | 8402 | | Storage (gridstore) | 11968312 (17GB) | 7162824 (6.9GB) | 6519520 (6.3GB) | | Storage (/data) | 17568708 (12GB) | 1141152 (1.1GB) | 1140384 (1.1GB) | | Insert (m:ss.mmm) | 14:42.452 | 15:02.748 | 15:05.404 | If anybody has any insight into this perplexing compression issue, please share any expertise.

## Observations [The documentation says](https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/enable-or-disable-backup-checksums-during-backup-or-restore-sql-server?view=sql-server-ver16#enable-or-disable-backup-checksum-for-a-backup-operation) > To enable backup checksums in a BACKUP (Transact-SQL) statement, specify the WITH CHECKSUM option. To disable backup checksums, specify the WITH NO_CHECKSUM option. This is the default behavior, except for a compressed backup. In other words, enabling compression enables checksums. However, in my maintenance plans (unfortunately, they're the GUI ones and not Ola's cool ones), I can see: - "Backup Compression (On)" in the main menu - "BackupCompressionOption" set to "On" and "Checksum" set to "False" listed in the properties menu for the Backup Database Task. - 'Verify backup integrity' ticked in the Modify menu, 'Compress backup' selected, and 'Perform checksum' unticked. ## The Question Does this suggest that checksums are disabled even though I've explicitly asked for backup compression? Or are the tickboxes/properties for checksums redundant if compression is enabled? ## What I've Tried * "View T-SQL" crashes SSMS every time that I try. * Finding the T-SQL in Query Store failed, even though I have it enabled on every database (I searched sys.query_store_query_text for like '%backup database%'). * msdb.dbo.backupset has 0 in all of its has_backup_checksums columns, but I don't know if compression renders this column useless.

My problem at the moment, is that I need to index/save snapshots of some data every x minutes, so every x minutes I am inserting around 500k new rows, into a table, which each one represents a snapshot of an account. My general problem is that this creates around 100Gb of new data per day at the moment, what is the best way to compress this, without increasing the time spent on querying. Is TimescaleDB a nice solution, even though isn't a time-series based table?

After doing some experimentation, it appears as though COMPRESSION lz4 doesn't have any effect on JSONB columns (whereas it does for JSON, and TEXT). Is that indeed the case? If so, why is that? I could dive into the Postgresql mailing lists and/or source code to try and find the answer, but hoping someone who already knows the answer can chime in.

I am compressing partitioned tables. before partitioned tables, I tried normal tables with the follwoing steps:

DBMS_REDEFINITION.START_REDEF_TABLE(
    uname => 'USER',
    orig_table => 'ORIGINAL',
    int_table => 'INTERIM'
);




BEGIN
   DBMS_REDEFINITION.CAN_REDEF_TABLE('USER','ORIGINAL', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/



BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE(
      uname => 'USER',
      orig_table => 'ORIGINAL',
      int_table => 'INTERIM',
      options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/



DECLARE
   error_count pls_integer := 0;
BEGIN
   DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('USER', 'ORIGINAL', 'INTERIM', dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
   DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/


DECLARE
   error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname => 'USER',
    orig_table => 'ORIGINAL',
    int_table => 'INTERIM',
    copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
    copy_triggers => TRUE,
    copy_constraints => TRUE,
    copy_privileges => TRUE,
    ignore_errors => FALSE,
    num_errors => 0
);
END;
/

ALTER TABLE ORIGINAL MOVE ROW STORE COMPRESS ADVANCED;

However, for partitioned tables, this is not possible. can someone tell me the procedure to compress partitioned tables in **Oracle 19c**?

I am running PostgreSQL on [compressed ZFS file system](https://bun.uptrace.dev/postgres/tuning-zfs-aws-ebs.html#disabling-toast-compression) . One tip mentioned is to disable PostgreSQL's inline TOAST compression because ZFS can compress data better. This can be done by setting column storage to EXTERNAL. I can do this column by column with:

ALTER TABLE my_table ALTER COLUMN my_column SET STORAGE EXTERNAL;

However, this might be a bit cumbersome, as every schema needs to have migrated to this by hand. Are there easy ways to - Set default STORAGE to EXTERNAL instead of MAIN for all columns - Disable TOAST compression other way I found [default_toast_compression option](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-DEFAULT-TOAST-COMPRESSION) but the documentation is unclear if I can disable it.

Everything that follows is quoted from the datetime row [here](https://learn.microsoft.com/en-us/sql/relational-databases/data-compression/row-compression-implementation?view=sql-server-ver16#how-row-compression-affects-storage) . I have broken the quote to give commentary on my understanding. > Uses the integer data representation by using two 4-byte integers. The integer value represents the number of days with base date of 1900-01-01. In other words, we have eight bytes to work with. This is exactly the same as what datetime already takes up. > The first 2 bytes can represent up to the year 2079. Compression can always save 2 bytes here until that point. In other words, until 2079 we are losing two bytes to the year. After that point, we will lose more. This means that for dates before 2079, we have six of our original eight bytes to work with. > Each integer value represents 3.33 milliseconds. Compression exhausts the first 2 bytes in first five minutes [sic] and needs the fourth byte after 4PM. In other words, after 4 PM we only have two of our remaining six bytes to work with. > Therefore, compression can save only 1 byte after 4PM. **This is clearly the part I misunderstand**. My earlier arithmetic proves that we have two byes left. However, Microsoft says that we only have one. This is doubly confusing, because if we are past 2079 then Microsoft's numbers would put us as having negative one bytes left. > When datetime is compressed like any other integer, compression saves 2 bytes in the date. The user has no control over how the compression works, so this line is entirely irrelevant. From the above, it's evident that I have a great misunderstanding. What have I missed?

[The documentation is very clear on this point](https://learn.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression?view=sql-server-ver16#effect-on-other-sql-server-components) > Compression doesn't affect backup and restore. but how is that possible? Surely, if my table is smaller (due to compression), then that should have some impact on backups/restores? If my table is smaller, then it should load faster. If my table needs some decompressing, then that should make it takes longer. Either way, I just can't believe that data compression **does absolutely nothing** to backups and restores. What am I missing?

I have a table that I believe owes most of its size to a huge NVARCHAR(max) column. I do not know how to test where most of its size comes from. Regardless, would such a table benefit from row compression? Or is NVARCHAR(max) already variable-length in such a way that row compression is guaranteed to do nothing?