Partition PostgreSQL: which partition did I INSERT my data into?
Partitioning is one of the most desired features of PostgreSQL, widely adopted by developers. This is not only true for in PostgreSQL 15, but also for older versions which did not provide as many features as the latest version of the database. That’s why you should know not only how to properly partition tables, but […]
Automatic partition creation in PostgreSQL
© Laurenz Albe 2022 Table partitioning is one of the best-liked features out of the more recent PostgreSQL developments. However, there is no support for automatic partition creation yet. This article shows what you can do to remedy that. Use cases for automatic partition creation There are essentially two use cases: Create partitions triggered by […]
pg_rewrite: PostgreSQL Table Partitioning
PostgreSQL table partitioning is by far one of the most popular new PostgreSQL features. Many people look to partitioned tables as a way to improve performance and broaden scalability. However, partitioning comes with a little problem: How can you partition an existing table without locking up the database? The answer is: pg_rewrite can help you […]
Simple partitioning of live data with Postgres
Partitioning data is a well known way to optimize big tables into smaller ones using a given attribute. And while creating a partitioned table structure itself is not complicated, the migration of a table on a live system can be tricky. Our solution offers a simple way to solve this problem. The usual way can be […]
Partition management – do you really need a tool for that?
By Kaarel Moppel The functionality of using table partitions to speed up queries and make tables more manageable as data amounts grow has been available in Postgres for a long time already. Nicer declarative support became available from v10 on – so in general it’s a known technique for developers. But what is not so […]
PostgreSQL: You might need to increase max_locks_per_transaction
“out of shared memory”: Some of you might have seen that error message in PostgreSQL already. But what does it really mean, and how can you prevent it? The problem is actually not as obscure as it might seem at first glance. max_locks_per_transaction is the critical configuration parameter you need to use to avoid trouble. […]
Discovering less-known PostgreSQL v12 features
By Kaarel Moppel Version 12 of PostgreSQL is not exactly fresh out of the oven, as the first minor release was already announced. However, I think it’s fair to say that this version can be still considered fresh for most users, and surely only a small percentage of users has upgraded. So I think it […]