4 ms·

Is Postgres ever going to allow automatic partitioning? Something like: CREATE TABLE logs ( time TIMESTAMPTZ, data JSONB ) PARTITION ON

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.)

yawgmoth 4y ago
pg_partman, which is included in most distributions of Postgres (including RDS) can handle some cases - https://github.com/pgpartman/pg_partman https://github.com/pgpartman/pg_partman - https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_Partitions.html https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Postg...
- eatonphil 4y ago
  Timescale is just an extension too, but still I wonder why Postgres should not support automatic partition management themselves.
  - CSDude 4y ago
    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
    - xdanger 4y ago
      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-in-cloud-era-v2/ https://www.timescale.com/blog/building-open-source-business... Comparison about the open source and community editions: https://docs.timescale.com/timescaledb/latest/timescaledb-edition-comparison/ https://docs.timescale.com/timescaledb/latest/timescaledb-ed...
      - CSDude 4y ago
        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.
        mfreed 4y ago
        "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-added-products-or-services https://www.timescale.com/legal/licenses#section-3-10-value-...
        boomskats 4y ago
        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.
        eska 4y ago
        I asked this question in their chat, the CEO answered within minutes that it’s allowed.
- boomskats 4y ago
  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
nicoburns 4y ago
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.
- ttfkam 4y ago
  You already can. PARTITION BY LIST. CREATE TABLE … PARTITION OF … FOR VALUES IN ('the_customer'); https://www.postgresql.org/docs/current/ddl-partitioning.html https://www.postgresql.org/docs/current/ddl-partitioning.htm...
  - nicoburns 4y ago
    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.
    - ttfkam 4y ago
      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.
jamesgresql 4y ago
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

yawgmoth 4y ago

pg_partman, which is included in most distributions of Postgres (including RDS) can handle some cases - https://github.com/pgpartman/pg_partman https://github.com/pgpartman/pg_partman - https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_Partitions.html https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Postg...

eatonphil 4y ago
Timescale is just an extension too, but still I wonder why Postgres should not support automatic partition management themselves.
- CSDude 4y ago
  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
  - xdanger 4y ago
    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-in-cloud-era-v2/ https://www.timescale.com/blog/building-open-source-business... Comparison about the open source and community editions: https://docs.timescale.com/timescaledb/latest/timescaledb-edition-comparison/ https://docs.timescale.com/timescaledb/latest/timescaledb-ed...
    - CSDude 4y ago
      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.
      - mfreed 4y ago
        "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-added-products-or-services https://www.timescale.com/legal/licenses#section-3-10-value-...
        boomskats 4y ago
        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.
      - eska 4y ago
        I asked this question in their chat, the CEO answered within minutes that it’s allowed.
boomskats 4y ago
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

nicoburns 4y ago

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.

ttfkam 4y ago
You already can. PARTITION BY LIST. CREATE TABLE … PARTITION OF … FOR VALUES IN ('the_customer'); https://www.postgresql.org/docs/current/ddl-partitioning.html https://www.postgresql.org/docs/current/ddl-partitioning.htm...
- nicoburns 4y ago
  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.
  - ttfkam 4y ago
    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.

jamesgresql 4y ago

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