Subject: Re: Large files for relations

From: Corey Huinker <[email-masked]>
Date: Tue, 9 May 2023 16:52:49 -0400

On Wed, May 3, 2023 at 1:37 AM Thomas Munro <[email-masked]> wrote:

> On Wed, May 3, 2023 at 5:21 PM Thomas Munro <[email-masked]>
> wrote:
> > rsync --link-dest
>
> I wonder if rsync will grow a mode that can use copy_file_range() to
> share blocks with a reference file (= previous backup).  Something
> like --copy-range-dest.  That'd work for large-file relations
> (assuming a file system that has block sharing, like XFS and ZFS).
> You wouldn't get the "mtime is enough, I don't even need to read the
> bytes" optimisation, which I assume makes all database hackers feel a
> bit queasy anyway, but you'd get the space savings via the usual
> rolling checksum or a cheaper version that only looks for strong
> checksum matches at the same offset, or whatever other tricks rsync
> might have up its sleeve.
>

I understand the need to reduce open file handles, despite the
possibilities enabled by using large numbers of small file sizes.
Snowflake, for instance, sees everything in 1MB chunks, which makes
massively parallel sequential scans (Snowflake's _only_ query plan)
possible, though I don't know if they accomplish that via separate files,
or via segments within a large file.

I am curious whether a move like this to create a generational change in
file file format shouldn't be more ambitious, perhaps altering the block
format to insert a block format version number, whether that be at every
block, or every megabyte, or some other interval, and whether we store it
in-file or in a separate file to accompany the first non-segmented. Having
such versioning information would allow blocks of different formats to
co-exist in the same table, which could be critical to future changes such
as 64 bit XIDs, etc.