pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex on (fixed size) bytea value
5+ messages / 3 participants
[nested] [flat]
* Index on (fixed size) bytea value
@ 2023-06-19 20:05 Les <nagylzs@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Les @ 2023-06-19 20:05 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Dear fellow list members,
I'm in the process of implementing a file storage system that is based on
PostgreSQL and streaming replication. There will possibly be many similar
files stored. I would like to implement block-level deduplication: each
file consists of a series of blocks, and each unique block is stored only
once (e.g. one block may be reused by multiple files). It will be part of a
bigger software, e.g. the same database will be used for other purposes too.
Here is the basic idea for storing individual blocks:
create table block(
id uuid not null primary key,
block bytea not null,
hs256 bytea not null
) ;
create unique index uidx_block_hs256 on block(hs256);
create or replace function trg_biu_block() returns trigger language plpgsql
as
$function$
begin
new.hs256 = digest(new.block, 'sha256');
end;
$function$;
create trigger trg_biu_block before insert or update on block for each row
execute procedure trg_biu_block();
This is just for storing the blocks. I'm going to put this "block" table
into a separate tablespace. File operations will be at least 95% read and
at most 5% write. (Streaming replication will hopefully allow almost
horizontal scaling for read operations.) Most of the files will be several
megabytes in size (images), and some of them will be 100MB or more
(videos). Total capacity is in the 10TB range. Storage will be SSD
(possibly fiber connected, or local RAID, we are not sure yet).
I do not want to use PostgreSQL large objects, because it does not have
block level deduplication.
Here are some things that I need help with:
1. What should be the maximum size of a block? I was trying to find out the
optimal value. Default BLCKSZ is 8192 bytes. AFAIK PostgreSQL does not
allow a row to occupy multiple blocks. I don't know enough to calculate the
optimal "block size" (the max. number of bytes stored in a single row in
the block.block field), but I suspect that it should be 4K or something
similar. I think that it should be as large as possible, without hitting
the toast. My thinking is this: most files will be at least 1MB in size, so
most "block" rows will reach the maximum tuple size. It would be practical
to make one row in the "block" table occupy almost one PostgreSQL block.
2. I'm not sure if it would be beneficial to increase BLCKSZ. I will be
able to test the speed of my (not yet finished) implementation with
different BLCKSZ values, but that alone won't help me make the decision,
because AFAIK BLCKSZ must have a fixed value for the PostgreSQL instance,
so it will affect all other tables in the database. It would be hard to
tell how changing BLCKSZ would affect the system as a whole.
3. In the above example, I used SHA-256 (pgcrypto), because apparently it
is very well optimized for 64 bit machines, and it has practically zero
chance of a collision. I think that sha512 would be an overkill. But I'm
not sure that this is the best choice. Maybe somebody with more experience
can suggest a better hash function.
4. The hs256 value will always be non-null, fixed 32 byte binary value, but
probably the query planner will not know anything about that. I was also
thinking about bit(256), but I don't see an easy way to convert the bytea
digest into bit(256). A simple type cast won't work here. Maybe using bytea
here is perfectly fine, and creating an index on the hs256 bytea fields is
as effective as possible.
I'm not looking for a definitive answer, just trying to get some hints from
more experienced users before I fill up the drives with terabytes of data.
Thank you,
Laszlo
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Index on (fixed size) bytea value
@ 2023-06-19 20:30 David G. Johnston <david.g.johnston@gmail.com>
parent: Les <nagylzs@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2023-06-19 20:30 UTC (permalink / raw)
To: Les <nagylzs@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Mon, Jun 19, 2023 at 1:05 PM Les <nagylzs@gmail.com> wrote:
> AFAIK PostgreSQL does not allow a row to occupy multiple blocks.
>
Your plan is going to heavily involve out-of-band storage. Please read up
on it here:
https://www.postgresql.org/docs/current/storage-toast.html
I'm not looking for a definitive answer, just trying to get some hints from
> more experienced users before I fill up the drives with terabytes of data.
>
>
Store a hash (and other metadata, like the hashing algorithm) as well as
the path to some system better designed for object storage and retrieval
instead.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Index on (fixed size) bytea value
@ 2023-06-20 06:13 Les <nagylzs@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Les @ 2023-06-20 06:13 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
David G. Johnston <david.g.johnston@gmail.com> ezt írta (időpont: 2023.
jún. 19., H, 22:30):
> On Mon, Jun 19, 2023 at 1:05 PM Les <nagylzs@gmail.com> wrote:
>
>> AFAIK PostgreSQL does not allow a row to occupy multiple blocks.
>>
>
> Your plan is going to heavily involve out-of-band storage. Please read up
> on it here:
>
> https://www.postgresql.org/docs/current/storage-toast.html
>
I'm aware of the TOAST, and how it works. I was referring to it ("I think
that it should be as large as possible, without hitting the toast. ") I
have designed a separate "block" table specifically to avoid storing binary
data in the TOAST. So my plan is not going to involve out-of-band storage.
Just to make this very clear: a record in the block table would store a
block, not the whole file. My question is to finding the optimal block size
(without hitting the toast), and finding the optimal hash algorithm for
block de-duplication.
Unless I totally misunderstood how the TOAST works. (?)
Laszlo
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Index on (fixed size) bytea value
@ 2023-06-20 06:50 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Les <nagylzs@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2023-06-20 06:50 UTC (permalink / raw)
To: Les <nagylzs@gmail.com>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Tue, 2023-06-20 at 08:13 +0200, Les wrote:
> I'm aware of the TOAST, and how it works. I was referring to it ("I think that it should
> be as large as possible, without hitting the toast. ") I have designed a separate "block"
> table specifically to avoid storing binary data in the TOAST. So my plan is not going to
> involve out-of-band storage.
>
> Just to make this very clear: a record in the block table would store a block, not the
> whole file. My question is to finding the optimal block size (without hitting the toast),
> and finding the optimal hash algorithm for block de-duplication.
Then you would ALTER the column and SET STORAGE MAIN, so that it does not ever use TOAST.
The size limit for a row would then be 8kB minus page header minus row header, which
should be somewhere in the vicinity of 8140 bytes.
If you want your block size to be a power of two, the limit would be 4kB, which would waste
almost half your storage space.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Index on (fixed size) bytea value
@ 2023-06-20 07:18 Les <nagylzs@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 5+ messages in thread
From: Les @ 2023-06-20 07:18 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-performance@lists.postgresql.org
>
>
> Then you would ALTER the column and SET STORAGE MAIN, so that it does not
> ever use TOAST.
>
> The size limit for a row would then be 8kB minus page header minus row
> header, which
> should be somewhere in the vicinity of 8140 bytes.
>
> If you want your block size to be a power of two, the limit would be 4kB,
> which would waste
> almost half your storage space.
>
Oh I see. So if I want to save some space for future columns, then storing
about 7500 bytes in the "block bytea" column would be close to optimal,
utilizing more than 90% of the block space. I guess that the fillfactor
setting will have no effect on this table, and it does not matter if I set
it or not.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-06-20 07:18 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-06-19 20:05 Index on (fixed size) bytea value Les <nagylzs@gmail.com>
2023-06-19 20:30 ` David G. Johnston <david.g.johnston@gmail.com>
2023-06-20 06:13 ` Les <nagylzs@gmail.com>
2023-06-20 06:50 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-06-20 07:18 ` Les <nagylzs@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox