internals

graph TD A["User Process (PostgreSQL Backend)"] --> B{"Virtual Address Space (Backend)"}; B -- Accesses Data --> C["Shared Memory Segment (Mapped into VAS)"]; C --> D{"Shared Buffers (Within Shared Memory)"}; D -- Holds Data from --> E[PostgreSQL Data Files on Disk]; subgraph "Virtual Address Space (Backend)" B1["Process Private Memory (work_mem, etc.)"] B2["Shared Memory Segment (for shared_buffers)"] B --o B1 B --o B2 end style B fill:#ccf,stroke:#333,stroke-width:2px style C fill:#9cf,stroke:#333,stroke-width:2px style D fill:#cff,stroke:#333,stroke-width:2px style E fill:#eee,stroke:#333,stroke-width:2px style A fill:#fff,stroke:#333,stroke-width:2px

When a table is designed the columns have an order that follows the human logic that doesn’t necessary gives coincides with the way PostgreSQL stores the data ad may cause unnecessary bloat.

Let’s find out why the column order is important and how we can use it to improve our storage and consequently get better performance.

In the previous blog post we have seen how plenty of ram doesn’t necessary results in a faster instance.

This time we’ll quickly see how PostgreSQL and Linux relate to each other in particular if we want to run our RDBMS at scale.

Then we’ll have a look to the virtual memory in Linux and how to make it more friendly to PostgreSQL.

Let’s dig in.

Nowadays it’s not uncommon to deal with machines with hundreds of GB of RAM.

Abundant memory can give PostgreSQL a massive performance boost. However things work slightly different than you may expect.

Let’s find out!

When in a pl/pgsql function there is an exception then the function stops the execution and returns an error. When this happens all the changes made are rolled back.

It’s always possible to manage the error at application level, however there are some cases where managing the exception inside the function it may be a sensible choice. And pl/pgsql have a nice way to do that. The EXCEPTION block.

However handling the exception inside a function is not just a cosmetic thing. The way the excepion is handled have implications that may cause issues.

The transactional model has been in PostgreSQL since the early versions. In PostgreSQL its implementation follows the guidelines of the SQL standard with some notable exceptions.

When designing an application it’s important to understand how the concurrent access to data happens in order to avoid unexpected results or even errors.

In checkpoint post we saw how a misbehaving session can crash PostgreSQL.

Before the version 9.6 the only option for avoiding this sort of things was to setup a script, usually via cron which killed the idle in transaction sessions after a specific timeout. In this post we’ll see in details what are the problems related with this approach and the new features implemented from PostgreSQL 9.6 can help the DBA to tackle potentially dangerous situations.

In the previous post we introduced the PostgreSQL’s dependency system.

At first sight the implementation can look like a maze where the succession of relationships are not clear.

This post will try to give a practical example to show how pg_depend can act like an Ariadne’s thread in order to resolve the dependencies.

The scenario presented is very simple but can be used as a starting point for more complex requirements.