public inbox for [email protected]help / color / mirror / Atom feed
Domains vs data types 4+ messages / 4 participants [nested] [flat]
* Domains vs data types @ 2025-08-20 04:47 Ertan Küçükoglu <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Ertan Küçükoglu @ 2025-08-20 04:47 UTC (permalink / raw) To: [email protected] Hello, I am using PostgreSQL 17.6. I would like to learn if there is any benefit of using domains over data types for table column definitions in terms of performance gain/loss. For example I might have table defined as below create table test ( a integer, b integer, c integer, d varchar(5) ); I might also have ame table defined as below create domain aint integer; create domain s5 varchar(5); create table test_domain ( a aint, b aint, c aint, d s5 ); Does the second table have any technical advantage/disadvantage over plain data type definition? Less metadata in memory? High metadata in memory? Less/increased disk space? Thanks & Regards, Ertan ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Domains vs data types @ 2025-08-20 16:12 Greg Sabino Mullane <[email protected]> parent: Ertan Küçükoglu <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Greg Sabino Mullane @ 2025-08-20 16:12 UTC (permalink / raw) To: Ertan Küçükoglu <[email protected]>; +Cc: [email protected] On Wed, Aug 20, 2025 at 12:48 AM Ertan Küçükoglu <[email protected]> wrote: > Does the second table have any technical advantage/disadvantage over plain > data type definition? > Less metadata in memory? High metadata in memory? Less/increased disk > space? > Same disk space. No disadvantage other than confusing your users, and any performance differences will be so minor as to be unmeasurable. (my two cents: domains are best when the data type is complex AND shared across multiple tables. Even then I tend to avoid them.) Cheers, Greg -- Crunchy Data - https://www.crunchydata.com Enterprise Postgres Software Products & Tech Support ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Domains vs data types @ 2025-08-20 16:56 Ron Clarke <[email protected]> parent: Greg Sabino Mullane <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Ron Clarke @ 2025-08-20 16:56 UTC (permalink / raw) To: Greg Sabino Mullane <[email protected]>; +Cc: Ertan Küçükoglu <[email protected]>; pgsql-general <[email protected]> Opinion: domains are useful if you give them names that are full of meaning. For example if you have the same type of data accross tables "item_number" or "account" etc so that you can use them to describe what you want stored in them and ensure the same defaults, nulls etc are applied accross tables. Having randomly named or encoded lists just makes life more complicated. On Wed, 20 Aug 2025, 18:13 Greg Sabino Mullane, <[email protected]> wrote: > On Wed, Aug 20, 2025 at 12:48 AM Ertan Küçükoglu < > [email protected]> wrote: > >> Does the second table have any technical advantage/disadvantage over >> plain data type definition? >> Less metadata in memory? High metadata in memory? Less/increased disk >> space? >> > > Same disk space. No disadvantage other than confusing your users, and any > performance differences will be so minor as to be unmeasurable. (my two > cents: domains are best when the data type is complex AND shared across > multiple tables. Even then I tend to avoid them.) > > Cheers, > Greg > > -- > Crunchy Data - https://www.crunchydata.com > Enterprise Postgres Software Products & Tech Support > > ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Domains vs data types @ 2025-08-20 17:01 David G. Johnston <[email protected]> parent: Ron Clarke <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: David G. Johnston @ 2025-08-20 17:01 UTC (permalink / raw) To: Ron Clarke <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; Ertan Küçükoglu <[email protected]>; pgsql-general <[email protected]> On Wed, Aug 20, 2025 at 9:57 AM Ron Clarke <[email protected]> wrote: > ...and ensure the same defaults, nulls etc are applied accross tables. > Just as a warning - a deviation we have from the SQL Standard regarding domains and their NOT NULL constraints makes specifying one on a domain a bit of a potential trap. Rely on column NOT NULL. David J. ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-08-20 17:01 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-08-20 04:47 Domains vs data types Ertan Küçükoglu <[email protected]> 2025-08-20 16:12 ` Greg Sabino Mullane <[email protected]> 2025-08-20 16:56 ` Ron Clarke <[email protected]> 2025-08-20 17:01 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox