#Help regarding updating millions of records

12 messages · Page 1 of 1 (latest)

steady bone
#

First check you've got suitable indexes in place for the updates you're doing, secondly check your query plan https://explain.dalibo.com/

explain.dalibo.com

Visualizing and understanding your PostgreSQL execution plans made easy.

unkempt root
#

Use things like an explain query to figure out what is slow. 6.5 million rows in itself isn't that much data, depends on proper indexes etc. Also things like too many indexes can cause negative performance as it would take a lot of time to recalculate indexes

ancient sphinx
#

yes i have properly indexed

i have a table called points, it has 8 columns, 1 is id that is primary key auto-incrementing and unique, one is relation with other table, one is
value (decimal, 15, 2), i have another 2 columns that needed to be updated based on current value, update all the records based on date comparison,
the current value and what the value was exactly one year back, and the other column is 5 years back, and im planning to add more columns in the table, first time i insert the value just by reading the csv that is super fast, but when i update other values it takes hell of time

here is the migration

            $table->id();
            $table->foreignId('source_id')->constrained('sources');
            $table->string('iid');
            $table->integer('type')->default(0)->comment('0 = unknown, 1 = state, 2 = metro, 3 = county, 4 = zip');
            $table->date('date');

            $table->decimal('value', 15, 2)->nullable();
            $table->decimal('value_growth_yoy', 15, 2)->nullable();
            $table->decimal('value_growth_five_year', 15, 2)->nullable();

            $table->timestamps();
            $table->softDeletes();
            $table->index('source_id');
            $table->index('date');
            $table->index('type');

and i did write a plan and submitted it
im stuck on this issue for 4 days

steady bone
#

I want to say partitioning but 6.5m isn't a lot of rows for postgres

ancient sphinx
#

partioning ? let me see what is it

unkempt root
#

I'd say, the first thing to figure out would be why it's slow. Sure, partitioning might help, but if it doesn't fix the core issue then it's just a band-aid.

ancient sphinx
#

here is what we are doing
ancient sphinx
steady bone
#

Partitioning isn't indexing, and please look at #rules to see how to format your code properly

#

you also haven't shown the explain plan

ancient sphinx
ancient sphinx