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 1sPdlB-005Zvv-Qj for pgsql-general@arkaria.postgresql.org; Fri, 05 Jul 2024 07:53:38 +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 1sPdl8-007MC6-97 for pgsql-general@arkaria.postgresql.org; Fri, 05 Jul 2024 07:53:34 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sPdl7-007MBy-RU for pgsql-general@lists.postgresql.org; Fri, 05 Jul 2024 07:53:34 +0000 Received: from mail-yb1-xb2b.google.com ([2607:f8b0:4864:20::b2b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sPdl2-000WOM-Du for pgsql-general@lists.postgresql.org; Fri, 05 Jul 2024 07:53:33 +0000 Received: by mail-yb1-xb2b.google.com with SMTP id 3f1490d57ef6-dfdcda2bdbcso152668276.1 for ; Fri, 05 Jul 2024 00:53:27 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1720166006; x=1720770806; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=+bYqKJ13r5RSLHlE6YYlFre4AhIalFFM2dv4DCLLB1g=; b=SA05EVZZ+6G8/etRwV/l2/M3/tbbrPyRJb+/Dc0zufjpW4QpgC11e66WPDHuTasu3Y z0/YWLnXIh6X8lKXuvkmZzgvgGtuAzr+UKbmJvUfoTgo7zz0GDqa3M0VQZVdfnWuQAiK 2T5JPL2Mk27QM42KDp3vZLNxkLzA11i6cg8gVhO+tbpNTS2SyoFG2mVpAURGZTcbma/J sYxSBTuKJ9+VEPxoLUqtPxx+KFRuc55zccjGSngSBSmlXuIZFlxih9qdHdLVeMtdOFb7 lNPWwppKUq2kI8mkXVFWpuEvt2BrvWr5q29LauR9kaCIQlNyCPuhCH9K0n9dFPTyNIui 4pSQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1720166006; x=1720770806; h=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=+bYqKJ13r5RSLHlE6YYlFre4AhIalFFM2dv4DCLLB1g=; b=IP81xknc/lK+E/1HaEm85oxKNSEPO75tUv6FugjE/VlWMQRwnP9L12Fu1J7/exxG+r k0vYL77gALQeL3rJP3wVaiV90hyo6WoyqmOkdCcRofevSUb4X31q8aZCw1o9Nu3FYOb3 c3o9Jus1XoPysJyemB7MuLShQHPIR+ZCYLzyZPR+2jxW2xi28woQ0lv6v2coQXCUvf19 RoWxN3kB+jNTuH094TNt1Aflj8JIIaPh2527HbRAzLPjrDkcnZnjg/nE8dmRdaHF2ZPP 9Q7BQMAtvE7CTob6BatgADA22s3ajeeT39DYydK3D5U3NjcCvfP/m/Ryzh03BKJcUtjT vjqw== X-Forwarded-Encrypted: i=1; AJvYcCXfn3G7hg4B4cSIq5QuynUt0U+PtdhTUeTQgbpmOg/TPilM5Fyp9xnXJitgPH4R7vIpRngxEh8pATFlB4HSHGVp8IH4Rll6OZlIdbXmidBzik6a X-Gm-Message-State: AOJu0YyLFcGetd6N5dwUa0H5hY5VmCATTyElGaVcD92enDRJG8xncM6B 5GxgWbQCBzWpkSp69PjpHV5uuSo/Brgqh7V7W+o8aNwjkt85Bal+4tQ0b5/EIS+qT9bpDL0v6mU ggSOIgM+iJ+2445GRvPGJjQVbblP1Eg== X-Google-Smtp-Source: AGHT+IGnD/moVSJd3q2XpQdmmbPNH7HQgf9Q13oDD9V4ZRchYOUou1i7wo+ScvY8LBphHgI0JtprwdyUZ/RDrAdxFYI= X-Received: by 2002:a25:6b0d:0:b0:e02:7fab:f00f with SMTP id 3f1490d57ef6-e03c1926c5fmr3234840276.2.1720166005648; Fri, 05 Jul 2024 00:53:25 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Lok P Date: Fri, 5 Jul 2024 13:23:14 +0530 Message-ID: Subject: Re: Design strategy for table with many attributes To: Guyren Howe , pgsql-general Content-Type: multipart/alternative; boundary="000000000000b8ba8a061c7b5b5c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b8ba8a061c7b5b5c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Some folks in the team suggested to have key business attributes or say frequently queried attributes in individual columns and others in a column in same table clubbed in JSON format. Is that advisable or any issues can occur with this approach? Also not sure how effectively postgres processes JSON (both read and write perspective) as compared to normal column in a oltp environment. Please advise. As David suggested it breaks if a row exceeds the 8k limit I. E a single page size , will that still holds true if we have a column with JSON in it? On Fri, 5 Jul, 2024, 12:04 pm Guyren Howe, wrote: > On Jul 4, 2024, at 23:28, Lok P wrote: > > > > *"Note that you might want to split up the =E2=80=9Cparent=E2=80=9D table= if that > naturally groups its columns together for certain uses. In that case, you > could have the same pk on all the 1:1 tables you then have. In that case, > the pk for each of those tables is also the fk."* > Do you mean having a real FK created through DDL and maintaining it or > just assume it and no need to define it for all the pieces/tables. Only > keep the same PK across all the pieces and as we know these are related t= o > the same transaction and are logically related? > > > A primary key *names something*. Often it=E2=80=99s a kind of platonic > representation of a real thing =E2=80=94 say, a person. > > I might use a person=E2=80=99s login columns in some functions, and the p= erson=E2=80=99s > name, birth date, etc in other functions. > > Rather than have one table, I should split this into two, but use the sam= e > primary key (I would either name both id or both, say, person_id, > irrespective of the name of the table, so it=E2=80=99s clear you=E2=80=99= re doing this). > > You can just do a join on the mutual primary keys as you=E2=80=99d expect= . In > fact, if you name them the same, you can just use NATURAL JOIN. > > So you=E2=80=99d have person_details and person_login tables, and have a = person_id > pk for both. > --000000000000b8ba8a061c7b5b5c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Some folks in the team suggested to have key busines= s attributes or say frequently queried attributes in individual columns and= others in a column in same table clubbed in JSON format. Is that advisable= or any issues can occur with this approach? Also not sure how effectively = postgres processes JSON (both read and write perspective) as compared to no= rmal column in a oltp environment. Please advise.=C2=A0

As David suggested it breaks if a row excee= ds the 8k limit I. E a single page size , will that still holds true if we = have a column with JSON in it?=C2=A0

On Fri, 5 Jul, 2024, 12:04 = pm Guyren Howe, <guyren@gmail.com> wrote:
On Jul 4, 2024, = at 23:28, Lok P <loknath.73@gmail.com> wrote:

"Note that you might want to split = up the =E2=80=9Cparent=E2=80=9D table if that naturally groups its columns = together for certain uses. In that case, you could have the same pk on all = the 1:1 tables you then have. In that case, the pk for each of those tables= is also the fk."

Do you mea= n having a real FK created through DDL and maintaining it or just assume it= and no need to define it for all the pieces/tables. Only keep the same PK = across all the pieces and as we know these are related to the same transact= ion and are logically related?=C2=A0

A primary key *names something*. Often it=E2=80=99s a kind of platonic rep= resentation of a real thing =E2=80=94 say, a person.

I might use a person=E2=80=99s login columns in some functions, and the = person=E2=80=99s name, birth date, etc in other functions.

Rather than have one table, I should split this into two, but use = the same primary key (I would either name both id or both, say, person_id, = irrespective of the name of the table, so it=E2=80=99s clear you=E2=80=99re= doing this).

You can just do a join on the mutual= primary keys as you=E2=80=99d expect. In fact, if you name them the same, = you can just use NATURAL JOIN.

So you=E2=80=99d ha= ve person_details and person_login tables, and have a person_id pk for both= .
--000000000000b8ba8a061c7b5b5c--