Jim PostgreSQL

/Jim PostgreSQL

Proliferation of NUMERIC

More and more I'm seeing people use a NUMERIC for their primary keys. This is a direct result of people leveraging automated tools for their Oracle to PostgreSQL migration. Oracle's NUMBER data type is frequently used as the primary key for a table so the tools just map to a PostgreSQL NUMERIC and calls it [...]

By | March 20th, 2017|Categories: Jim PostgreSQL|Tags: , , , |3 Comments

PostgreSQL Game Changer: Change Data Capture

One of the really cool things I learned at this year's PostgreSQL@SCaLE event was the new push of the PostgreSQL driver developers to integrate support for the PostgreSQL replication protocol. Dave Cramer first talked about in his JDBC talk and after talking to others, I found out the latest version of PsycoPG also has replication [...]

By | March 6th, 2017|Categories: Jim PostgreSQL|Tags: , , , , |1 Comment

Building PostgreSQL on Windows for Performance

Before PostgreSQL 9.1, adding additional features like data types was non-standard and awkward, but 9.1 brought extensions. By adding extensions as well as the plumbing for things like foreign data wrappers, the use of extensions have exploded over the last few years. This is great for people who are using PostgreSQL on Linux or similar [...]

By | February 17th, 2016|Categories: BigSQL, Jim PostgreSQL, OpenSCG|Tags: , , |3 Comments

PostgreSQL Studio 2.0 is out

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

By | December 1st, 2015|Categories: Jim PostgreSQL, OpenSCG, PostgreSQL|Tags: , , , |1 Comment

PostgreSQL PL/pgSQL Profiler

Note: there is a new version of plProfiler that makes it even easier to speed up your PL/pgSQL code. Read the release notes here The original post about an older version of plProfiler is below: Some of our customers really like writing their business logic inside of PostgreSQL. While this is really cool that PostgreSQL [...]

By | February 20th, 2015|Categories: Jim PostgreSQL, OpenSCG|Tags: , , , |Comments Off on PostgreSQL PL/pgSQL Profiler

Synchronous Commit

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

By | November 10th, 2014|Categories: Jim PostgreSQL, OpenSCG, PostgreSQL|Tags: , |Comments Off on Synchronous Commit

PostgreSQL Dollar Quoting

I recently attended a excellent meetup about Redshift and one of the comments by the presenter was the trouble of the running of the UNLOAD command. The trouble they were having was that the UNLOAD command takes an SQL statement as a parameter, but if that SQL statement has strings, you need to escape everything [...]

By | October 7th, 2014|Categories: Jim PostgreSQL|Tags: , |Comments Off on PostgreSQL Dollar Quoting

Trigger Overhead (Part 2)

I found a bit more time dig into timing of triggers and their overhead so I wanted to see how much overhead the choice of procedural language affected performance. I followed the same testing methodology from my original trigger test. For this test I created an empty trigger in the following languages: PL/pgSQL CREATE FUNCTION [...]

By | May 21st, 2014|Categories: Jim PostgreSQL|Tags: , |Comments Off on Trigger Overhead (Part 2)

Trigger Overhead

I recently had discussions with some folks about triggers in PostgreSQL. They had two main questions. What is the overhead of putting a trigger on a table? Should a trigger function be generic with IF statements to do different things for INSERT, UPDATE and DELETE? So I created a simple test to verify some assumptions. [...]

By | May 7th, 2014|Categories: Jim PostgreSQL|Tags: , |Comments Off on Trigger Overhead

PostgreSQL Videos

With all of the new users to PostgreSQL, one of the things we wanted to do for PGConf NYC this year was to have a video showing the history of PostgreSQL. We ended up with a very professional video showing how the features have grown over the years and by the applause of the attendees [...]

By | April 16th, 2014|Categories: Jim PostgreSQL|Tags: , , |Comments Off on PostgreSQL Videos