Thread: 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
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!