Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
70
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,033
  • 1
  • 11
  • 15
70
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,789
  • 5
  • 16
  • 27
70
votes
4 answers

Can a single PostgreSQL query use multiple cores?

In recent versions of PostgreSQL (as of Dec 2013), can we share a query between two or more cores to get a performance boost? Or should we get faster cores?
Alireza
  • 3,636
  • 10
  • 37
  • 44
70
votes
7 answers

When should I rebuild indexes?

When should I rebuild the indexes in my relational database (SQL Server)? Is there a case for rebuilding indexes on a regular basis?
Nick Chammas
  • 14,670
  • 17
  • 75
  • 121
70
votes
4 answers

Index Seek vs Index Scan

Looking at an execution plan of a slow running query and I noticed that some of the nodes are index seek and some of them are index scan. What is the difference between and index seek and an index scan? Which performs better? How does SQL choose…
Greg
  • 3,252
  • 5
  • 31
  • 55
70
votes
1 answer

How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

I want to migrate a fairly simple, internal, database driven application from SQLite3 to PostgreSQL 9.3 and tighten the permissions in the DB as I go. The application currently consists of a command to update the data; and one to query it.…
Jim Dennis
  • 755
  • 1
  • 6
  • 12
69
votes
12 answers

Passing array parameters to a stored procedure

I've got a process that grabs a bunch of records (1000's) and operates on them, and when I'm done, I need to mark a large number of them as processed. I can indicate this with a big list of IDs. I'm trying to avoid the "updates in a loop" pattern,…
D. Lambert
  • 1,057
  • 1
  • 8
  • 8
69
votes
4 answers

Created user can access all databases in PostgreSQL without any grants

I must be missing something with regards to setting up PostgreSQL. What I'd like to do is create multiple databases and users that are isolated from each other so that a specific user only has access to the databases I specify. However, from what I…
mikeplate
  • 793
  • 1
  • 6
  • 4
69
votes
5 answers

Is it safe to use innodb_flush_log_at_trx_commit = 2

I turned innodb_flush_log_at_trx_commit = 2 and get a very fast write speed. But is it safe be used in production web site?
Bruce Dou
  • 835
  • 1
  • 9
  • 8
68
votes
3 answers

Why is a new user allowed to create a table?

I'm wondering why a newly created user is allowed to create a table after connecting to a database. I have one database, project2_core: postgres=# \l List of databases Name | Owner |…
andreas-h
  • 813
  • 1
  • 6
  • 6
68
votes
3 answers

What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?

I have an indexed column that stores an MD5 hash. Thus, the column will always store a 32-character value. For whatever reason, this was created as a varchar rather than a char. Is it worth the trouble of migrating the database to convert it to a…
Jason Baker
  • 781
  • 1
  • 5
  • 6
68
votes
1 answer

How do I swap tables in MySQL?

Suppose, I have a table foo, which contains some statistics that are computed every now and then. It is heavily used by other queries. That's why I want to compute more recent statistics in foo_new and swap them when computation is ready. I could…
Ben
  • 877
  • 1
  • 7
  • 9
68
votes
5 answers

How can a group track database schema changes?

What version control methodologies help teams of people track database schema changes?
Toby
  • 1,128
  • 2
  • 12
  • 12
68
votes
9 answers

Plural vs Singular Table Name

How should I name my Tables when creating a new database? Singular: Client or Plural: Clients?
John Isaiah Carmona
  • 783
  • 1
  • 5
  • 7
67
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,540
  • 13
  • 46
  • 72
1 2 3
…
99 100