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 1rhvKq-00E5kn-82 for pgsql-general@arkaria.postgresql.org; Wed, 06 Mar 2024 17:45:44 +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 1rhvKo-00Cb8y-OU for pgsql-general@arkaria.postgresql.org; Wed, 06 Mar 2024 17:45:43 +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 1rhvKo-00Cb8o-3I for pgsql-general@lists.postgresql.org; Wed, 06 Mar 2024 17:45:42 +0000 Received: from tamriel.snowman.net ([70.109.60.50]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rhvKk-003B0s-2D for pgsql-general@postgresql.org; Wed, 06 Mar 2024 17:45:41 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id 84F9E5F7B7; Wed, 6 Mar 2024 12:45:33 -0500 (EST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=snowman.net; s=dkim; t=1709747133; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=NnZz1hEgJsglbyu0KNrpHca94A5TpmjL32qT5toVeAs=; b=bYp3hU9oq419SUbtsl82Kn90j/K7BO5APme82OSeF9qCtCqVuzwFE/ANndNaVJ7usYylmt +HsOo/Zt+drAwVI7Ef0zek6PB4RnjjnO5oILD3XG2wpPgjNPuMcxYlqFoigcLa57Uazne9 xJm6PawEy/mHNa4TwME0RFdIlNS6ROk= Date: Wed, 6 Mar 2024 12:45:33 -0500 From: Stephen Frost To: Guyren Howe Cc: PG-General Mailing List Subject: Re: Thoughts on user-defined types for talk at Postgres conference? Message-ID: References: <506dd5f4-65fe-4d34-8cfe-2ebc08b978e4@Spark> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="cCFikam6bH8Ahldv" Content-Disposition: inline In-Reply-To: <506dd5f4-65fe-4d34-8cfe-2ebc08b978e4@Spark> User-Agent: Mutt/2.1.4 (2021-12-11) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --cCFikam6bH8Ahldv Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Greetings, * Guyren Howe (guyren@gmail.com) wrote: > But what *really* sets Postgres apart from comparable systems is user def= ined types. I would like to carefully lay out how to define and use a user-= defined type (I don=E2=80=99t think I have time to dig into doing fancy stu= ff with C functions, so just the basic =E2=80=9Cuser defined sum type=E2=80= =9D), but also any gotchas. What I take it you're referring to here are most specifically called "Composite Types", as discussed here: https://www.postgresql.org/docs/current/sql-createtype.html > And I=E2=80=99d like to finish with some thoughts about when and how to u= se user-defined types. My feeling is that this feature is greatly under-use= d, mostly because it=E2=80=99s so non-standard. But AFAICT, user-defined ty= pes are fine and other than some ugliness due to SQL (mainly needing parent= heses in some unexpected places), fields in a user defined type work perfec= tly well in Postgres=E2=80=99s SQL. I guess you=E2=80=99d need to pull them= apart for values returned to clients, but that isn=E2=80=99t difficult. Yeah, it's a bit grotty when you're trying to reference them (as you note with the comment about the extra parentheses) but it's also a bit painful building them up to supply as input (though not impossible). > So, any gotchas with user defined types? Any thoughts about designing wit= h them? The biggest gotcha (which is a bit different from the usability issues, which we discuss above) from my perspective is the serialization of composite types- we use up a lot more space to store a composite type that looks like: CREATE TYPE twoint AS (a int, b int); then if we create a table as: CREATE TABLE t1 (a int, b int); Let's show this, follow the above two commands, do: CREATE TABLE t2 (c1 twoint); INSERT INTO t1 VALUES (1,2); INSERT INTO t2 VALUES ('(1,2)'); =3D*# select pg_column_size(a) from t1; pg_column_size=20 ---------------- 4 (1 row) =3D*# select pg_column_size(b) from t1; pg_column_size=20 ---------------- 4 (1 row) =3D*# select pg_column_size(a) from t2; pg_column_size=20 ---------------- 29 (1 row) We can see it for the whole row too: =3D*# select pg_column_size(t1.*) from t1; pg_column_size=20 ---------------- 32 (1 row) =3D*# select pg_column_size(t2.*) from t2; pg_column_size=20 ---------------- 53 (1 row) That's an additional 21 bytes, which is really quite a lot. What's included in those 21 bytes are the length (since it's now a variable-length column to PG, unlike the case with the individual int columns in the table where we know from the table structure the length of them), the type information (typmod if there is one and the OID of the composite type), some other info, and then the actual data of the two int values inside of the composite type. I've often wondered if there's a way to reduce this overhead, as I do think that plus some improvements on the usability side would go a long way to making composite types more appealing to users. Still, they are certainly handy in some instances, I just can't recommend heavy use of them for large data sets where size is particularly important (such as in heavy OLTP environments) due to their overhead. Thanks! Stephen --cCFikam6bH8Ahldv Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEwf6gbxKhD863zrx/7WyKOINHZFUFAmXoq7oACgkQ7WyKOINH ZFUffBAAhU9hDRtesCUwVpKC6g0PKXnjTbZLptWFrBI5Es4aBQW4zd5aKAkiQupP IUN9gpIbEaBngfg8Hhj0yK4q4PqQZOR1kqWB81cjtdWIoToVi2cggYtXIxWwr2Fp ULaONBzGMSfWHVm812tf0rIrzanCeBG6Upncltmhg15TBVQCk9gA1zLcLenUMk9H H/n+Xe14YHNhgiQ8oGP7D8Eqv1JC3WFHwdR4TWpgQzs2GxD8Vui9tlbm8T3EPan4 wa6kYwpnMk+M3++uuhyDxFkibu3aUA06G3TdHcH730oESUNWW2poDEoHpkIkHMUF KN3B9VdGPXiQ0w/SOgdxUyWqYCf1w8k25pL3xNGxTsb4KwSxACXrGgns+tqY5QBf iXTGA8lidr/CJuhAp+66/TZteGg4/iDtUjP8yYZnGxL229J0MYz0Q5Az+SwIxWtR +Y5LDvx8Q3jrrCYDUrZ9sOw5R7rVwCQSb2eHxkEbTx54vXGyVc9UNPxBX/Fy4J4k n4J8EiWOJaHRYClvjwVfj+ptsTiCcDEgvgNpJ1IbL+yvVusl5SchSh++9NBLZPsf 862Y6ZaEQQ8W+zAmJT0Hk/KMov/UhFIi4GWTDzYQgyuj5WzJQGn1qDLVW1zoUfK0 VFziny3bBbDL7Crj5LyYcSHEQgWN9yowEVA6PI2FDor5OTs6wQA= =Pebg -----END PGP SIGNATURE----- --cCFikam6bH8Ahldv--