PGSQL Phriday #011 — partitioning vs sharding in PostgreSQL

Tomasz Gintowt
2 min readAug 1, 2023

--

It is my great honor and pleasure to be the host of this month’s PGSQL Phriday.

Ladies and gentlemen, the topic I’ve chosen for this month is

partitioning vs sharding in PostgreSQL

My motivation:

I’ve spent last few months on digging into partitioning and I believe it’s natural step when our database is growing. At some point you have to realize that single table it’s not enough for you. What we can do ? Lets use partition or sharding. Many developers still don’t know the difference, use these word interchangeably. I’d like to make a PostgreSQL word a little bit better and share knowledge about partitions and shards.

Let’s put some light on difference between these two concepts. Please focus on:

  • difference between architecture
  • what business problem are you trying to solve
  • performance tips
  • how tables should be designed ( relations, primary keys, FK etc)
  • best practices

If you need more details please check table partitioning, CitusDB, Yugabyte, pg_partman extension, shardman .

I would love to hear your story!

The Rules

The guidelines for participating are as follows:

  1. Publish your blog post about “partitioning vs sharding” by Friday, August 4th, 2023
  2. Include “PGSQL Phriday #011” in the title or first paragraph of your blog post
  3. Link back to this blog post
  4. Announce your blog post on one or more of these platforms:
  • Twitter/Linkedin/FB using the #PGSQLPhriday hashtag
  • PostgreSQL Slack in the #PGSQLPhriday channel

5. Engage with other blog posts about this topic

PGSQL Phriday what is it ?

PGSQL Phriday is a monthly blogging event that’s hosted by members of the PostgreSQL community, modeled after the long-running T-SQL Tuesday event that’s been run by the SQL Server community.

The idea was also inspired by the State of PostgreSQL Survey, which is hosted by Timescale each year. In the 2022 survey results, “long-form” and “short-form” blog posts were the second and third most popular source of information for all respondents. It’s clear that PostgreSQL users love good blog content, and we think providing more opportunities for members of the community to contribute content helps the community as a whole.

I’ve been an Architect, a DevOps, a DataOps, and a DBA. I’m focused on databases, real-time data and streaming technologies. I work with Apache Kafka, RabbitMQ, Elastic Stack and PostgreSQL. After hours I love to share my knowledge and experience. I’m founder of DataOps Poland group, it’s an exchange place of experience, concepts and ideas from Data Engineering world.

https://www.dataops-academy.pl

https://www.meetup.com/dataops-poland/

https://www.linkedin.com/in/tomasz-gintowt/

--

--

Tomasz Gintowt

Architect, DevOps, SysOps, and DBA. Currently, I’m an IT Systems Engineer working with Apache Kafka, RabbitMQ, PostgreSQL, Elastic Stack in Real-Time Data Team.