Date: Mon, 20 Oct 2025 13:28:10 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Arrays vs separate tables
Message-ID: <pyh2kcisxwrhzz255gaiaawo5yubirdifzsfvz3okqcjeaaqqr@6lq5n6idswg7>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <162e5330-861c-ecc7-45c1-aaa1aff26c88@appl-ecosys.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="igseuptjuvcrz5mk"
Content-Disposition: inline
In-Reply-To: <162e5330-861c-ecc7-45c1-aaa1aff26c88@appl-ecosys.com>
Archived-At: <https://www.postgresql.org/message-id/pyh2kcisxwrhzz255gaiaawo5yubirdifzsfvz3okqcjeaaqqr%406lq5n6idswg7>
Precedence: bulk


--igseuptjuvcrz5mk
Content-Type: text/plain; protected-headers=v1; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
Subject: Re: Arrays vs separate tables
MIME-Version: 1.0

On 2025-10-19 13:43:09 -0700, Rich Shepard wrote:
> Now I'm slowly cleaning up my business tracking database using features n=
ot
> available way back when I developed it. That's why I ask questions that m=
ust
> seem obvious to all of you who work with postgres everyday and have for
> years. I'm reading 'SQL Queries for Mere Mortals' and just ordered the 4th
> edition of 'Database Design for Mere Mortals' to update my SQL knowledge.
>=20
> In the former book I read that sometimes it's better to have multiple val=
ues
> for an atribute in a single row by creating a separate table for that
> attribute rather than using the postgres array capability.

Replace "sometimes" with "usually", or "conventionally".

"No composite values" is the core of the first normal form. And of
course the other normal forms build on the first normal form, so your
dataase will never be normalized if you have arrays or other composite
values (e.g. json) in your database.

That said, sometimes it is advantageous to eschew normalization (if that
wasn't the case, PostgreSQL wouldn't have arrays, json, etc.).

The main reasons (IMHO) are:

* Performance: It's just a lot faster to get a row from a single table
  than to join multiple tables.
* Simplicity: If a set of values is always fetched together and updated
  together, you might as well treat it as a unit and not split it over
  multiple tables

I think the first reason is moot in your case. But it's the reason why
datawarehouses often contain huge tables with hundreds of columns and
lots and lots of redundant data.

The second may be relevant for you. If you always display and edit the
phone numbers of a contact together and your frontend makes it easier to
edit an array than a subset of rows from a table, you might just stuff
them into a table and ignore "purity".

        hjp

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--igseuptjuvcrz5mk
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmj2HMIACgkQ8g5IURL+
KF32NQ//TqonK4EBD6oM70aza3C1aGfBn/LHuHg6nGTNdC/yJ7ThcHEl/IxwSK/H
ZPznDCLUBr9U9G7B9ZC3eXvLgjhKVwb0OzUGBzVKvBfowW9l1383faf5rp03bJGF
SDi8C/UWVRobzV6DCK3QKz+nroJCt67pcTS5ttXODGLBXiyOBiEIqxLrso7QaIch
5ijdIP9+n9MY7Aue0UxMAI89nCZ3DNZgWIn3CZZNF5lo4yH8ja7oW5BwEDYkrsuQ
wERpjSxHu8Q4FGnerrTHtR/fF7r1DOZp6dXTEwPLFwTxG4JzUA8zIGhMCFNvII3R
7FppR9TqZpAT4LQ4wE7MK9nTIf7EQRNX94Q6fKY1rP+qwHpp9KCGD12h6dKhQItY
cMkv+B1JaefV76nxrY3vfbGoQV8jhb9n2hVn6geGRLdQCDN23yzV+zsUjEE/Nnk1
L9vRg8MyYk9BFALgmn0+7wZakLD9xmdFv1NBV4dmr9oOTzLuFjr/PkjNdUdUU0Mr
hPzf91hae4/xowzafHyVqPs/Srv6s5iwBKRSaXyZF4PWmDxSrC+RHscNOJquLVPA
3Biaz7rl133Qh0wIwBdMEwUgD2Tzpk+njMhGZJG6j3em/JjkPOaZS8V5BCeBIkU9
HkNWOffzemPD9OxQQ3OMa3Z9XZTRI+B1k6Z5arfHpRIKK3l0mms=
=1NA7
-----END PGP SIGNATURE-----

--igseuptjuvcrz5mk--