Get the newest tricks and useful tips about the world of PostgreSQL and Data Science! Our authors and technicians share their knowledge to keep you going with your data.
Searching for a quick tip? Check out our microblog, where we share our insights as they happen – short and simple!
Simple partitioning 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 live system can be tricky. Our solution offers a simple way to solve this problem. The usual way can be summarized […]
zheap: Inspecting storage sizes
To dig a bit deeper into zheap and PostgreSQL storage technology in general I decided to provide some more empirical information about space consumption. As stated in my previous blog post about zheap is more efficient in terms of storage consumption. The reasons are: The tuple header is much smaller Alignment has been improved The […]
PostgreSQL: Sophisticating temporary tables
Temporary tables have been around forever and are widely used by application developers. However, there is more to temporary tables than meets the eye. PostgreSQL allows you to configure the lifespan of a temporary table in a nice way and helps to avoid some common pitfalls. CREATE TEMPORARY TABLE … By default, a temporary table […]
pg_squeeze: Optimizing PostgreSQL storage
Is your database growing at a rapid rate? Does your database system slow down all the time? And maybe you have trouble understanding why this happens? Maybe it is time to take a look at pg_squeeze and fix your database once and for all. pg_squeeze has been designed to shrink your database tables without downtime. […]
zheap: Reinvented PostgreSQL storage
In PostgreSQL table bloat has been a primary concern since the original MVCC model was conceived. Therefore we have decided to do a series of blog posts discussing this issue in more detail. What is table bloat in the first place? Table bloat means that a table and/or indexes are growing in size even if […]
A quick look at PostgreSQL 13 RC1 query performance
If you read this blog post the new PostgreSQL version will be probably already officially released to the public for wider usage…but seems some eager DBA already installed the last week’s Release Candidate 1 and took it for a spin 😉 The “spin” though takes 3 days to run for my scripts, so that’s the […]
Debugging PL/pgSQL: GET STACKED DIAGNOSTICS
PL/pgSQL is the preferred way to write stored procedures in PostgreSQL. Of course there are more languages to write code available but most people still use PL/pgSQL to get the job done. However, debugging PL/pgSQL code can be a bit tricky. Tools are around but it is still not a fun experience. One thing to […]
HOT updates in PostgreSQL for better performance
© Laurenz Albe 2020 HOT updates are not a new feature. They were introduced by commit 282d2a03dd in 2007 and first appeared in PostgreSQL 8.3. But since HOT is not covered by the PostgreSQL documentation (although there is a README.HOT in the source tree), it is not as widely known as it should be: Hence […]
Generating a normal distribution in SQL
SQL and PostgreSQL are perfect tools to analyze data. However, they can also be used to create sample data which has to possess certain statistical properties. One thing many people need quite often is a normal distribution. The main question therefore is: How can one create this kind of sample data? Tablefunc: Creating normal distributions […]
PostgreSQL vs PostGreSQL vs Postgre vs Oracle vs all the rest
Is it Postgre, PostGreSQL, Postgres or PostgreSQL? We have all seen a couple of wrong ways to spell “PostgreSQL”. The question therefore is: How can one find data even if there are typos? In PostgreSQL there are various solutions to the problem. Depending on what kind of search you need you can choose between various […]
Tuning PostgreSQL autovacuum
© Laurenz Albe 2020 In many PostgreSQL databases, you never have to think or worry about tuning autovacuum. It runs automatically in the background and cleans up without getting in your way. But sometimes the default configuration is not good enough, and you have to tune autovacuum to make it work properly. This article presents […]
PostgreSQL: GROUP BY expression
GROUP BY is nothing new and is available in any relational database I am aware of. It is an integral part of SQL and PostgreSQL but what many people might not know is the fact that GROUP BY can do more than just group by simple fields. You can use expressions to group in an […]