Re: pgsql and large tables - Mailing list pgsql-general

The way I would do this is to spend some time designing the data model which you find optimal, when this is done you
createit in a database, and test it for a little while. Then you create views which look like your old database. This
waythe interface with your application will not be broken. Finally you test the new interface (make sure it actually
worksas it is supposed to) import the old data, and continue running it. Then you make your own little plan, as to how,
whichand when you are going to update the other scripts. This will give you many advantages: 

1. You will learn a lot more about postgres (views, rules etc.)
2. You will be able to get many of the advantages much quicker
3. You will get all the advantages over time and you can focus on the most important one's first.

Regards,

Aasmund.

On Fri, 16 Nov 2001 10:01:16 -0500 (EST), Francisco Reyes <lists@natserv.com> wrote:
> On Thu, 15 Nov 2001, Gurupartap Davis wrote:
>
>
> The basics of normalization are not all that complex. Most of the time the
> biggest issue is truly understanding your data and how you will need to
> access it.
>
>
> I could try to help you, but I would need the original tables with every
> field explained. Then your suggested new design.
>
>
> If they can be derived AND you will never need to search on them, then
> don't store them at all.
>
>
> It is not only the space saving, but your queries will run faster.
>
>
> Those could possibly be on a separate table then.
>
>
> You could do something in between an optimal design and something which
> would be relatively easy to do. Just explain to your employer that these
> changes can reduce space consumption and speed up your queries. The
> alternative is getting faster hardware and more memory... and even then
> queries may not be as fast as they could be just by re-designing your
> database.
>
>
> Again talk to your employer. This change will take place once and your
> queries and the savings on time and space will be a long term process.
>
>
> If you are not paid by the hour then it may be just an issue of you
> getting some help over the net and doing it yourself.
> You may want to look into general DB forums, newsgroups, etc... where
> people with more experience on design can help you.
>
> Doing a non formal design is fairly simple, it is just a matter of knowing
> your data. If you take the time to do what I suggested and explain your
> existing tables field by fiend, taking in consideration we may not know
> the terms, then people on this forum may be able to help you.
>
> You don't need to go into too many details. Something like
> zhr hour of ???. Usually blank/populated, etc..
> zday day of ???. Usually blank/populated, etc..
> model a description/name??
> temp temperature at time/date???
>
> You could also through in 20 records so we see how things are distributed.
> In other words which values are often repeated and may be better off on a
> separate table. Also which fields are usually blank so perhaps you may
> consider putting them on a separate table for the space savings.
>
>
> How about letting the data go into ASCII files and then import them at set
> intervals. In particular you want to have time to do your vacuum analyze
> at least once per day.
>
>
> Is the model run on the database?
>
>
>
> Actually in your case I would recommend to do a vacuum analyze after each
> large insert, if possible. If not then once a day.
>
> Does the data comes in batches or as a continues stream?
>
> What are the chances of getting more memory as I mentioned on the previous
> mail?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Aasmund Midttun Godal

aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46