> On 8 Sep 2023, at 13:25, Nature Conservation Geovista Space
> <conservation.by.geovista.sp...@gmail.com> 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.
