Linked Questions

7 votes
5 answers
15k views

Is it bad practice to add columns to an existing table?

Conversely: Is it better to get all the columns created at the time when a new table is created? I'm working on a new system and new requirements are coming up all the time. The latest requirement ...
Johan's user avatar
  • 613
12 votes
2 answers
21k views

Proper storage size estimation technique for PostgreSQL

We are preparing a PostgreSQL database for production usage and we need to estimate the storage size for this database. We are a team of developers with low expertise on database administration, so we ...
Bruno Polaco's user avatar
11 votes
2 answers
2k views

What fillfactor for caching table?

I have heavily updated / accessed table where I store serialized java objects. They are in the table for 2-3 hours (also are being updated during that period) and then removed. Size of table is around ...
Michal's user avatar
  • 163
8 votes
3 answers
6k views

Speed up creation of Postgres partial index

I am trying to create partial indexes for a large (1.2TB), static table in Postgres 9.4. My data is completely static, so I am able to insert all data, then create all indexes. In this 1.2TB table, ...
burnsy's user avatar
  • 275
6 votes
3 answers
4k views

Advantage of using INCLUDE as against adding the column in INDEX for covering index

Postgres docs state the following about Index-Only Scans and Covering-Indexes: if you commonly run queries like SELECT y FROM tab WHERE x = 'key'; the traditional approach to speeding up such queries ...
tuk's user avatar
  • 1,203
6 votes
1 answer
8k views

Postgres partial index on IS NULL not working

Postgres version Using PostgreSQL 10.3. Table definition CREATE TABLE tickets ( id bigserial primary key, state character varying, closed timestamp ); CREATE INDEX "state_index" ON "tickets"...
GeekJock's user avatar
  • 115
7 votes
2 answers
10k views

What is the most efficient way to count the number of rows in a table?

I am using Postgres with the following query: select count(*) from image; The primary key on this table is non-incrementing; it's a unique serial number for the images stored in the table. Our app ...
andrewniesen's user avatar
6 votes
2 answers
4k views

Exists subselect vs inner join?

I'm moving up to the next level of my mystery query. It looks like there's a subselect inside of an exists, but on the same table. I think this could probably be simplified with an INNER JOIN higher ...
neezer's user avatar
  • 113
4 votes
2 answers
5k views

Use column values in ORDER BY and LIMIT clauses

I have this table in a Postgres 9.3 database: I need to use the content of columns order and limit to sort and filter this table. The table will be sorted by column first_name. The final result will ...
Magno C's user avatar
  • 233
9 votes
2 answers
8k views

Optimizing queries on a range of timestamps (one column)

I am using Postgres 9.3 through Heroku. I have a table, "traffic", with 1M+ records that has many inserts and updates every day. I need to perform SUM operations across this table over different time ...
Evan Appleby's user avatar
  • 1,181
4 votes
1 answer
11k views

Find bloated tables and indexes in PostgreSQL without extensions

I have tables that change a lot during the day, lots of data is deleted, modified and inserted. I suspect that the tables and indexes on those tables might be bloated. I've seen that there are ...
Tom's user avatar
  • 428
3 votes
2 answers
8k views

Real time much greater than `EXPLAIN ANALYZE`'s execution time (index scan)

I want to fetch up to 100 rows based on their id. The id is the primary key of the table. The query that I had written looks like this: select * from table where id = any ($1); where $1 is ...
rubik's user avatar
  • 515
5 votes
2 answers
3k views

Good layout of 3d point data for spatial queries in Postgres?

Like shown in another question, I deal with a lot (>10,000,000) entries of points in a 3D space. These points are defined like this: CREATE TYPE float3d AS ( x real, y real, z real); If I am ...
tomka's user avatar
  • 927
2 votes
1 answer
2k views

Using pg_prewarm to load X number of latest rows into cache

We have a large query that is extra slow when customers "run it the first time, early in the morning..." So, I found pg_prewarm that I would like to use load into PG's buffer cache a certain amount ...
zam6ak's user avatar
  • 471
3 votes
1 answer
2k views

Why is my database 12 times bigger than inserted data?

I have a short question concerning my database size. I need to insert data in a database. Before the insert, some calculations need to be done. The point is: from 50 mb plain data (~700,000 lines), ...
A.c's user avatar
  • 45

15 30 50 per page