Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rd6wU-003swd-Kl for pgsql-general@arkaria.postgresql.org; Thu, 22 Feb 2024 11:08:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rd6wT-00Agol-8M for pgsql-general@arkaria.postgresql.org; Thu, 22 Feb 2024 11:08:41 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rd6tL-00Ac1J-0H for pgsql-general@lists.postgresql.org; Thu, 22 Feb 2024 11:05:27 +0000 Received: from mail-ed1-x52c.google.com ([2a00:1450:4864:20::52c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rd6tC-000QFe-UJ for pgsql-general@lists.postgresql.org; Thu, 22 Feb 2024 11:05:26 +0000 Received: by mail-ed1-x52c.google.com with SMTP id 4fb4d7f45d1cf-55f279dca99so2087532a12.3 for ; Thu, 22 Feb 2024 03:05:19 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1708599918; x=1709204718; darn=lists.postgresql.org; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=3hIpM8p1BHAFX/B2FsQTk8rVYsjPwpzqJolqqD+Yn88=; b=NrREg1UB2w5CI8inpfge+huGNf9JnXkywor4AI+TrKEOO7WDRD/zcRB2ug45aH0rFZ 7oKhnjOd1Ry+Mmw/PE8mD/1xJ5DtN45u2dIOZGKhNlNGxm7UXXiaFgMoqs/iIxTLW8ls yKEXa2Mdc7eNTxs35cYFLT70vf6EErxmwJAjHhlXBtDVl52qRvm+/447qB4u48emB6xa PCKslsDXlzqzeIc7lk5JnjMIcwG6l9bi88+PcKZDEUnkQEOcgaUh2F0ImSoXG2uQkgCF CypJjrnVrNFueLUZUoRBzxQMkddw1fiTriP1tvdml83nApJ4YApBbhm5cVEfEbF3XVDI evZw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1708599918; x=1709204718; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=3hIpM8p1BHAFX/B2FsQTk8rVYsjPwpzqJolqqD+Yn88=; b=YgnA2V8gKSNhv2cXlC2Ibn9hP8q2LeD9zv8dzwlmHhCwtdFm3Z0jeHTht/M3oocdP2 CX0ODD3C/+OgMrHxjehcu+rtZinpklfLYAcycOKJ+r7ItEXrdNyYI4e/xOkcE2IP1xSY /GhNbaV2WN3wVp+qh7wu25EVX2qEklzFw8a0YkafUpOk+kIBuP/MUnHV1CE3kEkn0F4n egCk4K+q1IS7eJw23mPx4z+tadWmNVrYYqYO/LVyB+Psoi9yi8/nEs/jeugluYXKjHQz 9Tl/7ezmrT5U9TPokj7aANlx1SohK0yrPeW6uoKAakpW4ytHtO+twZ1YEoF/ZQpKsTmY 1kng== X-Forwarded-Encrypted: i=1; AJvYcCWkXH+9sbzJ1WJmRqaze9kaptMlfRUOzflmpyv1hB0ec1DzwP4jJ0eSVyQ7pJlSI/LaA7G9soNkalwDbB6hdCRyyBGFPGm9DiijZqEzZ6tFsUYn X-Gm-Message-State: AOJu0YxD45+qQ1qtg2Ae0AQsbcJ3qGcsZUMeTSjBOHDF8yR5a844PzNi jG10K76L57tSk9P4voSedi9VohY4lt+NCFB6N2pHIV504BfVZ/Bg9UHU2jFw5Q== X-Google-Smtp-Source: AGHT+IGE79Ri72gYdr7gZLQwgJq0j6jkhFxsen8GpltOmy938FmEVweKwtJ2Da1WvI3TvbWchy67Bw== X-Received: by 2002:a50:fa81:0:b0:564:e5ad:2530 with SMTP id w1-20020a50fa81000000b00564e5ad2530mr3631626edr.35.1708599918000; Thu, 22 Feb 2024 03:05:18 -0800 (PST) Received: from [10.137.0.18] (ip-86-49-229-30.bb.vodafone.cz. [86.49.229.30]) by smtp.gmail.com with ESMTPSA id u19-20020aa7d993000000b005653c441a20sm384818eds.34.2024.02.22.03.05.17 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 22 Feb 2024 03:05:17 -0800 (PST) Message-ID: <64287632-d1c6-4f44-b1d4-bc4708a8eb37@enterprisedb.com> Date: Thu, 22 Feb 2024 12:05:16 +0100 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: How do you optimize the disk IO when you cannot assume a file will start at a boundary then? Content-Language: en-US To: Siddharth Jain , pgsql-general@lists.postgresql.org References: From: Tomas Vondra In-Reply-To: Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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 > > . > > 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