Обсуждение: Partition DB Tables by month
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
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
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
"Dani Oderbolz" <oderbolz@ecologic.de> 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
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
"Dani Oderbolz" <oderbolz@ecologic.de> 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
<field_a> = XXXXX AND
<field_b> = YYYYYY;
I hope this help
Regards
Gaetano
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
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
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) >
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 <andrew@libertyrms.info> M2P 2A8 +1 416 646 3304 x110
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
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110