Partitioning in Postgres, 2022 Edition | Hacker News Reader

Partitioning in Postgres, 2022 Edition

brandur.org

111 pointsplaur78228 comments

Is Postgres ever going to allow automatic partitioning?

Something like:

    CREATE TABLE logs (
      time TIMESTAMPTZ,
      data JSONB  
    ) PARTITION ON time IN INTERVAL '1 day'

And it just creates/manages these partitions for every day?

If you can already manage partitions like this manually it feels like the next step is to just have it be automatic. So you have less of a need to switch to Timescale or ClickHouse or whatever other database as the amount of data you're storing/querying grows. (Yeah that's a handwave-y suggestion but at least you could stick with Postgres for longer.)

pg_partman, which is included in most distributions of Postgres (including RDS) can handle some cases

- https://github.com/pgpartman/pg_partman

- https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Postg...

Timescale is just an extension too, but still I wonder why Postgres should not support automatic partition management themselves.

Timescale is not just an extension. It offers many features and a restrictive license. Partman is just a utility. But I agree something like partman should be part of core Postgres

It's just an extension. You can build the Apache 2.0 part of the extension and ignore the Timescale License parts. Those parts are mainly about multinode, compression and continuous aggregates.

TSL is mainly about not competing with their cloud offerings. So you can't run a database-as-a-service for time series data with it.

More about the license: https://www.timescale.com/blog/building-open-source-business...

Comparison about the open source and community editions: https://docs.timescale.com/timescaledb/latest/timescaledb-ed...

Why would I build a part of it manually when pgpartman is already available? It’s just an extension as its just code.

Regardless of what they say on a public page, that is a vague license. Can I use Timescale to provide a SaaS service that collects application traces, and I provide a DSL to query the database that is not exposing the DB directly? No, its a gray area.

"Can I use Timescale to provide a SaaS service that collects application traces, and I provide a DSL to query the database that is not exposing the DB directly?"

Yes you can. (Timescale co-founder here)

The vast majority of people using TimescaleDB use our community/open-source software (rather than managed Cloud), and vast vast majority of those use the TSL (Community) edition, including many as part of their SaaS service.

It's a three-part test for asking "is this a value added service?" [0] Given the way your have described your service, sounds like a clear "Yes".

- Is your SaaS service primarily different than a database product/service? Yes.

- Is the main value of your SaaS service different than that of a time-series database, and you aren't primarily offering your SaaS service as a time-series database? Yes.

- Are users prevented from directly defining internal table structures through the database DDL? Presumably yes.

[0] https://www.timescale.com/legal/licenses#section-3-10-value-...

As someone previously sceptical of the wording in your proprietary license, I appreciate the clarification for this use case & the rationale behind those license terms.

I asked this question in their chat, the CEO answered within minutes that it’s allowed.

pg_partman is awesome. The SQL syntax for partitioning isn't dissimilar to your example (since PG11?), but like you say, it's the automatic creation and cleanup of partitions that's it's missing. pg_partman used to have to do a lot more things, but nowadays you can just have it do the job of creating X future partitions for you at a given interval, and truncating partitions older than retention period Y.

It's a great extension. We have used it in production for a few years now, as part of a commercial on-prem observability tool (an app that keeps around 1TB of data at any one time, written at avg 15mb/sec, maybe 500ish partitions, user configurable retention strategy). It is extremely reliable. Support for Keith F working on it is one of the reasons I like Crunchy.

edit: I clearly didn't read the link before writing this

In addition to that, I'd really like:

    CREATE TABLE business_data (
      customer_key TEXT,
      data JSONB  
    ) PARTITION ON customer_key

where it creates a partition for each unique value of customer_key. Seems like this ought not to be that hard.

You already can. PARTITION BY LIST.

CREATE TABLE … PARTITION OF … FOR VALUES IN ('the_customer');

https://www.postgresql.org/docs/current/ddl-partitioning.htm...

Yeah, I guess I just find it a bit frustrating that I have to setup partitions ahead of time using DDL. I would like it if postgres would just automatically create a partition on the fly when I insert new values into the specified column.

That would open up some non-trivial DoS attack vectors as things currently stand. Who would own the new partition? If all records for the company were removed, does the partition get deleted or remain empty? What happens if some performs a large insert with random customer ids?

However if you really want to go down this road, you could put a trigger on the main customer table for INSERT or UPDATE to run a SECURITY DEFINED (setuid) function that runs a CREATE TABLE … IF NOT EXISTS DDL query to your specifications. Just beware of corner cases and be extremely careful regarding secure access to modifications of that customers table.

Just a quick note that Timescale is just an extension for Postgres, not a “switch” of database.

It’s as easy as:

    CREATE EXTENSION timescaledb:
    SELECT create_hypertable('table_name', 'time_column_name', chunk_time_interval=>interval '1 day'):

Disclaimer: I’m a Timescaler

I am building my own database engine using some data objects (key-value stores) I invented to form columnar store tables. It has some really fast query speeds and analytic features (e.g. pivot tables) that test favorably compared to Postgres and other RDBMS offerings. (https://www.youtube.com/watch?v=OVICKCkWMZE)

Partitioning a big table is definitely on my TODO list. How big does a typical table need to grow before partitioning is seen as a 'necessity'? What are some ways current partitioning strategies have made things too difficult?

That's awesome. Link to your repo or any posts you've written about your progress?

I haven't decided yet which parts I am going to open source or which license I am going to use; but the binaries are available for free download on our website https://www.Didgets.com and I have a newsletter at Didgets.substack.com

To my mind, the right maximum size of a partition is such that your important queries are still fast. This often means that your most important indexes for the partition that serves most queries fit in RAM. So it's highly specific to a particular schema.

Often partitions are made for time-related data, and these partitions may have smaller granularity. Say, you want to keep last 6 months of data, but make the partitions week-sized, or even day-sized, to make transitions more smooth.

We use Postgres partitioning quite successfully to handle our customer based data. Everything is partitioned by customer and some customers are partitioned further.

One gotcha to be careful with is that if you run a query spanning multiple partitions, it will run them all at once and if your database isn't super big - will bring it to its knees.

Outside of that really no issues. We also use Timescale quite heavily, which also works fantastic.

IIRC, it won't run them all at the _same time_, it will run them in parallel, which ends up being limited by the value of `max_parallel_workers` in your config. So if you find that parallel operations are swamping your DB, maybe trying stepping that config down a bit. In general, queries that span several partitions shouldn't be a problem. Hitting _all_ partitions for a really big table, that's another thing entirely. But since you're partitioned you really ought to be limiting by the value of the partition key in your queries anyways...

> But since you're partitioned you really ought to be limiting by the value of the partition key in your queries anyways...

Not if these are analytical queries...

If you're wondering what the point of partitioning a table that's going to be used for analytical queries: if you partition by insertion time, and your table is append-only, then you're only ever writing to one partition (the newest one), with all the partitions other than the newest one being "closed" — VACUUM FREEZEd and then never written to again. So, rather than one huge set of indices that get gradually-more expensive to update as you insert more records, those write costs will only rise up to the end of the month/week/day, and then jump back down to zero as you begin writing to a new empty partition.

Do you have any notes on how you set this up? Did you use extensions and if so which?

How do people partition with a requirement where you also need unique index constraints (across all partitions)?

I wonder if this worth the price: an extra (thing_uniq) table and extra key column on partitioned tables. So pay an extra query to the thing_uniq table before insert to target table. thing_uniq has one indexed column (the key). But we lose upsert.

Unique constraints work as long as the partition key is included in the constraint (which makes sense, so a table knows it’s the only partition for a value and checks it’s unique index).

I think I would use a lightweight 'reference' table with the unique index, put the actual data in the partitioned table, and use a foreign key to enforce a 1:1 relationship.

Something like this:

    create table orders(
             order_id int unique
           , year int
           , primary key (order_id, year)
    );

    create table order_data(
             order_id int
           , year int
           , LOADS_OF_COLUMNS_HERE jsonb

           , primary key (order_id, year)
           , foreign key (order_id, year) references orders(order_id, year)
    ) partition by list(year);

Yeah, same thought as my other comment. Not sure about the other comment that says using partition key .. it doesn't seem possible.

Reply on news.ycombinator.com