Re: Migrating from Informix to Postgre

"Dinesh Anchan" <dinesh(at)pinksheets(dot)com> writes:

> We are currently using Informix and looking for a more economic database
> solution(combination of Linux and Opensource database). We currently have 5
> database servers; 1 running on Sun E450(4 cpu), 2 on Sun E420(4 cpu), 1 on
> Sun E250 and 1 on Netra T1, with database sizes 30 GB, 12 GB, 12GB, 15GB,
> 2GB respectively. Databases on E420s are replicated between them. There
> are at least 10 tables which hold more than 75 million records and
> fragmented accross different tablespaces. They have indexes and used
> extensively in DSS type and as well as OLTP type of queries.
>
> I went through lot of material in the discussion group but could not find a
> straight forward answer for the following questions. I would greatly
> appreciate if someone can share your experience with Postgre.
>
> 1. How comfortably Postgre can support these databases( with respect to
> volume)?

No problems there. I think the docs lay out the table and column
limits--check the website for details.

> 2. How reliable and what kind of replication Postgre provides? Is it log
> based or transaction based?

Replication is (currently) master-slave only, and implemented using
triggers AFAIK. There is a project (Postgres-R) to implement
multi-master replication, but it is not done yet.

> 3. Does it support table fragmentation accross multiple tablespaces?

Current version does not support tablespaces, so no. Tables are split
into 1GB segments (to avoid file size limits on some systems) but
there is no way to control what data goes in what segments.

You can CLUSTER table rows according to a sort criterion, which can
improve performance.

> 4. Does it support raw devices?

No, just regular files. It;s not clear that raw devices would be a
win for Postgres (though people have talked about it).

> 5. I have read that it is transactional. But is it heterogeneous? Can I
> update multiple databases in multiple database servers of different family
> (like informix, MySQL etc) in a single transaction?

Not without a lot of custom coding and implementing your own two-phase
commit protocol. I don't think you could do this with any database
without specialized code.

> 6. How is the performance?

Good in some cases, bad in others, just like any other DB. ;)

In general it works well if you take care to maintain good statistics
on your data (using ANALYZE) and tweak queries where necessary.

Good luck! It sound like you've got a tall order to fill...

-Doug