Does piccolo support Tablespaces?

[theelderbeever]

Background on the question... some of our tables are very large and our deployment infrastructure limits us to 16TB of disk space per provisioned drive. Having all of the tables on the same drive runs the risk of filling up our drive. We would like to set tables to tablespaces so we can provision drives on a per table/tablespace basis.

Is there a way to CREATE TABLE foo(i int) TABLESPACE space1; when generating the ORM Models?

Is the solution to generate empty tables using a raw SQL migration and then add the models in the next migration? Would Piccolo perform the correct ALTER TABLE commands if the tables weren't generated via the ORM models?

[dantownsend]

Wow, that's a big table. Tablespaces aren't currently supported, but my feeling is it will be quite easy to add.

Table.__init_subclass__ just needs to accept a tablespace argument, and then the CREATE TABLE DDL just needs to use it:

piccolo/piccolo/query/methods/create.py

Line 31 in a89c2fa

def default_ddl(self) -> t.Sequence[str]:

I'll give it a quick go.

[dantownsend]

Something like this:

#527

The question is how far it goes - should it try creating tablespaces if they don't exist?

[dantownsend]

And whether to automatically create any indexes for that table in the same tablespace - I'm guessing the answer is yes.

[theelderbeever]

Should piccolo create tablespaces if they don't exist?

• I think the workaround is easy enough that it isn't super necessary. Plus I don't believe CREATE TABLESPACE can be used with IF NOT EXISTS which makes the SQL to check for the existence of the tablespace already annoying at best. So I would say probably not on this one

Should indexes be created in the same tablespace?

• I would say yes on this one especially since it is often the case where this is being done for drive storage capabilities. Granted this should probably be customizable. One argument in favor of being the case where you push older data to a tablespace on an HDD but wanting to keep the index on a faster SSD

re: the big table... yeah this is because we are still leveraging Timescale for our data. I am sure we will eventually run into issues where this becomes untenable and we really need to move to a multinode cluster but we haven't pulled the trigger on that quite yet.
FWIW this is a single node timescaledb running on AWS EKS with dedicated nodes for the database. Currently backed with just gp2 EBS stores but looking at some IOPs stores. Take a look at TimescaleDB's helm chart for single and multinode setups. Pretty good stuff and they are good implementations of a lot of things you directly call out in your "Managed Postgres" blog post.

[dantownsend]

It's pretty much finished now - just need to work out how to add unit tests for it.

#527

I could create a tablespace in /tmp/some-folder when the test runner starts, and then tear it down at the end.