1
vote
2answers
79 views

What is faster, one big query or many small queries?

I have been working for different companies, and I have noticed that some of them prefer to have views that will join a table with all its "relatives". But then on the application some times, we only ...
0
votes
1answer
24 views

Solutions to migrate databases

I'm working in redesign an application. This application has a MySQL database and has approximately 50 tables (there are no relations , there are not standars, etc). We want to migrate to Postgresql ...
3
votes
2answers
375 views

Is Data Warehousing possible in MySQL and PostgresSQL [closed]

I am fairly new to DB design and development. My requirement is simple drilldown/slicing based on time and language of words (in a language on a particular day). However, my db is mysql. But so far I ...
0
votes
1answer
37 views

Translating MySQL query to PostgreSQL

I have a query which works fine in MySQL, however when I try to recreate the same query in PostgreSQL (same DB structure) I just get a syntax error. MySQL: SELECT ticket.queue_id, queue.name, ...
0
votes
3answers
50 views

PostgreSQL equivalent of MySQL query variables?

Is there a straightforward way to adapt these types of MySQL queries to PostgreSQL: setting variables in MySQL like set @aintconst = -333 set @arealconst = -9.999 It seems not. Assigning ...
0
votes
0answers
33 views

Storing DB credentials in an application source code?

I am making an app which connects to a DB, and the app will have plenty different userslogins. Is it a good design choice to have a specific DB login/password for the application, using which the ...
0
votes
1answer
26 views

Useful guide & FAQ for Application+SQL [closed]

Of course when you are making your first application for fun it doesn't matter that much how you deal with things. But when you need everything to be up to the current standards you don't always know ...
0
votes
1answer
7 views

Determining which database instance makes biggest IO

Assuming that I have a dedicated server on which I am running multiple instances of mysql and postresql servers. How without iotop determine which instance in particular time (proc/pid/io shows data ...
1
vote
0answers
40 views

How to measure bandwidth requirements for MySQL, Postgres, …?

TLTR: how to configure MySQL/Postgres to get a fair measurement of the I/O requirments for analytical queries? Hello, for certain database and especially database hardware experiments for want to ...
0
votes
1answer
52 views

group by clause without aggregate function

I've always assumed GROUP BY was designed specifically for aggregate functions and in all other circumstances you should use ORDER BY. For example, we have three tables: orders, shippers, and ...
0
votes
2answers
91 views

Database Design Help: MySql/PostgreSQL Single Database/Multiple Databases

We are planning to build a web based custom business analytics tool using PHP and MySQL/PostgreSQL. In our application, we are planning to upload customer’s data using a Excel sheet and these data ...
1
vote
3answers
335 views

What's the most efficient UUID column type

For storing a 128 bits UUID there are multiple storage options: a byte[16] column two bigint/long(64 bits) columns a CHAR(36) column - 32 hex digits + 4 dashes. a UUID database specific column, if ...
1
vote
0answers
407 views

Mariadb new features vs PostgreSQL? [closed]

Mariadb 10 offer lot of new features. ( https://mariadb.com/products/mariadb/mariadb-vs-mysql) . I want to know, with these new features, will PostgreSQL would be a better solution for newly starting ...
1
vote
2answers
71 views

how should i do this trigger?

I am a newbie to the sql world :) Right now i'm trying to create my first mysql trigger that would either update a row or insert a new one. Could you help me with that ? Here is the code : DELIMITER ...
18
votes
6answers
708 views

Count where two or more columns in a row are over a certain value [basketball, double double, triple double]

I play a basketball game which allows to output its statistics as a database file, so one can calculate statistics from it that are not implemented in the game. So far I've had no problem caluclating ...
1
vote
4answers
128 views

Mass data upload from Excel - technology

I am a Developer seeking help in terms of DB technology for a specific requirement. I have done some research but can not conclude with list. Requirement: There will be a web application which will ...
2
votes
2answers
161 views

Problem translating MySQL to PostgreSQL

We're using Trac for issue tracking. I migrated it from MySQL to PostgreSQL, but some of the ticket queries no longer work. In the below, all I did this far is replace IFNULL() with COALESCE() and ...
-4
votes
1answer
297 views

What are the disadvantages of PostgreSQL compared to other DBMS? [closed]

I am a student studying PostgreSQL. I want to know the advantages and disadvantages of PostgreSQL in comparison to other DBMS.
2
votes
0answers
76 views

How to distribute a given database between multiple DBMS on a LAN? [closed]

Given a Database D, i need to distribute it over different DBMS in a LAN. D will be split into tables, and each DBMS will store a given table. Here is a sketch of what i need to do : I also need to ...
1
vote
1answer
119 views

Vacuum in postgresql and Purge in mysql

Do the vacuum operation in postgresql and purging operation in mysql perform the same tasks?
1
vote
1answer
77 views

Best approach on storing/displaying sanitized data

Surfing the web I find two approaches on storing data in databases. Approach 1: Store data in the database with the html-specialcharacters sanitized $userdata = $_POST['field']; ...
2
votes
1answer
258 views

What is the difference between MySQL and PostgreSQL in terms of writing SQL queries? [closed]

Other than performance and security is there really any difference between MySQL and Postgres like SQL queries, built-in functions, procedures and so on? I'm asking this because I'm familiar with PHP ...
0
votes
0answers
143 views

Postgres vs MySQL Database Maintenance

I have been using Postgres and MySQL for a long time now. There are a few routine maintenance chores that must be performed on a regular basis to keep a PostgreSQL server running smoothly. As as ...
4
votes
1answer
1k views

MySQL failing and very slow on importing a large file

I have a 47 GB MySQL dump of a single table: http://dumps.wikimedia.org/commonswiki/latest/commonswiki-latest-image.sql.gz I ultimately want it into PostgreSQL, but since I didn't figure out an easy ...
0
votes
0answers
70 views

Converting a Ruby on Rails site from MySQL to PostgreSQL? [closed]

Whats the best way to convert a MySQL database to PostgreSQL so that it does not create errors, and safely transferred? My developer is redeveloping my site and he suggested it use PostgreSQL ...
-1
votes
2answers
147 views

What database should I use to create a webmail service like gmail? [closed]

I planned to use python for my frontend with django framework. But for backend, I have no idea which database I should use. Since it involves plenty of reads and writes can someone point me in the ...
1
vote
0answers
83 views

How can I ensure a valid MySQL -> Postgres migration? [closed]

Our projects are migrating from MySQL to Postgres. We have a Ruby-on-Rails app that handles the schema (source code is on github). We have rewritten all queries to be ANSI-compliant. We have tried ...
2
votes
1answer
432 views

Partitioning MySQL vs PostgreSQL

For our new system we are looking for database server. We considered a lot of pros/cons for MySQL(InnoDB) vs PostgreSQL and the breaking point for us is partitioning. We will choose the one which ...
0
votes
0answers
70 views

What is the postgresql equivalent of mysql grant require x509?

We're migrating from MySQL to PostgreSQL. We have to support SSL. MySQL supports creating users which require SSL certificate validity using "GRANT ... REQUIRE X509" [1]. Does PostgreSQL have ...
0
votes
3answers
451 views

Performance of primary key IDs with gaps (but in sequential order)

I know that having non-sequential IDs is bad for index performance. But assuming all my IDs are created in correct order, but with large gaps: i.e: 154300000 283700000 351300000 464200000 ...will ...
0
votes
1answer
157 views

Measuring SQL execution time in PostgreSQL?

MySQL has a performance_schema database that allows one to capture SQL statement execution time data in a table (e.g. performance_schema.events_statements_history_long ; useful MySQL link). I was ...
0
votes
2answers
283 views

Which schema is better for a shopping project? [closed]

I'm working on a business-to-customer project that has variety of product types. There are a few properties like name, description, brand_id that each product has but there are also many specialized ...
1
vote
1answer
1k views

Postgresql vs MySQL - Which is better for join queries & writing data(inserts) [closed]

I have to design a database which will end up with 50M records in a single table(there will be other tables with lesser number of records). I'm more concerned with join queries & writing ...
0
votes
1answer
150 views

How does MySQL or PostgreSQL deal with multi-column indexes in ActiveRecord?

I'm creating indexes for my models right now and I want to know how MySQL and PostgreSQL deal with an index with more than 1 column like: add_index :users, [:username, :created_at] That should ...
3
votes
2answers
330 views

Equivalents to `ora_rowscn` in other databases?

Do other databases other than Oracle have ora_rowscn (http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns007.htm) equivalents, or do you have to implement them yourself in your fields, ...
-1
votes
1answer
449 views

how to export postgresql database table into mysql

I am trying to get data from a Postgres database, but I am totally new to using Postgres. I am here to ask my question to get some ideas from the best answer on how can I can do this. If possible, I'd ...
2
votes
1answer
159 views

Scaling of PostGIS vs MySQL cos/sin calculations [closed]

I need to sort database rows according to GPS coordinates (or any other geographical representation). The way I've understood it, MySQL Spatial Indexes are too buggy at the time of writing (at least ...
0
votes
2answers
124 views

Recommendation for RDBMS for large table [closed]

I have been asked for recommendation on the optimal RDBMS for a large amount of data. The largest table would contain 2TB of data. My own largest table is only 400GB (mysql, performs very well). The ...
0
votes
1answer
432 views

“relation does not exist” trying to import mysql dump into postgres

environment: ubuntu 10.04 mysql server 5.1.69 postgres 9.2 Here's the sequence of steps: created a new postgres database, myDatabase executed this command: mysqldump -u root -p ...
0
votes
4answers
1k views

Daily and Differential Backups [closed]

Is there a program with which I can create daily MySQL, PostgreSQL and MariaDB backups with these requirements: The first time, make a full backup. The second time, only the changes since the last ...
2
votes
4answers
1k views

Database design - People and Organisations

Databases are not my primary skill and I am finding I need some assistance please. The software we are building has "Customers". The Customer could either be a Person or an Organisation. I really ...
1
vote
1answer
6k views

Performance difference between MySQL and PostgreSQL for the same schema/queries [closed]

I'm a newbie DBA, and I have experience in Microsoft SQL Server but I want to jump to FLOSS. I'm starting a company, and we develop an app (PHP) with a Postgres backend, and we did some tests ...
1
vote
1answer
150 views

Equivalent of MRG_MYISAM in databases other than MySQL? [closed]

Does anyone know if other database have something equivalent to MRG_MYISAM (aka the MERGE table type/storage engine)? I know about fragmenting, but this is not quite the same AFAIK. We're using ...
0
votes
1answer
216 views

Object-oriented programming CRUD design pattern

At first I have posted this question at http://cstheory.stackexchange.com/questions/17170/object-oriented-programming-crud-design-pattern - that's because I think it's a subect question, and it can ...
1
vote
0answers
1k views

Achieve PostgreSQL hstore functionality in MySQL

Please suggest any MySQL 5.5+ Engine that can support collection of key-value pairs be stored in any column of a table. I don't know if this feature is available out of the box in MySQL like ...
0
votes
1answer
476 views

multi-master to single-slave replication at table level with PostgreSQL or MySQL

Here is my scenario Master1 hosting DB1 Master2 hosting DB2 ... MasterN hosting DBN replicate to: Slave1 hosting DB1,DB2... DBN I've read similar questions and they recommend to start different ...
6
votes
1answer
1k views

Is PostgreSQL replication production ready?

How does PostgreSQL native replication compare to MySQL? I know asynchronous replication has been supported for longer than sync, which is recent. Is synchronous reliable to be used in real projects? ...
1
vote
1answer
219 views

Efficient query plan for selecting all data duplicated on several columns

The flavour in question is PostgreSQL, but it is a generic enough query pattern that your techniques for optimising against other RDBMs should help as well. The purpose of the query is to show all ...
1
vote
1answer
278 views

Which Database to choose: MySQL or Oracle

I have an application generating time series data (32 channels, 22KHz, 6 secs) once a minute. These have to be stored in a database. At the moment I'm using MySQL with InnoDB tables ...
0
votes
1answer
226 views

Storing 5TB web server access log vs OLAP DB

We have over 5TB compressed web server log in a HDFS and we often analyse using Hadoop. It is painful to run map reduce on 5TB of data and most importantly, not many developer are familiar with it. ...