Categories
Database Development

How to prevent seek pagination from doing sequential scan over entire table

I have 3 tables and a materialized view: resource_categories contains all category names and metadata create table if not exists resource_categories ( category_id INT, title VARCHAR(255), content TEXT, icon VARCHAR(50) ); resources contains all resources under each category create table if not exists resources ( resource_id INT, title VARCHAR(255), content TEXT, link VARCHAR(1000), category_id INT […]

Categories
Database Development

How to prevent seek pagination from doing sequential scan over entire table

I have 3 tables and a materialized view resource_categories contains all category names and metadata create table if not exists resource_categories ( category_id INT, title VARCHAR(255), content TEXT, icon VARCHAR(50) ); resources contains all resources under each category create table if not exists resources ( resource_id INT, title VARCHAR(255), content TEXT, link VARCHAR(1000), category_id INT […]

Categories
Mastering Development

Best solution for INSERT or UPDATE bulk data from dataframe/csv to postgres database

Requirement: Insert new data and update existing data in bulk (row count > 1000) from a dataframe/csv (which ever suites) and save it in postgres database. Table: TEST_TABLE CREATE TABLE TEST_TABLE ( itemid varchar(100) NOT NULL PRIMARY KEY, title varchar(255), street varchar(10), pincode VARCHAR(100)); INSERT: [‘756252’, ‘tom title’, ‘APC Road’, ‘598733’ ], [‘75623’, ‘dick title’, […]

Categories
Database Development

How to use alternative storage engines of MariaDB?

I am migrating from MySQL to MariaDB, as I need better scalability for two types of tables (as compared with that of innoDB). Type 1: CREATE TABLE ItemPages ( PageID int(11) unsigned NOT NULL AUTO_INCREMENT, Title varchar(255), Added date, UNIQUE INDEX (Title), INDEX(Added), PRIMARY KEY(PageID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci KEY_BLOCK_SIZE=4 CREATE TABLE ItemContents […]