Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!news.albasani.net!weretis.net!feeder1.news.weretis.net!news.solani.org!.POSTED!not-for-mail From: Mladen Gogala Newsgroups: comp.databases.postgresql Subject: Re: migrating oracle to postgres Date: Thu, 23 Jun 2011 16:42:52 +0000 (UTC) Organization: solani.org Lines: 56 Message-ID: References: <1308640710.210659@proxy.dienste.wien.at> <96h5hqFo39U1@mid.individual.net> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Trace: solani.org 1308847372 32416 eJwFwYEBwDAEBMCVUF4yDur3HyF38UEx6Qh4MNg1bVLehOqic+tuX1oCAh7JX8mUGh6HzQM1VhHA (23 Jun 2011 16:42:52 GMT) X-Complaints-To: abuse@news.solani.org NNTP-Posting-Date: Thu, 23 Jun 2011 16:42:52 +0000 (UTC) User-Agent: Pan/0.133 (House of Butterflies) X-User-ID: eJwNwYkBwCAIA8CVypMI4yDK/iPYOxiFvZygYzBx5BNUbo1Kiaqxe9iqXWf/OtMWmY0rLrgPJ1wRjQ== Cancel-Lock: sha1:4QbYSAkaK67Hrg1iq7NM96/8elE= X-NNTP-Posting-Host: eJwFwYkBADAEBLCVaJ1nHJT9R2iCq6xtolDBYmefhttjEWrycs7B4ZHijBNUYbqobEFa3vMBG4UQ/g== Xref: x330-a1.tempe.blueboxinc.net comp.databases.postgresql:146 On Thu, 23 Jun 2011 17:48:09 +0200, Robert Klemme wrote: > Why do you consider this bad? I agree that it sounds silly at first > sight that the user needs to take care of cleaning up deadwood in the > database. On the other hand, on an Oracle instance you'll likely also > have periodic jobs doing statistics updates which is what VACUUMing also > does. Reducing the work a transaction has to do can be beneficial for > the client, too. No, what I consider bad is the architecture of putting dead rows in the table. That prevents Postgres from assigning a fixed row id to every row and from creating global indexes on partitioned tables. I believe that they should consider putting previous versions of the row in a separate table and have the vacuum process, let's call it "smon" for the sake of argument, do a periodical cleanup. That way rows in the table are not a moving target, there is a possibility of assigning a permanent row id, which would also allow creating global indexes. > >> The third is the fact that Postgres doesn't do multi-block reads. > > Isn't this alleviated by the OS doing readahead on many platforms? Weeellllll....it is. You are right, my objection is mostly of aesthetic nature. When I come to think of it, it does simplify the optimizer because there is only one type of I/O to think about. > >> Fixed size archive logs (16M) would be the next issue. > > This can be changed during compilation though. But I agree, you'd > rather want this to be tuneable at runtime - at least without > recompiling the software. > >> The lack of shared pool makes it impossible to see the plan of the >> statement being executed. And there is more. > > That's unfortunate. At least you can see SQL currently executed by > querying pg_stat_activity. > > Kind regards Oh yes, and there is also auto explain module which will dump the plan of any statement running longer than a predefined period of time (tunable). What I really miss is the ability to click on the session in PgAdmin3 and see the plan of a running statement. Also, sorting by the CPU time or the number of I/O requests issued would be nice. -- http://mgogala.byethost5.com