John Davidson

Message:

I have an issue on knowing how partition tables works exactly .

So , i have a table that is containing about 10000000 records .

To get my data from php page im running this code :

<select id="computation_date" name="computation_date" class="form-control input-lg" data-live-search="true">



<option value="">Select Computation Date</option>

<?php



// Select last computation date

$query_last_date = "SELECT datecalcul from Table_Name ORDER BY datecalcul DESC LIMIT 1";

$result_last_date = pg_query($query_last_date);

$row_last_date = pg_fetch_assoc($result_last_date);



// Fetch all the Computation Date data 

$query_date = "SELECT DISTINCT datecalcul as d_datecalcul from Table_Name ORDER BY datecalcul ASC";

$result_date = pg_query($query_date);

while ($row_date = pg_fetch_assoc($result_date)) {

echo '<option value="' . $row_date['datecalcul'] . '">' . $row_date['datecalcul'] . '</option>';

                    }

echo '<option value="' . $row_last_date['datecalcul'] . '"  selected="selected">' . $row_last_date['datecalcul'] . '</option>';

$last_date = $row_last_date['datecalcul'];

?>



</select>

So , i figured out that to handle lots of Data i have to use partition Tables to optimize and increase performance in displaying data in php web page

So i have , partitioned my 'Table_Name ' using this code on PostgreSQL :

-- step 1 - declare table defintion

create table Table_Truncate (

LIKE Table_Name INCLUDING ALL)

PARTITION BY RANGE (datecalcul);





-- step 2 - declare partitions

create table Table_Truncate_2021 PARTITION OF Table_Truncate

for values from ('2021-01-01') TO ('2021-12-31');



create table Table_Truncate_2020 PARTITION OF Table_Truncate

for values from ('2020-01-01') TO ('2020-12-31');



create table Table_Truncate_2023 PARTITION OF Table_Truncate

for values from ('2023-01-01') TO ('2023-12-31');



create table Table_Truncate_2022 PARTITION OF Table_Truncate

for values from ('2022-01-01') TO ('2022-12-31');



create table Table_Truncate_2000 PARTITION OF Table_Truncate

for values from ('2000-01-01') TO ('2000-12-31');



-- step 3 insert data

insert into Table_Truncate

select * from Table_Name ;

My question is , how can i display my data into php page using partition Tables ? or doesthe fact that i have partitionned my table i don't modify my php code ?

---

My Answer:
Once you have partitioned your table in PostgreSQL, you can continue to use the same PHP code to fetch and display data from the partitioned table. The partitioning is done at the database level and does not require any changes to your PHP code.

When you query the partitioned table in your PHP code, PostgreSQL will automatically route the query to the appropriate partition based on the partitioning key (in this case, the `datecalcul` column). So, your existing PHP code should work seamlessly with the partitioned table.

Just make sure that your queries in PHP are still targeting the main partitioned table (`Table_Truncate` in this case) and not the individual partition tables (`Table_Truncate_2021`, `Table_Truncate_2020`, etc.). PostgreSQL will handle the partitioning logic internally and retrieve the data from the correct partition based on the query conditions.

So, you can continue to use your existing PHP code to fetch and display data from the partitioned table without any modifications.