Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
80
votes
5 answers

What risks are there if we enable read committed snapshot in sql-server?

I have read here that some extra data will be stored per row so we might see a performance degradation but what other risks are there? eg. Will this affect recovery of the database? Is there anything else we need to do to take advantage of this? I…
Adam Butler
  • 1,531
  • 4
  • 16
  • 16
80
votes
1 answer

MERGE a subset of the target table

I am trying to use a MERGE statement to insert or delete rows from a table, but I only want to act on a subset of those rows. The documentation for MERGE has a pretty strongly worded warning: It is important to specify only the columns from the…
KutuluMike
  • 1,599
  • 2
  • 14
  • 13
79
votes
6 answers

Disable MySQL binary logging with log_bin variable

Default MySQL config file /etc/mysql/my.cnf installed by some debian package using APT often set log_bin variable, so binlog are enabled: log_bin = /var/log/mysql/mysql-bin.log When I want to disable binary logging on such installation, comment out…
Nicolas Payart
  • 2,508
  • 5
  • 28
  • 36
79
votes
3 answers

What is the difference between a connection and a session?

What is the difference between a connection and a session and how they are related?
jrara
  • 5,393
  • 20
  • 58
  • 65
78
votes
6 answers

How do I efficiently get "the most recent corresponding row"?

I have a query pattern that must be very common, but I don't know how to write an efficient query for it. I want to look up the rows of a table that correspond to "the most recent date not after" the rows of another table. I have a table, inventory…
Tom Ellis
  • 1,639
  • 3
  • 16
  • 14
77
votes
7 answers

How to list all views in SQL in PostgreSQL?

How do I list all views for a database using an SQL command in PostgreSQL? I would like something similar to output of the psql \dv command, but preferably just a list of view names. e.g., SELECT ...; my_view_1 my_view_2 my_view_3 I'm running…
Rob Bednark
  • 2,253
  • 6
  • 22
  • 22
76
votes
4 answers

Function Performance

Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company. One of the things I would like to do is move some of the application…
Derek Downey
  • 23,568
  • 11
  • 79
  • 104
75
votes
7 answers

What is a valid use case for using TIMESTAMP WITHOUT TIME ZONE?

There is a long and quite elucidating answer on the differences between TIMESTAMP WITH TIME ZONE -vs- TIMESTAMP WITHOUT TIME ZONE available in the SO post Ignoring time zones altogether in Rails and PostgreSQL. What I would like to know is: Are…
Marcus Junius Brutus
  • 3,409
  • 7
  • 30
  • 44
75
votes
8 answers

MAXDOP setting algorithm for SQL Server

When setting up a new SQL Server, I use the following code to determine a good starting point for the MAXDOP setting: /* This will recommend a MAXDOP setting appropriate for your machine's NUMA memory configuration. You will need to evaluate…
Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
75
votes
3 answers

What are the drawbacks with using UUID or GUID as a primary key?

I would like to build a distributed system. I need to store data in databases and it would be helpful to use an UUID or a GUID as a primary key on some tables. I assume it's a drawbacks with this design since the UUID/GUID is quite large and they…
Jonas
  • 33,945
  • 27
  • 62
  • 64
74
votes
3 answers

Optimizing a Postgres query with a large IN

This query gets a list of posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others. With this style of query, the obvious optimization would be to cache the "Post" ids, but unfortunately…
Garrett
  • 1,083
  • 1
  • 11
  • 16
74
votes
5 answers

How to create a temporary table using VALUES in PostgreSQL

I am learning PostgreSQL and trying to figure out how to create a temporary table or a WITH declaration that can be used in place of regular table, for debugging purposes. I looked at the documentation for CREATE TABLE and it says VALUES can be…
tinlyx
  • 3,810
  • 14
  • 50
  • 79
74
votes
5 answers

Is there a name for this database schema of key values?

We process a routine data feed from a client who just refactored their database from a form that seems familiar (one row per entity, one column per attribute) to one that seems unfamiliar to me (one row per entity per attribute): Before: one column…
prototype
  • 853
  • 1
  • 7
  • 6
74
votes
10 answers

Why do people recommend not using the name "Id" for an identity column?

I was taught not to use the name Id for the identity column of my tables, but lately I've just been using it anyways because it's simple, short, and very descriptive about what the data actually is. I've seen people suggest prefixing Id with the…
Rachel
  • 8,547
  • 20
  • 51
  • 74
72
votes
9 answers

Export Postgres table as json

Is there a way to export postgres table data as json to a file? I need the output to be line by line, like: {'id':1,'name':'David'} {'id':2,'name':'James'} ... EDIT: postgres version: 9.3.4
AliBZ
  • 1,827
  • 5
  • 17
  • 27
1 2 3
…
99 100