postgresql

This short post is for those who answered ‘keep the default’ in the following. Because the default (which is no huge page allocated) is not good for a database.

Question for my #PostgreSQL followers. Do you allocate Huge Pages on your server for your @PostgreSQL">@PostgreSQL shared_buffers, or keep the linux/postgres default?

 — @FranckPachot

Here is a little test of Jupyter Notebook to access a PostgreSQL database with very simple installation, thanks to Anaconda. I did it on Windows 10 but the same simplicity is on Linux and Mac.

Anaconda

I’ll use Anaconda to install the required components

I’m running on an Oracle Cloud Linux 7.6 VM provisioned as a sandbox so I don’t care about where it installs. For a better installation procedure, just look at Daniel Westermann script in:

Some more zheap testing - Blog dbi services

The zHeap storage engine (in development) is provided by EnterpriseDB:

EnterpriseDB/zheap

I’ll also use pg_active_session_history, the ASH (Active Session History) approach for PostgreSQL, thanks to Bertrand Drouvot

pgsentinel/pgsentinel

In order to finish with the references, I’m running this on an Oracle Cloud compute instance (but you can run it anywhere).

Cloud Computing VM Instances - Oracle Cloud Infrastructure

• postgresql
• Technical

• postgresql
• Technical

• postgresql
• Technical

TLDR: Note to future self… (1) Read this before you modify a table on a live PostgreSQL database. If you do it wrong then your app might totally hang. There is a right way to do it which avoids that. (2) Especially remember the lock_timeout step. Many blog posts around the ‘net are missing this and it’s very important.

Recently I was chatting with some PostgreSQL users (who, BTW, were doing rather large-scale cool stuff in PG) and they asked a question about making schema changes with minimal impact to the running application. They were specifically curious about changing a primary key from INT to BIGINT.  (Oh, you are making all your new PK fields BIGINT right?)