The presentation “PostgreSQL and JSON  with Pythonis part of Sunscrapers’ weekly talks. The main talking points include: PostgreSQL types,  HStore vs JSON vs JSONB,  SQLAlchemy, Django.

1. Why?

  • Schema-less data
  • Schema-unknown data
  • Document storage
  • User-defined schema

2. PostgreSQL types:

  • XML (since 8.2)
  • HStore (since 8.2)
  • JSON (since 9.2)
  • JSONB (since 9.4)

3. HStore vs JSON vs JSONB

  • Only simple key/value pairs
  • No nesting
  • Strings only
  • Indexing
  • Many functions and operators

4. SQLAlchemy

  • Simple validation on input
  • Stored as text (like XML)
  • Preserves key order and duplicates
  • Indexes (only expression index)


  • Full JSON implementation
  • Binary storage
  • No key order or duplicate preservation
  • Fast access operations
  • Indexing
  • No date type!

6. SQLAlchemy

  • PostgreSQL dialect
  • Close to database
  • HStore, JSON, JSONB data types and much more

7. Django ORM

  • HStoreField since 1.8
  • JSONField since 1.9 (uses JSONB)
  • Support for migrations
  • Third party libs like: django-hstore

Slides available here:

Przemek Lewandowski
Przemek Lewandowski
Co-founder & CTO

Przemek is the co-founder and CTO of Sunscrapers. After graduating from the Warsaw University of Technology, he worked as a software consultant. At Sunscrapers, Przemek acts as the technical leader who supervises high-quality service delivery, helps to solve problems, and mentors other team members. Przemek is a passionate community activist, he leads open-source projects, volunteers in projects like Django Girls London, and organizes/speaks at tech events like PyWaw.


The Ultimate Tutorial for Django REST Framework: Selective Fields and Related Objects (Part 7)

This is the last part of my tutorial for the Django REST framework. Be sure to catch up with the work we’ve completed in other parts of the series: [...]

Project management Web development

Project management methodologies: Agile vs. Waterfall vs. Scrum vs. Kanban

One of the most important decisions you will make when building a digital product is choosing the project management methodology. Today, organizations benefit from a wide range of project [...]

Join our newsletter.

Scroll to bottom

Hi there, we use cookies to provide you with an amazing experience on our site. If you continue without changing the settings, we'll assume that you're happy to receive all cookies on the Sunscrapers website. You can change your cookie settings at any time.

Learn more