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.
133
questions
0
votes
1answer
49 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
39 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 ...
2
votes
1answer
84 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
33 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
0answers
14 views
Backup ScyllaDB/Cassandra remotely with compression
How can I backup ScyllaDB/Cassandra remotely?
With PostgreSQL I can do something like this (backup, zip, transfer):
mkdir -p tmp/backup
OUTFILE=tmp/backup/db_backup_`date +%Y-%m-%d_%H%M%S`.sql.xz
...
0
votes
0answers
28 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
323 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 ...
-1
votes
1answer
44 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
87 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, ...
0
votes
1answer
148 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 ...
8
votes
2answers
3k 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
756 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
54 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
112 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
278 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
228 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
278 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.
14
votes
1answer
2k 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
199 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
426 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
96 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
776 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
65 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
114 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
1k 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
202 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
234 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
798 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
2k 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 ...
13
votes
1answer
9k 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
321 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
836 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
202 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
4k 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
1k 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'...
asked Oct 27 '17 at 10:08
Tom V - try topanswers.xyz
15.1k7 gold badges53 silver badges82 bronze badges
5
votes
2answers
3k 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
927 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 ...
2
votes
1answer
3k 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
420 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
87 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
46 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
2k 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
1k 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
2k 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 ...
10
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
124 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
294 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
8k 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 ...
