Questions tagged [compression]

The name given to the process of encoding data such that it uses lesser number of bits as compared to the original representation.

Filter by
Sorted by
Tagged with
4
votes
1answer
79 views

Huge Heap Table and table compression on SQL Server 2016

My database size is huge, and recently I noticed that a new table added few months ago is the culprit. Here is the table script. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo]....
0
votes
2answers
32 views

Is it possible to compress CLOB columns in DB2 10.5

I have a table with a CLOB(1M). If I enable compression (compression yes adaptive) on the the table the CLOB is not compressed. Is there a way to make this work in DB2 10.5?
1
vote
1answer
38 views

Is compression used in RDS SQLServer database

I have few Clarification regarding AWS RDS SQL Server. Do we have any compression technique to compress the data loaded into RDS SQL server? Is there any option or parameter to compress the data and ...
0
votes
1answer
67 views

MySQL: Change compression on an existing InnoDB table

Is there any way to turn on compression for an existing table? I don't care if there's downtime for this. The docs only mention setting compression when the table is created.
12
votes
1answer
1k views

Do SQL Server compressed indexes remain compressed on rebuild without specifying data compression?

After one rebuilds their SQL Server indexes using page compression (ALTER INDEX IX1 REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)), do subsequent rebuilds (as done by some maintenance scripts ...
0
votes
1answer
44 views

How to analyze compression options for Azure SQL Database?

What is the most efficient way to determine whether row or page or columnstore compression (or none) would be most effective for each index in an Azure SQL Database (standard or premium tier) used for ...
2
votes
1answer
214 views

sparse column or DATA_COMPRESSION?

in one of my databases I have the following table: CREATE TABLE [app].[applicantSkill]( [ApplicantSkillID] [int] IDENTITY(1,1) NOT NULL, [applicantID] [int] NOT NULL, [skillID] [tinyint] ...
1
vote
0answers
73 views

Testing Data Compression

We have a 1TB table that gets queried a lot. I have suggested we use Data Compression to save on space as well as help put more in memory. Because of the concern of CPU overhead, I have been tasked ...
3
votes
1answer
411 views

GZip existing varbinary(max) column in Sql Server 2008+

I have an existing legacy table that is ~180GB in size due to a client application storing PDF files as varbinary. I'd like to be able to compress that column for all rows using GZIP to help save ...
0
votes
1answer
27 views

After page compressing a huge table, the disk space didn't change. Do I need to shrink in any kind?

MSSQL: There is a daily partitioned table which was 2 GB and we needed to compress this table by page compression. After compression, the table size got smaller if I check from the table properties - ...
2
votes
0answers
70 views

Enable compression for SQL Server Managed Backup to Microsoft Azure

I am using SQL Server Managed Backup to Microsoft Azure for multiple databases on my SQL Server 2017. I am trying to figure out how to enable compression on some of the databases. It seems from this ...
0
votes
1answer
579 views

Store large compressed documents in Postgresql

Where I work we keep approximately 500,000 image files related to processing for our customer in a file system attached to our Redhat 5.11 production server. We are planning an upgrade to Redhat 7.4 ...
1
vote
1answer
117 views

Is there any string compaction option for PostgreSQL?

I have a table like this: CREATE TABLE BLOG( CATEGORY CHAR(32) ); Category is a column, which stored enum values. For example, I have 1 millions of rows and 10 unique values of Category. Let'...
2
votes
2answers
98 views

Cancelling the rebuilding of a partition with compression

Trying to enable compression on a large table in production but we are now out of time (been running for 6.5 hours already). What is the impact of cancelling the operation and letting it roll back? ...
2
votes
1answer
612 views

Are Postgres btree index keys compressed?

v8.4 and v9.2 (Yes, I know they're archaic and out of support, but there's nothing I can do about it.) Some of the tables in the database which I manage (but don't design) have btree indexes on ...
2
votes
1answer
999 views

Do I gain read performance improvement by using zlib rather than snappy compression in MongoDB?

My current storage engine is WiredTiger and its compression level is as default, snappy. I've come across MongoDB documentation and it's been mentioned that using zlib compress better but needs more ...
9
votes
1answer
6k views

Compress PostgreSQL Database

I have a large PostgreSQL database over 500GB in size which is too large. Is there anyway to compress the database down to a more manageable size? I have attempted to do this with SquashFS and the ...
0
votes
1answer
236 views

Using Ola Hallengren’s Maintenance in SQL Server 2016 with TDE Enabled databases and compression

I’m using Ola Hallengren’s maintenance and backup solution and looking into leveraging TDE compression as I have all the databases in instance using TDE impression. Is there a way of getting benefit ...
1
vote
1answer
556 views

How to iterate through table and modify entrys in an “if” statement with MySQL

What I have is a table containing some temperature readings which often jump back and forth. +------------+----------+-------------+ | timestamp | sensorID | temperature | +------------+----------+--...
3
votes
0answers
175 views

When would you use InnoDB PAGE compression over InnoDB TABLE compression?

From what I understand, InnoDB table level compression is "transparent" and appears to perform well on the surface. In what circumstances would it be beneficial to choose InnoDB page level ...
2
votes
1answer
3k views

Compression not available on SQL Server Standard? Options?

So as they say, everyday is a school day. Today I learned that my workplace, runs SQL Server Standard edition, where I would have assumed Enterprise was in place. Although in reality shouldn't be ...
12
votes
2answers
768 views

Find uncompressed size of all tables in a database

In Dynamics AX there is a caching mechanism where tables can be configured to be loaded into memory and cached. This cache is limited to a certain amount of KB to prevent memory issues. The setting I'...
5
votes
2answers
2k views

SQL Server 2012 compress column for whole table

I was wondering if SQL Server supports a kind of dictionary compression for a whole table, and not just a page. The system I'm working on wasn't initially created for the large amounts of data it ...
2
votes
1answer
613 views

Compression setting not effective for differential backup maintenance plan?

We are taking backups (Full, Differential, and Transaction Log) of databases using the maintenance plans feature in SQL Server. While taking the backup, I have chosen the compress option to compress ...
1
vote
1answer
2k views

Compression function in Postgres

Using Java & JDBC I am storing Strings into a JSONB column in Postgres. When inserting this works great. When retrieving values, I am running low on memory in Java because I am retrieving ...
1
vote
0answers
312 views

how do I set the compression level in tokudb

Tokudb has various algorithms, lmza, zlib, zstd etc. These algorithms have the ability to be configured. In that, you can set the compression level to a value between 1 and 10 depending on the ...
2
votes
1answer
73 views

Spatial Index Compression

I am looking to turn page compression on on a spatial index. The instance is 2008 Enterprise. The index is pre-existing on a vendor supplied app. In searching through BOL, it states that compression ...
1
vote
0answers
38 views

Wasted space for compressed SecureFile LOBs?

When using Oracle BasicFile and SecureFile one property of the implementation is that each LOB occupies multiples of the chunk size (and in case of SecureFile the smallest chunk size is the block size)...
5
votes
3answers
1k views

Enabling page compression on 1.5TB table

I need to come up with the best approach possible to enable page compression on a table 1500 GB in size, with 363,957,740 rows. The database size itself is 1.71 TB and holds archive data. If I ...
8
votes
2answers
732 views

SQL Server 2014 compression and maximum row size

I need to create wide denormalized table with lot of decimal(26,8) columns (fewer than 1024 column limit, most of the columns would be null or zero). I know about 8060 bytes per row limitation, so i ...
2
votes
1answer
1k views

MS SQL Server 2016 - Single Partitioned Table vs Multiply Separated Tables

We need to store a lot of logs in MS SQL Server 2016 Database for 1-1.5 year. Income: 50 Gb of logs per month (~ 50 millions rows per month), we store all data in 6 months and shrink 50% of this data ...
7
votes
2answers
1k views

PostgreSQL network compression for result set?

I was reading about network compression with PostgreSQL to see if there was anything that offered that which would permit big result sets to transfer over the wire to remote web servers. It seems ...
9
votes
1answer
1k views

What is the row overhead when using Page Compression?

I have created a table with 650 Numeric(19,4) columns. When I switch on Page Compression, by running ALTER TABLE fct.MyTable REBUILD WITH (DATA_COMPRESSION = PAGE); I get Msg 1975, Level 16, ...
1
vote
1answer
117 views

Compress SQL Server database

Is there a way to create a SQL Server 2014 database so that all data tables will be compressed automatically? Is there a way to compress all the existing tables of a SQL Server 2014 database? ...
1
vote
1answer
248 views

change journalCompressor to none - not default snappy

Quote from ObjectRocket Support Hi, Fyodor! The journalCompressor setting is specifically related to the journal files. Journals are typically not compressed because compressing them causes ...
4
votes
1answer
6k views

Assembly is not authorized for PERMISSION_SET=UNSAFE when creating a CLR assembly

The login running all these commands is a member of sysadmin server role on a SQL 2012 Developer instance. It is the owner of the DB where this is being deployed to. EXTERNAL ACCESS ASSEMBLY ...
3
votes
1answer
1k views

Attempting to compress an existing table results in no change to the table size

There is an existing table in SQL 2012 Enterprise DB, which is ~120MB in size with ~2000 records. Its largest field is nvarchar(max) containing spongy XML (ranging from 20 to 300 kB) which on its own ...
0
votes
1answer
339 views

Does dbms_lob.getlength return the compressed or uncompressed length?

If you have tablespace compression turned on, does dbms_lob.getlength return the compressed or uncompressed length of lobs stored on that tablespace? What about LOB compression?
1
vote
1answer
88 views

Database Backup compression

I am trying to see all of my backups are compressed or not. I ran below query to check but not getting good result: select top(10) database_name, case when backup_size = compressed_backup_size then ...
4
votes
1answer
403 views

Backup Failure on SQL TDE Database with Maxtransfersize Specified for Compression

Recently, my company has upgraded to 2016 and enabled TDE on all of our databases. Additionally, we have the production databases replicated on high availability servers. Our backups are taken from ...
1
vote
1answer
273 views

What Backup Compression Rate Can I Expect for a Database with Mostly JPG Data?

I am considering using a database to store one or more images (.JPG) associated with a record identifier and filename. CREATE TABLE Images ( RecordID BIGINT NOT NULL, ImageFilename VARCHAR(50)...
0
votes
1answer
13 views

Impact of table compression on HA Synchronous-commit mode scenarios

I am working with a HA SQL 2014 instance with Synchronous-commit mode. I did some table compression analysis with sp_estimate_data_compression_savings and I learned that table compression can be very ...
2
votes
3answers
1k views

Use of table compression in SQL Server 2014

I am going through articles about table compression in SQL Server. To me it seems quite easy to setup compressed tables and the benefits make the decision no brainer. I wanted to consult with this ...
1
vote
0answers
112 views

MariaDB replication - enabling compression

We've got a MariaDB database that's already being replicated and we wanted to turn on compression. I found the slave_compressed_protocol variable and see that it has to be turned on both on the ...
1
vote
4answers
250 views

External compression tools for database backup files

Can I use external compression tools like WinZip, 7zip or winrar to compress rman backup files? The DBMS is Oracle. A senior DBA claims that backups could be damaged by external compression tools. ...
2
votes
1answer
5k views

I wish to restore a gzipped tarball - what does mongodump --archive do? Howto do a mongorestore with --gzip?

I created a backup with mongodump -h $RANDOM_SECONDARY_SUP -u $BACKUP_USER -p $BACKUP_PASSWD --out /data/$BACKUP_USER/sup-repl/sup_$DUMPFILE tar cvzf /data/$BACKUP_USER/dbt/st_$DUMPFILE.tar.gz /data/...
1
vote
1answer
314 views

SQL Server Compression Estimate Is Significantly Lower When Estimating Current Compression Type

UPDATE - I originally said 'estimate significantly lower when set to 'NONE'. I'm amending that to say 'estimate significantly lower when set to [the compression in use]. When I execute the ...
12
votes
2answers
728 views

Compression on a heap

The follow is a paragraph from BOL: New pages allocated in a heap as part of DML operations will not use PAGE compression until the heap is rebuilt. Rebuild the heap by removing and reapplying ...
1
vote
1answer
122 views

How to remove Windows NTFS Compression from Oracle Database datafiles?

We know Windows NTFS compression on Oracle Database datafiles is a bad idea. See here and here. It prevents Asynchronous Random Disk I/O which can increase the risk of corruption and generally slow ...
0
votes
1answer
386 views

MySQL compression on Google Cloud SQL

As the Google Cloud SQL is a managed instance of MySQL under the hood, limited to an absolute max on 2nd generation machines of 100GB of RAM, should I be compressing some of my table and index data ? ...