
PostgreSQL at low level: stay curious - erthalion
https://erthalion.info/2019/12/06/postgresql-stay-curious/
======
davidw
Worth it just to learn about the -k option to strace, something that appears
to have been added in the last 5 years.

------
bogomipz
Under section 11 "memory reclaim" the author states:

>"But memory request is actually used only internally e.g. to calculate
oom_adj and QoS."

Could someone elaborate on this? Doesn't Kubernetes also use this to decide if
a container could be scheduled on this node in the first place? Or am I
missing some other obvious point the author is trying to make?

~~~
erthalion
Yes, K8S uses this information for scheduling. The phrase in the text is
mostly about K8S is not using this for resource isolation on cgroups level,
sorry if it wasn't clear enough.

------
janpot
Kind of unrelated and very high level thoughts, but I wish postgres had a
scripting model a bit like redis. Where functions are cached under their sha1
hash and can be executed by specifying that hash. cached functions would then
be evicted after not being run for a certain time. From the application side
I'd just always try executing the hash of the function and create it on the
fly when that errors (because doesn't exist on the db side yet). a model
similar to this one would completely convert me to defining most of my app
logic in plsql functions.

~~~
manigandham
STORED PROCEDURES: Available since PG11. Name them whatever you want, and call
them with parameters. They can be written in multiple languages like PL/pgSQL
which supports more constructs than standard SQL, and support defining
multiple transactions internally. They are part of your schema and always
available with support for security access.

PREPARED STATEMENTS: Queries that are executed often. Setup on every new
session but will be automatically compiled, cached for the session lifetime,
and executed with just a minimal identifier. Depends on your client driver to
handle seamlessly.

More info: [https://www.postgresql.org/docs/current/sql-
prepare.html](https://www.postgresql.org/docs/current/sql-prepare.html)

~~~
SigmundA
Looks like the plan caching is only per session which is a big bummer if you
call the same statements over and over from multiple sessions.

Plus you have manually deal with this which is weird coming from MSSQL where
it just does it with a global cache based on statement text hash all the time.

Brings me back to early 2000's when it was recommended to always use stored
procedures because either MSSQL 7 or 2000 can't remember didn't cache ad-hoc
statements only sprocs.

~~~
manigandham
Plan caching is different from prepared statements, but yes PG is not as
advanced as other commercial databases in execution.

Usually the database client drivers will handle this for you. If you're using
.NET, the npgsql driver maintains a connection pool and creates prepared
statements for each new session. If you have something like PGBouncer in the
middle then it gets more complicated.

~~~
SigmundA
There is almost no point is using prepared statements in MSSQL anymore, not
for a long time. The only benefit comes from not sending the prepared
statement over the wire each time and not having to hash it to do a plan
lookup.

That benefit is so very minor compared to the time taken to parse and plan the
statement that almost none uses it, for more details:

[https://dba.stackexchange.com/questions/129659/what-is-
the-s...](https://dba.stackexchange.com/questions/129659/what-is-the-sense-
and-benefit-of-using-sqlcommand-prepare/129721#129721)

In PG its seems to be the only choice and on top of that isn't even shared
among connections, this honestly blows my mind and will have to remember if I
move anything serious over to PG.

I like PG a lot and it does a lot of thing MSSQL doesn't but this is a big
missing performance piece.

~~~
manigandham
I'm very familiar with both (and we use both) but plan caching isn't going to
make that much of a difference for many scenarios. PG also has simpler
planning than MSSQL anyway. I doubt you'd notice a difference unless you're
extremely complex queries or a very high rate of transactions.

