Re: PostgreSQL

First of all, if set properly, there is very little double buffering, if any. Shared buffers should be very small, 2 or 3 GB at most. The rest of caching is done by the OS. Linux IO buffering is a FIFO queue: if a buffer is reused, it comes to "youngest" part of the queue, where the likelihood of getting thrown out is the least. That is rather efficient mechanism, tuned through the yearsof  Linux development. It's actually an approximation of FIFO, using the famous "two handed clock" algorithm, inherited from the older Unix siblings. Linux file systems (XFS for rotational drives, F2FS for SDD devices) are optimized for performance and their caching is also rather efficient. The lack of shared pool is a much bigger problem, only partially alleviated by the use of LLVM. While the Linux caching mechanism lacks the database optimizations, it is still rather efficient and is usually not impeding performance. Frequent parsing and re-parsing is much more of a problem. Regards

On 2/3/23 09:54, pier paolo Bruno wrote:
> For my experience, the biggest issue is that , if you have a lot of
> concurrent activities, postgres opens its files through the filesystem
> cache , so you have double buffering ( filesystem cache and postgresql
> buffer cache ) and there is no "filesystem_options=O_DIRECT" in
> postgres ( there is on wal but you have issue and you can not use
> archive if i remember ) and this is a quite huge drawback in terms of
> performance . Other problem can be vacuum as other said. This only for
> a low level point of few. If you have applications with tons of plsql
> and use of oracle packages, it can not be cheap to migrate them .... (
> port, modify/rewrite, test everything ... )
>
> Il giorno mer 11 gen 2023 alle ore 12:19 Frits Hoogland
> <frits.hoogland_at_gmail.com> ha scritto:
>
> PostgreSQL and Oracle are two different databases.
>
> Both implement most of the SQL standards, and both contain their
> own specific implementations for all kinds of things. This is true
> for anything that directly is defined by the SQL standard, and
> anything beyond that.
>
> PostgreSQL contains a procedural language extension, plpgsql, that
> has similarities with Oracle's PLSQL.
>
> Out of the available general databases, I do believe PostgreSQL is
> most close to Oracle SQL and PLSQL.
>
> A few differences:
> - PostgreSQL relies on the operating system page cache for
> datafile buffering, and thus strictly employs buffered reads for
> these.
> - There is no equivalence of the shared pool: every backend
> (database connection) has to build its own dictionary cache, SQL
> cache, row cache.
> - The implementation of consistency differs between Oracle and
> PostgreSQL. Although the general way they deal with it is obvious
> and alike, there are nuanced implementation differences between
> the two.
>
> Oracle would perform better at huge scale with very specific
> configuration.
> In reality, most products using the database use it in a very
> basic way, and for these PostgreSQL can probably quite easily
> replace Oracle, provided you can change the SQL, the driver, etc.
> In fact, this is happening in a lot of places.
>
> Frits
>
>
>
>> On 6 Jan 2023, at 13:36, Terrian Thomas J CTR DLA INFO OPERATIONS
>> ("Tom.Terrian.ctr") <dmarc-noreply_at_freelists.org> wrote:
>>
>> Here are some open ended question for the group…We are starting
>> to look at migrating our databases from Oracle to PostgreSQL.  I
>> know nothing about PostgreSQL.
>> Has anyone done a pro’s and con’s list of Oracle vs. PostgreSQL?
>> Anyone have a lesson’s learned list from migrating from Oracle to
>> PostgreSQL?
>> Any thoughts/comments on PostgreSQL?
>> I kind of think that you get what you pay for…wouldn’t that mean
>> that Oracle would outperform PostgreSQL in every way?
>> Any comments would be appreciated.
>> Tom
>

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

--
http://www.freelists.org/webpage/oracle-l