+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Database that can compress a column, or all data, automatically? (/thread-39989.html)
Database that can compress a column, or all data, automatically? - Calab - May-15-2023
I have a Sqlite3 database that I use to store testing data. Most of the columns are small but one column can be HUGE as it stores the JSON results from our test. This column can be up to 500KB is size, with the average being around 62KB. My database is about. My file is about 80MB after about a months usage.
I've done some testing using ZLIB to compress the data in that large column and I can get a 500% compression ratio using it. That means my current database would go from 80 MB to 16MB, which is a great savings in disk space.
What I want to know is, are there are any databases available to python that you can specify to compress a column, or that does data compression, in the background?
RE: Database that can compress a column, or all data, automatically? - ibreeden - May-16-2023
I believe SQL Server can store data compressed, but I have no experience with it.
But why don't you just compress the JSON with the gzip or zlib modules and then store it in your database? Of course you wil have to store the compressed data in a BLOB-type column.
RE: Database that can compress a column, or all data, automatically? - Larz60+ - May-16-2023
also, you can vaccum the database to get rid of 'dust'.
RE: Database that can compress a column, or all data, automatically? - Calab - May-22-2023
(May-16-2023, 07:54 AM)ibreeden Wrote: I believe SQL Server can store data compressed, but I have no experience with it.I think going to the trouble of maintaining a MS database is overkill for our project.
But why don't you just compress the JSON with the gzip or zlib modules and then store it in your database? Of course you wil have to store the compressed data in a BLOB-type column.
I will most likely compress the JSON before storing it to the database. I just didn't want to re-invent the wheel if there was something out there already.