A big one that nobody has mentioned yet - reduce memory requirements for your db. In general, for a database to be performant, it needs to be able to keep the hot part of the dataset and REGULARLY USED INDEXES in memory, you don't want to be paging indexes in and out of memory every time you have to use them.

Say you've got a giant table for of emails, and there's a bunch of indexed fields (people maybe want to filter their emails by tags, read/unread, etc.). If you keep them in one giant table, you need to keep those giant indexes fully in memory to get good performance. BUT, you know 99% of requests are for recent emails, people only occasionally look at old ones. So you partition your table by timestamp, and instead of running queries like `select * from email where status = 'unread' order by timestamp desc limit 50`, you run queries like `select * from email where status = 'unread' AND TIMESTAMP >= <pretty-recent> order by timestamp desc limit 50`. This only hits the first partition, and almost always returns the 50 emails you need - if it doesn't, you run subsequent queries to hit older partitions.

But now, say you partition by month, and keep 2 years of data, almost all queries are being serviced by a partition containing 1/24th of the total data, so keeping indexes for that 1 partition in memory is WAY easier than keeping indexes in memory for the entire giant table. When you have to query old partitions, it's slower and there's still some swapping happening, but that's rare anyways. So you can get away with having way less memory for your db, without performance degradation on most queries.

Partitioning by date is a common solution to efficient archiving - the partitions may be dumped independently, and instead of DELETE, which requires expensive cleanup after the fact, you can simply drop the partition (which does not require cleanup at all).

The declarative partitioning (added in PostgreSQL 10) is also transparent for the optimizer, i.e. it can understand how the data is routed to partitions, and can leverage it while planning/executing queries. For example when a table is partitioned on "a" and your query does "GROUP BY a" then in some cases the database can do the aggregation per partition - which should be more efficient in general (smaller hash tables, less data to sort, ...). Or when joining tables partitioned in the same way, it may be possible to do by joining the matching partitions (again, more efficient).

Obviously, PostgreSQL 10 only introduced the "core" declarative partitioning, and many such goodies are currently being worked on - either for PostgreSQL 11 or following version(s).

A simple example is you bucket your data into monthly tables such that when you run a query that has a timestamp constraint, the query analyzer is smart enough to only scan a subset of the tables that are relevant to your query. It's also really nice for expiring old data with table drops.

Imagine a very large table, let's say it's a tally table where we frequently run analytics queries against. Even with optimised indexing, you're going to hit performance issues eventually (it all depends on query complexity + size of data). Partitioning essentially splits data into different tables - Let's say we partition by date range, we could end up with a table for each individual year/month/week/day, whatever really! Instead of then performing actions against one big table, you would effectively only affect the partitions that belong to the date range you're interested in. Partitioning is all about splitting data into multiple tables.

It seems like the inverse of a merge table. Years ago we used to archive data into tables named by the date range and then we would create a merge table that combined them. So every night we would drop the oldest, create a new one, and create a new merge table of the updated set.

Partitioning looks to be going the other way. Create a big table, partition for convenience, drop partitions easily when you are ready to dispose of them. I can definitely see some cases where this would be super handy.

Poor data archiving practices.

Jokes aside when you need to operate reporting on a multi-billion row scale which only needs to look at a very specific subset of your data I could see the use of partitioning. It's the transparent cousin to "copy it into it's own table".