Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
48
votes
2 answers

When To Update Statistics?

I've inherited a Maintenance Plans that does the following: Cleanup old data Checks DB integrity Performs Database and Transaction Log Backups Reorganizes Our indexes Updates Statistics Delete old backups and Maintenance Plan files Of the 23…
Onion-Knight
  • 1,089
  • 2
  • 10
  • 15
48
votes
2 answers

How to partition existing table in postgres?

I would like to partition a table with 1M+ rows by date range. How is this commonly done without requiring much downtime or risking losing data? Here are the strategies I am considering, but open to suggestions: The existing table is the master and…
Evan Appleby
  • 1,203
  • 1
  • 10
  • 16
47
votes
2 answers

How to insert or update using single query?

I have a table test having columns id which primary key and auto incremented and name. I want to insert a new record if annd only if there are no records.For example input is id=30122 and name =john if there are records with id 30122 then I have…
SpringLearner
  • 611
  • 1
  • 5
  • 9
47
votes
3 answers

Replace a materialized view in Postgres

I have a materialized view in Postgres 9.3 that I'd like to update with new columns. However, other materialized views also depend upon this view, and the error message indicates that dropping a view isn't possible when other objects depend on it. …
John
  • 777
  • 1
  • 7
  • 12
47
votes
1 answer

How can I benchmark a PostgreSQL query?

I want to benchmark a query containing a user-defined function I have written for PostgreSQL. Are there any standard ways to run such a benchmark? I know that timing can be turned on with \timing at the psql prompt, but ideally I'd like to have a…
Franck Dernoncourt
  • 2,083
  • 13
  • 34
  • 52
47
votes
9 answers

Asked to Not Use Transactions and to Use A Workaround to Simulate One

I've been developing T-SQL for several years and am always digging in further, continuing to learn all I can about all aspects of the language. I recently started working at a new company and have received what I think is an odd suggestion regarding…
Forrest
  • 411
  • 4
  • 5
47
votes
1 answer

Scalability limitations of PostgreSQL and MySQL

I've heard that performance of non-sharded relational database such as MySQL or PostgreSQL "breaks" beyond 10 TB. I suspect that limits as such do exist as one would not come up with Netezza, Greenplum, or Vertica, etc, however I would like to ask…
Edmon
  • 759
  • 1
  • 8
  • 13
47
votes
1 answer

What does IDLE state denotes in a row of pg_stat_activity?

Can someone resolve my below doubts about pg_stat_activity in terms of postgres 9.5? Can some let me know what does the state IDLE denotes in the row of pg_stat_activity? Is it like it checks all the open connections to postgres and if the…
tuk
  • 1,263
  • 1
  • 15
  • 22
47
votes
6 answers

Is there any limit to the number of databases you can put on one SQL server?

I'm setting up a SaaS system, where we're planning to give each customer their own database. The system is already set up so that we can easily scale out to additional servers if the load becomes too great; we're hoping to have thousands, or even…
Shaul Behr
  • 2,963
  • 8
  • 34
  • 42
47
votes
5 answers

I need to run VACUUM FULL with no available disk space

I have one table that is taking up close to 90% of hd space on our server. I have decided to drop a few columns to free up space. But I need to return the space to the OS. The problem, though, is that I'm not sure what will happen if I run VACUUM…
Justin Rhyne
  • 573
  • 1
  • 4
  • 5
47
votes
3 answers

When is it better to create STATISTICS instead of creating an Index?

I have found plenty of information on what STATISTICS are: how they are maintained, how they can be created manually or automatically from queries or indexes, and so on. But, I have been unable to find any guidance or "best practices" information…
Solomon Rutzky
  • 70,048
  • 8
  • 160
  • 306
47
votes
2 answers

Order by column should have index or not?

I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How performance will affect by making or not making index to…
Somnath Muluk
  • 1,104
  • 3
  • 13
  • 15
47
votes
3 answers

Is it acceptable to have circular foreign key references\How to avoid them?

Is it acceptable to have a circular reference between two tables on the foreign key field? If not, how can these situations be avoided? If so, how can data be inserted? Below is an example of where (in my opinion) a circular reference would be…
KidCode
  • 872
  • 1
  • 8
  • 14
46
votes
1 answer

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

I have a legacy schema (disclaimer!) that uses a hash-based generated id for the primary key for all tables (there are many). An example of such an id is: 922475bb-ad93-43ee-9487-d2671b886479 There is no possible hope of changing this approach,…
Bohemian
  • 723
  • 1
  • 8
  • 13
46
votes
3 answers

Excessive sort memory grant

Why is this simple query granted so much memory? -- Demo table CREATE TABLE dbo.Test ( TID integer IDENTITY NOT NULL, FilterMe integer NOT NULL, SortMe integer NOT NULL, Unused nvarchar(max) NULL, CONSTRAINT PK_dbo_Test_TID …
Paul White
  • 94,921
  • 30
  • 437
  • 687
1 2 3
…
99 100