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 1vCH4w-00BVTt-Ip for pgsql-performance@arkaria.postgresql.org; Fri, 24 Oct 2025 12:39:33 +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 1vCH4v-00G6GY-GQ for pgsql-performance@arkaria.postgresql.org; Fri, 24 Oct 2025 12:39:32 +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 1vCH4v-00G6GO-2C for pgsql-performance@lists.postgresql.org; Fri, 24 Oct 2025 12:39:32 +0000 Received: from mail-il1-x12a.google.com ([2607:f8b0:4864:20::12a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vCH4s-003XU6-18 for pgsql-performance@lists.postgresql.org; Fri, 24 Oct 2025 12:39:31 +0000 Received: by mail-il1-x12a.google.com with SMTP id e9e14a558f8ab-430d7638d27so20003845ab.3 for ; Fri, 24 Oct 2025 05:39:30 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1761309569; x=1761914369; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=F7p18pKVmznHCiX27QC7NyTNGRHNHHKRcE1uYz04DDk=; b=aXzfEg5db3i0GXxrMuOiVG0+Qw2GHgfhYTmZMUWlXESuYtOY2Hu0k0pGLX7p/p+xVi aMa7zZVeVYMIdNMDeBIhs1CSWCsfB63einaEde+fwvFGE6xSf9OgSLLGrgidBpeR9MgB inDn+AJq0DpdPRSNrD0pGH3pI41xRyMC7ge5T/za6GnMJKTRHtvGZvNHqhRxltC6GRgv pPlTFXKPnvXMO9CaMkTh4m0AY2O7ehbYUKJaixVyT7UO74PUnejYdc1Nr3Ce7r45yQXs hNzY4ploEcmPnyeZXInIIzdIT/jobxBLw7ysGncmVxjApwiFxfoBYvBoT+pjG6l/c0rp 41Qw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1761309569; x=1761914369; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=F7p18pKVmznHCiX27QC7NyTNGRHNHHKRcE1uYz04DDk=; b=KnBD/rCpOaQIKQo+lILuy25K8Ubdi8G9EZZhFWInfVfH1E1rOxL2BleCB33m2Vf3NZ xqdOrO8r6YDKVD20mu5PjPTU9AD+ugif2QCdBrYSI5pi2GyWo6Ra5miKAP+YTS/lCh8d /tyJC7s9iLYxbRWCMOKdu8+jnxJHTykrpxgaMSp2UFHz4ludq0FwVJwcGmKy3/OXhE4t fxTsr/Yx7ihbqySh0WjSuAmPIUiGp3LzcHnvtIa+Jfkkka8wPpiZHfDuK9y27PWKAzo+ N0reUn0/3mk4VxZkRb+/QYJGmqp0blzpZ6WOpDN9wNAhPdi98qrf3q2YfogSBrCmMBSv m0gg== X-Forwarded-Encrypted: i=1; AJvYcCUqlYdI054j6Xf/7q1+yLlcoKXfRVjBR4Aw1/z5+4igNo3uXy4Qf8Quzhq9A1G3oYy0UHc6F3vUCiFONWpbdUSBrQ==@lists.postgresql.org X-Gm-Message-State: AOJu0YznGeXvL/bsULMsaK9sWMRrs0oNyc39ioVWqn9h/+bB/jpVDtTJ p4ofbxjYL8gF1u49NZ/aXQSIY6R0aHO9jSziv3shNqxBmAKfZj8GxbR+gJTaKmYiltIDhqBnLvw G5pAjDZnqpu6Qu0kzxtsXhUd1Cv9EcyQ= X-Gm-Gg: ASbGncvAztcVJDJrEav1BNnlYykXwIF0YXIKFd5ghdjnqVQUOhTi6siucoW6eLsCjKK Au6Cvw5ojJ/j+qfzi1vFLEgjnZyi+gFpqRTG0cHfA5nMH0ztwQNITgC7qT04BsN8gj0mOYS+pLK e0McHUoal3zywiN4D7Q08k/7yZOL+qn+qxWSKBeTZrxM4Tl57U6KdOQBuU9Z9aD/fyczk7Sg53K +aKSXUQ1mAh6ckUKNJQa+MS+S5LIt0BrQ1m1dGcsk5PFTusD/CMYEx6d8NxwWmlW0Nrd8ajZTWJ QuBzMPq8wiel8KPuXzw= X-Google-Smtp-Source: AGHT+IEqQGUKUhAg6angTOtkvqfmKNbH3WTkcI4OTjakwUpcovBm82wQ2mrsRpfGe/p6kZTdp9Z6ZBOwrzhRcWxR84o= X-Received: by 2002:a92:ca4a:0:b0:430:c0e4:9e43 with SMTP id e9e14a558f8ab-431ebeb3f53mr29598765ab.6.1761309569509; Fri, 24 Oct 2025 05:39:29 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Greg Sabino Mullane Date: Fri, 24 Oct 2025 08:38:57 -0400 X-Gm-Features: AWmQ_bmf0rcj_gNk894Eik80ibvsp1b5jy1emYW0FySWyxIY04pQPTIDRt7GBW8 Message-ID: Subject: Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 To: Jonathan Reis Cc: David Rowley , Laurenz Albe , Olof Salberger , pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000003aee160641e6d7fb" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000003aee160641e6d7fb Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Oct 23, 2025 at 10:14=E2=80=AFPM Jonathan Reis wrote: > Can't use pg_partman (this is true?) > Apologies, this warrants an explanation. It turns out I was wrong to be concerned. I was worried about pg_partman being able to partition by the decoded value of a column, but it already handles that quite well. From the docs (where col3 is of type uuid): SELECT partman.create_parent( p_parent_table :=3D 'partman_test.time_taptest_table' , p_control :=3D 'col3' , p_interval :=3D '1 day' , p_time_encoder :=3D 'partman.uuid7_time_encoder' , p_time_decoder :=3D 'partman.uuid7_time_decoder' ); This also kind of answers your other question about your uuidv7_floor function. While not native to Postgres, the uuid7_time_encoder function used above is extremely similar to yours. See https://github.com/pgpartman/pg_partman/blob/development/sql/functions/util= _uuid7_time.sql Cheers, Greg -- Crunchy Data - https://www.crunchydata.com Enterprise Postgres Software Products & Tech Support --0000000000003aee160641e6d7fb Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Oct 23, 2025 at 10:14=E2=80=AFPM = Jonathan Reis <jon.reis@conevit= y.com> wrote:
=
Can't use pg_par= tman (this is true?)

Apologies,= this warrants an explanation. It turns out I was wrong to be concerned. I = was worried about pg_partman being able to partition by the decoded value o= f a column, but it already handles that quite well. From the docs (where co= l3 is of type uuid):

SELECT partman.create=
_parent(
    p_parent_table :=3D 'partman_test.time_taptest_table'
    , p_control :=3D 'col3'
    , p_interval :=3D '1 day'
    , p_time_encoder :=3D 'partman.uuid7_time_encoder'
    , p_time_decoder :=3D 'partman.uuid7_time_decoder'
);

This also kind of answers your other ques= tion about your uuidv7_floor function. While not native to Postgres, the uu= id7_time_encoder function used above is extremely similar to yours. See=C2= =A0
Cheers,
Greg

--
Enterprise Postgres Software Products & Te= ch Support

--0000000000003aee160641e6d7fb--