pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcost of empty fields
4+ messages / 2 participants
[nested] [flat]
* cost of empty fields
@ 2005-04-15 14:22 Enrico Weigelt <weigelt@metux.de>
0 siblings, 1 reply; 4+ messages in thread
From: Enrico Weigelt @ 2005-04-15 14:22 UTC (permalink / raw)
To: pgsql-admin
Hi folks,
we've got an quite large table with 17 columns and now >15k rows.
The table works also as an journal will continously grow day by day.
Man of the columns (mostly text) aren't needed anymore if the tuple
has reached a certain age.
So I thought about clearing the unnecessary fields of old tuples
to save resources. Does it bring any performance improvement ?
Of I could splitt off this table an working- an archive- table
and map them together in some views - someday I probably *will*
do it - but our applications are not yet ready for this.
thx
--
---------------------------------------------------------------------
Enrico Weigelt == metux IT service
phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact@metux.de
cellphone: +49 174 7066481
---------------------------------------------------------------------
-- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
---------------------------------------------------------------------
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: cost of empty fields
@ 2005-04-15 16:58 Jim C. Nasby <decibel@decibel.org>
parent: Enrico Weigelt <weigelt@metux.de>
0 siblings, 1 reply; 4+ messages in thread
From: Jim C. Nasby @ 2005-04-15 16:58 UTC (permalink / raw)
To: pgsql-admin
If you setup rules on the view and just have the application select,
insert, update, and delete from the view instead of the raw tables you
won't need to change your application at all. Though you do need to be
aware that you can't easily enforce uniqueness across multiple tables.
As for clearing the fields, if by clearing you mean setting to NULL then
you're correct, it would save space. Just remember that you won't
actually be able to use the saved space until the table is vacuumed.
On Fri, Apr 15, 2005 at 04:22:07PM +0200, Enrico Weigelt wrote:
>
> Hi folks,
>
>
> we've got an quite large table with 17 columns and now >15k rows.
> The table works also as an journal will continously grow day by day.
> Man of the columns (mostly text) aren't needed anymore if the tuple
> has reached a certain age.
> So I thought about clearing the unnecessary fields of old tuples
> to save resources. Does it bring any performance improvement ?
>
> Of I could splitt off this table an working- an archive- table
> and map them together in some views - someday I probably *will*
> do it - but our applications are not yet ready for this.
>
>
> thx
> --
> ---------------------------------------------------------------------
> Enrico Weigelt == metux IT service
>
> phone: +49 36207 519931 www: http://www.metux.de/
> fax: +49 36207 519932 email: contact@metux.de
> cellphone: +49 174 7066481
> ---------------------------------------------------------------------
> -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
> ---------------------------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: cost of empty fields
@ 2005-04-21 02:19 Enrico Weigelt <weigelt@metux.de>
parent: Jim C. Nasby <decibel@decibel.org>
0 siblings, 1 reply; 4+ messages in thread
From: Enrico Weigelt @ 2005-04-21 02:19 UTC (permalink / raw)
To: pgsql-admin
* Jim C. Nasby <decibel@decibel.org> wrote:
> If you setup rules on the view and just have the application select,
> insert, update, and delete from the view instead of the raw tables you
> won't need to change your application at all. Though you do need to be
> aware that you can't easily enforce uniqueness across multiple tables.
hmm. that doesnt sound stable enough for me.
this table is really critical and there's a lot of money in game
(realtime currency trading ...)
> As for clearing the fields, if by clearing you mean setting to NULL then
> you're correct, it would save space. Just remember that you won't
> actually be able to use the saved space until the table is vacuumed.
Yes, that's what I had in mind.
Vacuum is no problem. The exchange is closed at weekend, so there's
really enough time for vacuum full analyze :)
cu
--
---------------------------------------------------------------------
Enrico Weigelt == metux IT service
phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact@metux.de
cellphone: +49 174 7066481
---------------------------------------------------------------------
-- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
---------------------------------------------------------------------
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: cost of empty fields
@ 2005-04-21 04:09 Jim C. Nasby <decibel@decibel.org>
parent: Enrico Weigelt <weigelt@metux.de>
0 siblings, 0 replies; 4+ messages in thread
From: Jim C. Nasby @ 2005-04-21 04:09 UTC (permalink / raw)
To: pgsql-admin
On Thu, Apr 21, 2005 at 04:19:02AM +0200, Enrico Weigelt wrote:
> * Jim C. Nasby <decibel@decibel.org> wrote:
> > If you setup rules on the view and just have the application select,
> > insert, update, and delete from the view instead of the raw tables you
> > won't need to change your application at all. Though you do need to be
> > aware that you can't easily enforce uniqueness across multiple tables.
> hmm. that doesnt sound stable enough for me.
> this table is really critical and there's a lot of money in game
> (realtime currency trading ...)
Note I didn't say you couldn't do it, I just said it wasn't easy. Easy
as in adding a normal unique constraint. In this case, you need to add
triggers to the tables to check for uniqueness. It's absolutely stable,
it's just not as nice as it could be.
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-04-21 04:09 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-15 14:22 cost of empty fields Enrico Weigelt <weigelt@metux.de>
2005-04-15 16:58 ` Jim C. Nasby <decibel@decibel.org>
2005-04-21 02:19 ` Enrico Weigelt <weigelt@metux.de>
2005-04-21 04:09 ` Jim C. Nasby <decibel@decibel.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox