pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Siddharth Jain <siddhsql@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: How do you optimize the disk IO when you cannot assume a file will start at a boundary then?
Date: Thu, 22 Feb 2024 12:05:16 +0100
Message-ID: <64287632-d1c6-4f44-b1d4-bc4708a8eb37@enterprisedb.com> (raw)
In-Reply-To: <CAPqV3pSfV-ALnjH1o9Ha8m8D0x72Koat+7KcXCSjk_p-1e6L1g@mail.gmail.com>
References: <CAPqV3pSfV-ALnjH1o9Ha8m8D0x72Koat+7KcXCSjk_p-1e6L1g@mail.gmail.com>

On 2/22/24 02:22, Siddharth Jain wrote:
> Hi All,
> 
> I understand the storage layer in databases goes to great lengths to ensure:
> - a row does not cross a block boundary
> - read/writes/allocation happen in units of blocks
> etc. The motivation is that at the OS level, it reads and writes pages
> (blocks), not individual bytes. I am only concerned about SSDs but I think
> the principle applies to HDD as well.
> 
> but how can we do all this when we are not even guaranteed that the
> beginning of a file will be aligned with a block boundary? refer this
> <https://stackoverflow.com/questions/8018449/is-it-guaranteed-that-the-beginning-of-a-file-is-aligned...;
> .
> 
> Further, I don't see any APIs exposing I/O operations in terms of blocks.
> All File I/O APIs I see expose a file as a randomly accessible contiguous
> byte buffer. Would it not have been easier if there were APIs that exposed
> I/O operations in terms of blocks?
> 
> can someone explain this to me?
> 

The short answer is that this is well outside our control. We do the
best we can - split our data files to "our" 8kB pages - and hope that
the OS / filesystem will do the right thing to map this to blocks at the
storage level.

The filesystems do the same thing, to some extent - they align stuff
with respect to the beginning of the partition, but if the partition
itself is not properly aligned, that won't really work.

As for the APIs, we work with what we have in POSIX - I don't think
there are any APIs working with blocks, and it's not clear to me how
would it fundamentally differ from the APIs we have now. Moreover, it's
not really clear which of the "block" would matter. The postgres 8kB
page? The filesytem page? The storage block/sector size?

FWIW I think for SSDs this matters way more than for HDD, because SSDs
have to erase the space before a rewrite, which makes it much more
expensive. But that's not just about the alignment, but about the page
size (with smaller pages being better).


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company





view thread (4+ messages)  latest in thread

Message-ID: <64287632-d1c6-4f44-b1d4-bc4708a8eb37@enterprisedb.com>
Permalink:  ../64287632-d1c6-4f44-b1d4-bc4708a8eb37@enterprisedb.com/
Also on:    postgresql.org/message-id/64287632-d1c6-4f44-b1d4-bc4708a8eb37@enterprisedb.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: tomas.vondra@enterprisedb.com, siddhsql@gmail.com
  Subject: Re: How do you optimize the disk IO when you cannot assume a file will start at a boundary then?
  In-Reply-To: <64287632-d1c6-4f44-b1d4-bc4708a8eb37@enterprisedb.com>
  References: <CAPqV3pSfV-ALnjH1o9Ha8m8D0x72Koat+7KcXCSjk_p-1e6L1g@mail.gmail.com> <64287632-d1c6-4f44-b1d4-bc4708a8eb37@enterprisedb.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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