Linked Questions

9 votes
5 answers
6k views

Enforce NOT NULL for set of columns with a CHECK constraint only for new rows

I have a table and need to add a new column without a default value: Constraint: ALTER TABLE integrations.billables DROP CONSTRAINT IF EXISTS cc_at_least_one_mapping_needed_billables, ADD ...
user avatar
4 votes
3 answers
1k views

Performance of rare SELECT vs. frequent INSERT in timeseries data

I have a simple timeseries table movement_history ( data_id serial, item_id character varying (8), event_time timestamp without timezone, location_id character varying (7), ...
Codeguy007's user avatar
53 votes
2 answers
87k views

Optimizing bulk update performance in PostgreSQL

Using PG 9.1 on Ubuntu 12.04. It currently takes up to 24h for us to run a large set of UPDATE statements on a database, which are of the form: UPDATE table SET field1 = constant1, field2 = ...
xyzzyrz's user avatar
  • 651
29 votes
2 answers
59k views

Disable all constraints and table checks while restoring a dump

I've obtained a dump of my PostgreSQL database with: pg_dump -U user-name -d db-name -f dumpfile which I then proceed to restore in another database with: psql X -U postgres -d db-name-b -f ...
Marcus Junius Brutus's user avatar
7 votes
2 answers
6k views

What causes large INSERT to slow down and disk usage to explode?

I have a table of about 3.1 million rows with the following definition and indexes: CREATE TABLE digiroad_liikenne_elementti ( ogc_fid serial NOT NULL, wkb_geometry geometry(Geometry,4258), ...
jeran's user avatar
  • 73
6 votes
2 answers
4k views

Improve update performance in PostgreSQL data warehouse (RDS)

I am using a Postgres data warehouse hosted on Amazon RDS. When trying to update one column of a fact table (25 million rows) from another table in the same database, the query takes several days to ...
ChristopherTull's user avatar
5 votes
2 answers
2k views

Alternative to CLUSTER without table lock

I'm facing performance degradation and increasing storage usage as a result of frequent new and updated records causing index and storage fragmentation. VACUUM doesn't help much. Unfortunetely ...
brauliobo's user avatar
  • 176
3 votes
2 answers
272 views

Can additional indices ever aggravate lock contention?

Not considering index creation: Can it ever be disadvantageous to create an index on a table with regards to lock acquisition? I have experienced already some cases where a carefully selected index ...
lxgr's user avatar
  • 131
14 votes
1 answer
7k views

How do completely empty columns in a large table affect performance?

I have 400 million rows in a Postgres db, and the table has 18 columns: id serial NOT NULL, a integer, b integer, c integer, d smallint, e timestamp without time zone, f smallint, g timestamp without ...
ebi's user avatar
  • 243
9 votes
1 answer
7k views

Sort table rows and save the row numbers using UPDATE

I have Postgres 9.5 with a table movimientos that has the following data: | id | concepto | movimiento | numero | orden | | 1 | AJUSTE 1 | 2542 | 0 | 2 | | 2 | APERTURA | 12541 |...
Aegis's user avatar
  • 271
9 votes
1 answer
3k views

Managing and speeding up queries on PostgreSQL table with over 3 trillion rows

I have time series data which spans over 10 years and has over 3 trillion rows and 10 columns. At the moment I use a PCIe SSD with 128GB of RAM and I am finding that querying takes a significant ...
Greg's user avatar
  • 381
7 votes
1 answer
2k views

How to safely replace the old table with its cleaned copy?

I have created a table_cleaned like table and inserted unique values from table into table_cleaned. Now I want to replace table with table_cleaned with DROP TABLE table; ALTER TABLE table_cleaned ...
Dmitriy Grankin's user avatar
6 votes
1 answer
23k views

Efficient way to insert/update/delete table records from complex query in Postgres 9.x

I have this function which returns a set a records and I need to persist those records into a table. I have to do it hundred times a day. My initial approach was just clear data from my table and re-...
Christian's user avatar
  • 335
3 votes
1 answer
1k views

Copying Data into another table and removing older data should I do in batches and should I remove indexes and FK constraints?

I am looking to remove older data from a table which consists of 889 million rows of data. I have a script but I am trying to make it more robust as removing approximately 418 million rows of data to ...
rdbmsNoob's user avatar
  • 459
2 votes
1 answer
1k views

Optimizing bulk update performance in Postgresql with dependencies

Basically my question is the same as this one, but WITH dependencies, so drop/renaming the table is not a trivial option (I assume). We are refactoring a large, poorly designed table which has many ...
Dwayne Towell's user avatar

15 30 50 per page