Many DBAs agree that one of the most useful extension in their arsenal is pg_repack. Pg_repack addresses a serious shortcoming in Postgres: doing VACUUM FULL online. Due to the way Postgres handles the MVCC, tables and indexes become bloated. Dead tuples are addressed by AUTOVACUUM and space will be marked free. In many situations a [...]
In a previous blog post we talked about the technical details behind the new CassandraFDW that enables PostgreSQL to connect to a Cassandra cluster. This provides PostgreSQL users the ability to easily read and write data to a very popular open source multi-master database that can support writes in multiple data centers. This means if [...]
In any modern PostgreSQL installation, there's usually at least one streaming replication standby server feeding off your production master. In fact, there's usually several. Being the good PostgreSQL administrator that you are, you've already enabled logging of replication commands in postgresql.conf thusly: log_replication_commands = on But what you're probably not doing is having each standby [...]
Finding performance problems in deeply nested PL/pgSQL business logic is difficult.One of the reasons is that PL/pgSQL hides anything happening in the PL code. None of the SQL queries inside of all the nested function and trigger calls ever shows up in...
Recently we had a customer request to build a custom extension against Postgres by BigSQL distribution. Even though BigSQL ships with a large set of commonly used extensions and good collection of FDWs, these kind of user build requirements always crop up, based on how powerful the Postgres extension model is. BigSQL makes it easy [...]
The Postgres landscape has become very competitive in recent years as the database project has matured and grown exponentially in popularity. Since more and more users are deploying Postgres to support a wide variety of applications, there is a need for experts to help make sure those deployments are successful. This has led to the [...]
In the short time that I am enhancing the PL profiler (https://bitbucket.org/openscg/plprofiler) I have been asked multiple times if it can be abused as a debugging aid. Not directly. The conversation goes something like this:Q: When my PL code is stuc...
PostgreSQL Studio is a fully open source web based UI for PostgreSQL that is ideal for Postgres in the cloud. With the impending release of PostgreSQL 9.5, PostgreSQL Studio 2.0 has just been released supporting some of the new features in Postgres. The big new feature that requires UI changes is support for Row Level [...]
Pre-reqs CENTOS su - yum groupinstall "Development Tools" DEBIAN sudo apt-get install build-essential OSX Install Xcode and enable gnu command line utilities MSYS2 pacman -S base-devel gcc Building from Source ./configure make make install
While I was at PGConf.EU a couple of weeks ago in Madrid, I attended a talk by Grant McAlister discussing Amazon RDS for PostgreSQL. While it was interesting to see how Amazon had made it very simple for developers to get a production PostgreSQL instance quickly, the thing that really caught my eye was the [...]