<h2>How does PostgreSQL fit within the Python stack?</h2>
<p>PostgreSQL is the default database choice for many Python developers,
including the Django team when testing the
<ahref="/object-relational-mappers-orms.html">Django ORM</a>. PostgreSQL is often
viewed as more feature robust and stable when compared to MySQL, SQLServer
and Oracle. All of those databases are reasonable choices. However, because
PostgreSQL tends to be used by Python developers the drivers and example
code for using the database tend to be better documented and contain fewer
bugs for typical usage scenarios. If you try to use an Oracle database with
Django, you'll see there is far less example code for that setup compared
to PostgreSQL backend setups.</p>
<divclass="well see-also">PostgreSQL is an implementation of the <ahref="/databases.html">relational database</a> concept. Learn more in the <ahref="/data.html">data</a> chapter or view the <ahref="/table-of-contents.html">table of contents</a> for all topics.</div>
<h2>Why is PostgreSQL a good database choice?</h2>
<p>PostgreSQL's open source license allows developers to operate one or
more databases without licensing cost in their applications. The open
source license operating model is much less expensive compared to Oracle
or other proprietary databases, especially as replication and sharding
become necessary at large scale. In addition, because so many people
ranging from independent developers to multinational organizations use
PostgreSQL, it's often easier to find developers with PostgreSQL experience
than other relational databases.</p>
<p>The PostgreSQL core team also releases frequent updates that greatly enhance
<ahref="https://github.com/aio-libs/aiopg">aiopg</a> library which
wraps psycopg2's asychronouos features together.</p>
<p>To abstract the connection between tables and objects, many Python
developers use an
<ahref="/object-relational-mappers-orms.html">object-relational mapper (ORM)</a> with
to turn relational data from PostgreSQL into objects that can be used in
their Python application. For example, while PostgreSQL provides a
relational database and psycopg is the common database connector, there
are many ORMs that can be used with varying web frameworks, as shown in the
table below.</p>
<p><imgsrc="/img/postgresql-orm-examples.png" width="100%" alt="Examples of how varying Python ORMs can work with PostgreSQL and the psycopg2 connector." class="technical-diagram" /></p>
<p>Learn more about
<ahref="/object-relational-mappers-orms.html">Python ORMs on that dedicated topic page</a>.</p>
<h2>PostgreSQL data safety</h2>
<p>If you're on Linux it's easy to get PostgreSQL installed using a package manager.
However, once the database is installed and running your responsibility is just beginning.
Before you go live with a production application, make sure to:</p>
to identify the biggest risks to your database. Small applications and
bootstrapped companies often cannot afford a full audit in the beginning but
as an application grows over time it becomes a bigger target.</p>
<p>The data stored in your database is the lifeblood of your application. If you have
ever
<ahref="https://www.twilio.com/blog/2014/02/introducing-developer-evangelist-matt-makai.html">accidentally dropped a production database</a>
or been the victim of malicious activity such as SQL injection attacks, you'll
know it's far easier to recover when a bit of work has been performed
beforehand on backups, replication and security measures.</p>
<h2>Python-specific PostgreSQL resources</h2>
<p>Many quickstarts and tutorials exist specifically for Django, Flask and
other web application frameworks. The ones below are some of the best
walkthroughs I've read.</p>
<ul>
<li>
<p><ahref="/blog/postgresql-python-3-psycopg2-ubuntu-1604.html">Setting up PostgreSQL with Python 3 and psycopg on Ubuntu 16.04</a>
provides instructions for getting a fresh Ubuntu install working with
PostgreSQL and Python 3.</p>
</li>
<li>
<p>This post on
<ahref="http://killtheyak.com/use-postgresql-with-django-flask/">using PostgreSQL with Django or Flask</a>
is a great quickstart guide for either framework.</p>
</li>
<li>
<p>This article explains how and why PostgreSQL can handle
<ahref="http://blog.lostpropertyhq.com/postgres-full-text-search-is-good-enough/">full text searching</a>
for many use cases. If you're going down this route, read
<ahref="http://blog.garage-coding.com/2015/12/18/postgres-fulltext-search.html">this blog post that explains how one developer implemented PostgreSQL full text search with SQLAlchemy</a>.</p>
is a tool for bulk loading data into a PostgreSQL database based on Django models.
<ahref="http://www.californiacivicdata.org/2015/07/17/hello-django-postgres-copy/">Say hello to our new open-source software for loading bulk data into PostgreSQL</a>
is an introduction to using the tool in your own projects.</p>
</li>
<li>
<p><ahref="http://nemesisdesign.net/blog/coding/how-to-speed-up-tests-django-postgresql/">How to speed up tests in Django and PostgreSQL</a>
explains some hacks for making your schema migration-backed run quicker.</p>
</li>
<li>
<p><ahref="http://www.machinalis.com/blog/full-text-search-on-django-with-database-back-ends/">Full Text Search in Django using Database Back-Ends</a>
provides code for both PostgreSQL and <ahref="/mysql.html">MySQL</a> for adding simple
full text search into your application.</p>
</li>
<li>
<p><ahref="https://pypi.python.org/pypi/records/">Records</a> is a wrapper around the psycopg2
driver that allows easy access to direct SQL access. It's worth a look if
you prefer writing SQL over using an
<ahref="/object-relational-mappers-orms.html">ORM</a> like SQLAlchemy.</p>
</li>
</ul>
<h2>General PostgreSQL resources</h2>
<p>PostgreSQL tutorials not specific to Python are also really helpful
for properly handling your data.</p>
<ul>
<li>
<p><ahref="https://russ.garrett.co.uk/talks/postgres-gds/">PostgreSQL: The Nice Bits</a> is a
good overview slideshow of why PostgreSQL is a great relational database.</p>
</li>
<li>
<p><ahref="http://postgresweekly.com/">PostgreSQL Weekly</a> is a weekly newsletter of
PostgreSQL content from around the web.</p>
</li>
<li>
<p><ahref="http://rachbelaid.com/introduction-to-postgres-physical-storage/">An introduction to PostgreSQL physical storage</a>
provides a solid walkthrough of where PostgreSQL files are located on
disk, how the files store your data and what mappings are important for
the underlying database structure. This post is an easy read and well worth
your time.</p>
</li>
<li>
<p>Braintree wrote about their experiences <ahref="https://www.braintreepayments.com/braintrust/scaling-postgresql-at-braintree-four-years-of-evolution">scaling PostgreSQL</a>.
The post is an inside look at the evolution of Braintree's usage of the database.</p>
</li>
<li>
<p>This post estimates the <ahref="http://hans.io/blog/2014/02/19/postgresql_connection/index.html">costs of a PostgreSQL connection</a>.</p>
</li>
<li>
<p>There is no such thing as total security but this IBM article covers
<ahref="http://www.ibm.com/developerworks/library/os-postgresecurity/">hardening a PostgreSQL database</a>. </p>
</li>
<li>
<p><ahref="http://instagram-engineering.tumblr.com/post/40781627982/handling-growth-with-postgres-5-tips-from-instagram">Handling growth with Postgres</a>
provides 5 specific tips from Instagram's engineering team on how to scale
the design of your PostgreSQL database.</p>
</li>
<li>
<p><ahref="http://rob.conery.io/2015/02/09/inserting-using-new-record-postgres/">Inserting And Using A New Record In Postgres</a>
shows some SQL equivalents to what many developers just do in their ORM
of choice.</p>
</li>
<li>
<p><ahref="http://patshaughnessy.net/2014/10/13/following-a-select-statement-through-postgres-internals">Following a Select Statement Through Postgres Internals</a>
provides a fascinating look into the internal workings of PostgreSQL
during a query.</p>
</li>
<li>
<p>If you're just getting started with PostgreSQL here are
<ahref="https://eye.raze.mx/10-beginner-postgresql-tasks-you-should-know/">10 beginner tasks you should know how to execute</a>.</p>
</li>
<li>
<p>The title's a bit presumptuous but here's a useful list of
<ahref="http://engineering.tilt.com/7-postgresql-data-migration-hacks/">7 PostgreSQL data migration hacks you should be using, but aren't</a>.</p>
is a list of code libraries, tutorials and newsletters focused
specifically on PostgreSQL.</p>
</li>
<li>
<p>While you can use a graphical interface for working with PostgreSQL, it's
best to spend some time getting
<ahref="http://phili.pe/posts/postgresql-on-the-command-line/">comfortable with the command-line interface</a>.</p>
</li>
<li>
<p>Backing up databases is important because data loss can and does happen.
This article explains
<ahref="http://www.n2ws.com/blog/how-to-backup-your-aws-cloud-based-postgresql-database.html">how to back up a PostgreSQL database hosted on an Amazon Web Services EC2 instance</a>
if managing your own database on a cloud server is your preferred setup.</p>
</li>
<li>
<p><ahref="https://tech.zalando.com/blog/hack-to-terminate-tcp-conn-postgres/">How to fix undead PostgreSQL queries</a>
shows a bit of a hack for what to do when you can't kill certain
PostgreSQL queries.</p>
</li>
<li>
<p><ahref="http://sdf.org/~riley/blog/2016/01/04/is-bi-directional-replication-bdr-in-postgres-transactional/">Is bi-directional replication (BDR) in PostgreSQL transactional?</a>
explores a relatively obscure topic with the final result that BDR is
similar to data stores with eventual consistency rather than consistency
as a requirement.</p>
</li>
<li>
<p><ahref="https://github.com/spotify/postgresql-metrics">PostgreSQL-metrics</a> is a
tool built by Spotify's engineers that extracts and outputs metrics from
an existing PostgreSQL database. There's also a way to extend the tools
to pull custom metrics as well.</p>
</li>
<li>
<p><ahref="https://blog.andyet.com/2016/02/04/postgres-9.5-document-store-hybrid/">Creating a Document-Store Hybrid in Postgres 9.5</a>
explains how to store and query JSON data, similar to how
<ahref="/no-sql-datastore.html">NoSQL data stores</a> operate.</p>
</li>
<li>
<p><ahref="http://eftimov.net/postgresql-indexes-first-principles">PostgreSQL Indexes: First Principles</a>
is a detailed look at what indexes are, what they are good for and
how to use them in PostgreSQL.</p>
</li>
<li>
<p>This <ahref="http://thebuild.com/presentations/pgha-fosdem-2016.pdf">slideshow on high availability for web applications</a>
has a good overview of various database setups common in production
web applications.</p>
</li>
</ul>
<h3>PostgreSQL monitoring and performance</h3>
<p>Monitoring one or more PostgreSQL instances and trying to performance tune
them is a rare skillset. Here are some resources to get you started if you
have to handle these issues in your applications.</p>
<ul>
<li>
<p>This
<ahref="http://russ.garrett.co.uk/2015/10/02/postgres-monitoring-cheatsheet/">guide to PostgreSQL monitoring</a>
is handy for knowing what to measure and how to do it.</p>
<ahref="http://www.deploypython.com/"><imgsrc="/img/sponsored/fsp-deployment-guide.png" alt="The Full Stack Python Guide to Deployments" width="100%"></a>
<pstyle="font-size: .8em; margin-top: 10px;">Searching for a complete, step-by-step deployment walkthrough? Learn more about <ahref="http://www.deploypython.com/">The Full Stack Python Guide to Deployments book</a>.
<divstyle="padding: 0 0 20px 0; margin: 0 0 20px 0; background-color: #22B24C;">
<divclass="container">
<pclass="banner"><ahref="https://www.gumroad.com/l/python-deployments" style="color: #fff">Learning web development? Check out The Full Stack Python Guide to Deployments book</a>!</p>