Database Administration & Monitoring

PostgreSQL partitioning (6): Attaching and detaching partitions

Having talked about partitioning strategies and partition pruning this time we will have a look on how you can attach and detach partitions to and from an existing partitioned table. If you missed the last posts about partitioning in PostgreSQL(…)

PostgreSQL partitioning (5): Partition pruning

This is the next post in the PostgreSQL partitioning series. If you missed the previous ones here they are: PostgreSQL partitioning (1): Preparing the data set PostgreSQL partitioning (2): Range partitioning(…)

PostgreSQL partitioning (4): Hash partitioning

The last partitioning strategy we will look at is: hash partitioning. If you missed the first posts in this series here they are: PostgreSQL partitioning (1): Preparing the data set PostgreSQL partitioning(…)

PostgreSQL partitioning (3): List partitioning

In the last posts of this series we prepared the data set and had a look at range partitioning. In this post we look at another partitioning strategy: List partitioning. Instead of(…)

PostgreSQL partitioning (2): Range partitioning

Now that the data set is ready we will look at the first partitioning strategy: Range partitioning. Usually range partitioning is used to partition a table by days, months or years although you can partition by(…)

PostgreSQL partitioning (1): Preparing the data set

This is the start of a series about partitioning in PostgreSQL. For this and the following posts I will use PostgreSQL 12 (which currently is in beta) so some stuff might not work if you are on PostgreSQL 11 or(…)

PostgreSQL 12: New partition reporting functions

PostgreSQL 10 introduced declarative partitioning (with some limitations), PostgreSQL 11 improved that a lot (Updating the partition key now works in PostgreSQL 11, Insert…on conflict with partitions(…)

PostgreSQL 12: Control when generic plans are used

When you are using prepared statements in PostgreSQL you might get a custom or a generic plan. Custom plans come with overhead of re-planning while generic plans avoid re-planning of the statement.

Securely store passwords in PostgreSQL

Every application somehow needs to deal with passwords. Some use external authentication methods such as ldap, others us the the framework the database already provides and create users and roles. But it is also not uncommon that applications implement their(…)

Can you start two (or more) PostgreSQL instances against the same data directory?

As PostgreSQL does not know the concept of running multiple instances against the same files on disk (e.g. like Oracle RAC) it should not be possible to start two or more instances against the same data directory. If that would(…)