Re: Can I use PostgreSQL to develop a self-organizing

On Tue, 9 Sep 2003, Rada Chirkova wrote:

> Hi,
>
> I have asked my question on pgsql-general, and Tom Lane suggested I post
> here too. I would really appreciate your opinion.
>
> At NC State University, my students and I are working on a project called
> "self-organizing databases," please see description below. I would like to
> use an open-source database system for implementation and would really
> appreciate your opinion on whether PostgreSQL is suitable for the project.
> In general, I am very impressed by the quality of PostgreSQL code and
> documentation, as well as by the support of the developer community.
>
> For the project, I need a cost-based query optimizer with exhaustive join
> enumeration and use of statistics on stored relations; PostgreSQL has that.
> I also need the ability to process SQL queries with aggregation, extensive
> indexing capabilities, view mechanisms, and possibly integrity constraints;
> it seems that PostgreSQL has all that. We will modify the query optimizer to
> incorporate rewriting queries using views, and we will create
> view-generating and view-manipulating modules.
>
> Please let me know if you have comments.

PostgreSQL does not, as yet, support materialised views. Since your
project aims to create materialised views automagically based on usage
patterns this doesn't seem like a problem.

Your project will probably need to modify the statistics collector code to
record performance of queries on different relations over time. There is
already code which uses these statistics to 'reorganise' the database, but
not in as sophisticated a way as you are looking at
(contrib/pg_autovacuum).

Be sure to keep the list up to date with what you are doing -- if you
proceed -- as this is an area which database vendors seem to be moving
(IBM seem to be making the most noise about it).

Thanks,

Gavin