public inbox for [email protected]
help / color / mirror / Atom feedRe: Arrays vs separate tables
8+ messages / 5 participants
[nested] [flat]
* Re: Arrays vs separate tables
@ 2025-10-20 12:55 Rich Shepard <[email protected]>
2025-10-20 13:38 ` Re: Arrays vs separate tables Michał Kłeczek <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Rich Shepard @ 2025-10-20 12:55 UTC (permalink / raw)
To: [email protected]
On Mon, 20 Oct 2025, Peter J. Holzer wrote:
> * 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
> 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".
Peter,
Thank you. That's my use case.
Regards,
Rich
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Arrays vs separate tables
2025-10-20 12:55 Re: Arrays vs separate tables Rich Shepard <[email protected]>
@ 2025-10-20 13:38 ` Michał Kłeczek <[email protected]>
2025-10-20 14:07 ` Re: Arrays vs separate tables Rich Shepard <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Michał Kłeczek @ 2025-10-20 13:38 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: [email protected]
> On 20 Oct 2025, at 14:55, Rich Shepard <[email protected]> wrote:
>
> On Mon, 20 Oct 2025, Peter J. Holzer wrote:
>
>> * 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
>
>> 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".
>
> Peter,
>
> Thank you. That's my use case.
There is also another concern - do you want to make sure phone numbers are not shared?
—
Michal
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Arrays vs separate tables
2025-10-20 12:55 Re: Arrays vs separate tables Rich Shepard <[email protected]>
2025-10-20 13:38 ` Re: Arrays vs separate tables Michał Kłeczek <[email protected]>
@ 2025-10-20 14:07 ` Rich Shepard <[email protected]>
2025-10-20 14:21 ` Re: Arrays vs separate tables Michał Kłeczek <[email protected]>
2025-10-20 14:25 ` Re: Arrays vs separate tables Ron Johnson <[email protected]>
2025-10-20 14:42 ` Re: Arrays vs separate tables Adrian Klaver <[email protected]>
0 siblings, 3 replies; 8+ messages in thread
From: Rich Shepard @ 2025-10-20 14:07 UTC (permalink / raw)
To: [email protected]
On Mon, 20 Oct 2025, Michał Kłeczek wrote:
> There is also another concern - do you want to make sure phone numbers are
> not shared?
Michal,
Shared with whom? I run a solo professional services consultancy so there's
only me here the database.
Rich
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Arrays vs separate tables
2025-10-20 12:55 Re: Arrays vs separate tables Rich Shepard <[email protected]>
2025-10-20 13:38 ` Re: Arrays vs separate tables Michał Kłeczek <[email protected]>
2025-10-20 14:07 ` Re: Arrays vs separate tables Rich Shepard <[email protected]>
@ 2025-10-20 14:21 ` Michał Kłeczek <[email protected]>
2025-10-20 14:25 ` Re: Arrays vs separate tables Rich Shepard <[email protected]>
2 siblings, 1 reply; 8+ messages in thread
From: Michał Kłeczek @ 2025-10-20 14:21 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: [email protected]
> On 20 Oct 2025, at 16:07, Rich Shepard <[email protected]> wrote:
>
> On Mon, 20 Oct 2025, Michał Kłeczek wrote:
>
>> There is also another concern - do you want to make sure phone numbers are
>> not shared?
>
> Michal,
>
> Shared with whom?
Sorry, I wasn’t clear.
How do you make sure a phone number is not shared by two (or more) customers?
—
Michal
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Arrays vs separate tables
2025-10-20 12:55 Re: Arrays vs separate tables Rich Shepard <[email protected]>
2025-10-20 13:38 ` Re: Arrays vs separate tables Michał Kłeczek <[email protected]>
2025-10-20 14:07 ` Re: Arrays vs separate tables Rich Shepard <[email protected]>
2025-10-20 14:21 ` Re: Arrays vs separate tables Michał Kłeczek <[email protected]>
@ 2025-10-20 14:25 ` Rich Shepard <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Rich Shepard @ 2025-10-20 14:25 UTC (permalink / raw)
To: [email protected]
On Mon, 20 Oct 2025, Michał Kłeczek wrote:
> How do you make sure a phone number is not shared by two (or more)
> customers?
Michal,
If it's the company's main telephone number then it's shared by all
employees with phones on their desks. If it's a direct number then it
connects to only a single phone.
Either way it doesn't matter to me. When the person I call is at their desk
my call will either be transferred by the receptionist or directly ring their
phone.
Rewgards,
Rich
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Arrays vs separate tables
2025-10-20 12:55 Re: Arrays vs separate tables Rich Shepard <[email protected]>
2025-10-20 13:38 ` Re: Arrays vs separate tables Michał Kłeczek <[email protected]>
2025-10-20 14:07 ` Re: Arrays vs separate tables Rich Shepard <[email protected]>
@ 2025-10-20 14:25 ` Ron Johnson <[email protected]>
2 siblings, 0 replies; 8+ messages in thread
From: Ron Johnson @ 2025-10-20 14:25 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
On Mon, Oct 20, 2025 at 10:07 AM Rich Shepard <[email protected]>
wrote:
> On Mon, 20 Oct 2025, Michał Kłeczek wrote:
>
> > There is also another concern - do you want to make sure phone numbers
> are
> > not shared?
>
> Michal,
>
> Shared with whom? I run a solo professional services consultancy so there's
> only me here the database.
>
Multiple contacts at the same client company. If, for whatever reason, the
phone number changes you've got to update X rows in your contacts table,
whereas only one row needs to be updated when the schema meets 1NF. This
eliminates update anomalies.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Arrays vs separate tables
2025-10-20 12:55 Re: Arrays vs separate tables Rich Shepard <[email protected]>
2025-10-20 13:38 ` Re: Arrays vs separate tables Michał Kłeczek <[email protected]>
2025-10-20 14:07 ` Re: Arrays vs separate tables Rich Shepard <[email protected]>
@ 2025-10-20 14:42 ` Adrian Klaver <[email protected]>
2025-10-20 14:48 ` Re: Arrays vs separate tables Rob Sargent <[email protected]>
2 siblings, 1 reply; 8+ messages in thread
From: Adrian Klaver @ 2025-10-20 14:42 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; [email protected]
On 10/20/25 07:07, Rich Shepard wrote:
> On Mon, 20 Oct 2025, Michał Kłeczek wrote:
>
>> There is also another concern - do you want to make sure phone numbers
>> are
>> not shared?
>
> Michal,
>
> Shared with whom? I run a solo professional services consultancy so there's
> only me here the database.
That is the heart of the matter, you should do what makes sense to you.
If you can get the information you need in the manner you want then
don't change things. Given the size of your datasets I don't see that
changes will materially affect the performance of your queries. I am
pretty sure most of the time the Postgres planner is resorting to a
sequence scan anyway.
>
> Rich
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Arrays vs separate tables
2025-10-20 12:55 Re: Arrays vs separate tables Rich Shepard <[email protected]>
2025-10-20 13:38 ` Re: Arrays vs separate tables Michał Kłeczek <[email protected]>
2025-10-20 14:07 ` Re: Arrays vs separate tables Rich Shepard <[email protected]>
2025-10-20 14:42 ` Re: Arrays vs separate tables Adrian Klaver <[email protected]>
@ 2025-10-20 14:48 ` Rob Sargent <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Rob Sargent @ 2025-10-20 14:48 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: Rich Shepard <[email protected]>; [email protected]
> On Oct 20, 2025, at 8:42 AM, Adrian Klaver <[email protected]> wrote:
>
> On 10/20/25 07:07, Rich Shepard wrote:
>>> On Mon, 20 Oct 2025, Michał Kłeczek wrote:
>>> There is also another concern - do you want to make sure phone numbers are
>>> not shared?
>> Michal,
>> Shared with whom? I run a solo professional services consultancy so there's
>> only me here the database.
>
> That is the heart of the matter, you should do what makes sense to you. If you can get the information you need in the manner you want then don't change things. Given the size of your datasets I don't see that changes will materially affect the performance of your queries. I am pretty sure most of the time the Postgres planner is resorting to a sequence scan anyway.
And all the hot tables have been paged in if not the entire db.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2025-10-20 14:48 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-20 12:55 Re: Arrays vs separate tables Rich Shepard <[email protected]>
2025-10-20 13:38 ` Michał Kłeczek <[email protected]>
2025-10-20 14:07 ` Rich Shepard <[email protected]>
2025-10-20 14:21 ` Michał Kłeczek <[email protected]>
2025-10-20 14:25 ` Rich Shepard <[email protected]>
2025-10-20 14:25 ` Ron Johnson <[email protected]>
2025-10-20 14:42 ` Adrian Klaver <[email protected]>
2025-10-20 14:48 ` Rob Sargent <[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