Обсуждение: Need help to organize database
Hi Sorry for my English.. I need to organize database structure for saving statistic data for objects. I have about 24 * 31 * 4 fields (4 month, 31 days, 24 hours) of data for one object. Each field contain 8 numbers (N in general). So: object1 -> data -> field1, field2,... object2 -> data -> field1, field2,... ... How can I store this data in postgres database? For example it may be 24 * 31 * 4 * 8 fields and 1 row per object in table - but it is not good idea, of course. Or may be 8 fields and 24 * 31 * 4 rows per object in table, but table looked very big... Or may be it's not good idea to use postgres for this purpose? -- The more we disagree, the more chance there is that at least one of us is right.
On Mon, Dec 20, 2004 at 12:13:31 +0000, "Vladimir S. Petukhov" <vladimir@sycore.org> wrote: > Hi > Sorry for my English.. > > I need to organize database structure for saving statistic data for objects. I > have about 24 * 31 * 4 fields (4 month, 31 days, 24 hours) of data for one > object. Each field contain 8 numbers (N in general). So: > object1 -> data -> field1, field2,... > object2 -> data -> field1, field2,... > ... > How can I store this data in postgres database? > > For example it may be 24 * 31 * 4 * 8 fields and 1 row per object in table - > but it is not good idea, of course. > > Or may be 8 fields and 24 * 31 * 4 rows per object in table, but table looked > very big... You want to store rows with an object id, a field or fields storing the hour and the 8 object fields (assuming these fields shouldn't also be one per row - but without any more knowledge about them I can't say). You should put a primary key constraint on the object id and the hour field(s). > > Or may be it's not good idea to use postgres for this purpose? Without seeing what you are going to do with the data it is hard to say if using a dbms is overkill or not.
Ok, this is a real example:
CREATE TABLE account (
val1 BIGINT NULL,
val2 BIGINT NULL,
...
daypos SMALLINT NULL, -- Day position
hourpos SMALLINT NULL, -- Hour position
id INT NULL -- Link to the object
);
On Tuesday 21 December 2004 14:38, Bruno Wolff III wrote:
> On Mon, Dec 20, 2004 at 12:13:31 +0000,
>
> "Vladimir S. Petukhov" <vladimir@sycore.org> wrote:
> > Hi
> > Sorry for my English..
> >
> > I need to organize database structure for saving statistic data for
> > objects. I have about 24 * 31 * 4 fields (4 month, 31 days, 24 hours) of
> > data for one object. Each field contain 8 numbers (N in general). So:
> > object1 -> data -> field1, field2,...
> > object2 -> data -> field1, field2,...
> > ...
> > How can I store this data in postgres database?
> >
> > For example it may be 24 * 31 * 4 * 8 fields and 1 row per object in
> > table - but it is not good idea, of course.
> >
> > Or may be 8 fields and 24 * 31 * 4 rows per object in table, but table
> > looked very big...
>
> You want to store rows with an object id, a field or fields storing the
> hour and the 8 object fields (assuming these fields shouldn't also
> be one per row - but without any more knowledge about them I can't say).
> You should put a primary key constraint on the object id and the hour
> field(s).
>
> > Or may be it's not good idea to use postgres for this purpose?
>
> Without seeing what you are going to do with the data it is hard to say
> if using a dbms is overkill or not.
--
Riches cover a multitude of woes.
-- Menander
Now playing: track08.mp3
AutoGenerated by fortune & xmms...
Instead of having separate fields for day, hour, ... - why not use timestamp values? On Dec 21, 2004, at 3:47 PM, Vladimir S. Petukhov wrote: > Ok, this is a real example: > > CREATE TABLE account ( > val1 BIGINT NULL, > val2 BIGINT NULL, > ... > > daypos SMALLINT NULL, -- Day position > hourpos SMALLINT NULL, -- Hour position > id INT NULL -- Link to the object > ); > > > > On Tuesday 21 December 2004 14:38, Bruno Wolff III wrote: >> On Mon, Dec 20, 2004 at 12:13:31 +0000, >> >> "Vladimir S. Petukhov" <vladimir@sycore.org> wrote: >>> Hi >>> Sorry for my English.. >>> >>> I need to organize database structure for saving statistic data for >>> objects. I have about 24 * 31 * 4 fields (4 month, 31 days, 24 >>> hours) of >>> data for one object. Each field contain 8 numbers (N in general). So: >>> object1 -> data -> field1, field2,... >>> object2 -> data -> field1, field2,... >>> ... >>> How can I store this data in postgres database? >>> >>> For example it may be 24 * 31 * 4 * 8 fields and 1 row per object in >>> table - but it is not good idea, of course. >>> >>> Or may be 8 fields and 24 * 31 * 4 rows per object in table, but >>> table >>> looked very big... >> >> You want to store rows with an object id, a field or fields storing >> the >> hour and the 8 object fields (assuming these fields shouldn't also >> be one per row - but without any more knowledge about them I can't >> say). >> You should put a primary key constraint on the object id and the hour >> field(s). >> >>> Or may be it's not good idea to use postgres for this purpose? >> >> Without seeing what you are going to do with the data it is hard to >> say >> if using a dbms is overkill or not. > > -- > Riches cover a multitude of woes. > -- Menander > > Now playing: track08.mp3 > AutoGenerated by fortune & xmms... > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > ----------------------------------------------------------- Frank D. Engel, Jr. <fde101@fjrhome.net> $ ln -s /usr/share/kjvbible /usr/manual $ true | cat /usr/manual | grep "John 3:16" John 3:16 For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life. $ ___________________________________________________________ $0 Web Hosting with up to 120MB web space, 1000 MB Transfer 10 Personalized POP and Web E-mail Accounts, and much more. Signup at www.doteasy.com
On Tue, Dec 21, 2004 at 20:47:31 +0000, "Vladimir S. Petukhov" <vladimir@sycore.org> wrote: > Ok, this is a real example: > > CREATE TABLE account ( > val1 BIGINT NULL, > val2 BIGINT NULL, > ... > > daypos SMALLINT NULL, -- Day position > hourpos SMALLINT NULL, -- Hour position > id INT NULL -- Link to the object > ); That approach is reasonable but depending on what the val* columns mean you might each of those as a separate role. You didn't add a lot of information, but that they appear to be the same type suggests that you might want one row per value. But without knowing what they mean it is hard to say.
On Tuesday 21 December 2004 21:21, Bruno Wolff III wrote:
> On Tue, Dec 21, 2004 at 20:47:31 +0000,
>
> "Vladimir S. Petukhov" <vladimir@sycore.org> wrote:
> > Ok, this is a real example:
> >
> > CREATE TABLE account (
> > val1 BIGINT NULL,
> > val2 BIGINT NULL,
> > ...
> >
> > daypos SMALLINT NULL, -- Day position
> > hourpos SMALLINT NULL, -- Hour position
> > id INT NULL -- Link to the object
> > );
>
> That approach is reasonable but depending on what the val* columns mean
> you might each of those as a separate role. You didn't add a lot of
> information, but that they appear to be the same type suggests that you
> might want one row per value. But without knowing what they mean it is
> hard to say.
OK, i want to store ststistic information, ingoing/outgoing traffic,
ingoing/outgoing errors(val1-val4) for example...
--
Man's unique agony as a species consists in his perpetual conflict between
the desire to stand out and the need to blend in.
-- Sydney J. Harris
Now playing: (Воскресение) - Музыкант.mp3
AutoGenerated by fortune & xmms...
On Wed, Dec 22, 2004 at 00:16:06 +0000, "Vladimir S. Petukhov" <vladimir@sycore.org> wrote: > On Tuesday 21 December 2004 21:21, Bruno Wolff III wrote: > > On Tue, Dec 21, 2004 at 20:47:31 +0000, > > > > "Vladimir S. Petukhov" <vladimir@sycore.org> wrote: > > > Ok, this is a real example: > > > > > > CREATE TABLE account ( > > > val1 BIGINT NULL, > > > val2 BIGINT NULL, > > > ... > > > > > > daypos SMALLINT NULL, -- Day position > > > hourpos SMALLINT NULL, -- Hour position > > > id INT NULL -- Link to the object > > > ); > > > > That approach is reasonable but depending on what the val* columns mean > > you might each of those as a separate role. You didn't add a lot of > > information, but that they appear to be the same type suggests that you > > might want one row per value. But without knowing what they mean it is > > hard to say. > > OK, i want to store ststistic information, ingoing/outgoing traffic, > ingoing/outgoing errors(val1-val4) for example... Those sound like different domains then for each column. So you probably do want to keep the 8 values in one row. I also noticed that you marked a lot of these values as NULL. I think that daypos, hourpos and id form the primary key and you probably don't want to allow NULLs for these.
On Tuesday 21 December 2004 22:00, Bruno Wolff III wrote: > On Wed, Dec 22, 2004 at 00:16:06 +0000, > > "Vladimir S. Petukhov" <vladimir@sycore.org> wrote: > > On Tuesday 21 December 2004 21:21, Bruno Wolff III wrote: > > > On Tue, Dec 21, 2004 at 20:47:31 +0000, > > > > > > "Vladimir S. Petukhov" <vladimir@sycore.org> wrote: > > > > Ok, this is a real example: > > > > > > > > CREATE TABLE account ( > > > > val1 BIGINT NULL, > > > > val2 BIGINT NULL, > > > > ... > > > > > > > > daypos SMALLINT NULL, -- Day position > > > > hourpos SMALLINT NULL, -- Hour position > > > > id INT NULL -- Link to the object > > > > ); > > > > > > That approach is reasonable but depending on what the val* columns mean > > > you might each of those as a separate role. You didn't add a lot of > > > information, but that they appear to be the same type suggests that you > > > might want one row per value. But without knowing what they mean it is > > > hard to say. > > > > OK, i want to store ststistic information, ingoing/outgoing traffic, > > ingoing/outgoing errors(val1-val4) for example... > > Those sound like different domains then for each column. So you probably do > want to keep the 8 values in one row. > I also noticed that you marked a lot of these values as NULL. I think that > daypos, hourpos and id form the primary key and you probably don't want to > allow NULLs for these. Yes, of course, this is example only. But relation between tables is not important now... I whant to ask - is it a good idea to store 1 time's data (value1-4) per row in 24*31 rows? May be it is better and quicker to store, for example, 2 time's data per row (value1-4, day 1, value1-4, day 2) or other structure?
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Why would it be? The only real advantage to what you are suggesting would be slightly reduced disk space usage, and just maybe *very* slightly improved performance on low-memory servers, or servers with very slow disks, but I find it unlikely that this would really be significant enough to be worthwhile. There are greater advantages to not doing something like that: easier coding of your front-end software, a layout which is a bit more comprehensible, making future maintenance of the design and code much easier, etc. Also, given the amount of data you are talking about, and assuming that you are inserting all of this data in one big lump, you may wish to VACUUM FULL after doing your INSERTs (not after each one, of course -- after doing all of the INSERTs, or after doing a big chunk of them. If data is inserted incrementally over a period of time, then just do the VACUUM ANALYZE every so often during that time, and you shouldn't have a problem). On Dec 21, 2004, at 8:24 PM, Vladimir S. Petukhov wrote: >> > Yes, of course, this is example only. > But relation between tables is not important now... > I whant to ask - is it a good idea to store 1 time's data (value1-4) > per row > in 24*31 rows? May be it is better and quicker to store, for example, 2 > time's data per row (value1-4, day 1, value1-4, day 2) or other > structure? - ----------------------------------------------------------- Frank D. Engel, Jr. <fde101@fjrhome.net> $ ln -s /usr/share/kjvbible /usr/manual $ true | cat /usr/manual | grep "John 3:16" John 3:16 For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life. $ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (Darwin) iD8DBQFByKie7aqtWrR9cZoRAhkNAJ9vQPL3Mdi4Z6xSi5S8y3Aqih/aXwCghZyi sNOrQnGMA1kCXxqbhNjPQjs= =BqP5 -----END PGP SIGNATURE----- ___________________________________________________________ $0 Web Hosting with up to 120MB web space, 1000 MB Transfer 10 Personalized POP and Web E-mail Accounts, and much more. Signup at www.doteasy.com
I absolutly agree with you, thank. On Tuesday 21 December 2004 22:50, you wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Why would it be? > > The only real advantage to what you are suggesting would be slightly > reduced disk space usage, and just maybe *very* slightly improved > performance on low-memory servers, or servers with very slow disks, but > I find it unlikely that this would really be significant enough to be > worthwhile. > > There are greater advantages to not doing something like that: easier > coding of your front-end software, a layout which is a bit more > comprehensible, making future maintenance of the design and code much > easier, etc. > > > Also, given the amount of data you are talking about, and assuming that > you are inserting all of this data in one big lump, you may wish to > VACUUM FULL after doing your INSERTs (not after each one, of course -- > after doing all of the INSERTs, or after doing a big chunk of them. If > data is inserted incrementally over a period of time, then just do the > VACUUM ANALYZE every so often during that time, and you shouldn't have > a problem). > > On Dec 21, 2004, at 8:24 PM, Vladimir S. Petukhov wrote: > > Yes, of course, this is example only. > > But relation between tables is not important now... > > I whant to ask - is it a good idea to store 1 time's data (value1-4) > > per row > > in 24*31 rows? May be it is better and quicker to store, for example, 2 > > time's data per row (value1-4, day 1, value1-4, day 2) or other > > structure? > > - ----------------------------------------------------------- > Frank D. Engel, Jr. <fde101@fjrhome.net> > > $ ln -s /usr/share/kjvbible /usr/manual > $ true | cat /usr/manual | grep "John 3:16" > John 3:16 For God so loved the world, that he gave his only begotten > Son, that whosoever believeth in him should not perish, but have > everlasting life. > $ > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.2.4 (Darwin) > > iD8DBQFByKie7aqtWrR9cZoRAhkNAJ9vQPL3Mdi4Z6xSi5S8y3Aqih/aXwCghZyi > sNOrQnGMA1kCXxqbhNjPQjs= > =BqP5 > -----END PGP SIGNATURE----- > > > > ___________________________________________________________ > $0 Web Hosting with up to 120MB web space, 1000 MB Transfer > 10 Personalized POP and Web E-mail Accounts, and much more. > Signup at www.doteasy.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
On Wed, Dec 22, 2004 at 01:24:57 +0000, "Vladimir S. Petukhov" <vladimir@sycore.org> wrote: > Yes, of course, this is example only. > But relation between tables is not important now... It is important for design. You should use a normallized design initially and consider denormalized designs if you have peformance problems. > I whant to ask - is it a good idea to store 1 time's data (value1-4) per row > in 24*31 rows? May be it is better and quicker to store, for example, 2 > time's data per row (value1-4, day 1, value1-4, day 2) or other structure? The normalized design is one set of values (for a particular day and time) per row. This will make querying the data easier. As far as what optimizations might be a good idea when you have problems, we can't tell you, because you haven't told us what typical queries look like. It still may be that postgres is overkill for your purposes and something more lightweight like Berkeley DB might be better for you.
On Tue, Dec 21, 2004 at 17:50:06 -0500, "Frank D. Engel, Jr." <fde101@fjrhome.net> wrote: > > Also, given the amount of data you are talking about, and assuming that > you are inserting all of this data in one big lump, you may wish to > VACUUM FULL after doing your INSERTs (not after each one, of course -- > after doing all of the INSERTs, or after doing a big chunk of them. If > data is inserted incrementally over a period of time, then just do the > VACUUM ANALYZE every so often during that time, and you shouldn't have > a problem). You only need to vacuum after updates or deletes. After mass inserts you just want to run ANALYSE.