Lloyd Albin

We ran into an Serializable Snapshot Isolation issue recently when doing atomic schema replacement in our data warehouse, where the end user would see zero rows of data even though when they started their transaction and set their isolation level to REPEATABLE READ READ ONLY or SERIALIZABLE READ ONLY and they queried the table and the query had results.

Requirements:

These are some of the requirements for our data warehouse.

I had a programmer today, ask me about how to check to see if a table is an unlogged table. He had already found the basic query on Stack Overflow with a google search, and asked me if it was OK to use. The basic query is actually incomplete due to not including the schema name.

With everybody working from home, you may wish have your multi monitor setup at work on your multi monitor setup at home.

The old way to do this is to set the height and width of your home setup to be the size of both your screens combined. Lets say you have two monitors that are 1920W x 1080H. You would then set your width to 1920*2=3840 and your height to 1080. The problem with this setup happens when you maximize an application, then it maximizes it across both screens.

At work the finance department likes Excel and uses the ODBC drivers to get data from PostgreSQL. This worked just fine when we had 32-bit Office, but when we switched to 64-bit Office, the documents would no longer work due to the fact that the 32-bit and 64-bit drivers were named differently. Historically if you used psqlODBC on a 32bit machine (PostgreSQL Unicode) and then used the same file on a 64bit machine (PostgreSQL Unicode(x64)).

We found that using minimal WAL files together with the CREATE TABLE and COPY commands inside a transaction causes a 10x to 300x slowdown, whereas the PostgreSQL code and docs say this will make the commands faster due to optimizations that writes the table directly bypassing the WAL files. The slowdown is amplified by the amount of shared buffers due to the fact that the FlushRelationBuffers function sequentially searches the entire shared buffers.

I was recently asked by another PostgreSQL DBA about what could be causing queries to run slow while their application is running but then run fast when the application is stopped. While I have given several presentations on this subject, each one has been on a specific cause and not talking about all of them together. I decided I would compile them into a list on this page.

This presentation covers the how to monitor Postgres's AutoVacuum system and how to tune it.

You may read this blog post to see some of the items you can catch by monitoring the AutoVacuum system.

Lloyd

I will be presenting on Viewing data at the intersection between roles at the PostgresConf Silicon Valley which is being held October 15 - 16, 2018 at the Hilton San Jose. The full schedule of the conference is available online.

This is a set of updates for collectd to collect even more data from Postgres that the Default Postgres Configuration file supplied with collectd.

https://github.com/LloydAlbin/SCHARP-collectd