Underwater noise pollution by shipping activities is widely recognised as a significant threat to marine life. The noise emitted by vessels can have various detrimental efects on fish and marine ecosystems. Therefore, accurately estimating and analysing vessel-generated underwater noise is a critical challenge for the protection and conservation of marine environments. For this reason, we have built a model for the spatio-temporal characterisation of underwater noise generated by vessels. This paper builds on this model by optimising the code pipeline, implementing table partitioning and leveraging parallelisation techniques. These enhancements allow us to explore various partitioning methods while significantly improving the computational performance and enabling more eficient analysis of underwater noise. Our approach not only improves the computational eficiency but also preserves the accuracy of the noise calculations, ofering a more scalable solution for large datasets.
Published in the Proceedings of the Workshops of the EDBT/ICDT 2025 Joint Conference (March 25-28, 2025), Barcelona, Spain * Corresponding author. $ giulia.rovinelli@unive.it (G. Rovinelli); esteban.zimanyi@ulb.be (E. Zimányi); simeoni@unive.it (M. Simeoni); davide.rocchesso@unimi.it (D. Rocchesso); rafaeta@unive.it (A. Rafaetà)
Copyright © 2025 for this paper by its authors. Use permitted under Creative Commons License
Attribution 4.0 International (CC BY 4.0). we focus on the fishing activities in the Northern Adri- the source levels to all the other vessels, we need to reatic Sea, one of the most heavily exploited areas of the late the sound pressure level to the engine horsepower, Mediterranean Sea, where underwater noise pollution the latter being available in our dataset. If we assume is a recognised consequence of intensive fishing activity. that a constant fraction of engine power gets converted The dataset used in this study includes AIS data from Ital- into acoustic power (i.e. acoustic power scales linearly ian and Croatian fishing vessels for June 2020. Moreover, with horsepower), then 3 dB are added per doubling in to determine the acoustic features of the vessel engines engine power. We adopt such a linear progression on and refine the propagation model, we use direct acoustic logarithmic scale of engine power and the resulting value measurements from the Interreg project SOUNDSCAPE1, is denoted with 0. For example, for engines between which conducted acoustic monitoring in the Northern 100 Hp and 835 Hp, considering a frequency of 63 Hz, we Adriatic Sea from March 2020 to June 2021. obtain a range between 123 dB and 136 dB.
The paper is organised as follows. Section 2 overviews Diferences in source level may result from variations
the sound propagation model introduced in [
The basic objective of noise modelling is to assess noise ranging from 5 dB to 15 dB during trawling
activhow much noise a particular activity will generate in ities. Specifically, it is noted that the efect of trawling
the surrounding area. Specifically, the aim is to model is minimal below 100 Hz and increases with frequency.
the received noise level (RL) at a given point (or points), Accordingly, we assign an increase of 5 dB at 63 Hz when
based on the sound source level (SL) of the noise source, the vessel is trawling.
and the amount of sound energy which is lost as the To account for transmission loss, we adopt a
combisound wave propagates from the source to the receiver nation of spherical propagation and mode stripping [
The classic sonar equation [
ment concerning the implementation of the underwater
noise model presented in [
The SOUNDSCAPE measurements [
The implementation of the model to calculate the un- ration. On this machine we deployed PostgreSQL 16.6,
derwater noise generated by vessels is succinctly de- PostGIS 3.5, and MobilityDB 1.3.
scribed below (for more details, see [
Finally, by grouping by cell id and time, we combine all the received sound levels to obtain the total noise level to be associated with the cell.
Grid table restructuring. We add two new attributes to the cell of the grid: grid_r and grid_c, which indicate the row and column numbers within the grid. Hence, starting from the lower-left corner, the grid cells are numbered sequentially, so they are identified as (1, 1), (1, 2) and so on. This grid-based system allows for an eficient
identification of the cells within a bounding box, without the need for costly spatial operations. The table Grid includes also the and coordinates of the cell centroid, which will be used for calculating sound propagation. The structure of the table Grid is as follows. results as the implementation described in Section 2. In fact, the new execution time for June 2020 is reduced to 7 hours, making the code over six time faster than the original version, saving 37 hours of execution time (see Figure 3, where this is called Optimised Pipeline). CREATE TABLE Grid ( grid_id integer PRIMARY KEY, grid_r integer NOT NULL, grid_c integer NOT NULL, centroid_x double precision, centroid_y double precision, elevation real, ambient_noise real, alpha tfloat ); CREATE INDEX idx_grid_r ON Grid (grid_r); CREATE INDEX idx_grid_c ON Grid (grid_c);
present an analysis of various partitioning and parallelisation techniques. In particular, selecting the cells afected by noise propagation for each point (Step 3 in Figure 1) remains a computationally expensive operation. This complexity arises from the need to perform a JOIN operation between the table PointBoundingBox, which contains each vessel position along with its sound propagation bounding box, encompassing over 4 million points, and the table Grid, which consists of 43,508 cells.
Consequently, the JOIN involves a computational efort equivalent to approximately 4 million × 43 thousand operations, making it inherently costly.
In Section 4.1, we examine table partitioning techniques in PostgreSQL, applying both range and hash partitioning strategies. In Section 4.2, we extend this approach by combining PostgreSQL partitioning with multidimensional tiling, focusing on the spatial dimension.
Finally, in Section 4.3, we leverage the Citus extension of PostgreSQL to apply sharding and take advantage of its parallel query execution capabilities.
Bounding box for Noise Propagation. To compute
the total received noise level for each cell of our grid, we
proceed as illustrated in Figure 1. After reconstructing
the vessel trajectories from the AIS data, we get the
positions of all the fishing vessels at the same time instants,
i.e., every 60 seconds (Step 1 in Figure 1). For each point
, we determine the cell it belongs to, by comparing the
coordinates of with the grid cell boundaries which are
computed by adding or subtracting 500 meters from the
coordinates of the cell centroid. We calculate the noise
generated by the fishing vessel obtained by adding to the
sound level associated with the horsepower of the boat, a
contribution related to the actual speed of the vessel in 4.1. PostgreSQL Partitioning
(see Equation (1)), and the noise due to the fishing activ- The first technique we explore to enhance the
execuity, if it occurs in . Then, we compute the propagation tion of our code is Table Partitioning in PostgreSQL. This
radius (expressed in meters) and we build the sound method consists in dividing a logically large table into
propagation bounding box (Step 2 in Figure 1), defined by smaller physical segments, with each partition being an
the minimum and maximum row and column identifiers independent table that stores a specific subset of the
origthat enclose all the cells afected by the noise generated inal data. PostgreSQL natively supports three forms of
by the vessel at . These boundaries are obtained simply partitioning [
SELECT point_id,trip_id,mmsi,x,y,time,db_boat, grid_r-radius AS r_min, grid_r+radius AS r_max, grid_c-radius AS c_min, grid_c+radius AS c_max FROM UnnestTripWithCell );
This query returns, for each spatio-temporal point where the point_id identifies the spatio-temporal point, (pbb.x, pbb.y, pbb.time), the cells that are afected trip_id is the identifier of the trip to which the point by the noise generated at that point by the fishing vessel, belongs, mmsi refers to the vessel performing the trip, and computes the distance between the point and the x and y are the coordinates of the point, time specifies centroids of these cells (Step 3 in Figure 1). the date and hour of the point, and db_boat denotes The query plan involves a combination of paralthe decibel level generated by the vessel at that point, lel and sequential scans to optimise the data retrieval based on its speed and activity. The remaining attributes process. The first step is a parallel append operarepresent the row and column identifiers used to con- tion, which processes multiple partitions of the table struct the sound propagation bounding box including all PointBb_RangePart in parallel. Each partition (correthe cells afected by the noise generated by the vessel at sponding to a diferent time range) is accessed through point_id. a parallel sequential scan. The second part of the plan
We partition the table PointBoundingBox into four involves a bitmap heap scan on the table Grid, where partitions based on time ranges to reflect the recurring rows are selected based on conditions that compare the weekly pattern: fishing activity is intense from Monday grid’s row and column identifiers with the corresponding to Thursday, while significantly lower from Friday to Sun- bounding box identifiers from the partitions. Specifically, day. Additionally, this partitioning ensures a balanced the query checks that the cells, identified by row grid_r disk usage across the partitions (see Table 1). We can and column grid_c, lie within the minimum and maxicreate the partitioned table as follows. mum row and column values of the bounding box. This comparison is optimised through bitmap index scans on CREATE TABLE PointBb_RangePart(LIKE PointBoundingBox) idx_grid_r and idx_grid_c, each filtering the data PARTITION BY RANGE(time); based on the row and column values. In essence, the query plan performs a parallel scan of partitioned data,
-dimensional domain into tiles of varying dimensions.
Range Partitioning Hash Partitioning This approach has several applications. For instance,
mulN. partition Disk Usage Rows Disk Usage Rows tidimensional tiling can be applied to partition and/or
1 81 MB 888,849 97 MB 1,090,196 distribute datasets across a cluster of servers. One key
23 19105MMBB 19,29609,1,14882 9847 MMBB 19,07579, 7,77362 advantage of this partitioning mechanism is that it
pre4 93 MB 1,019,383 92 MB 1,039,858 serves spatial and temporal proximity, unlike traditional
hash-based partitioning methods. This distribution
reduces the amount of data that needs to be exchanged
followed by an eficient indexed search of the grid, en- between nodes during query processing, a process
comsuring faster query execution by narrowing down the monly known as reshufling [
By partitioning the table PointBb_RangePart while spatial dimension. Specifically, we partition the positions leaving the rest of the code unchanged, the entire pipeline of vessels based on their spatial locations. The tiling can now completes in just 2 hours and 25 minutes. The com- be either regular, where all tiles are of equal size in each putation of sound propagation is 18.2 times faster than dimension, or adaptive, where the size of the cells may the first implementation (which took 44 hours) and 2.9 vary across dimensions. In the first case, we employ a times faster than the optimised version without partition- regular tiling, constructing a uniform grid consisting of ing (which took 7 hours). 4 × 3 cells, as shown in Figure 2a. To generate this grid, we used the MobilityDB function spaceTiles. The grid size was manually tuned to balance the trade-of between the number of partitions and the data distribution within each partition. Then we create the partitioned table along with the corresponding tables for the space tiles, by using the List partitioning technique.
Hash Partitioning on MMSI. As a second partitioning experiment, we use the hash partitioning on the mmsi column of the table PointBoundingBox. We aim to divide the table PointBoundingBox into four partitions based on a hash function. The partitioned table can be created as follows.
CREATE TABLE PointBoundingBox_RegGrid(LIKE
PointBoundingBox) PARTITION BY LIST(TileId); CREATE TABLE PointBb_RegGrid_1 PARTITION OF
PointBoundingBox_RegGrid FOR VALUES IN (1); CREATE TABLE PointBoundingBox_HashPart (LIKE
PointBoundingBox) PARTITION BY HASH(mmsi); CREATE TABLE PointBb_HashPart_1 PARTITION OF
PointBoundingBox_HashPart FOR VALUES WITH ( MODULUS 4, REMAINDER 0);
data is inserted into the partitioned table, the entries are We have only reported the creation of the first hash automatically directed to their corresponding partitions. partition. Next, we insert the values into the table The limitation of this type of tiling is that it does not PointBoundingBox_HashPart, which are automati- ensure balanced workload distribution across the tiles. cally distributed across the partitions. Table 1 (right) A possible solution to this issue is to use an adappresents some statistics on the number of rows and the tive grid, as illustrated in Figure 2b. In this case, we disk usage of each partition. In this case, we can ob- create a grid that divides the region based on the distriserve that the data distribution across the four partitions bution of vessel points in the Northern Adriatic Sea. It is is more balanced compared to the partitions obtained worth noting that some cells are smaller, as they contain through time-based range partitioning. a higher density of data points. Then, we partition the ta
Now we use table PointBoundingBox_HashPart, ble PointBoundingBox according to the adaptive grid instead of table PointBb_RangePart, in the query we structure. The process of creating the partitioned table, want to optimize, presented in the previous subsection. along with the corresponding tables for the spatial tiles, The query plan is the same as that described for range follows the same steps as for the regular grid. partitioning and consists of a Parallel Seq Scan across Table 2 presents statistics on the number of rows in the four partitions of the hash-partitioned table and a each tile, as well as their respective disk usage, for both Bitmap Heap Scan on the table Grid. The execution time the regular and adaptive grids. The table clearly shows for June 2020 is 2 hours and 20 minutes, which is slightly that the data partitioned according to the adaptive grid faster than the range partitioning approach. exhibits a more balanced distribution across the tiles compared to the regular tiling. However, certain tiles (specifically, tiles 1, 2, and 12) contain noticeably fewer data points, because they mostly cover the mainland.
The query we aim to optimise is the one presented (a) Regular grid.
(b) Adaptive grid.
Table 2 age the power of a distributed system while maintaining Statistics for the partitions by list on the tileId column. compatibility with existing PostgreSQL tools. By using sharding and replication Citus scales PostgreSQL across
Regular Grid Adaptive Grid several servers. Sharding is a method employed in disTile Disk Usage Rows Disk Usage Rows tributed systems to divide data horizontally across multi1 18 MB 168,403 32 kB 0 ple servers or nodes. It involves splitting a large dataset 2 95 MB 882,540 4000 kB 35,144 into smaller, more manageable pieces known as shards. 3 61 MB 571,392 53 MB 494,276 Each shard holds a portion of the data, and collectively, 54 7374 MMBB 731154,,001511 4982 MMBB 484559,,479619 they represent the entire dataset. Citus enables timeseries 6 23 MB 212,307 40 MB 373,537 data to be scaled by combining PostgreSQL single-node 7 6176 kB 54,928 44 MB 404,563 declarative table partitioning with its distributed shard8 32 kB 0 18 MB 165,596 ing capabilities, creating a scalable time-series database. 190 11177MMBB 11,05920,7,94873 6684 MMBB 569363,,420110 To optimise our pipeline, we first apply PostgreSQL 11 688 kB 5,200 15 MB 143,051 range partitioning based on time, followed by distributing 12 32 kB 0 1944 kB 16,524 the partitions using Citus sharding mechanism. Here, we utilise Citus in a single-node cluster configuration,where a single PostgreSQL server employs Citus to locally shard in Section 4.1. The query plan, like the previous ones, the data (with the coordinator also acting as a worker). combines parallel and sequential scans to optimise data This configuration has been implemented on the machine retrieval. The first step is a parallel append opera- described in Section 3 running Citus 12.1.6. As outlined in tion, which processes multiple partitions of the table Section 4.1 we want to partition the PointBoundingBox PointBoundingBox_RegGrid concurrently. This is table based on time ranges. The partitions can be defined followed by a bitmap heap scan on the table Grid, where using the following Citus function. rows are selected based on conditions that compare the SELECT create_time_partitions ( grid’s row and column identifiers with the corresponding table_name := ‘PointBoundingBox_RangePart’, bounding box identifiers from the partitions. By tiling the partition_interval := ‘1 week’, space with the regular grid, the full pipeline is executed start_from := ‘2020-06-01 00:00:00’, in 2 hours 46 minutes, while using the adaptive grid it end_at := ‘2020-06-30 23:59:59’ ); completes in just 2 hours and 16 minutes, which slightly improves the techniques in Section 4.1.
4.3. Using Citus for parallelisation
horizontal scaling, making it suitable for handling large datasets across multiple machines. It distributes both data and queries across a cluster, allowing users to lever
SELECT create_distributed_table(
‘PointBoundingBox_RangePart’, ‘point_id’); SELECT create_reference_table(‘Grid’);
5. Concluding Remarks
the table Grid into a single shard and replicates the
shard to every worker node. Tables distributed in the
second way are called reference tables and are employed Monitoring underwater noise pollution caused by
huto store data that requires frequent access by multiple man activities is crucial for preserving a healthy marine
nodes within a cluster. Table 3 presents statistics on ecosystem. In this paper, we presented several
optimithe number of rows in each partition, along with their sations to the underwater noise propagation pipeline
respective disk usage. presented in [
The objective, as in the previous cases, is to optimise support scalability and reduce computational overhead.
the query described in Section 4.1. When executed us- Figure 3 collects the results of our experiments on
ing Citus, the query plan reveals that the workload is June 2020 described in the previous sections. A clear
distributed across multiple tasks, with a total of 32 tasks improvement is observed between the original pipeline
created. Each task is assigned to a specific execution implementation presented in [
We also utilise Citus for the space tiling pre- plored. Finally, we plan to analyse the entire year of 2020
sented in Section 4.2. Specifically, we partition the to gain deeper insights into how partitioning and
paralPointBoundingBox table according to the adaptive grid lelisation perform with a larger volume of data, where
structure and distribute it using the Citus function pre- their advantages are likely to become more pronounced.
viously discussed. The query plan is clearly similar to This work enhances our original underwater sound
the case described above, with the workload distributed propagation model with greater computational eficiency,
across multiple tasks. The main diference lies in the pres- ofering a scalable solution for modelling underwater
ence of 12 partitioned tables. The execution time for the noise. By balancing estimation accuracy with
computaentire pipeline, using Citus and distributing the points tional efort, it can provide a convenient alternative to
according to the adaptive grid, is 3 hours and 30 minutes, existing approaches, which often rely on hydrophone
which is slightly faster than the partitioning by the time measurements or acoustic simulations and require
excolumn. However, the pipeline incorporating Citus did tensive input data along with significant computational
not yield better performance compared to partitioning resources to manage complex calculations.
alone. As detailed in Cubukcu et al. [
Ecosystem Program "Interconnected Northeast Innovation Ecosystem (iNEST)", CUP H43C22000540006.
This work took place within the framework of the DoE 2023-2027 (MUR, AIS.DIP.ECCELLENZA2023_27.FF project).