Job Description
Job Title: Senior Associate Developer
Education: MCA/B.E/B.Tech/ BSc/ Graduation
Experience: 6+years
Location: Bangalore/Mumbai/Hyderabad/Anywhere in India
Key Skills: SQL Development, Oracle and PostgreSQL SQL Expert.
Job Description:
Database Migration Experience:
Experience on Database Migration from Oracle to PostgreSQL, SQL Server to PostgreSQL, MySQL DB to PostgreSQL. Experts knowledge on generating schema conversion reports, Analyzing the report.
Oracle Database Knowledge:
Oracle Architecture: Strong understanding of Oracle's architecture (e.g., tablespaces, control files, redo logs, and segments).
- PL/SQL Programming: Proficiency in Oracle's PL/SQL, which is Oracle's procedural language for writing stored procedures, triggers, and functions.
- Oracle-Specific Features: Familiarity with Oracle-specific features such as Sequences, Synonyms, Materialized Views, Packages, Triggers, Flashback, and Partitioning.
- Oracle Query Optimization: Understanding Oracle's execution plans, hinting, and query optimization techniques.
- Backup/Recovery in Oracle: Knowledge of Oracle's backup and recovery mechanisms (e.g., RMAN, Data Pump, and Flashback).
PostgreSQL Knowledge:
- PostgreSQL Architecture: Understanding PostgreSQL's architecture (e.g., MVCC, WAL, and the query planner).
- PostgreSQL Data Types: Familiarity with PostgreSQL's native data types (e.g., JSONB, UUID, ARRAY) and their counterparts in Oracle.
- PL/pgSQL Programming: Proficiency in PostgreSQL's procedural language (PL/pgSQL) for creating stored procedures, triggers, and functions.
- PostgreSQL Features: Knowledge of PostgreSQL features such as JSON support, Full-text search, PostGIS, and foreign data wrappers.
- Performance Tuning: Understanding of PostgreSQL's query optimization, indexing, and tuning parameters (e.g., shared_buffers, work_mem, effective_cache_size).
PostgreSQL Basics:
- Database Design: Knowledge of data modeling techniques, understanding normalization, denormalization, and the creation of efficient schemas.
- SQL Proficiency: Strong command of SQL for writing complex queries, including SELECT, INSERT, UPDATE, DELETE, and data manipulation.
- Indexes: Understanding of indexing techniques (B-tree, GIN, GiST, BRIN) to optimize query performance.
- Normalization: Familiarity with normalization principles (1NF, 2NF, 3NF, BCNF) to design relational database structures efficiently.
- Relationships & Constraints: Knowledge of foreign keys, primary keys, unique constraints, and check constraints.
- Transactions and ACID Principles: Understanding of transactions, commit, rollback, isolation levels, and ensuring database integrity using ACID (Atomicity, Consistency, Isolation, Durability).
SQL Query Optimization:
- Writing efficient queries and using EXPLAIN to analyze execution plans.
- Identifying bottlenecks and applying query optimization strategies like using indexes, avoiding subqueries, and optimizing joins.
- Understanding execution plans and query tuning to improve performance.
Data Types and Functions:
- Familiarity with PostgreSQL-specific data types like JSON, JSONB, hstore, ARRAY, UUID, CITEXT, TSVECTOR (for full-text search), and others.
- Writing and using PostgreSQL built-in functions and creating custom functions using PL/pgSQL (PostgreSQL's procedural language).
- Working with stored procedures, triggers, and using functions to encapsulate business logic in the database.
PostgreSQL on Cloud:
- Experience with deploying PostgreSQL on cloud platforms like Amazon RDS, Google Cloud SQL, Azure Database for PostgreSQL, and other managed services.
- Familiarity with cloud-native data management tools like Amazon Aurora for PostgreSQL.
