> On 8 Sep 2023, at 13:25, Nature Conservation Geovista Space <conservation.by.geovista.space@xxxxxxxxx> wrote: > > Dear Pg-users, > I am coming back to Postgres/PostGIS after a few years. I am dealing with a big database with a lot of geometries and too many vertices. So a lot of geometry going on then… That seems important in this case. > After hours running a query to Subdivide, I get this Postgres error > 2023-09-08 02:11:23.745 BST [328594] postgres@database ERROR: could not extend file "base/16388/7985375.1020": No space left on device > 2023-09-08 02:11:23.745 BST [328594] postgres@database HINT: Check free disk space. > 2023-09-08 02:11:23.745 BST [328594] postgres@database STATEMENT: CREATE TABLE _gaul_administrative_subdivided100 AS ( > SELECT *, st_subdivide(geom,100) AS geom_subdivided100 > FROM gaul_administrative > ); That _looks_ like a query that could blow through space rather quickly. How large is gaul_administrative in GB? In rows? How many subdivisions does this generate per row on average? How many bytes are those subdivisions on average? Multiply those numbers, and you get a fair indication of how much space that table requires. Does that fit in 1.1TB? Frankly, I don’t see the point of repeating the geom column in that table after dividing it up, aren’t you just wasting space there? The original is still available in the source table, after all. And what about the other columns that you clone into this new table? My suggestion would be to minimise that with an operation like the above. This would be one of those situations where I’d introduce a (bigint) surrogate key and use that to link the two tables together, even though I’m a natural key person. Something like this (I had to make a few assumptions about your source table): CREATE TABLE _gaul_administrative_subdivided100 AS ( id bigint NOT NULL, geomid bigserial NOT NULL, geom_subdivided100 geometry NOT NULL ); -- If you care about referential integrity here ALTER TABLE _gaul_administrative_subdivided100 ADD FOREIGN KEY (id) REFERENCES gaul_administrative (id) ON DELETE CASCADE ON UPDATE RESTRICT; INSERT INTO _gaul_administrative_subdivided100 (id, geom_subdivided100) SELECT id, st_subdivide(geom,100) FROM gaul_administrative; (…) > It seems that it is not a problem of space. I wouldn’t be too sure of that, but I don’t have the numbers. > Command df -h returns: > Filesystem Size Used Avail Use% Mounted on > tmpfs 6.3G 1.1M 6.3G 1% /run > /dev/sda 1.3T 164G 1.1T 14% / > tmpfs 32G 3.2M 32G 1% /dev/shm > tmpfs 5.0M 0 5.0M 0% /run/lock > tmpfs 6.3G 4.0K 6.3G 1% /run/user/1000 Is this after the error and after PG finished rolling back? What does this show while that query is going on? If gaul_administrative takes up a large part of those 164G, then you probably don’t have enough space for a 10x multiplication in size from the original table to the new table. And that happening looks entirely possible from the information you provided. Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
