Citus: Row store vs. column store in PostgreSQL
Row store vs. column store – a lot has been written about this topic in the context of PostgreSQL and Citus. What does it really mean and what are the implications? Are column stores “always cool” and “always beneficial”? No, there’s more to it – which requires a closer look. When trying to understand the […]
Cleaning up a large number of BLOBs in PostgreSQL
PostgreSQL allows end users to store BLOBs (binary large objects) in the database. Many people use these functions to handle data directly in SQL. There has long been a discussion about whether this is a good thing or not. We expect that particular discussion to go on indefinitely. However, if you are in the “pro […]
When to use tablespaces in PostgreSQL
Users with an Oracle background consider tablespaces very important and are surprised that you can find so little information about them in PostgreSQL. This article will explain what they are, when they are useful and whether or not you should use them. What is a tablespace Essentially, a tablespace in PostgreSQL is a directory […]
Is UPDATE the same as DELETE + INSERT in PostgreSQL?
Introduction We know that PostgreSQL does not update a table row in place. Rather, it writes a new version of the row (the PostgreSQL term for a row version is “tuple”) and leaves the old row version in place to serve concurrent read requests. VACUUM later removes these “dead tuples”. If you delete a […]
BLOB cleanup in PostgreSQL
PostgreSQL offers a nice BLOB interface which is widely used. However, recently we came across problems faced by various customers, and it makes sense to reflect a bit and figure out how PostgreSQL handles BLOBs – and especially BLOB cleanup. Using the PostgreSQL BLOB interface In PostgreSQL, you can use various means to store binary […]
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 […]
Will more disks get you better PostgreSQL performance?
“Our database is slow. What if we just buy more disks? Is it going to fix things?”. I think every PostgreSQL database consultant in the world has heard this kind of question already more than once. Of course the very same questions are asked by our customers here at CYBERTEC. While more disks are surely […]
PostgreSQL underused features – WAL compression
The WAL compression feature: With the launch of Postgres 9.5, a new setting called “wal_compression” was introduced, that should decrease the IO load on behalf of CPU load. That is a desirable compromise considering typical constraints of modern hardware. But somehow the feature didn’t get the publicity it deserved – it wasn’t even mentioned in the […]
Shrink the storage footprint of PostgreSQL data
When writing data structures and table layouts, people usually don’t care about the order of columns. Why should they anyway? Columns can be reordered inside a SELECT at will, so why care? Well, you should care because the order of columns might have significant impact on the size of your table. That’s right: The size […]
Reducing space consumption
After digging through some customer source code yesterday I thought that it might be worth publishing a post about space consumption and enum types. It seems to be quite common to store status information as text fields. This is highly inefficient.