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
0
votes
4answers
49 views

Tuning innoDB for a write-intensive machine

I have MariaDB 10.5 on my desktop with multiple disks (SSD and HDD) for write-intensive projects. Writing to a single table is fast and the percentage of dirty pages remains close to zero with 1000-...
0
votes
0answers
44 views

Working with index-key sizes which are close to the DBMS's index-key size limit

I might need to work with relatively large (non-clustered) index keys (between 1KB to 4KB). I found that Spanner and DB2 support 8KB index-key size, MSSQL allows 1.7KB and Postgress around 2.6KB (1/3 ...
1
vote
1answer
34 views

Does SQL Server 2019 automatically un-compress data when it is queried by an application? [duplicate]

We are using SQL Server Express 2019 and have inevitably ran into the 15GB cap. I now have to figure out a way to quickly compress data since we cannot buy a license at the moment. At the moment it is ...
0
votes
0answers
34 views

How to backup tarantool remotely (over SSH) with compression?

In PostgreSQL usually I do this: # Backup (remotely via ssh, to local disk) ssh -p 22 user@dbserver "sudo -u postgres pg_dump -Fc -c dbname | xz - -c" \ | pv -r -b > tmp/db_backup_`date +...
0
votes
1answer
37 views

Space estimates for each table if compression is removed

I am trying to gather space estimates if compression is removed from tables in a database. I started with a temp table and generated statements to execute sp_estimate_data_compression_savings as ...
0
votes
1answer
56 views

How to backup clickhouse over SSH?

In postgreSQL, I usually run this command to backup and compress (since my country have really low bandwidth) from server to local: mkdir -p tmp/backup ssh sshuser@dbserver -p 22 "cd /tmp; ...
0
votes
0answers
44 views

Database Page Compression and ColumnStore Archive - is data compressed when in buffer pool and when sent to client? [duplicate]

One of benefits of Page Compression or ColumnStore Archive is reduced storage space requirements Here I have two questions: When compressed data is read from disk to buffer pool, in the buffer pool ...
3
votes
2answers
233 views

Test to see when table is candidate for compression (row or page)

Does anyone know a tool like this page https://columnscore.com/ where one can determine if a table is a good candidate for row or page compression. Also I am trying to understand the benefits of ...
1
vote
1answer
79 views

slow disk with InnoDB page compression

I have a write-intensive MariaDB with both NVMe SSD and HDD disks. I recently enabled page compression (innodb_compression_default=ON). I encountered two problems: The database gets slow after a ...
1
vote
1answer
231 views

ALTER innoDB table from row to page compression

My tables have been created with InnoDB row compression (ENGINE=InnoDB ROW_FORMAT=COMPRESSED). Now I am changing them to page compression. According to the official documentation of MariaDB, enabling ...
2
votes
2answers
523 views

is it safe to compress backups for databases with TDE enabled?

I've been reading about this for a long time, and it seems it's not safe to compress a backup when the database has TDE enabled. is anyone experiencing errors during restore with compressed backups ...
0
votes
0answers
54 views

Manual SCSU 'compression' in SQL Server 2017 for NVARCHAR(MAX)

SQL Server has the wonderful SCSU "compression" gem that works on NVARCHAR(1-4000) types. The usage of SCSU effectively means that the physical storage space used is approximately that of ...
1
vote
1answer
89 views

PostgreSQL backup + gzip

We have a need to send PostgreSQL DB backup to CRM developer. We send them and get feedback that the backup can't be "uzipped", as archive is broken. The backup script snippet looks like ...
0
votes
2answers
40 views

Table compression on 500GB table using Enterprise edition with online=on

I am using MS SQL2016 enterprise edition and we have several tables that need to be compressed to save memory and backup space. Right now they are each around 500GB and I am trying to get some space ...
0
votes
1answer
76 views

Question on compression for very large tables in sql server

I have a confusion on how this compression feature works in SQL server: For quite some large tables , ones with over TB, we recently implemented PAGE level compression. Example of how it was done:- ...
0
votes
2answers
92 views

How to plan bring down the space used by databases to as low as possible

Per our security breaks and guidelines when a database grow too big that it cant meet the requirements where it cannot be restored under a given RTO we need to plan on achieving same:- Based on your ...
0
votes
1answer
54 views

SQL Server 2016 compress function details - external decompression

According to SQL Server 2016 docs COMPRESS and DECOMPRESS methods are just a blackbox - you put the data in and after some magic it gets compressed or decompressed. The problem is that I need to find ...
1
vote
1answer
34 views

COMPRESS TABLESPACE 11g

I have quite big datapump dump file and tried to load it into compressed tablespace that is created with "DEFAULT COMPRESS FOR OLTP" because of lack of free space. Anyway, when I look at &...
1
vote
0answers
62 views

SQL Server 2016, history temporal table not compressed by default in Standard Edition

I have a BI database running on SQL Server 2016 Standard SP2 CU14 (13.0.5830.85) in which there are multiple temporal tables defined with default history tables. According to Microsoft, such history ...
1
vote
2answers
257 views

backup using gzip slow

We are currently backing up some schemas in Postgres using this command: pg_dump -h localhost -n test_schema mydb | gzip > /data1/backup/test_chema.dmp.gz And we are getting a rate of 50 Megabytes ...
0
votes
1answer
1k views

Compress JSON String Stored in PostgreSQL, such as MessagePack?

JSON strings are currently being stored in a PostgreSQL 11 table in a text field. For example, a row can have the text field asks containing the string: {"0.000295":1544.2,"0.000324":1050,"0.000325":...
1
vote
1answer
48 views

When would I want to use COMPRESS and DECOMPRESS functions in SQL Server 2016?

In what live operational or development situation /requirement would I want to use the Compress function? Like when should I become thankful for Compress and Decompress functions introduced in ...
3
votes
1answer
821 views

Find Uncompressed/Compressed Sizes of TimescaleDB Database/Table

For a database/table in TimescaleDB and PostgreSQL 11 running on ZFS with both ZFS compression and native TimescaleDB compression enabled, how can you find the uncompressed and compressed sizes when ...
1
vote
1answer
70 views

How does MOVE COMPRESS affect future data?

I'm working with Oracle 11g and a requirement came down the pipeline to "[C]ompress our data [sic]." And I'm wondering how compression will affect future data. The actual PL/SQL isn't an issue (...
0
votes
3answers
8k views

Unable to create tables with ROW_FORMAT=COMPRESSED [closed]

I'm attempting to install Nextcloud on MariaDB 10.4.11-MariaDB-1:10.4.11+maria~bionic in a docker container (mariadb:latest sha256:2f11cf2ec18988aec8346a5cf528d69ac3f0f4fc02af79ba28f4fd47b7778d6f). ...
0
votes
0answers
62 views

Data compression at distribution level in SQL Server replication

I have SQL Server transactional replication setup. For one of the server I am facing slowness due to limited network bandwidth. Is there any way to compress data at distribution level only.
2
votes
2answers
1k views

how to add data compression to a primary key of a table?

We work with tables with over 300GB of size, a small percentage of them tables use sparse columns although that doesn't always agree with CPU utilization as this question shows. For the tables that ...
0
votes
1answer
106 views

data compression does not show when scripting index on ssms

when I right click on SSMS and choose to script the create index: I get this script, without the data_compression: USE [APCore] GO /****** Object: Index [i_agencyId] Script Date: 07/11/2019 12:...
1
vote
1answer
193 views

Row Compression in AlwaysOn Availability Groups

I want to implement row compression on some databases that are in an availability group. Are row and page compression supported in availability group databases? Does it affect the replication, ...
1
vote
1answer
509 views

SQL Server Non Clustered Index Compression

I'm struggling to find out the answer, we currently use compression on all our data warehouse tables, which has improved performance and also greatly reduced the storage space used. I've only ...
11
votes
4answers
2k views

Alternatives to Network Backup

In our environment we have some servers that are in an Always On Availability Group, and some that are standalone. We normally backup to a network share, but we have recently observed that as the ...
10
votes
1answer
8k views

How can I see if the data in a SQL Server table is page-compressed?

This is a follow-on question to a question which I asked yesterday: Can I bulk insert into an empty page-compressed table and get full compression? The answer to that question (paraphrased from Randi ...
7
votes
2answers
1k views

Can I bulk insert into an empty page-compressed table and get full compression?

I have a lot of large tables (around 10 million wide rows) which need to be regularly loaded into SQL Server 2016 for read-only reporting. I would like these tables to be as small as possible on disk, ...
0
votes
1answer
117 views

how to find tables with either sparse or a column set column?

from the error message below: A compressed index is not supported on table that contains sparse columns or a column set column as I was rebuilding an index and changing the data_compression to ...
4
votes
1answer
166 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
593 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
508 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
692 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.
15
votes
1answer
4k 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
553 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
633 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
104 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 ...
5
votes
1answer
1k 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
190 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
138 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
2k 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 ...
2
votes
1answer
391 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
491 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
1k 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 ...
4
votes
1answer
3k 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 ...