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
1 answer
78 views

Need help on SQL Server data compression, hope to compress more on a large table

We have made good progress following our previous question about SQL Server data compression. For one of the tables, we compressed it from 20 GB to 2 GB, so this proves that the best compression ratio ...
James's user avatar
  • 149
0 votes
1 answer
40 views

How to compose a SQL Server command for doing and un-doing compressions

The difference between simple and detailed WITH clauses: In YouTube tutorial SQL Server Tutorial 20: Table, Index and Row Compression, the example at time 5:43 contains a simple WITH clause of the ...
James's user avatar
  • 149
-1 votes
1 answer
62 views

For row-level or page-level, how to undo a compression in SQL Server? With SQL command and/or GUI Management Studio

We want to compress a table, using SQL statements like the samples below. And, just in case any issue happened after the compression, and we need to roll back to the last good point. How do we do it? ...
James's user avatar
  • 149
0 votes
1 answer
45 views

Compressing a json-shaped postgres response during transport

We have an application that has a complex sql code generation scheme, that outputs queries of the form: SELECT coalesce(json_agg("foo"), '[]') AS "foo" FROM ( SELECT ...
jberryman's user avatar
  • 471
3 votes
1 answer
261 views

How does the XML_COMPRESSION option work?

XML_COMPRESSION has recently gone GA in Azure SQL Database I've been trying to find some details about how it works to understand the pros and cons and so far not found any specifics. Trying the below ...
Martin Smith's user avatar
  • 84.4k
0 votes
0 answers
76 views

Redshift: Any difference in performance between # and temporary tables?

Is there any difference between the below two styles of using temporary tables in Redshift -- in terms of cluster performance (or) R/W into and from the table? I am new to Redshift, hence bit unaware ...
ranit.b's user avatar
  • 131
-2 votes
1 answer
56 views

Is there any database uses lzma?

according to this article, lzma has a great performance on compression ratio. I think lzma database is a good choice if I don't care about compression speed, so is there any database that uses it?
destination's user avatar
0 votes
0 answers
48 views

pg_dump significant size difference between dumps

So we've been doing some temporary backups on windows machines and came across something interesting. Doing plain formatted dumps with no compression and blobs, we get significant size differences ...
mrxaxen's user avatar
0 votes
1 answer
142 views

Mariabackup, Xrtrabackup, how those handling InnodDB page compression, meaning: sparse files?

InnoDB engine, when using page compression, heavily depend on file system sparse file support. InnoDB Page Compression. It raises the question, how will mariabackup/xtrabackup handle those InooDB ...
g.pickardou's user avatar
1 vote
3 answers
340 views

MariaDB column compression and binlog

I have a table with big text column, approx 3M per row, the table is using InnoDB storage engine. The binlog size is an issue, I have binlog turned on and configured to format:row. I do know about ...
g.pickardou's user avatar
0 votes
1 answer
149 views

What is the best option to store XML in SQL Server 2016?

On SQL Server 2016 (13.0.6300.2) we have one table that have two XML columns and around 1 500 000 rows. Size of table is around 150 GB. What is the best option to compress this table ? I was checking ...
adam.g's user avatar
  • 345
0 votes
1 answer
498 views

PostgreSql 14 LZ4 not showing pg_column_compression. Not working with COPY import command

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 ...
ultimo_frogman's user avatar
4 votes
1 answer
1k views

Disable TOAST compression for all columns

I am running PostgreSQL on compressed ZFS file system. One tip mentioned is to disable PostgreSQL's inline TOAST compression because ZFS can compress data better. This can be done by setting column ...
Mikko Ohtamaa's user avatar
0 votes
0 answers
162 views

Backup with Compression of large TDE databases

I'm looking for help with Backup Compression for TDE databases, I'm working out of SQL 2016 (SP3-CU1) and am aware that the MaxTransferSize must be specified as over 64K. A few these DBs are over ...
Rebekah W's user avatar
1 vote
2 answers
1k views

is my data compressed?

Lets say I want to implement basic compression for my table. I know it can be done in two steps, for example: alter table MYTABLE compress; alter table MYTABLE move; Is there a way to check that ...
elfcheg's user avatar
  • 163
0 votes
1 answer
762 views

How to decrypt zipped data in MSSQL table

We're trying to import (reverse engineer) some data from someone else's MS SQL database, without any vendor support. In the past the data has been stored in either plain text or RTF so easy to extract....
Delphinus's user avatar
3 votes
1 answer
419 views

TDE Backups Won't Compress with NOINIT

We run weekly backups of a TDE-enabled database using a command in the following format: BACKUP DATABASE [DBName] TO DISK = N'C:\Temp\DBName.bak' WITH FORMAT, INIT, MEDIANAME = N'DBName ...
Tanner Jotblad's user avatar
1 vote
4 answers
1k 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-...
Googlebot's user avatar
  • 4,521
0 votes
0 answers
60 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 ...
user5721565's user avatar
1 vote
1 answer
522 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 ...
Ivan Snyman's user avatar
0 votes
1 answer
112 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 ...
Patrick's user avatar
  • 678
1 vote
1 answer
564 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; ...
Kokizzu's user avatar
  • 1,363
0 votes
0 answers
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 ...
Aleksey Vitsko's user avatar
3 votes
2 answers
841 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 ...
xhr489's user avatar
  • 805
1 vote
1 answer
424 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 ...
Googlebot's user avatar
  • 4,521
3 votes
1 answer
2k 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 ...
Googlebot's user avatar
  • 4,521
2 votes
2 answers
933 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 ...
Racer SQL's user avatar
  • 7,392
1 vote
1 answer
2k 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 ...
Michał Lipok's user avatar
0 votes
2 answers
293 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 ...
tbear58203's user avatar
0 votes
1 answer
474 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:- ...
BeginnerDBA's user avatar
  • 2,169
0 votes
2 answers
119 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 ...
BeginnerDBA's user avatar
  • 2,169
0 votes
1 answer
193 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 ...
Paweł Sopel's user avatar
1 vote
1 answer
146 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 &...
Mikhail Aksenov's user avatar
1 vote
0 answers
120 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 ...
Jacek Placek's user avatar
1 vote
2 answers
2k 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 ...
Matias's user avatar
  • 141
1 vote
1 answer
5k 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":...
Nyxynyx's user avatar
  • 1,121
1 vote
1 answer
156 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 ...
Vlad SQL's user avatar
3 votes
1 answer
1k 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 ...
Athena Wisdom's user avatar
1 vote
1 answer
168 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 (...
Dortimer's user avatar
  • 113
2 votes
3 answers
12k 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). ...
Tsaukpaetra's user avatar
0 votes
0 answers
131 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.
Dhaval Lakhani's user avatar
3 votes
2 answers
4k 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 ...
Marcello Miorelli's user avatar
1 vote
1 answer
416 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:...
Marcello Miorelli's user avatar
1 vote
1 answer
443 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, ...
igelr's user avatar
  • 2,132
3 votes
1 answer
1k 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 ...
Chris's user avatar
  • 43
11 votes
4 answers
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 ...
SQL_NoExpert's user avatar
  • 1,049
10 votes
1 answer
11k 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 ...
Caitlin M. Shaw's user avatar
8 votes
2 answers
2k 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, ...
Caitlin M. Shaw's user avatar
0 votes
1 answer
482 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 ...
Marcello Miorelli's user avatar
4 votes
1 answer
269 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]....
user9516827's user avatar
  • 1,315