Internals of PostgreSQL • Hacker News Board

Re: Internals of PostgreSQL

#11

Post by gaius » Sun, Jan 20, 2019, 11:14 AM UTC

chris_wot wrote: post #6

This is the thing I love about PostgreSQL, you can find out everything about the database. Oracle is ridiculous - they have something of the range of over a thousand hidden parameters, query tuning their CBO is somewhat of a black art and can change between point releases, and leads to articles like the following: http://www.dba-oracle.com/art_so_undoc_parms_p2.htm PostgreSQL, however, has none of these limitations.

Don’t take anything seriously on dba-oracle.com - Burleson is well known in the Oracle community as an expert in self-promotion but his technical skill is mediocre at best.

Jonathan Lewis, Guy Harrison and Craig “the Hammer” Shallahammer are infinitely more experienced and insightful.

Re: Internals of PostgreSQL

#12

Post by mohsinhijazee » Sun, Jan 20, 2019, 11:39 AM UTC

Thank you for posting. Really great resource.

Re: Internals of PostgreSQL

#13

Post by onezeronine2 » Sun, Jan 20, 2019, 12:21 PM UTC

As a person who have started in SQL server, I really like the succinct clarity of Postgresql functions and its lightweight installation on the server. Thanks for sharing, this could really help me in the future.

Re: Internals of PostgreSQL

#14

Post by cneumuel » Sun, Jan 20, 2019, 12:30 PM UTC

gaius wrote: post #11

chris_wot wrote: post #6

This is the thing I love about PostgreSQL, you can find out everything about the database. Oracle is ridiculous - they have something of the range of over a thousand hidden parameters, query tuning their CBO is somewhat of a black art and can change between point releases, and leads to articles like the following: http://www.dba-oracle.com/art_so_undoc_parms_p2.htm PostgreSQL, however, has none of these limitations.

Don’t take anything seriously on dba-oracle.com - Burleson is well known in the Oracle community as an expert in self-promotion but his technical skill is mediocre at best. Jonathan Lewis, Guy Harrison and Craig “the Hammer” Shallahammer are infinitely more experienced and insightful.

Right. There are many Oracle internals experts who are presenting at conferences, they are also quite approachable. I'd especially recommend Tanel Poder, several of his troubleshooting sessions are available on youtube.

Re: Internals of PostgreSQL

#15

Post by speg » Sun, Jan 20, 2019, 1:22 PM UTC

Aside: do you still need to use a cache service when using Postgres? Our Django web app forgoes any caching because “Postgres is fast enough” and “has its own cache”.

Re: Internals of PostgreSQL

#16

Post by fleetfox » Sun, Jan 20, 2019, 1:37 PM UTC

speg wrote: post #15

Aside: do you still need to use a cache service when using Postgres? Our Django web app forgoes any caching because “Postgres is fast enough” and “has its own cache”.

Assuming you mean application level cache you can't really generalize it. In general you don't want to cache anything if you can because cache invalidation is a really hard problem.

Re: Internals of PostgreSQL

#17

Post by brianwawok » Sun, Jan 20, 2019, 2:12 PM UTC

speg wrote: post #15

Aside: do you still need to use a cache service when using Postgres? Our Django web app forgoes any caching because “Postgres is fast enough” and “has its own cache”.

Generally true

But if some query takes 5 minutes to run and you can’t speed it up, you need to do something.

I cache a very few select queries that don’t need to be 100% accurate as scan a ton of rows.

Re: Internals of PostgreSQL

#18

Post by paulryanrogers » Sun, Jan 20, 2019, 3:00 PM UTC

chris_wot wrote: post #6

This is the thing I love about PostgreSQL, you can find out everything about the database. Oracle is ridiculous - they have something of the range of over a thousand hidden parameters, query tuning their CBO is somewhat of a black art and can change between point releases, and leads to articles like the following: http://www.dba-oracle.com/art_so_undoc_parms_p2.htm PostgreSQL, however, has none of these limitations.

IIRC Mysql too can change between point releases in surprising ways. (Though not sure if that practice began before or after Oracle's purchase of it.)

Re: Internals of PostgreSQL

#19

Post by woile » Sun, Jan 20, 2019, 3:10 PM UTC

Awesome source, does anyone know how to convert it into a mobi? I'd like to send it to my kindle

Re: Internals of PostgreSQL

#20

Post by bpicolo » Sun, Jan 20, 2019, 3:32 PM UTC

speg wrote: post #15

Aside: do you still need to use a cache service when using Postgres? Our Django web app forgoes any caching because “Postgres is fast enough” and “has its own cache”.

Depends on what you're using the cache for. Postgres is definitely fast enough for reads of a K/V pair at a scale your master node can support for most use cases, but there are cases that you can't suit with postgres alone at-scale, e.g:

1. Needing a consistent (meaning reading off replicas is not fresh enough) data source for reads and writes that happens every request to very high-request site. Think sessions, api rate limiting.

2. Needing a fast store for denormalized data.