Re: Best practice to get performance

On 11/18/2010 4:56 PM, Fredric Fredricson wrote:
> Hi,
> I have designed a handful databases but is absolutely no SQL-expert. Nor
> have I had any formal database training and have never worked with
> someone who had. What I know about SQL I have read in the documentation,
> found with google, and learned from my numerous mistakes.
>
> This question I have is somewhat related to the "unlogged tables"
> proposal that is discussed in another thread.
>
> The background is that I am designing a data storage that, unlike all
> other data storage, have some performance requirements (yes, that was a
> joke ;-).
> What I have done to handle this is to create "lookup" tables that cache
> preprocessed information. The simplest is row count but also results
> from selects with joins and group clauses. These tables are updated
> either on demand (first call), by triggers, or periodically.
>
> I assumed this was fairly standard practice and when I read about
> unlogged tables these tables was the first use that came to my mind.
> Since the lookup tables are used for performance and contain redundant
> data loosing the data at a restart is no real problem.
>
> What puzzle me though is that this use is never mentioned in the
> discussions, at least as far as I can see. Am I doing something
> "strange"? Is this something you should not have to do if you have
> "proper" database design?
>
> Regards
> /Fredric


unlogged will only help insert/update performance.  Lookup tables sound
readonly for a majority of time.  (I'm assuming lots of reads and every
once and a while updates).  I doubt that unlogged tables would speed up
lookup tables.

-Andy