datablogs: Development
Showing posts with label Development. Show all posts
Showing posts with label Development. Show all posts

Tuesday, November 14, 2023

PostgreSQL Table Partition on AWS RDS

If we grow bigger in the business , seamlessly our customer and transaction data also increases . In the meantime performance needs to consider as well 

So in this case of bigger tables indexes will not help us to achieve good performance on peak times . Alternatively we have partitioning to split the tables data into multiple pieces on all the relational database environments 

Like wise we are going to do range partition on sample table in PostgreSQL Database , In PostgreSQL three types of partition methods are available , 


Below are the important concern in the PostgreSQL Partition ,

  • Possible to attach regular table into partitioned one 
  • Not Possible to transform regular table to partitioned one   

So based on the above formula , we have tried to transform regular table into partitioned one for your reference 

Any one can use this example and perform partitioning in AWS PostgreSQL RDS easily 

Click GitHub Link for Code : AWS-PostgreSQL-RDS-Table-Partition

Step 1 : Create base datablogspaycheck table and insert some sample records 

DROP TABLE IF EXISTS datablogspaycheck CASCADE;

DROP SEQUENCE IF EXISTS public.paycheck_id_seq;

CREATE SEQUENCE public.paycheck_id_seq

    START WITH 1

    INCREMENT BY 1

    NO MINVALUE

    NO MAXVALUE

    CACHE 1;

create table datablogspaycheck

(

    payment_id int NOT NULL DEFAULT nextval('public.paycheck_id_seq'::regclass), 

    created timestamptz NOT NULL,

    updated  timestamptz NOT NULL DEFAULT now(),

    amount float,

    status varchar DEFAULT 'new'

);

CREATE INDEX idx_paycheck ON datablogspaycheck (created);

INSERT INTO datablogspaycheck (created) VALUES (

generate_series(timestamp '2023-01-01'

               , now()

               , interval  '5 minutes') ); 

Step 2 : Rename base table with new name

ALTER TABLE datablogspaycheck RENAME TO datablogspaycheck_basetable;

Step 3 : Create Partitioned table 

create table datablogspaycheck

(

    payment_id int NOT NULL DEFAULT nextval('public.paycheck_id_seq'::regclass), 

    created timestamptz NOT NULL,

    updated  timestamptz NOT NULL DEFAULT now(),

    amount float,

    status varchar DEFAULT 'new'

)PARTITION BY RANGE (created);

Step 4 : Create Separate Partition for each create date 

CREATE TABLE datablogspaycheck_202303 PARTITION OF datablogspaycheck

    FOR VALUES FROM ('2023-01-01') TO ('2023-03-01');

   

CREATE TABLE datablogspaycheck_20230304 PARTITION OF datablogspaycheck

    FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');

    

CREATE TABLE datablogspaycheck_202304 PARTITION OF datablogspaycheck

    FOR VALUES FROM ('2023-04-01') TO ('2023-05-01');

    

CREATE TABLE datablogspaycheck_202311 PARTITION OF datablogspaycheck

    FOR VALUES FROM ('2023-05-01') TO ('2023-11-01');

   

CREATE TABLE datablogspaycheck_2024 PARTITION OF datablogspaycheck

    FOR VALUES FROM ('2023-11-01') TO ('2024-01-01');

Step 5 : Migrate the all records

insert into datablogspaycheck (payment_id,created,updated,amount,status) select payment_id,created,updated,amount,status from datablogspaycheck_basetable;

Step 6 : Validate each partition 

select * from datablogspaycheck_202303 order by 2 desc

select * from datablogspaycheck_20230304 order by 2 desc

select * from datablogspaycheck_202311 order by 2 desc

Its done , Easily migrated normal table data into partitioned table 

Thanks for Reading !!!



Monday, September 24, 2018

MySQL for Entity Framework

The tutorial guides you through MySQL Database connectivity with Entity Framework

We are facing entity framework database provider compatible with version could not be found for your data connection issue while connecting with MySQL 5.7 and 8.0. Problem with MySql.Data.Entity.EF6.dll missing and not able to connect with Entity framework

As per my requirements, the customer wants to migrate MSSQL to MySQL server. So I want to use any version of MySQL, installed with below configurations and achieve the connectivity 
1.MySQL Server : 5.6.41
2.MySQL Connector/Net : 6.9.12
3.Entity Framework : 6.2.0
4.Visual Studio : Professional 2017
Configuration steps are below,

1. Download required versions,

MySQL - https://dev.mysql.com/downloads/windows/installer/5.7.html

Visual Studio - https://visualstudio.microsoft.com/downloads/

2. Once downloaded, Launch web API project and Install entity framework 6.2.0 through NuGet Packages 


3.Install the MySQL 5.6 with Connector/Net 

Once you installed, you will see below dll files local server 
C:\Program Files (x86)\MySQL\MySQL Connector Net 6.9.12\Assemblies\v4.5


4. Add the dll files in Project reference and rebuild the project 

Once we rebuild, open the web.config file add the below lines if already provider tag part exist remove and add it 


5. Again rebuild the project and add the connection. You will successfully be connected


Thanks for reading ...