Internals of PostgreSQL • Hacker News Board

Re: Internals of PostgreSQL

#21

Post by progval » Sun, Jan 20, 2019, 4:33 PM UTC

woile wrote: post #19

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

You can get an epub with pandoc. A very crude script to do it:

  wget http://www.interdb.jp/pg/pgsql{01..11}.html http://www.interdb.jp/pg/img/fig-{1..11}-{01..34}.png http://www.interdb.jp/pg/img/fig-4-fdw-{1..7}.png http://www.interdb.jp/pg/img/udc1.jpg
  mkdir img
  mv *.jpg *.png img/
  pandoc -s pgsql{01..11}.html -o internals_pgsql.epub

From there, you should be able to convert it to mobi with Calibre or a similar tool.

Re: Internals of PostgreSQL

#22

Post by anarazel » Sun, Jan 20, 2019, 6:47 PM UTC

paulryanrogers wrote: post #18

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.)

That's sometimes unavoidable. If there's a clear bug somewhere, fixing it will sometimes also affect queries/plans that weren't visibly affected by the bug. That's especially the case around costing issues - some queries might have regressed noticeably due to a bug since the last major version, but fixing it might affect other queries negatively (in a minor version). Postgres/we try to avoid that, but sometimes that's the most sensible way forward (and I assume the same is true for mysql etc).

Re: Internals of PostgreSQL

#23

Post by anarazel » Sun, Jan 20, 2019, 6:51 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”.

That really depends on what you're caching. Sometimes e.g. you might want to have local caches on application servers, not because the database would be too slow or couldn't take the load, but because the roundtrip time to the database make page loads too slow if you hit the DB every time.

Or you have some complex ranking/relationship/aggregation calculations - it might computationally be infeasible for the database, as hard as people worked on its efficiency, to calculate such a query on each request...

Re: Internals of PostgreSQL

#24

Post by vordoo » Sun, Jan 20, 2019, 6:52 PM UTC

Reminded me that "Postgres is easy to install, administer, maintain, and use... with just a little bit of orientation. This is that orientation."

Christophe Pettus: PostgreSQL Proficiency for Python People - PyCon 2014. https://www.youtube.com/watch?v=0uCxLCmzaG4

Re: Internals of PostgreSQL

#25

Post by aidos » Sun, Jan 20, 2019, 7:45 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”.

I read a comment like this and just desperately want to look at the query log to see what’s being run. Followed by explaining a few offenders to see what the planner is working with. You can get really deep into optimising your dB, but often there are a lot of low hanging fruit to work with that you can make “good enough” in no time at all.

Re: Internals of PostgreSQL

#26

Post by fiatjaf » Mon, Jan 21, 2019, 12:31 AM 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”.

Postgres is fast enough, but Django isn't.