Re: [sqlite] compressed sqlite3 database file?

[James K. Lowden]

On Thu, 11 Apr 2019 18:45:01 -0600
Warren Young  wrote:

> Sure, but what *is* on the disk after a crash is always consistent
> with ZFS, so any decent database engine can recover.

It's been some years, but I saw a presentation about running Postgres
on ZFS.  Every "victory" was a way to compensate for a feature of ZFS.
None of the problems surmounted are presented by normal filesystems.  

The simple fact is that ZFS does nothing for the DBMS.  As you
acknowledge, it does complicate matters.  And the features ZFS supplies
slow down the DBMS by stealing I/O cycles that the DBMS could otherwise
use productively.  

> > It's really not the ideal substrate for a system that takes its
> > fsyncs seriously.
> 
> You know, I?ve just realized that it?s been a really long time since
> I?ve heard anyone seriously talk about running databases on raw
> storage.  

I don't know what you mean by "raw" storage.  A storage substrate that
really syncs when it's told to and did do when it says it did doesn't
have to be a locally attached SATA drive.  It could be a SAN.  It just
has to be a yankee at the end of a dirt road: simple and honest.  ;-)  

> I?ve even heard of people successfully using ZFS snapshots to make
> live, continuous DB replications from one site to another for fast
> failover.

No doubt.  But absence of proof is not proof of absence: just because
it seems to work doesn't mean it can be depended on.  If the DBMS
developer can't vouch for it, it's a sure bet it's depending on at
least one fortuitous coincidence.  

> It calls into question how important, relatively speaking,
> lack of mediation is in system storage design.

We mustn't place much faith in common (a/k/a "best") practices, viz: 

> Of course raw storage isn?t the main alternative to ZFS.  It?s LVM+md
> +XFS and similar lash-ups, which are even worse in this regard.

Exactly.  The simple understanding that a DBMS has many of the
attributes of an operating system, and therefore needs direct access to
the hardware, is lost on many (I would say most) system administrators
and not a few database administrators.  

Now if you'll excuse me I have some kids to chase off my lawn.  

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Simon Slavin]

On 12 Apr 2019, at 1:45am, Warren Young  wrote:

> ZFS

I've used ZFS.  ZFS is a great file system for some purposes.  Fully-fledged 
databases isn't one of them.

Someone already mentioned the problem of a transaction-based DBMS running on a 
transaction-based filesystem.  I can add the problems of a minimum-change 
block-structured database running on a block-structured copy-on-write file 
system on solid state drives.  Also read this



and substitute SQLite for Oracle.  (Ironically, ZFS is a registered trademark 
belonging to Oracle Corporation.)  (Okay, I'm not 100% certain that SQLite has 
exactly the same problems as this article discusses.  So take it with a pinch 
of salt or we can start a new thread.)

OP says he's running on a Mac.  He's probably using APFS, which uses some of 
the techniques ZFS does (most relevantly copy-on-write).  But when APFS was 
being designed in 2016, macOS already used SQLite internally and high-end Macs 
already had SSDs, not spinning drives.  The people who designed APFS would have 
been worrying about SQLite and Solid State Drives from the first day they 
tested APFS on real computers.  I doubt the same is true of ZFS which was 
designed sometime around 2004.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Warren Young]

On Apr 11, 2019, at 1:27 PM, James K. Lowden  wrote:
> 
> On Wed, 10 Apr 2019 15:14:59 -0600
> Warren Young  wrote:
> 
>> If you?re going to buy some more storage, you should put ZFS on it
>> then, too. :)
> 
> That's interesting advice for a DBMS mailing list.  
> 
> ZFS has built-in transactions, of a sort.

ZFS was in fact designed by former database guys for a company that was heavily 
into the database market at the time, enough so that it is no surprise that 
they were eventually bought by a database company.

So yeah, ZFS and databases go together pretty well. :)

The main thing to watch out for is that your ZFS block size be the same as the 
page size of your database, or whatever it calls the equivalent structure.  
Otherwise, you end up with a write amplification problem similar to that 
created by the 512B/4kB mess that happened with mass storage several years ago.

That’s an easy thing to achieve with ZFS, because it lets you set the block 
size on a per filesystem basis, and ZFS filesystems are nearly cost-free to 
create within a ZFS pool.  You can even change the block size after the 
filesystem is created: the old files are still marked with the block size they 
were created with, and new files get the new block size.

Compression is also a per-filesystem attribute, changeable after the FS is 
created, so you can say “compress *these* things, but not these *other* things.”

> There's enough mediation in
> the filesystem to frustrate the efforts of the DBMS to make sure that
> what's committed in the transaction is, in fact, committed to the
> disk.

Sure, but what *is* on the disk after a crash is always consistent with ZFS, so 
any decent database engine can recover.

And if by some chance the DB engine + ZFS managed to lose enough data 
consistency that they cannot recover, you can roll back to a recent snapshot, 
which is cheap to create and easy to automate.

I’ve even heard of people successfully using ZFS snapshots to make live, 
continuous DB replications from one site to another for fast failover.

> It's really not the ideal substrate for a system that takes its
> fsyncs seriously.

You know, I’ve just realized that it’s been a really long time since I’ve heard 
anyone seriously talk about running databases on raw storage.  It calls into 
question how important, relatively speaking, lack of mediation is in system 
storage design.

Of course raw storage isn’t the main alternative to ZFS.  It’s LVM+md+XFS and 
similar lash-ups, which are even worse in this regard.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[James K. Lowden]

On Wed, 10 Apr 2019 15:14:59 -0600
Warren Young  wrote:

> On Apr 10, 2019, at 2:12 PM, Keith Medcalf 
> wrote:
> > 
> > It is far cheaper and much more reliable to just buy some file
> > storage space.  
> 
> If you?re going to buy some more storage, you should put ZFS on it
> then, too. :)

That's interesting advice for a DBMS mailing list.  

ZFS has built-in transactions, of a sort.  There's enough mediation in
the filesystem to frustrate the efforts of the DBMS to make sure that
what's committed in the transaction is, in fact, committed to the
disk.  It's really not the ideal substrate for a system that takes its
fsyncs seriously.  

--jkl


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Peter da Silva]

Oy. I've worked on safety-critical systems with hard real-time constraints
too. For the most part they didn't *have* file systems or the file systems
were basically read-only in production. Sticking a relational database any
closer than the SCADA monitoring node would not be a thing that happens,
let alone using a compressing file system to hold that database. But
there's a whole spectrum of embedded systems between that and arcade games.

On Wed, Apr 10, 2019 at 5:17 PM Keith Medcalf  wrote:

>
> On Wednesday, 10 April, 2019 14:21, Peter da Silva 
> wrote:
>
> >On Wed, Apr 10, 2019 at 3:12 PM Keith Medcalf 
> wrote:
>
> >> Why would anyone fart about with added complication and the
> >> concomittant increased unreliability when storage is so damn cheap?
>
> >Embedded systems and mobile devices.
>
> You mean "play things", for the most part.
>
> By their very definitions "play things" do not require reliability and as
> such the added complication and inherent increase in unreliability due to
> that increased complexity is of no real effect.
>
> I am used to dealing with "important shit".  That means that if it stops
> working, even for a minute, it might entail costs of millions of dollars
> and perhaps a few deaths or cripplings as well.
>
> There is a very great difference between the "streaming media crap" not
> working for a bit and you have to (heavens forbid) read a book, or the mail
> server going down for a day or two, which are really nothing more than
> minor inconveniences by comparison.  The streaming box screws up?  Throw it
> out and buy another.  In the "play things" world adding complexity to
> increase unreliability and save a few pennies is often a reasonable
> trade-off.  After all, nothing of any real significance will be lost -- it
> is merely a bit of inconvenience to suffer through with no real lasting
> impact.
>
> On the other hand if the consequence of failure is certain death of 10
> people, then I would much rather be spending more money on reliable
> hardware to maintain the designed level of reliability than to save a few
> shekels by tossing "compression" into the mix thereby reducing reliability
> and increasing the probability (through an increase in unpredictable
> failure modes) of those 10 people dying.  I think if you were one of those
> 10 people with your life at risk you would see things the same way.
>
> >But of course those probably don't apply here. :)
>
> It is all a matter of perspective.  Lets imaging that the problem with the
> 747MAX was not that the new control system was designed by an idiot and
> that insufficient training on the detection and correction of the "we know
> this is going to be a problem" so intruduced were not the issue.  Lets say
> instead that the files were merely a bit too big for the hard drives they
> decided to use.  They have the option of (a) spending an additional $100
> and getting larger storage and not changing the failure scenario's at all;
> or, (b) not spending any money and instead adding yet another layer of
> software to perform "compression" instead (thus changing the failure
> scenario's because now you have a whole whack of new failure modes).
>
> The "Play Things" people consider that the crash of the airliner and the
> loss of equipment and all life aboard is merely an "inconvenience" and will
> choose option (b) because hey, the software always works, right?  The
> "Important Shit" people will consider that the *possible* increase in risk
> of loss of equipment and life due to the addition of yet more complexity
> cannot be tolerated and will chose option (a) because it is far more cost
> effective than the analysis that will be required to *prove* option (a) has
> not increased the risk.
>
> I simply happen to fall into the "Important Shit" category of people by
> default.  I am somewhat risk-adverse as they say.  If the risk associated
> with a thing is significant, then spend as much as required to reduce that
> risk to an acceptable level.  If the risk associated with a thing is
> negligible, then get the cheapest shit available and when it "breaks" throw
> it out and get another.
>
> This does not mean that the "Play Things" outlook is incorrect.  It merely
> depends on the garden in which you are playing and in to which category the
> product falls.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Simon Slavin]

On 10 Apr 2019, at 8:51pm, Peng Yu  wrote:

> What do you recommend for Mac? Thanks.

I do not have a good answer ready for you.  These days storage is so cheap that 
buying an external 2TB drive is cheaper than spending a lot of time doing 
clever programming.  But if you want to investigate this ...

Is this a read-only database ?  One which you don't need to change ? The 
encryption addon I mentioned in my previous post might be ideal for a 
compressed read-only database.

How close are you to running out of space ?  Are you just trying to use as 
little space as possible or do you have a specific amount of space in mind 
(e.g. fitting the database on particular storage device).

Do you actually need to use SQLite for this ?  As I understand it it's a 
collection of strings, with a number for each string.  Do you need to be able 
to look up the strings, or look up the number and find the string ?  If space 
is so important to you it might be faster to produce hash codes and look up the 
hash code instead.  Hash codes are tiny, and lead to a tiny fixed file format.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Keith Medcalf]

On Wednesday, 10 April, 2019 14:21, Peter da Silva  wrote:

>On Wed, Apr 10, 2019 at 3:12 PM Keith Medcalf  wrote:

>> Why would anyone fart about with added complication and the
>> concomittant increased unreliability when storage is so damn cheap?

>Embedded systems and mobile devices.

You mean "play things", for the most part.

By their very definitions "play things" do not require reliability and as such 
the added complication and inherent increase in unreliability due to that 
increased complexity is of no real effect.

I am used to dealing with "important shit".  That means that if it stops 
working, even for a minute, it might entail costs of millions of dollars and 
perhaps a few deaths or cripplings as well.  

There is a very great difference between the "streaming media crap" not working 
for a bit and you have to (heavens forbid) read a book, or the mail server 
going down for a day or two, which are really nothing more than minor 
inconveniences by comparison.  The streaming box screws up?  Throw it out and 
buy another.  In the "play things" world adding complexity to increase 
unreliability and save a few pennies is often a reasonable trade-off.  After 
all, nothing of any real significance will be lost -- it is merely a bit of 
inconvenience to suffer through with no real lasting impact.

On the other hand if the consequence of failure is certain death of 10 people, 
then I would much rather be spending more money on reliable hardware to 
maintain the designed level of reliability than to save a few shekels by 
tossing "compression" into the mix thereby reducing reliability and increasing 
the probability (through an increase in unpredictable failure modes) of those 
10 people dying.  I think if you were one of those 10 people with your life at 
risk you would see things the same way.

>But of course those probably don't apply here. :)

It is all a matter of perspective.  Lets imaging that the problem with the 
747MAX was not that the new control system was designed by an idiot and that 
insufficient training on the detection and correction of the "we know this is 
going to be a problem" so intruduced were not the issue.  Lets say instead that 
the files were merely a bit too big for the hard drives they decided to use.  
They have the option of (a) spending an additional $100 and getting larger 
storage and not changing the failure scenario's at all; or, (b) not spending 
any money and instead adding yet another layer of software to perform 
"compression" instead (thus changing the failure scenario's because now you 
have a whole whack of new failure modes).

The "Play Things" people consider that the crash of the airliner and the loss 
of equipment and all life aboard is merely an "inconvenience" and will choose 
option (b) because hey, the software always works, right?  The "Important Shit" 
people will consider that the *possible* increase in risk of loss of equipment 
and life due to the addition of yet more complexity cannot be tolerated and 
will chose option (a) because it is far more cost effective than the analysis 
that will be required to *prove* option (a) has not increased the risk.

I simply happen to fall into the "Important Shit" category of people by 
default.  I am somewhat risk-adverse as they say.  If the risk associated with 
a thing is significant, then spend as much as required to reduce that risk to 
an acceptable level.  If the risk associated with a thing is negligible, then 
get the cheapest shit available and when it "breaks" throw it out and get 
another.

This does not mean that the "Play Things" outlook is incorrect.  It merely 
depends on the garden in which you are playing and in to which category the 
product falls.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Wout Mertens]

On Wed, Apr 10, 2019 at 9:51 PM Peng Yu  wrote:

> What do you recommend for Mac? Thanks.
>

Nothing. Apple doesn't want you to have compression, because then you would
take longer to buy a new Mac. The afsctool compression is a laughable hack
that only works on read-only data. Writing to the file uncompresses it.
It's a great way to recover 7GB from the 13GB XCode install, mind you.

You can install ZFS, but that might break any time macOS is upgraded, or
you can install docker and do all your development in docker images, but
that's of course slower.

Wout.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Warren Young]

On Apr 10, 2019, at 2:12 PM, Keith Medcalf  wrote:
> 
> It is far cheaper and much more reliable to just buy some file storage space. 
>  

If you’re going to buy some more storage, you should put ZFS on it then, too. :)

You get a whole lot more from ZFS than just transparent compression.

You actually have to go out of your way to disable compression on ZFS, since in 
the most common cases, you do want it, since compression + I/O is generally 
faster than raw I/O.  The disk space savings is almost a freebie, in that 
context.

We get this partly because computers often have unused CPU capacity, and ZFS 
defaults to one of the fastest lossless compression algorithms, lz4:

   https://facebook.github.io/zstd/

That page is for another up-and-coming alternative for ZFS, zstd, which claims 
to give gzip-like compression levels at near-lz4 speeds.  From their own 
numbers, though, it looks like the default will remain lz4.

About the only time you want to disable compression on ZFS is when you know the 
pool’s contents are compressed already, as with most digital media, so ZFS 
would end up just burning a lot of CPU to no good end otherwise.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[James K. Lowden]

On Wed, 10 Apr 2019 15:20:32 -0500
Peter da Silva  wrote:

> > Why would anyone fart about with added complication and the
> > concomittant increased unreliability when storage is so damn cheap?
> 
> Embedded systems and mobile devices.
> 
> But of course those probably don't apply here. :)

Are you saying my MB Pro isn't portable?  If not, I have an Osborne
Executive to sell you.  

I was going to tell the OP that the 1990s called, and they want their
STAC floppies back.  

--jkl

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Peter da Silva]

On Wed, Apr 10, 2019 at 3:12 PM Keith Medcalf  wrote:

> Why would anyone fart about with added complication and the concomittant
> increased unreliability when storage is so damn cheap?
>

Embedded systems and mobile devices.

But of course those probably don't apply here. :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Keith Medcalf]

It is far cheaper and much more reliable to just buy some file storage space.  

Last I looked 4 TB of NVMe is about $1,000.00.

This is not the old days when a 5 1/2 inch full height 40 MEGABYTE drive cost a 
bundle.  Geez, I remember when I got a bunch of CDC Wren IV 300 MB drives at 
the whopping 50% discounted price of $6,000.00 each.  Compsurfing those dozen 
drives shook the building for two weeks!

Nowadays one can procure 100 TB of RAID-6 for about the price of one of those 
Wren IV drives...

Why would anyone fart about with added complication and the concomittant 
increased unreliability when storage is so damn cheap?


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Warren Young
>Sent: Wednesday, 10 April, 2019 09:05
>To: SQLite mailing list
>Subject: Re: [sqlite] compressed sqlite3 database file?
>
>On Apr 9, 2019, at 11:39 PM, Peng Yu  wrote:
>>
>> Is there a way to make the database file of a size comparable (at
>least
>> not over 5 times) to the original TSV table in the .gz file?
>
>Transparent file compression is a feature of several filesystems:
>NTFS, ZFS, Btrfs, and more:
>
>
>https://en.wikipedia.org/wiki/Comparison_of_file_systems#Allocation_a
>nd_layout_policies
>
>If you can enable this feature on your existing system or switch to
>one of the filesystems that do support it, you don’t need a non-
>default SQLite configuration.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Peng Yu]

What do you recommend for Mac? Thanks.

On 4/10/19, Simon Slavin  wrote:
> On 10 Apr 2019, at 7:08pm, Peng Yu  wrote:
>
>> https://softwarerecs.stackexchange.com/questions/45010/transparent-file-compression-apps-for-macos
>>
>> I work on Mac. Would this be worthwhile to try?
>
> I do not recommend it.  Two reasons:
>
> A) If you copy the file you get an uncompressed result.  This is because
> most functions do not know the file is compressed.  They try to read the
> file block by block and the operating system automatically hands them
> uncompressed blocks.  So if you copy your file to a Flash Drive or back it
> up, you end up with the big version.
>
> B) Macs are moving, or have already moved, away from HFS+ to APFS.  Although
> HFS+ compressed files seem to work without problems in APFS Apple has not
> announced support for this feature.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Regards,
Peng
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Simon Slavin]

On 10 Apr 2019, at 7:08pm, Peng Yu  wrote:

> https://softwarerecs.stackexchange.com/questions/45010/transparent-file-compression-apps-for-macos
> 
> I work on Mac. Would this be worthwhile to try?

I do not recommend it.  Two reasons:

A) If you copy the file you get an uncompressed result.  This is because most 
functions do not know the file is compressed.  They try to read the file block 
by block and the operating system automatically hands them uncompressed blocks. 
 So if you copy your file to a Flash Drive or back it up, you end up with the 
big version.

B) Macs are moving, or have already moved, away from HFS+ to APFS.  Although 
HFS+ compressed files seem to work without problems in APFS Apple has not 
announced support for this feature.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Warren Young]

On Apr 10, 2019, at 12:08 PM, Peng Yu  wrote:
> 
> https://softwarerecs.stackexchange.com/questions/45010/transparent-file-compression-apps-for-macos
> 
> I work on Mac. Would this be worthwhile to try?

The first link didn’t work here because it didn’t like the APFS drive I tried 
it on.  (Symptom: “Expecting f_type of 17, 23 or 24. f_type is 26.”)

I then tried the so-called “Github mirror”, which is no such thing: it’s 
considerably advanced beyond the last version published at the first link, and 
one of the improvements is APFS awareness.

Using that improved version, simple tests then worked, but then attempting to 
use it on a SQLite DB file uncompressed it and left it uncompressed.  I believe 
this is because this OS feature relies on the old resource fork feature, which 
means it only works with apps using the Apple-proprietary programming 
interfaces, not POSIX interfaces, as SQLite does.

> Does the transparent
> compression work at the file system level or at the directory level?

Neither: it works at the file level.

You can point afsctool at a directory and it will compress the files in that 
directory, but if you then drop another file in that directory, it won’t 
automatically be compressed.

The tool will also skip over files it considers “already compressed” unless you 
give it the -L flag, so giving it a directory name isn’t guaranteed to result 
in all files in that directory being compressed.

> Would
> it have a slight chance to corrupt the existent files on the disk (e.g.,
> power outrage during compression)?

Between the resource forks issue and the fact that we’re having to use a third 
party tool to enable it, I wouldn’t put much trust in this feature.

If you want to put your trust in a third-party OS add-on, O3X is worth much 
more of your attention than afsctool:

https://openzfsonosx.org/

If you don’t have a spare disk to feed to it, you can create a pool using raw 
disk images:

   
https://openzfsonosx.org/wiki/FAQ#Q.29_Can_I_set_up_a_test_pool_using_files_instead_of_disks.3F

ZFS’s abilities to a) add vdevs to a pool; and b) replace smaller vdevs with 
larger ones together mean you can safely set the initial pool size to just 
barely larger than the initial DB file plus any ancillary space needed.  (WAL, 
slack pages between VACUUM calls, etc.)  You can then grow the pool 
occasionally to keep ahead of the growth of the DB without sacrificing too much 
on filesystem overhead.  It’d be easy to write an on-demand 20% pool size 
growth script, for instance; it’d be maybe half a dozen lines of Bash.

Lest you go off on an unfortunate tangent from this idea, note that the 
“compressed disk image” feature of Disk Utility won’t help you here.  Those are 
read-only.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Peng Yu]

https://softwarerecs.stackexchange.com/questions/45010/transparent-file-compression-apps-for-macos

I work on Mac. Would this be worthwhile to try?  Does the transparent
compression work at the file system level or at the directory level? Would
it have a slight chance to corrupt the existent files on the disk (e.g.,
power outrage during compression)?

On Wed, Apr 10, 2019 at 11:02 AM Wout Mertens 
wrote:

> As I said in my previous email, I have a 13GB database that transparently
> compresses to 800MB. Not sure if it got through, didn't get replies to my
> last two emails.
>
> Wout.
>
> On Wed., Apr. 10, 2019, 5:04 p.m. Warren Young  wrote:
>
> > On Apr 9, 2019, at 11:39 PM, Peng Yu  wrote:
> > >
> > > Is there a way to make the database file of a size comparable (at least
> > > not over 5 times) to the original TSV table in the .gz file?
> >
> > Transparent file compression is a feature of several filesystems: NTFS,
> > ZFS, Btrfs, and more:
> >
> >
> >
> https://en.wikipedia.org/wiki/Comparison_of_file_systems#Allocation_and_layout_policies
> >
> > If you can enable this feature on your existing system or switch to one
> of
> > the filesystems that do support it, you don’t need a non-default SQLite
> > configuration.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
-- 
Regards,
Peng
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Peter da Silva]

A database trades off space for performance and functionality. It is
expected that a database with indexes that you can randomly access is going
to take more space than the raw data, let alone a compressed version of the
raw data.

On Wed, Apr 10, 2019 at 12:39 AM Peng Yu  wrote:

> I have some TSV table in .gz format of only 278MB. But the
> corresponding sqlite3 database exceeds 1.58GB (without any index). Is
> there a way to make the database file of a size comparable (at least
> not over 5 times) to the original TSV table in the .gz file? Thanks.
>
> --
> Regards,
> Peng
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Wout Mertens]

As I said in my previous email, I have a 13GB database that transparently
compresses to 800MB. Not sure if it got through, didn't get replies to my
last two emails.

Wout.

On Wed., Apr. 10, 2019, 5:04 p.m. Warren Young  wrote:

> On Apr 9, 2019, at 11:39 PM, Peng Yu  wrote:
> >
> > Is there a way to make the database file of a size comparable (at least
> > not over 5 times) to the original TSV table in the .gz file?
>
> Transparent file compression is a feature of several filesystems: NTFS,
> ZFS, Btrfs, and more:
>
>
> https://en.wikipedia.org/wiki/Comparison_of_file_systems#Allocation_and_layout_policies
>
> If you can enable this feature on your existing system or switch to one of
> the filesystems that do support it, you don’t need a non-default SQLite
> configuration.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Warren Young]

On Apr 9, 2019, at 11:39 PM, Peng Yu  wrote:
> 
> Is there a way to make the database file of a size comparable (at least
> not over 5 times) to the original TSV table in the .gz file?

Transparent file compression is a feature of several filesystems: NTFS, ZFS, 
Btrfs, and more:

   
https://en.wikipedia.org/wiki/Comparison_of_file_systems#Allocation_and_layout_policies

If you can enable this feature on your existing system or switch to one of the 
filesystems that do support it, you don’t need a non-default SQLite 
configuration.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Keith Medcalf]

The disassembled bird will always require more tar to coat than the assembled 
bird.  This is because the disassembled bird will have a greater surface area 
to coat with tar than the assembled bird.  This is a fact of physics which, 
although you may try as you might, you cannot change (unless of course you are 
Q in which case you may simply change the gravimetric constant of the universe).

You should probably perform "data normalization" on the contents of your file.  
This is likely to result in the greatest reduction in space used that anything 
else in the multiverse ...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Peng Yu
>Sent: Tuesday, 9 April, 2019 23:39
>To: SQLite mailing list
>Subject: [sqlite] compressed sqlite3 database file?
>
>I have some TSV table in .gz format of only 278MB. But the
>corresponding sqlite3 database exceeds 1.58GB (without any index). Is
>there a way to make the database file of a size comparable (at least
>not over 5 times) to the original TSV table in the .gz file? Thanks.
>
>--
>Regards,
>Peng
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Wout Mertens]

I know of two options: The proprietary
https://sqlite.org/zipvfs/doc/trunk/www/readme.wiki and this extension that
you have to call on strings yourself:
https://github.com/siara-cc/Shox96_Sqlite_UDF

Furthermore, some filesystems allow transparent compression, like ntfs,
bcachefs, zfs and btrfs. I have a 13GB DB that takes up 850MB on btrfs.

Wout.


On Wed, Apr 10, 2019 at 7:39 AM Peng Yu  wrote:

> I have some TSV table in .gz format of only 278MB. But the
> corresponding sqlite3 database exceeds 1.58GB (without any index). Is
> there a way to make the database file of a size comparable (at least
> not over 5 times) to the original TSV table in the .gz file? Thanks.
>
> --
> Regards,
> Peng
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] compressed sqlite3 database file?

[Peng Yu]

I have some TSV table in .gz format of only 278MB. But the
corresponding sqlite3 database exceeds 1.58GB (without any index). Is
there a way to make the database file of a size comparable (at least
not over 5 times) to the original TSV table in the .gz file? Thanks.

-- 
Regards,
Peng
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users