Blog

PostgreSQL v10: An Amplified Version of PostgreSQL

For more than a decade, the Postgres community has released new major versions almost annually to meet the evolving needs of the Database Industry. The first great release of Postgres was 8.3 in 2008, with a clean and evolutionary release of 8.4 eighteen months later. The 2017 v10 release of Postgres is the first version that I would consider truly, “Enterprise Ready.”

PostgreSQL has seen great success in the commercial enterprise for years. However, that success has largely been relegated to departmental success; e.g. the installation is solving a specific problem for a specific department. Often it’s the public facing data source for a larger Oracle instance or to manipulate GIS data. In these roles, PostgreSQL ...

Read More

Speaking at PGConf Seattle, are you going?

Jim Mlodgenski in attendence at the 9th Annual PGConf.EU and I am working hard on updating my slides for my presentation at PGConf Seattle. PGConf Seattle is being held at the downtown Seattle Sheraton on November 13th and 14th.  I will be speaking on Postgres version 10. I will also be training on Postgres Performance and Maintenance (you can buy your ticket here). If you are planning on attending my training or any of the others I recommend that you register quickly. There are only half a dozen seats left for mine and only a few more than that for Robert Bernier's.

The Postgres V10 presentation is the same presentation I gave at PGConf Ohio as well as ...

Read More

Postgres is better than MySQL but not because of how long it took to fix a bug

Many argue which is better: PostgreSQL or MySQL. A recent post by MySQL evangelist and community manager Frederic Descamps prompted some criticism of the amount of time that it took for a particular bug to be fixed -- 14.5 years to be exact, from the initial report.

There’s a long list of technical and performance comparisons, but here’s the number one reason to choose Postgres over MySQL.

Community Drives Change

The PostgreSQL community is different than MySQL. First, MySQL is not a project; it is a product. Yes, they are both Open Source but Postgres is vendor neutral, contributor centric, and not controlled by any single entity. Using a product or project defines the culture you are embracing when you ...

Read More

London PostgreSQL Meetup

Devrim Gunduz

One of the fantastic characteristics of Postgres leaders is their willingness to serve the community.

Yesterday I found out that one of our former team members, Devrim Gunduz, has created a new London PostgreSQL user group and they had their inaugural meeting in October. At the time of this writing there were 123 members in the group. This level of response shows a great demand for Postgres content. I spoke with Devrim yesterday, and he shared that he has a mission to provide the London community with new content each month. It is a bold goal as running meetups that frequently can be daunting, but we believe there is enough support in the area to warrant this frequency. Devrim has ...

Read More

Silicon Valley Postgres Meetup: How to Auto-cache Postgres with no code changes

The first meeting of the Silicon Valley Postgres Meetup was last night. Amazon Web Services sponsored the facilities in Cupertino and Roland Lee from Hemdalldata presented on:

How to Auto-Cache Postgres with no code changes.

Roland Lee

There were about 20 people in attendance as well as another half a dozen that participated via Amazon Chime. Debbie Cerda, our Director of Business Development flew out from Austin, Tx to host. When we launched the Silicon Valley Meetup we wanted to ensure that it would not conflict with the well respected San Francisco PUG. Based on initial response, there is not a conflict and we are very happy about that. We attribute the lack of conflict two items:

  1. San Francisco is not part ...

Read More

Postgres at Seattle Web Developers Meetup, recap

Postgres: The center of your data universe

Postgres at Seatte Web Developers

This talk is proving to be great content for those who are not necessarily Postgres Users. Last night I presented this talk at the Seattle Web Developers Meetup. The location was Adobe, next to Google and Tableau. I didn't even know there was a small tech complex on N. 34th in Seattle. There were about 27 people, which falls in line with the 50% rule of Meetups[1]. Here are my observations from the audience:

  1. Surprisingly the majority of developers were Python web developers (as a group)
  2. Many of them do not use Postgres and wanted to hear more about it
  3. There were several government employees that were wondering how to get ...

Read More

Postgres, upcoming community awesomeness

Upcoming community awesomeness

Now that summer is over and we have officially decided never to schedule anything in August again, we need to share a bunch of upcoming community goodness!

Goals

Never lose sight of the goal

 

  • PGConf US and NYCPUG are hosting: PGConf US Mini: NYC tomorrow! Bruce Momjian will be speaking on Postgres v10. This is the second mini that PGConf US has organized. The first was last May in Austin. It looks like a great lineup and turnout.

 

  • I ...

Read More

Postgres v10: An Amplified version of PostgreSQL at VanDev and Vancouver Postgres tonight!

If you are looking for all the skinny on Postgres v10, I have just the meetup for you. I will be giving my presentation: Postgres V10: An Amplified version of PostgreSQL tonight at a joint meeting of Vancouver Developers Network and Vancouver Postgres Meetup. Be there or be square.

In this presentation I go over all the major features in Postgres v10 including but not limited to:

  1. BigData
  2. Replication and Scaling
  3. Administration
  4. SQL Features
  5. XML and JSON

Join the meetups, its fun and will make you a better human.

Read More

Announce: Denver Postgres User Group

After much deliberation with the CMD community team we have launched the Denver Postgres User Group! We hope that our community in Denver, Boulder, and Colorado Springs will join us at upcoming events and submit content. It has been a long time since we have had an active Denver group and Denver is a hot bed for Postgres external development. Our first meeting will be announced soon and should be expected in October. Have ideas on facilities or content? Please contact us on the meetup page. We would love to see 3 - 4 meetings before PGConf US National!

Read More

Postgres deferred PRIMARY KEYS, a hidden gem

Oracle 7.3 supports it!

That is how this all started. A gentleman tweeted about a Postgres limitation that Oracle has not had since at least since Oracle 7.3

The problem

As you can see in the tweet, Postgres by default will not defer a PRIMARY KEY check. Without the check being deferred the following will not work:

postgres=# select * from demo; 
id  
----
 1
 2
(2 rows)
postgres=# alter table demo add primary key(id);   
ALTER TABLE
postgres=# begin;
BEGIN
postgres=# update demo set id=id-1;
UPDATE 2
postgres=# update demo set id=id+1;
ERROR:  duplicate key value violates unique constraint "demo_pkey"
DETAIL:  Key (id)=(1) already exists.

  The solution

The solution, as mentioned ...

Read More

Postgres autovacuum, bloat and tpc-c style workloads

For most workloads the Postgres Autovacuum daemon works just fine. You go about your day with 3 workers that wake up once a minute to make sure that everything is nice and tidy. If things are dirty enough (around 10%) then one of the workers gets in gear and cleans things up. Unfortunately, if you have an inverted load from the norm, Autovacuum may not be able to keep up and you will suffer increased fragmentation and bloat.

The norm

The most common workload for Postgres (especially web based apps) includes many reads and some writes. It is usually somewhere around 75-95% reads and 5-25% writes. Assuming an average database, Autovacuum will keep up just fine and with tuning will ...

Read More

Where is the Postgres community?

A recent poll was conducted @amplifypostgres to determine where the Postgres community should have its interactive communication. Options included were Google Hangouts, Slack, Reddit or “Other”.

The results were not surprising, with Google Hangouts beating Slack with 157 votes cast. There were also notable mentions of IRC, and Gitter. A couple of long time Postgresql.Org members asked the inevitable, “What is wrong with IRC?” Of course there is nothing wrong with IRC but when you tell many community users to use IRC, the most common response is “IRWhat?” which is either a sign of disdain or ignorance depending on the user.

The problem and what is driving this post was an additional comment made by a long time community ...

Read More

PostgreSQL: The Center of your Data Universe @ Datalayer Conference

You should always be careful of what you ask for. A couple of months ago while I was feeling particularly brave, I submitted to present at the DataLayer Conference. The next thing I knew, I was speaking at the DataLayer Conference. The conference takes place in Austin on May 17th. Conferences like this one are an awesome channel for PostgreSQL advocacy. Of course I am partial to PgConf US but a conference such as DataLayer allows us to reach people who may be using one of those "other" databases. 

Here is the synopsis of the presentation I will be giving:

PostgreSQL: The Center of Your Data Universe

Although there are still battles to be fought, the war has already ...

Read More

Indirect Advocacy

Last week I spoke at the Bellingham Young Professional Group on starting and running a business. It was a well attended presentation. I was nervous at first because although I do a lot of public speaking, I usually speak to technical people. This was a wholly different crowd and I was pulling from a different set of expertise. The crowd was largely under 30 and wanting to start a business of some sort. The presentation went over well and by far the most common feedback was, "I didn't even consider that, thank you". It is a good feeling to know you are helping people.

Indirect Advocacy: not directly caused by a recommendation of a particular cause

By stepping out ...

Read More

PostgreSQL for Oracle people

Below is the video of the webinar I did recently on PostgreSQL and Oracle. This webinar went very well. This is the first time I had ever performed a webinar that I recall. It was an interesting experience.  

PostgreSQL for Oracle Developers and DBA's

 

If you would like more information on this topic or any other topic surrounding PostgreSQL and Open Source, don't hesitate to contact us.

Read More

Do not buy the closed source lie of free videos

There is a nice lie out there. A lot of people want to believe it. They think by believing this lie it will somehow increase something for them. In some ways that is true. If you want what you are doing to be about you. If you are a believer in Open Source, it isn't about you. It is about the community and bettering that community as a whole.

If we provide the videos of our sessions for free, you won't attend the conference.

The PgConf US conference grows every year and guess what, they provide their videos for free.

If you pay for the conference, we will provide the videos for free.

LinuxFest Northwest which is larger ...

Read More

What should I submit to PgConf US 2017?

From the title, that is the question. This is the last week of the PgConf US 2017 CFP (you can submit here: http://www.pgconf.us/2017/submit/) and I have no idea what to submit.

I am blessed that my talks are very well attended, the audience is engaged and we all have a good time. Many times laughing at me because I have a hard time staying on one specific topic (especially if someone brings a kid into the room). There is the disclaimer I have to put up on my slides because there are some in the community that can't handle humor or PG-13 content but we must all love our neighbor and enjoy them ...

Read More

Snap packages for 9.3.15, 9.4.10, 9.5.5 and 9.6.1 available

The snap packages for 9.3.15, 9.4.10, 9.5.5 and 9.6.1 are now available. To install them:

sudo snap install postgresql$version

Where $version is one of 93, 94, 95 or 96.

The snap packages for PostgreSQL are a community project being lead by Command Prompt. You can visit the repo at github.

To learn more about snap packages please visit the Ubuntu snapcraft developer FAQ.

Read More

Can I make initdb quiet?

A #postgresql user today asked:

noob question - trying to write a Dockerfile that runs postgres... how do I get the effect of a non-interactive `service postgresql initdb` call?

  While several other community members provided the truthful but not helpful answer of, "Just throw Docker in the Trash", I worked out the following hack/trick/snipe hunt. The answer is, you can't. You have to call initdb directly. This took a few tries because PostgreSQL does not ship -q (quiet) flag with initdb. It will always make noise even when you don't want it to. However, if you call initdb directly, pass a few flags that have nothing to do with actually being quiet and redirect STDERR then ...

Read More

psql tips: Change the location and filtering of the history file

Anyone who uses PostgreSQL knows of the best client available: psql. This is the client that ships with PostgreSQL. Yes it is a command line client (which turns some people off) but that also means that it is the most efficient at everyday tasks for a DBA. What a lot of people don't know is that psql is rather configurable. Here is an example:

Problem 1: I want my history file in a place other that ~/.psql_history

Problem 2: I want my history file to be per database not global

Solution 1: Edit the .psqlrc file and change the history file settings

\set HISTFILE ~/psql_history/.psql_history

This will put your .psql_history file into the directory psql_history under your home ...

Read More

Will Postgres use the second element of an index if it is the only element in the WHERE clause?

This is a test table from an Oracle to Postgres migration. The table has had a dozen or so columns removed for the illustration of this test case. I did not design this table but the customer is fixing it (adding proper primary key, changing to boolean and integer where appropriate etc...).

Table "public.costcenter"

       Column       |            Type             |     Modifiers
-------------------+-----------------------------+--------------------
  costcenterid      | numeric                     | not null
  costcenterno      | character varying(100)      | not null
  amount            | numeric(18,2)               |
  closed            | boolean                     | not null
  enteruser         | integer                     |
  phonelines        | numeric                     |
  cckeyid           | character varying(100)      |
  country           | character varying(2)        |
  level4            | character varying(100)      |
  level5            | character varying(100)      |
  level6            | character varying(100)      |
  level7            | character varying(100)      |
  level8            | character varying(100)      |
  level9            | character varying(100)      |
  latitude          | character varying(20)       |
  longitude         | character varying(20 ...

Read More

Rich in the Jungle: A AWS to Softlayer comparison for PostgreSQL

I have updated my Rich in the Jungle presentation with new pricing for AWS vs. Softlayer. Things haven't changed much, in terms of raw performance per dollar (which is not the only qualifier). Softlayer is clearly the winner.

Read More

The fall of Open Source

Once upon a time FOSS was about Freedom. It was about exposing equality within source code. It allowed everyone equal rights and equal access to the technology they were using. An idea that if you were capable, you could fix code or pay someone to fix code. An ideology that there was something greater than yourself and that there was an inherent right built into what it is to be human with software.

Leaders to lemmings

I sat in a bar slowly nursing beers with other community members over a period of hours. We spoke of many things. We spoke of the never-done new PostgreSQL website. We spoke of my distaste for Amazon Web Services since reformed, with the exception ...

Read More

What is good for the community is good for the company (profit is the reward)

As the PostgreSQL community continues down its path of world domination I can't help but wonder whether the various PostgreSQL companies are going to survive the changes. Once upon a time there was an undercurrent of understanding that what was good for the community was good for the company. Whatever company that may be. However, over the last few years it seems that has changed. It seems there is more prevalance toward: What is good for the company is good for the community, or in other words, "The goal is profit."

That is a flawed discipline to follow in the Open Source world. A truly beneficial, strong and diverse community has to eliminate that thought entirely. The goal is ...

Read More

PgConf.US: 2016 Kicking the donkey of PostgreSQL Replication

My slides from my presentation and PgConf.US 2016:

 

Read More