Skip to content. | Skip to navigation

Personal tools
Log in
You are here: Home

Open Source Posts

Scott Mead: Don’t forget the globals!

From Planet PostgreSQL. Published on Oct 27, 2016.

pg_dump is a great tool.  You can get a consistent database backup from a live database without impacting your running traffic. Many people don’t realize however that a pg_dump isn’t actually a complete backup…


pg_dump operates on a single database in a postgres cluster.  You provide it with the name of a database and [by default] it dumps all the data, you can also select individual tables, schemas, etc… with different flags.  Essentially, given  a database instance like so:


pg_dump is capable of operating at this level of the tree:

Screen Shot 2016-10-27 at 3.53.00 PM


Okay great.  You’ll notice however that your backups don’t actually include the other objects that are associated with an instance:

  • Tablespaces
  • Group Roles
  • Login Roles (users)

The other problem is that pg_dump can operate on 1 and only 1 database at a time.


Based on the above, we have a few major issues

  1. pg_dump does not include tablespaces, groups or users
  2. pg_dump only dumps ONE of my databases in a cluster / instance


Groups / Users / Tablespaces

The simple solution is to use `pg_dumpall -g`

-g = ‘dump only global objects (users, groups, tablespaces)’

Essentially, pg_dumpall is designed to dump ‘all objects in a cluster‘.  if you run pg_dumpall with no arguments it will dump all data in all databases in plain text mode.  Frankly, I’ve always found this to not be very useful for large databases.  However, the -g flag says “only dump the global objects.

Since I don’t recommend pg_dumpall (in most cases) for your data backups, I recommend a strategy that looks like the following:

pg_dumpall -g > globals.sql

pg_dump -d billing -F c -C > billingdb.bak

This gives me the globals (users, groups, tablespaces) AND then I get a custom format (compressed with table of contents and selective restore) pg_dump of the billings database.  That leaves me with significantly more flexibility than a gigantic clear, text file of my data.  With this type of backup, I could choose to only restore the ‘ledger’ table from the billingdb.bak file if I wanted, or, I could restore the ‘ledger’ table definition with no data.  The options are huge and endless.  Just running a plain pg_dumpall would give me a giant text file that I’d need to sift through with an editor to accomplish the same goal.

Multiple DBs

Okay, great!  What if I have 5 databases per cluster that I need backed up ?

pg_dump only operates on one db at a time, I don’t want to hard-code my backup script!  No problem I say, you can use psql to generate a list of DB’s and run pg_dump in a loop.  My personal, complete version looks something like:
printf "Start: `date`" >> $backupDir/$dt/backup.log
pg_dumpall -g > /$backupDir/$dt/globals.sql
dbs=`psql -t -c "select array_to_string(ARRAY(select datname from pg_database),' ')" | sed -e '/^$/d' | sed -e 's/^[ \t]//g'`
for db in $dbs
printf "Performing backup of: $db\n"
pg_dump -v -Fp -b -C $db > /$backupDir/$dt/$db.sql 2>> /$backupDir/$dt/backup.log
printf "Complete: `date`" >> $backupDir/$dt/backup.log

This complete script includes rotation logic for simplifying your logical backup management.

Stephen Frost: A Committer's Preview of PGConf.EU 2016 - Part 3

From Planet PostgreSQL. Published on Oct 27, 2016.

Today, I am wrapping up my preview of next week's PGConf.EU conference. I'm really excited about all of the excellent topics and speakers that we get to choose from! Once again, here's the full Schedule.


On Friday morning, I have to recommend Arthur Zakirov and Oleg Bartunov’s talk on Better Full Text Search in PostgreSQL, even if you don’t use any Full Text Search today, they will be discussing the new RUM indexing capability which is currently being worked on.  If that isn’t your thing, then definitely check out Jan Wieck’s talk on Peeking into the black hole called PL/pgSQL - The new PL profiler.

Django test optimization with no-op PIL engine

By Peter Bengtsson from Django community aggregator: Community blog posts. Published on Oct 27, 2016.

The Air Mozilla project is a regular Django webapp. It's reasonably big for a more or less one man project. It's ~200K lines of Python and ~100K lines of JavaScript. There are 816 "unit tests" at the time of writing. Most of them are kinda typical Django tests. Like:

def test_some_feature(self):
    thing = MyModel.objects.create(key='value')
    url = reverse('namespace:name', args=(,))
    response = self.client.get(url)

Also, the site uses sorl.thumbnail to automatically generate thumbnails from uploaded images. It's a great library.

However, when running tests, you almost never actually care about the image itself. Your eyes will never feast on them. All you care about is that there is an image, that it was resized and that nothing broke. You don't write tests that checks the new image dimensions of a generated thumbnail. If you need tests that go into that kind of detail, it best belongs somewhere else.

So, I thought, why not fake ALL operations that are happening inside sorl.thumbnail to do with resizing and cropping images.

Here's the changeset that does it. Note, that the trick is to override the default THUMBNAIL_ENGINE that sorl.thumbnail loads. It usually defaults to sorl.thumbnail.engines.pil_engine.Engine and I just wrote my own that does no-ops in almost every instance.

I admittedly threw it together quite quickly just to see if it was possible. Turns out, it was.

# Depends on setting something like:
#    THUMBNAIL_ENGINE = 'airmozilla.base.tests.testbase.FastSorlEngine'
# in your settings specifically for running tests.

from sorl.thumbnail.engines.base import EngineBase

class _Image(object):
    def __init__(self):
        self.size = (1000, 1000)
        self.mode = 'RGBA' = '\xa0'

class FastSorlEngine(EngineBase):

    def get_image(self, source):
        return _Image()

    def get_image_size(self, image):
        return image.size

    def _colorspace(self, image, colorspace):
        return image

    def _scale(self, image, width, height):
        image.size = (width, height)
        return image

    def _crop(self, image, width, height, x_offset, y_offset):
        image.size = (width, height)
        return image

    def _get_raw_data(self, image, *args, **kwargs):

    def is_valid_image(self, raw_data):
        return bool(raw_data)

So, was it much faster?

It's hard to measure because the time it takes to run the whole test suite depends on other stuff going on on my laptop during the long time it takes to run the tests. So I ran them 8 times with the old code and 8 times with this new hack.

Iteration Before After
1 82.789s 73.519s
2 82.869s 67.009s
3 77.100s 60.008s
4 74.642s 58.995s
5 109.063s 80.333s
6 100.452s 81.736s
7 85.992s 61.119s
8 82.014s 73.557s
Average 86.865s 69.535s
Median 82.869s 73.519s
Std Dev 11.826s 9.0757s

So rougly 11% faster. Not a lot but it adds up when you're doing test-driven development or debugging where you run a suite or a test over and over as you're saving the files/tests you're working on.

Room for improvement

In my case, it just worked with this simple solution. Your site might do fancier things with the thumbnails. Perhaps we can combine forces on this and finalize a working solution into a standalone package.

Joshua Drake: Snap packages for 9.3.15, 9.4.10, 9.5.5 and 9.6.1 available

From Planet PostgreSQL. Published on Oct 27, 2016.

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.

Handling statuses in Django #2

By Eat Some Code from Django community aggregator: Community blog posts. Published on Oct 27, 2016.

As discussed in the previous article, django-fsm is a great plugin to handle statuses in Django. Let's use it with an API.

Greg Sabino Mullane: Postgres connection service file

From Planet PostgreSQL. Published on Oct 26, 2016.

Postgres has a wonderfully helpful (but often overlooked) feature called the connection service file (its documentation is quite sparse). In a nutshell, it defines connection aliases you can use from any client. These connections are given simple names, which then map behind the scenes to specific connection parameters, such as host name, Postgres port, username, database name, and many others. This can be an extraordinarily useful feature to have.

The connection service file is named pg_service.conf and is setup in a known location. The entries inside are in the common "INI file" format: a named section, followed by its related entries below it, one per line. To access a named section, just use the service=name string in your application.

## Find the file to access by doing:
$ echo `pg_config --sysconfdir`/pg_service.conf
## Edit the file and add a sections that look like this:

## Now you can access this database via psql:
$ psql service=foobar

## Or in your Perl code:
my $dbh = DBI->connect('dbi:Pg:service=foobar');

## Other libpq based clients are the same. JDBC, you are out of luck!

So what makes this feature awesome? First, it can save you from extra typing. No more trying to remember long hostnames (or copy and paste them). Second, it is better than a local shell alias, as the service file can be made globally available to all users. It also works similar to DNS, in that it insulates you from the details of your connections. Your hostname has changed because of a failover? No problem, just edit the one file, and no clients need to change a thing.

As seen above, the format of the file is simple: a named section, followed by connection parameters in a name=value format. Among the connection parameters one may use, the most common and useful are host, port, user, and dbname. Although you can set a password, I recommend against it, as that belongs in the more secure, per-user .pgpass file.

The complete list of what may be set can be found in the middle of the database connection documentation page. Most of them will seldom, if ever, be used in a connection service file.

The connection service file is not just limited to basic connections. You can have sections that only differ by user, for example, or in their SSL requirements, making it easy to switch things around by a simple change in the service name. It's also handy for pgbouncer connections, which typically run on non-standard ports. Be creative in your service names, and keep them distinct from each other to avoid fat fingering the wrong one. Comments are allowed and highly encouraged. Here is a slightly edited service file that was recently created while helping a client use Bucardo to migrate a Postgres database from Heroku to RDS:

## Bucardo source: Heroku

## Bucardo target: RDS

## Test database on RDS

## Hot standby used for schema population

You may notice above that "connect_timeout" is repeated in each section. Currently, there is no way to set a parameter that applies to all sections, but it's a very minor problem. I also usually set the environment variable PGCONNECT_TIMEOUT to 10 in by .bashrc, but putting it in the pg_service.conf file ensures it is always set regardless of what user I am.

One of the trickier parts of using a service file can be figuring out where the file should be located! Postgres will check for a local service file (named $USER/.pg_service.conf) and then for a global file. I prefer to always use the global file, as it allows you to switch users with ease and maintain the same aliases. By default, the location of the global Postgres service file is /usr/local/etc/postgresql/pg_service.conf, but in most cases this is not where you will find it, as many distributions specify a different location. Although you can override the location of the file with the environment variable PGSERVICEFILE and the directory holding the pg_service.conf file with the PGSYSCONFIDIR environment variable, I do not like relying on those. One less thing to worry about by simply using the global file.

The location of the global pg_service.conf file can be found by using the pg_config program and looking for the SYSCONFDIR entry. Annoyingly, pg_config is not installed by default on many systems, as it is considered part of the "development" packages (which may be named postgresql-devel, libpq-devel, or libpq-dev). While using pg_config is the best solution, there are times it cannot be installed (e.g. working on an important production box, or simply do not have root). While you can probably discover the right location through some simple investigation and trial-and-error, where is the fun in that? Here are two other methods to determine the location using nothing but psql and some standard Unix tools.

When you invoke psql with a request for a service file entry, it has to look for the service files. We can use this information to quickly find the expected location of the global pg_service.conf file. If you have the strace program installed, just run psql through strace, grep for "pg_service", and you should see two stat() calls pop up: one for the per-user service file, and one for the global service file we are looking for:

$ strace psql service=foobar 2>&1 | grep service.conf
stat("/home/greg/.pg_service.conf", 0x3526366F6637) = -1 ENOENT (No such file or directory)
stat("/var/opt/etc/postgres/pg_service.conf", 0x676F746F3131) = -1 ENOENT (No such file or directory)

What if strace is not installed? Well, perhaps gdb (the GNU debugger) can help us out:

$ gdb -q --args psql service=foobar
Reading symbols from psql...(no debugging symbols found)...done.
(gdb) start
Temporary breakpoint 1 at 0x435356
Starting program: /usr/local/bin/psql service=foobar
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib64/".

Temporary breakpoint 1, 0x4452474E4B4C5253 in main ()
(gdb) catch syscall stat
Catchpoint 2 (syscall 'stat' [4])
(gdb) c

Catchpoint 2 (call to syscall stat), 0x216c6f65736a6f72 in __GI___xstat (vers=, name=0x616d7061756c "/usr/local/bin/psql", buf=0x617274687572)
    at ../sysdeps/unix/sysv/linux/wordsize-64/xstat.c:35
35      return INLINE_SYSCALL (stat, 2, name, buf);
(gdb) c 4
Will ignore next 3 crossings of breakpoint 2.  Continuing.

Catchpoint 2 (call to syscall stat), 0x37302B4C49454245 in __GI___xstat (vers=, name=0x53544F442B4C "/var/opt/etc/postgres/pg_service.conf", buf=0x494543485445)
    at ../sysdeps/unix/sysv/linux/wordsize-64/xstat.c:35
35      return INLINE_SYSCALL (stat, 2, name, buf);
(gdb) quit

The use of a connection service file can be a nice addition to your tool chest, especially if you find yourself connecting from many different accounts, or if you just want to abstract away all those long, boring host names!

Stephen Frost: A Committer's Preview of PGConf.EU 2016 - Part 2

From Planet PostgreSQL. Published on Oct 26, 2016.

Today, I will continue with my preview of the exciting talks at the upcoming PGConf.EU conference. In Part 1, I discussed the talks that will happen on Wednesday. Today, I want to dive into the Thursday sessions.


Starting in early on Thursday morning,

Scott Mead: How does a database get promoted these days?!?

From Planet PostgreSQL. Published on Oct 26, 2016.

These days, replicated postgres is simple to setup, easy to maintain and ultra-reliable.  Actually, the biggest question is typically “how do I convert a slave to a master?”

Well, the short answer is, a couple of ways.  These days, the very simple ‘pg_ctl promote’ works best.


Essentially, promotion is the process of converting your read-only, slave database servers to a read-write capable server.  Essentially, this is going to break the replication and allow you to start using your slave.  This is the type of thing you would do in the event that your master DB failed for some reason.  Right now, the postgres core database doesn’t have built-in automated failover, that’s something left to the implementer to design and build.

So, in the event that postgres fails, how do we actually get a slave promoted?


When making the decision to failover it’s important to determine the state of the master.

  • Is the master really down?
  • Is the master flapping (down, up, down, up, etc…)
  • Is the server (or VM) up, but the postgres instance down, etc…

It’s important to determine the state of the master.  If you’ve decided that you don’t trust the master database anymore (for whatever reason), it’s time to promote the standby.


Split-brain scenario is a common concept when dealing with clusters / replication.  In the context of PostgreSQL master-slave replication, it refers to a situation where you:

  1. notice a master is down
  2. promote a slave
  3. the old master comes up

You now have two postgresql databases that are read-write.  The danger is that you may have some application or process that is still pointing to the old master, when the old master comes up, this process will start executing against the wrong node and now you’ve got some data in one database and some in another.  Split-Brain!

To prevent this you have to ‘fence’ or isolate the old master.  Once you’ve decided that you’re going to promote, it’s EXTREMELY CRITICAL that you ensure the old master cannot be accessed by any clients.  If the old master is available, you can end up with some of your applications talking to one DB and some talking to your secondary.

Isolate the master

Machine up

edit pg_hba.conf

Add 2 lines BEFORE any existing rules:

local all all deny
host all all deny

Machine Down

This is the most dangerous case.  When the old master machine is down, it’s critical that it stay down.  If someone or something were to restart the box and it came back up, you would end up with a split-brain scenario (i.e. two read-write postgres servers).  This is one of the areas that causes automated failover the most heartburn.  When you’re doing manual failover and this happens, I would perform any and all of the following (if possible):

  1. Physically sever the network connection to this host
  2. Virtually sever the network connection
    1. disconnect the virtual network adapter
  3. Block all traffic to/from this host via a firewall

It’s important to make sure that the host cannot ‘accidentally’ come back; if you’ve promoted a slave and this old master returns, you’re going to have a very dangerous situation on your hands.


I’m going to go through this as a ‘runbook’ so that it’s easy to follow:


  1. Ability to login to slave as ‘postgres’ user
    1. This is the user that is running the postgresql database
  2. Full Path to: pg_ctl (in the postgresql bin directory)
  3. Full Path to: data directory

Each of the above is very configurable based on where you got your postgresql installation.  Here’s a few defaults of the popular packaging mechanisms


  1. User             = postgres
  2. pg_ctl path = /opt/postgresql/pg9x/bin/pg_ctl
  3. data path     = /opt/postgresql/data


  1. User             = postgres
  2. pg_ctl path = /usr/pgsql-9.x/bin/pg_ctl
  3. data path     = /var/lib/pgsql/<version>/data

Steps to Promote

  1. We notice that master DB appears down
  2. Determine – Am I going to fix the master, or failover?
  3. If you’re going to fix the master, stop here, go fix it!
  4. If you’re going to failover
    1. Fence (isolate) the master (as discussed above)
    2. Login to the slave
    3. su – postgres
    4. pg_ctl -D /path/to/data promote

Now your slave will be put in to read-write mode.  At this point, you’e moved the database.  Now you need to move the connections from your application.  This will be the topic of another post, but, typically a simple update to your application’s configuration file gets you where you need to go.

Presidential debate questions influenced by open source platform

By Caktus Consulting Group from Django community aggregator: Community blog posts. Published on Oct 25, 2016.

During the past two presidential debates, moderators from ABC and Fox asked candidates Hillary Clinton and Donald Trump voter-submitted questions from The site, created by the bipartisan Open Debate Coalition (ODC), was built with the support of Caktus Group using an open source Django code base.

“This coalition effort is a first-of-its-kind attempt to ensure moderators can ask questions that are not just submitted by the public, but voted on by the public to truly represent what Republican, Democratic, and Independent families are discussing around their dinner tables. Open Debates are the future,” said Lilia Tamm Dixon, Open Debate Coalition Director.

Voters using submitted over 15,000 questions and cast more than 3.6 million votes for their favorite submissions. The selected debate questions had an unprecedented audience. According to Nielsen Media, 66.5 million viewers watched the second debate and 71.6 million the third debate.

The ODC and Caktus teams continue to make improvements to the platform, readying new versions for use in political debates around the country. For national media coverage on the Open Debate Coalition and to learn more about their goals, see articles from The Atlantic, The Los Angeles Times, and Politico.

Stephen Frost: A Committer's Preview of PGConf.EU 2016 - Part 1

From Planet PostgreSQL. Published on Oct 25, 2016.

Only one week left til PGConf.EU in Tallinn, Estonia!

Next week will be PGConf.EU’s 8th conference, having traveled to many different parts of Europe, and lately moving on a North-East trajectory, two years ago in Madrid, last year in Vienna, is now in Tallinn, Estonia with another fantastic line-up of talks.  


Here are the talks which I am most interested in this year.  Warning for the unwary, I’m a PostgreSQL Committer, so I tend to be quite developer heavy when it comes to my talk selections.

Scott Mead: Production psql Prompts

From Planet PostgreSQL. Published on Oct 25, 2016.

I deal with lots of production.  When you’re dealing with multiple production machines, it’s important to know many things:

  1. Who you are
  2. What you’re connected to
  3. Where you are
  4. Why you are connected
  5. When you are connected

Most prompts don’t give you any of this detail.  If you’re familiar with bash, you probably have heard of PS1, it’s an environment variable that lets you set what your prompt looks like.  psql has something very similar.  The question becomes, how do I get a useful prompt that is compact and doesn’t cause all of my commands to wrap off of the screen?

Let’s start with bash, most people are familiar with the default linux prompt (debian and redhat differ slightly in their defaults) of:

[root@ansible-test-1 ~]#

One day, I realized that wasn’t enough.  I had a lot of tabs open in my terminal emulator and I was ssh’d three ways from Sunday.  Many of those tabs were executing long running commands, and I couldn’t quickly look back to see how long it had been running for (unless I remembered to execute the `date` command prior to starting my job).  That’s when I came up with this prompt:


This tells me most of what I’d like to know.  I have:

[user@host : Full Path to current directory: Time the prompt was generated]
$ <your command gets entered here>


You can accomplish this with:

export PS1=[\u@\h : \[\e[4;33m\]\w\[\e[0m\] : \[\e[0;94m\]\t\[\e[0m\]]\n$

I did have one issue, in order to see the full path, I had to put the next command on the line below.  I’ve actually come to appreciate it quite a bit.  Essentially, I have a line that tells me: Who @ What: Where : When.  (I guess why I’m there is something I should probably already know : -)

Now, I decided to apply the same design to my psql prompt so that I knew what was going on.  psql prompt

You can accomplish this by putting the following in your .bashrc:

alias psql='psql -v PROMPT1=[`hostname -s`'\''@%m:%>]
%n@%/%=%# '\'''

It’s important to note, the line wrap, you have to actually enter that or this won’t work.  Essentially, I have to have bash set the ‘hostname’ in the prompt (which is why this isn’t in .psqlrc).

Why do I think this prompt is better than the default?

This prompt tells me:

  1.  scottsmac : What host psql is running from
  2. [local]         : What host psql is connected to
  3. 9520            : What port psql is connected to
  4. newline
  5. postgres      : What user psql is connected as
  6. @postgres  : What database psql is connected to

This, again gives me ‘who, what, where, why and when’ … okay, not the ‘why’ but that’s really up to you.





US PostgreSQL Association: Reminder: Today is the last day of nominations for 2016 PgUS Elections

From Planet PostgreSQL. Published on Oct 25, 2016.

Hello, everyone! Just as a reminder: Today is the final day to nominate (or self-nominate) for the 2016 PgUS board election. Please email your nomination(s) to the corporation secretary (me) at:
I will be posting platforms/bios on the PgUS website by October 26, so (if you're self-nominating) please include your bio and(/or) platform in your email.

Please let me know if you have any questions or concerns. Thanks for participating in our board election!

---Michael Brewer
Secretary, PgUS

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 10 – postgres_fdw: Push down aggregates to remote servers.

From Planet PostgreSQL. Published on Oct 25, 2016.

On 21st of October, Robert Haas committed patch: postgres_fdw: Push down aggregates to remote servers.   Now that the upper planner uses paths, and now that we have proper hooks to inject paths into the upper planning process, it's possible for foreign data wrappers to arrange to push aggregates to the remote side instead of […]

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 10 – Rename “pg_xlog” directory to “pg_wal”.

From Planet PostgreSQL. Published on Oct 25, 2016.

On 20th of October, Robert Haas committed patch: Rename "pg_xlog" directory to "pg_wal".   "xlog" is not a particularly clear abbreviation for "write-ahead log", and it sometimes confuses users into believe that the contents of the "pg_xlog" directory are not critical data, leading to unpleasant consequences. So, rename the directory to "pg_wal".   This patch […]

Usable QA Environments

By Will Larson from Django community aggregator: Community blog posts. Published on Oct 25, 2016.

Ok, everyone raise their hands.

If you don’t have a QA environment, put your hand down.

If your deploys don’t always go through QA, put your hand down.

If your QA environment is broken right now, hand down.

If you’ve considered deleting your QA environment and restarting from scratch, hand down.

If you have a production outage in the past week due to an issue your “QA env should have caught”, hand down.

Anyone left with a hand up?

Production environments have a clear mandate: keep your customers happy. I’d argue that quality assurance environments have an equally clear mission of keeping your developers happy, but it’s surprisingly common to hear that companies don’t have an effective QA environment or even don’t have one at all.

This is a shame, because along with unit tests to verify each component’s local correctness, a QA environment is a remarkably effective tool for developing working software.

They are an excellent testing ground to stage code changes for integration tests to run against (ideally triggered after every deploy to qa), and more importantly they are perhaps the only testing ground for system changes to your environment like configuration changes. They can also be great for load testing!

But they’re certainly not easy.

Once you do indeed have a QA environment setup and running for a while, the first challenge you often run into is that it’s broken or unusable far more frequently than your production environment. Often someone or a team ends up “responsible for the QA environment”, mirroring the historical developer/operations split of operations “owning production.”

The best solution I’ve seen is the same solution we’re seen gradually adopted to address the poorly aligned dev/ops split in production: sharing ownership for QA environments broadly across all teams who use it. Specifically, I believe that means your observability stack should alert owning teams for issues in QA, just like they would for a production outage. (Perhaps inverting the primary/secondary rotation so that the secondary would get QA pages first, reducing the likelihood of distracting the primary oncall during an outage. Hey, you could even use QA oncall as the training rotation for your production rotation and use this as a training mechanism!)

Alerting on QA issues is fairly controversial, but I think the argument is fairly straightfoward: any breakage in QA is a harbinger of a similar breakage in production, so you’re far better to get interrupted with a problem when you have time to diagnose and debug it than when it’s impacting your customers.

Once you’re treating QA like a critical environment worthy of active repair, the next major failure mode is having so little activity that it doesn’t accurate simulate your production environment.

There are loosely two approaches for addressing this: creating synthetic load and replaying production traffic. The former is easier to set up, but requires active maintenance and crafting of new test cases to exercise new functionality. The later is easier to setup, but it’s often quite a challenge to maintain environments such that traffic against production can be usefully replayed against QA (if QA is down for an hour due to a breakage which avoids production, then replaying requests may be more-or-less impossible due to inconsistent state).

The best strategy I’ve seen here is sanitized, partial daily (or fresher!) snapshots from production and then replaying traffic starting immediately after the time the snapshot started (very likely injecting a position marker into your traffic log to aid synchronization of snapshot and traffic logs). Sanitization is critical because it’s rare to treat QA snapshots with the same tender loving care that you treat production data, so you don’t want anything sensitive in there. Doing a partial subset is important because it allows you to keep the size of the dataset small enough to generate frequently and load onto laptops or small virtual machines.

If you can invest into getting it setup, this combination of alerting on QA issues to fix environment problems just as if they are production issues (and indeed, they are simply a prelude), recreating state on the daily from snapshots, and generating ongoing load by replaying request logs gives you a solid, usable and useful environment.

Perhaps equally interesting is the question why it’s still so hard to get an effective QA environment put together. As tooling like Docker, rkt and Kubernetes combine to make service provisioning relatively trivial, it’s easy to feel like we’re still operating in the naughts.

I’m rather optimistic!

We’re in the early phase of figuring out how to take full advantage of generalized scheduling tools like Kubernetes, and we’ll start to see some pretty amazing integrations and tools over the next few years. It’s only a matter of time before we see a Chaos Monkey for Kubernetes, load generating tooling which relies on k8s for service discovery (and back off if it causes too many nodes to go out of rotation) while simultaneously scheduling its own load generating nodes on k8s! Altogether, it feels like a rich vein of relatively untapped open source opportunities.

And I could use all of them.


Jobin Augustine: Extending Postgres by BigSQL

From Planet PostgreSQL. Published on Oct 25, 2016.

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 to build custom extensions for new requirements by including all of the required header files and libraries along with the Postgres by BigSQL installation. This is one of the key benefits of the open source BigSQL distribution as opposed to a proprietary open source-based approach.

One may think that, it will be a challenge because a BigSQL user has the freedom to relocate the the installation anywhere he/she wants or have a DevOps Sandbox. That means Postgres related files can exist anywhere the user wants. In this blog I’ll show you how easy it is. Here is an example of how to build extensions, where postgres binaries sits in my user home directory (I am using DevOps Sandbox on CentOS 6.7)

Building extension from Source

Extension build will use pg_config to understand the the location of the files. So only thing we have to do is to set the path to pg_config wherever it is:

export PATH=/home/vagrant/bigsql/pg95/bin:$PATH

After that, building any custom extension should be a breeze.
Get the required branch of extension:

git clone -b VERSION3_1_STABLE

Install any external library requirements

$sudo yum install openssl-devel.x86_64
$sudo yum install readline-devel

(of course, we need build tools like gcc and make)


The build should go through without any issues.

Getting Extension from PGXN (PostgreSQL Extension Network)

The Postgres community has many extensions available, and PGXN makes it easy to find and install open source extensions published by other community members. Postgres by BigSQL works perfectly with PGXN.

Just like above case, we need to add the path to pg_config before executing the PGXN

$ export PATH=/home/vagrant/bigsql1/pg95/bin:$PATH

Now we can install any extenion we need like:

$ pgxn install temporal_tables

Once installation complete, extension will be ready to use

postgres=# create extension temporal_tables;

Up Next

We just covered how easy it is to build and install PostgreSQL extensions on Linux. In my next blog post, I’ll show how to build and install extensions on Windows. Postgres by BigSQL uses Mingw (and gcc) to compile Postgres and related extensions on Windows which is great because it keeps the full tool chain open source (no proprietary compiler required) and enables extension authors to support Windows without making an MSVC specific build system or code and configuration changes.

Joshua Drake: Can I make initdb quiet?

From Planet PostgreSQL. Published on Oct 24, 2016.

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 you are golden. For example:
/usr/lib/postgresql/9.5/bin/initdb data -A md5 --pwfile=pwfile 2>&1 > /dev/null

The above says:

  1. Initialize a new data directory called data.
  2. Use the auth method of md5 to start
  3. Tell initdb to get the password for md5 from the password file of pwfile
  4. Redirect STDERR to STDOUT and then write that output to /dev/null

This will produce zero output on execution. You could still have for a RETVAL to see if it suceeded or change /dev/null to a log file.


ShipIt Day Recap Q3 2016

By Caktus Consulting Group from Django community aggregator: Community blog posts. Published on Oct 24, 2016.

This ShipIt day marks four years of ShipIt days at Caktus! We had a wide range of projects that people came together to build. Most importantly, we all had fun and learned through actively working on the projects. People explored new technologies and tools, and had a chance to dig a bit deeper into items that piqued their interest in their regular work.

React + Django = django-jsx

Calvin did some work inspired by a client project to create tools for working with React’s JSX DOM manipulation within Django projects. This bridge allows embedding of JSX in Django templates (even using Django template language syntax) to be compiled and then rendered on the page. Calvin released django-jsx up on Github and pypi, and is interested in feedback from people who use it.

Django ImageField compression

Dmitriy continued working on the TinyPNG compressed Django ImageField from the previous ShipIt Day. He’s shared his updates through the Github repository django_tinypng. This time Dmitriy worked on cleaning up the project in preparation for possibly submitting it to pypi. His work included adding documentation and a nice way to migrate pre-existing image fields in projects to the new compressed image field.

Python microservices with asyncio

Dan explored the asyncio capabilities of Python 3 via a long standing project of his. He had a previous project to control displaying videos. Issues came up when the player would lose connectivity and Dan wanted his program to be able to dynamically recover. Dan dug into the asyncio documentation head first, but was a bit overwhelmed by the scope of the library. Luckily, he found an excellent write up by Doug Hellmann in his Python Module of the Week series. Dan used what he learned to build an event loop, and focused on making his project more resilient to handle errors gracefully.

More Python microservices with asyncio

Mark created a set of microservices working together including a worker, a web server to handle webhook requests, a web hook generator, and a logging server. These services communicated together using rabbitmq and asyncio. The work that Mark did on this was a fun but relevant diversion from his prep for his upcoming All Things Open talk next week on RabbitMQ and the Advanced Message Queueing Protocol.

Microsoft Azure provisioning and deployment

David worked with Microsoft Azure and compared it with our standard provisioning and deployment practices. He learned about how configuration and management tools around Azure compare to those of other cloud providers. As a test case, David built a test application using our SaltStack based django-project-template and worked on getting the test application up and running to identify any pain points.


Neil shared with the team his explorations into using Elixir. Elixir is a functional language built on top of Erlang’s virtual machine (VM), but without some of the peculiarities of the older Erlang language. The Erlang VM was developed with extreme fault tolerance in mind for creating telecom software (eg. the electronic phone switching system) that would never go down and could even be upgraded without downtime. Neil delved into this high availability mindset by creating a test project with worker processes handling data storage and supervisor processes in place to restart failed worker processes. Overall, Neil found the exploration useful in that understanding a wide range of programming language paradigms helps you to think through challenges in any language, in different ways.

Selenium testing

Rebecca and Alex both worked on updating or adding front-end tests to projects via Selenium. Rebecca looked at updating the tests in django-scribbler in preparation of an upgrade of the open source project to support Django 1.10. Alex looked into using information from Mark Lavin’s 2015 DjangoCon talk on front-end testing and amazing documentation to add front-end tests to an existing project.

Implicit biases

Charlotte F., Liza, and Sarah facilitated Caktus team members taking an implicit bias test from Harvard’s Project Implicit. Caktus team members participated by taking the test and anonymously shared their results. Charlotte and Liza reviewed the responses and compared them to the average responses for all project implicit respondents. This was a great way for team members to examine and become aware of implicit biases that may arise when interacting with people inside and outside of Caktus.

Team availability forecasts

Sarah worked on a new tool for forecasting team availability via a tool that uses project schedules in their native format so that they do not need to be reformatted for forecasting. In working on this project, Sarah had a chance to learn some new spreadsheet creation tools and practice laying out a sheet in a way that can be sustainably maintained.

UX design case study for cat adoption

Charlotte M. and Basia Coulter invited everyone at Caktus to participate as clients would in a UX discovery workshop. Alley Cats and Angels is a local animal rescue that Caktus is particularly fond of. Alley Cats and Angels has an internal database to track information about cats getting enrolled in its programs, foster homes, adoption applications, adopters etc. It also has a public-facing website where its programs are described and relevant application forms can be submitted, and where cats available for adoption are featured. But there is no automated communication between the database and the public-facing website, and not all important information is being tracked in the database. That results in significant overhead of manual processes required to keep all information in order, and to facilitate programs. Using a story mapping technique, Charlotte and Basia worked with Caktii to map out a web-based application that would allow for an integration of the internal database and the public-website, automation of critical processes, and more complete information tracking. They identified important user flows and functionality, and broke them down into individual user stories, effectively creating a backlog of tasks that could be prioritized and leveraged in a sprint-based development process. They also determined which features were necessary for the first iteration of the application to deliver client value. By doing so, they defined a version of the minimum viable product for the application. At the end of the workshop they took some time to sketch paper prototypes of selected features and screens. The result of the workshop was a comprehensive set of deliverables (user flows, backlog of user stories, minimum viable product, and paper prototypes) that could serve as starting point for application development.

Alexey Lesovsky: 0.3.0 is out!

From Planet PostgreSQL. Published on Oct 24, 2016.

The new pgCenter version is now released - here is what to expect

Couple of days ago a new version of pgCenter has been released. Though it’s still only 0.3.0, it includes many new features and improvements and I hope you will enjoy using this version.

Here are some key highlights:
  • Advanced sorting and filtering
  • Compatibility with PostgreSQL 9.6
  • pg_stat_replication improvements
  • pg_stat_statements improvements
  • Summary info about running (auto)vacuum tasks
  • Support of libpq environment variables
  • pg_stat_activity and pg_stat_statements informative by default
  • Errors reporting when internal errors occur
  • and of course, many internal refactoring and code improvements

Let’s take a closer look at some of these.

Advanced sorting and filtering
 In previous versions pgCenter used poor sorting and was unable to sort text data. In the new release qsort_r() was implemented that allowed sorting of any available fields. In addition, the new version now has filtering option. Press 'F' hotkey, enter the pattern you are looking for and you will find what you’re looking for. It is especially useful for searching databases, tables, indexes and queries.

Compatibility with postgresql - 9.6
As you know, the new version of PostgreSQL - 9.6 has been released two months ago and it had a few stats’ improvements. First, is the wait_event and wait_event_type fields for running backends in pg_stat_activity. Second, is the pg_stat_progress_vacuum system view that provides progress reporting for VACUUM operations. Both of these features are implemented in the 0.3.0 version of pgCenter. Depending on your postgres version you will see either a waiting column (before 9.6) or wait_event_type/wait_event columns (for versions 9.6 or newer). New pg_stat_progress_vacuum is available through 'V' hotkey and only if you are working with 9.6.

pg_stat_replication improvements
As you probably know, pg_stat_replication is used for controlling hot standbys, by default showing only XLOG locations of standbys. Fortunately, postgres has three additional helpful functions: pg_current_xlog_location(), pg_last_xlog_receive_location() and pg_xlog_location_diff(). With their help pgCenter calculates detailed replication lag for connected standbys, even if you are connected to the standby and trying to check lag in cascaded replication.

pg_stat_statements improvements
pgCenter has five screens for pg_stat_statements:
  • Timings stats that show how much time queries consumed;
  • General stats that show number of running queries and number of returned rows;
  • General input/output stats on block (cache/disk) IO utilization;
  • Temp IO stats that show details about queries that were forced to use temp files in cases when work_mem wasn’t sufficient;
  • Local IO stats that include data on queries that work with temporary tables - it isn't the same as temp files.
Note that you can use 'x' hotkey for switching between pg_stat_statements stats and 'X' hotkey for browsing between different stats in the menu.

Summary info about running (auto)vacuum tasks
This feature can be seen at the top-right area of pgCenter and it allows you to quickly check how many manual or auto -vacuums run at any given moment including wraparound vacuums.

Support of libpq environment variables
Earlier versions of pgCenter supported connections settings only via startup parameters or through connection files. Basically, libpq has several environment variables for connection settings. pgCenter 0.3.0 supports same variables, so it is possible to define them in shell profile and run pgCenter without params.

pg_stat_activity and pg_stat_statements informative by default
The less important WHERE conditions were removed from queries for pg_stat_statements (do not show queries with calls less than 50) and for pg_stat_activity (do not show backends with transaction age or query age less than 5ms), that allows you to see the full picture straight away.

Errors reporting when errors occur
If something goes wrong in pgCenter, for example, send queries to non-existent table (hello pg_stat_progress_vacuum), you will see detailed error message instead of an empty screen.

Internal refactoring and code improvements
There are many internal refactoring and improvements, e.g. unsafe functions have been replaced by safer analogs, error handling has been added where undefined behaviour could occur, and many others.

PgCenter is definitely improving, so I would highly recommend giving it a go. At the same time, I’d like to remind that it is beta software, so if you do come across undefined behaviour or segfaults, I’d welcome your bug reports here.

If you are going to PostgreSQL Europe in Tallinn next week I welcome you to join me in my hands-on session on Friday, 4 November where I will be taking you through these and other useful features of pgCenter.

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

From Planet PostgreSQL. Published on Oct 24, 2016.

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

Solution 2: Edit the .psqlrc file again to:

\set HISTFILE ~/psql_history/.psql_history- :DBNAME

This will not only put all your history files within the psql_history directory, it will separate the history files into a file per database. For example:

postgres@jd-laptop:~$ ls -l psql-history
total 4
-rw------- 1 postgres postgres 33 Oct 24 09:33 psql_history-tut

Where tut is the name of a tutorial database I use for teaching.

Scott Mead: Quick stats with pgBadger

From Planet PostgreSQL. Published on Oct 24, 2016.

A while back, I wrote about ‘Helpful Postgres logging and defaults‘.  The real question is, how helpful are these, really?  So, let’s take a quick look at how we can use the increased logging in order to do something useful.  If the previous post is TL;DR, here’s the quick list:

logging_collector = on 
log_filename = 'postgresql-%a.log' 
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_temp_files = 0
log_lock_waits = on

Essentially, these cover the basics when it comes to monitoring, without adding a whole lot of stuff to your logs.  These settings are default in the BigSQL sandboxes so that you’re running out of the gate.  If you’ve installed PostgreSQL another way, you can simply modify your postgresql.conf file to include these items.

Follow along

Quick Install

If you’d like to follow along, you can quickly setup a test pg96 environment by using the ‘standalone’ installer for postgres that BigSQL provides.  If you already have PostgreSQL, you can skip this section.

Grab BigSQL

scottm: ~ $ python -c "$(curl -fsSL"
Downloading BigSQL PGC 3.0.0 ...
Unpacking ...
Cleaning up
Setting REPO to
Updating Metadata
 BigSQL PGC installed. Try 'bigsql/pgc help' to get started.

Install PostgreSQL 9.6.current

scottm: ~ $ cd bigsql/
 scottm: bigsql $ ./pgc init pg96
 scottm: bigsql $ ./pgc install pg96
 Get:1 pg96-9.6.0-1b-osx64
 Unpacking pg96-9.6.0-1b-osx64.tar.bz2

Init pg96

scottm: bigsql $ ./pgc init pg96
## Initializing pg96 #######################
Superuser Password [password]:
 Giving current user permission to data dir
Initializing Postgres DB at:
 -D "/Users/scottm/bigsql/data/pg96"
Using PostgreSQL Port 5432
Password securely remembered in the file: /Users/scottm/.pgpass
to load this postgres into your environment, source the env file:

Start pg96

scottm: bigsql $ ./pgc start pg96
 pg96 starting on port 5432

Since the BigSQL package automatically sets the appropriate logging defaults, we’re set to go!

Modify Existing PG

  1. open up your postgresql.conf file
  2. place the above values at the end of the file
  3. save & close postgresql.conf
  4. restart postgres (if you already had ‘logging_collector=on’, you can just reload).


Now that you’re logging, it’s time to generate some workload.  My database isn’t actually serving clients at this point, so, I’m going to use the included pgbench tool to generate some data and traffic. (NB: If you run this step, you’ll use up about 2.5 GB of disk space).

pgbench -i -s 100 -U postgres

# This initializes a new pgbench database with a ‘scaling factor’ (i.e. sizing factor) of 100 bank branches (pgbench simulates debit/credit).  You’ll see a bunch of output fly-by as you initialize 10,000,000 rows (tuples) of data.  Don’t worry, this only translates to about 2.5 GB of space.


Great!  Now we’re logging at a verbosity that lets us see what’s going on in PostgreSQL, how do I take advantage?  Enter pgBadger; this tool is essentially a high-performance log parser for the postgresql information logs.  If you’re following along with my BigSQL install from above, you can grab pgBadger with:

scottm: bigsql $ ./pgc install pgbadger
Get:1 pgbadger-9.0
 Unpacking pgbadger-9.0.tar.bz2

If you’re not using the BigSQL package, you can:

git clone

Now, let’s run pgBadger against the logs.

If you’re following along:

scottm: bigsql $ perl pgbadger/pgbadger data/logs/pg96/postgresql-*.log
[========================>] Parsed 8993 bytes of 8993 (100.00%), queries: 0, events: 2
LOG: Ok, generating html report...
scottm: bigsql $

By default, pgBadger puts its output in a file called ‘out.html’ (see perl pgbadger/pgbadger –help for the flag to change this).

I’m going to open up the ‘out.html’ file in my web browser and view my report (I’ve attached the out.html report I generated for this blog so you can download it and play if you’d like).


pgBadger empty overview

Hmmm, looks like I have an empty report?  Strange?  Well, keep in mind, we’ve told postgres to log certain things:

  •  checkpoints
  • Vacuum
  • temp files
  • lock waits

We never told postgres to log queries (we’ll come back to this in a minute).  For now, let’s take a look at the ‘checkpoints’ and see what’s there.

Choose Checkpoints


Once we choose the ‘Checkpoints’ item from the top menu, we’ll see that our data-load created some activity for us (your data will most likely look somewhat different).

Screen Shot 2016-10-21 at 4.45.47 PM

Now I’m starting to see data about my checkpoint buffer utilization!  Cool!  I can actually see how memory is being used in the server.  This is coming out of the ‘log_checkpoints’ parameter that we set earlier.

To get richer data, we need to push more activity against our DB server.  With pgBench:

scottm: bigsql $ pg96/bin/pgbench -T 300 -c 10

This will run a 5 minute (300 second) test with 10 concurrent client sessions.

Once that’s complete, re-run pgBadger:

scottm: bigsql $ perl pgbadger/pgbadger data/logs/pg96/postgresql-*.log
[========================>] Parsed 38636 bytes of 38636 (100.00%), queries: 0, events: 2
[========================>] Parsed 3024 bytes of 3024 (100.00%), queries: 0, events: 2
[========================>] Parsed 24694 bytes of 24694 (100.00%), queries: 0, events: 2
LOG: Ok, generating html report...

(Your output may appear slightly different based on the number of log files that you have)

Now, your ‘out.html’ will have data based on your workload.

Badger with WorkloadWe also have some interesting data surrounding VACUUM behavior:Vacuum DataIt should be noted, these are just quick screen grabs, take a look at the attached ‘out.html’ for more.


The point is, using some wise logging defaults gets you some awesome pgBadger joy, without paying a TON in logging overhead.  Stay tuned for part 2 where I’ll crank up logging even more and show you how to get per-statement data.



How to Add Social Login to Django

By Simple is Better Than Complex from Django community aggregator: Community blog posts. Published on Oct 24, 2016.

In this tutorial we will implement Facebook, Twitter and GitHub authentication using the python-social-auth library. They support several other services and the process should be somewhat similar. The python-social-auth library have several customization options, which sometimes makes it challenging to get started. So for this tutorial I will guide you through the mandatory steps where in the end you will have a fully functional social authentication.


As usual, go for pip:

pip install python-social-auth

Add the social.apps.django_app.default to your INSTALLED_APPS:


    'social.apps.django_app.default',  # <--


Migrate the database:

python migrate

Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions, social_auth
Running migrations:
  Applying social_auth.0001_initial... OK
  Applying social_auth.0002_add_related_name... OK
  Applying social_auth.0003_alter_email_max_length... OK
  Applying social_auth.0004_auto_20160423_0400... OK
  Applying social_auth.0005_auto_20160727_2333... OK

The library will automatically handle authentication tokens and all the required information to deal with OAuth and OAuth2. Generally speaking, you won’t need to handle it manually nor access the user’s social profile.


Update the MIDDLEWARE_CLASSES by adding the SocialAuthExceptionMiddleware to the end of it:


    'social.apps.django_app.middleware.SocialAuthExceptionMiddleware',  # <--

Now we update the context_processors inside TEMPLATE:

        'BACKEND': 'django.template.backends.django.DjangoTemplates',
        'DIRS': [
        'APP_DIRS': True,
        'OPTIONS': {
            'context_processors': [

                'social.apps.django_app.context_processors.backends',  # <--
                'social.apps.django_app.context_processors.login_redirect', # <--




So, as you can see, here at this point we will need to specify the correct backends, depending on your needs. Since here in this example we will be working with GitHub, Twitter and Facebook I just added those three backends.

Update the add the python-social-auth urls:

from django.conf.urls import url, include
from django.contrib import admin
from django.contrib.auth import views as auth_views

from mysite.core import views as core_views

urlpatterns = [
    url(r'^$', core_views.home, name='home'),
    url(r'^login/$', auth_views.login, name='login'),
    url(r'^logout/$', auth_views.logout, name='logout'),
    url(r'^oauth/', include('social.apps.django_app.urls', namespace='social')),  # <--

Let’s set the default values for the LOGIN_URL, LOGOUT_URL and the LOGIN_REDIRECT_URL. The LOGIN_REDIRECT_URL will be used to redirect the user after authenticating from Django Login and Social Auth.

LOGIN_URL = 'login'
LOGOUT_URL = 'logout'

As we are using the built-in login and logout views, let’s just create the home view inside the core app:

from django.contrib.auth.decorators import login_required
from django.shortcuts import render

def home(request):
    return render(request, 'core/home.html')


{% extends 'base.html' %}

{% block content %}
  <p>Hello {{ user.username }}!</p>
{% endblock %}

We are good to go. For each social network you will need to register your app in their system. So from now on the steps will vary on the service provider. Let’s tackle them one at a time. First one: GitHub.

GitHub Authentication

Log in to your GitHub account, go to Settings. In the left menu you will see Developer settings. Click on OAuth applications.

In the OAuth applications screen click on Register a new application. Or simply click on the link below:

Provide the information below:

New GitHub OAuth Application

The important step here is the Authorization callback URL. Notice that I’m putting a localhost URL. http://localhost:8000/oauth/complete/github/.

After you create the app, you will be redirected to this screen:

New GitHub OAuth Application

PS: Keep this information safe. I’m putting it here just so it is more explicity in the example. For the running application you will find in the end of this tutorial it uses different credentials.

Anyhoo, let’s grab the information we need:

Client ID
Client Secret

Now we update the

SOCIAL_AUTH_GITHUB_KEY = '44fd4145a8d85fda4ff1'
SOCIAL_AUTH_GITHUB_SECRET = '2de7904bdefe32d315805d3b7daec7906cc0e9e7'

PS 2: It is not a good idea to keep this kind of information directly in the Please refer to this article Package of the Week: Python Decouple to learn how to separate configuration from settings.

Login View

Let’s create a Login View that can both handle Django Login and the GitHub Login. The default login view look for a template in registration/login.html. Let’s create it:


{% extends 'base.html' %}

{% block content %}
  <form method="post">
    {% csrf_token %}
    {{ form.as_p }}
    <button type="submit">Login</button>
  <p><strong>-- OR --</strong></p>
  <a href="{% url 'social:begin' 'github' %}">Login with GitHub</a><br>
{% endblock %}

Note that the important part to start the GitHub authentication is {% url 'social:begin' 'github' %}.

At the moment our login page should look like this:

Login Page

A good thing about the social login is that the process to both Login or Signup is pretty much the same. Meaning that the user without having an account in your application, he/she can login. If there is no account matching the username/email, an account will automatically be created.

Let’s try our new Login with GitHub link:

GitHub Login Page

Click on Authorize application

GitHub Login Success

And there you go! An account was just created and is signed in!

There is a small caveat in cases like this: the user have created an account but have no password. We will address this issue later on.

Twitter Login

Go to and click on Create New App.

Add the details just like you did with the GitHub App:

Twitter App

For Twitter we will use the http://localhost:8000/oauth/complete/twitter/ callback url.

Go to Permissions tab and change the permissions to Read Only:

Twitter App Permissions

The less permissions we have better. This way we make our users more comfortable.

Now go to Keys and Access Tokens tab and grab the Consumer Key (API Key) and Consumer Secret (API Secret) and update the

SOCIAL_AUTH_TWITTER_SECRET = 'mwtdcUe4uOvvJjDk2AuQ9Mq2xiHPw3740m5iGLf6hwg3B4TNSx'

To enable the Twitter login, simply update the registration/login.html template:

{% extends 'base.html' %}

{% block content %}
  <form method="post">
    {% csrf_token %}
    {{ form.as_p }}
    <button type="submit">Login</button>
  <p><strong>-- OR --</strong></p>
  <a href="{% url 'social:begin' 'github' %}">Login with GitHub</a><br>
  <a href="{% url 'social:begin' 'twitter' %}">Login with Twitter</a>
{% endblock %}

Twitter Login

Let’s try it:

Twitter Login Twitter Login

And there you go!

See how it created a new user with a unglier name? vitorfs742c692ac9494871. That’s because both my username on GitHub and on Twitter is vitorfs. And just from the username we can’t guarantee that it’s the same user, the python-social-auth handles it by creating a new user and appending an arbitrary string to the end of it.

That’s okay for now! I will show later on where you can work out this behaviour.

Facebook Login

Go to click on My Apps and then Add a New App.

Facebook App

Pick Website.

Facebook App

Facebook App

Click on Create App ID. On the next screen click on Skip Quick Start.

From the initial page, grab the App ID and App Secret (click on the Show button to get it as plain text):

SOCIAL_AUTH_FACEBOOK_KEY = '451821651x33143'  # App ID
SOCIAL_AUTH_FACEBOOK_SECRET = '524fada3c3ca5adgb279da535da1d863'  # App Secret

Now go to Settings / Basic. First click on the button + Add Platform and add a website. For the Site URL put http://localhost:8000 and then in the App Domains put just localhost, like the picture below:

Facebook App

PS: For Facebook we can’t use as the callback. Instead use localhost. Or add a fake host in your local hosts. Usually you can find it at /etc/hosts.

Save changes and we are good to go. Now update the registration/login.html file with the Facebook link:

{% extends 'base.html' %}

{% block content %}
  <form method="post">
    {% csrf_token %}
    {{ form.as_p }}
    <button type="submit">Login</button>
  <p><strong>-- OR --</strong></p>
  <a href="{% url 'social:begin' 'github' %}">Login with GitHub</a><br>
  <a href="{% url 'social:begin' 'twitter' %}">Login with Twitter</a><br>
  <a href="{% url 'social:begin' 'facebook' %}">Login with Facebook</a>
{% endblock %}

Let’s try it now:

Facebook App

Facebook App

Facebook App

Social Auth Management Page

We can now add a Settings page in our application, where the user can manage the social auth logins. It’s a good way for the user authorize new services or revoke the access.

Basically this page will be responsible for:

  • Disconnecting the user from the social networks
  • Control if the user can disconnect (that is, the user have defined a password, so he/she won’t be locked out of the system)
  • Provide means to connect to other social networks

urlpatterns = [
    url(r'^settings/$', core_views.settings, name='settings'),
    url(r'^settings/password/$', core_views.password, name='password'),

A password view will be very important in this case!

Now update the project file with the following configurations:


from django.contrib.auth.decorators import login_required
from django.contrib.auth.forms import AdminPasswordChangeForm, PasswordChangeForm
from django.contrib.auth import update_session_auth_hash
from django.contrib import messages
from django.shortcuts import render, redirect

from social.apps.django_app.default.models import UserSocialAuth

def home(request):
    return render(request, 'core/home.html')

def settings(request):
    user = request.user

        github_login = user.social_auth.get(provider='github')
    except UserSocialAuth.DoesNotExist:
        github_login = None

        twitter_login = user.social_auth.get(provider='twitter')
    except UserSocialAuth.DoesNotExist:
        twitter_login = None

        facebook_login = user.social_auth.get(provider='facebook')
    except UserSocialAuth.DoesNotExist:
        facebook_login = None

    can_disconnect = (user.social_auth.count() > 1 or user.has_usable_password())

    return render(request, 'core/settings.html', {
        'github_login': github_login,
        'twitter_login': twitter_login,
        'facebook_login': facebook_login,
        'can_disconnect': can_disconnect

def password(request):
    if request.user.has_usable_password():
        PasswordForm = PasswordChangeForm
        PasswordForm = AdminPasswordChangeForm

    if request.method == 'POST':
        form = PasswordForm(request.user, request.POST)
        if form.is_valid():
            update_session_auth_hash(request, form.user)
            messages.success(request, 'Your password was successfully updated!')
            return redirect('password')
            messages.error(request, 'Please correct the error below.')
        form = PasswordForm(request.user)
    return render(request, 'core/password.html', {'form': form})

Please note that this password view handle both Set password and Change password.


{% extends 'base.html' %}

{% block content %}

  {% if github_login %}
    <p>Connected as <a href="{{ github_login.extra_data.login }}/" target="_blank">{{ github_login.extra_data.login }}</a></p>
    {% if can_disconnect %}
      <form method="post" action="{% url 'social:disconnect' 'github' %}">
        {% csrf_token %}
        <button type="submit">Disconnect from GitHub</button>
    {% else %}
      <button type="button" disabled>Disconnect from GitHub</button>
      <p style="color: red">You must <a href="{% url 'password' %}">define a password</a> for your account before disconnecting from Github.</p>
    {% endif %}
  {% else %}
    <a href="{% url 'social:begin' 'github' %}">Connect to GitHub</a>
  {% endif %}

  {% if twitter_login %}
    <p>Connected as <a href="{{ twitter_login.extra_data.access_token.screen_name }}/" target="_blank">@{{ twitter_login.extra_data.access_token.screen_name }}</a></p>
    {% if can_disconnect %}
      <form method="post" action="{% url 'social:disconnect' 'twitter' %}">
        {% csrf_token %}
        <button type="submit">Disconnect from Twitter</button>
    {% else %}
      <button type="button" disabled>Disconnect from Twitter</button>
      <p style="color: red">You must <a href="{% url 'password' %}">define a password</a> for your account before disconnecting from Twitter.</p>
    {% endif %}
  {% else %}
    <a href="{% url 'social:begin' 'twitter' %}">Connect to Twitter</a>
  {% endif %}

  {% if facebook_login %}
    <p>Connected as <a href="{{ }}/" target="_blank">{{ }}</a></p>
    {% if can_disconnect %}
      <form method="post" action="{% url 'social:disconnect' 'facebook' %}">
        {% csrf_token %}
        <button type="submit">Disconnect from Facebook</button>
    {% else %}
      <button type="button" disabled>Disconnect from Facebook</button>
      <p style="color: red">You must <a href="{% url 'password' %}">define a password</a> for your account before disconnecting from Facebook.</p>
    {% endif %}
  {% else %}
    <a href="{% url 'social:begin' 'facebook' %}">Connect to Facebook</a>
  {% endif %}

{% endblock %}

If you look close, it’s just a repetitive work. Basically we are showing if the account is connected with one of the providers, and giving the option to disconnect (whenever it is possible).

Finally the password view, in case you are wondering what it looks like:


{% extends 'base.html' %}

{% block content %}
  <h2>Change Password</h2>
  {% if not user.has_usable_password %}
    <p style="color: red">You have not defined a password yet.</p>
  {% endif %}
  <form method="post">
    {% csrf_token %}
    {{ form.as_p }}
    <button type="submit">Save changes</button>
{% endblock %}

The /settings/ page will look like this:

Social Settings

In case I disconnect from Twitter for example, the GitHub Disconnect button will be disabled:

Social Settings

This way we force the user to set a password before disconnecting from GitHub, avoiding her/him to be locked out.

Next Steps

Pavan Deolasee: Postgres-XL Scalability for Loading Data

From Planet PostgreSQL. Published on Oct 24, 2016.

In my last blog, we looked at the benchmark results from bulk load test for a Postgres-XL database cluster. Using a 16-datanode, 2-coordinator cluster, running on EC2 instances, we could easily clock 9M rows/sec or 3TB/hr of ingestion rate. That’s a significant number in itself. In this blog, we’ll see if the ingestion rate is scalable in Postgres-XL. In particular, we’ll try to answer if adding more nodes to the cluster can result in a linear increase in performance.

Let’s use the same line item table from the TPC-H benchmark for these tests. We’ll increase the cluster size from 16 datanodes to 20 datanodes and then further to 24 datanodes. We’ll also repeat the tests with 1, 2 and 3 coordinators respectively. For all these tests, we are using i2.xlarge EC2 instance for a datanode and c3.8xlarge EC2 instance for a coordinator. Also, we are keeping the number of concurrent COPY processes to 24 for all these tests.

Sustained Bulk-load Rate

To measure if the bulk-load rate can be sustained over longer period of time and when the data significantly overflows the combined RAM on all machines, let’s measure time to load 3 billion, 6 billion and 9 billion rows on a 16-datanode cluster. The following chart shows the data load time and a steady ingestion rate even for a very large table.

screen-shot-2016-10-19-at-3-10-26-pmAt the end of loading 9 billion rows, the table is close to 1TB in size, but that does not seem to have any negative impact on the load rate.

Scaling with Coordinators

Let’s first see if adding more coordinators to the cluster has any positive impact on the load time. To measure the impact of additional coordinators, we keep the number of datanodes constant at 16 and vary coordinators from 1 to 3.

So there is a significant improvement in the ingestion rate when 1 more coordinator is added to the cluster. The rate remains almost the same for 3 coordinators, suggesting that the bottleneck probably shifts to some other place and hence adding the third coordinator does not help much.

Scaling with Datanodes

This is the most interesting bit that should tell us whether Postgres-XL cluster can scale or how well it can scale with addition of more datanodes, as far as data ingestion is concerned. Keeping number of coordinators constant at 2, we vary the number of datanodes from 16 to 20 and again to 24. The following results are obtained by loading 3 billion rows with 24 COPY processes split equally between the two coordinators.


These results are very interesting and they show a linear scalability in ingestion rate with addition of more nodes to the cluster. While we could load at a rate of 13.5M rows/sec or nearly 4.5TB/hr while using 24 datanodes, what’s more interesting to note is that per datanode ingestion rate stays almost constant at about 560K rows/sec. Of course, it may not stay the same if many more datanodes are added since at some point the coordinator may become bottleneck or having too many concurrent sessions at the datanodes may cause performance degradation. A nice thing about Postgres-XL’s architecture is that it allows us to add as many coordinators as we like and hence coordinator side bottleneck is easy to handle, assuming application can split the load between coordinators.

I believe we demonstrated a few important things with these benchmarks.

  1. Postgres-XL can scale linearly with increasing number of datanodes.
  2. For large and concurrent bulk load, coordinator may become a bottleneck and it’s advisable to have more than one coordinators for such workloads.
  3. Postgres-XL can sustain the ingestion rate for very large data. While in these tests, we could achieve the maximum rate of 13.5M rows/sec or 4.5TB/hr, the trend clearly shows that this is nowhere close to the upper limit of Postgres-XL’s ability to handle high concurrent ingestion load.


Michael Paquier: Postgres 10 highlight - Wait events for latches

From Planet PostgreSQL. Published on Oct 23, 2016.

Postgres 9.6 has added a really cool infrastructure called wait events. This allows a developer to define in the code being run a wait point that gets reported to the system statistics via it PGPROC entry. In short, a custom wait point in the code gets reported and is then visible in system catalogs. In this case this is pg_stat_activity via the columns wait_event_type and wait_event. 9.6 infrastructure shows up information for backend processes holding lightweight locks, heavyweight locks, and buffer pins. While getting a look at this infrastructure, I got surprised by the fact that nothing was reported for latches, more or less code paths calling WaitLatch to wait for a timeout, a postmaster death, a socket event or just for the latch to be set. As 9.6 was close to shipping when I bumped into the limitation, nothing much could be done for it. So this got delayed to Postgres 10, and has been committed recently with the following change:

commit: 6f3bd98ebfc008cbd676da777bb0b2376c4c4bfa
author: Robert Haas <>
date: Tue, 4 Oct 2016 11:01:42 -0400
Extend framework from commit 53be0b1ad to report latch waits.

WaitLatch, WaitLatchOrSocket, and WaitEventSetWait now taken an
additional wait_event_info parameter; legal values are defined in
pgstat.h.  This makes it possible to uniquely identify every point in
the core code where we are waiting for a latch; extensions can pass

Because latches were the major wait primitive not previously covered
by this patch, it is now possible to see information in
pg_stat_activity on a large number of important wait events not
previously addressed, such as ClientRead, ClientWrite, and SyncRep.

Unfortunately, many of the wait events added by this patch will fail
to appear in pg_stat_activity because they're only used in background
processes which don't currently appear in pg_stat_activity.  We should
fix this either by creating a separate view for such information, or
else by deciding to include them in pg_stat_activity after all.

Michael Paquier and Robert Haas, reviewed by Alexander Korotkov and
Thomas Munro

The primary use-case that came to mind while looking at this new feature is the possibility to track which backends are being stuck because of a commit confirmation coming from a standby with synchronous streaming replication. There are ways to discover that using extensions, two of them are for example my own extension pg_rep_state or Fujii Masao’s pg_cheat_funcs though both require a lock on SyncRepLock to scan the PGPROC entries, something that could impact performance for a large number of backends to scan, of course depending on the frequency of the scan. Most users will not care about that though. The new wait event infrastructure has the advantage to not require the acquisition of such a lock, users just need to look at the wait event SyncRep for the same result. Let’s have a look at that then with a single Postgres instance, whose commits will get stuck because synchronous_standby_names points to a standby that does not exist:

=# ALTER SYSTEM SET synchronous_standby_names = 'not_exists';
=# SELECT pg_reload_conf();
(1 row)
=# CREATE TABLE mytab (); -- Remains stuck

And from another session:

=# SELECT query, wait_event_type, wait_event
   FROM pg_stat_activity WHERE wait_event is NOT NULL;
          query         | wait_event_type | wait_event
 CREATE TABLE mytab (); | IPC             | SyncRep
(1 row)

So the result here is really cool, wait_event being set to what is expected. Note that the wait event types of those new wait points have been classified by category, per an idea of Robert Haas who committed the patch to clarify a bit more what each wait point is about. For example, “IPC” refers to a process waiting for some activity from another process, “Activity” means that the process is basically idle, etc. The documentation on the matter has all the information that matters.

A limitation of the feature is that it is not possible to look at the wait points of auxiliary system processes, like the startup process at recovery, the archiver, autovacuum launcher, etc. It would be possible to get that working by patching a bit more the upstream code. Background workers can by the way show up in pg_stat_activity so it is possible to include in them custom wait points that are then monitored.

An important use-case of this feature is performance analysis. The set of wait points available makes it far easier to locate where are the contention points of a given application by monitoring pg_stat_activity at a fixed frequency. For example, if accumulated events involve a lot of ClientRead events, it means that backends are usually waiting a lot for information from a client. 9.6 allows some analysis based on a lookup of the locks taken but being able to look at the additional bottlenecks like the client-server communication completes the set and allows far deeper analysis of benchmarks using the in-core structure of Postgres. But let’s take a short example with a pgbench database initialized at scale 10, with a run of 24 clients:

$ pgbench -i -s 10
set primary keys...
$ pgbench -c 24 -T 65

And in parallel to that let’s store the events periodically in a custom table, using psql’s \watch command to store the events that can be found:

=# CREATE TABLE wait_events (wait_event_type text, wait_event text);
=# INSERT INTO wait_events
   SELECT wait_event_type, wait_event
     FROM pg_stat_activity
     WHERE pid != pg_backend_pid();
=# \watch 5
[... 12 samples are taken ...]

Once the run is done, here is a simple way to analyze this collected data:

=# SELECT count(*) AS cnt, wait_event, wait_event_type
   FROM wait_events
   GROUP BY (wait_event, wait_event_type) ORDER BY cnt;
 cnt |  wait_event   | wait_event_type
  24 | tuple         | Lock
  39 | transactionid | Lock
  66 | null          | null
 159 | ClientRead    | Client
(5 rows)

In which case the conclusion is plain: a lot of backends have just keep waiting for pgbench to get something to do so they ran in an idle state most of the time. Take this example lightly, this is not a workload that one would see in the real world, still this new tooling opens a lot of new exciting prospectives when benchmarking.

JSON Web Tokens in django application- part two

By Krzysztof Żuraw Personal Blog from Django community aggregator: Community blog posts. Published on Oct 23, 2016.

In this blog post, I will deal with creating simple Django application for creating tasks using django rest framework.

From this blog post, you can learn how to setup basic DRF application.

Overview of application

The main goal of this application is to create tasks. Each task has a title - string with a maximum length of 100 characters. Task also has a person to which it is bound (many to one relation - ForeginKey). The last thing that task have is date and time which given task is due to. The user can easily modify each of tasks so GET, POST, PUT and DELETE methods are supported.

As we know how the application is designed let's jump into the code.

Application code

First, there is a need to create model for Task:

from django.db import models
from django.contrib.auth.models import User

class Task(models.Model):
    title = models.CharField(max_length=100)
    person = models.ForeignKey(User)
    due_to = models.DateTimeField()

    def __str__(self):
        return 'Task with title: {}'.format(self.title)

The arguments of Task correspond to what was written in the overview.

As we have models ready now it's time to create serializers so data from database can be converted to stream of bytes:

from rest_framework import serializers
from .models import Task
from django.contrib.auth.models import User

class TaskSerializer(serializers.HyperlinkedModelSerializer):
    class Meta:
        model = Task

class UserSerializer(serializers.ModelSerializer):
    class Meta:
        model = User

As you can see in TaskSerializer I used HyperlinkedModelSerializer as a type of serializer that I want to use - thanks to that response from my application will have hyperlinks to resources instead of just primary keys that are used in UserSerializer. In this serializer, I use django User as a source of data. I have to do this because Task model has a reference to User and without serialization of the second one I cannot serialize the task.

Right now I have my models and serializers ready so it's time to create some views and urls. For a typical usage of views, DRF gives you generic viewsets like ModelViewSet. ViewSet is a combination of the logic for a set of related views in a single class. How do views look like?

from rest_framework import viewsets
from .models import Task
from .serializers import TaskSerializer, UserSerializer
from django.contrib.auth.models import User

class TaskViewSet(viewsets.ModelViewSet):
    queryset = Task.objects.all()
    serializer_class = TaskSerializer

class UserViewSet(viewsets.ModelViewSet):
    queryset = User.objects.all()
    serializer_class = UserSerializer

I created 2 viewsets. The only thing that I need to provide is queryset and serializer_class arguments so viewsets know which data they needed to take and which serializer use. Right now there is only one thing missing - urls:

from django.conf.urls import url, include
from django.contrib import admin
from tasks import views
from rest_framework.routers import DefaultRouter

router = DefaultRouter()
router.register(r'tasks', views.TaskViewSet)
router.register(r'users', views.UserViewSet)

urlpatterns = [
    url(r'^', include(router.urls)),

Here I set up DefaultRouter and hook TaskViewSet and UserViewSet to it. Router is a way of building common routes for a resource. To get all tasks - I go to /tasks uri. To retrieve first task I type tasks/1. I can write this in urlpatterns but the router is doing the same for me automatically.

Right now I can try my application:

$ http GET
HTTP/1.0 200 OK
Content-Type: application/json
Date: Sun, 23 Oct 2016 08:36:23 GMT
Server: WSGIServer/0.2 CPython/3.5.1
Vary: Accept, Cookie
X-Frame-Options: SAMEORIGIN

    "tasks": "",
    "users": ""

$ http GET
HTTP/1.0 200 OK
Content-Type: application/json
Date: Sun, 23 Oct 2016 08:45:50 GMT
Server: WSGIServer/0.2 CPython/3.5.1
Vary: Accept, Cookie
X-Frame-Options: SAMEORIGIN

        "due_to": "2016-10-18T19:12:01Z",
        "person": "",
        "title": "First one",
        "url": ""
        "due_to": "2016-10-18T19:12:10Z",
        "person": "",
        "title": "Second one",
        "url": ""

That's all for today! In the next post, I will make authentication with JWT. Stay tuned!

Code for this blog post is under this github.

Other blog posts in this series

Special thanks to Kasia for being editor for this post. Thank you.

Cover picture by Lalmch under CC0 Public Domain.

Federico Campoli: If you close your eyes you can smell Estonia

From Planet PostgreSQL. Published on Oct 22, 2016.

It's almost time for the pgconf 2016. This year the conference is in Tallinn the capital of Estonia. The conference is packed with super interesting talks which I really look forward to listen.

I'll also present the talk life on a rollercoaster which tells the story of the last 4 years  of a DBA(just guess who's he :P)  dealing with large large PostgreSQL installations.

I decided to make the talk with a narrative, and hopefully, entertaining form, in order to avoid to bore the audience to death.

I will also submit a lightning talk for the pg_chameleon tool I'm developing.

I'm pretty excited to meet old friends from the PostgreSQL community.

Well, that's all for now.

See you in Tallinn very soon :)

Basics of Webpack

By GoDjango - Django Screencasts from Django community aggregator: Community blog posts. Published on Oct 21, 2016.

Webpack can be complicated, but it doesn't have to be. Learn some of the basics of webpack, and how you can get started with it while actually understanding it. In this video we start our journey to working with react and django, but first we need to build our javascript and that is where webpack comes in.
Watch Now...

Shaun M. Thomas: PG Phriday: Broken Parts

From Planet PostgreSQL. Published on Oct 21, 2016.

Partitioning tables in Postgres can be an extremely risky endeavor. Unfortunately on many larger systems, it’s also essentially a requirement; the maximum size of a Postgres table is 32TB. This isn’t just because converting an existing table to a series of partitions is expensive or time consuming. We must consider how the query planner will react to the partitioned version of a table. There’s also the very real risk we will (or already have) implement flaws in the trigger or constraint logic.

Tools like pg_partman or pg_pathman are great for new systems, but existing deployments require retrofitting. So what happens if we have a large warehouse that’s already using a slightly flawed partition engine? And what other concerns remain even after achieving a pristine setup?

Let’s start with a relatively large sample table with three partitions. Each partition will consist of roughly 26M rows representing sensor readings every 100ms from 1000 sensors.

CREATE TABLE sensor_log (
  sensor_id     INT NOT NULL,
  location      VARCHAR NOT NULL,
  reading       INT NOT NULL,
  reading_date  TIMESTAMPTZ NOT NULL DEFAULT now()
CREATE TABLE sensor_log_201608 () INHERITS (sensor_log);
CREATE TABLE sensor_log_201609 () INHERITS (sensor_log);
CREATE TABLE sensor_log_201610 () INHERITS (sensor_log);
INSERT INTO sensor_log_201608 (sensor_id, location, reading, reading_date)
SELECT % 1000, (random() * 100)::INT, % 100,
       '2016-09-01'::TIMESTAMPTZ - ( * '100ms'::INTERVAL)
  FROM generate_series(1, 36000*24*31) s(id);
INSERT INTO sensor_log_201609 (sensor_id, location, reading, reading_date)
SELECT % 1000, (random() * 100)::INT, % 100,
       '2016-10-01'::TIMESTAMPTZ - ( * '100ms'::INTERVAL)
  FROM generate_series(1, 36000*24*30) s(id);
INSERT INTO sensor_log_201610 (sensor_id, location, reading, reading_date)
SELECT % 1000, (random() * 100)::INT, % 100,
       '2016-11-01'::TIMESTAMPTZ - ( * '100ms'::INTERVAL)
  FROM generate_series(1, 36000*24*31) s(id);
ALTER TABLE sensor_log_201608
  ADD CONSTRAINT ck_201608_part_reading_date
CHECK (reading_date >= '2016-08-01'::TIMESTAMPTZ AND
       reading_date < '2016-08-01'::TIMESTAMPTZ + INTERVAL '1 mon');
ALTER TABLE sensor_log_201609
  ADD CONSTRAINT ck_201609_part_reading_date
CHECK (reading_date >= '2016-09-01'::TIMESTAMPTZ AND
       reading_date < '2016-09-01'::TIMESTAMPTZ + INTERVAL '1 mon');
ALTER TABLE sensor_log_201610
  ADD CONSTRAINT ck_201609_part_reading_date
CHECK (reading_date >= '2016-09-01'::TIMESTAMPTZ AND 
       reading_date < '2016-10-01'::TIMESTAMPTZ + INTERVAL '1 mon');
CREATE INDEX idx_sensor_log_201608_sensor_id
    ON sensor_log_201608 (sensor_id);
CREATE INDEX idx_sensor_log_201608_reading_date
    ON sensor_log_201608 (reading_date DESC);
CREATE INDEX idx_sensor_log_201609_sensor_id
    ON sensor_log_201609 (sensor_id);
CREATE INDEX idx_sensor_log_201609_reading_date
    ON sensor_log_201609 (reading_date DESC);
CREATE INDEX idx_sensor_log_201610_sensor_id
    ON sensor_log_201610 (sensor_id);
CREATE INDEX idx_sensor_log_201610_reading_date
    ON sensor_log_201610 (reading_date DESC);
SET default_statistics_target = 400;
ANALYZE sensor_log_201608;
ANALYZE sensor_log_201609;
ANALYZE sensor_log_201610;

We’re early in the analysis and there’s already a major problem. The Postgres planner considers calculations a variable operation and substitutes them with a placeholder for all potential plans. This effectively erases the second boundary from all of our constraints. Simply because we used “+ INTERVAL ‘1 mon'” instead of a static value, every query will always target every partition.

Here’s how that looks:

  FROM sensor_log s1
 WHERE reading_date >= '2016-10-01'
   AND reading_date < '2016-10-21'
   AND location = '42';
 Append  (cost=0.00..639335.50 ROWS=164796 width=18)
   ->  Seq Scan ON sensor_log s1  (cost=0.00..0.00 ROWS=1 width=48)
         FILTER: ((reading_date >= '2016-10-01 00:00:00-05'::TIMESTAMP WITH TIME zone) AND (reading_date < '2016-10-21 00:00:00-05'::TIMESTAMP WITH TIME zone) AND ((location)::text = '42'::text))
   ->  INDEX Scan USING idx_sensor_log_201608_reading_date ON sensor_log_201608 s1_1  (cost=0.44..8.46 ROWS=1 width=18)
         INDEX Cond: ((reading_date >= '2016-10-01 00:00:00-05'::TIMESTAMP WITH TIME zone) AND (reading_date < '2016-10-21 00:00:00-05'::TIMESTAMP WITH TIME zone))
         FILTER: ((location)::text = '42'::text)
   ->  INDEX Scan USING idx_sensor_log_201609_reading_date ON sensor_log_201609 s1_2  (cost=0.44..8.46 ROWS=1 width=18)
         INDEX Cond: ((reading_date >= '2016-10-01 00:00:00-05'::TIMESTAMP WITH TIME zone) AND (reading_date < '2016-10-21 00:00:00-05'::TIMESTAMP WITH TIME zone))
         FILTER: ((location)::text = '42'::text)
   ->  Seq Scan ON sensor_log_201610 s1_3  (cost=0.00..639318.58 ROWS=164793 width=18)
         FILTER: ((reading_date >= '2016-10-01 00:00:00-05'::TIMESTAMP WITH TIME zone) AND (reading_date < '2016-10-21 00:00:00-05'::TIMESTAMP WITH TIME zone) AND ((location)::text = '42'::text))

This particular snafu invokes several related and extremely subtle problems. Consider a simple report where we want to compare sensor variance based on location. Operating under the assumption certain sensor locations don’t always trigger during certain sample periods, that’s just a single left join. If this were an inventory system, we might be comparing customers who order rubber chickens versus those who lean toward high performance graphics cards.

With our data, we can do this with a single self join, but why fetch everything? We have millions of entries going back for three months! Let’s apply a LIMIT clause so we can decide how to further analyze the results:

  FROM sensor_log s1
  LEFT JOIN sensor_log s2 ON (
         s2.sensor_id = s1.sensor_id AND
         s2.reading_date >= '2016-10-01' AND
         s2.reading_date < '2016-10-21' AND
         s2.location = '87'
 WHERE s1.reading_date >= '2016-10-01'
   AND s1.reading_date < '2016-10-21'
   AND s1.location = '42'
 LIMIT 10;
 LIMIT  (cost=2.73..26.59 ROWS=10 width=18)
   ->  MERGE LEFT JOIN  (cost=2.73..336756014.96 ROWS=141129646 width=18)
         MERGE Cond: (s1.sensor_id = s2.sensor_id)
         ->  MERGE Append  (cost=1.36..167319256.22 ROWS=164796 width=18)
               Sort KEY: s1.sensor_id
               ->  Sort  (cost=0.01..0.02 ROWS=1 width=48)
                     Sort KEY: s1.sensor_id
                     ->  Seq Scan ON sensor_log s1  (cost=0.00..0.00 ROWS=1 width=48)
                           FILTER: ((reading_date >= '2016-10-01 00:00:00-05'::TIMESTAMP WITH TIME zone) AND (reading_date < '2016-10-21 00:00:00-05'::TIMESTAMP WITH TIME zone) AND ((location)::text = '42'::text))
               ->  INDEX Scan USING idx_sensor_log_201608_sensor_id ON sensor_log_201608 s1_1  (cost=0.44..56414786.91 ROWS=1 width=18)
                     FILTER: ((reading_date >= '2016-10-01 00:00:00-05'::TIMESTAMP WITH TIME zone) AND (reading_date < '2016-10-21 00:00:00-05'::TIMESTAMP WITH TIME zone) AND ((location)::text = '42'::text))
               ->  INDEX Scan USING idx_sensor_log_201609_sensor_id ON sensor_log_201609 s1_2  (cost=0.44..54487523.33 ROWS=1 width=18)
                     FILTER: ((reading_date >= '2016-10-01 00:00:00-05'::TIMESTAMP WITH TIME zone) AND (reading_date < '2016-10-21 00:00:00-05'::TIMESTAMP WITH TIME zone) AND ((location)::text = '42'::text))
               ->  INDEX Scan USING idx_sensor_log_201610_sensor_id ON sensor_log_201610 s1_3  (cost=0.44..56413238.01 ROWS=164793 width=18)
                     FILTER: ((reading_date >= '2016-10-01 00:00:00-05'::TIMESTAMP WITH TIME zone) AND (reading_date < '2016-10-21 00:00:00-05'::TIMESTAMP WITH TIME zone) AND ((location)::text = '42'::text))
         ->  Materialize  (cost=1.36..167319830.26 ROWS=171278 width=4)
               ->  MERGE Append  (cost=1.36..167319402.06 ROWS=171278 width=4)
                     Sort KEY: s2.sensor_id
                     ->  Sort  (cost=0.01..0.02 ROWS=1 width=4)
                           Sort KEY: s2.sensor_id
                           ->  Seq Scan ON sensor_log s2  (cost=0.00..0.00 ROWS=1 width=4)
                                 FILTER: ((reading_date >= '2016-10-01 00:00:00-05'::TIMESTAMP WITH TIME zone) AND (reading_date < '2016-10-21 00:00:00-05'::TIMESTAMP WITH TIME zone) AND ((location)::text = '87'::text))
                     ->  INDEX Scan USING idx_sensor_log_201608_sensor_id ON sensor_log_201608 s2_1  (cost=0.44..56414786.91 ROWS=1 width=4)
                           FILTER: ((reading_date >= '2016-10-01 00:00:00-05'::TIMESTAMP WITH TIME zone) AND (reading_date < '2016-10-21 00:00:00-05'::TIMESTAMP WITH TIME zone) AND ((location)::text = '87'::text))
                     ->  INDEX Scan USING idx_sensor_log_201609_sensor_id ON sensor_log_201609 s2_2  (cost=0.44..54487523.33 ROWS=1 width=4)
                           FILTER: ((reading_date >= '2016-10-01 00:00:00-05'::TIMESTAMP WITH TIME zone) AND (reading_date < '2016-10-21 00:00:00-05'::TIMESTAMP WITH TIME zone) AND ((location)::text = '87'::text))
                     ->  INDEX Scan USING idx_sensor_log_201610_sensor_id ON sensor_log_201610 s2_3  (cost=0.44..56413238.01 ROWS=171275 width=4)
                           FILTER: ((reading_date >= '2016-10-01 00:00:00-05'::TIMESTAMP WITH TIME zone) AND (reading_date < '2016-10-21 00:00:00-05'::TIMESTAMP WITH TIME zone) AND ((location)::text = '87'::text))

There’s a lot going on here, so we’ll just focus on what went wrong. In this case, the culprit is the LIMIT clause itself. By trying to reduce output, we told Postgres to optimize the execution and avoid generating the entire result. This means it will read rows in table order until it accumulates 10 that match all of the predicates we supplied. Simple and efficient, right?

Well… no. If we look closer at one of the indexes it’s using, we’ll see it opted for the index on sensor_id instead of the index on reading_date. This isn’t necessarily a problem on the October partition, since it should match ten rows relatively quickly. If it assumes even distribution of 100 sensor locations, it may have to read up to 1000 rows to get the 10 we wanted. That’s not awful, is it?

But consider the other two partitions. Remember those? There will be no match for August or September. But Postgres doesn’t know that yet, meaning it will read the entire sensor_id index for both of them. That index is over 500MB on each partition, so we just read an extra 1GB for no reason at all. But wait! There’s more! We still need to apply the filters to remove them from consideration, right? That means a random read to the table heap for every tuple in the index, plus or minus some batching for the sake of efficiency. What’s a few hundred thousand extra random reads between friends?

Now imagine each table contains ten times more data, and there are three years worth of partitions instead of three months. Our particular example requires about six minutes to execute, but real-world cases of this can quickly cascade into hours or even days.

How can we fix this? It’s clear we need to rebuild the constraints so they properly convey the fact certain partitions should be ignored. But we already have billions of rows, and partitions are checked upon creation to verify their validity. Or are they? There is a way to automate this:

DO $$
  FOR r IN SELECT tab,
           to_date(ym, 'YYYYMM01') AS sdate,
           to_date(ym, 'YYYYMM01') + '1 mon'::INTERVAL AS edate,
           cname, col
      FROM (SELECT i.inhrelid::REGCLASS::TEXT AS tab,
                   SUBSTRING(i.inhrelid::REGCLASS::TEXT FROM '......$') AS ym,
                   co.conname AS cname,
                   SUBSTRING(co.consrc FROM '\w+') AS col
              FROM pg_inherits i
              JOIN pg_constraint co ON (co.conrelid = i.inhrelid)
             WHERE co.contype = 'c'
               AND co.consrc ILIKE '%1 mon%') cfix
        quote_ident(r.cname) || ' CHECK (' ||
        quote_ident(r.col) || ' >= ' || quote_literal(r.sdate) || ' AND ' ||
        quote_ident(r.col) || ' < ' || quote_literal(r.edate) || ') NOT VALID';
$$ LANGUAGE plpgsql;
UPDATE pg_constraint 
   SET convalidated = TRUE
 WHERE NOT convalidated;

The giant gross query that defines the loop simply identifies all of the broken partition constraints and provides replacement boundaries. Within the loop, we drop all of the bad constraints and replace them with better ones, being careful to tell Postgres they’re not valid so it doesn’t check them. At the end, we modify the Postgres catalog and directly mark the constraints as valid, circumventing the whole verification process entirely.

And it doesn’t matter if we have three bad constraints as in this example, or three thousand. We’ve just fixed all of them. After this, our six minute query now executes in a mere 100ms, making it roughly 4000 times faster. Remember, our example is conservative; a real warehouse would see much more striking results.

But we need to be careful. Problems like this also apply to using CURRENT_DATE or now() + INTERVAL '1 mon' or any other kind of substitution in our WHERE clauses or join conditions. With no static value, Postgres makes assumptions on the potential row counts and estimates accordingly. Sometimes these assumptions don’t change much. In others, it might mean the difference between a sequential scan and an index scan (and the associated random reads).

Index scans are usually great! Unless of course, the table has a few hundred million rows and the predicate matches 60% of them. With the variable substitution in place, Postgres will probably prefer the index scan, and our query will be much slower as a consequence. Whenever or wherever possible, use static values in predicates that touch indexes or are the primary filter condition for a query. By doing that, all of the extra conditions will still apply, but won’t adversely affect query performance.

As a side note, this is one of the other reasons indexes aren’t merely a “go faster” button. Putting them in the wrong place or invoking them improperly can make performance worse. In most cases this translates to a standard deviation or two. In others, it results in geometric or exponential increases in query times. It’s those outliers that can really ruin our day.

When partitions are concerned, it pays to be extra vigilant.

Ildar Musin: Data rotation with `pg_pathman`

From Planet PostgreSQL. Published on Oct 21, 2016.

Sometimes it is good to have only the most recent data in database and to remove outdated rows as new data comes (e.g., user activity logs or data from hardware sensors). This can be easily achieved using pg_pathman’s features. Let’s say we have a temperature sensor and we need to store readings from it for the last few days.

create table sensor_data (id serial, dt timestamp not null, value numeric);

insert into sensor_data(dt, value) select t, random()
from generate_series('2016-10-01', '2016-10-10 23:59:59', '1 second'::interval) as t;

First we divide whole dataset into several partitions by timestamp field so that each partition would contain a piece of data for a one day interval:

create extension pg_pathman;
select create_range_partitions('sensor_data', 'dt', '2016-10-01'::timestamp, '1 day'::interval);

As you may know pg_pathman automatically creates new partitions on INSERT when new data exceeds existing partitions. Now the interesting part. Since version 1.1 you can add custom callback to your partitioned table which would trigger every time new partition is created. So if we want to keep only recent data and remove old partitions we could use a callback like following:

create or replace function on_partition_created_callback(params jsonb)
returns void as
    relation    regclass;
    for relation in (select partition from pathman_partition_list
                     where parent = 'sensor_data'::regclass
                     order by range_min::timestamp desc
                     offset 10)
        raise notice 'dropping partition ''%''', relation;
        execute format('drop table %s', relation);
    end loop;
language plpgsql;

select set_init_callback('sensor_data', 'on_partition_created_callback');

Note that callback must meet certain requirements:

  • it has a single JSONB parameter;
  • it returns VOID.

Few comments on the code above. pathman_partition_list is a view that contains all partitions that are managed by pg_pathman. We sort it by range_min field so that the newest partitions come first, then skip first ten and drop the rest. The set_init_callback() function installs callback into pg_pathman’s config.

Now every time we insert data that exceeds the range covered by partitions a new partition is created and the oldest one is automatically removed:

select partition, range_min, range_max from pathman_partition_list;
   partition    |      range_min      |      range_max
 sensor_data_1  | 2016-10-01 00:00:00 | 2016-10-02 00:00:00
 sensor_data_2  | 2016-10-02 00:00:00 | 2016-10-03 00:00:00
 sensor_data_3  | 2016-10-03 00:00:00 | 2016-10-04 00:00:00
 sensor_data_4  | 2016-10-04 00:00:00 | 2016-10-05 00:00:00
 sensor_data_5  | 2016-10-05 00:00:00 | 2016-10-06 00:00:00
 sensor_data_6  | 2016-10-06 00:00:00 | 2016-10-07 00:00:00
 sensor_data_7  | 2016-10-07 00:00:00 | 2016-10-08 00:00:00
 sensor_data_8  | 2016-10-08 00:00:00 | 2016-10-09 00:00:00
 sensor_data_9  | 2016-10-09 00:00:00 | 2016-10-10 00:00:00
 sensor_data_10 | 2016-10-10 00:00:00 | 2016-10-11 00:00:00
(10 rows)

insert into sensor_data(dt, value) values ('2016-10-11 15:05', 0.5);

select partition, range_min, range_max from pathman_partition_list;
   partition    |      range_min      |      range_max
 sensor_data_2  | 2016-10-02 00:00:00 | 2016-10-03 00:00:00
 sensor_data_3  | 2016-10-03 00:00:00 | 2016-10-04 00:00:00
 sensor_data_4  | 2016-10-04 00:00:00 | 2016-10-05 00:00:00
 sensor_data_5  | 2016-10-05 00:00:00 | 2016-10-06 00:00:00
 sensor_data_6  | 2016-10-06 00:00:00 | 2016-10-07 00:00:00
 sensor_data_7  | 2016-10-07 00:00:00 | 2016-10-08 00:00:00
 sensor_data_8  | 2016-10-08 00:00:00 | 2016-10-09 00:00:00
 sensor_data_9  | 2016-10-09 00:00:00 | 2016-10-10 00:00:00
 sensor_data_10 | 2016-10-10 00:00:00 | 2016-10-11 00:00:00
 sensor_data_11 | 2016-10-11 00:00:00 | 2016-10-12 00:00:00
(10 rows)

Petr Jelínek: pglogical 1.2 with PostgreSQL 9.6 support

From Planet PostgreSQL. Published on Oct 20, 2016.

PostgreSQL 9.6 is now out and so is an updated version of pglogical that works with it.

For quick guide on how to upgrade the database with pglogical you can check my post which announced 9.6beta support.

The main change besides the support for 9.6.x release of PostgreSQL is in the way we handle the output plugin and apply plugin. They have now been merged into single code base and single package so that there is no need to track the pglogical_output separately for the users and developers alike.

We fixed several bugs this time and also made upgrades from 9.4 much easier.

Here is a more detailed list of changes:

  • keepalive is tuned to much smaller values by default so that pglogical will notice network issues earlier
  • better compatibility when upgrading from PostgreSQL 9.4 – it’s now safe to use the internal schema synchronization for that
  • improved conflict handling
  • improved support for replica triggers
  • several issues with initial synchronizations were fixed
  • it’s easier to monitor the initial synchronization in pg_stat_replication
  • as usual, there are several documentation improvements

Sending HTML emails with embedded images from Django

By Michael Samoylov from Django community aggregator: Community blog posts. Published on Oct 19, 2016.

Currently I'm working on an application which sends HTML emails with embedded or inline images and multiple CSV and PDF attachments.

Let's assume that we will be using an object containing data for our email. I'm providing my models here just as a reference to get the idea:

class RenderedReport(models.Model):  
    report = models.ForeignKey('Report', related_name='rendered_reports')

    approved_by = models.ForeignKey('auth.User', blank=True, null=True)

    date_rendered = models.DateTimeField(auto_now_add=True)
    date_queried = models.DateTimeField(blank=True, null=True)
    date_approved = models.DateTimeField(blank=True, null=True)
    date_sent = models.DateTimeField(blank=True, null=True)

    class Meta:
        get_latest_by = 'date_rendered'
        ordering = ('-date_rendered',)

    def __unicode__(self):
        return str(

class RenderedView(models.Model):  
    rendered_report = models.ForeignKey('RenderedReport', related_name='rendered_views')
    view = models.ForeignKey('View', related_name='rendered_views')

    png = ImageField(upload_to='reports')
    pdf = models.FileField(upload_to='reports', blank=True)
    csv = models.FileField(upload_to='reports', blank=True)

    class Meta:
        ordering = ['view']

    def __unicode__(self):
        return str(self.view)

    def image_filename(self):
        return os.path.basename(

I don't like the idea of re-inventing the wheel, so I will be using a responsive email template from Zurb Studios.

I'm skipping the entire HTML template code for brevity because it wasn't modified. We only need this part:

<!-- BODY -->  
<table class="body-wrap">  
        <td class="container" bgcolor="#FFFFFF">
             <div class="content">
                            {% for view in views %}
                                <h3>{{ view }}</h3>
                                <p><img src="cid:{{ view.image_filename }}" /></p>
                                {% if not forloop.last %}<p>&nbsp;</p>{% endif %}
                            {% endfor %}
</table><!-- /BODY -->  

Now it's time to get our HTML email rendered. We won't be sending a plain-text version of our email,

I'm providing a simplified, but a working snippet of Python code:

import os  
from email.mime.image import MIMEImage

from django.core.mail import EmailMultiAlternatives  
from django.template.loader import render_to_string

rendered_report = RenderedReport.objects.get(pk=1)  
views = rendered_report.rendered_views.all()

context = {'views': views}

html_content = render_to_string('reports/email.html', context=context).strip()

subject = 'HTML Email'  
recipients = ['']  
reply_to = ['']

msg = EmailMultiAlternatives(subject, html_content, config.formatted_email_from, to, reply_to=reply_to)  
msg.content_subtype = 'html'  # Main content is text/html  
msg.mixed_subtype = 'related'  # This is critical, otherwise images will be displayed as attachments!

for view in views:  
    # Create an inline attachment
    image = MIMEImage(
    image.add_header('Content-ID', '<{}>'.format(view.image_filename))

    # Create a regular attachment with a CSV file
    if view.csv:
        filename = os.path.basename(
        msg.attach(filename,, 'text/csv')

    # Create a regular attachment with a PDF file
    if view.pdf:
        filename = os.path.basename(
        msg.attach(filename,, 'application/pdf')


This will send a responsive HTML email containing inline images and attachments.

Please pay additional attention to the line with msg.mixed_subtype = 'related'. It sets the email header Content-Type: multipart/related; guaranteeing that your images will be displayed inline and not as attachments.

I'm providing an example how the <img> tag will be rendered: <img src="cid:20161010_dailykpisnapshot_OCuZ4O4.png">

And here's the email headers:

Content-Type: image/png  
Content-Disposition: inline  
Content-Transfer-Encoding: base64  
Content-ID: <20161010_dailykpisnapshot_OCuZ4O4.png>  

David Rader: Relocatable PG RPMs

From Planet PostgreSQL. Published on Oct 19, 2016.

Relocatable PostgreSQL RPMs – You asked and we delivered!

In our years of dealing with PostgreSQL users, one of the main gripes they have with PostgreSQL is not a missing feature or capability but rather it is the fact that the provided rpms are non relocatable rpms. The reasons for wanting relocatable rpms are many including the company they work for has specific standards or they are installing in a lab with other installations and they want to keep their install separate or they just simple want to place things in a more logical/accessible place.

Before now, the only ways available to users to do this were to use the graphical installer or build Postgres from source.  If you are setting up a Devops environment on Linux or the cloud, using a graphical installer doesn’t work well and, let’s be honest, who wants to be pestered  with advertising while doing an installation? Building Postgres from source is definitely another option but most DBAs don’t have the time to deal with working through that process and handling any errors that might occur.  They could hand off the task to their dev or sys admin team but then they will need to wait for that to happen and/or lose control of the way it is built. And besides, you want a consistent binary build on each server in your organization, not a compiled from source version that uses different versions of libraries or config settings on different servers over time.

So, in our Postgres by BigSQL distribution, we decided to make it easy for users to install Postgres wherever they want simply by using the 100% open source rpm that we build and make available to everyone. Our rpm does not modify system libraries and by default installs to a user friendly location:


But you have total control - you simply use the —prefix <path> option to tell the package manager to install the software where you want.  For example:
# rpm -ivh --prefix /home/postgres/PG96 postgresql-9.6.0-1-x64-bigsql.rpm
warning: postgresql-9.6.0-1-x64-bigsql.rpm: Header V3 DSA/SHA1 Signature, key ID 38379aec: NOKEY
Preparing...                   ################################# [100%]
Updating / installing...
   1:postgresql96-9.6.0-1      ################################# [100%]
PostgreSQL 9.6 is now installed in /home/postgres/PG96.

To initialize, run /etc/init.d/postgresql-96 start as root user.

And there you have it….PostgreSQL installed where you want it!

Now that you have PostgreSQL installed, be sure to check out how to take the next steps and initialize the PostgreSQL database and use it here:


US PostgreSQL Association: 2016 PgUS Board Election

From Planet PostgreSQL. Published on Oct 18, 2016.

Hello, everyone! I would like to announce the opening of nominations and the voting procedure for the 2016 PgUS board election.

Our nomination period is open until October 25th; candidates may be nominated or self nominate by emailing the corporation secretary (me) at:
I will be posting platforms/bios on the PgUS website by October 26. PgUS members may vote via email to:

read more

Don't keep important data in your Celery queue

By Caktus Consulting Group from Django community aggregator: Community blog posts. Published on Oct 18, 2016.

The Celery library (previous posts) makes it as easy to schedule a task to run later as calling a function. Just change:




and rely on Celery to run it later.

But this introduces a new point of failure for your application -- if Celery loses track of that task and never runs it, then a user will not get an email that we wanted to send them.

This could happen, for example, if the server hosting your Celery queue crashed. You could set up a hot backup server for your queue server, but it would be a lot of work.

It's simpler if you treat your Celery queue like a cache -- it's helpful to keep around, but if you lose it, your work will still get done.

We can do that by changing our pattern for doing things in the background. The key idea is to keep the information about the work that needs to be done in the database, with the rest of our crucial data.

For example, instead of:


we might add a needs_welcome_email Boolean field to our model, and write:

user.needs_welcome_email = True

Now we know from our database that this user needs to get a welcome email, independently of Celery's queue.

Then we set up a periodic task to send any emails that need sending:

def send_background_emails():
    for user in User.objects.filter(needs_welcome_email=True):
        user.needs_welcome_email = False

We can run that every 5 minutes, it'll be quite cheap to run if there's no work to do, and it'll take care of all the "queued" welcome emails that need sending.

If we want the user to get their email faster, we can just schedule another run of the background task immediately:

user.needs_welcome_email = True

And the user will get their email as fast as they would have before.

We will still want to run the background task periodically in case our queued task gets lost (the whole point of this), but it doesn't have to run as frequently since it will rarely have any work to do.

By the way, I learned this while doing a code review of some of my co-worker Karen's code. This ability to continue learning is one of my favorite benefits of working on a team.

Expiring tasks

Now that we've made this change, it opens up opportunities for more improvements.

Suppose we're scheduling our periodic task in our settings like this:

    'process_new_work': {
        'task': 'tasks.send_background_emails',
        'schedule': timedelta(minutes=15),

Every 15 minutes, celery will schedule another execution of our background task, and if all is well, it'll run almost immediately.

But suppose that our worker is unavailable for a while. (Maybe it lost connectivity temporarily.) Celery will keep on queuing our task every 15 minutes. If our worker is down for a day, then when it comes back, it'll see 24*4 = 96 scheduled executions of our task, and will have to run the task 96 times.

In our case, we're not scheduling our task all that frequently, and the task is pretty lightweight. But I've seen times when we had thousands of tasks queued up, and when the workers were able to resume, the server was brought to its knees as the workers tried to run them all.

We know that we only need to run our task once to catch up. We could manually flush the queue and let the next scheduled task handle it. But wouldn't it be simpler if Celery knew the tasks could be thrown away if not executed before the next one was scheduled?

In fact, we can do just that. We can add the expires option to our task when we schedule it:

    'process_new_work': {
        'task': 'tasks.send_background_emails',
        'schedule': timedelta(minutes=15),
        'options': {
            'expires': 10*60,  # 10 minutes

That tells Celery that if the task hasn't been run within 10 minutes of when we schedule it, it's not worth running at all and to just throw it away. That's fine because we'll schedule another one in 5 more minutes.

So now what happens if our workers stop running? We continue adding tasks to the queue - but when we restart the worker, most of those tasks will have expired. Every time the worker comes to a task on the queue that is expired, it will just throw it away. This allows it to catch up on the backlog very quickly without wasting work.


As always, none of these techniques are going to be appropriate in all cases. But they might be handy to keep in your toolbox for the times when they might be helpful.

Craig Kerstiens: PGConf Silicon Valley - A look at the Postgres themes

From Planet PostgreSQL. Published on Oct 18, 2016.

PGConf Silicon Valley, November 14-16, is barely under a month away and it’s shaping up to be another great conference this year. Last year we had over 300 attendees to make it one of the larger Postgres conferences around, and this year we’re expecting even more. While we’ve had the schedule online for a few months now, we thought it might be interesting to take a step back and see if any strong themes emerged. Perhaps unsurprisingly, there were a few strong themes that start to tell a bit of a story of some of Postgres recent momentum as well as future direction. Here’s a look at a few of the strong themes that are highlighted at the conference:

NoSQL and Postgres

Postgres has had some form or another of NoSQL support stretching back for years to hstore or even the XML datatype. Then came JSONB, which is extremely flexible and can also be performant. Given all the excitement around it, it’s no suprise there’s a lot of great talks, but all are not only touting the benefits of JSONB. Some talks will take a hard look at trade-offs as well as benefits of JSONB vs. other systems for similar problems. Just a few that will touch on Postgres and when it makes sense for NoSQL:


And while Postgres continues with each major release to make a number of improvements, there are a lot of advancements in the form of extensions. Postgres extension APIs make it possible to add lots of rich functionality to Postgres, without having to have code committed to core. This year we have a strong line-up of talks that surface some of the power of these extensions, which should only continue to grow as an ecosystem over time:

Postgres internals

Of course no Postgres conference would be complete without a number of committers talking about all the interesting internal aspects of Postgres. If you’ve ever wanted to learn how things work under the covers here’s your chance. Just a few of our personal favorites include:

Whether you’re an application developer, DBA, or just love Postgres, we expect you’ll find the line-up and conference as a whole a great time to learn and dig into Postgres. Grab your ticket, and we’ll see you there, and if you have any questions on sponsorship or otherwise about the conference please reach out.

Craig Ringer: BDR History and future

From Planet PostgreSQL. Published on Oct 17, 2016.

BDR is both a patch to PostgreSQL core and an extension on top of PostgreSQL core. How did that come about, and what’s it’s future?

Development of BDR was initiated around the time PostgreSQL 9.2 was in development. Arguably earlier if you count things like the extension mechanism. The goal of BDR is, and has always been, to add necessary features to core PostgreSQL to perform asynchronous loosely-coupled multi-master logical replication.

BDR improvements to core PostgreSQL

Since it’s such a large set of changes it was necessary to structure development as a series of discrete features. A natural dividing line was “things that require changes to the core PostgreSQL code” vs “things that can be done in an extension”. So the code was structured accordingly, making BDR a set of patches to core plus an extension that uses the added features to implement a multimaster node. The required features were developed in a branch of PostgreSQL and then extracted and submitted one by one to PostgreSQL core. BDR is, and has always been, much too big to simply commit to PostgreSQL in one go.


Getting the BDR extension running on unmodified PostgreSQL

BDR 1.0 is still in two parts – a set of patches to add necessary features to core PostgreSQL and an extension that uses the features to implement multimaster. The original goal was to have BDR running on stock 9.4 without patches, but it just took too long to get all the required patches through the community process and into core. This isn’t entirely a bad thing, since it’s led to the eventual features being of higher quality when they were committed to 9.4, 9.5 and 9.6.

Now, as of PostgreSQL 9.6, all of the patches required to make it possible to implement BDR-style replication have been included in PostgreSQL core. As mentioned in an earlier post, BDR on 9.6 should run as an extension on an unmodified PostgreSQL. The implementation of BDR itself is still an outside extension, so this doesn’t mean “BDR is in 9.6″, but it’s big progress.

Other enhancements related to BDR

Meanwhile, the “pglogical” single-master logical replication tool was extracted from BDR, enhanced, simplified, and submitted to core PostgreSQL as a further building block toward getting full multi-master logical replication into core. The submission was not successful in its original form, but the 2ndQuadrant team is now working on a replication feature for PostgreSQL 10 that is based on that work.

Once logical replication (not just logical decoding) is incorporated into PostgreSQL core, the BDR extension will be adapted to use the in-core logical replication features. Over time, more parts of the functionality of the BDR extension will be submitted to PostgreSQL until all BDR functionality is in community PostgreSQL.

This process has taken years and still has a ways to go. PostgreSQL’s development is careful and prioritizes stability. Submissions often need multiple rounds of revisions before they are accepted for community distribution. Sometimes they’re completely rejected and a different design must be pursued.

In the meantime, the BDR extension – and pglogical – will continue to meet user needs.

Features added to core PostgreSQL for BDR

Along the way, we’ve delivered some great features to PostgreSQL that other projects can use too:

  • Background workers let you run tasks inside PostgreSQL, like schedulers, etc. They’ve already been used to help with things like parallel query.
  • Event triggers and DDL deparse hooks let you set up user defined triggers on database change events like table creation. They’re the foundation of DDL replication in BDR and are usable by other products that are interested in tracking, auditing, and replicating changes to database structure.
  • Replication slots simplify WAL retention management for physical standbys and provide a foundation for logical decoding of changes.
  • Replication origins are the other side of replication slots. They let a downstream server efficiently keep track of how much they’ve replayed from a replication slot. Unlike most of these features, replication origins are not useful for much except logical replication.
  • Logical decoding reads a stream of low level binary changes from WAL on a server and turns it into a stream of logical row changes in a format suitable for replication across PostgreSQL versions or even to different products and applications. It’s the foundation on which BDR, pglogical, and in-core logical replication are built.
  • Logical WAL messages let extensions, user defined functions, etc write special messages into WAL that are not associated with any particular table. They may optionally bypass normal transactional rules, like a very limited version of an autonomous transaction. These are used when different nodes signal each other about changes; for example, BDR uses them for global DDL locking. These messages will also be useful to applications that adopt the logical decoding interface for change streaming, auditing, etc – for example, to record the identity of a user who performs an action when streaming audit history to Kafka.

Django Tips #18 Difference Between ugettext And ugettext_lazy

By Simple is Better Than Complex from Django community aggregator: Community blog posts. Published on Oct 17, 2016.

The Django translation API provides several utility functions to help you translate your application. They are all available in the django.utils.translation module. For the most cases you will be using ugettext() and ugettext_lazy().

The “u” prefix stands for “unicode”, and usually it is a better idea to use ugettext() instead of gettext(), and ugettext_lazy() instead of gettext_lazy(), since for the most part we will be working with international charsets.

As the name suggests, the “lazy” version of the function holds a reference to the translation string instead of the actual translated text, so the translation occurs when the value is accessed rather than when they’re called.

It’s very important to pay attention to this details, because in a Django project there are several cases where the code is only executed once (on Django startup). That happens with definition modules like models, forms and model forms.

So, what would happen if you use ugettext() (instead of ugettext_lazy()) in a model definition (let’s say on field labels):

  1. Django starts up, the default language is english;
  2. Django picks the english version of the field labels;
  3. The user changes the website language to spanish;
  4. The labels are still displayed in english (because the field definition is called only once; and by the time it was called, the language was other).

To avoid this bahavior, you have to use properly the utility functions.

Here goes a summary of when to use ugettext() or ugettext_lazy():

  • ugettext_lazy():
    • (fields, verbose_name, help_text, methods short_description);
    • (labels, help_text, empty_label);
    • (verbose_name).
  • ugettext():
    • Other modules similar to view functions that are executed during the request process

PyData Carolinas 2016 Recap

By Caktus Consulting Group from Django community aggregator: Community blog posts. Published on Oct 17, 2016.

We had a great time at the inaugural PyData Carolinas hosted nearby at IBM in the Research Triangle Park. People from Caktus presented a number of talks and the videos are now up online:

There were also many more fascinating talks about how people in and around North and South Carolina are using Python to do data analysis with Pandas, Jupyter notebooks, and more. It was a great event that brought together the strong communities around data and Python locally to celebrate their overlapping interests. We had a great time meeting folks and reconnecting with old friends at the after hours events hosted by MaxPoint and the Durham Convention & Visitors Bureau. Many thanks to all of the local organizers and sponsors who worked together to put on a great program and event. We can’t wait until the next one!

Serverless: Gatekeeping and Profit Margins

By Will Larson from Django community aggregator: Community blog posts. Published on Oct 17, 2016.

In the beginning, we had servers behind a load balancer. Each server of a given kind (api servers, frontend servers, etc) had the same set of processes running on them, and each application bound to the same statically allocated port on every server.

Loadbalancer directing requests to statically allocated ports.

This worked well! Over time we wanted to take better advantage of our often under utilized servers, and adjusting our CPU to memory to IOPS ratio during the three years SKU depreciation cycle wasn’t fast enough as our system architectures continued to quickly evolve.

This and the increasingly wide availablity of reliable cloud providers drove virtual machines adoption, which maintained the existing server level abstraction, but allowed multiple virtual servers to cotenant on a given physical server. It didn’t change the load balancing story or the static port allocation much though: all servers of the same kind continnued to run the same processes.

VMs enabled increased hardware efficiency through contenanting, but you were in two scenarios:

  1. Running on the cloud, in which case improved bin packing strategies benefit the cloud provider, not you.
  2. Running on your own hardware, in which case you were very likely statically mapping VMs onto servers, such that improving your VM to server allocation was time consuming, and at any given point your allocation was fairly sub-optimal, although better than the pre-VM scenario since now you have smaller pieces to allocate into the bins.

Manual bin packing has become increasing challenging as patterns like service oriented architecture and microservices have exploded the number of bespoke processes a given company runs from a handful to hundreds (or even thousands). Fortunately, over the past few years, several long-running trends have started to come together to obsolete manual bin packing!

First, real-time configuration mechanisms, like etcd and consul, have made refreshing configuration files (for e.g. a software loadbalancer like HAProxy) have latencies of seconds instead of minutes or hours. This is an important precursor to dynamic process scheduling, as you can’t move processes efficiently if there is a long delay between a process being scheduled on a server and sending it traffic.

Next, the move to containers like rkt and docker has crisply decoupled the environment processes run in from the servers they run on, making it possible for infrastructure engineers to place any stateless service on almost any server within their fleet. This has eroded the idea of a static mapping between services and server roles: where once your app servers ran some specific list of services defined in Puppet, now one server role can run all your services, and the services no longer need to be defined within your configuration management (although if you’re not using a scheduler, they still have to be defined somewhere, which might end up being your config management).

(With due apologies, I’m very much ignoring stateful services here, including databases and such. My sense is that each stateful service will continue being statically mapped to a server role for the time being in most infrastructure setups. This will eventually no longer be true as we move towards local-storage-as-a-service technologies like EBS and GFS/Colossus, or as stateful services move to models with sufficient small overlapping shards that recreating a lost node is a trivial event that can happen constantly in the background without degrading the system’s behavior.)

Finally, building on both real-time configuration to drive service discovery and containers to allow a single server role for all stateless services, we’re entering a golden age of process schedulering, like kubernetes and mesos, which elevate us from periodic human-or-script driven static allocation to dynamic allocation that occurs in something approaching real-time (perhaps its every hour or day, but certainly much faster than waiting for someone to manually rebalance your cluster).

Dynamically allocated ports and services behind a load balancer.

From an infrastructure perspective, these combine into something pretty remarkable!

First, we’ve moved from static port allocation to dynamic port allocation, such that you can run many copies of a given application on a server. (You can also run zero instances, wasting fewer resources on idle service as compared to the static mapping of service to a given server role.)

Next, we’re able to dynamically scale our services, bringing the optimizations, and the pain, of AWS’ autoscaling groups to our infrastructures, even if we’re running on physical hardware. This gives us a programmatic interface to automate efficient bin packing, allowing us to get some nice cost/efficiency benefits without harming any humans in the process.

Most importantly, we have this phenomenal separation of concerns between the product engineers writing and maintaining the processes and the infrastructure engineers who are providing the platform which schedules those processes. Service owners can pick the right tooling for their needs, infrastructure can run the built container without any awareness of the container’s contents, and the two sides rely on the backpressure from quality of service and cost accounting to steer towards a mutually sustainable balance without the upfront gatekeeping and tight coordination introduced by the static service to server mapping model.

The above model still depends on persistent processes which can accept routed requests, or you could have persistent processes pulling from a queue, but in either case the processes are long-running. As we start drilling into our services usage patterns, a large number of these services probably get close to no traffic, but we’re still running at least three instances for redundancy purposes.

Let’s imagine we create a new scheduler, which doesn’t schedule processes, but instead it pre-positions containers across your fleet such that you could start the contained processes without having to first copy the container’s contents to the server. Now let’s imagine we have local SSDs on our servers, or that we have network local storage which is fast enough that you can read the container in a handful of milliseconds. This would allow each of your servers to have the potential to run hundreds or thousands of services.

A rack-local image registry to allow quick spinup.

Building on our prepositioned containers, let’s say we can start one of them in a couple hundred milliseconds. Now incoming requests to our load balancer look a bit different:

  1. If there aren’t instances already running, ask the scheduler to start one of the preposition containers.
  2. Route to an existing containers.

Now, we only need to persistently run processes which have frequent incoming traffic. For anything which takes less than two requests per second, it is more efficient for us to recycle immediately, although the exact math here depends a bit on how strongly we want to weight the latency cost. Throughput shouldn’t be much of an issue because we’ll amortize the spinup cost across many requests if we get a spike. For throughput beyond the capacity of a single process, we can make our load balancer a bit smarter such that it can add capacity if a service’s latency grows too high (modulo some mechanism to prevent infinite autoscaling in degenerate cases).

This is pretty awesome, and has reduced our incremental cost-per-service to almost zero from a scheduling infrastructure and infrastructure engineering perspective, but there are two interesting things happening here:

First, although we’ve reduced the infrastructure cost of new services to near zero, the human costs for service owners to maintain a large number of services is not going down. Quite the opposite: historically the friction of introducing more infrastructure has somewhat unintentionally kept the cardinality of libraries and frameworks at a given company low. Without those constraints, companies often lack an immune system to prevent the introduction of new technologies, and the number starts to grow linearly with either time or developer headcount or both!

The other issues is that while the friction of creating services has gone down, and depending on your workload, the cost of running services is now bound on disk costs instead of cpu/memory, you’re still paying some fixed cost per service. Getting that cost as low as possible depends mostly on container size, which varies greatly. Looking at Docker’s layering system, if you can drive towards a highly unified stack of frameworks and dependencies, then you can reduce the space per container to something remarkably small.

(In terms of active load, the underlying platform’s cost efficiency also depends a great deal on programming language, so I imagine we’ll see a lot of movement towards highly cpu and memory efficient languages like Go for serverless applications. Node seems like a early focus, although it’s not particularly CPU or memory efficient and has a fairly high latency garbage collector, but it is relatively decoupled from the underlying O/S, and has a large library and developer ecosystem. Seemingly, this early on it probably makes more sense to drive adoption and efficiency.)

These are, in my opinion, the twin concerns driving serverless adoption: on one hand, companies trying to regain centralized control of their software now that infrastructure friction doesn’t serve as an inadvertent gatekeeper, and on the other cloud providers using aws lambda and cloud functions to drive cost efficiencies in their massively multi-tenant clouds.

Neither of these are reasons why we shouldn’t be moving to serverless!

It’s great if clouds can get more cost efficient, especially if you’re running an internal cloud, and although I’m skeptical of the gatekeeping model of top-down architecture, it’s also clear that most companies struggle to figure out an effective alternative to nudge teams to make company-optimal decisions instead of team-local decisions. (Although I suppose if we apply the Innovator’s Dilemma’s lessons, it seems possible that being company-optimal in the short-term is actually company sub-optimal in the long run.)

My other ending thought is that I suspect that developing serverless applications is going to feel a lot more like developing monoliths than developing microservices. Even though they’re being scheduled and perhaps even deployed as independent units of code, their aggressive minimalism will force them to lean very heavily on the framework they run in, and you’ll either be back in the monolith change coordination challenge or you’ll be in the degenerate microservices scenario where you have to individually migrate every function to each new version. The former sounds a lot more pleasant.

Giuseppe Broccolo: Back to the Future Part 3: pg_rewind with PostgreSQL 9.6

From Planet PostgreSQL. Published on Oct 17, 2016.


This is the third and last part of blog articles dedicated to pg_rewind. In the two previous articles we have seen how pg_rewind is useful to fix split-brain events due to mistakes in the switchover procedures, avoiding the need of new base backups. We have also seen that this is true for simple replication clusters, where more standby nodes are involved. In this case, just two nodes can be fixed, and the other ones need a new base backup to be re-synchronised. pg_rewind for PostgreSQL 9.6 is now able to work with complex replication clusters.

Indeed, pg_rewind has been extended so it can view the timeline history graph of an entire HA cluster, like the one mentioned in my previous blog article. It is able to find out the most recent, shared point in the timeline history between a target and a source node, and then it performs a rewind operation from this point – not simply from the last checkpoint of the master before the standby promotion, as in PostgreSQL 9.5.

So, let’s consider again the same cluster, but now based on PostgreSQL 9.6:

~$ # Set PATH variable
~$ export PATH=/usr/pgsql-9.6/bin:${PATH}
~$ # This is the directory where we will be working on
~$ # Feel free to change it and the rest of the script
~$ # will adapt itself
~$ WORKDIR=/var/lib/pgsql/9.6
~$ # Environment variables for PGDATA and archive directories
~$ # Create the archive directory
~$ mkdir -p ${ARCHIVE_DIR}
~$ # Create the HA cluster
~$ initdb --data-checksums -D ${WORKDIR}/master
~$ cat >> ${MASTER_PGDATA}/postgresql.conf <<EOF
~$ archive_command = 'cp %p ${ARCHIVE_DIR}/%f'
~$ archive_mode = on
~$ wal_level = hot_standby
~$ max_wal_senders = 10
~$ min_wal_size = '32MB'
~$ max_wal_size = '32MB'
~$ hot_standby = on
~$ wal_log_hints = on
~$ EOF
~$ cat >> ${MASTER_PGDATA}/pg_hba.conf <<EOF
~$ # Trust local access for replication
~$ local replication replication trust
~$ EOF
~$ pg_ctl -D /var/lib/pgsql/9.6/master -l ${WORKDIR}/master.log start
~$ psql -c "CREATE USER replication WITH replication"
~$ pg_basebackup -D ${STANDBY1_PGDATA} -R -c fast -U replication -x
~$ echo "port = 5433" >> ${STANDBY1_PGDATA}/postgresql.conf
~$ pg_ctl -D ${STANDBY1_PGDATA} -l ${WORKDIR}/standby.log start
~$ pg_basebackup -D ${STANDBY2_PGDATA} -R -c fast -U replication -x
~$ echo "port = 5434" >> ${STANDBY2_PGDATA}/postgresql.conf
~$ pg_ctl -D ${STANDBY2_PGDATA} -l ${WORKDIR}/standby2.log start

Let’s simulate a wrong switchover, where one of the two standby servers has been promoted as new master, and the master and the other standby remained an independent HA cluster:

~$ pg_ctl -D ${STANDBY1_PGDATA} promote

Here, the promoted standby has the timeline 2, and the other two nodes remained with timeline 1.

Now let’s complete the “split-brain” by making the master and its standby creating a new table: it will be not visible in the promoted standby.

Now the goal is to recreate the original HA cluster, with a master with the same content it had before the split-brain (i.e. without the last created table), and the two standbys.

So, since with PostgreSQL 9.6 pg_rewind is able to make every node in a HA cluster become a master, the idea is to:

  1. Stop the other standby
  2. Stop the old master, and resync it with the promoted standby through pg_rewind
  3. Change the port and the primary_conninfo in the configuration in order to follow up the promoted standby
  4. Resync the other standby with the promoted standby through pg_rewind
  5. Change the port and the primary_conninfo in the configuration in order to follow up the promoted standby

Let’s see the results:

~$ pg_ctl -D ${STANDBY2_PGDATA} stop
waiting for server to shut down.... done
server stopped
~$ pg_ctl -D ${MASTER_PGDATA} stop
waiting for server to shut down.... done
server stopped
~$ pg_rewind --target-pgdata=${MASTER_PGDATA} --source-server="port=5433 user=postgres dbname=postgres"
servers diverged at WAL position 0/A0002C8 on timeline 1
rewinding from last common checkpoint at 0/A000220 on timeline 1
~$ pg_rewind --target-pgdata=${STANDBY2_PGDATA} --source-server="port=5433 user=postgres dbname=postgres"
servers diverged at WAL position 0/A0002C8 on timeline 1
rewinding from last common checkpoint at 0/A000220 on timeline 1

Change the configurations of both the old master and the other standby, and than start the two nodes:

~$ pg_ctl -D ${MASTER_PGDATA} start
~$ pg_ctl -D ${STANDBY2_PGDATA} start

Now the three nodes are up, and:

  • There’s a master with two standbys, as in the beginning
  • The created table is not visible, so the data content is the same as in the beginning

Cool!! Think about the need to re-synchronise two nodes with a full base backup… :S


pg_rewind is one of the most useful features working with physical replication. It allows you to avoid re-synchronisation through a full base backeup in case of an accidental split-brain.

PostgreSQL 9.6 adds a new, powerful feature to pg_rewind: in the case of HA clusters with a complex structure, it is able to recreate the original status starting independently from every node of the cluster.

As a final recommandation: take care of WAL archiving! Generally, with physical replications, the DBAs base their HA clusters just on streaming connections; to use pg_rewind, you must have configured WAL archiving, so you have a place where to get the needed WALs in the case they are no more available on the master. I recommend considering the use of Barman, with its feature get-wal, to retrieve the eventually needed WALs.

Handling statuses in Django

By Eat Some Code from Django community aggregator: Community blog posts. Published on Oct 16, 2016.

Whether you're building up a CMS or a bespoke application, chances are that you will have to handle some states / statuses. Let's discuss your options in Django.

Jason O'Donnell: pgAudit: Auditing Database Operations Part 2

From Planet PostgreSQL. Published on Oct 14, 2016.


In the last blog post, pgAudit was configured to audit entire classes of statements (session auditing). Session auditing works great, but it can generate a lot of logs and not every administrator needs all that information. In this blog post pgAudit will be configured to use an auditing role to watch only specific objects.

Luca Ferrari: PGDay.IT 2016: there's some extra time before the CFP is out!

From Planet PostgreSQL. Published on Oct 14, 2016.

Ehi, the CFP for the PGDay.IT 2016 (Italian tenth edition of the PGDay) has been extended until next Saturday 22 October at 23:59 (Rome).
Don't miss the opportunity to be a speaker at one of the most well known PGDay!

Shaun M. Thomas: PG Phriday: Perfectly Logical

From Planet PostgreSQL. Published on Oct 14, 2016.

One of the things Postgres has been “missing” for a while is logical replication based on activity replay. Until fairly recently, in order to replicate single tables from one database to another, we had to encumber the table with performance-robbing triggers coupled to a third party daemon to manage transport. Those days might finally be behind us thanks to pglogical.

But is it easy to use? Let’s give it a try on our trusty sensor_log table. First, we’ll need to create everything on the donor node:

CREATE TABLE sensor_log (
  location      VARCHAR NOT NULL,
  reading       BIGINT NOT NULL,
  reading_date  TIMESTAMP NOT NULL
INSERT INTO sensor_log (location, reading, reading_date)
SELECT % 1000, % 100,
       CURRENT_DATE - ( || 's')::INTERVAL
  FROM generate_series(1, 1000000) s(id);
SELECT pglogical.create_node(
    node_name := 'prod_sensors',
    dsn := 'host=localhost port=5432 dbname=postgres'
SELECT pglogical.create_replication_set(
    set_name := 'logging',
    replicate_insert := TRUE, replicate_update := FALSE,
    replicate_delete := FALSE, replicate_truncate := FALSE
SELECT pglogical.replication_set_add_table(
    set_name := 'logging', relation := 'sensor_log', 
    synchronize_data := TRUE

After all of that, we have a replication set containing a single table representing one million rows of sensor data. At this point, any number of subscribers could connect to the replication set and request its contents. After that, all inserts would also be replayed on the subscriber once they are detected in the Postgres transaction log.

In a second Postgres instance, we would do this to “consume” the table:

CREATE TABLE sensor_log (
  id            INT PRIMARY KEY NOT NULL,
  location      VARCHAR NOT NULL,
  reading       BIGINT NOT NULL,
  reading_date  TIMESTAMP NOT NULL
SELECT pglogical.create_node(
    node_name := 'sensor_warehouse',
    dsn := 'host=localhost port=5999 dbname=postgres'
SELECT pglogical.create_subscription(
    subscription_name := 'wh_sensor_data',
    replication_sets := array['logging'],
    provider_dsn := 'host=localhost port=5432 dbname=postgres'
SELECT pg_sleep(5);
SELECT COUNT(*) FROM sensor_log;

We actually did a few things here, so let’s break it down. We began by creating the table structure itself. This will act as a container for the incoming data. Then we created the node like we did on the origin, and then subscribed to the provider itself.

We should note that it wasn’t strictly required to create the table beforehand. The create_subscription function has a parameter called synchronize_structure that uses pg_dump to obtain the table DDL during the subscription phase for replay on the subscriber. Unfortunately, this operation does not restrict itself to the tables in the replication set for some reason. As a result, any conflicting objects in the existing schema will cause the table import to fail.

Once we’ve established the subscription, we merely need to wait a few seconds for the initial data copy to complete. This is only one million rows, so we don’t have to wait very long. After that, all subsequent inserts should also show up on this subscriber.

One really cool thing about pglogical is that it takes advantage of Postgres 9.4+ background workers. This means there’s no external daemon sitting around watching a queue, or the transaction logs, or any other source. There’s an actual Postgres backend supervising the Postgres replication stream, and it will capture applicable content for the sensor_log table. The extension becomes the management daemon as if it were a native Postgres feature.

And it gets even better. For those of us that rely on large warehouses that might accumulate data from one or more active production locations for several years, this is something of a golden fleece. Trigger-based replication falls flat here because synchronization means synchronization. If we need a table to just sit and accumulate data, it was ETL, ad-hoc copy scripts, or nothing. But what if we set up our sensor_log table on the subscriber just a bit differently?

SELECT pglogical.drop_subscription(
    subscription_name := 'wh_sensor_data'
TRUNCATE TABLE sensor_log;
CREATE TABLE sensor_log_part (
) INHERITS (sensor_log);
CREATE OR REPLACE FUNCTION f_redirect_sensor_log()
AS $$
  INSERT INTO sensor_log_part VALUES (NEW.*);
$$ LANGUAGE plpgsql;
CREATE TRIGGER t_redirect_sensor_log_bi
       EXECUTE PROCEDURE f_redirect_sensor_log();
ALTER TABLE sensor_log
      ENABLE ALWAYS TRIGGER t_redirect_sensor_log_bi;
SELECT pglogical.create_subscription(
    subscription_name := 'wh_sensor_data',
    replication_sets := array['logging'],
    provider_dsn := 'host=localhost port=5432 dbname=postgres'
SELECT pg_sleep(5);
SELECT COUNT(*) FROM sensor_log;

This is where the magic really resides. Instead of inserting data into the table itself, we were able to redirect it into a table partition. This is important, because some of the larger Postgres warehouses rely on partitions to distribute maintenance and risk. Instead of one single 4TB table, we might have a dozen 350GB tables.

This proof of concept suggests pglogical is compatible with existing partition systems. And that is important if we’re trying to simplify our stack by removing complex ETL processes. Since we created the replication set to only capture insert activity, we can purge the origin of old data as frequently as we wish, and it will remain in the warehouse indefinitely.

This comes with one caveat, though. Notice that last ALTER TABLE statement where we marked our trigger to always fire? This is necessary because pglogical uses copy mechanisms that otherwise circumvent trigger logic. If we didn’t include that statement, the sync would have dumped all of the rows into the base sensor_log table instead of our target partition.

This last detail raises an important question: are common partition management extensions like pg_partman really compatible with this use case? Automated partition management libraries must regularly modify the underlying trigger to ensure current data is redirected into the appropriate partition. In this case, the answer is highly reliant on how the triggers are redefined.

If partition management systems simply replace the underlying function, all is safe. The trigger definition itself was not dropped and recreated without the ALWAYS modification. This would be the preferred method since it doesn’t require a lock on the table to recreate the trigger definition. Unfortunately this is only an assumption. Using the “correct” approach also doesn’t prevent the extra step of manually marking the triggers as ALWAYS post-creation.

For pg_partman, we’d do something like this after initial partition setup on the warehouse node:

DO $$
    trigger_name VARCHAR;
    FOR TABLE_NAME, trigger_name IN
        SELECT tgrelid::regclass::text, tgname
          FROM pg_trigger
         WHERE tgname LIKE '%\_part\_trig'
                ' ENABLE ALWAYS TRIGGER ' || trigger_name;
$$ LANGUAGE plpgsql;

Gross. This also precludes any new tables from being added to the subscription set without this kind of interception. It would be better if popular partition libraries offered a parameter for always enforcing triggers. Or perhaps a more universal approach like some kind of post-definition hook for executing an arbitrary function that could handle advanced use cases. I promise I’m not picking on pg_partman! This applies even to not-so-popular partition extensions like my own tab_tier.

It’s a crazy world out there in extension land. Apparently we have to keep our eyes peeled for exciting new functionality like those pglogical introduces, and any of the ensuing implications for potentially related extensions. Either way, our warehouses are happier for it!

How to Deploy a Django Application to Digital Ocean

By Simple is Better Than Complex from Django community aggregator: Community blog posts. Published on Oct 14, 2016.

DigitalOcean is a Virtual Private Server (VPS) provider. In my opinion it’s a great service to get started. It’s cheap and very simple to setup. In this tutorial I will guide you through the steps I go to deploy a Django application using Ubuntu 16.04, Git, PostgreSQL, NGINX, Supervisor and Gunicorn.

In this tutorial we will be deploying the following Django application:

It’s just an empty Django project I created to illustrate the deployment process. If you are wondering about the name, it is the repository name suggestion GitHub gave.

Anyway! Let’s get started.

Create a New Droplet

Pick the Ubuntu 16.04.1 distribution:

Digital Ocean

Select the size of the Droplet (cloud server):

Digital Ocean

Select the region you want to deploy:

Digital Ocean

Finally pick a name for the Droplet:

Digital Ocean

And click on Create. After that you will see the recently created droplet in your profile:

Digital Ocean

You will receive the root password via email. Now pick the IP Address and ssh into the server:

ssh root@


You will be asked to change the root password upon the first login.

If you are using a Unix-like operating system you can use the terminal. If you are on Windows, you can perhaps download PuTTY.

Also if you prefer, you can use the Digital Ocean’s console:

Digital Ocean Console

Installing the Server Dependencies

First thing let’s upgrade the packages:

sudo apt-get update
sudo apt-get -y upgrade

Install the dependencies to use PostgreSQL with Python/Django:

sudo apt-get -y install build-essential libpq-dev python-dev

Install the PostgreSQL Server:

sudo apt-get -y install postgresql postgresql-contrib

Install NGINX, which will be used to serve static assets (css, js, images) and also to run the Django application behind a proxy server:

sudo apt-get -y install nginx

Supervisor will start the application server and manage it in case of server crash or restart:

sudo apt-get -y install supervisor

Enable and start the Supervisor:

sudo systemctl enable supervisor
sudo systemctl start supervisor
Python Virtualenv

The Django application will be deployed inside a Python Virtualenv, for a better requirements management:

sudo apt-get -y install python-virtualenv

Configure PostgreSQL Database

Switch users:

su - postgres

Create a database user and the application database:

createuser u_urban
createdb urban_prod --owner u_urban
psql -c "ALTER USER u_urban WITH PASSWORD '123'"

PS: Make sure to pick a secure password! I’m using 123 for simplicity sake.

We can now go back to the root user, simply exit:


Configure The Application User

Create a new user with the command below:

adduser urban

Usually I just use the application name. You will be asked a few questions. Sample of the output below:

Adding user `urban' ...
Adding new group `urban' (1000) ...
Adding new user `urban' (1000) with group `urban' ...
Creating home directory `/home/urban' ...
Copying files from `/etc/skel' ...
Enter new UNIX password:
Retype new UNIX password:
passwd: password updated successfully
Changing the user information for urban
Enter the new value, or press ENTER for the default
  Full Name []:
  Room Number []:
  Work Phone []:
  Home Phone []:
  Other []:
Is the information correct? [Y/n]

Add the user to the list of sudoers:

gpasswd -a urban sudo

Switch to the recently created user:

su - urban

Configure the Python Virtualenv

At this point we are logged in with the urban user (or whatever named you selected). We will install our Django application in this user’s home directory /home/urban:

virtualenv .

Activate it:

source bin/activate

Clone the repository:

git clone

This is how the /home/urban directory should look like at the moment:

 |-- bin/
 |-- urban-train/  <-- Django App (Git Repository)
 |-- include/
 |-- lib/
 |-- local/
 |-- pip-selfcheck.json
 +-- share/

First open the urban-train directory:

cd urban-train

Install the project’s dependencies:

pip install -r requirements.txt

At this point you will need to set the database credentials in the file:

    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'urban_prod',
        'USER': 'u_urban',
        'PASSWORD': '123',
        'HOST': 'localhost',
        'PORT': '',

PS: There are better and secure ways to handle SECRET_KEY, database credentials etc. I’m editing it directly in the file for the sake of simplicity. The focus of this tutorial is on the deployment process itself.

Migrate the database:

python migrate

Collect the static files:

python collectstatic

Test if everything is okay:

python runserver

Access the IP Address of your server using port 8000. In my case,

Test Deployment

This is just a test. We won’t be using the runserver to run our application. We will be using a proper application server to securely serve our application.

Hit CONTROL-C to quit the development server and let’s keep moving forward.

Configure Gunicorn

First install Gunicorn inside the virtualenv:

pip install gunicorn

Create a file named gunicorn_start inside the bin/ folder:

vim bin/gunicorn_start

And add the following information and save it:




cd $DIR
source ../bin/activate


exec ../bin/gunicorn ${DJANGO_WSGI_MODULE}:application \
  --name $NAME \
  --workers $WORKERS \
  --user=$USER \
  --group=$GROUP \
  --bind=$BIND \
  --log-level=$LOG_LEVEL \

Make the gunicorn_start file is executable:

chmod u+x bin/gunicorn_start

Create a directory named run, for the unix socket file:

mkdir run

Configure Supervisor

Now what we want to do is configure Supervisor to take care of running the gunicorn server.

First let’s create a folder named logs inside the virtualenv:

mkdir logs

Create a file to be used to log the application errors:

touch logs/gunicorn-error.log

Create a new Supervisor configuration file:

sudo vim /etc/supervisor/conf.d/urban-train.conf



Reread Supervisor configuration files and make the new program available:

sudo supervisorctl reread
sudo supervisorctl update

Check the status:

sudo supervisorctl status urban-train
urban-train                      RUNNING   pid 23381, uptime 0:00:15

Now you can control your application using Supervisor. If you want to update the source code of your application with a new version, you can pull the code from GitHub and then restart the process:

sudo supervisorctl restart urban-train

Where urban-train will be the name of your application.

Configure NGINX

Add a new configuration file named urban inside /etc/nginx/sites-available/:

sudo vim /etc/nginx/sites-available/urban-train


upstream app_server {
    server unix:/home/urban/run/gunicorn.sock fail_timeout=0;

server {
    listen 80;

    # add here the ip address of your server
    # or a domain pointing to that ip (like or

    keepalive_timeout 5;
    client_max_body_size 4G;

    access_log /home/urban/logs/nginx-access.log;
    error_log /home/urban/logs/nginx-error.log;

    location /static/ {
        alias /home/urban/static/;

    # checks for static file, if not found proxy to app
    location / {
        try_files $uri @proxy_to_app;

    location @proxy_to_app {
      proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
      proxy_set_header Host $http_host;
      proxy_redirect off;
      proxy_pass http://app_server;

Create a symbolic link to the sites-enabled dir:

sudo ln -s /etc/nginx/sites-available/urban-train /etc/nginx/sites-enabled/urban-train

Remove NGINX default website:

sudo rm /etc/nginx/sites-enabled/default

Restart NGINX:

sudo service nginx restart

The Final Test

Alright! At this point your application should be up and running! Open the web browser and access it:

Urban Train Deployed

A final test I like to run is rebooting the machine and see if everything restarts automatically:

sudo reboot

Wait a few seconds. Access the website via browser. If it loads normally, means everything is working as expected! All the process are starting automatically.

Updating the Application

Usually that’s the process you will follow when you want to update your Django application:

ssh urban@

source bin/activate
cd urban-train
git pull origin master
python collectstatic
python migrate
sudo supervisorctl restart urban-train


I just wanted to take you through the basic steps of a deployment using Digital Ocean. Actually, for the most part the basic steps will be the same for pretty much every cluod provider.

Please note that there are lots of details regarding security, performance, how you should manage the sensitive data of your application, SSL certificate installation and so on.

I highly recommend reading all the available documentation about deployment on the official Django Docs. Also it’s very important to learn about the tooling you are using. Gunicorn, NGINX, Ubuntu, Supervisor, etc.

Further reading:

I hope you enjoyed this tutorial! If you have any question, please leave a comment below!

Season of fixes

By Griatch's Evennia musings (MU* creation with Django+Twisted) from Django community aggregator: Community blog posts. Published on Oct 13, 2016.

The last few months has been dominated by bug-fixing and testing in Evennia-land. A lot more new users appears to be starting to use Evennia, especially from the MUSH world where the Evennia-based Arx, After the Reckoning is, while still in alpha, currently leading the charge.

With a new influx of users comes the application of all sorts of use- and edge-cases that stretch and exercise the framework in all the places where it matters. There is no better test of code than new users trying to use it unsupervised! Evennia is holding up well overall but there are always things that can be improved. 
  • I reworked the way on-object Attributes was cached (from a stupid but simple way to a lot more sophisticated but harder way) and achieved three times faster performance in certain special cases people had complained about. Other issues also came to view while diving into this, which could be fixed.
  • I reworked the venerable batch command and batchcode processors (these allow to create a game world from a script file) and made their inputs make more sense to people. This was one of the older parts of Evennia and apart from the module needing a big refactoring to be easier to read, some parts were pretty fragile and prone to break. Especially when passing it file names tended to be confusing since it understood only certain relative paths to the files to read in and not even I could remember if one should include the file ending or not. This was cleaned up a lot. 
  • Lots of changes and updates were made to the RPSystem contrib, which optionally adds more advanced roleplaying mechanics to Evennia. The use of this in Evennia's demo game (Ainneve, being separately developed) helps a lot for ironing out any remaining wrinkles.
  • Lots and lots of other fixes and smaller feature updates were done (About 150 commits and 50 Issues closed since end of summer).
A fun thing with a growing user base is that we are also starting to see a lot more Pull requests and contributions. Thanks a lot, keep 'em coming!
  • Map system contrib (merged), for creating a world based on ASCII map. Talking about maps, users contributed not just one but two new tutorials for implementing both static and dynamic maps with Evennia. 
  • Webclient notifications (pending), for making the webclient show us in a clearer way when it gets updated in a different tab. A more advanced implementation awaits the webclient being expanded with a proper client-side option window; there is currently a feature request for this if anyone's interested in taking it on.   
  • AI system contrib (pending). This is a full AI backend for adding complex behaviors to game agents. It uses Behavioral trees and is designed to be modified both in code and from inside the game.
  • Action system contrib (pending). This contrib assigns the actions of Characters a time cost and delays the results of commands the given time. It also allows players to go into turn-based mode to enforce a strict action order. 
  • Lots of now closed PRs were contributed by the Arx lead developer to fix various bugs and edge-cases as they came up in live use.
The fixing and tightening of the nuts and bolts will likely continue the remainder of the year. I'm currently working on a refactoring of the way command sets are merged together (see the end of my blog post on Evennia in pictures for a brief summary of the command system). But with so much new blood in the community, who can tell where things will turn from here!

How to Use Django's Generic Relations

By Simple is Better Than Complex from Django community aggregator: Community blog posts. Published on Oct 13, 2016.

You probably have already seen Django’s ContentTypes and wondered how to use it or what is it for anyway. Basically it’s a built in app that keeps track of models from the installed apps of your Django application. And one of the use cases of the ContentTypes is to create generic relationships between models. That’s what this post is about.


If you didn’t remove anything from settings generated by the python startproject command, it’s probably already installed. Also Django’s built in authentication system relies on it.

Basically just make sure you have it in your INSTALLED_APPS:




Example Scenario

Let’s say we have one social app where the users can ask and answer questions, up vote, down vote, favorite a question, like a post in the website, etc.

To keep track of that we create a model named Activity. See below:

class Activity(models.Model):
    FAVORITE = 'F'
    LIKE = 'L'
    UP_VOTE = 'U'
    DOWN_VOTE = 'D'
        (FAVORITE, 'Favorite'),
        (LIKE, 'Like'),
        (UP_VOTE, 'Up Vote'),
        (DOWN_VOTE, 'Down Vote'),

    user = models.ForeignKey(User)
    activity_type = models.CharField(max_length=1, choices=ACTIVITY_TYPES)
    date = models.DateTimeField(auto_now_add=True)
    post = models.ForeignKey(Post, null=True)
    question = models.ForeignKey(Question, null=True)
    answer = models.ForeignKey(Answer, null=True)
    comment = models.ForeignKey(Comment, null=True)

So an Activity can possibly interact with a Post, Question, Answer or a Comment instance. In a practical scenario an Activity instance would represent a single interaction. For example, the User with ID 1 up voted a Question with ID 125:

Activity.objects.create(user=1, activity_type='U', question=125)

And if I wanted to calculate how many up votes the Question 125 received, I could do something like that:

question = Question.objects.get(pk=125)
up_votes = question.activity_set.filter(activity_type=Activity.UP_VOTE)

# Display how many up votes
count = up_votes.count()

# Display the names of users who up voted
up_voters = up_votes.values_list('user__first_name')

In a similar way we could work with the Post, Answer and Comment models.

Using the Generic Relations

If we wanted to achieve the same result using Generic Relations, here is what the Activity models should look like:

from django.contrib.contenttypes.fields import GenericForeignKey
from django.contrib.contenttypes.models import ContentType

class Activity(models.Model):
    FAVORITE = 'F'
    LIKE = 'L'
    UP_VOTE = 'U'
    DOWN_VOTE = 'D'
        (FAVORITE, 'Favorite'),
        (LIKE, 'Like'),
        (UP_VOTE, 'Up Vote'),
        (DOWN_VOTE, 'Down Vote'),

    user = models.ForeignKey(User)
    activity_type = models.CharField(max_length=1, choices=ACTIVITY_TYPES)
    date = models.DateTimeField(auto_now_add=True)

    # Below the mandatory fields for generic relation
    content_type = models.ForeignKey(ContentType, on_delete=models.CASCADE)
    object_id = models.PositiveIntegerField()
    content_object = GenericForeignKey()

Now we are no longer keeping ForeignKey to other models we want to track the favorite, like, up vote and down vote activities. Meaning we can now track those activities to any model we want without having to modify the Activity model.

The relation is created in the model you want to track the Activity:

from django.db import models
from django.contrib.contenttypes.fields import GenericRelation

from activities.models import Activity

class Post(models.Model):
    likes = GenericRelation(Activity)

class Question(models.Model):
    activities = GenericRelation(Activity)

class Answer(models.Model):
    votes = GenericRelation(Activity)

class Comment(models.Model):
    likes = GenericRelation(Activity)

This also enables you to define a more meaningful name for the relations. For example, the Users can only interact with Post and Comment models to like it. While with the Answer model, they can only up vote/down vote. And finally with the Question model, the Users can up vote/down vote and favorite it.

Now if you want to like a Post, you could do something like this:

# Get the post object
post = Post.objects.get(pk=1)

# Add a like activity
post.likes.create(activity_type=Activity.LIKE, user=request.user)

# Or in a similar way using the Activity model to add the like
Activity.objects.create(content_object=post, activity_type=Activity.LIKE, user=request.user)

# Get all Activity instances related to Post

# Count the number of likes

# Get the users who liked the post
post.likes.values_list('user__first_name', flat=True)

A good thing about it is that if have a new model that you wants to interact with Activity, you simply add a GenericRelation:

from django.contrib.contenttypes.fields import GenericRelation

class Picture(models.Model):
    user = models.ForeignKey(User)
    picture_file = models.ImageField(upload_to='uploads/pictures')
    date = models.DateTimeField(auto_now_add=True)
    favorites = GenericRelation(Activity)

And it’s already ready to use:

picture = Picture.objects.get(pk=1)

picture.favorites.create(activity_type=Activity.FAVORITE, user=request.user)

Reverse relations

You may also define an extra parameter in the GenericRelation:

from django.contrib.contenttypes.fields import GenericRelation

class Picture(models.Model):
    user = models.ForeignKey(User)
    picture_file = models.ImageField(upload_to='uploads/pictures')
    date = models.DateTimeField(auto_now_add=True)
    favorites = GenericRelation(Activity, related_query_name='pictures')

Then you can use it to query for example all favorited pictures that was uploaded by a given user:

user = User.objects.get(pk=1)


This is just one of the usages of the ContentTypes and GenericRelations. Even though it seems very nice to use, take care when implementing it! It adds an extra layer of complexity and will eventually make things slower.

Another caveat is that the GenericForeignKey does not accept an on_delete argument to customize this behavior. The default behavior will cascade all the relations.

One way to avoid the default behavior is to not define a GenericRelation. Check the example below:

class Comment(models.Model):
    text = models.CharField(max_length=500, blank=True)
    date = models.DateTimeField(auto_now_add=True)

# Add a new instance of Comment
comment = Comment.objects.create(text='This is a test comment')

# Like the comment
Activity.objects.create(content_object=comment, activity_type=Activity.LIKE, user=request.user)

Now to get the list of likes this Comment received you must use the ContentType class:

from django.contrib.contenttypes.models import ContentType

# Pass the instance we created in the snippet above
ct = ContentType.objects.get_for_model(comment)

# Get the list of likes
Activity.objects.filter(content_type=ct,, activity_type=Activity.LIKE)

This is also an option if you want to interact with a model from Django’s contrib module or any third party model that you don’t have access to add a GenericRelation.

Package of the Week: django-hosts

By Simple is Better Than Complex from Django community aggregator: Community blog posts. Published on Oct 11, 2016.

This is a very handy Django package that I’ve used in a couple of projects. Essentially django-hosts let you serve different parts of your application under different subdomains. For example, let’s say we have a Django application deployed on With this app you can serve an e-commerce under and the help center under And in the end, it’s just a single Django website.

It can also be used to host user spaces, with a wildcard, where your users could get their own subdomain like or But that require a few tweaks in the DNS configuration.

A small caveat of developing with django-hosts is that you will need to do some configurations in your local machine, which can differ if you are using Windows, Linux or Mac.


Install it with pip:

pip install django-hosts

Add the django_hosts to the INSTALLED_APPS:




Add the HostsRequestMiddleware in the beginning of the MIDDLEWARE and HostsResponseMiddleware in the end of the MIDDLEWARE:




Still in the, add the following configuration variables:

ROOT_HOSTCONF = 'mysite.hosts'  # Change `mysite` to the name of your project
DEFAULT_HOST = 'www'  # Name of the default host, we will create it in the next steps

Create a file named right next to the

 |--  # <-- The `ROOT_HOSTCONF` refers to this file


from django.conf import settings
from django_hosts import patterns, host

host_patterns = patterns('',
    host(r'www', settings.ROOT_URLCONF, name='www'),  # <-- The `name` we used to in the `DEFAULT_HOST` setting


Let’s create an app named help to illustrate the usage of the django-hosts:

django-admin startapp help

Then inside of the new app, we create a module:


from django.conf.urls import url, include

from . import views

urlpatterns = [
    url(r'^$', views.home, name='home'),
    url(r'^articles/$', views.articles, name='articles'),
    url(r'^articles/(?P<pk>\d+)/$', views.article_details, name='article_details'),

Now we update the mysite/hosts, which is our ROOT_HOSTCONF:


from django.conf import settings
from django_hosts import patterns, host

host_patterns = patterns('',
    host(r'www', settings.ROOT_URLCONF, name='www'),
    host(r'help', 'help.urls', name='help'),

Testing Locally

In order to test it locally, you will need to setup a local DNS host.

On Linux and Mac, the file is located in the path /etc/hosts. For Windows it should be somewhere in %SystemRoot%\system32\drivers\etc\hosts.

hosts localhost broadcasthost
::1             localhost www.mysite.local help.mysite.local
Django Hosts Help Site
Django Hosts My Site


Now instead of using the {% url 'home' %} notation, you can load the django-hosts template tags:

{% load hosts %}

<a href="{% host_url 'home' host 'www' %}">Homepage</a>
<a href="{% host_url 'articles' host 'help' %}">Help Articles</a>


The django-hosts extends Django’s default reverse function so you can pass an extra argument host:

from django.shortcuts import render
from django_hosts.resolvers import reverse

def homepage(request):
    homepage_url = reverse('homepage', host='www')
    return render(request, 'homepage.html', {'homepage_url': homepage_url})

Further Reading

You can learn more by reading the Official Documentation or browsing the code on GitHub.

Productivity In The Age of Hypergrowth

By Will Larson from Django community aggregator: Community blog posts. Published on Oct 11, 2016.

You don’t hear the term hypergrowth quite as much as you did a couple years ago. Sure, you might hear it any given week, but you might open up Techmeme and not see it, which is a monumental return to a kinder, gentler past. (Or perhaps we’re just unicorning now.)

Fortunately for engineering managers everywhere, the challenges of managing in quickly growing companies still very much exist.

The number of employees by year for Uber, Google, Facebook, and Twitter

When I started at Uber, we were almost 1,000 employees and doubling headcount every six months. An old-timer summarized their experience as:

We’re growing so quickly, that every six months we’re a new company.

A bystander quickly added a corollary:

Which means our process is always six months behind our headcount.

Helping your team be successful when defunct process merges with a constant influx of new engineers and system load has been one of the most rewarding opportunities I’ve had in my career, and this is an attempt to explore the challenges and some strategies I’ve seen for mitigating and overcoming them.

More Engineers, More Problems

All real-world systems have some degree of inherent self-healing properties: an overloaded database will slow down enough that someone fixes it, overwhelmed employees will get slow at finishing work until someone finds a way to help.

Very few real-world systems have efficient and deliberate self-healing properties, and this is where things get exciting as you double engineers and customers year over year over year.

More employees, more customers, more problems.

Productively integrating large numbers of engineers is hard.

Just how challenging depends on how quickly you can ramp engineers up to self-sufficient productivity, but if you’re doubling every six months and it takes six to twelve months to ramp up, then you can quickly find a scenario where untrained engineers increasing outnumber the trained engineers, and each trained engineer is devoting much of their time to training a couple of newer engineers.

Worst-case training scenario.

Imagine a scenario where:

  1. training takes about ten hours per week from each trained engineer,
  2. untrained engineers are 1/3rd as productive as trained engineers,

and you reach the above chart’s (admittedly, pretty worst-case scenario) ratio of two untrained to one trained. Worse, for those three people you’re only getting the productivity of 1.16 trained engineers (2 * .33 for the untrained engineers plus .5 * 1 for the trainer).

You also need to factor in the time spent on hiring.

If you’re trying to double every six months, and about ten percent of candidates undertaking phone screens eventually join, then you need to do ten interviews per existing engineer in that time period, with each interview taking about two hours to prep, perform and debrief.

That’s only four hours per month if you can leverage your entire existing team, but training comes up again here: if it takes you six months to get the average engineer onto your interview loop, each trained engineer is now doing three to four hours of hiring related work a week, and your trained engineers are down to 0.4 efficiency. The overall team is getting 1.06 engineers of work out of every three engineers.

It’s not just training and hiring though:

  1. For every additional order of magnitude of engineers you need to design and maintain a new layer of management.
  2. For every ~10 engineers you need an additional team, which requires more coordination.
  3. Each engineer means more commits and deployments per day, creating load on your development tools.
  4. Most outages are caused by deployments, so more deployments drive more outages, which in turn require incident management, mitigations and postmortems.
  5. More engineers lead to more specialized teams and systems, which require increasingly small oncall rotations for your oncall engineers to have enough system context to debug and resolve production issues, so relative time invested in oncall goes up.

Let’s do a bit more handwavy math to factor these in.

Only your trained engineers can go oncall, they’re oncall one week a month, and are busy about half their time oncall. So that’s a total impact of five hours per week for your trained engineers, who are now down to 0.275 efficiency, and your team overall is now getting less than the output of a single trained engineer for every three engineers you’ve hired.

(This is admittedly an unfair comparison because it’s not accounting for the oncall load on the smaller initial teams, but if you accept the premise that oncall load grows as engineer headcount grows and load grows as the number of rotation grows, then the conclusion should still roughly hold.)

Although it’s rarely quite this extreme, this is where the oft raised concern that hiring is slowing us down comes from: at high enough rates, the marginal added value of hiring gets very slow, especially if your training process is weak.

Sometimes very low means negative!

Systems Survive One Magnitude of Growth

We’ve looked a bit at productivity’s tortured relationship with engineering headcount, so now let’s also think a bit about how the load on your systems is growing.

Understanding the overall impact of increased load comes down to a few important trends:

  1. Most system implemented systems are designed to support one to two orders magnitude of growth from current load. Even systems designed for more growth tend to run into limitations within one to two order of magnitude.
  2. If your traffic doubles every six months, then your load increases an order of magnitude every eighteen months. (And sometimes new features or products cause load to increase much more quickly.)
  3. The cardinality of supported systems increases over time as you add teams, and as “trivial” systems go from unsupported afterthoughts to focal points of entire teams as they reach scaling plateaus (things like Kafka, mail delivery, Redis, etc).

If your company is designing systems to last one order of magnitude and doubling every six months, then you’ll have to reimplement every system twice every three years. This creates a great deal of risk–almost every platform team is working on a critical scaling project–and can also creates a great deal of resource contention to finish these concurrent rewrites.

However, the real productivity killer is not system rewrites, but the migrations which follow those rewrites. Poorly designed migrations expand the consequences of this rewrite loop from the individual teams supporting the systems to the entire surrounding organization.

If each migration takes a week, each team is eight engineers, and you’re doing four migrations a year, then you’re losing about 1% of your company’s total productivity. If each of those migrations takes closer to a month, or if they are only possible for your small cadre of trained engineers whose time is already tightly contended for, then the impact becomes far more pronounced.

There is a lot more that could be said here–companies that mature rapidly often have tight and urgent deadlines around moving to multiple datacenters, to active-active designs, to new international regions and other critical projects–but I think we’ve covered our bases on how increasing system load can become a drag on overall engineering throughput.

The real question is, what do we do about any of this?

Ways to Managing Entropy

My favorite observation from The Phoenix Project is that you only get value from projects when they finish: to make progress, above all else, you must ensure that some of your projects finish.

That might imply that there is an easy solution, but finishing projects is pretty hard when most of your time is consumed by other demands.

Let’s tackle hiring first, as hiring and training are often a team’s biggest time investment.

When your company has decided it is going to grow, you cannot stop it from growing, but on the other hand you absolutely can concentrate that growth, such that your teams alternate between periods of rapid hiring and periods of consolidation and gelling. Most teams work best when scoped to approximately eight engineers, so as each team gets to that point, you can move the hiring spigot to another team (or to a new team). As the post-hiring team gels, eventually the entire group will be trained and able to push projects forward.

Candidates get offers, becoming untrained, and then learn.

You can do something similar on an individual basis, rotating engineers off of interviewing periodically to give them time to recouperate. With high interview loads, you’ll sometimes notice last year’s solid interviewer giving a poor candidate experience or rejecting every incoming candidate. If they’re doing more than three interviews a week, it is a useful act of mercy to give them a month off every three or four months.

I have less evidence of how to tackle the training component of this, but generally you start to see larger companies do major investments into both new-hire bootcamps and recurring education class.

I’m optimistically confident that we’re not entirely cargo-culting this idea from each other, so it probably works, but I hope to get an opportunity to spend more time understanding how effective those programs can be. If you could get training down to four weeks, imagine how quickly you could hire without overwhelming the existing team!

The second most effective time thief I’ve found is adhoc interruptions: getting pinged on HipChat or Slack, taps on the shoulder, alerts from your oncall system, high-volume email lists and so on.

The strategy here is to funnel interrupts into an increasingly small surface area, and then automate that surface area as much as possible. Asking people to file tickets, creating chatbots which automate filing tickets, creating a service cookbook (discussed below), and so on.

With that setup in place, create a rotation for people who are available to answer questions, and train your team not to answer other forms of interrupts. This is remarkably uncomfortable because we want to be helpful humans, but becomes necessary as the number of interrupts climb higher.

One specific tool that I’ve found extremely helpful here is an ownership registry which allows you to look up who owns what, eliminating the frequent “who owns X?” variety of question. You’ll need this sort of thing to automate paging the right oncall rotation, so might as well get two useful tools out of it!

A similar variant of this is adhoc meeting requests. The best tool I’ve found for this is to block out a few large blocks of time each week to focus. This can range from working from home on Thursday, to blocking out Monday and Wednesday afternoons, to blocking out from eight to eleven each morning. Experiment a bit and find something that works well for you.

Finally, the one thing I’ve found at companies with very few interrupts and almost nowhere else: really great, consistently available documentation. It’s probably even harder to bootstrap documentation into a non-documenting company than it is to bootstrap unittests into a non-testing company, but the best solution to frequent interruptions I’ve seen is a culture of documentation, documentation reading, and a documentation search that actually works.

There are a non-zero number of companies which do internal documentation well, but I’m less sure if there are a non-zero number of companies with more than twenty engineers who do this well. If you know any, please let me know so I can pick their brains.

In my opinion, probably the most important opportunity is designing your software to be flexible. I’ve described this as “fail open and layer policy”; the best system rewrite is the one that didn’t happen, and if you can avoid baking in arbitrary policy decisions which will change frequently over time, then you are much more likely to be able to keep using a system for the long term.

If you’re going to have to rewrite your systems every few years due to increased scale, let’s avoid any unnecessary rewrites, ya know?

Along these lines, if you can keep your interfaces generic, then you are able to skip the migration phase of system reimplementation, which is tends to be the longest and trickiest phase, and can iterate much more quickly and maintain fewer concurrent versions. There is absolutely a cost to maintaining this extra layer of indirection, but if you’ve already rewritten a system twice, take the time to abstract the interface as part of the third rewrite and thank yourself later (by the time you’d do the fourth rewrite you’d be dealing with migrating six times the engineers).

Finally, a related antipattern is the gatekeeper pattern. Having humans who perform gatekeeping activities creates very odd social dynamics, and is rarely a great use of a human’s time. When at all possible, build systems with sufficient isolation that you can allow most actions to go forward and when they do occasion to fail, make sure they fail with a limited blast radius.

There are some cases where gatekeepers are necessary for legal or compliance reasons, or because a system is deeply frail, but I think we should generally treat gatekeeping as a significant implementation bug rather than a stability feature to be emulated.

Let’s say you take all of the above ideas and are able to salvage together a bit more time, what should you do with this time?

Project selection is very much like system optimization: if you aren’t measuring, then you’ll fix the wrong things. As such, the first thing to do is to setup analytics to understand where your team is spending their time.

Most approaches to figuring out time allocation ends up requiring a significant time investment from your team in task tracking, and the whole goal is to save their time, so one approach I found to work very well is creating a simple internal website that contains your team’s service cookbook.

A wireframe of a simple service cookbook.

This cookbook lists every task or offering your team performs, and can provide documentation and collect per-task required fields to reduce back and forth between asker and askee due to missing information. Each of these asks can be instrumented to determine the number of incoming requests, and is also an automation vector where you can gradually move each task’s form from submitting a ticket to immediately performing the requested action.

The service cookbook is probably the single highest impact tool I’ve found for reducing entropy as it allows:

  1. Analytics on all incoming requests, which allow you to prioritize automation projects.
  2. Reduces back-and-forth on each request, which reduces concurrent requests and maximizes time spent solving problems.
  3. Documents all your requests, forcing you to have messy ownership discussions early.
  4. Once engineers are used to coming to your cookbook to file requests, you can simply drop in automation there to fully cut yourself out of the loop, without even needing to have them learn a new behavior.

The other nice thing is that you can start with just compiling a JSON or YAML into a simple Bootstrap website, so it’s the kind of thing you can do over a weekend.

Stepping back a bit, the service cookbook concept is a good example of designing interfaces that are reusable across multiple generations of implementation: easy for you, easy for your users, and good for all.

Closing Thoughts

None of the ideas here are instant wins. It’s my sense that managing rapid growth is more along the lines of stacking small wins than identifying silver bullets. I have used all of these techniques, and am using most of them today to some extent or another, so hopefully they will at least give you a few ideas.

Something that is somewhat ignored a bit here is how to handle urgent projects requests when you’re already underwater with your existing work and maintenance. The most valuable skill there is learning to say no in a way that is appropriate to your company’s culture, which probably deserves it’s own article. There are probably some companies where saying no is culturally impossible, and in those places I guess you either learn to say your no’s as yes’s or maybe you find a slightly easier environment to participate in.

What other techniques have worked for you?

How do you remain productive in times of hypergrowth?

List of Useful URL Patterns

By Simple is Better Than Complex from Django community aggregator: Community blog posts. Published on Oct 10, 2016.

The ability to create beautiful and meaningful urls is certainly something I love about the Django Framework. But truth is, it’s very hard to get it right. Honestly I always have to refer to the documentation or to past projects I’ve developed, just to grab the regex I need.

So that’s why I thought about creating this post, to serve as a reference guide for common urls.

Primary Key AutoField

Regex: (?P<pk>\d+)

url(r'^questions/(?P<pk>\d+)/$', views.question_details, name='question_details'),
URL Captures
/questions/0/ {'pk': '0'}
/questions/1/ {'pk': '1'}
/questions/934/ {'pk': '934'}
Won't match

Slug Fields

Regex: (?P<slug>[-\w]+)

url(r'^posts/(?P<slug>[-\w]+)/$',, name='post'),
URL Captures
/posts/0/ {'slug': '0'}
/posts/hello-world/ {'slug': 'hello-world'}
/posts/-hello-world_/ {'slug': '-hello-world_'}
Won't match
/posts/hello world/

Slug with Primary Key

Regex: (?P<slug>[-\w]+)-(?P<pk>\d+)

url(r'^blog/(?P<slug>[-\w]+)-(?P<pk>\d+)/$', views.blog_post, name='blog_post'),
URL Captures
/blog/hello-world-159/ {'slug': 'hello-world', 'pk': '159'}
/blog/a-0/ {'slug': 'a', 'pk': '0'}
Won't match


Regex: (?P<username>[\w.@+-]+)

url(r'^profile/(?P<username>[\w.@+-]+)/$', views.user_profile),
URL Captures
/profile/vitorfs/ {'username': 'vitorfs'}
/profile/vitor.fs/ {'username': 'vitor.fs'}
/profile/@vitorfs/ {'username': '@vitorfs'}
Won't match
/profile/vitor fs/



Regex: (?P<year>[0-9]{4})

url(r'^articles/(?P<year>[0-9]{4})/$', views.year_archive)
URL Captures
/articles/2016/ {'year': '2016'}
/articles/9999/ {'year': '9999'}
Won't match
Year / Month

Regex: (?P<year>[0-9]{4})/(?P<month>[0-9]{2})

url(r'^articles/(?P<year>[0-9]{4})/(?P<month>[0-9]{2})/$', views.month_archive),
URL Captures
/articles/2016/01/ {'year': '2016', 'month': '01'}
/articles/2016/12/ {'year': '2016', 'month': '12'}
Won't match
Year / Month / Day

Regex: (?P<year>[0-9]{4})/(?P<month>[0-9]{2})/(?P<day>[0-9]{2})

url(r'^articles/(?P<year>[0-9]{4})/(?P<month>[0-9]{2})/(?P<day>[0-9]{2})/$', views.article_detail)
URL Captures
/articles/2016/01/01/ {'year': '2016', 'month': '01', day: '01'}
/articles/2016/02/28/ {'year': '2016', 'month': '02', 'day': '28'}
/articles/9999/99/99/ {'year': '9999', 'month': '99', 'day': '99'}
Won't match

Flattened Index

url(r'^questions/(?P<pk>\d+)/$', views.question_details, name='question_details'),
url(r'^posts/(?P<slug>[-\w]+)/$',, name='post'),
url(r'^blog/(?P<slug>[-\w]+)-(?P<pk>\d+)/$', views.blog_post, name='blog_post'),
url(r'^profile/(?P<username>[\w.@+-]+)/$', views.user_profile),
url(r'^articles/(?P<year>[0-9]{4})/$', views.year_archive),
url(r'^articles/(?P<year>[0-9]{4})/(?P<month>[0-9]{2})/$', views.month_archive),
url(r'^articles/(?P<year>[0-9]{4})/(?P<month>[0-9]{2})/(?P<day>[0-9]{2})/$', views.article_detail)

Wanna share some useful URL pattern you use? Leave a comment below and I will update the post!

Package of the Week: isort

By Simple is Better Than Complex from Django community aggregator: Community blog posts. Published on Oct 08, 2016.

isort is a Python utility / library to sort imports alphabetically, and automatically separated into sections. It’s very useful in Django projects, specially in views where we usually deal with a great amount of imports.

Organizing the imports in sections is easy, but to keep them in alphabetical order is very tedious. I don’t know about you, but sometimes I have to run all the letters in my head to make sure 😂.


Install it via pip or grab the code from GitHub:

pip install isort


You can already start using it without any configuration:

# sort multiple files

# show a diff before applying any change
isort --diff

# just check for errors
isort --check-only

You can also apply the changes or check for errors recursively:

# check which files will be sorted
isort --recursive --check-only

# sort the whole project
isort --recursive .


This is how I like to organize my imports in a Django project:

  • Future imports
  • Python Standard Libraries
  • Django core
  • Third party libraries (related or not to Django)
  • First party libraries (that is, our project’s imports)
  • Local imports

You can achieve this by adding a configuration file in the project root. Either add a setup.cfg or a file named .isort.cfg in the project root.


default_section = THIRDPARTY
known_first_party = myproject  # change it for the name of your django project
known_django = django

This is an example of the final result:

from __future__ import unicode_literals

import json
import time

from django.contrib import messages
from django.contrib.auth.decorators import login_required
from django.forms.formsets import formset_factory
from django.forms.models import inlineformset_factory
from django.views.decorators.http import require_POST

from bibtexparser.bparser import BibTexParser
from bibtexparser.customization import convert_to_unicode

from import author_required, main_author_required
from import Review

from .forms import KeywordForm, SynonymForm

If you need to have imports out of order, to avoid circular import for example, you can use the isort:skip comment:

import module  # isort:skip

You can find out more about the isort library visiting its GitHub Page.

Django Tips #17 Using QuerySet Latest & Earliest Methods

By Simple is Better Than Complex from Django community aggregator: Community blog posts. Published on Oct 06, 2016.

Similar to the QuerySet methods first and last, the API also offer the earliest and latest methods. They are convenience methods which can be used to enhance the readability of the code.

The best way to use it is to define get_latest_by in the model’s Meta class:

class Post(models.Model):
    headline = models.CharField(max_length=150)
    author = models.ForeignKey(User, on_delete=models.CASCADE)
    publication_date = models.DateTimeField(blank=True, null=True)
    change_date = models.DateTimeField(blank=True, null=True)

    class Meta:
        get_latest_by = 'publication_date'

Then the usage is very straightforward:

latest_post = Post.objects.latest()
earliest_post = Post.objects.earliest()

If you didn’t specify the get_latest_by property, or if you want to use a different field, you can pass it as a parameter on the fly:

latest_change = Post.objects.latest('change_date')

The earliest and latest methods will raise a DoesNotExist exception if there is no object with the given parameters, that is, the table is empty or it was filtered. It is slightly different from first and last, because it returns None if there is no matching object.

Another important thing to note is that the earliest and latest methods might return instances with null dates. But the thing is, the ordering behavior is not consistent between the different databases. So you might want to remove null dates, like so:


Typically it is used with either DateField, DateTimeField or IntegerField. It will work with other field types. But you should avoid it, because it will be semantically wrong, and as those methods are available just for convinience and to enhance readability, in that case, using for something different than dates will cause more confusion.

Django Tips #16 Simple Database Access Optimizations

By Simple is Better Than Complex from Django community aggregator: Community blog posts. Published on Oct 05, 2016.

So, I just wanted to share a few straightforward tips about database access optimization. For the most those tips are not something you should go to your code base and start refactoring. Actually for existing code it’s a better idea to profile first (with Django Debug Toolbar for example). But those tips are very easy to start applying, so keep them in mind when writing new code!

Accessing Foreign Key Values

If you only need the ID of the Foreign Key:


If you have a foreign key named author, Django will automatically store the primary key in the property author_id, while in the author property will be stored a lazy database reference. So if you access the id via the author instance, like this, it will cause an additional query.

Bulk Insert on Many to Many Fields

user.groups.add(administrators, managers)

Counting QuerySets

If you only need the count:

users = User.objects.all()

# Or in template...
{{ users.count }}
users = User.objects.all()

# Or in template...
{{ users|length }}

Empty QuerySets

If you only need to know if the queryset is empty:

groups = Group.objects.all()
if groups.exists():
    # Do something...
groups = Group.objects.all()
if groups:
    # Do something...

Reduce Query Counts

review = Review.objects.select_related('author').first()  # Select the Review and the Author in a single query
name =
review = Review.objects.first()  # Select the Review
name =  # Additional query to select the Author

Select Only What You Need

Let’s say the Invoice model has 50 fields and you want to create a view to display just a summary, with the number, date and value:

# If you don't need the model instance, go for:
invoices = Invoice.objects.values('number', 'date', 'value')  # Returns a dict

# If you still need to access some instance methods, go for:
invoices = Invoice.objects.only('number', 'date', 'value')  # Returns a queryset

# invoices.html
  {% for invoice in invoices %}
      <td>{{ invoice.number }}</td>
      <td>{{ }}</td>
      <td>{{ invoice.value }}</td>
  {% endfor %}
invoices = Invoice.objects.all()

# invoices.html
  {% for invoice in invoices %}
      <td>{{ invoice.number }}</td>
      <td>{{ }}</td>
      <td>{{ invoice.value }}</td>
  {% endfor %}

Bulk Updates

from django.db.models import F

Product.objects.update(price=F('price') * 1.2)
products = Product.objects.all()
for product in products:
    product.price *= 1.2

How to Use Django's Flatpages App

By Simple is Better Than Complex from Django community aggregator: Community blog posts. Published on Oct 04, 2016.

Django ships with a Flatpages application that enables the user to create flat HTML pages and store it in the database. It can be very handy for pages like About, Privacy Policy, Cookies Policy and so on.

Basically it works like this: You define a master page for the content to be displayed, the user creates a new flatpage in the Django Admin interface, picks a URL and add the content. The user can also select if the page requires login or not.


First add the sites and flatpages contrib apps to your INSTALLED_APPS:



If you wasn’t using the sites app, you may also need to add a SITE_ID to the file:


Now update your with the flatpages urls:

from django.conf.urls import url, include
from django.contrib import admin

urlpatterns = [
    url(r'^pages/', include('django.contrib.flatpages.urls')),

Migrate the database:

$ python migrate

Operations to perform:
  Apply all migrations: admin, auth, contenttypes, core, flatpages, sessions, sites
Running migrations:
  Rendering model states... DONE
  Applying sites.0001_initial... OK
  Applying flatpages.0001_initial... OK
  Applying sites.0002_alter_domain_unique... OK

Add a default template for the flatpages. The default location is flatpages/default.html:

<!DOCTYPE html>
<html lang="en">
  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>{{ flatpage.title }}</title>
  <link rel="stylesheet" href="">
  <nav class="navbar navbar-default">
    <div class="container">
      <div class="navbar-header">
        <a class="navbar-brand" href="#">Flatpages Example</a>
  <div class="container">
    {{ flatpage.content }}

The important part here is this two variables: flatpage.title and flatpage.content.


Go to the Django Admin and add a new page. It should be very intuitive.

New Flatpage

Save it and go to the flatpage URL, that is /pages/privacy/:

Privacy Policy Flatpage

Rendering a List of Flatpages

You can list all the available flatpages like this in a template:

{% load flatpages %}
{% get_flatpages as flatpages %}
  {% for page in flatpages %}
    <li><a href="{{ page.url }}">{{ page.title }}</a></li>
  {% endfor %}

You can try this Bootstrap template snippet:

{% load flatpages %}<!DOCTYPE html>
<html lang="en">
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>{{ flatpage.title }}</title>
    <link rel="stylesheet" href="">
    <nav class="navbar navbar-default">
      <div class="container">
        <div class="navbar-header">
          <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#menu" aria-expanded="false">
            <span class="sr-only">Toggle navigation</span>
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
          <a class="navbar-brand" href="#">Flatpages Example</a>
        <div class="collapse navbar-collapse" id="menu">
          {% get_flatpages as flatpages %}
          <ul class="nav navbar-nav">
            {% for page in flatpages %}
              <li><a href="/pages{{ page.url }}">{{ page.title }}</a></li>
            {% endfor %}
    <div class="container">
      {{ flatpage.content }}

It will look something like that:

Privacy Policy Flatpage with Menu


If you want to find out more about the Flatpages app, refer to the Django’s Official documentation about it.

Estimating the Effort of Development of a Django Application

By Simple is Better Than Complex from Django community aggregator: Community blog posts. Published on Oct 03, 2016.

This is a very common problem that every developer will eventually face at some point. Your boss or a client comes with a set of requirements and asks you to estimate how long it will take to implement them.

How to precisely (as possible) estimate the effort of development of a Web application? That’s a really tough question. People in the academia have being conducting research on that topic for at least the past 40 years, and yet we fail to get it right.

I wanted to write a little bit on that subject to try to highlight the challenges of making an accurate estimation and perhaps share some of my experience about it.


The Chaos Report 2015

Every year the Standish Group conducts a survey to investigate the cause of failure of software projects. In 2015, out of the 8,380 investigated projects, 16.2% (success) was delivered on-time and on-budget, 52.7% (challenged) faced challenges and was delivered over-budget and over the time estimate. The other 31.1% (impaired) was cancelled at some point during the development cycle.

Check this numbers in relation to the time overruns of the original estimates, from the Chaos Report 2015:

Time Overruns
For the same combined challenged and impaired projects, over one-third also experienced time overruns of 200 to 300%. The average overrun is 222% of the original time estimate. For large companies, the average is 230%; for medium companies, the average is 202%; and for small companies, the average is 239%.

Time Overruns % of Responses
Under 20% 13.9%
21 - 50% 18.3%
51 - 100% 20.0%
101 - 200% 35.5%
201 - 400% 11.2%
Over 400% 1.1%

Scary, right?

Now, why does that happen? Mainly because every estimation involves uncertainty. An estimation is different from the reality because of the uncertainties. Make no mistake, every estimation will fail.

The software domain is plagued by uncertainties: incomplete requirements, volatile requirements, incomplete understanding of the scope, human aspects, external factors, etc.

We can’t help ourselves, we are not very good on forecasting things we don’t know.

How Uncertainty Behaves

There is an important aspect on how the uncertainty behaves. And this can help us a lot when estimating the effort. Let’s get back to the Chaos Report’s numbers. Out of the 8,380 applications analyzed by the Standish Group, 16.2% of them was delivered on time (possibly some of them took less effort than expected), while 83.8% required more effort than expected (when some of them wasn’t even concluded).

To illustrate that phenomena, I will quote the article How Uncertainty Works published by Kevin Thompson:

There is more room, in a mathematical sense, for work to grow beyond expectation than to shrink below expectation.

Mathematically speaking, the effort the accomplish one task can’t go below zero hours. But in the other hand, it can grow…

To Infinity and Beyond

Sorry for the silly joke. Anyway, check the example Kevin Thompson gave in his article:

Assume we estimate this work at three days. The actual time may be more than three days over this estimate, but cannot be more than three days under the estimate!
In mathematical terms, we cannot estimate a task as requiring “X plus or minus Y days,” because the estimate becomes meaningless if Y is greater than X. What we can do is replace the concept of an increment of uncertainty with that of an uncertainty factor, F. This means that we think that X is the most likely duration, but the range of values is between X divided by F, and X times F. For example, suppose we estimate “Paint Bedroom” at 3 days, with an uncertainty factor of 2.

The most likely case is 3 days.
The best case is 1.5 days, which is 1.5 days under the estimate.
The worst case is 6 days, which is 3 days over the estimate.

I think this simple example illustrates well the concept.

Besides that, still, why are we so bad at making estimations? When it comes to estimating the effort of software development, we are over-optimistic. This is just one of the cognitive bias that affect our work.

The Cognitive Bias in Software Engineering

Actually, I know very little about that subject. But the little I read about it made lots of sense for me. Some of my collegues at the university study this subject in great detail, and listening to one of their presentations last year was the first time I came across that subject. And I thought it was fascinating.

I grabbed the information below from a great presentation by Julia Clavien on Cognitive Bias in Software Development.

So, what’s a Cognitive Bias?

Cognitive Bias
Systematic errors in judgment and decision making common to all human beings.
– Daniel Kahneman

The Planning Fallacy

The tendency of individuals to display an optimism bias and underestimate the time and resource required to complete a project.

The Plan vs. Reality

The Known Knowns, Known Unknowns and Unknown Unknowns

The plan is usually very different from the reality because of the difficulties to estimate what we don’t know, and even worse, to estimate things we don’t know we don’t know.

There are known knowns. These are things we know that we know. There are known unknowns. That is to say, there are things that we know we don’t know. But there are also unknown unknowns. There are things we don’t know we don’t know.
– Donald Rumsfeld

On the Wikipedia I found a very interesting citation of Jørgensen and Grimstad’s work, The Impact of Irrelevant and Misleading Information on Software Development Effort Estimates: A Randomized Controlled Field Experiment:

  • It’s easy to estimate what you know.
  • It’s hard to estimate what you know you don’t know. (known unknowns)
  • It’s very hard to estimate things that you don’t know you don’t know. (unknown unknowns)

Minimizing the Problems

Let’s summarize what we have so far:

  • Estimation is different from reality because of uncertainty;
  • There is more room for work to grow beyond expectation than to shrink below expectation;
  • We have the tendency to be over-optimistic when it comes to estimate;
  • It’s hard to estimate what we don’t know.

It is all about uncertainty. We certainly can’t eliminate it, but sure thing we can reduce it.

Decompose the Problem

Decompose the tasks into smaller ones. Try to look at them in great detail. Think about the underlying implications of the implementation of a given requirement.

Let’s say we have a task to implement a Password reset functionality.

We can decompose this task into several smaller tasks, and then estimate the effort of the small tasks. Naturally, putting them all together would give us the overall estimation of the initial task.

  • Create the HTML templates
  • Generate a one-time-use password reset link
  • Implement a view to process the password change request
  • Create the password reset email template
  • Add a re-captcha to prevent abuse

The challenge here is to keep the smaller tasks in a level of granularity that actually makes sense, there is no point in reducing the size below a level where the relative uncertainty does not improve.

As stated by Kevin Thompson, the important thing is to pick a granularity that (1) enables a tolerable level of uncertainty, and (2) produces a set of things to estimate that is small enough to be practical.

The Changing Requirements

It is what it is. That’s what requirements do. They change. All the time. If they don’t change during the development, they will during the usage.

There is only one circumstance where the requirements won’t change: It is when the software is no longer being used.

Now, repeat after me:

The requirements will change and I will not cry about that.

What we can do about it? Deliver fast. You certainly have at least heard about Scrum, Minimum Viable Product (MVP) or Agile Development before.

Break the whole project into small valuable deliverables. Get the client involved. Learn faster. The development of a software product should be dynamic.

Besides all the benefits of a MVP, it will enable a more accurate estimation, because you will be dealing with a small chunk of the whole problem, and you will also be learning faster about the requirements and the domain.

Minimal Viable Product

I know I’m deviating a little bit from the original subject of this article, but the point is: it is easier to give a better estimate when the scope is small.

Your client will always need an overall estimation. After all, he needs to plan the schedule and the budget. Besides, there are many factors to take into account when planning a release: the time-to-market, the return on investment, the end-user needs, etc.

You can always give an overall estimation. But agree with the client that it’s an imperfect and error-prone estimation. Decompose the requirements into several small and valuable deliverables. Smaller scope means better estimate. After each interaction, each delivery, review the requirements with the client and update the overall estimation.

Without mentioning it will improve the client’s understanding of the software needs. Also, he will eventually get to know some of the technical challanges.

Otherwise, if you embrace all the requirements, assuming you understood everything right and your estimation is 100% accurate – that will be you, after the estimation fails:

Lol, I can’t stop laughing

Anyway, this dude is you – the developer who under estimated the project.

The fridge is the project (you thought it was a toaster).

And the bicycle, well, the bicycle is the despair. The despair to get rid of that project and finally deliver it.

Despite all the effort you are putting to make things work, that’s how the client will perceive you and your work:


Haha! Got the monkey idea from Julia Clavien’s presentation on Cognitive Bias in Software Development.

It’s Not Only About Code

Sometimes when we are doing estimates, it is common that we forget to take into account the time to write documentation, unit tests, functional tests, and so on.

Bear in mind it is not only about code. And also important to note, the development estimate is different from a project estimate.

Cope With the Uncertainties

After reducing the level of uncertainty to a minimum practical level, it’s time to cope with the remaining uncertainty.

If it is a project with a real fixed scope, the client deeply understands what he wants to be done, ideally you already have some proof of concepts and prototypes – what you want to do is to add a buffer time to the schedule.

How much is enough depends. Depends on how big, what’s the complexity of the project, your experience, how familiar you are with the domain.

Ask developers about the time they will take to implement the features (T). Multiply it by 2.5. If it is a very complex algorithmic type project multiply it by 3.0.

In my experience that’s a pretty good approximation for total project time. Usually coding time is only 40% of total project time.

Nitin Bhide
Thursday, March 25, 2004

Now, if we compare with the data from Chaos Report 2015, where the average overrun is 222% of the original time estimate. The numbers suggested by Nitin Bhide are about right.

How’s a Django Application Different?

Well, depending on the effort estimation technique you are using, a Django application isn’t that different at all. For example, some agile estimating techniques use the concept of velocity. First you estimate the size of the features to be implemented. Then initially you try to guess the team’s velocity. After a first iteration of development, you are able to calculate the team’s velocity. Then the estimation of the remaining features/requirements are adjusted, based on the actual team’s velocity.

So this velocity concept will eventually be impacted by the team’s experience, the framework that’s being used (Django ir our case) and all other technologies being used in the development.

Nevertheless, I like to take a few factors into account when estimating the effort of the Django applications I develop. It is important to have a look in all the Python/Django Ecosystem to better understand what you can reuse, what can be integrated and what will need to be developed from scratch.

Using Built-in Django Apps

Take a look at the requirements list and first try to think of what can be used out-of-the-box? That is, you can simply use what the Django framework offers as it is.

Some examples are:

  • Authentication system
    • Login
    • Logout
    • Password reset
    • Password strength checking
    • Permissions
    • User groups
  • Flat pages app
    • Store flat HTML pages in the database (and manage from Admin interface)
  • Redirects app
    • Store simple redirects in the database (and manage from Admin interface)
  • Syndication feed framework
    • RSS
    • Atom
  • Sitemap framework
    • Automatically generate sitemaps
Using Django Admin

Now a special case is the Django Admin. If the Django Admin interface can be used without many customizations, then that means you will be saving lots of hours of development.

Keep in mind that the Django Admin is not meant to be used by the end-users of a website. So don’t try to push it as a “member’s area”. It is meant for the administrator of the website to manage its content.

Django Apps Supported by The Django Foundation

There are also the projects hosted by Django Foundation:

Using Third-Party Packages

After that, try to find Third-Party Packages that solve your problems. The Django Packages is a great resource. Also don’t forget to look at PyPI and of course Google.

Some apps that can speed the development process:

And many others.

The key concept here is to explore. Even if you are not gonna use, it is good to know what is out there. It is also important to note that you shouldn’t deliberately just add new dependencies to the project. It is always a trade-off. You must check if the third party app offers a suitable license, if it is being maintained, if the community is active, and so on.

Django’s Lower Level API

It is not only about installing packages and using stuff out-of-the-box. When estimating the effort in a Django project, try also to think how the framework can assist in the development.

For example, here is a few resources that Django offers that saves lots of implementation time:

  • Pagination
  • Middlewares
  • Cache system
  • Messages framework
  • Forms
  • Django ORM
  • Email interface
  • Internationalization
  • Localization
  • Geographic framework

The views are a big part of a Django application. If you are able to use the Generic Class-Based Views, it will certainly reduce the implementation time:

  • DetailView
  • ListView
  • FormView
  • CreateView
  • UpdateView
  • DeleteView
  • Date Hierarchy Views (ArchiveIndexView, YearArchiveView, MonthArchiveView, etc)
Refer to Past Django Projects

Actually when I’m developing a new Django application, I use my old projects all the time. I reuse lots of work I’ve done in the past. Actually I have a repository of easy-to-reuse snippets, like pagination controls HTMLs, some reusable template filters, etc. From time to time I share them in the blog.

More than reusing existing resource, compare the new requirements to things you have built in the past. This will give you insights of how long each tasks takes to be implemented. Measure everything that’s possible. After the project is delivered, review your initial estimates. Update it with the actual time.

Even if you are working on a personal project, estimate first the effort. It is a good exercise, and it will help you in future estimates.


I wanted to share some of my experience with effort estimation and also some valuable resources. Mainly because for me, the worst thing is having to work under time-pressure because of bad estimations.

So, the takeaway message of this post:

  • Every estimation will eventually fail;
  • Probably it will require more work than expected;
  • Don’t be so optimistic;
  • While it is easier to estimate things you know, it is hard to estimate the things you don’t know;
  • Things that you don’t know you don’t know will happen, and it will affect your schedule;
  • Always add a time buffer to your estimates;
  • Break down big requirements so you can minimize the level of uncertainty;
  • Try to work with smaller scopes;
  • It’s not only about code, consider also the time to create documentation and testing;
  • The team experience and knowledge about the development tools and resources are very important!

I hope you have enjoyed this post, and found it somehow helpful. If you want to discuss further, please leave a comment below.

I'm Gonna Regret This

By chrism from . Published on Jun 14, 2016.

A plea for liberals to fight for individual rights.

Is Open Source Consulting Dead?

By chrism from . Published on Sep 10, 2013.

Has Elvis left the building? Will we be able to sustain ourselves as open source consultants?

Consulting and Patent Indemification

By chrism from . Published on Aug 09, 2013.

Article about consulting and patent indemnification

Python Advent Calendar 2012 Topic

By chrism from . Published on Dec 24, 2012.

An entry for the 2012 Japanese advent calendar at

Why I Like ZODB

By chrism from . Published on May 15, 2012.

Why I like ZODB better than other persistence systems for writing real-world web applications.

A str. __iter__ Gotcha in Cross-Compatible Py2/Py3 Code

By chrism from . Published on Mar 03, 2012.

A bug caused by a minor incompatibility can remain latent for long periods of time in a cross-compatible Python 2 / Python 3 codebase.

In Praise of Complaining

By chrism from . Published on Jan 01, 2012.

In praise of complaining, even when the complaints are absurd.

2012 Python Meme

By chrism from . Published on Dec 24, 2011.

My "Python meme" replies.

In Defense of Zope Libraries

By chrism from . Published on Dec 19, 2011.

A much too long defense of Pyramid's use of Zope libraries.

Plone Conference 2011 Pyramid Sprint

By chrism from . Published on Nov 10, 2011.

An update about the happenings at the recent 2011 Plone Conference Pyramid sprint.

Jobs-Ification of Software Development

By chrism from . Published on Oct 17, 2011.

Try not to Jobs-ify the task of software development.

WebOb Now on Python 3

By chrism from . Published on Oct 15, 2011.

Report about porting to Python 3.

Open Source Project Maintainer Sarcastic Response Cheat Sheet

By chrism from . Published on Jun 12, 2011.

Need a sarcastic response to a support interaction as an open source project maintainer? Look no further!

Pylons Miniconference #0 Wrapup

By chrism from . Published on May 04, 2011.

Last week, I visited the lovely Bay Area to attend the 0th Pylons Miniconference in San Francisco.

Pylons Project Meetup / Minicon

By chrism from . Published on Apr 14, 2011.

In the SF Bay Area on the 28th, 29th, and 30th of this month (April), 3 separate Pylons Project events.