Monthly Archives: November 2016


Is my query stuck? PostgreSQL 9.6

One of the most common queries that I keep in my pocket is SELECT pid, datname, usename, now() - query_start AS runtime, waiting, state, query FROM pg_stat_activity WHERE waiting='true'; This gives you a quick look at any queries that are ‘waiting’.  Specifically, you will see queries that are explicitly blocked on some other database activity. [...]

By | November 17th, 2016|Categories: Scott PostgreSQL|0 Comments

Integrating Cassandra, Spark, PostgreSQL and Hadoop as a Hybrid Data Platform – Part 3

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 [...]

Getting Postgres data into a shell script

I use postgres for a lot of different things. Many times, it's a full-blown application where I can directly access the database (i.e. python + psycopg2, java + jdbc, etc...). What if you're using something a bit less formal, like a shell script? psql psql is the native, command-line interface that ships with a postgresql [...]

By | November 9th, 2016|Categories: Scott PostgreSQL|Tags: , |3 Comments

Which standby was that again?

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 [...]

PL/pgSQL Profiler 3 (plprofiler) is released

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...

By | November 3rd, 2016|Categories: OpenSCG, PostgreSQL, PostgreSQL Blogs|Tags: , , |Comments Off on PL/pgSQL Profiler 3 (plprofiler) is released

Inaugural pgCMH mtg scheduled

As announced on our Twitter, we've scheduled our inaugural meeting for Jan 24, 2017 at 1800 hrs. The folks at CoverMyMeds will graciously provide the meeting space (and parking garage) while OpenSCG is buying the pizza!

At this first meeting, we'll be discussing what you, the members, would like to get from the meetings, we'll talk about future topics, and general organizational things. I know it's not exciting, but I need everyone's input to make this group something you get value from.

Our first mtg is happening on Jan 24 at @covermymeds with @openscg buying pizza. Bring a friend! RSVP at

— pgCMH (@pgCMH) October 19, 2016

Please RSVP via the MeetUP event so we have sufficient food and drink!

See you there!

By | November 3rd, 2016|Categories: PostgreSQL User Group|Tags: , , |Comments Off on Inaugural pgCMH mtg scheduled

PostgreSQL bloat estimates

The PostgreSQL storage manager is tasked with the complex job of satisfying ACID compliance. This is a well documented series of algorithms that I won’t go into here. Like any storage system or database, PostgreSQL tables can start to take on free space (bloat as it is sometimes known). While all free space is not [...]

By | November 2nd, 2016|Categories: Scott PostgreSQL|Tags: , , |0 Comments