Sammlung von Newsfeeds
Our PostgreSQL blog about “Speeding up count(*)” was widely read and discussed by our followers on the internet. We also saw some people commenting on the post and suggesting using different means to speed up count(*). I want to specifically focus on one of those comments and to warn our readers.
Let's imagine a scenario in which you are dealing with JSON in your application and you want to store it in your database. You let out a heavy sigh and think, "I guess I am going to have to add something besides my favorite DB (Postgres) to my architecture. I wish I could just keep using PostgreSQL."
SSDs have different performance characteristics than magnetic disks, and using them optimally isn't always clear. SSDs have several performance benefits:
While I continue to be amazed at how fast PG runs on Amazon Aurora (no WAL logging) or how fast I can create a snapshot or standby (shared storage), there are always a few clouds around. Today the cloud I’m lookin’ at is replica reboots. Every week or so, one of my standbys gets rebooted and I get notified. It is always due to the same reason: “reboot due to slave lagging.” Apparently, aurora will automatically restart a standby if it gets more than 10 seconds behind the primary, that’s right just 10 seconds. And it is not configurable, burned in
Last time I announced to checkout MobilityDB to improve our approach to extract overlapping passage times of healthy and infected individuals – here we go!
MobilityDB itself is a PostgreSQL extension built on top of PostGIS, specializing on processing and analysing spatio-temporal data. To do so, the extension adds a bunch of types and functions on top of PostGIS to solve different kinds of spatial-temporal questions.
In one of our recent releases of the open source Citus extension, we overhauled the way Citus executes distributed SQL queries—with the net effect being some huge improvements in terms of performance, user experience, Postgres compatibility, and resource management. The Citus executor is now able to dynamically adapt to the type of distributed SQL query, ensuring fast response times both for quick index lookups and big analytical queries.
A classic spatial query is to find the nearest neighbours of a spatial feature. Our previous post "Won’t You Be My Neighbor? Quickly Finding Who is Nearby" discussed this capability from a PostgreSQL perspective.
If you reboot your PostgreSQL aurora instance, your distribution stats will still be there, but your monitoring stats will not. Official PostgreSQL documentation states that if you do a normal shutdown, both distribution and monitoring stats will still be there when you restart your instance (Monitoring Stats):
If you are using psql command line utility and wonder how you can set application_name…
If you try to set application_name using psql variable, it doesn’t work …
PostgreSQL Vacuum is a vast subject. There are many things that can be discussed in vacuuming. But in this post, I am going to touch something interesting.
Before we begin, I have a question for you.
Is vacuum table_name releases space to disk?
And if your answer is NO, then you are wrong.
The answer is MAYBE (terms and conditions apply).
I have noticed that there is a page on the offical PostgreSQL documentation (https://www.postgresql.org/docs/current/logical-replication-restrictions.html) that states several restrictions to the current logical replication design. One of the restrictions is about sequence relation type where any changes associated with a sequence is not logically replicated to the subscriber or to the decoding plugin. This is an interesting restriction and I took the initiative to look into this restriction further and evaluate if it is possible to have it supported.
There are so many multi-host technologies and it is hard to remember the benefits of each one, so I decided to create a list:
High availability: Streaming replication is the simplest way to have multiple copies of your database, ready for fail over
Usually in this blog I’m writing about various fun topics around PostgreSQL – like perhaps new cool features, some tricky configuration parameters, performance of particular features or on some “life hacks” to ease the life of DBA-s or developers.