How to: Pick a PostgreSQL Python driver

Using Postgres with python is easy and provides first class database capabilities for applications and data processing. When starting a new project, you need to choose which PostgreSQL python driver to use. The best choice depends on your deployment, python version, and background. Here's a quick guide to choosing between three popular python PostgreSQL drivers: [...]

By | January 10th, 2017|Categories: Dave PostgreSQL|Tags: , |8 Comments

pg_repack in Postgres by BigSQL

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

By | January 9th, 2017|Categories: BigSQL, Jobin PostgreSQL, OpenSCG, PostgreSQL|0 Comments

PostgreSQL Schema Visualization

I spend a lot of time trying to learn what's already been implemented, as DBA's, we tend to live in that world. It's important that you have tools that allow you to quickly get the visual so you can get on with your job. One of the biggest 'reverse engineering' tasks that DBA's have to [...]

By | December 22nd, 2016|Categories: Scott PostgreSQL|1 Comment

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