pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedApproach to Data Summary and Analysis
12+ messages / 6 participants
[nested] [flat]
* Approach to Data Summary and Analysis
@ 2014-04-14 16:27 Robert DiFalco <robert.difalco@gmail.com>
0 siblings, 3 replies; 12+ messages in thread
From: Robert DiFalco @ 2014-04-14 16:27 UTC (permalink / raw)
To: pgsql-performance; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
I have several related tables that represent a call state. Let's think of
these as phone calls to simplify things. Sometimes I need to determine the
last time a user was called, the last time a user answered a call, or the
last time a user completed a call.
The basic schema is something like this:
CREATE TABLE calls (
id BIGINT NOT NULL, // sequence generator
user_id BIGINT NOT NULL,
called TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES my_users(id) ON DELETE CASCADE
);
CREATE TABLE calls_answered (
id BIGINT NOT NULL,
answered TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
FOREIGN KEY (id) REFERENCES calls(id) ON DELETE CASCADE
);
And so on for calls_connected, calls_completed, call_errors, etc.
Occasionally I will want to know things like "When was the last time a user
answered a call" or "How many times has a user been called".
I can do these queries using a combination of MAX or COUNT. But I'm
concerned about the performance.
SELECT MAX(a.id)
FROM calls_answered a JOIN calls c ON c.id = a.id
WHERE c.user_id = ?;
Or the number of answered calls:
SELECT MAX(a.id)
FROM calls_answered a JOIN calls c ON c.id = a.id
WHERE c.user_id = ?;
Sometimes I might want to get this data for a whole bunch of users. For
example, "give me all users whose have not answered a call in the last 5
days." Or even "what percentage of users called actually answered a call."
This approach could become a performance issue. So the other option is to
create a call_summary table that is updated with triggers.
The summary table would need fields like "user_id", "last_call_id",
"call_count", "last_answered_id", "answered_count", "last_completed_id",
"last_completed_count", etc.
My only issue with a summary table is that I don't want a bunch of null
fields. For example, if the user was *called* but they have never *answered* at
call then the last_call_id and call_count fields on the summary table would
be non-NULL but the last_answer_id and answer_count fields WOULD be NULL.
But over time all fields would eventually become non-NULL.
So that leads me to a summary table for EACH call state. Each summary table
would have a user id, a ref_id, and a count -- one summary table for each
state e.g. call_summary, call_answered_summary, etc.
This approach has the down side that it creates a lot of tables and
triggers. It has the upside of being pretty efficient without having to
deal with NULL values. It's also pretty easy to reason about.
So for my question -- is the choice between these a personal preference
sort of thing or is there a right or wrong approach? Am I missing another
approach that would be better? I'm okay with SQL but I'm not expert so I'm
not sure if there is an accepted DESIGN PATTERN for this that I am missing.
Thanks!
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Approach to Data Summary and Analysis
@ 2014-04-14 21:35 Vincent Veyron <vincent.veyron@libremen.org>
parent: Robert DiFalco <robert.difalco@gmail.com>
2 siblings, 0 replies; 12+ messages in thread
From: Vincent Veyron @ 2014-04-14 21:35 UTC (permalink / raw)
To: Robert DiFalco <robert.difalco@gmail.com>; +Cc: pgsql-performance; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
On Mon, 14 Apr 2014 09:27:29 -0700
Robert DiFalco <robert.difalco@gmail.com> wrote:
> I have several related tables that represent a call state.
>
> And so on for calls_connected, calls_completed, call_errors, etc.
>
> So for my question -- is the choice between these a personal preference
> sort of thing or is there a right or wrong approach? Am I missing another
> approach that would be better?
Hi Robert,
I guess a call state is subject to change, in which case you would have to shuffle records between tables when that happens?
ISTM you should consider using only a 'calls' table, and add an 'id_call_state' field to it that references the list of possible states. This would make your queries simpler.
create table call_state(
id_call_state text PRIMARY KEY,
libelle text);
INSERT INTO call_state (id_call_state, libelle) VALUES ('calls_connected', 'Connected'), ('calls_completed', 'Completed'), ('call_errors', 'Error');
> CREATE TABLE calls (
> id BIGINT NOT NULL, // sequence generator
id_call_state INTEGER NOT NULL REFERENCES call_state,
> user_id BIGINT NOT NULL,
> called TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
>
> PRIMARY KEY (id),
> FOREIGN KEY (user_id) REFERENCES my_users(id) ON DELETE CASCADE
> );
--
Regards, Vincent Veyron
http://libremen.com/
Legal case, contract and insurance claim management software
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [GENERAL] Approach to Data Summary and Analysis
@ 2014-04-15 14:56 Chris Curvey <chris@chriscurvey.com>
parent: Robert DiFalco <robert.difalco@gmail.com>
2 siblings, 1 reply; 12+ messages in thread
From: Chris Curvey @ 2014-04-15 14:56 UTC (permalink / raw)
To: Robert DiFalco <robert.difalco@gmail.com>; +Cc: pgsql-performance; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
On Mon, Apr 14, 2014 at 12:27 PM, Robert DiFalco
<robert.difalco@gmail.com>wrote:
> I have several related tables that represent a call state. Let's think of
> these as phone calls to simplify things. Sometimes I need to determine the
> last time a user was called, the last time a user answered a call, or the
> last time a user completed a call.
>
> The basic schema is something like this:
>
> CREATE TABLE calls (
> id BIGINT NOT NULL, // sequence generator
> user_id BIGINT NOT NULL,
> called TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
>
> PRIMARY KEY (id),
> FOREIGN KEY (user_id) REFERENCES my_users(id) ON DELETE CASCADE
> );
>
> CREATE TABLE calls_answered (
> id BIGINT NOT NULL,
> answered TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
>
> PRIMARY KEY (id),
> FOREIGN KEY (id) REFERENCES calls(id) ON DELETE CASCADE
> );
>
>
> And so on for calls_connected, calls_completed, call_errors, etc.
>
> Occasionally I will want to know things like "When was the last time a
> user answered a call" or "How many times has a user been called".
>
> I can do these queries using a combination of MAX or COUNT. But I'm
> concerned about the performance.
>
> SELECT MAX(a.id)
> FROM calls_answered a JOIN calls c ON c.id = a.id
> WHERE c.user_id = ?;
>
>
> Or the number of answered calls:
>
> SELECT MAX(a.id)
> FROM calls_answered a JOIN calls c ON c.id = a.id
> WHERE c.user_id = ?;
>
>
> Sometimes I might want to get this data for a whole bunch of users. For
> example, "give me all users whose have not answered a call in the last 5
> days." Or even "what percentage of users called actually answered a call."
> This approach could become a performance issue. So the other option is to
> create a call_summary table that is updated with triggers.
>
> The summary table would need fields like "user_id", "last_call_id",
> "call_count", "last_answered_id", "answered_count", "last_completed_id",
> "last_completed_count", etc.
>
> My only issue with a summary table is that I don't want a bunch of null
> fields. For example, if the user was *called* but they have never
> *answered* at call then the last_call_id and call_count fields on the
> summary table would be non-NULL but the last_answer_id and answer_count
> fields WOULD be NULL. But over time all fields would eventually become
> non-NULL.
>
> So that leads me to a summary table for EACH call state. Each summary
> table would have a user id, a ref_id, and a count -- one summary table for
> each state e.g. call_summary, call_answered_summary, etc.
>
> This approach has the down side that it creates a lot of tables and
> triggers. It has the upside of being pretty efficient without having to
> deal with NULL values. It's also pretty easy to reason about.
>
> So for my question -- is the choice between these a personal preference
> sort of thing or is there a right or wrong approach? Am I missing another
> approach that would be better? I'm okay with SQL but I'm not expert so I'm
> not sure if there is an accepted DESIGN PATTERN for this that I am missing.
>
> Thanks!
>
>
>
My initial thought is: that design is over-normalized. The thing you are
trying to model is the call, and it has severl attributes, some of which
may be unknown or not applicable (which is what NULL is for). So my
thought would be to do something like this:
CREATE TABLE calls (
id BIGINT NOT NULL, // sequence generator
user_id BIGINT NOT NULL,
called TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
answered TIMESTAMPTZ
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES my_users(id) ON DELETE CASCADE
);
--
I asked the Internet how to train my cat, and the Internet told me to get a
dog.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Approach to Data Summary and Analysis
@ 2014-04-15 15:12 Chris Curvey <chris@chriscurvey.com>
parent: Chris Curvey <chris@chriscurvey.com>
0 siblings, 1 reply; 12+ messages in thread
From: Chris Curvey @ 2014-04-15 15:12 UTC (permalink / raw)
To: Robert DiFalco <robert.difalco@gmail.com>; +Cc: pgsql-performance; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
On Tue, Apr 15, 2014 at 10:56 AM, Chris Curvey <chris@chriscurvey.com>wrote:
> On Mon, Apr 14, 2014 at 12:27 PM, Robert DiFalco <robert.difalco@gmail.com
> > wrote:
>
>> I have several related tables that represent a call state. Let's think of
>> these as phone calls to simplify things. Sometimes I need to determine the
>> last time a user was called, the last time a user answered a call, or the
>> last time a user completed a call.
>>
>> The basic schema is something like this:
>>
>> CREATE TABLE calls (
>> id BIGINT NOT NULL, // sequence generator
>> user_id BIGINT NOT NULL,
>> called TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
>>
>> PRIMARY KEY (id),
>> FOREIGN KEY (user_id) REFERENCES my_users(id) ON DELETE CASCADE
>> );
>>
>> CREATE TABLE calls_answered (
>> id BIGINT NOT NULL,
>> answered TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
>>
>> PRIMARY KEY (id),
>> FOREIGN KEY (id) REFERENCES calls(id) ON DELETE CASCADE
>> );
>>
>>
>> And so on for calls_connected, calls_completed, call_errors, etc.
>>
>> Occasionally I will want to know things like "When was the last time a
>> user answered a call" or "How many times has a user been called".
>>
>> I can do these queries using a combination of MAX or COUNT. But I'm
>> concerned about the performance.
>>
>> SELECT MAX(a.id)
>> FROM calls_answered a JOIN calls c ON c.id = a.id
>> WHERE c.user_id = ?;
>>
>>
>> Or the number of answered calls:
>>
>> SELECT MAX(a.id)
>> FROM calls_answered a JOIN calls c ON c.id = a.id
>> WHERE c.user_id = ?;
>>
>>
>> Sometimes I might want to get this data for a whole bunch of users. For
>> example, "give me all users whose have not answered a call in the last 5
>> days." Or even "what percentage of users called actually answered a call."
>> This approach could become a performance issue. So the other option is to
>> create a call_summary table that is updated with triggers.
>>
>> The summary table would need fields like "user_id", "last_call_id",
>> "call_count", "last_answered_id", "answered_count", "last_completed_id",
>> "last_completed_count", etc.
>>
>> My only issue with a summary table is that I don't want a bunch of null
>> fields. For example, if the user was *called* but they have never
>> *answered* at call then the last_call_id and call_count fields on the
>> summary table would be non-NULL but the last_answer_id and answer_count
>> fields WOULD be NULL. But over time all fields would eventually become
>> non-NULL.
>>
>> So that leads me to a summary table for EACH call state. Each summary
>> table would have a user id, a ref_id, and a count -- one summary table for
>> each state e.g. call_summary, call_answered_summary, etc.
>>
>> This approach has the down side that it creates a lot of tables and
>> triggers. It has the upside of being pretty efficient without having to
>> deal with NULL values. It's also pretty easy to reason about.
>>
>> So for my question -- is the choice between these a personal preference
>> sort of thing or is there a right or wrong approach? Am I missing another
>> approach that would be better? I'm okay with SQL but I'm not expert so I'm
>> not sure if there is an accepted DESIGN PATTERN for this that I am missing.
>>
>> Thanks!
>>
>>
>>
> (Sorry, fat-fingered and hit "send too early"...)
CREATE TABLE calls (
id BIGINT NOT NULL, // sequence generator
user_id BIGINT NOT NULL,
called TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
answered TIMESTAMPTZ NULL,
connected TIMESTAMPTZ NULL,
completed TIMESTAMPTZ NULL,
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES my_users(id) ON DELETE CASCADE
);
Then your queries end up looking like this:
--last time john answered
SELECT MAX(a.id)
FROM calls
where answered is not null
and user_id = ?
-- users that have not answered a call in the last five days (I can think
of a few ways to interpret that phrase)
select myusers.*
from myusers
where not exists
( select *
from calls
where calls.user_id = myusers.user_id
and answered >= <five days ago>)
-- average ring time
select avg(extract ('seconds' from called - answered))
where answered is not null
--
I asked the Internet how to train my cat, and the Internet told me to get a
dog.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [GENERAL] Approach to Data Summary and Analysis
@ 2014-04-15 15:53 Robert DiFalco <robert.difalco@gmail.com>
parent: Chris Curvey <chris@chriscurvey.com>
0 siblings, 1 reply; 12+ messages in thread
From: Robert DiFalco @ 2014-04-15 15:53 UTC (permalink / raw)
To: chris@chriscurvey.com; +Cc: pgsql-performance; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Actually that was exactly the initial table design. There were more fields
because for my use case there were a lot more states and certain states
have additional data (for example when a call goes from answered to
connected it also gets the user_id of the person being connected to). So
that one table started getting a LOT of columns which starting making it
hard to reason about.
The more normalized version has a couple of things going for it. COUNT,
MIN, MAX, etc are very fast because I don't have to conditionally add null
checks. Everything is inserted so for the millions of calls that get made
the normalized schema was much more efficient for writing. It was also
easier to understand. The answer table only has calls that were answered,
the error table only has calls the resulted in an error after being
connected, etc.
I know this kind of gets into a religious area when discussing NULLs and
what level of normalization is appropriate so I don't want to spark any of
that on this thread. But only doing inserts and never doing updates or
deletes performed very well for large data sets.
That said, I could explore a compromise between the monolithic table
approach and the completely normalized set of tables approach. Thanks for
your input!
On Tue, Apr 15, 2014 at 8:12 AM, Chris Curvey <chris@chriscurvey.com> wrote:
>
>
>
> On Tue, Apr 15, 2014 at 10:56 AM, Chris Curvey <chris@chriscurvey.com>wrote:
>
>> On Mon, Apr 14, 2014 at 12:27 PM, Robert DiFalco <
>> robert.difalco@gmail.com> wrote:
>>
>>> I have several related tables that represent a call state. Let's think
>>> of these as phone calls to simplify things. Sometimes I need to determine
>>> the last time a user was called, the last time a user answered a call, or
>>> the last time a user completed a call.
>>>
>>> The basic schema is something like this:
>>>
>>> CREATE TABLE calls (
>>> id BIGINT NOT NULL, // sequence generator
>>> user_id BIGINT NOT NULL,
>>> called TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
>>>
>>> PRIMARY KEY (id),
>>> FOREIGN KEY (user_id) REFERENCES my_users(id) ON DELETE CASCADE
>>> );
>>>
>>> CREATE TABLE calls_answered (
>>> id BIGINT NOT NULL,
>>> answered TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
>>>
>>> PRIMARY KEY (id),
>>> FOREIGN KEY (id) REFERENCES calls(id) ON DELETE CASCADE
>>> );
>>>
>>>
>>> And so on for calls_connected, calls_completed, call_errors, etc.
>>>
>>> Occasionally I will want to know things like "When was the last time a
>>> user answered a call" or "How many times has a user been called".
>>>
>>> I can do these queries using a combination of MAX or COUNT. But I'm
>>> concerned about the performance.
>>>
>>> SELECT MAX(a.id)
>>> FROM calls_answered a JOIN calls c ON c.id = a.id
>>> WHERE c.user_id = ?;
>>>
>>>
>>> Or the number of answered calls:
>>>
>>> SELECT MAX(a.id)
>>> FROM calls_answered a JOIN calls c ON c.id = a.id
>>> WHERE c.user_id = ?;
>>>
>>>
>>> Sometimes I might want to get this data for a whole bunch of users. For
>>> example, "give me all users whose have not answered a call in the last 5
>>> days." Or even "what percentage of users called actually answered a call."
>>> This approach could become a performance issue. So the other option is to
>>> create a call_summary table that is updated with triggers.
>>>
>>> The summary table would need fields like "user_id", "last_call_id",
>>> "call_count", "last_answered_id", "answered_count", "last_completed_id",
>>> "last_completed_count", etc.
>>>
>>> My only issue with a summary table is that I don't want a bunch of null
>>> fields. For example, if the user was *called* but they have never
>>> *answered* at call then the last_call_id and call_count fields on the
>>> summary table would be non-NULL but the last_answer_id and answer_count
>>> fields WOULD be NULL. But over time all fields would eventually become
>>> non-NULL.
>>>
>>> So that leads me to a summary table for EACH call state. Each summary
>>> table would have a user id, a ref_id, and a count -- one summary table for
>>> each state e.g. call_summary, call_answered_summary, etc.
>>>
>>> This approach has the down side that it creates a lot of tables and
>>> triggers. It has the upside of being pretty efficient without having to
>>> deal with NULL values. It's also pretty easy to reason about.
>>>
>>> So for my question -- is the choice between these a personal preference
>>> sort of thing or is there a right or wrong approach? Am I missing another
>>> approach that would be better? I'm okay with SQL but I'm not expert so I'm
>>> not sure if there is an accepted DESIGN PATTERN for this that I am missing.
>>>
>>> Thanks!
>>>
>>>
>>>
>> (Sorry, fat-fingered and hit "send too early"...)
>
> CREATE TABLE calls (
> id BIGINT NOT NULL, // sequence generator
> user_id BIGINT NOT NULL,
> called TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
> answered TIMESTAMPTZ NULL,
> connected TIMESTAMPTZ NULL,
> completed TIMESTAMPTZ NULL,
>
>
> PRIMARY KEY (id),
> FOREIGN KEY (user_id) REFERENCES my_users(id) ON DELETE CASCADE
> );
>
> Then your queries end up looking like this:
>
> --last time john answered
> SELECT MAX(a.id)
> FROM calls
> where answered is not null
> and user_id = ?
>
> -- users that have not answered a call in the last five days (I can think
> of a few ways to interpret that phrase)
> select myusers.*
> from myusers
> where not exists
> ( select *
> from calls
> where calls.user_id = myusers.user_id
> and answered >= <five days ago>)
>
> -- average ring time
> select avg(extract ('seconds' from called - answered))
> where answered is not null
>
>
>
> --
> I asked the Internet how to train my cat, and the Internet told me to get
> a dog.
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Approach to Data Summary and Analysis
@ 2014-04-15 16:02 Rob Sargent <robjsargent@gmail.com>
parent: Robert DiFalco <robert.difalco@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Rob Sargent @ 2014-04-15 16:02 UTC (permalink / raw)
To: pgsql-general@postgresql.org
On 04/15/2014 09:53 AM, Robert DiFalco wrote:
> Actually that was exactly the initial table design. There were more
> fields because for my use case there were a lot more states and
> certain states have additional data (for example when a call goes from
> answered to connected it also gets the user_id of the person being
> connected to). So that one table started getting a LOT of columns
> which starting making it hard to reason about.
>
> The more normalized version has a couple of things going for it.
> COUNT, MIN, MAX, etc are very fast because I don't have to
> conditionally add null checks. Everything is inserted so for the
> millions of calls that get made the normalized schema was much more
> efficient for writing. It was also easier to understand. The answer
> table only has calls that were answered, the error table only has
> calls the resulted in an error after being connected, etc.
>
> I know this kind of gets into a religious area when discussing NULLs
> and what level of normalization is appropriate so I don't want to
> spark any of that on this thread. But only doing inserts and never
> doing updates or deletes performed very well for large data sets.
>
> That said, I could explore a compromise between the monolithic table
> approach and the completely normalized set of tables approach. Thanks
> for your input!
>
I wonder if the "LOT of columns" are the bits that need to be parcelled
off as specific to one condition of a call?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Approach to Data Summary and Analysis
@ 2014-04-15 22:26 Roxanne Reid-Bennett <rox@tara-lu.com>
parent: Robert DiFalco <robert.difalco@gmail.com>
2 siblings, 1 reply; 12+ messages in thread
From: Roxanne Reid-Bennett @ 2014-04-15 22:26 UTC (permalink / raw)
To: pgsql-general@postgresql.org
On 4/14/2014 12:27 PM, Robert DiFalco wrote:
> And so on for calls_connected, calls_completed, call_errors, etc.
>
> Occasionally I will want to know things like "When was the last time a
> user answered a call" or "How many times has a user been called".
> ...
> Sometimes I might want to get this data for a whole bunch of users.
> ...
> So the other option is to create a call_summary table that is updated
> with triggers.
> ...
> My only issue with a summary table is that I don't want a bunch of
> null fields.
> ...
> But over time all fields would eventually become non-NULL.
>
> So that leads me to a summary table for EACH call state. This approach
> has the down side that it creates a lot of tables and triggers. It has
> the upside of being pretty efficient without having to deal with NULL
> values. It's also pretty easy to reason about.
> ...
> So for my question -- is the choice between these a personal
> preference sort of thing or is there a right or wrong approach? Am I
> missing another approach that would be better? I'm okay with SQL but
> I'm not expert so I'm not sure if there is an accepted DESIGN PATTERN
> for this that I am missing.
>
There is no right or wrong - there is better, worse, best, and worst for
any specific scenario. In my experience, most people have time/money to
get to an 80% "better" design than all the other answers during design
and then it gets refined over time. And yes, personal experience does
play a part in how people interpret better/worse [aka religion] ;)
I didn't see anybody ask these questions - and to identify "better" -
they have to be asked.
1. How much data are you feeding into your system how fast?
this directly affects your choices on distribution, parallel
processing... writes vs updates vs triggers for copying vs all reads
[and if on bare metal - potentially where you place your logs,
indexes, core lookup tables, etc]
2. How much data are you reading out of your system - how fast?
you have given "simple" use cases (how many calls completed
within a time frame or to a number)
you have given very slightly more complex use cases (when was
the last time John answered a call)
you have given a slightly more bulky processing question of (how
many times have these users been called)
So...
a) How many users executing read queries do you have?
b) What is the expected load for simple queries (per
week/day/hour/minute - depending upon your resolution on speed)
c) What is the expected load for your mid-line complex queries
d) What is the "maximum" volume you expect a bulk query to go
after (like all users in the last 18 years, or this city's users in the
last day?) and how frequently will that kind of query be executed? How
much tolerance for delay do your users have?
e) do you have any known really complex queries that might bog
the system down?
f) How much lag time can you afford between capture and reporting?
Answers to the above define your performance requirements - which
defines the style of schema you need. Queries can be written to pull
data from any schema design - but how fast they can perform or how
easily they can be created...
Chris and Vincent both targeted a balance between writes and reads -
which adequately answers 80-85% of the usages out there. But you didn't
give us any of the above - so their recommendation (while very likely
valid) may not actually fit your case at all.
As to design patterns -
"Generally" a database schema is more normalized for an operational
system because normalization results in fewer writes/updates and lowers
the risk of corruption if a failure takes place. It also isolates
updates for any specific value to one location minimizing internally
caused data corruption.
Reporting systems are generally less normalized because writes are more
one-time and reads are where the load occurs.
Sometimes you have to use data replication to have a system that
appropriately supports both.
you have shown you are already aware of normalization.
If you weren't aware of approaches to Data Warehousing... you can review
information about how it is accomplished
- see the blogs on kimballgroup DOT com they cover a lot of high(er)
level concepts with enough specificity to be of some direct use.
[that website also covers some ideas for "Big Data" which aren't
necessarily limited to RDBMS']
Specify your performance requirements, then figure out your schema design.
FWIW I don't understand your (or any other person's) hesitancy for "lots
of" "NULL" values. They provide meaning in a number of different
ways... not the least of which is that you don't know (yet) - which is
knowledge in and of itself.
Roxanne
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Approach to Data Summary and Analysis
@ 2014-04-16 01:10 Robert DiFalco <robert.difalco@gmail.com>
parent: Roxanne Reid-Bennett <rox@tara-lu.com>
0 siblings, 2 replies; 12+ messages in thread
From: Robert DiFalco @ 2014-04-16 01:10 UTC (permalink / raw)
To: Roxanne Reid-Bennett <rox@tara-lu.com>; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
1. >500K rows per day into the calls table.
2. Very rarely. The only common query is gathering users that have not been
called "today" (along with some other qualifying criteria). More analytical
queries/reports are done for internal use and it is not essential that they
be lickity-split.
a. Usually just one connection at a time executes read queries.
b. the users not called today query will be done once a day.
c. Daily
d. All users for the last year (if you are asking about retention). We will
also rarely have to run for all time.
e. Not that I'm aware of (or seen) today.
f. For the simple queries we cannot afford latency between calls and
querying who was already called.
While I don't seem to be getting much support for it here :D my write
performance (which is most essential) has been much better since I further
normalized the tables and made it so that NULL is never used and data is
never updated (i.e. it is immutable once it is written).
As for wanting to avoid NULLs I don't really know what to say. Obviously
some times NULL's are required. For this design I don't really need them
and they make the data harder to reason about (because they are kind of
open to interpretation). They can also give you different results than you
sometimes expect (for example when looking for a non matching key, you
start having to inject some OR IS NULLs and such). Also, the absence of
null can make a lot of queries more optimal). That said, I understand where
you all are coming from with de-normalization. It's definitely the path of
the least resistance. Our instinct is to want to see all related data in a
single table when possible.
The summary table was really a separate point from whether or not people
liked my schema or not -- I mean whether I de-normalize as people are
asking or not, there would still be the question of a summary table for MAX
and COUNT queries or to not have a summary table for those. I probably made
the original question too open ended.
On Tue, Apr 15, 2014 at 3:26 PM, Roxanne Reid-Bennett <rox@tara-lu.com>wrote:
> On 4/14/2014 12:27 PM, Robert DiFalco wrote:
>
>> And so on for calls_connected, calls_completed, call_errors, etc.
>>
>> Occasionally I will want to know things like "When was the last time a
>> user answered a call" or "How many times has a user been called".
>> ...
>>
>> Sometimes I might want to get this data for a whole bunch of users.
>> ...
>>
>> So the other option is to create a call_summary table that is updated
>> with triggers.
>> ...
>>
>> My only issue with a summary table is that I don't want a bunch of null
>> fields.
>> ...
>>
>> But over time all fields would eventually become non-NULL.
>>
>> So that leads me to a summary table for EACH call state. This approach
>> has the down side that it creates a lot of tables and triggers. It has the
>> upside of being pretty efficient without having to deal with NULL values.
>> It's also pretty easy to reason about.
>> ...
>>
>> So for my question -- is the choice between these a personal preference
>> sort of thing or is there a right or wrong approach? Am I missing another
>> approach that would be better? I'm okay with SQL but I'm not expert so I'm
>> not sure if there is an accepted DESIGN PATTERN for this that I am missing.
>>
>> There is no right or wrong - there is better, worse, best, and worst for
> any specific scenario. In my experience, most people have time/money to
> get to an 80% "better" design than all the other answers during design and
> then it gets refined over time. And yes, personal experience does play a
> part in how people interpret better/worse [aka religion] ;)
>
> I didn't see anybody ask these questions - and to identify "better" -
> they have to be asked.
> 1. How much data are you feeding into your system how fast?
> this directly affects your choices on distribution, parallel
> processing... writes vs updates vs triggers for copying vs all reads
> [and if on bare metal - potentially where you place your logs,
> indexes, core lookup tables, etc]
> 2. How much data are you reading out of your system - how fast?
> you have given "simple" use cases (how many calls completed within
> a time frame or to a number)
> you have given very slightly more complex use cases (when was the
> last time John answered a call)
> you have given a slightly more bulky processing question of (how
> many times have these users been called)
> So...
> a) How many users executing read queries do you have?
> b) What is the expected load for simple queries (per
> week/day/hour/minute - depending upon your resolution on speed)
> c) What is the expected load for your mid-line complex queries
> d) What is the "maximum" volume you expect a bulk query to go after
> (like all users in the last 18 years, or this city's users in the last
> day?) and how frequently will that kind of query be executed? How much
> tolerance for delay do your users have?
> e) do you have any known really complex queries that might bog the
> system down?
> f) How much lag time can you afford between capture and reporting?
>
> Answers to the above define your performance requirements - which defines
> the style of schema you need. Queries can be written to pull data from any
> schema design - but how fast they can perform or how easily they can be
> created...
>
> Chris and Vincent both targeted a balance between writes and reads - which
> adequately answers 80-85% of the usages out there. But you didn't give us
> any of the above - so their recommendation (while very likely valid) may
> not actually fit your case at all.
>
> As to design patterns -
> "Generally" a database schema is more normalized for an operational system
> because normalization results in fewer writes/updates and lowers the risk
> of corruption if a failure takes place. It also isolates updates for any
> specific value to one location minimizing internally caused data corruption.
> Reporting systems are generally less normalized because writes are more
> one-time and reads are where the load occurs.
> Sometimes you have to use data replication to have a system that
> appropriately supports both.
>
> you have shown you are already aware of normalization.
> If you weren't aware of approaches to Data Warehousing... you can review
> information about how it is accomplished
> - see the blogs on kimballgroup DOT com they cover a lot of high(er)
> level concepts with enough specificity to be of some direct use.
> [that website also covers some ideas for "Big Data" which aren't
> necessarily limited to RDBMS']
>
> Specify your performance requirements, then figure out your schema design.
>
> FWIW I don't understand your (or any other person's) hesitancy for "lots
> of" "NULL" values. They provide meaning in a number of different ways...
> not the least of which is that you don't know (yet) - which is knowledge in
> and of itself.
>
> Roxanne
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Approach to Data Summary and Analysis
@ 2014-04-16 01:27 Gavin Flower <GavinFlower@archidevsys.co.nz>
parent: Robert DiFalco <robert.difalco@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Gavin Flower @ 2014-04-16 01:27 UTC (permalink / raw)
To: Robert DiFalco <robert.difalco@gmail.com>; Roxanne Reid-Bennett <rox@tara-lu.com>; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
On 16/04/14 13:10, Robert DiFalco wrote:
> 1. >500K rows per day into the calls table.
> 2. Very rarely. The only common query is gathering users that have not
> been called "today" (along with some other qualifying criteria). More
> analytical queries/reports are done for internal use and it is not
> essential that they be lickity-split.
> a. Usually just one connection at a time executes read queries.
> b. the users not called today query will be done once a day.
> c. Daily
> d. All users for the last year (if you are asking about retention). We
> will also rarely have to run for all time.
> e. Not that I'm aware of (or seen) today.
> f. For the simple queries we cannot afford latency between calls and
> querying who was already called.
>
> While I don't seem to be getting much support for it here :D my write
> performance (which is most essential) has been much better since I
> further normalized the tables and made it so that NULL is never used
> and data is never updated (i.e. it is immutable once it is written).
>
> As for wanting to avoid NULLs I don't really know what to say.
> Obviously some times NULL's are required. For this design I don't
> really need them and they make the data harder to reason about
> (because they are kind of open to interpretation). They can also give
> you different results than you sometimes expect (for example when
> looking for a non matching key, you start having to inject some OR IS
> NULLs and such). Also, the absence of null can make a lot of queries
> more optimal). That said, I understand where you all are coming from
> with de-normalization. It's definitely the path of the least
> resistance. Our instinct is to want to see all related data in a
> single table when possible.
>
> The summary table was really a separate point from whether or not
> people liked my schema or not -- I mean whether I de-normalize as
> people are asking or not, there would still be the question of a
> summary table for MAX and COUNT queries or to not have a summary table
> for those. I probably made the original question too open ended.
>
>
> On Tue, Apr 15, 2014 at 3:26 PM, Roxanne Reid-Bennett <rox@tara-lu.com
> <mailto:rox@tara-lu.com>> wrote:
>
> On 4/14/2014 12:27 PM, Robert DiFalco wrote:
>
> And so on for calls_connected, calls_completed, call_errors, etc.
>
> Occasionally I will want to know things like "When was the
> last time a user answered a call" or "How many times has a
> user been called".
> ...
>
> Sometimes I might want to get this data for a whole bunch of
> users.
> ...
>
> So the other option is to create a call_summary table that is
> updated with triggers.
> ...
>
> My only issue with a summary table is that I don't want a
> bunch of null fields.
> ...
>
> But over time all fields would eventually become non-NULL.
>
> So that leads me to a summary table for EACH call state. This
> approach has the down side that it creates a lot of tables and
> triggers. It has the upside of being pretty efficient without
> having to deal with NULL values. It's also pretty easy to
> reason about.
> ...
>
> So for my question -- is the choice between these a personal
> preference sort of thing or is there a right or wrong
> approach? Am I missing another approach that would be better?
> I'm okay with SQL but I'm not expert so I'm not sure if there
> is an accepted DESIGN PATTERN for this that I am missing.
>
> There is no right or wrong - there is better, worse, best, and
> worst for any specific scenario. In my experience, most people
> have time/money to get to an 80% "better" design than all the
> other answers during design and then it gets refined over time.
> And yes, personal experience does play a part in how people
> interpret better/worse [aka religion] ;)
>
> I didn't see anybody ask these questions - and to identify
> "better" - they have to be asked.
> 1. How much data are you feeding into your system how fast?
> this directly affects your choices on distribution,
> parallel processing... writes vs updates vs triggers for copying
> vs all reads
> [and if on bare metal - potentially where you place your
> logs, indexes, core lookup tables, etc]
> 2. How much data are you reading out of your system - how fast?
> you have given "simple" use cases (how many calls completed
> within a time frame or to a number)
> you have given very slightly more complex use cases (when
> was the last time John answered a call)
> you have given a slightly more bulky processing question of
> (how many times have these users been called)
> So...
> a) How many users executing read queries do you have?
> b) What is the expected load for simple queries (per
> week/day/hour/minute - depending upon your resolution on speed)
> c) What is the expected load for your mid-line complex queries
> d) What is the "maximum" volume you expect a bulk query to
> go after (like all users in the last 18 years, or this city's
> users in the last day?) and how frequently will that kind of
> query be executed? How much tolerance for delay do your users have?
> e) do you have any known really complex queries that might
> bog the system down?
> f) How much lag time can you afford between capture and
> reporting?
>
> Answers to the above define your performance requirements - which
> defines the style of schema you need. Queries can be written to
> pull data from any schema design - but how fast they can perform
> or how easily they can be created...
>
> Chris and Vincent both targeted a balance between writes and reads
> - which adequately answers 80-85% of the usages out there. But
> you didn't give us any of the above - so their recommendation
> (while very likely valid) may not actually fit your case at all.
>
> As to design patterns -
> "Generally" a database schema is more normalized for an
> operational system because normalization results in fewer
> writes/updates and lowers the risk of corruption if a failure
> takes place. It also isolates updates for any specific value to
> one location minimizing internally caused data corruption.
> Reporting systems are generally less normalized because writes are
> more one-time and reads are where the load occurs.
> Sometimes you have to use data replication to have a system that
> appropriately supports both.
>
> you have shown you are already aware of normalization.
> If you weren't aware of approaches to Data Warehousing... you can
> review information about how it is accomplished
> - see the blogs on kimballgroup DOT com they cover a lot of
> high(er) level concepts with enough specificity to be of some
> direct use.
> [that website also covers some ideas for "Big Data" which aren't
> necessarily limited to RDBMS']
>
> Specify your performance requirements, then figure out your schema
> design.
>
> FWIW I don't understand your (or any other person's) hesitancy for
> "lots of" "NULL" values. They provide meaning in a number of
> different ways... not the least of which is that you don't know
> (yet) - which is knowledge in and of itself.
>
> Roxanne
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org
> <mailto:pgsql-general@postgresql.org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
Have you considered partial indexes? Using the /WHERE //predicate/
option of /CREATE INDEX/.
This can be useful if you often look for things that are often only a
small subset of keys. For example a partial index on sex would useful
for nurses, only indexing those that are male as they are in a very
small minority.
Cheers,
Gavin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Approach to Data Summary and Analysis
@ 2014-04-16 15:42 Roxanne Reid-Bennett <rox@tara-lu.com>
parent: Robert DiFalco <robert.difalco@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Roxanne Reid-Bennett @ 2014-04-16 15:42 UTC (permalink / raw)
To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
On 4/15/2014 9:10 PM, Robert DiFalco wrote:
> 1. >500K rows per day into the calls table.
> 2. Very rarely. The only common query is gathering users that have not
> been called "today" (along with some other qualifying criteria). More
> analytical queries/reports are done for internal use and it is not
> essential that they be lickity-split.
> a. Usually just one connection at a time executes read queries.
> b. the users not called today query will be done once a day.
> c. Daily
> d. All users for the last year (if you are asking about retention). We
> will also rarely have to run for all time.
> e. Not that I'm aware of (or seen) today.
> f. For the simple queries we cannot afford latency between calls and
> querying who was already called.
>
> While I don't seem to be getting much support for it here :D my write
> performance (which is most essential) has been much better since I
> further normalized the tables and made it so that NULL is never used
> and data is never updated (i.e. it is immutable once it is written).
Based on the above you are primarily capturing data and feeding back
essentially one easy to find result set [who has NOT been successfully
called] on an ongoing single threaded basis [once per day?]. So you are
absolutely correct - tune for writing speed.
> The summary table was really a separate point from whether or not
> people liked my schema or not -- I mean whether I de-normalize as
> people are asking or not, there would still be the question of a
> summary table for MAX and COUNT queries or to not have a summary table
> for those. I probably made the original question too open ended.
>
Do you know your answer?
you said : "Occasionally I will want to know things like "
you answered to frequency on queries as "the users not called today
query will be done once a day." as was c) [I'm assuming once?]
and d) appears to be "ad-hoc" and you said your users can deal with
latency in response for those.
So finding Min/Max/Count quickly really *don't* matter for tuning.
So the only reason I can see to add a summary table is to ... simplify
maintenance [note I did NOT say "development"] and then only IF it
doesn't impact the write speeds beyond an acceptable level. Proper
internal / external documentation can mitigate maintenance nightmares.
If your developer(s) can't figure out how to get the data they need from
the schema - then give them the queries to run. [you are likely better
at tuning those anyway]
Last consideration - business consumption of data does change over
time. Disk space is cheap [but getting and keeping speed sometimes
isn't]. You might consider including ongoing partial archival of the
operational data during slow usage (write) periods.
Roxanne
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Approach to Data Summary and Analysis
@ 2014-04-16 18:40 Robert DiFalco <robert.difalco@gmail.com>
parent: Roxanne Reid-Bennett <rox@tara-lu.com>
0 siblings, 1 reply; 12+ messages in thread
From: Robert DiFalco @ 2014-04-16 18:40 UTC (permalink / raw)
To: Roxanne Reid-Bennett <rox@tara-lu.com>; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Thanks Roxanne, I suppose when it comes down to it -- for the current use
cases and data size -- my only concern is the "calling" query that will
need to use max to determine if a user has already had a call today. For a
large user set, for each user I would either have to MAX on the answered
timestamp to compare it against today or do an exist query to see if any
timestamp for that user is greater or equal than "today".
But I suppose I just need to construct a huge dataset and see. I was
thinking by keeping a summary so I always knew the last answer or call time
for each user that I could mitigate this becoming an issue. Over time a
single user could have answered a call thousands of times. So that would
make a "<=" timestamp query be just # of users instead of # of users X 1000
(or however many calls they have answered over the non-archived time
period).
On Wed, Apr 16, 2014 at 8:42 AM, Roxanne Reid-Bennett <rox@tara-lu.com>wrote:
> On 4/15/2014 9:10 PM, Robert DiFalco wrote:
>
>> 1. >500K rows per day into the calls table.
>> 2. Very rarely. The only common query is gathering users that have not
>> been called "today" (along with some other qualifying criteria). More
>> analytical queries/reports are done for internal use and it is not
>> essential that they be lickity-split.
>> a. Usually just one connection at a time executes read queries.
>> b. the users not called today query will be done once a day.
>> c. Daily
>> d. All users for the last year (if you are asking about retention). We
>> will also rarely have to run for all time.
>> e. Not that I'm aware of (or seen) today.
>> f. For the simple queries we cannot afford latency between calls and
>> querying who was already called.
>>
>> While I don't seem to be getting much support for it here :D my write
>> performance (which is most essential) has been much better since I further
>> normalized the tables and made it so that NULL is never used and data is
>> never updated (i.e. it is immutable once it is written).
>>
>
> Based on the above you are primarily capturing data and feeding back
> essentially one easy to find result set [who has NOT been successfully
> called] on an ongoing single threaded basis [once per day?]. So you are
> absolutely correct - tune for writing speed.
>
>
> The summary table was really a separate point from whether or not people
>> liked my schema or not -- I mean whether I de-normalize as people are
>> asking or not, there would still be the question of a summary table for MAX
>> and COUNT queries or to not have a summary table for those. I probably made
>> the original question too open ended.
>>
>> Do you know your answer?
> you said : "Occasionally I will want to know things like "
> you answered to frequency on queries as "the users not called today query
> will be done once a day." as was c) [I'm assuming once?]
> and d) appears to be "ad-hoc" and you said your users can deal with
> latency in response for those.
>
> So finding Min/Max/Count quickly really *don't* matter for tuning.
>
> So the only reason I can see to add a summary table is to ... simplify
> maintenance [note I did NOT say "development"] and then only IF it doesn't
> impact the write speeds beyond an acceptable level. Proper internal /
> external documentation can mitigate maintenance nightmares. If your
> developer(s) can't figure out how to get the data they need from the schema
> - then give them the queries to run. [you are likely better at tuning those
> anyway]
>
> Last consideration - business consumption of data does change over time.
> Disk space is cheap [but getting and keeping speed sometimes isn't]. You
> might consider including ongoing partial archival of the operational data
> during slow usage (write) periods.
>
>
> Roxanne
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Approach to Data Summary and Analysis
@ 2014-04-18 05:46 Roxanne Reid-Bennett <rox@tara-lu.com>
parent: Robert DiFalco <robert.difalco@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Roxanne Reid-Bennett @ 2014-04-18 05:46 UTC (permalink / raw)
To: ; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
On 4/16/2014 2:40 PM, Robert DiFalco wrote:
> Thanks Roxanne, I suppose when it comes down to it -- for the current
> use cases and data size -- my only concern is the "calling" query that
> will need to use max to determine if a user has already had a call
> today. For a large user set, for each user I would either have to MAX
> on the answered timestamp to compare it against today or do an exist
> query to see if any timestamp for that user is greater or equal than
> "today".
I didn't go back to look at your original schema- but.. if your 500K
records are coming in time ordered... You may be able to track "max" as
an attribute on an "SCD" based on the caller/callee table [or the
caller/ee table itself if that table is only used by your app] with an
update from a post-insert trigger on the appropriate table. Even if they
aren't time ordered, you add the overhead of a single comparative in the
trigger. Downside is that you fire a trigger and an update for every
insert. [or just an update depending on what is driving your load of the
500K records]
Again - the proof on "value" of this overhead is a comparison of the
cost for the updates vs the cost on the query to find max() I suspect
your once a day query can afford all sorts of other optimizations that
are "better" than a trigger fired on every insert. [such as the
function index - that was already mentioned] I really suspect you just
don't have enough load on the query side (complex queries * # of users)
to justify the extra load on the write side (+1 trigger, +1 update /
insert) to avoid a (potentially) heavy query load 1x/day.
Another option... if only worried about "today".. then keep only
"today's" data in your query table, and migrate historical data nightly
to a pseudo archive table for those "every once in a while" questions.
I haven't played with table inheritance in Postgres - but that's a
capability I might look at if I were doing a pseudo archive table.
Roxanne
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2014-04-18 05:46 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-04-14 16:27 Approach to Data Summary and Analysis Robert DiFalco <robert.difalco@gmail.com>
2014-04-14 21:35 ` Vincent Veyron <vincent.veyron@libremen.org>
2014-04-15 14:56 ` Chris Curvey <chris@chriscurvey.com>
2014-04-15 15:12 ` Chris Curvey <chris@chriscurvey.com>
2014-04-15 15:53 ` Robert DiFalco <robert.difalco@gmail.com>
2014-04-15 16:02 ` Rob Sargent <robjsargent@gmail.com>
2014-04-15 22:26 ` Roxanne Reid-Bennett <rox@tara-lu.com>
2014-04-16 01:10 ` Robert DiFalco <robert.difalco@gmail.com>
2014-04-16 01:27 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
2014-04-16 15:42 ` Roxanne Reid-Bennett <rox@tara-lu.com>
2014-04-16 18:40 ` Robert DiFalco <robert.difalco@gmail.com>
2014-04-18 05:46 ` Roxanne Reid-Bennett <rox@tara-lu.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox