Re: PostgreSQL, MySQL, etc., was Re: PostgreSQL is much faster than MySQL, only when...

> Since we are on the topic of what prompted us to use PostgreSQL, I
> figured I would share my experiences as well, and some additional
> thoughts that I had.
>
> I chose PostgreSQL about 2 years ago when I realized that the
> application I was building needed something more robust than MySQL. I
> found the learning curve to be a bit steep at first, but eventually got
> the hang of it and have developed a real appreciation for PostgreSQL.
> Unlike many members here I won't say I would never go back to MySQL,
> because there are some circumstances where I would (where I need an
> RDBMS-like interface but not an RDBMS, f. ex. MySQL's heap tables could
> be very useful for real-time control systems). However, I am actually
> seeing less and less value in MySQL as I become more familiar with the
> extensibility features in PostgreSQL.

I'm moving from Oracle 8i to PostgreSQL. I don't really do much fancy
stuff in SQL now, but I do use views a little bit and so MySQL just isn't
an option for me.

I just finished reading another book on PostgreSQL so I can imagine
why you like it so much. The only two things I'd really like to see in
PostgreSQL are:

1. Support for "CONNECT BY" (useful for family trees), which I
understand is being worked on now. The only twist is that I'd also like to
see a specialized index option for supporting this as this is something
that Oracle can't do -- my vision of such an index file is that it would be
quite different from others in that it would be designed to increase the
speed of traversing through the tree. I'm not convinced that Oracle's
solution to this is optimal in this regard.

2. Moving to table spaces (PostgreSQL version 8 maybe?) rather
than just storing a whole bunch of files in a single directory. Oracle's
implementation is nice because tables, indexes, etc., can span multiple
table spaces, and there are great performance optimization and scalability
advantages that otherwise just aren't possible without them. I read in
another thread (approx. 2 months old) earlier this evening that some folks
would like to see OIDs deprecated, and if this is the case then the sub-
directories under "base/" will obviously need a different naming mechanism,
so instead of re-thinking this perhaps it would be a good opportunity for
the PostgreSQL team to look at the possibility of implementing things
within table spaces.

> Anyway one additional thought I had was that it should be possible to
> write parsers for text files in PL/PERLu and then plug those in as
> views. In this way, files such as /etc/passwd or even /var/log/messages
> could be used as if they were tables in the database. Information from
> these tables could then be selected into other tables, presented
> directly to an application, etc. While it mught be possible to do the
> same for MySQL, it would be much more work. Imagine being able to run a
> query such as:
>
> select * from logs.iptables
> where proto = 'TCP' and td_entered > '2003-01-01' and port (select
> port from sysinfo.services where service='ssh');
>
> in order to get every hit against the ssh service on the firewall in the
> current year. Of course, I imagine, it would take some time to run ;-)
> But for generating reports of system activirt, I would not assume this
> to be a problem.

A very interesting idea, but my feeling is that pure PERL is best
suited for dealing with flat text files.

> Next question-- any ideas how one could generate something like MySQL's
> heap tables (maybe in shared memory?) within PostgreSQL?

I have no idea. Anyone else?

--
Randolf Richardson - rr(at)8x(dot)ca
Vancouver, British Columbia, Canada

Please do not eMail me directly when responding
to my postings in the newsgroups.