Most Popular
1500 questions
22
votes
3 answers
PostgreSQL High Availability/Scalability using HAProxy and PGBouncer
I have multiple PostgreSQL servers for a web application.
Typically one master and multiple slaves in hot standby mode (asynchronous streaming replication).
I use PGBouncer for connection pooling: one instance installed on each PG server (port 6432)…
Nicolas Payart
- 2,508
- 5
- 28
- 36
22
votes
2 answers
What is the meaning of tinyint(N)?
When we use an argument length with numerical data types, as far as I know this specifies the display width.
I tried the following:
mysql> create table boolean_test (var1 boolean, var2 tinyint);
Query OK, 0 rows affected (0.10 sec)
mysql>…
Cratylus
- 1,013
- 3
- 12
- 18
22
votes
3 answers
How to get a list of locked accounts / check that account is locked?
I can use following statement to unlock an account:
ALTER USER username ACCOUNT UNLOCK
But which statement can I use to verify that account is currently locked out?
Mike
- 747
- 8
- 14
- 25
22
votes
3 answers
Is there a way to access temporary tables of other sessions in postgres?
I'm working with a Windows application that uses a (local) postgres Database and stores some information in a temporary table. I'd like to have a look at the temporary table, but pgadmin and dbVis tell me: ERROR: cannot access temporary tables of…
newenglander
- 1,075
- 5
- 13
- 23
22
votes
2 answers
LIKE uses index, CHARINDEX does not?
This question is related to my old question. The below query was taking 10 to 15 seconds to execute:
SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE…
IT researcher
- 3,168
- 15
- 59
- 82
22
votes
3 answers
Check if postgresql database exists (case insensitive way)
Is there a "elegant built-in" case-insensitive way to check if db is exists?
I've found only SELECT datname FROM pg_catalog.pg_database WHERE datname='dbname', but this is a CS check.
The first thing that comes to mind to retrieve all db names and…
Andrei Orlov
- 537
- 2
- 4
- 12
22
votes
1 answer
Database design: Two 1 to many relationships to the same table
I have to model a situation where I have a table Chequing_Account (which contains budget, iban number and other details of the account) which has to be related to two different tables Person and Corporation which both can have 0, 1 or many chequing…
dendini
- 395
- 2
- 6
- 13
22
votes
2 answers
What is the difference between MySQL VARCHAR and TEXT data types?
After version 5.0.3 (which allowed VARCHAR to be 65,535 bytes and stopped truncating trailing spaces), is there any major difference between these two data types?
I was reading the list of differences and the only two of note are:
For indexes on…
Derek Downey
- 23,568
- 11
- 79
- 104
22
votes
7 answers
Where can I find some guidance on index strategies?
Most of us will probably agree that using database indexes is good. Too many indexes and performance can actually be degraded.
As a general rule, which fields should be indexed?
Which fields should not be indexed?
What are the rules for using…
SpecialAgent_W436
- 395
- 1
- 3
- 9
22
votes
4 answers
Should I manually VACUUM my PostgreSQL database if autovacuum is turned on?
I use software which makes a big PostgreSQL database (there is a table with a million rows in it) and the developers says I should VACUUM and ANALYZE periodically. But the PostgreSQL database default is autovacuum turned on.
Should I vacuum/analyze…
kissgyorgy
- 345
- 1
- 3
- 8
22
votes
3 answers
Modify all tables in a database with a single command
Is there a single or a one line command to modify all tables within a database. I would like to issue this command in every table within a database:
ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8;
My objective is to modify the charset from…
hodl
- 321
- 1
- 2
- 6
22
votes
1 answer
Should I be alarmed by this NO JOIN PREDICATE warning?
I'm troubleshooting the bits and pieces of a poorly-performing stored procedure. This section of the procedure is throwing a NO JOIN PREDICATE warning
select
method =
case methoddescription
when 'blah' then 'Ethylene…
swasheck
- 10,755
- 5
- 48
- 89
22
votes
3 answers
What is the correct result for this query?
I came across this puzzle in the comments here
CREATE TABLE r (b INT);
SELECT 1 FROM r HAVING 1=1;
SQL Server and PostgreSQL return 1 row.
MySQL and Oracle return zero rows.
Which is correct? Or are both equally valid?
Martin Smith
- 87,941
- 15
- 255
- 354
22
votes
3 answers
What's the difference between POINT(X,Y) and GeomFromText("POINT(X Y)")?
I'd like to store some geometric positions in my MySQL database. For this I use the POINT datatype. Almost everywhere I read that the function GeomFromText should be used to insert data in the table.
However, I found out that POINT(X,Y) also works.…
ComSubVie
- 323
- 1
- 2
- 6
22
votes
1 answer
Why does SQL Server 2012 Express use 9.5GB of RAM on my server?
I am building an application in which I plan to embed SQL Server 2012 Express as the primary datastore. When testing on my development machine (Win7-32 with 3GB RAM), I never observed the sqlservr.exe process to use more than 1GB of RAM as I would…
Dan
- 545
- 2
- 6
- 14