New answers tagged

0

Have you ever tested something similar in production? As a general rule, I don't test things in production if I can avoid it. I have certainly tested them in test, however. Example 2: add the btree_gin extension and create a composite index on created_at and tags. The problem is the same as above: I think that PostgreSQL cannot use ordering since the ...


0

There are two approaches: create an index on the array: CREATE INDEX ON items USING gin (tags); That allows the database to quickly find the matching rows, but then it has to perform a top-n sort. create a B-tree index on created_at: CREATE INDEX ON items (created_at); That will allow the database to avoid the sort, but it has to scan and discard the ...


0

Generate the current explain plan using explain select * from table_name; Create a gin index on tags column and btree index on created at column. Generate the new query/explain plan post index creation to notice the cost difference and execution times.


1

Unless you have insert triggers that do complex stuff, an INSERT is probably bottlenecked by either CPU or I/O. Use vmstat 1 to determine what is the case: High CPU utilization is obvious, high I/O load manifests as iowait% at 10 or higher. This assumes that you have not set CPU or I/O limits on the container. If both values look fine, you are probably not ...


1

This is not a horrible load for modern server hardware. On a write heavy DB its going to be dependent on the Hard-disk layer, triggers, functions, indexes and constraints that have to be processed/checked. An easy way to get to the information you want is to install pgAdmin, and use the dashboard to see what is going on. Number of connections, ...


2

psql -Atq -c 'SELECT * FROM atable ORDER BY id' | md5sum


2

If you don't have any foreign keys referencing that table (i.e. "incoming" foreign keys), you could do something like this: -- save the squashed data into a temporary table create temp table new_data as SELECT min(date) as date, user_id, sum(usd_value) as usd_value, sum(eur_value) as eur_value from ledger GROUP BY date_trunc('hour', date), user_id; -- get ...


0

If you reduce wal_level to replica, and you have a logical replication slot (as is needed for logical replication), the server will refuse to restart. This is because wal_level = logical is required by logical decoding and logical replication. If you first drop the subscription and the publication, thereby removing logical replication, the replication slot ...


Top 50 recent answers are included