TransWikia.com

Relational DB which can do inserts at billions of rows with multiple indexed columns?

Database Administrators Asked by 2alltra8034 on October 28, 2021

I am looking for advice on a database to potentially replace SQLite for my application.

To briefly describe my DB requirements:

  • needs to support billions of rows and the most queried table has multiple indexed columns (this is currently causing slow imports with SQLite at 15m rows)
  • inserts done manually weekly, but should be done quickly i.e not having to go to sleep and wake up before i can query the DB with the freshly imported 100k-1m rows
  • almost no update queries

The only problem i have so far is the speed of importing new data sets into my DB, which i only need to do infrequently and manually. It has gotten very slow if not impossible to import data with the current amount of records (15 million) due to the indexing. This leaves me two options for SQLIte inserts:

  1. Spread the data across thousands of SQLite tables – added complexity and i believe this would all need to be custom coded in my app

  2. Drop indexes -> insert new data -> re-create indexes – also quite slow

Could PostgreSQL or another relational DB offer fast inserts with multiple indexed columns up to 100 million + rows?

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP