Partition DB Tables by month

Поиск
Список
Период
Сортировка
Искать

Partition DB Tables by month

От:
Romildo Wildgrube <romildo@ragingnet.com>
Дата:
Hi,

Is there a way to break the data files by month like in Oracle so that I 
can easely drop the tables on a monthly basis? Is this something 
postgres supports or is there a work around solution?

Any help will be much appreciated.

Romi

Re: Partition DB Tables by month

От:
Dani Oderbolz <oderbolz@ecologic.de>
Дата:
Romildo Wildgrube wrote:

> Hi,
>
> Is there a way to break the data files by month like in Oracle so that 
> I can easely drop the tables on a monthly basis? Is this something 
> postgres supports or is there a work around solution? 

Hmm, as far as I know its not supported.
But I could think of a solution of a table per month
which are then hidden behind a view which does a UNION ALL
on all of them.
You could even automate the Adminostration of this via a Function.
I mean, in Oracle Partitions behave like single tables, so this would
simulate somethig similar.
But I think the Optimizer would not
be able to see that he must only access the table table_Jan_2003
if you are restricting on January 2003...

Cheers, Dani


Re: Partition DB Tables by month

От:
Dani Oderbolz <oderbolz@ecologic.de>
Дата:
Ray Ontko wrote:

>One limitation to the UNION approach is that you can't 
>insert, update, or delete through the UNION view.  At
>some point the application needs to understand how the
>virtual table is partitioned into these month-specific
>tables.
>
>Romido: Why not simply delete the rows each month instead
>of dropping tables each month?
>
Hmm,
but it wouls surely be possible (at the cost of some performace)
to put a trigger on the view to actually sort this all out.
I guess deleting is a really bad option, as
1. The DB needs to do all kinds of logging which you donmm't want (you 
dont want to rollback ever)
2. This operations leaves you with a big Vacuum job

Therefore, I think, Partitioning could be a good thing.
BDW: This might be a really important reason for a
company to switch their Data Warehouse to Postgres,
as this is almost impossible without it.


Regards,
Dani

Re: Partition DB Tables by month

От:
"Mendola Gaetano" <mendola@bigfoot.com>
Дата:
"Dani Oderbolz"  wrote:
> Ray Ontko wrote:
> 
> >One limitation to the UNION approach is that you can't 
> >insert, update, or delete through the UNION view.  At
> >some point the application needs to understand how the
> >virtual table is partitioned into these month-specific
> >tables.
> >
> >Romido: Why not simply delete the rows each month instead
> >of dropping tables each month?
> >
> Hmm,
> but it wouls surely be possible (at the cost of some performace)
> to put a trigger on the view to actually sort this all out.
> I guess deleting is a really bad option, as
> 1. The DB needs to do all kinds of logging which you donmm't want (you 
> dont want to rollback ever)
> 2. This operations leaves you with a big Vacuum job
> 
> Therefore, I think, Partitioning could be a good thing.
> BDW: This might be a really important reason for a
> company to switch their Data Warehouse to Postgres,
> as this is almost impossible without it.

If the goal is have the query optimized for the last month
you can easilly accomplish this using a partial index.


Regards
Gaetano Mendola

Re: Partition DB Tables by month

От:
Dani Oderbolz <oderbolz@ecologic.de>
Дата:
Mendola Gaetano wrote:

>you can easilly accomplish this using a partial index.
>  
>
Would that really work with a view?
Can you post a syntax example for this?

Regards,
Dani

Re: Partition DB Tables by month

От:
"Mendola Gaetano" <mendola@bigfoot.com>
Дата:
"Dani Oderbolz"  wrote:
> Mendola Gaetano wrote:
>
> >you can easilly accomplish this using a partial index.
> >
> >
> Would that really work with a view?
> Can you post a syntax example for this?

CREATE TABLE foo (
field_a
field_b
......
fast_search  BOOLEAN NOT NULL DEFAULT 1,
time_stamp TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_fast_search ON foo ( fast_search ) where fast_search = 't';

for each field to index:
CREATE INDEX idx_field_a ON foo ( field_a ) where fast_search = 't';
CREATE INDEX idx_field_b ON foo ( field_b ) where fast_search = 't';


at the beginning of each month you can now do:

UPDATE foo SET fast_search = 'f'
WHERE time_stamp < now() AND
fast_search = 't';


Your improved query for the last month:

SELECT *
FROM foo
WHERE fast_search = 't' AND
     = XXXXX AND
    = YYYYYY;


I hope this help


Regards
Gaetano





















PG 7.3.3 startup problem

От:
Kris Kiger <kris@musicrebellion.com>
Дата:
I've installed postgres 7.3.3 on one of my debian machines.  On other 
installations postgres is started automatically on machine bootup. 
 However, on one installation it fails to do so.  In fact, it doesn't 
appear that PG tries to start at all durring the bootup process.  I'm 
still relatively new to linux, but from my understanding, I need to be 
looking in the /etc/init.d directory for boot setup information.  Does 
anyone know of a good reference I could learn more about how the linux 
bootup process works, and how to trouble shoot this particular problem? 
 Thanks in advance for the help

Kris


Re: Partition DB Tables by month

От:
Dani Oderbolz <oderbolz@ecologic.de>
Дата:
Mendola Gaetano wrote:

> CREATE TABLE foo (field_a
>
>field_b
>......
>fast_search  BOOLEAN NOT NULL DEFAULT 1,
>time_stamp TIMESTAMPTZ NOT NULL DEFAULT now()
>);
>CREATE INDEX idx_fast_search ON foo ( fast_search ) where fast_search = 't';
>
>for each field to index:
>CREATE INDEX idx_field_a ON foo ( field_a ) where fast_search = 't';
>CREATE INDEX idx_field_b ON foo ( field_b ) where fast_search = 't';
>
>
>at the beginning of each month you can now do:
>
>UPDATE foo SET fast_search = 'f'
>WHERE time_stamp < now() AND
>fast_search = 't';
>  
>
I guess we are missunderstanding each other.
I meant you were saying that you could have a partial index on a view
which does a Union All of may tables (each table contains a Month).
But I think thats really not possible.

Cheers,
Dani

Re: PG 7.3.3 startup problem

От:
Renney Thomas <renneyt@yahoo.com>
Дата:
A good start would be to read the logfile. A lot of startup problems can 
be solved from the messages within. It could be something as simple as 
trying to start PG as root.

Kris Kiger wrote:

> I've installed postgres 7.3.3 on one of my debian machines.  On other 
> installations postgres is started automatically on machine bootup. 
> However, on one installation it fails to do so.  In fact, it doesn't 
> appear that PG tries to start at all durring the bootup process.  I'm 
> still relatively new to linux, but from my understanding, I need to be 
> looking in the /etc/init.d directory for boot setup information.  Does 
> anyone know of a good reference I could learn more about how the linux 
> bootup process works, and how to trouble shoot this particular 
> problem? Thanks in advance for the help
>
> Kris
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Re: PG 7.3.3 startup problem

От:
Andrew Sullivan <andrew@libertyrms.info>
Дата:
On Wed, Jul 30, 2003 at 10:34:51AM -0500, Kris Kiger wrote:
> appear that PG tries to start at all durring the bootup process.  I'm 
> still relatively new to linux, but from my understanding, I need to be 
> looking in the /etc/init.d directory for boot setup information.  Does 
> anyone know of a good reference I could learn more about how the linux 
> bootup process works, and how to trouble shoot this particular problem? 

Debian's actually a little different from some other UNIX systems
(on;y in details, but there are differences).  You might want to look
at 

http://www.debian.org/doc/manuals/reference/ch-system.en.html#s-boot

At base, however, you want to use one of the sample rc scripts into
/etc/rc[n].d , and make a symlink to it from /etc/init.d.  I believe
the details are covered in the section of the doc I mention above.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
                              M2P 2A8
                                         +1 416 646 3304 x110

Re: PG 7.3.3 startup problem

От:
Andrew Sullivan <andrew@libertyrms.info>
Дата:
On Wed, Jul 30, 2003 at 01:23:25PM -0400, Andrew Sullivan wrote:
> 
> At base, however, you want to use one of the sample rc scripts into
> /etc/rc[n].d , and make a symlink to it from /etc/init.d.  I believe

Doh!  The other way around, actually.  Boy, it's embarassing
tripping over your own shoelaces.

The point is that the scripts go in /etc/init.d and you put a symlink
from (say) /etc/rc2.d/S75postgresql to /etc/init.d/postgresql.  The
details are in the doc I mentioned (I just checked).

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
                              M2P 2A8
                                         +1 416 646 3304 x110

FAQ