Best practice to get performance
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
On 11/18/10 2:56 PM, Fredric Fredricson wrote:
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?
in certain other enterprise oriented databases, there is a feature known
as Materialized Views, which provides functionality similar to what
you're doing.
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
On 11/19/10 15:49, Andy Colson wrote:
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.
Are FreeBSD's temp tables still storage-backed? (i.e. are there
memory-backed temp tables)?
On 11/19/2010 4:04 PM, Ivan Voras wrote:
On 11/19/10 15:49, Andy Colson wrote:
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.Are FreeBSD's temp tables still storage-backed? (i.e. are there
memory-backed temp tables)?
Sorry, I'm not sure what you are asking. Not sure what this has to do
with FreeBSD, or its temp tables.
Unless: s/freebsd/postgres/i
Ah, yes, Postgres temp tables still make it to disk eventually. They
are not WAL logged (but if they get really big they spill to disk).
There are no memory only tables, no. But PG will cache the heck out of
all tables, so you get the best of both worlds.
-Andy
On 11/19/10 23:14, Andy Colson wrote:
On 11/19/2010 4:04 PM, Ivan Voras wrote:
On 11/19/10 15:49, Andy Colson wrote:
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.Are FreeBSD's temp tables still storage-backed? (i.e. are there
memory-backed temp tables)?Sorry, I'm not sure what you are asking. Not sure what this has to do
with FreeBSD, or its temp tables.Unless: s/freebsd/postgres/i
Yes, sorry, switched between mailing lists too fast :)
Ah, yes, Postgres temp tables still make it to disk eventually. They are
not WAL logged (but if they get really big they spill to disk). There
are no memory only tables, no. But PG will cache the heck out of all
tables, so you get the best of both worlds.
Thanks!