No support for partitioned tables? Perhaps in name ... but I use a time-based
"partition" tables that inherit from a base table; new partitions are "placed"
(moved) round-robin on a set of drives. Somewhat manual, but if you really need
a solution now, it works.
Quoting Greg Stark <gsstark@mit.edu>:
>
> "Matt Casters" <Matt.Casters@advalvas.be> writes:
>
> > I've been reading up on partitioned tabes on pgsql, will the performance
> > benefit will be comparable to Oracle partitioned tables?
>
> Postgres doesn't have any built-in support for partitioned tables. You can
> do
> it the same way people did it on Oracle up until 8.0 which is by creating
> views of UNIONs or using inherited tables.
>
> The main advantage of partitioned tables is being able to load and drop data
> in large chunks instantaneously. This avoids having to perform large deletes
> and then having to vacuum huge tables to recover the space.
>
> However in Postgres you aren't going to get most of the performance
> advantage
> of partitions in your query plans. The Oracle planner can prune partitions
> it
> knows aren't relevant to the query to avoid having to search through them.
>
> This can let it get the speed of a full table scan without the disadvantage
> of
> having to read irrelevant tuples. Postgres is sometimes going to be forced
> to
> either do a much slower index scan or read tables that aren't relevant.
>
> --
> greg
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
--
"Dreams come true, not free."
